MySQL 对 derived table 的优化处理与使用限制

前言

随着 MySQL 版本的发展，优化器是越来越智能，优化器开关也越来越多，本文给大家分享一下 MySQL 对 derived table 的优化处理。

何为 derived table？这里我把它翻译成派生表，简单来讲，就是将 from 子句中出现的检索结果集当做一张表，比如 from 一个 select 构造的子查询，这个子查询就是一个派生表，from 一个视图，这个视图就是一个派生表，from 一个 with 构造的临时表（Common table expression，CTE），这个 CTE 表就是一个派生表。

一、优化器对 derived table 的优化

优化器处理 derived table 有两种策略：1.将 derived table 合并到外查询块中，2，将 derived table 物化为一个临时表。使用优化器开关 derived_merge 来控制优化器选择哪种策略。设置为 on，选择策略 1；设置为 off，选择策略 2。此开关从 5.7.6 版本时引入，默认值为 on。

8.0.22 版本中又引入优化器开关derived_condition_pushdown，默认值为 on，表示外查询块中与派生表相关的条件会推入到派生表中，设置为 off，则不会推入。

下面实验来说明，接下来本章节实验都是基于 MySQL8.0.25。

实验表结构

mysql> show create table t1\G*************************** 1. row ***************************       Table: t1Create Table: CREATE TABLE `t1` (  `id` int DEFAULT NULL,  `c1` varchar(100) DEFAULT NULL,  KEY `idx_id` (`id`),  KEY `idx_c1` (`c1`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)

实验语句：

select * from (select id,c1 from t1 ) dt where id=2;

（1）当前为两个开关都打开，此为默认值

mysql> set optimizer_switch="derived_merge=on,derived_condition_pushdown=on";Query OK, 0 rows affected (0.00 sec)

从执行计划可以看出，select_type 列没有 DERIVED 类型，说明派生表发生合并，相当于执行语句 select id,c1 from t1 where id=2合并后其实用不到下推了，下推关闭与否对执行计划没有影响。

（2）开启合并，关闭下推

' fill='%23FFFFFF'%3E%3Crect x='249' y='126' width='1' height='1'%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E)

可以看到对此语句而言，开启合并，不开启下推对执行计划没有影响。

（3）关闭合并，开启下推

mysql> set optimizer_switch="derived_merge=off,derived_condition_pushdown=on";Query OK, 0 rows affected (0.00 sec)

' fill='%23FFFFFF'%3E%3Crect x='249' y='126' width='1' height='1'%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E)

select_type 列出现 DERIVED 类型, 说明派生表没有合并，派生表会物化为临时表，但此时外查询块中的条件推入到了派生表中，相当于执行语句 select * from (select id,c1 from t1 where id=2) dt，对过滤后的数据进行物化，先过滤再物化，访问的数据量小，物化结果集小。

（4）两个开关都关闭

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

既不合并，又不下推，此时要对 t1 全表进行物化，再对物化结果集进行条件过滤。这种情况效率是最低的。

从上面的实验可以看出使用derived_merge, 可以避免不必要的物化，合并后，相当于将外查询块中的过滤条件直接推给了derived table。这样的执行计划更高效。那既然这样，下推的开关还有什么意义呢？

官方文档中有这样的说明：

When a derived table cannot be merged into the outer query (for example, if the derived table uses aggregation), pushing the outer WHERE condition down to the derived table should decrease the number of rows that need to be processed and thus speed up execution of the query.

也就是说合并有限制时，条件下推到派生表就起了作用，减少物化数据的行数，这样能加速查询的执行。

那我们接下来研究一下派生合并的限制吧

二、derived merge 的使用限制。

derived merge 有限制的时候，往往是derived_condition_pushdown发挥作用的时候，但是也有一些限制对这两者都有影响。

1.派生表中含有max(),min(),count(),sum()等聚合函数，或者含有DISTINCT，GROUP BY,HAVING这些分组子句， 此时不会发生合并，但是外层查询的条件会推入派生表。这一点是derived_condition_pushdown主要发挥作用的地方。

例子 1：外层查询对派生表中的聚合列做过滤，过滤条件会以 having 子句的形式推入到 derived table。

SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i, j) AS dt WHERE sum > 100;

优化器会将语句改写为：

SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i, j HAVING sum > 100) AS dt;

例子 2：外层查询对派生表的分组列做过滤，过滤条件会直接 推入派生表，减少 derived table 物化结果集的大小。

SELECT * FROM (SELECT i,j, SUM(k) AS sum FROM t1 GROUP BY i,j) AS dt WHERE i > 10 

