MySQL 派生表合并优化的原理和实现

本文分享自华为云社区《【华为云MySQL技术专栏】MySQL 派生表合并优化的原理和实现》，作者：GaussDB 数据库。

MySQL是一种流行的开源关系型数据库管理系统，广泛应用于各种Web应用程序和企业系统中。随着数据量的增加和查询复杂度的提高，优化SQL查询性能变得至关重要。派生表（Derived Table）是SQL查询中常用的一种技术，通过在主查询中嵌套子查询来实现更复杂的数据处理。然而，派生表的使用有时会导致系统的性能瓶颈。

为了解决这一问题，MySQL引入了派生表合并优化（Derived Table Merging Optimization）。本文将详细介绍派生表合并优化的原理及在MySQL中的实现。

派生表是一个临时表，它是由子查询的结果集生成并在主查询中使用。简单来讲，就是将FROM子句中出现的检索结果集当成一张表，比如 FROM一个SELECT构造的子查询，这个子查询就是一个派生表；SELECT一个视图，这个视图就是一个派生表；SELECT一个WITH构造的临时表（Common table expression，CTE），这个CTE表就是一个派生表。如下图举例所示：

图1 子查询语句样例

MySQL优化器处理派生表有两种策略：

派生表合并优化的核心思想是将派生表的子查询合并到主查询中，从而避免生成临时表。具体来说就是，优化器会尝试将派生表的子查询展开，并直接嵌入到主查询的执行计划中。这样可以减少临时表的使用，降低内存和磁盘I/O的负担，从而提高查询性能。

下文将通过案例对派生表合并优化进行详细说明。

1．案例分析

创建如下两张表：

对于下述的查询语句：

关闭optimizer_switch（优化器开关）的derived_merge选项，对应的执行计划如下：

select_type列出现两行DERIVED类型, 说明派生表没有合并，派生表会物化为临时表。

执行EXPLAIN ANALYZE进一步分析，可知两个子查询都是物化为临时表后，再执行JOIN。

开启optimizer_switch（优化器开关）的derived_merge选项，对应的执行计划如下：

从执行计划可以看出，select_type列上只有一行为DERIVED类型，说明发生了派生表合并。

执行EXPLAIN ANALYZE进一步分析，employees表上的子查询仍然会被物化为临时表。departments表上的子查询（派生表）进行了合并优化，departments表直接与临时表t1进行JOIN。

对比derived_merge选项开启和关闭的两个执行计划可知，开启派生表合并优化特性后，departments表上的子查询（派生表）不再物化为临时表，而是合并到了父查询，进而简化了执行计划，并提高了执行效率。

另外，也可以发现，并不是所有派生表都可以合并优化，比如，案例中的employees表上的子查询（派生表），因为含有聚合函数，就无法进行合并优化。

2．应用场景限制

如下场景中派生表合并优化是无效的：

1）派生表中含有聚合函数，或者含有DISTINCT、GROUP BY、HAVING这些分组子句。比如，案例中的派生表t1包含了聚合函数和GROUP BY分组就无法合并优化。

2）派生表的SELECT列表中有子查询，也就是标量子查询。比如：

因为派生表b的select 列表中有标量子查询，无法合并，会被物化。

3）分配了用户变量。比如：

上面这个例子使用用户变量的形式给记录加了行号，不能合并。

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

5）UNION或UNION ALL。比如：

因为派生表dt有union操作，无法合并，会被物化。

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

7）派生表中含LIMIT子句，因为合并会导致结果集改变。比如：

8）只引用了字面量值。比如：

1.背景知识

我们使用的MySQL代码版本号为8.0.22。在介绍派生表代码实现之前，先了解下MySQL描述一条查询的逻辑语法树结构，有4个较为核心的类：

SELECT_LEX_UINT

对于一个query expression的描述结构，其中可以包含union/union all等多个query block的集合运算，同时SELECT_LEX_UNIT也根据query的结构形成递归包含关系。

SELECT_LEX

对于一个query block的描述结构，就是我们最为熟悉SPJ(选择Selection、投影Projection、连接Join) + group by + order by + select list… 这样的一个查询块，一个SELECT_LEX_UNIT中可能包含多个SELECT_LEX，而SELECT_LEX内部则也可能嵌套包含其他SELECT_LEX_UNIT。

Item

