1、Derived_merge简介

MySQL Reference manual是这么描述的:

The derived_merge flag controls whether the optimizer attempts to merge subqueries and views in the FROM clause into the outer query block, assuming that no other rule prevents merging. By default, the flag is on to enable merging. Setting the flag to off prevents merging.

其实derived_merge是系统变量optimizer_switch众多参数中的一个参数选项，从5.7.6版本(包括5.7.6)开始支持，默认值是derived_merge=on，用来控制优化器是否合并衍生表或视图的。

注意

本文实验的所有环境都是MySQL8.0.13;

derived_merge是MySQL5,6和MySQL5.7比较重要的一个区别，对SQL优化很是重要，笔者曾遇到过相关案例，类似于本文第4部分案例。

2.Derived_merge示例

select * from (select * from t_group)as t1;

子查询合并后等价于select * from t_group;

设置derived_merge=on，从执行计划和warnings中可以看到from后面的子查询被合并了，

mysql>setoptimizer_switch='derived_merge=on';mysql>descselect*from(select*fromt_group)ast1;+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+|1|SIMPLE|t_group|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|NULL|+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+1rowinset,1warning(0.00sec)mysql>showwarnings\G***************************1.row***************************Level:NoteCode:1003Message:/*select#1*/select`employees`.`t_group`.`emp_no`AS`emp_no`,`employees`.`t_group`.`dept_no`AS`dept_no`,`employees`.`t_group`.`from_date`AS`from_date`,`employees`.`t_group`.`to_date`AS`to_date`from`employees`.`t_group`1rowinset(0.00sec)

设置derived_merge=off，从执行计划和warnings中可以看到from后面的子查询仍然是独立的一个子查询，并没有去掉括号被合并

mysql>setoptimizer_switch='derived_merge=off';mysql>descselect*from(select*fromt_group)ast1;+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+|1|PRIMARY|<derived2>|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|NULL||2|DERIVED|t_group|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|NULL|+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+2rowsinset,1warning(0.00sec)mysql>descselect*from(select*fromt_group)ast1;+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+|1|PRIMARY|<derived2>|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|NULL||2|DERIVED|t_group|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|NULL|+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+2rowsinset,1warning(0.00sec)mysql>showwarnings\G***************************1.row***************************Level:NoteCode:1003Message:/*select#1*/select`t1`.`emp_no`AS`emp_no`,`t1`.`dept_no`AS`dept_no`,`t1`.`from_date`AS`from_date`,`t1`.`to_date`AS`to_date`from(/*select#2*/select`employees`.`t_group`.`emp_no`AS`emp_no`,`employees`.`t_group`.`dept_no`AS`dept_no`,`employees`.`t_group`.`from_date`AS`from_date`,`employees`.`t_group`.`to_date`AS`to_date`from`employees`.`t_group`)`t1`1rowinset(0.00sec)

3、防止Derived_merge的一些技巧

因为derived_merge默认是on,但是有时候我们又不想通过修改配置参数不让子查询合并，那么还有其他办法吗？当然，可以通过在子查询中添加关键字的方法：

聚合函数(SUM(), MIN(), MAX(), COUNT(), and so forth)

distinct

group by

having

limit

union or union all

使用变量符号@

例如：

mysql>descselect*from(selectcount(*)fromt_group)ast1;+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+|1|PRIMARY|<derived2>|NULL|system|NULL|NULL|NULL|NULL|1|100.00|NULL||2|DERIVED|t_group|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|NULL|+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+2rowsinset,1warning(0.00sec)mysql>descselect*from(selectdistinct*fromt_group)ast1;+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+|1|PRIMARY|<derived2>|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|NULL||2|DERIVED|t_group|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|Usingtemporary|+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+2rowsinset,1warning(0.00sec)mysql>descselect*from(selectdept_nofromt_groupgroupbydept_no)ast1;+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+|1|PRIMARY|<derived2>|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|NULL||2|DERIVED|t_group|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|Usingtemporary|+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+2rowsinset,1warning(0.00sec)mysql>descselect*from(select*fromt_grouphavingemp_no>15000)ast1;+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+|1|PRIMARY|<derived2>|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|NULL||2|DERIVED|t_group|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|NULL|+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+2rowsinset,1warning(0.00sec)mysql>descselect*from(select*fromt_groupunionselect*fromt_order)ast1;+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+|1|PRIMARY|<derived2>|NULL|ALL|NULL|NULL|NULL|NULL|20|100.00|NULL||2|DERIVED|t_group|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|NULL||3|UNION|t_order|NULL|ALL|NULL|NULL|NULL|NULL|10|100.00|NULL||NULL|UNIONRESULT|<union2,3>|NULL|ALL|NULL|NULL|NULL|NULL|NULL|NULL|Usingtemporary|+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+4rowsinset,1warning(0.00sec)mysql>descselect*fromt_grouptjoin(select@rn:=10001emp_no)eont.emp_no=e.emp_no;+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+|1|PRIMARY|<derived2>|NULL|system|NULL|NULL|NULL|NULL|1|100.00|NULL||1|PRIMARY|t|NULL|ALL|NULL|NULL|NULL|NULL|10|10.00|Usingwhere||2|DERIVED|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|NULL|Notablesused|+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+3rowsinset,2warnings(0.00sec)

