Documentation Home

8.2.2.3 使用 EXISTS 策略优化子查询

某些优化适用于使用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并且 inner_expr不能 NULL

  • 您不需要区NULL分子 FALSE查询结果。如果子查询是子句中ORor AND表达式 的一部分WHERE,MySQL 假定您不关心。优化器注意到 不需要区分子查询结果的另一个实例是这个构造 NULLFALSE

    ... WHERE outer_expr IN (subquery)

    在这种情况下,该WHERE子句拒绝返回 或的行。 IN (subquery)NULLFALSE

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

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

  • NULL,如果 SELECT产生任何行,其中inner_exprNULL

  • FALSE,如果 SELECT只产生非NULL值或什么都不产生

在这种情况下,查找行的方法 不再有效。有必要寻找这样的行,但如果没有找到,还要寻找行 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变体。但是,它们在EXPLAIN输出中不可见,因此您必须使用EXPLAIN EXTENDED后跟SHOW WARNINGS(注意checking 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。根据NULL作为未知值 的 SQL 解释,应该评估为: NULL IN (SELECT inner_expr ...)

  • NULL,如果 SELECT产生任何行

  • FALSE,如果不 SELECT产生任何行

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

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

outer_expr优化器选择 SQL 合规性而不是速度,因此它考虑 了以下可能性 NULL

  • 如果outer_exprNULL,要评估以下表达式,有必要执行 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不是 NULL

  • TRUE链接 外部表达式oe_iNULL

笔记

触发器函数不是您使用创建的那种触发器CREATE TRIGGER

包装在 trigcond()函数中的等式不是查询优化器的一流谓词。大多数优化无法处理可能在查询执行时打开和关闭的谓词,因此它们假定 any 是未知函数并忽略它。这些优化可以使用触发的等式: trigcond(X)

  • 引用优化: 可用于构造 、 或 表访问。 trigcond(X=Y [OR Y IS NULL])refeq_refref_or_null

  • 基于索引查找的子查询执行引擎: 可用于构造 或 访问。 trigcond(X=Y)unique_subqueryindex_subquery

  • 表条件生成器:如果子查询是多个表的join,则尽快检查触发条件。

当优化器使用触发条件创建某种基于索引查找的访问时(对于前面列表的前两项),它必须有一个回退策略来应对条件关闭的情况。这种回退策略总是相同的:进行全表扫描。在 EXPLAIN输出中,回退显示Full scan on NULL keyExtra列中:

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 EXTENDED 后跟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但实际上从未是,则没有性能损失。

为了帮助查询优化器更好地执行您的查询,请使用以下建议:

  • NOT NULL像真的 一样声明一个列。通过简化列的条件测试,这也有助于优化器的其他方面。

  • 如果您不需要区NULL分子 FALSE查询结果,则可以轻松避免缓慢的执行路径。替换如下所示的比较:

    outer_expr IN (SELECT inner_expr FROM ...)

    用这个表达式:

    (outer_expr IS NOT NULL) AND (outer_expr IN (SELECT inner_expr FROM ...))

    ThenNULL IN (SELECT ...)永远不会被评估,因为 AND一旦表达式结果明确,MySQL 就会停止评估部分。

    另一种可能的重写:

    EXISTS (SELECT inner_expr FROM ...
            WHERE inner_expr=outer_expr)

    这适用于不需要区 NULL分子FALSE 查询结果的情况,在这种情况下您可能确实需要 EXISTS.

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