8.2.2.4 通过合并或实现优化派生表、视图引用和公用表表达式

MySQL 8.0 参考手册 / 第8章优化 / 8.2 优化SQL语句 / 8.2.2 优化子查询、派生表、视图引用和公用表表达式 / 8.2.2.4 通过合并或实现优化派生表、视图引用和公用表表达式

8.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在各自的查询中都被视为一个单独的表。

优化器以相同的方式处理派生表、视图引用和公用表表达式：它尽可能避免不必要的物化，这使得将条件从外部查询下推到派生表并生成更有效的执行计划。（有关示例，请参阅第 8.2.2.2 节，“使用物化优化子查询”。）

如果合并会导致引用超过 61 个基表的外部查询块，则优化器会选择具体化。

ORDER BY如果这些条件都为真，优化器将派生表或视图引用中的子句传播到外部查询块：

外部查询未分组或聚合。
外部查询未指定 DISTINCT、HAVING或 ORDER BY。
外部查询将此派生表或视图引用作为FROM子句中的唯一来源。

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

以下方法可用于影响优化器是否尝试将派生表、视图引用和公用表表达式合并到外部查询块中：

可以使用和优化器提示MERGE。 NO_MERGE他们假设没有其他规则阻止合并。请参阅第 8.9.3 节，“优化器提示”。
同样，您可以使用系统变量的derived_merge标志。optimizer_switch请参阅第 8.9.2 节，“可切换优化”。默认情况下，启用该标志以允许合并。禁用该标志可防止合并并避免 ER_UPDATE_TABLE_USED 错误。
该derived_merge标志也适用于不包含 ALGORITHM子句的视图。因此，如果 ER_UPDATE_TABLE_USED使用与子查询等效的表达式的视图引用发生错误，添加 ALGORITHM=TEMPTABLE到视图定义会阻止合并并优先于 derived_merge值。
可以通过在子查询中使用任何阻止合并的构造来禁用合并，尽管它们对实现的影响并不那么明确。防止合并的构造对于派生表、公用表表达式和视图引用是相同的：
- 聚合函数或窗口函数（SUM()、 MIN()、 MAX()、 COUNT()等）
- DISTINCT
- GROUP BY
- HAVING
- LIMIT
- UNION或者 UNION ALL
- 选择列表中的子查询
- 分配给用户变量
- 仅引用文字值（在这种情况下，没有基础表）

如果优化器选择物化策略而不是合并派生表，它会按如下方式处理查询：

优化器推迟派生表的具体化，直到在查询执行期间需要其内容。这提高了性能，因为延迟具体化可能导致根本不必这样做。考虑一个将派生表的结果连接到另一个表的查询：如果优化器首先处理另一个表并发现它没有返回任何行，则不需要进一步执行连接并且优化器可以完全跳过具体化派生表。
在查询执行期间，优化器可能会向派生表添加索引以加速从中检索行。

对于包含派生表的查询，请考虑以下EXPLAIN 语句：SELECT

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

优化器通过延迟派生表直到 SELECT执行期间需要结果来避免具体化派生表。在这种情况下，不会执行查询（因为它出现在 EXPLAIN语句中），因此永远不需要结果。

即使对于已执行的查询，派生表具体化的延迟也可能使优化器能够完全避免具体化。发生这种情况时，查询执行速度会比执行具体化所需的时间更快。考虑以下查询，它将派生表的结果连接到另一个表：

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被多次引用，优化器可能会创建多个索引，以最合适的方式加速每个引用的访问。

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

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;

ALGORITHMfor 子句 CREATE VIEW不影响视图定义中该语句之前的任何WITH 子句的具体化。SELECT考虑这个声明：

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

该ALGORITHM值仅影响的具体化SELECT，而不影响WITH子句。

在 MySQL 8.0.16 之前，如果 internal_tmp_disk_storage_engine=MYISAM，任何使用磁盘临时表实现 CTE 的尝试都会发生错误，因为对于 CTE，用于磁盘内部临时表的存储引擎不能是 MyISAM。从 MySQL 8.0.16 开始，这不再是问题，因为TempTable现在总是InnoDB用于磁盘内部临时表。

如前所述，CTE 如果具体化，即使被多次引用，也只会具体化一次。为了指示一次性具体化，优化器跟踪输出包含一次出现 creating_tmp_table加上一次或多次出现的reusing_tmp_table。

CTE 类似于派生表，其 materialized_from_subquery节点遵循引用。这对于被多次引用的 CTE 来说是正确的，因此没有 materialized_from_subquery节点重复（这会给人一种印象，即子查询被多次执行，并产生不必要的冗长输出）。只有一个对 CTE 的引用有一个完整的 materialized_from_subquery节点及其子查询计划的描述。其他参考文献有一个减少的materialized_from_subquery节点。同样的想法适用 EXPLAIN于格式输出 TRADITIONAL：未显示其他引用的子查询。