• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • 使用 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-value行的子查询进行比较将进行相同的转换。如果oe_iie_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
    • 你不必区分NULLFALSE子查询结果。如果子查询是子句中ORor AND表达式的一部分WHERE,则MySQL认为您不在乎。在优化程序注意到另一个实例NULLFALSE子查询结果不需要区分是这样的结构:

      ... WHERE outer_expr IN (subquery)
      

      在这种情况下,该WHERE子句拒绝该行,无论是return 还是。IN(subquery)NULLFALSE

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

    • NULL中,如果SELECT产生任何行,其中inner_exprNULL
    • FALSE,如果SELECT仅产生非NULL值或什么都不产生

    在这种情况下,使用查找行的方法不再有效。有必要查找这样的行,但是如果找不到,则还要在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
            Extr 	: Using where; Using index
    ...
    

    unique_subqueryindex_subquery子查询,具体的访问方法也有“或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

    • 如果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()函数中包装的等式不是查询优化器的第一类谓词。大多数优化无法处理可能在查询执行时打开和关闭的谓词,因此它们假定任何谓词都是未知函数,而忽略它。那些优化可以使用触发的等式:trigcond(X)

    • 参考优化:可用于构建,或表访问。trigcond(X=Y[OR Y IS NULL])refeq_refref_or_null
    • 基于索引查找的子查询执行引擎:可用于构造或访问。trigcond(X=Y)unique_subqueryindex_subquery
    • 表条件生成器:如果子查询是多个表的联接,则将尽快检查触发条件。

    当优化器使用触发条件创建某种基于索引查找的访问时(对于前面列表的前两项),对于条件关闭的情况,优化器必须具有回退策略。此后备策略始终相同:执行全表扫描。在EXPLAIN输出中,回退显示Full scan on NULL keyExtr 列中:

    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
            Extr 	: 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但实际上不是,则不会影响性能。

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

    • 声明一列,就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 ...)永远不会进行评估,因为AND一旦表达式结果明确,MySQL就会停止评估零件。

      另一种可能的重写:

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

    subquery_materialization_cost_based所述的标志optimizer_switch系统变量使得能够在子查询物化和之间的选择控制IN-到-EXISTS子查询变换。请参见“可切换的优化”。