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

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页