优化器会将语句改写为：

SELECT * FROM (SELECT i,j, SUM(k) AS sum FROM t1 WHERE i > 10 GROUP BY i,j) AS dt;

2.派生表的 select 列表中有子查询，也就是标量子查询, 此时不会合并，但是条件会下推入派生表。

举例：

select *   from (select stuno,                course_no,                (select course_name                 from course c                where c.course_no = a.course_no) as course_name,                score          from score a) b       where b.stuno = 1;

' fill='%23FFFFFF'%3E%3Crect x='249' y='126' width='1' height='1'%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E)

上面这个例子中，因为派生表 b 的 select 列表中有标量子查询 (select course_name from course c where c.course_no =a.course_no) as course_name，所以派生表 b 被物化，但是条件 stuno = 1 推入到派生表。

3.分配了用户变量，这种情况不发生合并，但是条件会下推入派生表。

select (@i := @i + 1) as rownum, stuno, course_no, course_name, score       from ((select a.stuno, a.course_no, b.course_name, a.score                from score a                left join course b                  on a.course_no = b.course_no) dt, (select (@i := 0) num) c)      where stuno = 1;

上面这个例子使用用户变量的形式给记录加了行号，不能合并，但是可以将条件下推到派生表。

4.如果合并会导致外查询块中超过 61 张基表的连接访问，优化器会选择物化派生表。

这个其实不用关注，几乎没有语句对表的访问达到这个量级。

5.UNION 或 union all，这种情况不会发生合并，在 MySQL8.0.29 版本之后条件会下推。

select id, c1  from (select id, c1 from t1         union         select id, c1 from t2) dt where dt.id = 1;

8.0.25 版本：

' fill='%23FFFFFF'%3E%3Crect x='249' y='126' width='1' height='1'%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E)

8.0.32 版本：

' fill='%23FFFFFF'%3E%3Crect x='249' y='126' width='1' height='1'%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E)

6.对于视图而言，创建视图时如果指定了 ALGORITHM=TEMPTABLE，它会阻止合并，这个属性的优先级比优化器开关的优先级要高。

7.派生表中含 LIMIT 子句，这种情况既不会合并，也不会条件下推，因为合并或条件下推后会导致结果集改变。

select * from (select id,c1 from t1 limit 10) a where a. id=1;

8.只引用了字面量值，这种情况不发生合并。

select * from (select '1' as c1, 2 as c2 ) a;

对于前 4 种情况，合并被阻止，但是条件下推可以发挥作用，第 5 种 union 和 union all 的这种需使用 8.0.29 及之后的版本才会条件下推到派生表。6，7 既不能合并也不能下推，对于 8 这种情况，没有想到实际的应用场景。

三、derived_condition_pushdown 的使用限制

1.MySQL8.0.29 版本以前，派生表中使用了 union，条件不能推入派生表，MySQL8.0.29 及以后的版本没有此限制，前面已提及。

2.派生表中使用了 limit ，条件不能推入派生表，前面已提及。

3.条件包含子查询，不能推入到派生表，但是会发生合并

select stuno, course_no, course_name, score  from (select a.stuno, a.course_no, b.course_name, a.score           from score a           left join course b             on a.course_no = b.course_no) dt where stuno = (select distinct id from t1 where c1='gg张三')

4.条件是一个带参数的表达式，无法推入派生表。

5.派生表作为外连接的内层表(比如 left join 的右表)，条件无法推入到派生表，因为条件推入后，语句含义就变了。

6.从 MySQL8.0.28 版本开始，派生表的 select 列表中包含分配的用户变量，条件不能推入派生表。

