Documentation Home

8.2.2.2 使用物化优化子查询

优化器使用物化来实现更高效的子查询处理。物化通过将子查询结果生成为临时表(通常在内存中)来加速查询执行。MySQL 第一次需要子查询结果时,会将结果具体化到一个临时表中。任何后续需要结果的时候,MySQL 都会再次引用临时表。优化器可以使用散列索引对表进行索引,以使查找快速且成本低廉。索引包含唯一值以消除重复项并使表更小。

子查询具体化尽可能使用内存中的临时表,如果表变得太大,则回退到磁盘存储。请参阅 第 8.4.4 节,“MySQL 中的内部临时表使用”

如果不使用物化,优化器有时会将不相关的子查询重写为相关的子查询。例如,以下IN子查询是不相关的(where_condition 仅涉及列 fromt2和 not 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(请参阅 第 8.9.2 节,“可切换优化”。) materialization启用标志后,物化适用于出现在任何地方的子查询谓词(在选择列表中,,,,,,或) WHERE, 对于属于以下任何用例的谓词: ONGROUP BYHAVINGORDER 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 ...)
  • 谓词 is INor NOT INand a result of UNKNOWN ( NULL) 与 a result of 具有相同的含义FALSE

以下示例说明了对等价性UNKNOWNFALSE谓词评估的要求如何影响是否可以使用子查询具体化。假设 只涉及来自和不where_condition涉及的列, 因此子查询是不相关的。 t2t1

此查询需要具体化:

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

IN 在这里,谓词是否返回UNKNOWNor 并不重要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包括 materializematerialized-subquery