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> set optimizer_switch='derived_merge=on'; mysql> desc select * from (select * from t_group)as t1; +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+ | 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 | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* 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` 1 row in set (0.00 sec)设置derived_merge=off，从执行计划和warnings中可以看到from后面的子查询仍然是独立的一个子查询，并没有去掉括号被合并
mysql> set optimizer_switch='derived_merge=off'; mysql> desc select * from (select * from t_group)as t1; +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+ | 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 | +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+ 2 rows in set, 1 warning (0.00 sec) mysql> desc select * from (select * from t_group)as t1; +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+ | 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 | +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+ 2 rows in set, 1 warning (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* 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` 1 row in set (0.00 sec)3、防止Derived_merge的一些技巧
郑重声明：本文版权归原作者所有，转载文章仅为传播更多信息之目的，如作者信息标记有误，请第一时间联系我们修改或删除，多谢。
