某些优化适用于使用IN（或=ANY）运算符测试子查询结果的比较。本节讨论这些优化，尤其是关于NULL价值所面临的挑战。讨论的最后部分提出了如何帮助优化器的建议。
考虑以下子查询比较：
outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)
MySQL的评估查询“从外到内。” 即，首先获得外表达式的值 outer_expr，然后运行子查询，并且捕获的行，它产生。
一个非常有用的优化是“通知”子查询仅感兴趣的行是内部表达式*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*代表相应的外部和内部表达式值，则此子查询比较：
(oe_1, ..., oe_N) IN
(SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)
成为：
EXISTS (SELECT 1 FROM ... WHERE subquery_where
AND oe_1 = ie_1
AND ...
AND oe_N = ie_N)
为简单起见，下面的讨论假定一对外部表达式值和内部表达式值。
如果满足以下任一条件，则 上述“下推”策略将起作用：
-
*
outer_expr*并且 *inner_expr*不可能是NULL。 -
你不必区分
NULL从FALSE子查询结果。如果子查询是子句中ORorAND表达式 的一部分WHERE，则MySQL会认为您不在乎。在优化程序注意到另一个实例NULL和FALSE子查询结果不需要区分是这样的结构：... WHERE outer_expr IN (subquery)在这种情况下，该
WHERE子句拒绝该行，无论是return 还是。IN (*subquery*)``NULL``FALSE
假设*outer_expr已知这是一个非NULL值，但是子查询不会产生诸如outer_expr*= 的行 inner_expr。然后 *outer_expr* IN (SELECT ...)评估如下：
在这种情况下，使用查找行的方法 不再有效。有必要寻找这样的行，但是如果没有找到，则还要在is处 寻找行 。粗略地说，子查询可以转换为如下形式： *outer_expr* = *inner_expr*inner_exprNULL
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。据的SQL解释NULL为“未知值， ” 应该评估为： NULL IN (SELECT *inner_expr* ...)
为了进行正确的评估，必须能够检查是否SELECT已产生任何行，因此 不能将其下推到子查询中。这是一个问题，因为许多现实世界中的子查询会变得非常缓慢，除非可以降低相等性。 *outer_expr* = *inner_expr*
本质上，取决于的值，必须有不同的方法来执行子查询 outer_expr。
优化选择超速SQL合规性，所以它占的可能性 *outer_expr*可能是 NULL：
-
如果*
outer_expr*为NULL，则要评估以下表达式，必须执行SELECT以确定它是否产生任何行：NULL IN (SELECT inner_expr FROM ... WHERE subquery_where)必须在
SELECT此处执行原始文件 ，而没有前面提到的那种下推式等价物。 -
另一方面，当 *
outer_expr*不是时NULL，进行此比较绝对必要：outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)转换为使用下推条件的表达式：
EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)没有这种转换，子查询将很慢。
为了解决是否将条件下推到子查询中的难题，这些条件被包装在 “触发器”函数中。因此，以下形式的表达式：
outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)
转换为：
EXISTS (SELECT 1 FROM ... WHERE subquery_where
AND trigcond(outer_expr=inner_expr))
更一般而言，如果子查询比较基于几对外部和内部表达式，则转换将采用以下比较：
(oe_1, ..., oe_N) IN (SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)
并将其转换为以下表达式：
EXISTS (SELECT 1 FROM ... WHERE subquery_where
AND trigcond(oe_1=ie_1)
AND ...
AND trigcond(oe_N=ie_N)
)
每个 函数都是一个特殊函数，其求值结果如下： trigcond(*X*)
- *
X*当 “链接的”外部表达 *oe_i*不是NULL TRUE当“链接的” 外部表达*oe_i*是NULL
笔记
触发器函数不是您使用创建的那种触发器CREATE TRIGGER。
trigcond()`函数 中包装的等式 不是查询优化器的第一类谓词。大多数优化无法处理可能在查询执行时打开和关闭的谓词，因此它们假定任何谓词 都是未知函数，而忽略它。这些优化可以使用触发的等式： `trigcond(*`X`*)
- 参考优化： 可用于构建 ， 或 表访问。
trigcond(*X*=*Y* [OR *Y* IS NULL])refeq_refref_or_null - 基于索引查找的子查询执行引擎： 可用于构造 或 访问。
trigcond(*X*=*Y*)unique_subqueryindex_subquery - 表条件生成器：如果子查询是多个表的联接，则将尽快检查触发条件。
当优化器使用触发条件创建某种基于索引查找的访问时（对于前面列表的前两项），对于条件关闭的情况，优化器必须具有回退策略。此后备策略始终是相同的：执行全表扫描。在 EXPLAIN输出中，回退显示Full scan on NULL key在 Extra列中：
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 ...) 现在表达可能会导致全表扫描（这是慢）时，它以前没有。这是为获得正确结果而付出的代价（触发条件策略的目标是提高合规性，而不是速度）。
对于多表子查询，执行NULL IN (SELECT ...)特别慢，因为联接优化器不会针对外部表达式为的情况进行优化NULL。它假定NULL左侧的子查询评估非常少见，即使有统计数据表明并非如此。另一方面，如果外部表达式可能是 NULL但实际上不是，则不会影响性能。
为了帮助查询优化器更好地执行查询，请使用以下建议：
-
声明一列，就
NOT NULL好像它确实是一样。通过简化色谱柱的条件测试，这也有助于优化程序的其他方面。 -
如果您不需要区分
NULL来自FALSE子查询的结果，你可以很容易地避免慢的执行路径。替换如下所示的比较：outer_expr [NOT] IN (SELECT inner_expr FROM ...)带有以下表达式：
(outer_expr IS NOT NULL) AND (outer_expr [NOT] IN (SELECT inner_expr FROM ...))然后
NULL IN (SELECT ...)永远不会进行评估，因为AND一旦表达式结果明确，MySQL就会停止评估 零件。另一种可能的重写：
[NOT] EXISTS (SELECT inner_expr FROM ... WHERE inner_expr=outer_expr)
在 subquery_materialization_cost_based 所述的标志optimizer_switch 系统变量使得能够在子查询物化和之间的选择控制 IN-到-EXISTS子查询变换。请参见 第8.9.2节“可切换的优化”。
