MySQL之SQL分析三部曲实际案例（五）--临时表，优化器的选择

-------------------------------------------------------------------------------------------------正文---------------------------------------------------------------------------------------------------------------
问题出现于生产环境，为了方便表述，在测试环境构造了类似的场景

所有操作都是基于MySQL-5.6.26下进行的，并在MySQL-5.7.7-rc下进行了对比试验

背景：有用户在抱怨生产系统上，某一个Web的详情页面太慢，忍无可忍
问题分析过程：略
问题聚焦：最终确定是某一个SQL语句太慢，查询时间用了10s（慢查询日志给出的信息）
出现这种语句的原因：开发人员使用框架，利用模板统一生成的SQL

出问题的SQL语句（同类型构造，简化版，代码插入用不了，直接贴上来了_(:з」∠)_）
select t3.* 
from (select t1.*,t2.gname from students t1 inner join grade t2 on t1.sid=t2.f_sid) t3 
where t3.sid > 10005;
相关表的具体数据：
student

grade


分析：这个语句简化以后，优化策略很明显，把外围的select *去掉，然后t3的选择条件下推到里面的join里面去，其实就好了；
对应在优化器的语法优化策略中， 就是把投影条件(t3.*)和选择条件(t3.sid>10005)下推，一直推到对应的表上面，然后再把嵌套子查询上提，去掉最外层的select *；
但是为何实际上的SQL执行速度这么慢呢？看一下MySQL给出的执行计划

可以看到优化器并没有如想象中的方式去改写语句，那么看看trace里面的内容， 稍微观察一下优化器提供的一些信息
这里截取部分信息（对trace内容的分析仅限于个人的理解，如有偏差之处，希望能指出，学无止境 _(:з」∠)_ ）
在join的准备阶段，优化器把这个SQL解析成为了两个部分，最外层的查询：select #1，和内层的查询select #2

在join的优化阶段，优化器没有下推t3.sid > 10005的条件，而是作为了临时表t3的查询条件保留了下来，


从而使得这个查询以一个比较糟糕的方式在执行，
而根据之前的分析，稍微改写一下这个语句：
select t1.*,t2.gname
from students t1
inner join grade t2 on t1.sid=t2.f_sid 
where t1.sid > 10005;
然后看看实际的执行计划

最终的执行结果，和之前的语句也是一致的；

结论：所以如果生产库上出现这种语句，在使用5.6.26或者5.6.26之前的版本时，优化器并不会去改写这种类型语句，需要DBA或者开发自行改写。
如果使用了模板自动生成的话，会比较麻烦，需要和开发好好深入的沟通一下了....._(:з」∠)_

那么问题来了，MySQL的优化器真的这么烂，就没救了么？
一起看看“对优化器有多项重大优化和改进”的MySQL-5.7.7-rc的表现

从执行计划来看，在MySQL-5.7.7-rc中，优化器对这种语句进行了改写，那么看一下trace里面的内容
在最开始的join的准备阶段，优化器合并了t3

在join的优化阶段，最外围的t3条件已经下推到了t1表中


看样子MySQL-5.7.7-rc确实是对优化器做了一些改进~MySQL的5.7版本的还是值得期待的~
-------------------------------------------------------------------------------------------------结尾---------------------------------------------------------------------------------------------------------------
PS：只是一些个人的看法，模板和框架确实极大的简化了开发的工作，多人协作/代码review方面也确实便利了很多，但是感觉框架和模板终归还是定死了一些条条框框的东西，始终还是缺乏一些灵活性
也有可能只是我所见的开发并没有用好这些工具，也不一定

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29510932/viewspace-1818444/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29510932/viewspace-1818444/