MySQL :: MySQL 8.4 Reference Manual :: 10.2.2.4 Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization

version 8.4

8.0 current
5.7

8.0 Japanese

MySQL 8.4 Reference Manual / ... / Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization

10.2.2.4 优化派生表、视图引用和公共表达式中的合并或物化

优化器可以使用两个策略来处理派生表引用（也适用于视图引用和公共表达式）：

将派生表合并到外部查询块中
将派生表物化为内部临时表

示例 1：

SELECT * FROM (SELECT * FROM t1) AS derived_t1;

在合并派生表derived_t1的情况下，查询执行方式类似于：

SELECT * FROM t1;

示例 2：

SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2 ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;

在合并派生表derived_t2的情况下，查询执行方式类似于：

SELECT t1.*, t2.f1
  FROM t1 JOIN t2 ON t1.f2=t2.f1
  WHERE t1.f1 > 0;

在物化时，derived_t1和derived_t2各自被视为它们所属查询中的单独表。

优化器对派生表、视图引用和公共表达式的处理方式相同：它尽量避免不必要的物化，从而使得推送外部查询条件到派生表中，并生成更高效的执行计划。 (例如，见第10.2.2.2节，“使用物化优化子查询”。)

如果合并将导致外部查询块引用超过 61 个基础表，优化器选择物化。

优化器在派生表或视图引用中传播一个ORDER BY子句，如果满足以下条件：

外部查询不被分组或聚合。
外部查询不指定DISTINCT、HAVING或ORDER BY。
外部查询只有这个派生表或视图引用作为FROM子句的唯一来源。

否则，优化器忽略ORDER BY子句。

影响优化器是否尝试合并派生表、视图引用和公共表达式的方法有：

可以使用MERGE和NO_MERGE优化器提示。它们假设没有其他规则阻止合并。见第10.9.3节，“Optimizer Hints”。
类似地，您也可以使用derived_mergeoptimizer_switch系统变量的第10.9.2节，“Switchable Optimizations”。默认情况下，标志启用以允许合并。禁用标志防止合并避免ER_UPDATE_TABLE_USED错误。

也可以应用于不包含ALGORITHM子句的视图。因此，如果对一个使用表达式等同于子查询的视图引用出现ER_UPDATE_TABLE_USED错误，添加ALGORITHM=TEMPTABLE到视图定义中可以防止合并，并且优先于derived_merge值。
可以通过在子查询中使用阻止合并的构造来禁用合并，虽然这些构造不如明确地影响物化。阻止合并的构造对于衍生表、通用表达式和视图引用是一样的：
- 聚合函数或窗口函数（SUM()，MIN()，MAX()，COUNT()等）
- DISTINCT
- GROUP BY
- HAVING
- LIMIT
- UNION或UNION ALL
- select 列表中的子查询
- 用户变量赋值
- 仅对字面值进行引用（在这个情况下，没有 underlying 表）

如果优化器选择了物化策略，而不是合并derived表，它将按照以下方式处理查询：

优化器推迟derived表的物化，直到执行查询时需要它的内容。这可以提高性能，因为延迟物化可能不需要进行物化。考虑一个连接derived表结果到另一个表的查询：如果优化器首先处理那个表并且发现它返回无行，那么就不需要继续执行join操作，并且可以完全跳过derived表的物化。
在查询执行时，优化器可能将索引添加到derived表以加速从中检索行。

考虑以下EXPLAIN语句，用于包含derived表的SELECT查询：

EXPLAIN SELECT * FROM (SELECT * FROM t1) AS derived_t1;

优化器避免物化derived表，直到执行SELECT时需要结果。在这个情况下，查询不被执行（因为它在EXPLAIN语句中），所以结果永远不需要。

即使是执行查询，延迟derived表物化也可能使优化器避免物化。发生这种情况时，查询执行速度更快，因为不需要进行物化。考虑以下连接derived表结果到另一个表的查询：

SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2
          ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;

如果优化器首先处理t1，并且WHERE子句产生了空结果，那么连接一定为空，衍生表不需要被物化。

对于衍生表需要物化的情况，优化器可能添加一个索引到物化表以加速访问。如果这个索引使得ref访问该表，可以大大减少查询执行期间读取数据的数量。考虑以下查询：

SELECT *
 FROM t1 JOIN (SELECT DISTINCT f1 FROM t2) AS derived_t2
         ON t1.f1=derived_t2.f1;

优化器在构建f1列索引从derived_t2中，如果这样做将使得使用ref访问的执行计划成本最低。添加索引后，优化器可以像对待常规表一样处理物化衍生表，并且从生成的索引中获益。索引创建的开销与查询执行无索引的成本相比是微不足道的。如果ref访问将导致更高的成本，而不是其他访问方法，优化器则不创建索引，什么也没损失。

对于衍生表物化也是对常规表达式（CTE）的真实。另外，以下考虑特别适用于CTE。

物化派生表的真理同样适用于通用表达式（CTE）。此外，以下考虑特别适用于CTE：

如果一个CTE被查询物化，它将在查询中只被物化一次，即使该查询引用它多次。

递归CTE总是被物化。

如果CTE被materialized，优化器自动添加相关索引，以提高顶层语句对CTE的访问速度。这与派生表的自动索引类似，但如果CTE被多次引用，优化器可能创建多个索引，以便每个引用方式最快。

可以将MERGE和NO_MERGE优化器提示应用于CTE。顶层语句中的每个CTE引用都可以有自己的提示，允许选择性地合并或materialize CTE引用。以下语句使用提示来指示cte1应该被合并，cte2应该被materialized：

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT /*+ MERGE(cte1) NO_MERGE(cte2) */ cte1.b, cte2.d
FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

对CREATE VIEW的WITH子句中的SELECT语句的ALGORITHM子句不影响materialization。

CREATE ALGORITHM={TEMPTABLE|MERGE} VIEW v1 AS WITH ... SELECT ...

只有SELECT语句的ALGORITHM值影响materialization，不影响WITH子句。

如前所述，如果CTE被materialized，哪怕多次引用，也只会materialized一次。 optimizer trace输出中包含一个creating_tmp_table和一个或多个reusing_tmp_table的出现。

CTE类似于派生表，后跟随着materialized_from_subquery节点。这对多次引用CTE也成立，因此没有materialized_from_subquery节点的重复（否则会假设子查询被执行多次，产生冗长的输出）。只有一个引用CTE拥有完整的materialized_from_subquery节点，其他引用CTE有简化的materialized_from_subquery节点。同样，对于EXPLAIN输出在TRADITIONAL格式下，其他引用不显示子查询。

PREV HOME UP NEXT