MySQL :: MySQL 8.3 Reference Manual :: 10.2.2.2 Optimizing Subqueries with Materialization

version 8.3

8.0 current
5.7

8.0 Japanese

MySQL 8.3 Reference Manual / ... / Optimizing Subqueries with Materialization

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，都不会影响结果。

以下查询不使用子查询物化，例如 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 还可以将子查询materialization应用于单表UPDATE或DELETE语句，该语句使用[NOT] IN或[NOT] EXISTS子查询谓词，前提是该语句不使用ORDER BY或LIMIT，并且子查询materialization由优化器提示或optimizer_switch设置允许。

PREV HOME UP NEXT