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

The following restrictions apply to the use of subquery materialization:

  • The types of the inner and outer expressions must match. For example, the optimizer might be able to use materialization if both expressions are integer or both are decimal, but cannot if one expression is integer and the other is decimal.

  • The inner expression cannot be a BLOB.

Use of EXPLAIN with a query provides some indication of whether the optimizer uses subquery materialization:

  • 与不使用物化的查询执行相比,select_type可能会从DEPENDENT SUBQUERY变为 SUBQUERY. 这表明,对于每个外行执行一次的子查询,物化使子查询只执行一次。

  • 对于扩展EXPLAIN 输出,下面显示的文本 SHOW WARNINGS包括 materializematerialized-subquery