------------------------------------------------------------正文------------------------------------------------------------
由于是生产环境下进行的,截图和SQL都隐去了一些信息
背景:有用户在抱怨生产系统上,某一个Web的页面太慢,忍无可忍
问题分析过程:略
问题聚焦:最终确定是某一个SQL语句太慢,查询时间用了4s(慢查询日志给出的信息)
SQL分析三部曲之一:explain,结果如下图
可以很明显的看到主要问题出在tom表上,使用了索引还有这么高的rows,从常规考虑来说,这个SQL使用了错误的索引
那么查看一下这个表上的索引,发现tom表上是存在联合索引的,显然,手动指定索引就可以了。
以解决问题为目的,就到上面就可以了,不过为了弄清楚 优化器没有选择使用这个联合索引,反而用了效率更低的其他的索引的原因,还需要看具体的优化器判断过程
SQL分析三部曲之二:profile,拖慢SQL的主要问题在于扫描了不必要的数据,因此不必用profile来寻找时间消耗的主要目标
SQL分析三部曲之三:optimizer_trace,操作过程略,部分结果如图
从下图可以看到,在判断where条件时,优化器选择了这个联合索引,同时计算出了rows和cost
接着往下看优化器的逻辑,在最后,由于SQL语句中有limit m,n的存在,优化器重新计算了使用这个索引的cost
接下来就是喜闻乐见的索引更换
选择另外一个索引是因为primary key和ptime的索引能够组成二级索引,而且ptime也出现在了where的条件当中,所以最终的结果,就变成了最前面explain的extra里面出现的Using index condition
在实际的测试和验证过程中,删掉limit语句以后,优化器就能正确的选择最优的索引, 也证明了limit m,n这个语句是导致优化器做出了错误判断的罪魁祸首~
优化器计算的cost出现了问题?MySQL的优化器一直以来背了无数黑锅(口碑烂 ),不过真正导致优化器做出错误选择的家伙,一般是 表的统计信息不完整/不全面/不正确........
最后附上正确的执行计划截图
![](https://img-blog.csdn.net/20151007175232722)
![](https://img-blog.csdn.net/20151007175240479)
rows已经降到了11~
PS:Using index condition,这是在在5.6之后新加入的特性,index condition pushdown,百度可以搜到很多介绍的文章,这里就略过了
由于是生产环境下进行的,截图和SQL都隐去了一些信息
背景:有用户在抱怨生产系统上,某一个Web的页面太慢,忍无可忍
问题分析过程:略
问题聚焦:最终确定是某一个SQL语句太慢,查询时间用了4s(慢查询日志给出的信息)
罪魁祸首的SQL语句:
select col1,col2......colN
from tom
inner join toa on tom.id = toa.id
left join tov on tom.id = tov.id
inner join toi on tom.id = toi.id
left join fo on tom.stype = 2 and fo.id = tom.id
WHERE ( tom.ostatus = 1 and tom.sid in ( 1 , 2 , 3) and tom.ptime >= '2333-01-01 09:41:58.056' and tom.ptime <= '2333-02-01 09:41:58.056' and tom.otype != 2 and toi.iid = '233333333333333' and tom.stype in ( 1 , 2 ) ) order by tom.ptime desc limit 20,20
SQL分析三部曲之一:explain,结果如下图
可以很明显的看到主要问题出在tom表上,使用了索引还有这么高的rows,从常规考虑来说,这个SQL使用了错误的索引
那么查看一下这个表上的索引,发现tom表上是存在联合索引的,显然,手动指定索引就可以了。
以解决问题为目的,就到上面就可以了,不过为了弄清楚 优化器没有选择使用这个联合索引,反而用了效率更低的其他的索引的原因,还需要看具体的优化器判断过程
SQL分析三部曲之二:profile,拖慢SQL的主要问题在于扫描了不必要的数据,因此不必用profile来寻找时间消耗的主要目标
SQL分析三部曲之三:optimizer_trace,操作过程略,部分结果如图
从下图可以看到,在判断where条件时,优化器选择了这个联合索引,同时计算出了rows和cost
接着往下看优化器的逻辑,在最后,由于SQL语句中有limit m,n的存在,优化器重新计算了使用这个索引的cost
接下来就是喜闻乐见的索引更换
选择另外一个索引是因为primary key和ptime的索引能够组成二级索引,而且ptime也出现在了where的条件当中,所以最终的结果,就变成了最前面explain的extra里面出现的Using index condition
在实际的测试和验证过程中,删掉limit语句以后,优化器就能正确的选择最优的索引, 也证明了limit m,n这个语句是导致优化器做出了错误判断的罪魁祸首~
优化器计算的cost出现了问题?MySQL的优化器一直以来背了无数黑锅(口碑烂 ),不过真正导致优化器做出错误选择的家伙,一般是 表的统计信息不完整/不全面/不正确........
最后附上正确的执行计划截图
rows已经降到了11~
PS:Using index condition,这是在在5.6之后新加入的特性,index condition pushdown,百度可以搜到很多介绍的文章,这里就略过了