考虑下面的子查询：

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)

为了简单，接下来的讨论假设外层和内层都是单个值，前面讨论的转换是有限制条件的，只有当我们忽略NULL值才行，推入策略只有当满足下面的两个条件后才能生效：

outer_expr 和 inner_expr都不能为NULL

你也不需要区分子查询的NULL和FALSE，（如果子查询是WHERE从句的OR 或AND表达式的一部分，MySQL假设你不在意）。

当上述条件的一个或者都不满足时，优化变得复杂，假设outer_expr是一个非NULL值，但是子查询没有产生满足outer_expr = inner_expr的行，outer_expr IN (SELECT ...)就按照下面的规则求值：

·NULL, 如果SELECT产生inner_expr = NULL任意行
· FALSE, 如果SELECT仅产生非NULL或者没有任何输出。

这种情况， 使用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的访问方式。

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子查询访问方式都有“or NULL”的变种，然而他们是不会显示在EXPLAIN的输出中的， 你必须先执行EXPLAIN EXTENED然后执行SHOW WARNINGS（注意警告信息中的检查NULL）

mysql> EXPLAIN EXTENDED
-> SELECT outer_expr IN (SELECT maybe_null_key FROM t2) 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: maybe_null_key
key: maybe_null_key
key_len: 5
ref: func
rows: 2
Extra: Using index

mysql> SHOW WARNINGS\G

*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select (`test`.`t1`.`outer_expr`,
(((`test`.`t1`.`outer_expr`) in t2 on
maybe_null_key checking NULL))) AS `outer_expr IN (SELECT
maybe_null_key FROM t2)` from `test`.`t1`

额外的 OR ... IS NULL 条件使查询执行更复杂（一些适用于子查询的优化策略变得不再可用），但是一般这个是可以忍受的。
情况变得更加糟糕当 outer_expr 也能为NULL的时候，根据SQL的规范，NULL是一个未知的值，NULL IN (SELECT inner_expr ...)将被评价为：

·? NULL, 如果SELECT产生任意的行。
·??FALSE,SELECT没有产生任何行。

为了合适的求值，需要检测是否SELECT能产生行，所以outer_expr = inner_expr不能推入到子查询。这是一个问题，现实中子查询变得非常慢除非能将相等条件推入子查询。

本质上，根据outer_expr不同的值使用不同的方式来执行子查询，优化器优先选择遵照SQL标准而不是速度，所以它考虑outer_expr 可能为NULL的情况。如果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为NOT NULL
TRUE: oe_i为NULL时
注意此处的触发器函数和我们使用CREATE TRIGGER创建的不是一个类型的。

对于优化器来说trigcond()中的相等判断不是第一优先级的谓词，许多优化不能处理在执行时可以关闭和开启的谓词的，所以他们假设所有的trigcond为一个未知的函数且忽略它，此时，触发的相等性判断可以被优化器用于：

• ref类型优化，trigcond（X=Y OR Y IS NULL）能用于构造ref, eq_ref或者REF_OR_NULL表访问方式。
• 索引基于的子查询执行引擎，trigcond（X=Y）能用于构造unique_subquery 或index_subquery访问方式。
• 表条件产生器：如果子查询是多个表的join，触发条件将尽快执行。当优化器使用触发条件来构造索引基于的访问（上面列表的前两个条目），它必须有一个失败策略来判断当条件关闭。这个失败策略永远是一样的。执行一个全表扫描， 在EXPLAIN的输出中，失败显示为Extra列显示在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 EXTENED后执行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 ...)现在可能引起全表扫描（慢）。它以前不必，这是为了正确的结果的代价，（trigcond策略的目的是为了提高SQL规范的服从性而不是速度）

对于多表的子查询，执行NULL IN (SELECT ...)非常慢因为join优化器当外层表达式为NULL时不会优化，它假设左边为NULL的子查询求值很罕见，即使有统计显示不是这样，另一方面，如果外层表达式可能为NULL但是从来没有，也不会有性能的损耗。

为了帮助查询优化器能更好的执行你的查询，使用这些提示：

• 声明列为NOT NULL如果他们不会取NULL，（这还会帮助优化器的其他方面通过简化列的条件测试）
• 如果你不需要区分子查询结果的NULL和false,你可以很容易的避免蛮执行路径，想这样改写
• outer_expr IN (SELECT inner_expr FROM ...)

为:

(outer_expr IS NOT NULL) AND (outer_expr IN (SELECT inner_expr FROM ...))
然后 NULL IN (SELECT ...)将永远不需要执行因为MySQL停止AND求值当表达式的结果确定后。
subquery_materialization_cost_based控制子查询的materialization 策略和（in->exists）转变策略的选择，默认ON

参考文档

http://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html

Posted in: MySQL practise | Tags: Exists, MySQL, SubQuery