1、Derived_merge簡介

成都創(chuàng)新互聯(lián)專業(yè)為企業(yè)提供昌吉網(wǎng)站建設(shè)、昌吉做網(wǎng)站、昌吉網(wǎng)站設(shè)計(jì)、昌吉網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計(jì)與制作、昌吉企業(yè)網(wǎng)站模板建站服務(wù)，十載昌吉做網(wǎng)站經(jīng)驗(yàn)，不只是建網(wǎng)站，更提供有價(jià)值的思路和整體網(wǎng)絡(luò)服務(wù)。

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.

其實(shí)derived_merge是系統(tǒng)變量optimizer_switch眾多參數(shù)中的一個(gè)參數(shù)選項(xiàng)，從5.7.6版本(包括5.7.6)開始支持，默認(rèn)值是derived_merge=on，用來控制優(yōu)化器是否合并衍生表或視圖的。

注意

本文實(shí)驗(yàn)的所有環(huán)境都是MySQL8.0.13;

derived_merge是MySQL5,6和MySQL5.7比較重要的一個(gè)區(qū)別，對SQL優(yōu)化很是重要，筆者曾遇到過相關(guān)案例，類似于本文第4部分案例。

2.Derived_merge示例

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

子查詢合并后等價(jià)于select * from  t_group;

設(shè)置derived_merge=on，從執(zhí)行計(jì)劃和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)

設(shè)置derived_merge=off，從執(zhí)行計(jì)劃和warnings中可以看到from后面的子查詢?nèi)匀皇仟?dú)立的一個(gè)子查詢，并沒有去掉括號(hào)被合并

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

因?yàn)閐erived_merge默認(rèn)是on,但是有時(shí)候我們又不想通過修改配置參數(shù)不讓子查詢合并，那么還有其他辦法嗎？當(dāng)然，可以通過在子查詢中添加關(guān)鍵字的方法：

聚合函數(shù) (SUM(), MIN(), MAX(), COUNT(), and so forth)

distinct

group by

having

limit

union or union all

使用變量符號(hào)@

例如：

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     |  | 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     |  | 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     |  | 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     |  | 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      |  | 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 |  | 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     |  | 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、如果子查詢數(shù)據(jù)量特別大，子查詢合并可以起到優(yōu)化SQL的效果，相當(dāng)于減少了子查詢執(zhí)行次數(shù)。

例如如下一條SQL,當(dāng)關(guān)閉子查詢合并功能的時(shí)候，SQL執(zhí)行需要9秒多，開啟子查合并功能后，時(shí)間為5秒，效率提高了1倍；

如果當(dāng)前使用的是MySQL5,6版本，不支持derived_merge功能，這個(gè)時(shí)候我們可以通過改寫SQL，減少子查詢的方法來提高SQL效率，這也是優(yōu)化SQL的一條思路。

沒有發(fā)生子查詢合并的SQL執(zhí)行情況：

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     |  | 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)

發(fā)生子查詢合并的SQL執(zhí)行情況：

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”.