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

附上MySQL之SQL分析三部曲地址http://blog.itpub.net/29510932/viewspace-1709732/

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

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

点击(此处)折叠或打开

select count(*)
from tom
inner toa on tom.oid = toa.oid
inner toi on tom.oid = toi.oid
left join fo on tom.stype = 2 and fo.oid = tom.oid
WHERE ( tom.ptime >= '2333-01-07 08:44:50.624' and tom.ptime = '2333-01-14 08:44:50.624' and tom.sid in ( 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 , 13 ) )

SQL分析三部曲之一：explain，结果如下图

这次的问题比第一个案例变麻烦了，因为explain中，优化器计算出来的rows并不高，然而事实是话费的执行时间是不可接受的
回头看看tom表的索引，发现优化器选择的是sid的索引，但是本身还存在另外一个的联合索引，那是不是和案例一一样，是索引选择错误？
那么explain看看

意料之外的结果........rows多得多，但是从常规来说，联合索引应该是会好一些的，所以实际跑跑效果试试

结果只能说是意料之中.....

那么问题来了，和案例一不同，优化器确实是选择了rows->cost比较少的执行计划，但是实际上，这个语句执行的时间和优化器计算的cost完全反过来了！

为什么？

SQL分析三部曲之二：profile，拖慢SQL的主要问题在于优化器计算出来的cost有问题，因此不必用profile来寻找时间消耗的主要目标

SQL分析三部曲之三：optimizer_trace，操作过程略，部分结果如图

方框所示的索引就是sid的索引，箭头所指的索引就是的联合索引，可以看到优化器认为两个索引都能用

看看这两个索引的cost计算

左边的是错误的索引的cost,右边是联合索引的cost,优化器确确实实是计算出来了cost,虽然这个cost和实际情况并不相符
箭头所指的地方就是这个答案啦~~~
原因也比较简单,因为优化器在计算cost的时候，也是参照一个固定的cost模型来计算的，而且有很多entries的具体值（行数，索引叶子数，IO等）都是估计值（案例一发生问题的地方）
同时，在计算cost的时候，优化器的计算方式，也是会根据MySQL本身的一些参数来改变的
比如说这个参数：index_dives_for_eq_ranges，主要会影响到等值计算，包括，但可能不限于逻辑运算=，in，或者其他（查看trace可以知道最终MySQL改写的语句）
当SQL语句中的等值计算的数量超过这个值的时候，trace中的这个值会变成false，这意味着MySQL的优化器认为计算这种执行计划的cost可能会花费较多的时间，从而采用一种更加高效，但是不精确的估计值计算方式
而在联合索引的计算当中，这个值变为了true，这意味这优化器在计算这种执行计划的时候，采用的是更加精确的cost计算方式，虽然计算的过程会多花点时间。（为什么联合索引就变成true？个人推测是联合索引排除了很多的无用数据，所以虽然in中的值比较多，但是本身数据基数少了，做精确计算并不会多花太多时间，因此使用了精确计算）
单独使用的索引时，由于in里面的值超过了设定的数量，所以采用了相对不精确的计算方式，得出了错误的cost
原因已经分析出来，现在去验证一下是不是这个原因，先看一下默认的值

in中的值超过了10个，预料之中，那么修改一下，看看新的explain和trace

可以看到执行计划已经变成了使用联合索引，rows的估计值也变成了30000多，那么抱着一丝好奇心，使用索引的真正的cost是多少？
看一下trace的内容

index_dives_for_eq_ranges的值已经变成了true，在精确的计算方式下，真正的rows和cost已经揭晓~~惨不忍睹........
choosen:true?不是true就不会计算cost了~
可以看到之前的判断是正确的，由于index_dives_for_eq_ranges相关的值的原因，导致MySQL在计算cost的时候，对不同索引采用了不同的策略。
完结撒花~~MySQL的优化器真是坑~~真是真的坑~over