• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • 使用半联接转换优化 IN 和 EXISTS 子查询

    MySQL查询优化器有多种策略可用于评估子查询:

    • 对于具有使用子查询IN= ANYEXISTS谓词,优化器具有以下选择:

      • 半连接
      • 物化
      • EXISTS战略
    • 对于一个使用子查询NOT IN<> ALLNOT EXISTS谓语,优化有以下选择:

      • 物化
      • EXISTS战略

    对于派生表,优化器具有以下选择(这也适用于视图引用和公用表表达式):

    • 将派生表合并到外部查询块中
    • 将派生表具体化为内部临时表

    以下讨论提供了有关前面的优化策略的更多信息。

    注意

    使用子查询修改单个表的UPDATEDELETE语句的限制是,优化器不使用半联接或实现子查询优化。解决方法是,尝试将它们重写为使用联接而不是子查询的多表UPDATEDELETE语句。

    半联接是准备时转换,它启用多种执行策略,例如表提取,重复删除,首次匹配,松散扫描和实现。如本节所述,优化器使用半联接策略来改善子查询的执行。

    对于两个表之间的内部联接,该联接从一个表返回一行的次数是另一表中存在匹配项的次数。但是对于某些问题,唯一重要的信息是是否存在匹配项,而不是匹配项的数量。假设在课程表和班级名册(每个班级都有学生)中分别有命名的表classroster列出的班级。要列出实际招收学生的课程,您可以使用以下联接:

    SELECT class.class_num, class.class_name
        FROM class
        INNER JOIN roster
        WHERE class.class_num = roster.class_num;
    

    但是,结果为每个注册学生列出一次每个班级。对于所提出的问题,这是不必要的信息重复。

    假设它class_numclass表中的主键,则可以通过使用来抑制重复SELECT DISTINCT,但是首先生成所有匹配的行仅是为了稍后消除重复,效率低下。

    可以使用子查询获得相同的无重复结果:

    SELECT class_num, class_name
        FROM class
        WHERE class_num IN
            (SELECT class_num FROM roster);
    

    在这里,优化器可以识别出该IN子句要求子查询仅返回roster表中每个类编号的一个实例。在这种情况下,查询可以使用半联接;也就是说,一种操作仅返回的每行一个实例,该实例class与中的行匹配roster

    包含EXISTS子查询谓词的以下语句与包含子查询谓词的前一条语句等效IN

    SELECT class_num, class_name
        FROM class
        WHERE EXISTS
            (SELECT * FROM roster WHERE class.class_num = roster.class_num);
    

    在MySQL 8.0.16及更高版本中,任何带有EXISTS子查询谓词的语句都应与带有等效IN子查询谓词的语句经受相同的半联接转换。

    从MySQL 8.0.17开始,以下子查询转换为反联接:

    • NOT IN(SELECT ... FROM ...)
    • NOT EXISTS(SELECT ... FROM ...)
    • IN(SELECT ... FROM ...)IS NOT TRUE
    • EXISTS(SELECT ... FROM ...)IS NOT TRUE
    • IN(SELECT ... FROM ...)IS FALSE
    • EXISTS(SELECT ... FROM ...)IS FALSE

    简而言之,形式为IN(SELECT ... FROM ...)或的子查询的任何否定EXISTS(SELECT ... FROM ...)都将转换为反联接。

    反联接是仅返回没有匹配项的行的操作。考虑下面显示的查询:

    SELECT class_num, class_name
        FROM class
        WHERE class_num NOT IN
            (SELECT class_num FROM roster);
    

    该查询在内部重写为反连接SELECT class_num, class_name FROM class ANTIJOIN roster ON class_num,它返回在每行的一个实例class,其以任何行匹配roster。这意味着,对于中的每一行class,只要在中找到匹配项rosterclass就可以丢弃其中的行。

    如果要比较的表达式可以为空,则在大多数情况下不能应用反连接转换。该规则的一个例外是它(... NOT IN(SELECT ...))IS NOT FALSE及其等效项(... IN(SELECT ...))IS NOT TRUE可以转换为反联接。

    外部查询规范中允许使用外部联接和内部联接语法,并且表引用可以是基表,派生表,视图引用或公共表表达式。

    在MySQL中,子查询必须满足以下条件才能作为半联接(或在MySQL 8.0.17及更高版本中,如果NOT修改了子查询,则为反联接):

    • 它必须是一个的一部分IN= ANYEXISTS谓词出现在顶层WHEREON条款,可能作为在一个术语AND表达。例如:

      SELECT ...
          FROM ot1, ...
          WHERE (oe1, ...) IN
              (SELECT ie1, ... FROM it1, ... WHERE ...);
      

      在此,和分别代表查询的外部和内部部分中的表,以及和代表引用外部和内部表中的列的表达式。ot_iit_ioe_iie_i

      在MySQL 8.0.17和以后,子查询也可以是参数通过修改的表达NOTIS[NOT] TRUEIS[NOT] FALSE

    • 它必须是SELECT没有UNION构造的单个。
    • 它不能包含HAVING子句。
    • 它不能包含任何聚合函数(无论是显式还是隐式分组)。
    • 它不能有LIMIT子句。
    • 该语句不得STRAIGHT_JOIN在外部查询中使用联接类型。
    • STRAIGHT_JOIN修改必须不存在。
    • 外部和内部表的总数必须小于联接中允许的最大表数。
    • 子查询可以是相关的或不相关的。在MySQL 8.0.16及更高版本中,去相关性会在WHERE用作参数的子查询的子句中参见琐碎相关的谓词EXISTS,并使其能够像在内一样进行优化IN(SELECT b FROM ...)。术语“琐碎相关”表示谓词是相等谓词,它是WHERE子句中唯一的谓词(或与组合AND),并且一个操作数来自子查询中引用的表,另一个操作数来自外部查询块。
    • DISTINCT关键字是允许的,但忽略。半联接策略自动处理重复项删除。
    • GROUP BY子句允许的,但忽略,除非子查询还包含一个或多个聚合函数。
    • 一个ORDER BY条款是允许的,但忽略,因为排序是无关的半连接策略的评价。

    如果子查询满足上述条件,MySQL会将其转换为半联接(或者,在适用于MySQL 8.0.17或更高版本的情况下,为反联接),并从以下策略中选择基于成本的选择:

    • 将子查询转换为联接,或使用表提取,并将查询作为子查询表与外部表之间的内部联接运行。表提取将表从子查询中拉出到外部查询。
    • Duplicate Weedout:像运行半连接一样运行半连接,并使用临时表删除重复的记录。
    • FirstMatch:当扫描内部表以查找行组合并且给定值组有多个实例时,请选择一个而不是全部返回。这种“快捷方式”扫描可以消除不必要行的产生。
    • LooseScan:使用索引扫描子查询表,该索引允许从每个子查询的值组中选择一个值。
    • 将子查询具体化到用于执行联接的索引临时表中,该索引用于删除重复项。当将临时表与外部表连接时,该索引以后也可能用于查找。如果不是,则扫描表。有关实现的更多信息,请参见“通过实现优化子查询”。

    可以使用以下optimizer_switch系统变量标志来启用或禁用这些策略中的每一个:

    • semijoin标志控制是否使用半联接。从MySQL 8.0.17开始,这也适用于防联接。
    • 如果semijoin使能,firstmatchloosescanduplicateweedout,和materialization标志enable更好地控制在允许的半连接策略。
    • 如果duplicateweedout禁用了半连接策略,则除非所有其他适用策略也都被禁用,否则将不使用它。
    • 如果duplicateweedout已禁用,则有时优化器可能会生成远非最佳的查询计划。发生这种情况的原因是贪婪搜索期间的启发式修剪,可以通过设置来避免optimizer_prune_level=0

    默认情况下启用这些标志。请参见“可切换的优化”。

    优化器将视图和派生表的处理差异最小化。这会影响使用STRAIGHT_JOIN修饰符的IN查询以及带有可转换为半联接的子查询的视图。以下查询说明了这一点,因为处理中的更改导致转换中的更改,从而导致不同的执行策略:

    CREATE VIEW v AS
    SELECT *
    FROM t1
    WHERE a IN (SELECT b
               FROM t2);
    
    SELECT STRAIGHT_JOIN *
    FROM t3 JOIN v ON t3.x = v. ;
    

    优化器首先参见视图,然后将IN子查询转换为半联接,然后检查是否有可能将视图合并到外部查询中。因为STRAIGHT_JOIN外部查询中的修饰符防止半联接,所以优化程序拒绝合并,从而导致使用物化表进行派生表评估。

    EXPLAIN输出表明使用了半连接策略,如下所示:

    • 对于扩展EXPLAIN输出,以下内容显示的文本显示SHOW WARNINGS了重写的查询,该查询显示了半联接结构。(请参见“扩展的EXPLAIN输出格式”。)由此,您可以了解哪些表从半联接中被拉出。如果将子查询转换为半联接,则将看到该子查询谓词已消失,并且其表和WHERE子句已合并到外部查询联接列表和WHERE子句中。
    • 对于重复Weedout临时表的使用是由指示Start temporaryEnd temporaryExtr 列。那些没有拉出表是在范围内EXPLAIN所涵盖的输出行Start temporary,并End temporary有自己rowid的临时表。
    • FirstMatch(tbl_name)Extr 列表示加入shortcutting。
    • LooseScan(m..n)Extr 列指示使用LooseScan策略。mn是关键部件号。
    • 用于实现的临时表由select_type值为的MATERIALIZED行和的table值为的行指示。<subqueryN>