8.2.2.4 通过合并或实现来优化派生 table 和视图引用

优化器可以使用两种策略来处理派生 table 引用(这也适用于视图引用)：

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

Example 1:

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

通过合并派生 tablederived_t1，该查询的执行类似于：

SELECT * FROM t1;

Example 2:

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

通过合并派生 tablederived_t2，该查询的执行类似于：

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

通过实现，derived_t1和derived_t2在各自的查询中均被视为单独的 table。

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

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

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

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

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

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

假定没有其他规则阻止合并，则可以使用optimizer_switch系统变量的derived_merge标志。参见第 8.9.2 节“可切换的优化”。默认情况下，该标志启用以允许合并。禁用该标志可防止合并并避免ER_UPDATE_TABLE_USED错误。

derived_merge标志还适用于不包含ALGORITHM子句的视图。因此，如果使用与子查询等效的 table 达式的视图引用发生ER_UPDATE_TABLE_USED错误，则在视图定义中添加ALGORITHM=TEMPTABLE可以防止合并并优先于derived_merge值。

可以通过在子查询中使用任何阻止合并的构造来禁用合并，尽管这些构造对实现的影响不那么明显。防止合并的构造与派生 table 和视图引用相同：

聚合函数(SUM()，MIN()，MAX()，COUNT()等)
- DISTINCT
- GROUP BY
- HAVING
- LIMIT
- UNION或UNION ALL
- 选择列 table 中的子查询
- 分配给用户变量
- 仅引用 Literals 值(在这种情况下，没有基础 table)

derived_merge标志还适用于不包含ALGORITHM子句的视图。因此，如果使用与子查询等效的 table 达式的视图引用发生ER_UPDATE_TABLE_USED错误，则在视图定义中添加ALGORITHM=TEMPTABLE可以防止合并，并优先于当前derived_merge值。

如果优化器选择实现策略而不是合并派生 table，则它将按以下方式处理查询：

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

考虑以下EXPLAIN语句，用于包含派生 table 的SELECT查询：

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

优化程序通过将其推迟到SELECT执行期间需要结果之前来避免具体化派生 table。在这种情况下，查询不会执行(因为它发生在EXPLAIN语句中)，因此永远不需要结果。

即使对于已执行的查询，派生 table 实现的延迟也可以使优化程序完全避免实现。发生这种情况时，查询执行将比实现实现所需的时间更快。考虑以下查询，该查询将派生 table 的结果连接到另一个 table：

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

如果最先进行优化的过程t1和WHERE子句产生的结果为空，则联接必须一定为空，并且派生 table 不必实现。

对于派生 table 需要实现的情况，优化器可以向实现 table 添加索引以加快对其的访问。如果这样的索引启用对 table 的ref访问，则可以大大减少查询执行期间读取的数据量。考虑以下查询：

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

如果这样做可以使ref访问用于最低成本的执行计划，则优化器将在derived_t2的f1列上构造索引。添加索引后，优化器可以将物化派生 table 与具有索引的常规 table 相同，并且它从生成的索引中也可以得到类似的好处。与没有索引的查询执行成本相比，索引创建的开销可以忽略不计。如果ref访问会比其他访问方法带来更高的成本，则优化器不会创建索引，也不会丢失任何内容。

对于优化程序跟踪输出，未将合并的派生 table 或视图引用显示为节点。仅其基础 table 出现在顶部查询的计划中。

Name	MySql 中文文档
Version
Language
Badge
Last Updated	2021-07-03T12:24:35