mysql函数做条件_MySQL语句优化（三）：避免条件字段做函数操作

今天跟各位分享一个生产环境慢查询的例子，是一个比较典型的“条件字段使用了函数导致无法走索引”的例子。

一、定位慢查询

首先发现慢查询告警，通过运维平台看到慢查询主要是下面这条：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(*) 语句优化和数据导入优化等。