记一次mysql5.7的新特性derived_merge的坑

       前段时间公司由于拖工资离职，拖了整整三个月了呀火大有木有，题主果断辞职，不跟他多bb。当然辞职后不免又到了找工作刷 面试题和写简历的尴尬阶段，总觉得很多很多面试题吧在工作中卵用没有，还非要每天无脑地背，没办法谁叫面试官就好这口。 当然这只是吐槽哈，小伙伴们别跟我一样有这个想法，我就是有点aggressive，这段时间面试题呀还有面试呀学到了很多知识点是真正工作中不能比的。 但是还是好累好累 | _ |，生活还是下手了。

      好了咱们进入正题（屏幕前的你：谁让你bb这么多的），-_-# 。 最近有个面试题呢是这样，要求使用 group by 分组得出记录里面最大的数（注意是一整行，而不是只要一列，只要一列当然max聚合函数就搞定了），那么咱举一个栗子：ps：这个表格的设计，emmmmmm，当然真正设计表不会这样设计，会出现冗余，如此设计只是方便阅读，因为就一张表

id	stu_name	course_name	score
1	张三	数学	90
2	李四	语文	94
3	张三	语文	98
4	李四	数学	97
5	李四	英语	99
6	张三	英语	100
要求查询出各科分数最高的学生姓名，有的童鞋可能会这样写:

select stu_name,course_name,max(score) from tb_score group by course_name

      这样写就会导致一个问题也就是查询出来的各科最高分数可能不是那个学生的，结果如下：

      很明显数学得97分的压根就不是张三，这是为什么呢，group by 后的显示的列会只会根据所有组的第一行来显示，张三刚好在数学组的第一行，所以出来的是张三。

      当然有的童鞋知道了这个特性后知道该怎么做了，那就是 秘密武器 order by , 先排序，然后分数最高的肯定是所有组的第一行，那么 group by 后自然是取得这样行。ok， 按我们预定好的写一下这个sql（疯狂编写sql中… two hours later）：

SELECT
   t.stu_name,
   t.course_name,
   max( t.score )
FROM
   ( SELECT stu_name, course_name, score FROM score ORDER BY score DESC ) t
GROUP BY
   t.course_name

      然后发现结果还是这样

      what the fuck？？？什么鬼，然后去查看mysql执行日志：

      执行日志显示只有一个表的处理，不对呀，应该是两张表，先从from查询出一张表然后再从这张表筛选出一张新表，总共两张表才对。 因有惑，所追查， 在mysql5.6中，如果是这样写确实会出现两张表的处理，本人一顿操作将版本切换到5.6再执行如下：

      这时结果就完全正确了，执行日志显示出现了一个主表一个Derived表，根据资料显示，Derived为派生表，也就是说，from里面查询出的是派生表，也可以理解为临时表，先从score表格中查询的记录放到这个临时表，然后再从这个临时表进行分组，分组后的结果放入一张新表，就产生了正确结果。

      那么为什么切换了版本后就好了呢？ 其实mysql5.7针对于5.6版本做了一个优化，针对mysql本身的优化器增加了一个控制优化器的参数叫 derived_merge ，什么意思呢，“派生类合并”。

      什么意思呢，据mysql官方使用手册的说法：

      那这就谢逼了呀，这个跟我没写 from 嵌套子查询有什么区别。

      oh，我太难了。

      ok，既然已经了解了很多，原来是派生类合并在作怪，通过对mysql官方使用手册的了解，mysql5.7对 derived_merge 参数默认设置为on，也就是开启状态，我们在mysql5.7中把它关闭 shut downn 使用如下命令：

set session optimizer_switch=‘derived_merge=off’;
set global optimizer_switch=‘derived_merge=off’;

      这样如果from中查询出来的的结果就不会与外部查询块合并了，sql执行结果如下：

      其实修改 derived_merge 参数得谨慎而行之，因为mysql5.7版本有了这个优化的机制是有它的道理的，之所以去除派生类与外部块合并，是因为减少查询开销，派生类是个临时表，开辟一个临时表的同时还要维护和排序或者分组，都会影响效率，所以尽量不要去修改此参数。

      其实也有多种办法不需要修改 derived_merge 参数而使合并派生类失效，具体做法可参考官方使用手册， 摘抄手册文：

可以通过在子查询中使用任何阻止合并的构造来禁用合并，尽管这些构造对实现的影响并不明确。 防止合并的构造对于派生表和视图引用是相同的：
   1.聚合函数（ SUM() ， MIN() ， MAX() ， COUNT()等）
   2.DISTINCT
   3.GROUP BY
   4.HAVING
   5.LIMIT
   6.UNION或UNION ALL
   7.选择列表中的子查询
   8.分配给用户变量
   9.仅引用文字值（在这种情况下，没有基础表）

      另外说说上面的问题，在 derived_merge 是开启的时候，不知道有没有注意到既然from里面的子查询和不写子查询没有区别，因为mysql会把子查询跟外部查询块一起合并，上面的sql如下：

SELECT
   t.stu_name,
   t.course_name,
   max( t.score )
FROM
   ( SELECT stu_name, course_name, score FROM score ORDER BY score DESC ) t
GROUP BY
   t.course_name

      既然由于这个机制，里面的 order by 应该会跟外部块一起执行，也就是说 order by 会跑到外面来（说的形象一点哈），那么为什么结果的排序依旧是乱的：

      这里得查一下使用文档，上面是这样说的：

      上面的sql里的外部块由于使用到了分组，那么优化器会忽略掉 order by 子句

      这次就到这里，持续关注哦~~