【Mysql】derived table派生表

Learn from jdh-yfy

1 什么是derived table

派生表为直接或者间接的通过一个查询表达式从一个或者多个表中得到的表。某种意义上来讲，MySQL中的视图也是派生表，如图，这两条SQL语句表A即为派生表。

2. 什么是derived_merge

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

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

举一个实际的例子，比如对于
select * from （select * from table_name）t where id= 100;
外层查询的条件会推进到派生表的子查询中，实际的执行过程就变为：
select * from （select * from table_name where id =100）t where id= 100

3. derived_merge开启和关闭的区别

在MySQL5.6版本（无derived_merge）的执行情况如下图：

在MySQL5.7版本（有derived_merge且开启的）的执行情况如下图：

4. 无法利用derived_merge的情况

当派生子查询存在以下操作时，该特性无法生效。
DISTINCT、 GROUP BY、 UNION/UNION ALL 、 HAVING、 关联子查询、 LIMIT/OFFSET 以及 聚合操作 等

a. 存在DISTINCT时：

b. 存在group by 时：

c. 存在UNION/UNION ALL时：

d. 存在having时：

e. 存在limit时：