MySQL 8.0 官方文档 第八章 优化（二十七）—— 使用合并或物化优化派生表、视图引用和公共表表达式

第八章 优化（二十七）—— 使用合并或物化优化派生表、视图引用和公共表表达式

8.2 优化SQL语句

8.2.2 优化子查询、派生表、视图引用和公共表表达式

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节 “使用物化优化子查询”）

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

如果以下条件都是真，那么，优化器会把派生表中的ORDER BY子句或视图引用传播到外部查询块中：

• 外部查询中没有分组或使用聚集函数。

• 外部查询中没有指定DISTINCT、HAVING或ORDER BY。

• 外部查询把此派生表或视图引用作为FROM子句唯一源。

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

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

• 可以使用MERGE（合并）和NO_MERGE（不合并）优化器提示。它们应用于假设没有其他规则会阻止合并的情形。参见 8.9.3节 “优化器提示”。

• 类似地，可以使用optimizer_switch系统变量的derived_merge（派生表合并）标志。参见 8.9.2节 “可切换优化”。此标志默认是启用的，因此允许使用合并。禁用此标志就会阻止合并和避免发生ER_UPDATE_TABLE_USED（使用更新表错误）错误。
  此derived_merge标志也适用于不包含ALGORITHM（算法）子句的视图。因此，如果在使用了一个表达式等于子查询的视图引用时，发生了ER_UPDATE_TABLE_USED错误，那么，把ALGORITHM=TEMPTABLE（算法=临时表）添加到视图的定义中以阻止合并，并且该算法的设置优于derived_merge的值。

• 通过在子查询中使用任何会阻止合并的结构来禁用合并是可能的，尽管它们没有像对物化的影响那么明显。会导致阻止合并的构造对于派生表、公共表表达式和视图引用是相同的：

  (1) 聚合函数或窗口函数（SUM(), MIN(), MAX(), COUNT()等）

  (2) DISTINCT

  (3) GROUP BY

  (4) HAVING

  (5) LIMIT

  (6) UNION或者 UNION ALL

  (7) 在选择列表中含有子查询

  (8) 给用户变量赋值

  (9) 只引用字面值（在这种情况下，没有基础表）

如果优化器为派生表选择的策略是物化，而不是合并，则它将以如下方式处理此查询：

• 优化器会推迟派生表的物化，直到在查询执行中需要它的内容为止。这将提升性能，因为推迟物化可能会导致根本不必实施它。考虑一个派生表的结果连接另一个表的查询情形：如果优化器先处理那个另外表，且发现它没有返回行，则无需进一步执行此连接，优化器就能完全跳过对该派生表的物化。

• 在查询执行期间，优化器可能会添加一个索引到派生表中以提升从中检索行的速度。

考虑以下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;

优化器可以在derived_t2表的列f1上创建一个索引，如果这样做能得到最低成本的执行计划来使用ref方法进行访问。添加此索引后，优化器就能把物化派生表当成带索引的常规表，并从生成的索引中获得类似的好处。与没有索引的查询执行成本相比，创建索引的开销是微不足道的。如果使用``ref```访问会导致比其他一些访问方式成本更高，那么优化器不会创建索引，也不会损失什么。

对于优化器跟踪输出，合并表或视图引用不会当成一个节点显示出来。只有在顶层查询计划中显示其基础表。

对派生表物化适用的，同样对公共表表达式（common table expression --> CTEs）也适用。另外，以下注意事项特别适用于公共表表达式。

如果一个公共表被一个查询物化，那么它只为该查询物化一次，即使该查询引用它多次。

但是，递归的公共表总是要物化的。

物化公共表表达式后，优化器就可以自动添加相关的索引，如果优化器估计此索引能加速顶层语句对该公共表表达式的访问，就创建它。这类似于派生表的自动化索引，只是，如果公共表表达式被引用多次，则优化器可能会创建多个索引，以最合适的方式加速每个引用的访问。

MERGE和NO_MERGE优化器提示也能用于公共表表达式。顶层语句中的每个公共表表达式都可以有自己的提示，以允许有选择性地合并或物化公共表表达式。以下的语句使用提示来指示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;

CREAVE VIEW的ALGORITHM子句不会影响在视图定义中SELECT语句前面的任何WITH子句的物化：

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

ALGORITHM的值只会影响SELECT的物化，不会对WITH子句有影响。

在MySQL 8.0.16之前，如果internal_tmp_disk_storage_engine=MYISAM（内部临时磁盘存储引擎=MYISAM），那么任何试图使用磁盘上的临时表来物化公共表表达式的尝试就会发生错误，这是因为对于公共表表达式而言，用于磁盘上的内部临时表的存储引擎不能是MyISAM。从MySQL8.0.16开始，这不再是一个问题，因为现在临时表总是将InnoDB用于磁盘上的内部临时表。

如前所述，公共表表达式，即使要被引用多次，如果要被物化，也只会物化一次。为了表示进行了一次物化，优化器跟踪输出中会包含一次creating_tmp_table（创建临时表）加上一次或多次reusing_tmp_table（重用临时表）。

公共表表达式类似于派生表，而派生表的materialized_from_subquery（物化自子查询）节点遵循引用。对于被多次引用的公共表表达式来说，这是正确的，因此不必对materialized_from_subquery节点进行复制（多次引用会产生子查询被执行多次的印象，且产生不必要冗长的输出）。对具有完整的materialized_from_subquery节点及其子查询计划描述的公共表表达式的引用只有一次。而其他引用有一个简化的materialized_from_subquery节点。同样的思想也适用于传统格式的EXPLAIN输出：其他引用的子查询不会显示。

上一集 MySQL 8.0 官方文档 第八章 优化（二十六）—— 使用EXISTS策略优化子查询

下一集 MySQL 8.0 官方文档 第八章 优化（二十八）—— 派生的条件下推优化