MySQL 8.0参考手册::8.2.2.4用合并或物化优化派生表、视图引用和通用表表达式

10bet网址

版本8.0

5.7
5.6

8.0日本

MySQL 8.0参考手册/.．./ 用合并或物化优化派生表、视图引用和公共表表达式

8.2.2.4通过合并或物化优化派生表、视图引用和公共表表达式

优化器可以使用两种策略处理派生表引用(也适用于视图引用和公共表表达式):

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

示例1:

SELECT * FROM (SELECT * FROM t1) AS derived_t1

合并派生表derived_t1，该查询执行类似于:

SELECT * FROM t1;

示例2:

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

合并派生表derived_t2，该查询执行类似于:

选择t1。*, t2。f1从t1JOIN t2 ON t1.f2=t2.f1 WHERE t1.f1 > 0;

实体化,derived_t1而且derived_t2都在各自的查询中作为单独的表处理。

优化器以同样的方式处理派生表、视图引用和公共表表达式:它尽可能避免不必要的物化，这允许将外部查询的条件下推到派生表，并产生更高效的执行计划。(示例请参见第8.2.2.2节，“使用物化优化子查询”.）

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

优化器传播一个命令子句引用外部查询块，如果这些条件都为真:

外部查询没有分组或聚合。
外部查询不指定截然不同的，有,或命令．
类中的惟一源是外部查询的派生表或视图引用从条款。

否则，优化器将忽略命令条款。

以下方法可以影响优化器是否试图将派生表、视图引用和公共表表达式合并到外部查询块:

的合并而且NO_MERGE可以使用优化器提示。它们适用的前提是没有其他规则阻止合并。看到第8.9.3节，“优化器提示”．
类似地，您可以使用derived_merge国旗的optimizer_switch系统变量。看到第8.9.2节“可切换优化”．默认情况下，启用标志允许合并。禁用标志可以防止合并和避免ER_UPDATE_TABLE_USED错误。
的derived_mergeFlag也适用于包含no的视图算法条款。因此,如果一个ER_UPDATE_TABLE_USED如果视图引用使用与子查询等价的表达式，则会发生错误算法=易被诱惑的的定义阻止合并，并优先于derived_merge价值。
可以通过在子查询中使用任何阻止合并的构造来禁用合并，尽管这些构造对物化的影响并不明显。防止合并的构造对于派生表、公共表表达式和视图引用是一样的:
- 聚合函数或窗口函数(SUM ()，MIN ()，MAX ()，COUNT ()，等等)
- 截然不同的
- 集团
- 有
- 限制
- 联盟或UNION ALL
- 选择列表中的子查询
- 对用户变量的赋值
- 只引用文字值(在本例中，没有底层表)

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

优化器推迟派生表的物化，直到查询执行期间需要它的内容。这可以提高性能，因为延迟实体化可能导致根本不需要做它。考虑一个将派生表的结果连接到另一个表的查询:如果优化器首先处理另一个表，发现它没有返回任何行，则无需进一步执行连接，优化器可以完全跳过派生表的物化。
在查询执行期间，优化器可以向派生表添加索引，以加快从中提取行的速度。

考虑以下解释声明,选择包含派生表的查询:

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

优化器通过将派生表延迟到期间需要的结果来避免物化派生表选择执行。在这种情况下，查询不会被执行(因为它发生在解释语句)，因此不需要该结果。

即使对于已执行的查询，派生表物化的延迟也可能使优化器完全避免物化。当这种情况发生时，执行物化所需的时间将加快查询执行的速度。考虑下面的查询，它将一个派生表的结果连接到另一个表:

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

如果优化过程t1第一次和在哪里子句产生空结果，连接必须为空，派生表不需要物化。

对于派生表需要物化的情况，优化器可以向物化表添加索引，以加快对它的访问。如果这样的索引启用裁判访问表，它可以大大减少查询执行期间读取的数据量。考虑以下查询:

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

优化器在列上构造索引f1从derived_t2如果这样做可以使用裁判获得最低成本的执行计划。在添加索引之后，优化器可以将物化的派生表视为具有索引的常规表，它也可以从生成的索引中获得类似的好处。与没有索引的查询执行成本相比，创建索引的开销可以忽略不计。如果裁判访问将导致比其他一些访问方法更高的成本，优化器不会创建索引，也不会丢失任何东西。

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

派生表的物化适用于公共表表达式(CTEs)。此外，以下的考虑特别适用于cte。

如果CTE由查询物化，则该CTE仅为查询物化一次，即使查询多次引用它。

递归的CTE总是物化的。

如果一个CTE被物化，如果优化器估计索引可以加快顶级语句对CTE的访问，它就会自动添加相关的索引。这类似于派生表的自动索引，不同的是，如果CTE被多次引用，优化器可能会创建多个索引，以最适当的方式加快每个引用的访问速度。

的合并而且NO_MERGE优化器提示可以应用到cte。顶级语句中的每个CTE引用都可以有自己的提示，允许有选择地合并或物化CTE引用。下面的语句使用提示来表示cte1应该合并cte2应该实现:

WITH cte1 AS (SELECT a, b FROM table1)， cte2 AS (SELECT c, d FROM table2) SELECT /*+ MERGE(cte1) NO_MERGE(cte2) */ cte1。b, cte2。JOIN cte2 WHERE cte1。一个= cte2.c;

的算法条款创建视图不影响物化为什么与条款前选择语句。考虑一下这句话:

CREATE ALGORITHM={TEMPTABLE|MERGE} VIEW v1 AS WITH…选择……

的算法价值只影响物化选择,而不是与条款。

在MySQL 8.0.16之前，如果internal_tmp_disk_storage_engine = MYISAM，对于任何使用磁盘上临时表物化CTE的尝试都会发生错误，因为对于CTE，用于磁盘上内部临时表的存储引擎无法物化MyISAM．从MySQL 8.0.16开始，这不再是一个问题，因为易被诱惑的现在总是使用InnoDB对于磁盘上的内部临时表。

如前所述，如果物化了CTE，则只物化一次，即使被引用多次。为指示一次性物化，优化器跟踪输出包含creating_tmp_table加上一次或多次reusing_tmp_table．

cte类似于派生表，其中materialized_from_subquery节点遵循引用。这对于多次引用的CTE是正确的，因此没有重复materialized_from_subquery节点(这会给人一种子查询被多次执行的印象，并产生不必要的冗长输出)。对CTE的引用只有一个完整的materialized_from_subquery节点及其子查询计划的描述。其他参考文献有删减materialized_from_subquery节点。同样的想法也适用于解释输出传统的format:没有显示其他引用的子查询。

上一页首页向上下一个