8.2.2.2 通过实现来优化子查询

优化器使用实现来启用更有效的子查询处理。物化通过生成子查询结果作为临时 table(通常在内存中)来加快查询执行速度。 MySQL 第一次需要子查询结果时，会将结果具体化为临时 table。任何随后的需要结果的时间，MySQL 都会再次引用临时 table。优化器可以使用哈希索引对 table 进行索引，以使查找快速，廉价。索引包含唯一值，以消除重复项并使 table 更小。

子查询实现在可能的情况下使用内存中的临时 table，如果 table 太大，则会退回到磁盘上的存储。参见第 8.4.4 节“ MySQL 中的内部临时 table 使用”。

如果未使用实现，则优化器有时会将不相关的子查询重写为相关的子查询。例如，以下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);

使用临时 table 的子查询实现避免了这样的重写，并使得只可能执行一次子查询，而不是对外部查询的每一行执行一次。

为了使子查询实现在 MySQL 中使用，必须启用optimizer_switch系统变量materialization标志。 (请参见第 8.9.2 节“可切换的优化”。)启用materialization标志，实现适用于出现在以下任何一种情况下的谓词：在任何位置(在选择列 tableWHERE，ON，GROUP BY，HAVING或ORDER BY中)出现的子查询谓词：

• 当没有外部 table 达式* oe_i 或内部 table 达式 ie_i *为空时，谓词具有这种形式。 * N *为 1 或更大。

(oe_1, oe_2, ..., oe_N) [NOT] IN (SELECT ie_1, i_2, ..., ie_N ...)

• 当存在单个外部 table 达式* oe 和内部 table 达式 ie *时，谓词具有这种形式。table 达式可以为空。

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);

以下限制适用于子查询实现的使用：

• 内部和外部 table 达式的类型必须匹配。例如，如果两个 table 达式都是整数或两个都是十进制，那么优化器可能可以使用实现，但是如果一个 table 达式是整数而另一个 table 达式是十进制，则优化器不能使用实现。

• 内部 table 达式不能为BLOB。

在查询中使用EXPLAIN可以 table 明优化器是否使用子查询实现：

• 与不使用实现的查询执行相比，select_type可能从DEPENDENT SUBQUERY更改为SUBQUERY。这 table 明，对于将对每个外行执行一次的子查询，实现将使子查询仅执行一次。

• 对于扩展的EXPLAIN输出，随后的SHOW WARNINGS显示的文本包括materialize和materialized-subquery。