MySQL–SQL优化案例

问题现象

遇到的一个case,case发现某一个用户的某个操作总是超时,排查发现超时的原因是因为sql超时。sql大概长这样:select * from 表 where userid =123 and sign='T' and type='M' order by id desc limit 0,20,执行发现这个sql执行并返回竟然需要四十多秒。

问题分析

为什么一个sql会突然变得这么慢呢,其实第一个想到的就是索引的问题。我随机换了几个其他的userid发现并没有这种问题,更神奇的是当我去掉where或order by或limit中的任意一个的时候,sql就会恢复正常,执行时间都在毫秒级。

错误选择执行计划

查看表结构可以发现该表有两个索引一个是sql的where条件上有联合索引,另一个是id的主键索引。表总行数大概2000w行左右,用explain发现快的userid值的sql走的都是where的联合索引,这条慢的(userid值)走得是主键索引。

那么基本可以判断出,userid对应值的这条sql,优化器错误的选择了主键索引。

选主键索引原因分析

MySQL--SQL优化案例(1)优化器放弃索引,全表扫

为什么优化器会错误选择索引,首先想到的是因为where,因为mysql会根据where利用索引要先读索引文件,二分查找找到对应数据的数据磁盘指针,再根据读到的指针再读磁盘上对应的数据数据,计算出影响结果集。当这个结果集大于一定的比例时mysql会放弃这个索引。放弃的原因是因为在非命中覆盖索引的情况下,因为走索引是需要回表的,mysql认为这个成本很大故放弃了这个索引。这个比例经过计算,当结果集超过总数的15%~30%,就不会走这个索引了。但是我看了一下这条慢的sql,其实这条慢的sql远远没有达到这个比例。那为什么走了主键索引呢?而且走就走了为什么会这么慢呢?

(2)排序分析

接下来注意到order by。order by也是一个成本很大的操作,而这条sql恰恰用到了id来排序,这把mysql高兴坏了,因为主键索引本身就是有序的。mysql通过遍历B+树叶子结点即可完成排序,从左往右遍历即是正序,反之倒序(如下图所示)。当然,光凭这一点就让mysql选择走主键索引还是不够的。

(3)limit 分析

最后再来看看limit,order by+limit有时会改变原有sql的执行顺序,当limit所占比例很小的时候,举个例子比如limit20,mysql会认为我找符合20条的太容易了,于是他先选择先order by后判断where,就是直接遍历主键索引的叶子节点。每遍历一条数据判断是否满足where的条件,如果满足就计数,直到达到我们要的20条。所以在这种逻辑下,我们任意砍掉一些where条件,sql就会变快,因为匹配得要求降低了,可以更早的匹配到20个了。所以我去掉一个where条件,走得索引没有变化,但是sql变快很多就是因为这个。

说了以上的逻辑,那为什么我换几个userid并没有慢sql的问题呢?还是刚刚那个limit和where的问题,刚刚说了当limit的大小和利用where查索引文件的结果集的大小对比,limit所占的比例非常小的时候(limit相比where条件量)mysql才会像我如上所述那样决定索引决定执行顺序(走主键索引)。后续我用该userid和where条件查了下该userid该条件下在该表的返回总条数,果然这个userid返回的count是其他userid的100倍~1000倍,所以才导致了只有该userid执行此sql非常慢的情况。

问题总结

解决这个问题的思路也很简单,就是命令这个sql走where的联合索引。强制走索引的方式一种可以利用force index语句来指定想走的索引。另外,根据索引的特性,由于参与运算的列是不会走索引的,我们可以在order by id这里改成order by id+0 这样也可以解决我们的问题。

where 索引列 order by 主键+limit 很有可能导致优化器错误的选择主键索引而全表扫描。

发表评论

相关文章