某些优化适用于使用IN(or =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_iand 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)

为简单起见，以下讨论假定一对外部和内部表达式值。

刚才描述的转换有其局限性。只有当我们忽略可能的NULL值时它才有效。也就是说，只要以下两个条件都为真 ， “下推”策略就有效：

当这些条件中的一个或两个都不成立时，优化就会更加复杂。

假设outer_expr已知那是一个非NULL值，但子查询不会生成满足 outer_expr= 的行inner_expr。然后 outer_expr IN (SELECT ...)评估如下：

在这种情况下，查找行的方法 不再有效。有必要寻找这样的行，但如果没有找到，还要寻找行 where is 。粗略地说，子查询可以转换成这样： outer_expr = inner_exprinner_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。根据NULL作为“未知值” 的 SQL 解释，应该评估为： NULL IN (SELECT inner_expr ...)

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

本质上，必须有不同的方法来执行子查询，具体取决于 的值 outer_expr。

outer_expr优化器选择 SQL 合规性而不是速度，因此它考虑 了以下可能性 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()函数中的等式不是查询优化器的一流谓词。大多数优化无法处理可能在查询执行时打开和关闭的谓词，因此它们假定 any 是未知函数并忽略它。这些优化可以使用触发的等式： 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 ...) 可能会导致全表扫描（这很慢），而以前不会。这是为正确结果付出的代价（触发条件策略的目标是提高合规性，而不是速度）。

For multiple-table subqueries, execution of NULL IN (SELECT ...) is particularly slow because the join optimizer does not optimize for the case where the outer expression is NULL. It assumes that subquery evaluations with NULL on the left side are very rare, even if there are statistics that indicate otherwise. On the other hand, if the outer expression might be NULL but never actually is, there is no performance penalty.

To help the query optimizer better execute your queries, use these suggestions:

系统变量的 subquery_materialization_cost_based 标志optimizer_switch 可以控制子查询物化和子查询转换之间的 IN选择EXISTS。请参阅 第 8.9.2 节，“可切换优化”。