MySQL 8.0 官方文档 第八章 优化（二十五）—— 使用物化优化子查询

第八章 优化（二十五）—— 使用物化优化子查询

8.2 优化SQL语句

8.2.2 优化子查询、派生表、视图引用和公共表表达式

8.2.2.2 使用物化优化子查询

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

在可能的情况下，子查询物化会使用内存中的临时表，但是，如果该临时表变得太大，则只能求助于磁盘存储。参见 8.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);

使用了临时表的查询物化避免了这样的重写，并且使该子查询只执行一次成为可能，而不是在遍历外部查询中的每一行时都执行一次。

为了在MySQL中能使用子查询物化，必须启用optimizer_switch系统变量materialization标志（参见8.9.2节，“可切换优化”）。启用了物化标志后，物化适用于出现在任何位置的（例如：在选择列表，WHERE, ON, GROUP BY, HAVING，或ORDER BY中）子查询谓词，这些谓词属于以下任何用例：

• 当外部表达式oe_i或内部表达式ie_i都不为空（nullable）时，谓词具有以下形式，其中 N等于1或大于1。

  (oe_1, oe_2, ..., oe_N) [NOT] IN (SELECT ie_1, ie_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是可以为null的：

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

以下限制适用于子查询物化的使用:

• 内外表达式的类型必须匹配。例如，如果两种表达式都是integer或decimal，则优化器可能会使用物化，但是如果一个表达式是integer，另一个是decimal，则优化器不能使用物化。

• 内部表达式不能是BLOB。

使用了EXPLAIN的查询会提供了一些优化器是否使用了子查询物化的提示。

• 与没有使用物化的查询相比，EXPLAIN输出中select_type（选择类型）列提示的内容会从DEPENDENT SUBQUERY（关联子查询）改变为SUBQUERY（子查询）。这表明了对于每个外部行都会执行一次子查询，而物化使子查询只执行一次。

• 对于扩展的EXPLAIN输出，接着执行SHOW WARNINGS显示的文本会包含materialize（物化）和materialized-subquery（物化的子查询）。

在MySQL 8.0.21及更高版本中，MySQL也可以将子查询物化应用到单表UPDATE或DELETE语句中，使用的前提是要求使用[NOT] IN或[NOT] EXISTS子查询谓词，但是没有使用ORDER BY或LIMIT，并且可以通过优化器提示或优化器可切换设置来允许使用子查询物化。

上一集 MySQL 8.0 官方文档 第八章 优化（二十四）—— 用半连接转换优化IN和EXISTS子查询谓词

下一集 MySQL 8.0 官方文档 第八章 优化（二十六）—— 使用EXISTS策略优化子查询