Documentation Home
MySQL 8.4 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 39.8Mb
PDF (A4) - 39.9Mb
Man Pages (TGZ) - 257.9Kb
Man Pages (Zip) - 364.9Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb


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,并且结果为UNKNOWNNULL)与结果为FALSE具有相同的含义。

以下示例演示了对UNKNOWNFALSE谓词评估的等价性对子查询物化的使用。假设where_condition仅涉及t2的列,不涉及t1的列,因此子查询是非相关的。

这个查询适用于物化:

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

在这里,不管IN谓词返回UNKNOWNFALSE,都不会将来自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

MySQL也可以将子查询物化应用于一个单表UPDATEDELETE语句,该语句使用一个[NOT] IN[NOT] EXISTS子查询谓词,提供该语句不使用ORDER BYLIMIT,并且子查询物化被允许通过优化器提示或optimizer_switch设置。