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;
复制

分析

• 从图一可以看出，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字段有索引，但是没走索引。

原因

对条件索引字段做函数操作，可能会破坏索引值的有序性，因此优化器决定放弃走树搜索。

三、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(*) 语句优化和数据导入优化等。