对于expression的描述结构，例如on条件、where条件、投影列等，都是用这个类来描述一个个表达式的，Item系统是MySQL SQL层代码中最为复杂的子系统之一，其构成了表达式树。

TABLE_LIST

TABLE_LIST类是MySQL查询处理的核心部分，涵盖了SQL表达式中的各种表类型。以案例中的SQL查询语句为例，在派生表合并优化前，其对应的类实例映射关系如下：

图2 派生表合并优化前的SQL语句

图3 派生表合并优化前的逻辑语法树

图2为SQL表达式，图3为MySQL处理后对应的逻辑语法树。图2颜色涵盖的SQL语句范围与图3相同颜色的类实例一一对应。比如，图2米黄色涵盖了整条SELECT语句（query block），也就对应着图3的SELECT_LEX1实例；图2最外层的浅灰色包含了米黄色区域，代表整条SQL语句（query expression），对应着图3的SELECT_LEX_UINT1实例（不涉及UNION操作，SELECT_LEX_UINT1只包含SELECT_LEX1，即一个SELECT_LEX实例）。

图2中用括号圈起来的部分，就是一个SELECT_LEX_UNIT，而每个SELECT toke开始的一个query block，就是一个SELECT_LEX，而在外层的SELECT_LEX中，会嵌套子查询，用一个SELECT_LEX_UNIT描述，子查询中可以是任意查询形态，再包含多个SELECT_LEX，从而形成SELECT_LEX_UNIT -> SELECT_LEX -> SELECT_LEX_UNIT -> SELECT_LEX … 这种相互嵌套的结构。

最外层的 query block（SELECT_LEX1）有两个派生表（t1、t2）。t1 和 t2 通过 derived 指针分别指子查询 query expression（SELECT_LEX_UINT3、SELECT_LEX_UINT2）。

2. 代码实现

MySQL主要在prepare阶段处理派生表的合并优化，详细的函数调用和处理过程如下：

案例中的SQL语句经过上面的派生表的合并优化处理后，其对应的映射关系如下：

图4 派生表合并优化后的SQL语句

图5 派生表合并优化后的逻辑语法树

对比合并优化前，有如下变化：（图4的SQL语句已基于图5的逻辑语法树等价变换）

1）派生表t2所指向的内部 query expression（SELECT_LEX_UINT2/SELECT_LEX2）已消除。

2）SELECT_LEX2中的物理表departments上移至外部query block（SELECT_LEX1）的JOIN运算中。

3）SELECT_LEX2中的WHERE条件合并到SELECT_LEX1。

4）SELECT_LEX1中针对派生表t2的投影，替换为物理表departments。

前文描述了MySQL派生表合并优化的具体实现，那么，如何从原理上证明该优化方法的正确性呢？可以尝试根据关系代数定理对其进行论证。

先简化场景，假设有两个表，一个是主查询（主表）R，一个是派生表D。在没有合并优化之前，查询可能是这样的形式：

不考虑具体实现的复杂性，让我们通过一个简单查询的例子来说明外层查询和派生表合并的效果。假设派生表D是从主表R通过选择操作产生的：D = σ条件2(R)，而外层查询又对D进行选择：σ条件1(D)。

根据关系代数的选择的叠加律（σa(σb(R)) = σa ∧ b(R)），可以合并这两个选择操作为一个操作，直接作用在主表R上：σ条件1 ∧ 条件2(R)。

这样，外层查询和派生表D就被合并成了一个直接对原始表R进行操作的查询，省去了创建和访问派生表D的开销。

对于更复杂的派生表，它们可能通过多个操作，如连接、投影和选择，从一个或多个表导出。针对这样的情况，基于关系代数的性质，比如选择的叠加律和交换律、投影的结合律等，通过相应的关系代数变换，所有这些操作的组合都可以被重写为直接作用于原始表上的一系列操作，也就证明了MySQL的这一优化方式是有效的。

本文从一个案例出发梳理了MySQL派生表合并优化的流程实现和优化原理，并对优化前后同一条SQL语句在代码层面的类实例映射关系进行了对比。MySQL派生表合并的代码实现细节比较多，篇幅有限，不再赘述，希望本文能够作为一个参考，帮助感兴趣的读者进一步研究这部分源码。

点击关注，第一时间了解华为云新鲜技术~

未经允许不得转载：大白鲨游戏网 » MySQL 派生表合并优化的原理和实现