mysql5.7官网直译SQL语句优化--使用存在策略来优化子查询

2.4 Optimizing Subqueries with the EXISTS Strategy 使用存在策略来优化子查询。
一些优化是适用于对比操作的使用在IN(或者=ANY)操作去测试子查询结果。这部分讨论这些优化，特别是考虑NULL值存在的挑战。后续讨论的部分建议你怎么样帮助优化器。
考虑如下的子查询对比：
outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)
mysql评估查询从外到内。也就是说，它首先获取在外部表达式outer_expr的值，然后运行子查询并且抓取其产生的行数据。
一个非常有用的悠哈是去通知子查询，只有内部表达式inner_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假设你并不关心。另一个例子其中优化器注意到NUll和FALSE结果在子查询中不需要被区分是这样的结构：
... WHERE outer_expr IN (subquery)
 在这种情况下，where条件会拒绝在IN(子查询)返回null或者false的行。
 当上述一个或者两个条件都不满足，优化器会更加复杂。
 假设outer_expr是不为null的但是子查询并没有产生和内部条件相匹配的行数据。那么outer_expr IN (select...) 评估如下：
 1）NUll,如果SELECT 产生任何行其中inner_expr是null.
 2) FALSE,如果SELECT 产生只有非NULL值或者什么也不做。
 在这种情况下，关于查找outer_expr=inner_exper的行的捷径不能再用了。它必须去扫描这些行，即使什么也没有，就算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各自独特的子查询方法也有对or null的变体。
额外的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的可能性：
1）如果outer_expr是NULL,为了评估下面的表达式，它必须去执行SELECT去决定是否生成任何行数据：
NULL IN (SELECT inner_expr FROM ... WHERE subquery_where)
在这种情况下，执行原始SELECT是必须的，没有任何等价条件可以提前压入帮助查询。
2）另一方面，当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 当“linked"外部表达式oe_i是非null值的情况。
>TRUE 当“linked"外部表达式oe_i是null值的情况。
  注意：
  触发函数并不是你通过create Trigger生成的那种触发函数。
相等的被包装在trigcond()函数内部的并是不是第一类的断言对于查询优化器。大多数优化器不能处理断言，可能开或和关查询的执行时间，所以他们假设任何trigcond(x) 被看作是未知函数并且忽略它。被触发 等价可以被用于这些优化：
>引用优化：trigcond(X=Y[OR Y IS NULL])能够被用于转换ref,eq_ref,或者是ref_or_null表查询。
>基于索引子查询的执行引擎：trigcond(x=y)能够被用于转换unique_subquery或者index_subquery查询。
>表条件生成器：如果子查询是一个连接多个表，触发条件会尽快被检查。
当优化器使用触发条件去创建一些索引查询方法(就像先前列出的第一二系列)，它必须有一个回退策略当调剂是关闭的情况下使用。这个准备策略总是一样的:做一个全表扫描。在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但事实上不可能，这没有性能坏处。


为了帮助查询优化器更好的执行你的查询，使用这些建议：
1）申明一列是NOT NULL的如果他不能为null的话。这也从其他反面帮助优化器对条件检测的简单性
2）如果你不需要区别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部分如果表达式结果是空的话。
另一个可能的重写是：
EXISTS (SELECT inner_expr FROM ...
        WHERE inner_expr=outer_expr)
optimizer_switch系统变量的subquery_materialization_cost_based标记能够控制上面的选择是子查询物化还是IN-TO-EXISTS子查询转换。具体请看8.9.3的优化开关。
到这里关于通过EXISTS策略来优化子查询就结束了。接下来我们将介绍关于8.3Optimization and Indexes 优化和索引