子查詢合并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#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的一些技巧

因為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#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`) 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#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`)
1 row in set (0.00 sec)

參考鏈接

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