某些优化适用于使用 IN (或 =ANY) 运算符测试子查询结果的比较。这一节讨论这些优化，特别是关于 NULL 值带来的挑战。最后一部分讨论如何帮助优化器。

考虑以下子查询比较：

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

MySQL 从外部到内部评估查询。也就是说，它首先获取外部表达式 outer_expr 的值，然后运行子查询并捕获它产生的行。

一个非常有用的优化是将适当的相等条件“推送”到子查询的 WHERE 子句中，以使其更加 restrict。转换后的比较看起来像这样：

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 输出中，备用策略显示为 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 但实际上从不为 NULL，那么就没有性能损失。

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

subquery_materialization_cost_based 标志控制 optimizer_switch 系统变量中的子查询物化成本基于选择之间的选择。请参阅 第 10.9.2 节，“可切换优化”。