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
MySQL之SQL分析三部曲实际案例(五)--临时表,优化器的选择
grade
MySQL之SQL分析三部曲实际案例(五)--临时表,优化器的选择

分析:这个语句简化以后,优化策略很明显,把外围的select *去掉,然后t3的选择条件下推到里面的join里面去,其实就好了;
对应在优化器的语法优化策略中, 就是把投影条件(t3.*)和选择条件(t3.sid>10005)下推,一直推到对应的表上面,然后再把嵌套子查询上提,去掉最外层的select *;
但是为何实际上的SQL执行速度这么慢呢?看一下MySQL给出的执行计划
MySQL之SQL分析三部曲实际案例(五)--临时表,优化器的选择
可以看到优化器并没有如想象中的方式去改写语句,那么看看trace里面的内容, 稍微观察一下优化器提供的一些信息
这里截取部分信息(对trace内容的分析仅限于个人的理解,如有偏差之处,希望能指出,学无止境 _(:з」∠)_ )
在join的准备阶段,优化器把这个SQL解析成为了两个部分,最外层的查询:select #1,和内层的查询select #2
MySQL之SQL分析三部曲实际案例(五)--临时表,优化器的选择
在join的优化阶段,优化器没有下推t3.sid > 10005的条件,而是作为了临时表t3的查询条件保留了下来,
MySQL之SQL分析三部曲实际案例(五)--临时表,优化器的选择
MySQL之SQL分析三部曲实际案例(五)--临时表,优化器的选择
从而使得这个查询以一个比较糟糕的方式在执行,
而根据之前的分析,稍微改写一下这个语句:
select t1.*,t2.gname
from students t1
inner join grade t2 on t1.sid=t2.f_sid 

where t1.sid > 10005;
然后看看实际的执行计划
MySQL之SQL分析三部曲实际案例(五)--临时表,优化器的选择
最终的执行结果,和之前的语句也是一致的;

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

那么问题来了,MySQL的优化器真的这么烂,就没救了么?
一起看看“对优化器有多项重大优化和改进”的MySQL-5.7.7-rc的表现
MySQL之SQL分析三部曲实际案例(五)--临时表,优化器的选择
从执行计划来看,在MySQL-5.7.7-rc中,优化器对这种语句进行了改写,那么看一下trace里面的内容
在最开始的join的准备阶段,优化器合并了t3
MySQL之SQL分析三部曲实际案例(五)--临时表,优化器的选择
在join的优化阶段,最外围的t3条件已经下推到了t1表中
MySQL之SQL分析三部曲实际案例(五)--临时表,优化器的选择

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


下一篇:verilog小程序——上电自动复位