乐趣区

关于数据库:MySQL对derived-table的优化处理与使用限制

jiezi

前言

随着 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: t1
Create 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_ci
1 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 :)

Enjoy GreatSQL :)

## 对于 GreatSQL

GreatSQL 是由万里数据库保护的 MySQL 分支，专一于晋升 MGR 可靠性及性能，反对 InnoDB 并行查问个性，是实用于金融级利用的 MySQL 分支版本。

相干链接：GreatSQL 社区 Gitee GitHub Bilibili

GreatSQL 社区：

社区博客有奖征稿详情：https://greatsql.cn/thread-100-1-1.html

技术交换群：

微信：扫码增加 GreatSQL 社区助手 微信好友，发送验证信息 加群。

退出移动版