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。
实验表结构
实验语句:
(1)当前为两个开关都打开,此为默认值
从执行计划可以看出,select_type 列没有 DERIVED 类型,说明派生表发生合并,相当于执行语句 select id,c1 from t1 where id=2
合并后其实用不到下推了,下推关闭与否对执行计划没有影响。
(2)开启合并,关闭下推
![图片](data:image/svg+xml,%3C%3Fxml version='1.0' encoding='UTF-8'%3F%3E%3Csvg width='1px' height='1px' viewBox='0 0 1 1' version='1.1' xmlns='http://www.w3.org/2000/svg' xmlns:xlink='http://www.w3.org/1999/xlink'%3E%3Ctitle%3E%3C/title%3E%3Cg stroke='none' stroke-width='1' fill='none' fill-rule='evenodd' fill-opacity='0'%3E%3Cg transform='translate(-249.000000, -126.000000)' 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)关闭合并,开启下推
![图片](data:image/svg+xml,%3C%3Fxml version='1.0' encoding='UTF-8'%3F%3E%3Csvg width='1px' height='1px' viewBox='0 0 1 1' version='1.1' xmlns='http://www.w3.org/2000/svg' xmlns:xlink='http://www.w3.org/1999/xlink'%3E%3Ctitle%3E%3C/title%3E%3Cg stroke='none' stroke-width='1' fill='none' fill-rule='evenodd' fill-opacity='0'%3E%3Cg transform='translate(-249.000000, -126.000000)' 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)两个开关都关闭
既不合并,又不下推,此时要对 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。
优化器会将语句改写为:
例子 2:外层查询对派生表的分组列做过滤,过滤条件会直接 推入派生表,减少 derived table 物化结果集的大小。
优化器会将语句改写为:
2.派生表的 select 列表中有子查询,也就是标量子查询, 此时不会合并,但是条件会下推入派生表。
举例:
![图片](data:image/svg+xml,%3C%3Fxml version='1.0' encoding='UTF-8'%3F%3E%3Csvg width='1px' height='1px' viewBox='0 0 1 1' version='1.1' xmlns='http://www.w3.org/2000/svg' xmlns:xlink='http://www.w3.org/1999/xlink'%3E%3Ctitle%3E%3C/title%3E%3Cg stroke='none' stroke-width='1' fill='none' fill-rule='evenodd' fill-opacity='0'%3E%3Cg transform='translate(-249.000000, -126.000000)' 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.分配了用户变量,这种情况不发生合并,但是条件会下推入派生表。
上面这个例子使用用户变量的形式给记录加了行号,不能合并,但是可以将条件下推到派生表。
4.如果合并会导致外查询块中超过 61 张基表的连接访问,优化器会选择物化派生表。
这个其实不用关注,几乎没有语句对表的访问达到这个量级。
5.UNION 或 union all,这种情况不会发生合并,在 MySQL8.0.29 版本之后条件会下推。
8.0.25 版本:
![图片](data:image/svg+xml,%3C%3Fxml version='1.0' encoding='UTF-8'%3F%3E%3Csvg width='1px' height='1px' viewBox='0 0 1 1' version='1.1' xmlns='http://www.w3.org/2000/svg' xmlns:xlink='http://www.w3.org/1999/xlink'%3E%3Ctitle%3E%3C/title%3E%3Cg stroke='none' stroke-width='1' fill='none' fill-rule='evenodd' fill-opacity='0'%3E%3Cg transform='translate(-249.000000, -126.000000)' 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 版本:
![图片](data:image/svg+xml,%3C%3Fxml version='1.0' encoding='UTF-8'%3F%3E%3Csvg width='1px' height='1px' viewBox='0 0 1 1' version='1.1' xmlns='http://www.w3.org/2000/svg' xmlns:xlink='http://www.w3.org/1999/xlink'%3E%3Ctitle%3E%3C/title%3E%3Cg stroke='none' stroke-width='1' fill='none' fill-rule='evenodd' fill-opacity='0'%3E%3Cg transform='translate(-249.000000, -126.000000)' 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 子句,这种情况既不会合并,也不会条件下推,因为合并或条件下推后会导致结果集改变。
8.只引用了字面量值,这种情况不发生合并。
对于前 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.条件包含子查询,不能推入到派生表,但是会发生合并
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 子句将会被忽略。
举个例子:
虽然派生表中有对 score 列进行排序,但结果集中明显 score 列是乱序的,也就是 order by 子句被忽略了。之所以被忽略,就是没有满足第三个条件,外层查询 from 子句中不只有派生表 a,还有 course 表。
执行计划如下:执行计划中没有 derived table 表 a,说明发生了合并。
![图片](data:image/svg+xml,%3C%3Fxml version='1.0' encoding='UTF-8'%3F%3E%3Csvg width='1px' height='1px' viewBox='0 0 1 1' version='1.1' xmlns='http://www.w3.org/2000/svg' xmlns:xlink='http://www.w3.org/1999/xlink'%3E%3Ctitle%3E%3C/title%3E%3Cg stroke='none' stroke-width='1' fill='none' fill-rule='evenodd' fill-opacity='0'%3E%3Cg transform='translate(-249.000000, -126.000000)' 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 的信息。
合并后相当于执行语句:
如果禁止 derived table 合并,order by 子句就不会被忽略,语句的结果就是正确的。
五、控制优化器是否使用 derived_merge 与 derived_condition_pushdown
抛开合并与条件下推的使用限制,MySQL 提供了优化器开关与 hint 两种方式来灵活控制是否使用 derived_merge
与derived_condition_pushdown
1.优化器开关。
2.hint。hint 级别的控制优先级高于优化器开关。
六、总结
本文参照官方文档的介绍,以及 MySQL 不同版本做了大量的实验测试,将 derived table 的优化处理以及使用限制、注意事项做了一个详细介绍,希望为 SQL 开发者及优化人员带来一丝帮助。
Enjoy GreatSQL :)
评论