子查询合并Derived_merge

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 `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 `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 `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的一些技巧

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

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

distinct

group by

having

limit

union or union all

使用变量符号@

例如：

mysql> desc select * from (select count(*) from t_group)as t1;
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
| 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  |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> desc select * from (select distinct * 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 | Using temporary |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
2 rows in set, 1 warning (0.00 sec)

mysql> desc select * from (select dept_no from t_group group by dept_no)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 | Using temporary |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
2 rows in set, 1 warning (0.00 sec)

mysql> desc select * from (select * from t_group having emp_no > 15000)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 union select * from t_order)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 |   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 | UNION RESULT | <union2,3> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
4 rows in set, 1 warning (0.00 sec)

mysql> desc select * from t_group t join (select @rn:=10001 emp_no)e on t.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 | Using where    |
|  2 | DERIVED     | NULL       | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
3 rows in set, 2 warnings (0.00 sec)

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

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

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

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

set optimizer_switch='derived_merge=off';

mysql> select
    -> total.emp_no,
    -> total.salary,
    -> total.to_date,
    -> total.last_name,
    -> total.hire_date
    -> from
    -> (select
    -> s.emp_no emp_no,
    -> s.salary salary,
    -> s.to_date to_date,
    -> e.last_name last_name,
    -> e.hire_date hire_date
    -> from salaries s
    -> inner join employees e where s.emp_no=e.emp_no) total;
2844047 rows in set (9.48 sec)

mysql> desc select
    -> total.emp_no,
    -> total.salary,
    -> total.to_date,
    -> total.last_name,
    -> total.hire_date
    -> from
    -> (select
    -> s.emp_no emp_no,
    -> s.salary salary,
    -> s.to_date to_date,
    -> e.last_name last_name,
    -> e.hire_date hire_date
    -> from salaries s
    -> inner join employees e where s.emp_no=e.emp_no limit 10000000) 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  |
+----+-------------+------------+------------+------+----------------+---------+---------+-----------------+---------+----------+-------+
3 rows in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message:  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 `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`) limit 10000000) `total`
1 row in set (0.00 sec)

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

mysql> set optimizer_switch='derived_merge=on';

mysql> select
    -> total.emp_no,
    -> total.salary,
    -> total.to_date,
    -> total.last_name,
    -> total.hire_date
    -> from
    -> (select
    -> s.emp_no emp_no,
    -> s.salary salary,
    -> s.to_date to_date,
    -> e.last_name last_name,
    -> e.hire_date hire_date
    -> from salaries s
    -> inner join employees e where s.emp_no=e.emp_no) total;
2844047 rows in set (5.03 sec)

mysql> desc select
    -> total.emp_no,
    -> total.salary,
    -> total.to_date,
    -> total.last_name,
    -> total.hire_date
    -> from
    -> (select
    -> s.emp_no emp_no,
    -> s.salary salary,
    -> s.to_date to_date,
    -> e.last_name last_name,
    -> e.hire_date hire_date
    -> from salaries s
    -> inner join employees e where s.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  |
+----+-------------+-------+------------+------+----------------+---------+---------+-----------------+--------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message:  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`)
1 row in set (0.00 sec)

参考链接

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