7.如果物化的派生表是一个 Common Table Expression(CTE 表），也就是使用 with as 构造的表，如果这个表被引用多次，则条件不能推入到派生表。

8.如果派生表是一个视图，视图创建时使用了 ALGORITHM=TEMPTABLE，则条件不会推入进视图。

四、dervied_merge 使用注意事项

如果满足以下三个条件，优化器会将 derived table 中的 order by 子句延迟到合并后的查询中执行。

• (1)外层查询中没有分组或聚合运算

• (2)外层查询没有指定 distinct，having 或 order by。

• (3)外层查询中只有这个派生表作为 from 子句的唯一源。

这三个条件任何一项不满足，derived table 中的 order by 子句将会被忽略。

举个例子：

--学生成绩表create table score(stuno int,course_no varchar(10),score double(6,1),index idx_courseno(course_no),index idx_stuno(stuno)); insert into score values(1,'yw',97),(1,'sx',100),(1,'yy',70); insert into score values(2,'yw',90),(2,'sx',90),(2,'yy',80); insert into score values(3,'yw',89),(3,'sx',99),(3,'yy',90); insert into score values(4,'yw',98),(4,'sx',88),(4,'yy',87); --科目表 create table course(course_no varchar(10),course_name varchar(20),teacher varchar(20),note varchar(100),index idx_courseno(course_no)); insert into course values('yw','语文','Jenny','aaa'); insert into course values('sx','数学','Tony','bbb'); insert into course values('yy','英语','Richard','ccc');  --执行语句:查询学生语文学科的成绩，并按成绩倒序排列。mysql>  select a.stuno, b.course_name, a.score, b.teacher    ->    from (select stuno, course_no, score    ->            from score    ->           where course_no = 'yw'    ->           order by score desc) a    ->   inner join course b    ->      on a.course_no = b.course_no;+-------+-------------+-------+---------+| stuno | course_name | score | teacher |+-------+-------------+-------+---------+|     1 | 语文        |  97.0 | Jenny   ||     2 | 语文        |  90.0 | Jenny   ||     3 | 语文        |  89.0 | Jenny   ||     4 | 语文        |  98.0 | Jenny   |+-------+-------------+-------+---------+4 rows in set (0.00 sec)

虽然派生表中有对 score 列进行排序，但结果集中明显 score 列是乱序的，也就是 order by 子句被忽略了。之所以被忽略，就是没有满足第三个条件，外层查询 from 子句中不只有派生表 a，还有 course 表。

执行计划如下：执行计划中没有 derived table 表 a，说明发生了合并。

' fill='%23FFFFFF'%3E%3Crect x='249' y='126' width='1' height='1'%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E)

展示一下 warning 的信息。

mysql> show warnings;+-------+------+--------------------------------------------------------------------------------------------| Level | Code | Message                                                                                   |+-------+------+-------------------------------------------------------------------------------------------+| Note  | 1003 | /* select#1 */ select `test`.`score`.`stuno` AS `stuno`,`test`.`b`.`course_name` AS `course_name`,`test`.`score`.`score` AS `score`,`test`.`b`.`teacher` AS `teacher` from `test`.`score` join `test`.`course` `b` where ((`test`.`b`.`course_no` = 'yw') and (`test`.`score`.`course_no` = 'yw')) |+-------+------+-------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

合并后相当于执行语句：

select a.stuno,b.course_name,a.score,b.teacher from score a,course b where a.course_no='yw' and b.course_no='yw';

如果禁止 derived table 合并,order by 子句就不会被忽略，语句的结果就是正确的。

mysql> select /*+no_merge(a) */a.stuno, b.course_name, a.score, b.teacher    ->    from (select stuno, course_no, score    ->            from score    ->           where course_no = 'yw'    ->           order by score desc) a    ->   inner join course b    ->      on a.course_no = b.course_no;+-------+-------------+-------+---------+| stuno | course_name | score | teacher |+-------+-------------+-------+---------+|     4 | 语文        |  98.0 | Jenny   ||     1 | 语文        |  97.0 | Jenny   ||     2 | 语文        |  90.0 | Jenny   ||     3 | 语文        |  89.0 | Jenny   |+-------+-------------+-------+---------+4 rows in set (0.00 sec)

五、控制优化器是否使用 derived_merge 与 derived_condition_pushdown

抛开合并与条件下推的使用限制，MySQL 提供了优化器开关与 hint 两种方式来灵活控制是否使用 derived_merge 与derived_condition_pushdown

1.优化器开关。

 set optimizer_switch="derived_merge=on" 启用派生表合并；
 set optimizer_switch="derived_merge=off"禁用派生表合并。
 set optimizer_switch="derived_condition_pushdown=on" 启用条件推入派生表；
 set optimizer_switch="derived_condition_pushdown=off" 禁用条件推入派生表；

2.hint。hint 级别的控制优先级高于优化器开关。

/*+ MERGE(derived table name) */  启用派生表合并/*+ NO_MERGE(derived table name) */ 禁用派生表合并/*+ DERIVED_CONDITION_PUSHDOWN(derived table name) */  启用条件推入派生表/*+ NO_DERIVED_CONDITION_PUSHDOWN(derived table name) */  禁用条件推入派生表

六、总结

本文参照官方文档的介绍，以及 MySQL 不同版本做了大量的实验测试，将 derived table 的优化处理以及使用限制、注意事项做了一个详细介绍，希望为 SQL 开发者及优化人员带来一丝帮助。

Enjoy GreatSQL :)