导读
关于SQL优化,作为一个数据库运维者,这是必须掌握的技能,对于我个人来说,为了提升自己,借助工作闲暇时间不得不去研究数据库优化方面的东西,以下通过一个简单的例子大体了解,如果遇到慢SQL该如何去优化,应该从哪几方面入手。
待优化场景
执行计划见下:
该表的几个索引见下:
SQL优化之路
首先,我们先review下这个SQL:
mysql> select id, user_id from rs
where id> 6350261
and mutual_plan_id= 1
and state= 10
and actived_at< '2017-04-21'
and mod(id, '16')= '5'
order by id asc limit 5000\G
从执行计划中 Extra 部分有 Using filesort ,而且rows 有将近100w,说明索引选用的选择率很不理想;并且这个SQL的最大性能瓶颈就是额外排序,并且条件中有函数不能用到索引。
其次,我们要考虑,为什么会出现这种问题呢 ?
我们从表结构中可以得出 id 为主键,WHERE 条件里含有 id 列,按理应该选择主键索引才对,但执行计划中并没选中它,因为条件中还包含了 mod(id, '16') = '5' ,导致优化器认为同时存在函数转换,没有优先选择主键索引。
第三步,既然我们找到原因了,那就改写下SQL,好让优化器能优先选择主键索引。
SQL改写的做法是:
-
改造成子查询;
-
将 mod(id, '16')= '5' 条件放在子查询外层;
-
在子查询里增加 distinct(大家猜一下distinct的作用是什么呢)。
第四步,我们再看下新的SQL及其执行计划:
mysql> select
a.id
from (
select distinct id from rs force index (primary )
where id> 6350261
and mutual_plan_id= 1
and state= 10
and actived_at< '2017-04-21'
)a where mod(id, '16')= '5'
limit 5000
执行计划如下:
可以看到,Using filesort 被消除了。
顺便说下,前面加上 distinct 的作用是为了防止视图合并。
最后,我们利用子查询 以及 LIMIT 1 将整个SQL 改写成如下:
mysql> select
b.id ,(select c.user_id from rs c where
c.id= b.id limit 1) user_id from (
select
a.id
from (
select distinct id from rs force index (primary )
where id> 6350261
and mutual_plan_id= 1
and state= 10
and actived_at< '2017-04-21'
)a where mod(id, '16')= '5'
limit 5000
) b
执行计划见下:
优化结果
SQL执行时间由修改之前的 12s 多下降到 6s 内。
因为 WHERE条件中有 MOD() 函数,没办法用到索引,所以提升效果不是太明显。
延伸方案
1、如果是用MySQL 5.7版本,可以选择创建虚拟列并加索引,类似下面的做法:
ALTER TABLE rs ADD modid INT(11) UNSIGNED GENERATED ALWAYS AS ( MOD(id, '16') ) NOT NULL,
ADD INDEX idx_modid(modid);
2、如果是MySQL 5.7以前的版本,则可以考虑把 mod() 函数转化成子查询,这个相对麻烦点,我们以后找机会再讨论。