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

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

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

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

优化器以相同的方式处理派生表，视图引用和公用表表达式：尽可能避免不必要的实现，从而可以将条件从外部查询下推到派生表，并产生更有效的执行计划。（有关示例，请参见“通过实现来优化子查询”）。

如果合并将导致外部查询块引用超过61个基本表，则优化程序将选择实现。

ORDER BY如果满足以下所有条件，则优化器将派生子句在派生表或视图引用中传播到外部查询块：

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

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

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

• 该MERGE和NO_MERGE优化器提示可以使用。它们适用的前提是没有其他规则可以阻止合并。请参见“优化器提示”。

• 同样，您可以使用系统变量的derived_merge标志optimizer_switch。请参见“可切换的优化”。默认情况下，该标志启用以允许合并。禁用该标志可防止合并并避免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的访问，从而自动添加相关索引。这类似于派生表的自动索引，不同之处在于，如果多次引用CTE，优化器可能会创建多个索引，以最合适的方式加快每个引用的访问速度。

MERGE和NO_MERGE优化器提示可以应用到的CTE。顶层语句中的每个CTE引用都可以具有自己的提示，从而可以选择性地合并或实现CTE引用。以下语句使用提示来指示cte1应合并并cte2应实现：

WITH
  cte1 AS (SELECT  , 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.  = cte2.c;

ALGORITHM子句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格式输出：未显示其他引用的子查询。