4、如果子查询数据量特别大，子查询合并可以起到优化SQL的效果，相当于减少了子查询执行次数。

例如如下一条SQL,当关闭子查询合并功能的时候，SQL执行需要9秒多，开启子查合并功能后，时间为5秒，效率提高了1倍；

如果当前使用的是MySQL5,6版本，不支持derived_merge功能，这个时候我们可以通过改写SQL，减少子查询的方法来提高SQL效率，这也是优化SQL的一条思路。

没有发生子查询合并的SQL执行情况：

setoptimizer_switch='derived_merge=off';mysql>select->total.emp_no,->total.salary,->total.to_date,->total.last_name,->total.hire_date->from->(select->s.emp_noemp_no,->s.salarysalary,->s.to_dateto_date,->e.last_namelast_name,->e.hire_datehire_date->fromsalariess->innerjoinemployeesewheres.emp_no=e.emp_no)total;2844047rowsinset(9.48sec)mysql>descselect->total.emp_no,->total.salary,->total.to_date,->total.last_name,->total.hire_date->from->(select->s.emp_noemp_no,->s.salarysalary,->s.to_dateto_date,->e.last_namelast_name,->e.hire_datehire_date->fromsalariess->innerjoinemployeesewheres.emp_no=e.emp_nolimit10000000)total;+----+-------------+------------+------------+------+----------------+---------+---------+-----------------+---------+----------+-------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+------------+------------+------+----------------+---------+---------+-----------------+---------+----------+-------+|1|PRIMARY|<derived2>|NULL|ALL|NULL|NULL|NULL|NULL|2995588|100.00|NULL||2|DERIVED|e|NULL|ALL|PRIMARY|NULL|NULL|NULL|299512|100.00|NULL||2|DERIVED|s|NULL|ref|PRIMARY,emp_no|PRIMARY|4|testdb.e.emp_no|10|100.00|NULL|+----+-------------+------------+------------+------+----------------+---------+---------+-----------------+---------+----------+-------+3rowsinset,1warning(0.00sec)mysql>showwarnings\G***************************1.row***************************Level:NoteCode:1003Message:/*select#1*/select`total`.`emp_no`AS`emp_no`,`total`.`salary`AS`salary`,`total`.`to_date`AS`to_date`,`total`.`last_name`AS`last_name`,`total`.`hire_date`AS`hire_date`from(/*select#2*/select`testdb`.`s`.`emp_no`AS`emp_no`,`testdb`.`s`.`salary`AS`salary`,`testdb`.`s`.`to_date`AS`to_date`,`testdb`.`e`.`last_name`AS`last_name`,`testdb`.`e`.`hire_date`AS`hire_date`from`testdb`.`salaries``s`join`testdb`.`employees``e`where(`testdb`.`s`.`emp_no`=`testdb`.`e`.`emp_no`)limit10000000)`total`1rowinset(0.00sec)

发生子查询合并的SQL执行情况：

mysql>setoptimizer_switch='derived_merge=on';mysql>select->total.emp_no,->total.salary,->total.to_date,->total.last_name,->total.hire_date->from->(select->s.emp_noemp_no,->s.salarysalary,->s.to_dateto_date,->e.last_namelast_name,->e.hire_datehire_date->fromsalariess->innerjoinemployeesewheres.emp_no=e.emp_no)total;2844047rowsinset(5.03sec)mysql>descselect->total.emp_no,->total.salary,->total.to_date,->total.last_name,->total.hire_date->from->(select->s.emp_noemp_no,->s.salarysalary,->s.to_dateto_date,->e.last_namelast_name,->e.hire_datehire_date->fromsalariess->innerjoinemployeesewheres.emp_no=e.emp_no)total;+----+-------------+-------+------------+------+----------------+---------+---------+-----------------+--------+----------+-------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-------+------------+------+----------------+---------+---------+-----------------+--------+----------+-------+|1|SIMPLE|e|NULL|ALL|PRIMARY|NULL|NULL|NULL|299512|100.00|NULL||1|SIMPLE|s|NULL|ref|PRIMARY,emp_no|PRIMARY|4|testdb.e.emp_no|10|100.00|NULL|+----+-------------+-------+------------+------+----------------+---------+---------+-----------------+--------+----------+-------+2rowsinset,1warning(0.00sec)mysql>showwarnings\G***************************1.row***************************Level:NoteCode:1003Message:/*select#1*/select`testdb`.`s`.`emp_no`AS`emp_no`,`testdb`.`s`.`salary`AS`salary`,`testdb`.`s`.`to_date`AS`to_date`,`testdb`.`e`.`last_name`AS`last_name`,`testdb`.`e`.`hire_date`AS`hire_date`from`testdb`.`salaries``s`join`testdb`.`employees``e`where(`testdb`.`s`.`emp_no`=`testdb`.`e`.`emp_no`)1rowinset(0.00sec)

参考链接

Section 8.2.2.3, “Optimizing Derived Tables and View References”.