MySQL :: MySQL 8.4 参考手册 :: 10.2.2.2 使用物化优化子查询

版本 8.4

8.0 日语

MySQL 8.4 参考手册 / ... / 使用物化优化子查询

10.2.2.2 使用物化优化子查询

优化器使用物化来实现更有效的子查询处理。物化通过将子查询结果生成一个临时表（通常在内存中）来加快查询执行速度。MySQL 第一次需要子查询结果时，会将该结果物化到临时表中。之后每次需要该结果时，MySQL 都会再次引用该临时表。优化器可能会使用哈希索引为该表建立索引，以使查找速度快且成本低。索引包含唯一的值，以消除重复项并缩小表的大小。

子查询物化尽可能使用内存中的临时表，如果表变得太大，则会回退到磁盘存储。参见第 10.4.4 节，“MySQL 中使用内部临时表”。

如果未使用物化，优化器有时会将非相关子查询重写为相关子查询。例如，以下 IN 子查询是非相关的（where_condition 仅涉及 t2 中的列，不涉及 t1）

SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

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

SELECT * FROM t1
WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_condition AND t1.a=t2.b);

使用临时表进行子查询物化可以避免这种重写，并使子查询只需执行一次，而不是针对外部查询的每一行执行一次。

要在 MySQL 中使用子查询物化，optimizer_switch 系统变量的 materialization 标志必须启用。（参见第 10.9.2 节，“可切换优化”。）启用 materialization 标志后，物化适用于出现在任何位置（在 select 列表、WHERE、ON、GROUP BY、HAVING 或 ORDER BY 中）的子查询谓词，适用于以下任何用例的谓词

当没有外部表达式 oe_i 或内部表达式 ie_i 为可空时，谓词具有以下形式。N 为 1 或更大。
```
(oe_1, oe_2, ..., oe_N) [NOT] IN (SELECT ie_1, i_2, ..., ie_N ...)
```
当只有一个外部表达式 oe 和内部表达式 ie 时，谓词具有以下形式。表达式可以为空。
```
oe [NOT] IN (SELECT ie ...)
```
谓词为 IN 或 NOT IN，UNKNOWN (NULL) 的结果与 FALSE 的结果具有相同的含义。

以下示例说明了 UNKNOWN 和 FALSE 谓词评估的等效性要求如何影响是否可以使用子查询物化。假设 where_condition 仅涉及 t2 中的列，不涉及 t1，因此子查询是非相关的。

此查询可以进行物化

SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

这里，IN 谓词返回 UNKNOWN 或 FALSE 都无关紧要。无论哪种情况，t1 中的行都不会包含在查询结果中。

以下查询是一个不使用子查询物化的示例，其中 t2.b 是可空列

SELECT * FROM t1
WHERE (t1.a,t1.b) NOT IN (SELECT t2.a,t2.b FROM t2
                          WHERE where_condition);

使用子查询物化存在以下限制

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

使用 EXPLAIN 对查询进行分析可以提供一些关于优化器是否使用子查询物化的信息

与不使用物化执行查询相比，select_type 可能会从 DEPENDENT SUBQUERY 更改为 SUBQUERY。这表明，对于会针对每个外部行执行一次的子查询，物化可以使子查询只执行一次。
对于扩展的 EXPLAIN 输出，后续 SHOW WARNINGS 显示的文本包含 materialize 和 materialized-subquery。

MySQL 也可以将子查询物化应用于使用 [NOT] IN 或 [NOT] EXISTS 子查询谓词的单表 UPDATE 或 DELETE 语句，前提是该语句不使用 ORDER BY 或 LIMIT，并且子查询物化被优化器提示或 optimizer_switch 设置允许。