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. MySQL第一次需要子查询结果时，会将结果具体化为一个临时表。Any subsequent time the result is needed, MySQL refers again to the temporary table. 任何后续需要结果的时候，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”.请参阅第8.4.4节，“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中的列）：
SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);
The optimizer might rewrite this as an 优化器可能会将其重写为EXISTS correlated subquery:EXISTS相关的子查询：
SELECT * FROM t1
WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_condition AND t1.a=t2.b);
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 要在MySQL中使用子查询物化，必须启用optimizer_switch system variable materialization flag must be enabled. optimizer_switch系统变量materialization标志。(See Section 8.9.2, “Switchable Optimizations”.) （请参阅第8.9.2节，“可切换的优化”。）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:materialization标志后，物化应用于出现在任意位置（在选择列表、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. oe_i或内部表达式ie_i都不可为空时，谓词具有这种形式。N is 1 or larger.N等于或大于1。
(oe_1,oe_2, ...,oe_N) [NOT] IN (SELECTie_1,i_2, ...,ie_N...)
The predicate has this form, when there is a single outer expression 当存在单个外部表达式oe and inner expression ie. oe和内部表达式ie时，谓词具有这种形式。The expressions can be nullable.表达式可以为NULL。
oe[NOT] IN (SELECTie...)
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:此查询需要具体化：
SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);
Here, it does not matter whether the 在这里，IN predicate returns UNKNOWN or FALSE. IN谓词返回UNKNOWN还是FALSE并不重要。Either way, the row from 无论哪种方式，t1 is not included in the query result.t1中的行都不包括在查询结果中。
An example where subquery materialization is not used is the following query, where 未使用子查询具体化的一个示例是以下查询，其中t2.b is a nullable column:t2.b是可为空的列：
SELECT * FROM t1
WHERE (t1.a,t1.b) NOT IN (SELECT t2.a,t2.b FROM t2
WHERE where_condition);
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. select_type可能会从DEPENDENT SUBQUERY更改为SUBQUERY。This indicates that, for a subquery that would be executed once per outer row, materialization enables the subquery to be executed just once.这表明，对于每个外部行执行一次的子查询，物化使子查询只执行一次。
For extended 对于扩展的EXPLAIN output, the text displayed by a following SHOW WARNINGS includes materialize and materialized-subquery.EXPLAIN输出，以下SHOW WARNINGS显示的文本包括materialize和materialized-subquery。
In MySQL 8.0.21 and later, MySQL can also apply subquery materialization to a single-table 在MySQL 8.0.21及更高版本中，MySQL还可以对使用UPDATE or DELETE statement that uses a [NOT] IN or [NOT] EXISTS subquery predicate, provided that the statement does not use ORDER BY or LIMIT, and that subquery materialization is allowed by an optimizer hint or by the optimizer_switch setting.[NOT] IN或[NOT] EXISTS子查询谓词的单个表UPDATE语句或DELETE语句应用子查询物化，前提是该语句不使用ORDER BY或LIMIT，并且优化器提示或optimizer_switch设置允许子查询物化。