某些优化适用于使用 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 值行的子查询进行比较也受相同的转换约束。如果 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 已知为非 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 时，情况会糟糕得多。根据 SQL 将 NULL 解释为 “未知值,”，NULL IN (SELECT inner_expr ...) 应该评估为：

为了进行正确的评估，需要能够检查 SELECT 是否产生了任何行，因此不能将 outer_expr = inner_expr 下推到子查询中。这是一个问题，因为除非可以下推相等性，否则许多现实世界中的子查询会变得非常慢。

本质上，必须根据 outer_expr 的值采用不同的方式来执行子查询。

优化器选择 SQL 兼容性而不是速度，因此它考虑了 outer_expr 可能为 NULL 的可能性。

为了解决是否将条件下推到子查询中的困境，条件被包装在 ““触发器” 函数中。因此，以下形式的表达式：

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) 都是一个特殊函数，其评估结果如下：

包装在 trigcond() 函数中的相等性不是查询优化器的一级谓词。大多数优化无法处理在查询执行时可能打开和关闭的谓词，因此它们假设任何 trigcond(X) 都是未知函数并忽略它。触发的相等性可用于以下优化：

当优化器使用触发条件来创建某种基于索引查找的访问（如前面列表中的前两项）时，它必须针对条件关闭的情况制定回退策略。此回退策略始终相同：执行全表扫描。在 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 ...) 表达式现在可能会导致全表扫描（速度较慢）。这是为了获得正确结果而付出的代价（触发条件策略的目标是提高兼容性，而不是速度）。

对于多表子查询，NULL IN (SELECT ...) 的执行速度特别慢，因为联接优化器没有针对外层表达式为 NULL 的情况进行优化。它假设左侧为 NULL 的子查询评估非常少见，即使有统计数据表明并非如此。另一方面，如果外层表达式可能为 NULL 但实际上从不为 NULL，则不会有任何性能损失。

为了帮助查询优化器更好地执行您的查询，请使用以下建议：

subquery_materialization_cost_based 标志（属于 optimizer_switch 系统变量）支持控制子查询物化和 IN 到 EXISTS 子查询转换之间的选择。请参阅第 10.9.2 节 “可切换优化”。