某些优化适用于使用IN(或=ANY)来测试子查询结果的比较，特别是关于NULL带来的。讨论的最后一部分建议如何优化。

以下两个表数据基本一致都在一万左右

考虑以下子查询比较：

SELECT
	* 
FROM
	retired_cadre_base 
WHERE
	cardre_id IN (
	SELECT
		cardre_id 
	FROM
		retired_cadre_family 
	WHERE
	post_code = '317000')

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

这里的优化可以是赋连接条件于子查询，只有内部表达式inner_expr等于outer_expr的行才是所关注的。这是通过向子查询的WHERE子句下推一个适当的相等项来实现的，限制查询范围。转换后的SQL语句如下所示:

SELECT
	* 
FROM
	retired_cadre_base a 
WHERE
	EXISTS (
	SELECT
		1 
	FROM
		retired_cadre_family b 
	WHERE
	post_code = '317000' 
	AND a.cardre_id = b.cardre_id)

转换之后，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和inner_expr都不能为NULL。

如果该子查询是WHERE子句中的OR或AND的组成部分，则MySQL假设你不会在意。因此你不必区分子查询结果是NULL还是FALSE。优化器注意到另一种不必区分子查询结果是NULL或FALSE的实例，请看如下构建的条件：

... WHERE outer_expr IN (subquery)

在这种情况下，WHERE子子句都会拒绝不管IN (subquery)返回NULL还是FALSE时的行。

假设已知outer_expr的值是非空的，但是，子查询不能产生符合outer_expr = inner_expr的行。那么，outer_expr IN (SELECT inner_expr …)计算结果如下：

如果SELECT产生的任何行的条件是inner_expr是NULL，那么以上表达式计算结果是NULL

如果SELECT产生的只有非空值或什么也没有产生，那么以上表达式计算结果是FALSE

在这种情况下，寻找符合outer_expr = inner_expr的方法已无效。虽然寻找这样的行是必要的，但是如果找不到，那么也要寻找符合inner_expr is NULL的行。粗略地说，该子查询可以被转换成以下形式：

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND
        (outer_expr = inner_expr OR inner_expr IS NULL))

需要额外计算的IS NULL条件就是为什么MySQL有ref_or_null（引用或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             # 引用或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（索引子查询）也有 “or NULL”变体。

另外，条件OR … IS NULL使查询稍微复杂一些（且子查询中的一些优化变得不可用），但是，通常这是可接受的。

当outer_expr（外部表达式）也可以是NULL时，情况会更糟。按照SQL会把NULL解析为“未知值（unkown value）”这种逻辑，那么，对NULL IN (SELECT inner_expr …)的计算应该按照如下规则进行：

如果SELECT产生任何行，则NULL

如果SELECT没有产生行，则FALSE

为了进行正确地计算，必须要能够检查SELECT究竟是否已经产生了任何行，所以，不能把 outer_expr = inner_expr下推到子查询中。这是许多真实世界中子查询变得非常慢的原因之一，除非该等式能被下推。

本质上，根据外部表达式（outer_expr）的值，必须有不同的执行子查询的方法。

优化器选择了SQL遵从而不是速度，因此它考虑了外部表达式可能是NULL的可能性：

如果外部表达式是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)

如果没有这种转换，则子查询执行速度是慢的。

为了解决是否要下推条件到子查询中这样的困境，需要使用“触发器(trigger)”函数把这些条件包裹起来。因此，使用以下形式的表达式：

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

转换为：

EXISTS (SELECT 1 
				FROM ... 
				WHERE subquery_where
                         AND trigcond(outer_expr = inner_expr))   # trigcond: 条件触发

更一般地说，如果子查询的比较是基于几对外部和内部表达式，那么采取以下转换进行比较：

(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)都是计算以下值的特殊函数：

当“连接”的外部表达式oe_i不是NULL时，结果是X

当“连接”的外部表达式oe_i是NULL时，结果是TRUE

注意：
这里的触发器函数不是使用CREAT TRIGGER创建的触发器。

包裹在trigcond()函数中的等式并不是查询优化器的头等谓词。大多数优化器不能处理这种在查询执行期间可以开和关的谓词，因此它们假设任何trigcond()都是未知函数而忽略它。触发的等式可以在以下优化中使用：

引用优化：可以使用 trigcond(X = Y [OR Y IS NULL])来构造ref、eq_ref或ref_or_null表的访问。

基于索引查找的子查询执行引擎：trigcond(X = Y)可用于构建唯一子查询或索引子查询访问。

表条件生成器：如果子查询是多个表的连接，则应该尽快检查该触发的条件。

当优化器使用触发条件来创建某种基于索引查找的访问时（就像上面列表中的前两项一样），它必须有一个回退策略，以应对出现条件关闭时的情形。这种回退策略始终是一样的：即做全表扫描。在EXPLAIN输出的Extra列中，回退显示为Full scan on NULL key（在NULL键上进行全表扫描）：

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的情况不能优化，因此，NULL IN (SELECT …)的执行特别慢。即使有统计数据表明不是这样，它还是假设左侧为NULL的子查询计算非常稀少。另一方面，如果外部表达式可能为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 …)永远不会执行，因为一旦此表达式结果已经明确（译者：如果outer_expr是NULL，则(outer_expr IS NOT NULL)就是(NULL IS NOT NULL)，此表达式整体计算结果是FALSE**，也就是通过如此转换，如果子查询返回NULL就转换成FALSE），MySQL就会立即停止计算AND部分。

另一种可能的重写：

[NOT] EXISTS (SELECT inner_expr FROM ...
        WHERE inner_expr = outer_expr)

optimizer_switch（优化器切换）系统变量的subquery_materialization_cost_based（基于子查询物化成本）标志控制着选择那一种策略：子查询物化，还是，IN-to-EXISTS子查询转换。