通过设置 materialization 优化子查询
优化器使用实现来启用更有效的子查询处理。实例化通常通过在内存中生成子查询结果作为临时表来加快查询执行速度。MySQL第一次需要子查询结果时,将其结果化为临时表。任何随后的需要结果的时间,MySQL都会再次引用临时表。优化器可以使用哈希索引对表进行索引,以使查找快速,廉价。索引包含唯一值,以消除重复项并使表更小。
子查询实现在可能的情况下使用内存中的临时表,如果表太大,则会退回到磁盘上的存储。请参见“ MySQL中的内部临时表使用”。
如果未使用实现,则优化器有时会将不相关的子查询重写为相关的子查询。例如,以下IN
子查询是不相关的(where_condition
仅涉及from t2
和not中的列t1
):
SELECT *FROM t1WHERE t1.IN (SELECT t2.bFROM t2WHERE where_condition);
优化器可能会将其重写为EXISTS
相关子查询:
SELECT *FROM t1WHERE EXISTS (SELECT t2.bFROM t2WHERE where_condition AND t1. =t2.b);
使用临时表的子查询实现避免了此类重写,并使得仅可以执行一次子查询,而不是对外部查询的每一行执行一次。
为了使子查询实现在MySQL中使用,必须启用optimizer_switch
系统变量materialization
标志。(见“切换优化”)。随着materialization
启用的标志,物化适用于任何地方出现子查询谓词(在选择列表中,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 t1WHERE t1.IN (SELECT t2.bFROM t2WHERE where_condition);
在此,IN
谓词返回UNKNOWN
还是都没有关系FALSE
。无论哪种方式,from的行都t1
不会包含在查询结果中。
以下查询是不使用子查询实现的示例,其中t2.b
的列为可空:
SELECT *FROM t1WHERE (t1. ,t1.b) NOTIN (SELECT t2. ,t2.bFROM t2WHERE where_condition);
以下限制适用于子查询实现的使用:
- 内部和外部表达式的类型必须匹配。例如,如果两个表达式都是整数或两个都是十进制,那么优化器可能可以使用实现,但是如果一个表达式是整数而另一个表达式是十进制,则优化器不能使用实现。
- 内部表达式不能是
BLOB
。
EXPLAIN
与查询一起使用可提供有关优化器是否使用子查询实现的某种指示:
- 与不使用实现的查询执行相比,
select_type
可以从更改DEPENDENT SUBQUERY
为SUBQUERY
。这表明,对于将对每个外行执行一次的子查询,实现将使子查询仅执行一次。 - 对于扩展
EXPLAIN
输出,以下内容显示的文本SHOW WARNINGS
包括materialize
和materialized-subquery
。