mysql 翻译系列 二十三

10.2.2.3 使用EXISTS策略优化子查询

某些优化方法适用于使用IN（或=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值行的子查询进行比较时，也可以进行相同的转换。如果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。
• 无需区分子查询结果中的NULL和FALSE。如果子查询是WHERE子句中OR或AND表达式的一部分，MySQL会认为你对此并不在意。优化器注意到无需区分NULL和FALSE子查询结果的另一种情况是这种结构：

... WHERE outer_expr IN (subquery)

在这种情况下，无论IN (subquery)返回NULL还是FALSE，WHERE子句都会排除该行。

假设已知outer_expr为非NULL值，但子查询没有产生outer_expr = inner_expr的行。那么outer_expr IN (SELECT ...)的评估结果如下：

• 如果SELECT产生了任何inner_expr为NULL的行，则结果为NULL。
• 如果SELECT只产生非NULL值或没有产生任何行，则结果为FALSE。

在这种情况下，查找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产生了任何行，则结果为NULL。
• 如果SELECT没有产生任何行，则结果为FALSE。

为了正确评估，必须能够检查SELECT是否产生了任何行，因此outer_expr = inner_expr不能下推到子查询中。这是一个问题，因为在很多实际的子查询中，除非能下推等值条件，否则查询会变得非常慢。

本质上，必须根据outer_expr的值采用不同的方式来执行子查询。

优化器优先考虑SQL的合规性而非速度，因此它会考虑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)都是一个特殊函数，其求值结果如下：

• 当 “关联” 的外部表达式oe_i不为NULL时，结果为X。
• 当 “关联” 的外部表达式oe_i为NULL时，结果为TRUE。
  注意：触发函数并非使用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输出中，回退策略会在Extra列中显示为Full scan on 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后再运行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，则不会有性能损失。

为了帮助查询优化器更好地执行查询，可以参考以下建议：

• 如果某列确实不能为空，将其声明为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 ...)永远不会被评估，因为一旦表达式结果确定，MySQL就会停止评估AND的各个部分。

• 另一种可能的重写方式：

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

optimizer_switch系统变量的subquery_materialization_cost_based标志可以控制在子查询物化和IN到EXISTS子查询转换之间进行选择。请参见10.9.2节 “可切换的优化”。