8.2.2.4 通过合并或实现来优化派生 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_t1derived_t2在各自的查询中均被视为单独的 table。

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

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

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

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

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

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

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

如果优化器选择实现策略而不是合并派生 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;

如果最先进行优化的过程t1WHERE子句产生的结果为空,则联接必须一定为空,并且派生 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_t2f1列上构造索引。添加索引后,优化器可以将物化派生 table 与具有索引的常规 table 相同,并且它从生成的索引中也可以得到类似的好处。与没有索引的查询执行成本相比,索引创建的开销可以忽略不计。如果ref访问会比其他访问方法带来更高的成本,则优化器不会创建索引,也不会丢失任何内容。

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

首页