mysql OPTIMIZER特性之DERIVED_MERGE

1. 什么是DERIVED TABLE ？

derived table中文译为派生表，关于派生表的含义，翻阅了MySQL的官方手册，并没有找到相对应的解释，不过在SQL92标准中有对它进行定义，原文如下

A derived table is a table derived directly or indirectly from one
or more other tables by the evaluation of a <query expression>.
The values of a derived table are derived from the values of the
underlying tables when the <query expression> is evaluated.

解释为：派生表为直接或者间接的通过一个查询表达式从一个或者多个表中得到的表。某种意义上来讲，MySQL中的视图也是派生表。
举个例子：在如下SQL语句中，表A即为派生表。

select * from (select * from tb_1) AS A where A.col_1= 

2. 什么是DERIVED_MERGE ？

derived_merge指的是一种查询优化技术，作用就是把派生表合并到外部的查询中，提高数据检索的效率。这个特性在MySQL5.7版本中被引入，可以通过如下SQL语句进行查看/开启/关闭等操作。

• 查看是否开启

mysql> show global variables like '%optimizer_switch%'\G
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
1 row in set (0.00 sec)

derived_merge=on表示开启,如果是off的话表示关闭。

• 关闭derived_merge：

set session optimizer_switch='derived_merge=off'; //session
set global optimizer_switch='derived_merge=off'; //global

• 开启derived_merge：

set session optimizer_switch='derived_merge=on'; //session
set global optimizer_switch='derived_merge=on'; //global

3. DERIVED_MERGE开启和关闭的区别

在关闭或者无法使用derived_merge特性时，MySQL处理派生表需要将其物化成临时表，然后外部查询再对临时表进行检索操作。可见如下操作

• 在关闭derived_merge时：

mysql>
mysql> set session optimizer_switch='derived_merge=off';
Query OK, 0 rows affected (0.00 sec)

mysql> explain select * from (select * from t1 where id < 1000 ) AS A where A.id < 10;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |  999 |    33.33 | Using where |
|  2 | DERIVED     | t1         | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |  999 |   100.00 | Using where |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_1` int(11) DEFAULT NULL,
  `id_2` int(11) DEFAULT NULL,
  `id_3` int(11) DEFAULT NULL,
  `id_4` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  `m_status` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1783026 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

• 在开启derived_merge时

• mysql> explain select * from (select * from t1 where id < 1000 ) AS A where A.id < 10;
  +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  | id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
  +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  |  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    9 |   100.00 | Using where |
  +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
  1 row in set, 1 warning (0.01 sec)

• 可以发现在开启derived_merge时，少了对派生表的处理，这些处理包括对派生查询表达式的prepare,optimize,数据检索，物化，到使用完之后的清除等，所以查询效率提高了很多。
  其实更加明显的对比案例应该是派生表的查询无索引，而外部查询使用索引进行检索，这种情况下，利用到derived_merge和无法利用的效率会相差非常大。

但是发现，有时这个特性会导致查询变慢，我在生产环境遇到过，在关闭derived_merge时，sql秒返回结果，开启后，查询需要20分钟，所以，要谨慎使用。

以下是一些可能导致查询变慢的原因：

1. 数据量过大：如果查询中的数据量非常大，即使使用派生合并，查询也可能变得特别慢。在这种情况下，您可以尝试使用其他优化技术，如分区或索引来提高查询性能。

2. 查询的条件过于复杂：如果查询包含多个复杂的条件(如多表left join或者right join），优化器可能会选择错误的执行计划，导致查询变慢。在这种情况下，您可以尝试拆分查询或使用更简单的条件。

3. 查询的表结构过于复杂：如果查询包含多个连接和子查询，优化器可能会选择错误的执行计划，导致查询变慢。在这种情况下，您可以尝试简化查询或使用更简单的表结构。

如果您的查询使用了派生合并，并且查询变得特别慢，您可以尝试禁用派生合并来查看查询性能是否会改善。您可以在查询中添加以下语句来禁用派生合并：

SET optimizer_switch='derived_merge=off';

--本篇文章转自：optimizer特性之derived_merge - 灰信网（软件开发博客聚合）