Mysql的Derived派生表查询优化

原文链接
8.2.2.4 使用合并（Merging）或具体化（Materialization）优化派生表(Derived)和视图引用(View)
Mysql优化器可以使用以下两种策略来处理派生表引用（这也适用于视图引用）：

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

示例 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;

使用具体化(Materialization)，derived_t1和derived_t2分别都在各自的查询中被视为单独的表。

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

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

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

• 外部查询未分组或聚合。

• 外部查询未指定 DISTINCT、HAVING或 ORDER BY。

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

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

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

可以使用系统变量的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
  -选择列表中的子查询
• 分配给用户变量
• 仅引用文字值（在这种情况下，没有基础表）

该derived_merge标志也适用于不包含ALGORITHM 子句的视图。因此，如果 ER_UPDATE_TABLE_USED使用与子查询等效的表达式的视图引用发生错误，则添加ALGORITHM=TEMPTABLE 到视图定义会阻止合并并优先于当前 derived_merge值。

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

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

在查询执行期间，优化器可能会向派生表添加索引以加快从中检索行的速度。

对于包含派生表的查询， 请考虑以下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级别访问会导致比其他访问方法更高的成本，则优化器不会创建索引并且不会丢失任何内容。

对于优化器跟踪输出，合并的派生表或视图引用不显示为节点。只有它的基础表出现在顶部的查询计划中。

总结：如果派生表（子查询）具体化为临时表，该临时表数据量较大且优化器没有为该临时表创建索引，那么对该临时表进行查询会导致性能问题，所以尽量不要让子查询产生具体化的临时表（Derived），如果要产生临时表，也要严格控制临时表的数据量