背景:
随着业务量上升,数据量越来越大,今天sql监控平台预警出一条慢sql,耗时约2.8s左右,如下:
SELECT * FROM table_a
WHERE
create_time BETWEEN '2021-03-04 17:10:00' AND '2021-04-05 17:10:00'
AND status = 1
AND MOD(id,8)=6
AND deleted = 0
表中索引:
create index idx_create_time_status
on pl_finance_fee_extend (create_time, status);
其中该日期内('2021-03-04 17:10:00' AND '2021-04-05 17:10:00'
)数据量约为70万,explain结果如下:
id = 1
select_type = SIMPLE
table = table_a
partitions =
type = ALL
possible_keys = idx_create_time_status
key =
key_len =
ref =
rows = 741470
filtered = 0.50
Extra = Using where
为什么明明有create_time, status
的联合索引,却没走索引呢?查了下资料大概意思是搜索出来的数量如果超过了整个表的1/5,time这种范围查找的索引就不会走了。此结论有待考正,毕竟走不走索引是mysql优化器说了算。
基于这个前提我们可以把时间范围缩小、切割,也就是把sql切割成31个,每个sql只查询一天(需要改程序),explain如下:
explain
SELECT * FROM table_a
WHERE
create_time BETWEEN '2021-03-04 17:10:00' AND '2021-03-05 17:10:00'
AND status = 1
AND MOD(id,8)=6
and deleted=0
id = 1
select_type = SIMPLE
table = table_a
partitions =
type = range
possible_keys = idx_create_time_status
key = idx_create_time_status
key_len = 8
ref =
rows = 28660
filtered = 1.00
Extra = Using index condition; Using where
果然走了索引,接下来继续优化:
查看程序发现,程序中只用到了table_a的4个字段,分别是create_time、status、deleted、column_a,而现有的索引是idx_create_time_status (create_time, status)
,如果把deleted、column_a这两个字段加到现有的联合索引后面索引是idx_create_time_status (create_time, status,deleted,column_a)
,查询的时候只查询这个4字段,就可以避免回表了,直接从索引键上获取数据,更改索引:
drop index idx_create_time_status on pl_finance_fee_extend;
create index idx_create_time_status
on pl_finance_fee_extend (create_time, status, deleted,column_a);
explain如下:
explain
SELECT create_time, status, deleted,column_a FROM table_a
WHERE
create_time BETWEEN '2021-03-04 17:10:00' AND '2021-03-05 17:10:00'
AND status = 1
AND MOD(id,8)=6
and deleted=0
id = 1
select_type = SIMPLE
table = table_a
partitions =
type = range
possible_keys = idx_create_time_status
key = idx_create_time_status
key_len = 8
ref =
rows = 28660
filtered = 10.00
Extra = Using where; Using index
可见与之前的差别是:
1、filtered从1.0变为10.0
2、Extra = Using index condition 变为Using index
对于差别1:
Filtered表示返回结果的行数占需读取行数的百分比 Filtered列的值越大越好
对于差别2:
using index condition:查找使用了索引,但是需要回表查询数据
using index :使用覆盖索引的时候就会出现
using index & using where:查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询数据
结果:
耗时从2.8s降到了80ms