MySQL-5.7-8.2.2.2 Optimizing Subqueries with Materialization

The optimizer uses materialization to enable more efficient subquery processing.

优化器使用具体化来支持更有效的子查询处理。

Materialization speeds up query execution by generating a subquery result as a temporary table, normally in memory.

具体化通过将子查询结果生成为临时表(通常在内存中)来加快查询执行。

The first time MySQL needs the subquery result, it materializes that result into a temporary table. Any subsequent time the result is needed, MySQL refers again to the temporary table.

当MySQL第一次需要子查询结果时，它将结果具体化到一个临时表中。任何后续需要结果的时候，MySQL都会再次引用临时表。

The optimizer may index the table with a hash index to make lookups fast and inexpensive. The index contains unique values to eliminate duplicates and make the table smaller.

优化器可以使用哈希索引来索引表，以使查找速度更快、成本更低。索引包含唯一的值，以消除重复值并使表更小。

Subquery materialization uses an in-memory temporary table when possible, falling back to on-disk storage if the table becomes too large.

子查询具体化在可能的情况下使用内存中的临时表，如果表变得太大，则退回到磁盘上存储。

See Section 8.4.4, “Internal Temporary Table Use in MySQL”.

If materialization is not used, the optimizer sometimes rewrites a noncorrelated subquery as a correlated subquery.

如果不使用具体化，优化器有时会将不相关的子查询重写为相关的子查询。

For example, the following IN subquery is noncorrelated (where_condition involves only columns from t2 and not t1):

例如，下面的IN子查询是不相关的(where_condition只涉及t2中的列而不涉及t1中的列):

The optimizer might rewrite this as an EXISTS correlated subquery: 

优化器可能会将其重写为EXISTS相关子查询:

 Subquery materialization using a temporary table avoids such rewrites and makes it possible to execute the subquery only once rather than once per row of the outer query.

使用临时表的子查询具体化避免了这种重写，并使子查询只执行一次，而不是对外部查询的每行执行一次。

For subquery materialization to be used in MySQL, the optimizer_switch system variable materialization flag must be enabled.

对于在MySQL中使用的子查询具体化，必须启用optimizer_switch系统变量物化标志。

(See Section 8.9.2, “Switchable Optimizations”.)

With the materialization flag enabled, materialization applies to subquery predicates that appear anywhere (in the select list, WHERE, ON, GROUP BY, HAVING, or ORDER BY), for predicates that fall into any of these use cases:

启用具体化标志后，具体化将应用于出现在任何地方(在选择列表、WHERE、ON、GROUP BY、HAVING或ORDER BY)的子查询谓词，对于这些用例中的任何谓词:

The predicate has this form, when no outer expression oe_i or inner expression ie_i is nullable. N is 1 or larger.

当没有外部表达式oe_i或内部表达式ie_i可为空时，谓词就有这种形式。N大于等于1。

 The predicate has this form, when there is a single outer expression oe and inner expression ie. The expressions can be nullable.

当有一个单独的外部表达式oe和内部表达式ie时，谓语就有这种形式。表达式可以是空的。

The predicate is IN or NOT IN and a result of UNKNOWN (NULL) has the same meaning as a result of FALSE.

谓词是IN或NOT IN, UNKNOWN (NULL)的结果与FALSE的结果具有相同的含义。

The following examples illustrate how the requirement for equivalence of UNKNOWN and FALSE predicate evaluation affects whether subquery materialization can be used.

下面的示例说明了UNKNOWN和FALSE谓词求值的等价需求如何影响子查询物化是否可以使用。

Assume that where_condition involves columns only from t2 and not t1 so that the subquery is noncorrelated.

假设where_condition只涉及来自t2而不是t1的列，因此子查询是不相关的。

This query is subject to materialization:

 这个查询取决于物化:

Here, it does not matter whether the IN predicate returns UNKNOWN or FALSE. Either way, the row from t1 is not included in the query result.

在这里，IN谓词是否返回UNKNOWN或FALSE并不重要。无论是哪种方式，t1中的行都不会包含在查询结果中。

An example where subquery materialization is not used is the following query, where t2.b is a nullable column:

不使用子查询物化的示例是下面的查询，其中t2。B是一个可空列:

The following restrictions apply to the use of subquery materialization:

以下限制适用于子查询物化的使用:

• The types of the inner and outer expressions must match. For example, the optimizer might be able to use materialization if both expressions are integer or both are decimal, but cannot if one expression is integer and the other is decimal.

• 内部表达式和外部表达式的类型必须匹配。例如，如果两个表达式都是整数或小数，优化器可能能够使用物化，但如果一个表达式是整数而另一个是小数，则不能使用物化。

• The inner expression cannot be a BLOB.

• 内部表达式不能是BLOB。

Use of EXPLAIN with a query provides some indication of whether the optimizer uses subquery materialization:

在查询中使用EXPLAIN提供了优化器是否使用子查询物化的一些指示:

• Compared to query execution that does not use materialization, select_type may change from DEPENDENT SUBQUERY to SUBQUERY. This indicates that, for a subquery that would be executed once per outer row, materialization enables the subquery to be executed just once.

• 与不使用物化的查询执行相比，select_type可能从dependentsubquery变成SUBQUERY。这表明，对于每个外部行执行一次的子查询，物化使子查询只执行一次。

• For extended EXPLAIN output, the text displayed by a following SHOW WARNINGS includes materialize and materialized-subquery.

• 对于扩展的EXPLAIN输出，下面的SHOW WARNINGS显示的文本包括物化和物化子查询。