MySQL 分页查询优化和分析

1. 先关闭mysql缓存，避免影响sql真实查询效果

show variables like '%query_cache_%';

• query_cache_size 为缓存查询结果分配的内存的数量。默认值是0，即禁用查询缓存。
  请注意即使query_cache_type设置为0也将分配此数量的内存。
  要想防止query_cache_size的值运行时超过32MB，使用选项--maximum-query_cache_size=32M。
• query_cache_type 设置查询缓存类型0 1 2 (OFF ON DEMAND)

#variables系统变量,global 全局生效
#临时的直接执行
set global query_cache_size=0;
set global query_cache_type=0;

#永久的修改配置文件my.cnf ,添加下面的配置即可。
query_cache_type=0
query_cache_size=0

• 查看缓存命中情况 , Qcache_hits 数值的变化

show STATUS like '%qcache%';

2. 以pay_bill 查询支付流水表分页为例子

2.1 查看索引，先把索引删除（主键会自动生成唯一索引）。观察在无索引状态下查询速度

#查看表索引
show index from pay_bill;

• 删除索引

#DROP INDEX <索引名> ON <表名>
DROP INDEX index_transDate ON pay_bill;

只有主键索引

2.2 在Navicat里面测试感觉不准确，所以在项目里面，用jmeter压测。xml 如下

    <select id="queryBillExportV1" parameterType="Map" resultType="PayBill">
            SELECT id, createDate, modifyDate, transDate, appId, mchId, childMchId, deviceInfo, tradeNo, paySn, openid, payType, transStatus, paymentBank, currency, totalAmount, redEnvelopesAmount, wxRefundNo, refundNo, refundAmount, redEnvRefundAmount, refundType, refundStatus, riskName, businessData, fee, rate, remark FROM pay_bill
            order by transDate desc
            <!--order by 和 like 在使用时只能使用$()拼接符：用 #{}会导致sql语句中多个 ’ ’ ，sql语句失效-->
            LIMIT  ${map.offset},${map.pageSize}
    </select>

    <select id="queryBillExportV2" parameterType="Map" resultType="PayBill">
        SELECT a.id, a.createDate, a.modifyDate, a.transDate, a.appId, a.mchId, a.childMchId, a.deviceInfo, a.tradeNo, a.paySn, a.openid, a.payType, a.transStatus, a.paymentBank, a.currency, a.totalAmount, a.redEnvelopesAmount, a.wxRefundNo, a.refundNo, a.refundAmount, a.redEnvRefundAmount, a.refundType, a.refundStatus, a.riskName, a.businessData, a.fee, a.rate, a.remark FROM pay_bill a
        INNER JOIN
            (SELECT b.id FROM pay_bill b order by b.transDate desc LIMIT ${map.offset},${map.pageSize}) b
        ON a.id = b.id
    </select>

2.3 jmeter 10个并发，循环5次。数据40w，offset=1， pageSize=200

从结果来看，V2接口平均用时比V1接口要快。V1接口平均用时2938，V2接口平均用时2455

2.4 jmeter 10个并发，循环5次。数据40w，offset=400000， pageSize=200

从结果来看，V2接口平均用时比V1接口要快，limit分页越往后面查询速度会越慢。V1接口平均用时7505，V2接口平均用时2556.值得一提的是V2的中位数1696明显变快。

2.5 增加transDate 索引

ALTER TABLE pay_bill ADD INDEX index_transDate ( transDate );
受影响的行: 0
时间: 10.689s

# 创建脚本
# 1.PRIMARY  KEY（主键索引）
ALTER  TABLE  `table_name`  ADD  PRIMARY  KEY (  `column`  ) 
# UNIQUE(唯一索引)
ALTER  TABLE  `table_name`  ADD  UNIQUE (`column` ) 
# INDEX(普通索引)
ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column`  )
# FULLTEXT(全文索引)
ALTER  TABLE  `table_name`  ADD  FULLTEXT ( `column` )
# 多列索引(联合所以),支持column1 | column1,column2| column1,column2,column3 3种组合进行查找
ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column1`,  `column2`,  `column3`  )

2.6 jmeter 10个并发，循环5次。数据40w，offset=400000， pageSize=200

对比2.4，V1接口没有明显变化，但是V2速度提高了一倍。V1接口平均用时7349，V2接口平均用时1212

2.7 数据库版本,表数据大小

select version();

10.1.22-MariaDB

#查看指定库的指定表的大小
select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='pay' and table_name='pay_bill'

表和索引大小

注意：
对于大量数据深度分页查询慢是不可避免的。对于这类处理

1. 忽略总数，根据上一页下一页思想，例如，百度查询，es查询
2. 对于不变数据，建立排序字段。更具区间筛选，例如每页10条，查第2页 >20 <30