MySQL之SQL分析三部曲实际案例

------------------------------------------------------------正文------------------------------------------------------------

由于是生产环境下进行的，截图和SQL都隐去了一些信息

背景：有用户在抱怨生产系统上，某一个Web的页面太慢，忍无可忍
问题分析过程：略
问题聚焦：最终确定是某一个SQL语句太慢，查询时间用了4s（慢查询日志给出的信息）

罪魁祸首的SQL语句：

select col1,col2......colNfrom 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.idWHERE ( 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，百度可以搜到很多介绍的文章，这里就略过了