MySQL :: MySQL 8.4 参考手册 :: 27.5.2 视图处理算法

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 27 存储对象 / 27.5 使用视图 / 27.5.2 视图处理算法

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

27.5.2 视图处理算法27.5.2 View Processing Algorithms

可选的 ALGORITHM 子句用于 CREATE VIEW 或 ALTER VIEW，这是 MySQL 对标准 SQL 的扩展。它影响 MySQL 如何处理视图。ALGORITHM 有三个取值：MERGE、TEMPTABLE 或 UNDEFINED。The optional ALGORITHM clause for CREATE VIEW or ALTER VIEW is a MySQL extension to standard SQL. It affects how MySQL processes the view. ALGORITHM takes three values: MERGE, TEMPTABLE, or UNDEFINED.

对于 MERGE，引用视图的语句文本与视图定义合并，使视图定义的部分替换语句中相应的部分。For MERGE, the text of a statement that refers to the view and the view definition are merged such that parts of the view definition replace corresponding parts of the statement.
对于 TEMPTABLE，视图的结果将被检索到一个临时表中，然后用该临时表来执行语句。For TEMPTABLE, the results from the view are retrieved into a temporary table, which then is used to execute the statement.
对于 UNDEFINED，MySQL 会选择使用哪种算法。如果可能，它更偏好 MERGE 而不是 TEMPTABLE，因为 MERGE 通常更高效，并且如果使用临时表，视图无法被更新。For UNDEFINED, MySQL chooses which algorithm to use. It prefers MERGE over TEMPTABLE if possible, because MERGE is usually more efficient and because a view cannot be updated if a temporary table is used.
If no ALGORITHM clause is present, the default algorithm is determined by the value of the derived_merge……的旗optimizer_switch system variable. For additional discussion, see Section 10.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”.If no ALGORITHM clause is present, the default algorithm is determined by the value of the derived_merge flag of the optimizer_switch system variable. For additional discussion, see Section 10.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”.

明确指定TEMPTABLE的一个原因是，在临时表创建之后、用于完成语句处理之前，可以释放底层表上的锁。这可能会比MERGE算法更快地释放锁，从而使使用该视图的其他客户端不会被长时间阻塞。A reason to specify TEMPTABLE explicitly is that locks can be released on underlying tables after the temporary table has been created and before it is used to finish processing the statement. This might result in quicker lock release than the MERGE algorithm so that other clients that use the view are not blocked as long.

视图算法可以是UNDEFINED有三个原因：A view algorithm can be UNDEFINED for three reasons:

CREATE VIEW语句中没有ALGORITHM子句。No ALGORITHM clause is present in the CREATE VIEW statement.
CREATE VIEW语句中有明确的ALGORITHM = UNDEFINED子句。The CREATE VIEW statement has an explicit ALGORITHM = UNDEFINED clause.
ALGORITHM = MERGE 被指定用于只能通过临时表处理的视图。在这种情况下，MySQL 会生成一个警告并将算法设置为 UNDEFINED。ALGORITHM = MERGE is specified for a view that can be processed only with a temporary table. In this case, MySQL generates a warning and sets the algorithm to UNDEFINED.

如前所述，MERGE 是通过将视图定义的对应部分合并到引用该视图的语句中来处理的。以下示例简要说明了 MERGE 算法的工作方式。示例假设存在一个定义如下的视图 v_merge：As mentioned earlier, MERGE is handled by merging corresponding parts of a view definition into the statement that refers to the view. The following examples briefly illustrate how the MERGE algorithm works. The examples assume that there is a view v_merge that has this definition:

CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS
SELECT c1, c2 FROM t WHERE c3 > 100;

示例 1：假设我们执行以下语句：Example 1: Suppose that we issue this statement:

SELECT * FROM v_merge;

MySQL 处理该语句如下：MySQL handles the statement as follows:

v_merge 变为 tv_merge becomes t
* 变为 vc1, vc2，对应于 c1, c2* becomes vc1, vc2, which corresponds to c1, c2
视图的 WHERE 子句已添加The view WHERE clause is added

要执行的结果语句变为：The resulting statement to be executed becomes:

SELECT c1, c2 FROM t WHERE c3 > 100;

示例 2：假设我们发出此语句：Example 2: Suppose that we issue this statement:

SELECT * FROM v_merge WHERE vc1 < 100;

此语句的处理方式与前一个类似，只是 vc1 < 100 变为 c1 < 100，并且视图的 WHERE 子句被添加到语句的 WHERE 子句中，使用 AND 连接符（并添加括号以确保子句的各部分以正确的优先级执行）。要执行的结果语句变为：This statement is handled similarly to the previous one, except that vc1 < 100 becomes c1 < 100 and the view WHERE clause is added to the statement WHERE clause using an AND connective (and parentheses are added to make sure the parts of the clause are executed with correct precedence). The resulting statement to be executed becomes:

SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100);

实际上，要执行的语句具有以下形式的 WHERE 子句：Effectively, the statement to be executed has a WHERE clause of this form:

WHERE (select WHERE) AND (view WHERE)

如果无法使用 MERGE 算法，则必须使用临时表。阻止合并的构造与阻止派生表和公共表表达式合并的构造相同。例如子查询中的 SELECT DISTINCT 或 LIMIT。详细信息，请参见第 10.2.2.4 节，“通过合并或物化优化派生表、视图引用和公共表表达式”。If the MERGE algorithm cannot be used, a temporary table must be used instead. Constructs that prevent merging are the same as those that prevent merging in derived tables and common table expressions. Examples are SELECT DISTINCT or LIMIT in the subquery. For details, see Section 10.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”.

首页上一级下一节