今天跟各位分享一个生产环境慢查询的例子,是一个比较典型的“条件字段使用了函数导致无法走索引”的例子。
一、定位慢查询
首先发现慢查询告警,通过运维平台看到慢查询主要是下面这条:SELECT tml_num_id, status_num_id FROM sd_bl_so_tml_hdr WHERE tenant_num_id = 6 AND data_sign = 0 AND sub_unit_num_id = 100004 AND channel_num_id = 91 AND date_format(order_date, '%Y%m%d') = date_format('2019-06-02', '%Y%m%d') AND status_num_id < 3 LIMIT 100;
平均执行时间26秒。
二、分析慢查询
首先查看执行计划:explain SELECT tml_num_id, status_num_id FROM sd_bl_so_tml_hdr WHERE tenant_num_id = 6 AND data_sign = 0 AND sub_unit_num_id = 100004 AND channel_num_id = 91 AND date_format(order_date, '%Y%m%d') = date_format('2019-06-02', '%Y%m%d') AND status_num_id < 3 LIMIT 100;
这里解释一下执行计划中几个关键列:key:实际使用的索引
key_len:索引长度
rows:预估扫描的行数
查看该表索引详情show index from sd_bl_so_tml_hdr;
图二 索引详情Cardinality表示索引中唯一值的数目
查看表结构desc sd_bl_so_tml_hdr;FieldTypeNullKeyDefaultExtra......
order_datedatetimeYESMULCURRENT_TIMESTAMP
status_num_idbigint(20)YESMUL0
sub_unit_num_idbigint(20)YESMUL0
......
分析从图一可以看出,SQL执行时使用的是ix_sd_bl_so_tml_hdr8索引,因为key_len=9,而status_num_id的字段类型是允许为空的bigint,所以status_num_id的key_len也为9,所以确定只走了联合索引ix_sd_bl_so_tml_hdr8(status_num_id,handover_id)的第一个字段(status_num_id)的索引,从row列看出扫描行数为10万行。
从图二可以看出,status_num_id的唯一值只是424,过滤数据不明显,导致扫描的行数过多。
最重要的一点是,条件中有order_date,并且order_date字段有索引,但是没走索引。列类型KEY_LEN备注id intkey_len = 4+1int为4bytes,允许为NULL,加1byte
id bigint not nullkey_len=8bigint为8bytes
id bigintkey_len=8+1允许为null,加1byte
user char(30) utf8key_len=30*3+1utf8每个字符为3bytes,允许为NULL,加1byte
user varchar(30) not null utf8key_len=30*3+2utf8每个字符为3bytes,变长数据类型,加2bytes
user varchar(30) utf8key_len=30*3+2+1utf8每个字符为3bytes,允许为NULL,加1byte,变长数据类型,加2bytes
cre_time datetimekey_len=8+1 (MySQL 5.6.4之前的版本) key_len=5+1(MySQL 5.6.4及之后的版本)允许为null,加1byte
原因
对条件索引字段做函数操作,可能会破坏索引值的有序性,因此优化器决定放弃走树搜索。
三、SQL优化
因为知道是因为对条件索引字段(order_date)做了函数操作,才导致无法走索引的,因此改写SQL成条件字段不做函数处理的形式。如下:SELECT tml_num_id, status_num_id FROM sd_bl_so_tml_hdr WHERE tenant_num_id = 6 AND data_sign = 0 AND sub_unit_num_id = 100004 AND channel_num_id = 91 AND order_date BETWEEN '2019-06-02 00:00:00' and '2019-06-02 23:59:59' AND status_num_id < 3 LIMIT 100;
查看执行计划explain SELECT tml_num_id, status_num_id FROM sd_bl_so_tml_hdr WHERE tenant_num_id = 6 AND data_sign = 0 AND sub_unit_num_id = 100004 AND channel_num_id = 91 AND order_date BETWEEN '2019-06-02 00:00:00' and '2019-06-02 23:59:59' AND status_num_id < 3 LIMIT 100;
可以看到使用了ix_sd_bl_so_tml_hdr7(sub_unit_num_id,order_date)的索引并且key_len为15,sub_unit_num_id为允许为null的bigint型,因此key_len为9,order_date为允许为null的datetime类型,因此key_len为6。所以执行计划中的key_len=sub_unit_num_id的key_len + order_date的key_len ;确定走了联合索引的两个字段的索引。
并且扫描行数为859行(原来为10万行),大大减少了扫描行数。
四、优化前后执行时间对比
因此在工作中应该尽量避免条件字段使用函数
更多 SQL 优化技巧请点击下方的“阅读原文”,里面包含了分页查询优化、join 语句优化、count(*) 语句优化和数据导入优化等。