MySQL中SQL优化案例

导读

     关于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改写的做法是：

1. 改造成子查询；

2. 将 mod(id, '16')= '5'  条件放在子查询外层；

3. 在子查询里增加 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() 函数转化成子查询，这个相对麻烦点，我们以后找机会再讨论。