8.2.2.3 使用 EXISTS 策略优化子查询
某些优化适用于使用IN(或=ANY)运算符测试子查询结果的比较。本节讨论这些优化，尤其是针对NULL值所带来的挑战。讨论的最后部分提出了如何帮助优化器的建议。
考虑以下子查询比较：
outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)
MySQL“从外到内”评估查询。也就是说，它首先获取外部 table 达式* outer_expr *的值，然后运行子查询并捕获其产生的行。
一个非常有用的优化是“通知”子查询仅感兴趣的行是那些内部 table 达式* inner_expr 等于 outer_expr *的行。这是通过将适当的等式推入子查询的WHERE子句以使其更具限制性来完成的。转换后的比较如下所示：
EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)
转换后，MySQL 可以使用下推式相等性来限制为评估子查询而必须检查的行数。
更一般而言，将* N 值与返回 N * -value 行的子查询进行比较将经历相同的转换。如果* oe_i 和 ie_i *table 示相应的外部和内部 table 达式值，则此子查询比较：
(oe_1, ..., oe_N) IN
(SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)
Becomes:
EXISTS (SELECT 1 FROM ... WHERE subquery_where
AND oe_1 = ie_1
AND ...
AND oe_N = ie_N)
为简单起见，下面的讨论假定一对外部 table 达式值和内部 table 达式值。
刚刚描述的转换有其局限性。仅当我们忽略可能的NULL值时才有效。也就是说，只要满足以下两个条件，就可以使用“下推”策略：
-
outer_expr和inner_expr*不能为NULL。
-
您无需将
NULL与FALSE子查询结果区分开。如果子查询是WHERE子句中OR或ANDtable 达式的一部分，则 MySQL 假定您不在乎。优化器注意到不需要区分NULL和FALSE子查询结果的另一个实例是以下结构：
... WHERE outer_expr IN (subquery)
在这种情况下，无论IN (subquery)返回NULL还是FALSE，WHERE子句都拒绝该行。
当这些条件中的任何一个或两个都不成立时，优化将更加复杂。
假定* outer_expr 是一个非NULL值，但是子查询不会产生 outer_expr * = * inner_expr *的行。然后outer_expr IN (SELECT ...)评估如下：
在这种情况下，使用outer_expr = inner_expr查找行的方法不再有效。有必要查找这样的行，但是如果找不到，则还要查找* inner_expr *为NULL的行。粗略地讲，子查询可以转换为如下形式：
EXISTS (SELECT 1 FROM ... WHERE subquery_where AND
(outer_expr=inner_expr OR inner_expr IS NULL))
需要评估额外的IS NULL条件是 MySQL 具有ref_or_null访问方法的原因：
mysql> EXPLAIN
SELECT outer_expr IN (SELECT t2.maybe_null_key
FROM t2, t3 WHERE ...)
FROM t1;
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: t1
...
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: t2
type: ref_or_null
possible_keys: maybe_null_key
key: maybe_null_key
key_len: 5
ref: func
rows: 2
Extra: Using where; Using index
...
unique_subquery和index_subquery特定于子查询的访问方法也具有“或NULL”变体。
附加的OR ... IS NULL条件使查询的执行稍微复杂一些(并且子查询中的某些优化变得不适用)，但这通常是可以容忍的。
当* outer_expr *可以是NULL时，情况就更糟了。根据NULL的 SQL 解释为“未知值”，NULL IN (SELECT inner_expr ...)的计算结果应为：
为了进行正确的评估，必须能够检查SELECT是否已产生任何行，因此outer_expr = inner_expr无法下推到子查询中。这是一个问题，因为许多现实世界中的子查询会变得非常缓慢，除非可以降低相等性。
本质上，根据* outer_expr *的值，必须有不同的方法来执行子查询。
优化器选择 SQL 遵从性而不是速度，因此考虑了* outer_expr *可能是NULL的可能性：
- 如果*
outer_expr*是NULL，则要计算以下 table 达式，必须执行SELECT以确定它是否产生任何行：
NULL IN (SELECT inner_expr FROM ... WHERE subquery_where)
必须在此处执行原始的SELECT，而没有前面提到的那种下推式等式。
- 另一方面，当*
outer_expr*不是NULL时，此比较绝对必要：
outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)
转换为使用下推条件的 table 达式：
EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)
没有这种转换，子查询将很慢。
为了解决是否将条件下推到子查询中的难题，将条件包装在“触发”函数中。因此，以下形式的 table 达式：
outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)
转换为：
EXISTS (SELECT 1 FROM ... WHERE subquery_where
AND trigcond(outer_expr=inner_expr))
更一般而言，如果子查询比较基于几对外部和内部 table 达式，则转换将采用以下比较：
(oe_1, ..., oe_N) IN (SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)
并将其转换为以下 table 达式：
EXISTS (SELECT 1 FROM ... WHERE subquery_where
AND trigcond(oe_1=ie_1)
AND ...
AND trigcond(oe_N=ie_N)
)
每个trigcond(X)是一个特殊函数，其结果为以下值：
-
X当“链接的”外部 table 达式oe_i*不是NULL时
-
TRUE当“链接的”外部 table 达式*oe_i*为NULL时
Note
触发器函数不是您用CREATE TRIGGER创建的那种*触发器。
trigcond()函数中包装的等式不是查询优化器的第一类谓词。大多数优化无法处理可能在查询执行时打开和关闭的谓词，因此它们假定任何trigcond(X)为未知函数并忽略它。那些优化可以使用触发的等式：
-
参考优化：
trigcond(X=Y [OR Y IS NULL])可用于构造ref，eq_ref或ref_or_nulltable 访问。 -
基于索引查找的子查询执行引擎：
trigcond(X=Y)可用于构造unique_subquery或index_subquery访问。 -
table 条件生成器：如果子查询是多个 table 的联接，则将尽快检查触发条件。
当优化器使用触发条件创建某种基于索引查找的访问时(对于前面列 table 的前两项)，对于条件关闭的情况，优化器必须具有回退策略。此后备策略始终相同：执行全 table 扫描。在EXPLAIN输出中，后备广告在Extra列中显示为Full scan on NULL key：
mysql> EXPLAIN SELECT t1.col1,
t1.col1 IN (SELECT t2.key1 FROM t2 WHERE t2.col2=t1.col2) FROM t1\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: t1
...
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: t2
type: index_subquery
possible_keys: key1
key: key1
key_len: 5
ref: func
rows: 2
Extra: Using where; Full scan on NULL key
如果先运行EXPLAIN，然后运行SHOW WARNINGS，则可以看到触发的条件：
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select `test`.`t1`.`col1` AS `col1`,
<in_optimizer>(`test`.`t1`.`col1`,
<exists>(<index_lookup>(<cache>(`test`.`t1`.`col1`) in t2
on key1 checking NULL
where (`test`.`t2`.`col2` = `test`.`t1`.`col2`) having
trigcond(<is_not_null_test>(`test`.`t2`.`key1`))))) AS
`t1.col1 IN (select t2.key1 from t2 where t2.col2=t1.col2)`
from `test`.`t1`
使用触发条件会影响性能。现在，NULL IN (SELECT ...)table 达式以前可能没有引起全 table 扫描(这很慢)。这是为获得正确结果而付出的代价(触发条件策略的目标是提高合规性，而不是速度)。
对于多 table 子查询，NULL IN (SELECT ...)的执行特别慢，因为联接优化器不会针对外部 table 达式为NULL的情况进行优化。它假定左侧带有NULL的子查询评估非常少见，即使有统计数据 table 明并非如此。另一方面，如果外部 table 达式可能是NULL但实际上不是，则不会影响性能。
为了帮助查询优化器更好地执行查询，请使用以下建议：
-
如果确实是一列，则将其声明为
NOT NULL。通过简化色谱柱的条件测试，这也有助于优化程序的其他方面。 -
如果您不需要区分
NULL和FALSE子查询结果，则可以轻松避免执行路径缓慢。替换如下所示的比较：
outer_expr IN (SELECT inner_expr FROM ...)
具有以下 table 达式：
(outer_expr IS NOT NULL) AND (outer_expr IN (SELECT inner_expr FROM ...))
然后永远不会评估NULL IN (SELECT ...)，因为一旦 table 达式结果明确，MySQL 就会停止评估AND个部分。
另一种可能的重写：
EXISTS (SELECT inner_expr FROM ...
WHERE inner_expr=outer_expr)
当您不需要区分NULL和FALSE子查询结果时，这将适用，在这种情况下，您实际上可能需要EXISTS。
optimizer_switch系统变量的subquery_materialization_cost_based标志可控制子查询实现和IN到EXISTS子查询转换之间的选择。参见第 8.9.2 节“可切换的优化”。
