mysql用视图合并行_mysql8 参考手册--通过合并或实现来优化派生表，视图引用和公用表表达式...

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

1、将派生表合并到外部查询块中

2、将派生表具体化为内部临时表

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

随着实现，derived_t1并且 derived_t2在各自的查询中每个都被视为一个单独的表。

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

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

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

1、外部查询未分组或聚合。

2、外部查询不指定 DISTINCT，HAVING或 ORDER BY。

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

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

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

1、MERGE和 NO_MERGE优化器提示可以使用。它们适用的前提是没有其他规则可以阻止合并。请参见第8.9.3节“优化器提示”。

2、同样，您可以使用系统变量的 derived_merge标志 optimizer_switch。请参见第8.9.2节“可切换的优化”。默认情况下，该标志启用以允许合并。禁用该标志可防止合并并避免 ER_UPDATE_TABLE_USED 错误。

derived_merge标志还适用于不包含任何ALGORITHM子句的视图。因此，如果 ER_UPDATE_TABLE_USED使用等同于子查询的表达式的3、视图引用发生错误，则添加 ALGORITHM=TEMPTABLE到视图定义将防止合并，并优先于该 derived_merge值。

可以通过在子查询中使用任何阻止合并的构造来禁用合并，尽管这些构造对实现的影响不那么明显。防止合并的构造对于派生表，公用表表达式和视图引用都是相同的：

1、聚集函数或窗函数(SUM()， MIN()， MAX()， COUNT()，等等)

2、DISTINCT

3、GROUP BY

4、HAVING

5、LIMIT

6、UNION 要么 UNION ALL

7、选择列表中的子查询

8、分配给用户变量

9、仅引用文字值(在这种情况下，没有基础表)

如果优化程序选择实现策略而不是为派生表合并，那么它将按以下方式处理查询：

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

2、在查询执行期间，优化器可以将索引添加到派生表中，以加快从中获取行的速度。

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访问会比其他访问方法带来更高的成本，则优化器不会创建索引，也不会丢失任何内容。

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

对于派生表的实现，正确的情况也适用于公用表表达式(CTE)。此外，以下注意事项专门与CTE有关。

如果查询实现了CTE，则即使查询多次引用，CTE也会为该查询实现一次。

递归CTE始终会实现。

如果实现了CTE，则优化器估计索引将加快顶层语句对CTE的访问，从而自动添加相关索引。这类似于派生表的自动索引，不同之处在于，如果多次引用CTE，优化器可能会创建多个索引，以最合适的方式加快每个引用的访问速度。

的MERGE和 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.d

FROM cte1 JOIN cte2

WHERE cte1.a = cte2.c;

CREATE VIEW的ALGORITHM子句不影响视图定义中SELECT语句前面的任何WITH子句的具体化。请考虑以下声明：考虑以下语句：

CREATE ALGORITHM={TEMPTABLE|MERGE} VIEW v1 AS WITH ... SELECT ...

该ALGORITHM值仅影响的实现SELECT，而不 影响WITH子句。

在MySQL 8.0.16之前，如果 internal_tmp_disk_storage_engine=MYISAM使用磁盘临时表实现CTE的任何尝试均发生错误，因为对于CTE，用于磁盘内部临时表的存储引擎不能为 MyISAM。从MySQL 8.0.16开始，这不再是问题，因为它TempTable现在始终InnoDB用于磁盘内部临时表。

如前所述，CTE如果实现了，即使多次引用也要实现一次。为了指示一次实现，优化程序跟踪输出包含一个事件的 creating_tmp_table加上一个或多个reusing_tmp_table。

CTE与派生表相似，materialized_from_subquery节点遵循该派生表 。对于多次引用的CTE而言，这是正确的，因此没有materialized_from_subquery节点重复 (这会给子查询多次执行的印象，并产生不必要的冗长输出)。只有一个对CTE的引用具有完整的 materialized_from_subquery节点及其子查询计划的描述。其他参考具有减少的materialized_from_subquery节点。同样的想法适用 EXPLAIN于TRADITIONAL格式输出 ：未显示其他引用的子查询。