MySQL5.7性能优化系列（二）——SQL语句优化（4）——使用合并、物化策略优化派生表、视图引用

时间：2021-02-04 06:05:18

优化器可以使用两种策略处理派生表（FROM子句中的子查询）引用：

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

优化器使用相同的策略来处理视图引用。

示例1：

SELECT * FROM (SELECT * FROM t1) AS 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;

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

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子句。

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

  • 可以使用optimizer_switch系统变量的derived_merge标志，假设没有其他规则阻止合并。默认情况下，该标志被允许合并。禁用该标志可防止合并，并避免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 or UNION ALL
    • 选择列表中的子查询
    • 分配给用户变量
    • 仅提供字面量值（在这种情况下，没有底层表）

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

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

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

请考虑以下EXPLAIN语句，子查询将显示在SELECT查询的FROM子句中：

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

优化器避免通过延迟实现子查询，直到在SELECT执行期间需要结果。在这种情况下，查询不会被执行（因为它发生在EXPLAIN语句中），所以结果是永远不需要的。

即使对于执行的查询，子查询实现的延迟也可能使优化器完全避免物化。发生这种情况时，执行实现所需的时间更快。考虑以下查询，它将FROM子句中的子查询的结果连接到另一个表：

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

如果优化过程t1首先和WHERE子句产生一个空的结果，连接必须是空的，并且子查询不需要物化。

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

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

优化器从derived_t2构造列f1上的索引，如果这样做可以使用最低成本执行计划的引用访问。添加索引后，优化器可以将物化派生表视为与具有索引的常规表相同，并从生成的索引中获益同样。与没有索引的查询执行成本相比，索引创建的开销是微不足道的。如果引用访问将导致比其他访问方法更高的成本，则优化程序不会创建索引并且不会丢失任何内容。

对于优化器跟踪输出，合并派生表或视图引用未显示为节点。顶层查询计划中只显示其基础表。

原文链接：https://dev.mysql.com/doc/refman/5.7/en/derived-table-optimization.html
标签：

相关文章