10.2.2.2 优化子查询 with Materialization
优化器使用物化来启用更高效的子查询处理。物化加速查询执行生成一个临时表,通常在内存中。第一次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);
子查询物化使用临时表避免了重写,使得可以执行子查询只执行一次,而不是每行outer查询。
为了在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也可以将子查询物化应用于一个单表UPDATE
或DELETE
语句,该语句使用一个[NOT] IN
或[NOT] EXISTS
子查询谓词,提供该语句不使用ORDER BY
或LIMIT
,并且子查询物化被允许通过优化器提示或optimizer_switch
设置。