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 和视图引用相同:
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 出现在顶部查询的计划中。