MySQL :: MySQL 9.0 参考手册 :: 10.2.2.1 使用半连接和反连接转换优化 IN 和 EXISTS 子查询谓词

版本 9.0

8.4 当前

MySQL 9.0 参考手册 / ... / 使用半连接和反连接转换优化 IN 和 EXISTS 子查询谓词

10.2.2.1 使用半连接和反连接转换优化 IN 和 EXISTS 子查询谓词

半连接是一种准备时间转换，它允许使用多种执行策略，例如表提取、重复消除、首匹配、松散扫描和物化。优化器使用半连接策略来改进子查询执行，如本节所述。

对于两个表之间的内连接，连接会根据另一个表中的匹配次数返回来自一个表的行。但对于某些问题，唯一重要的是是否存在匹配，而不是匹配的次数。假设有两个表，名为 class 和 roster，分别列出课程的课程安排和班级名册（每个班级的注册学生）。要列出实际有学生注册的课程，可以使用以下连接

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

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

假设 class_num 是 class 表中的主键，可以通过使用 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);

任何包含 EXISTS 子查询谓词的语句都将受到与包含等效 IN 子查询谓词的语句相同的半连接转换的影响。

以下子查询将转换为反连接

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 中的每一行，一旦在 roster 中找到匹配项，就可以丢弃 class 中的行。

如果被比较的表达式是可为空的，则在大多数情况下无法应用反连接转换。该规则的例外是 (... NOT IN (SELECT ...)) IS NOT FALSE 及其等效形式 (... IN (SELECT ...)) IS NOT TRUE 可以转换为反连接。

外连接和内连接语法在外部查询规范中是允许的，表引用可以是基本表、派生表、视图引用或公用表表达式。

在 MySQL 中，子查询必须满足以下条件才能被视为半连接（或者如果 NOT 修改子查询，则视为反连接）

它必须是 WHERE 或 ON 子句顶层出现的一个 IN、= ANY 或 EXISTS 谓词的一部分，可能是一个 AND 表达式中的项。例如
```
SELECT ...
    FROM ot1, ...
    WHERE (oe1, ...) IN
        (SELECT ie1, ... FROM it1, ... WHERE ...);
```
这里，ot_i 和 it_i 代表查询外部和内部部分的表，oe_i 和 ie_i 代表引用外部和内部表中列的表达式。
子查询也可以作为被 NOT、IS [NOT] TRUE 或 IS [NOT] FALSE 修饰的表达式的参数。
它必须是一个单独的 SELECT 语句，不包含 UNION 结构。
它不能包含 HAVING 子句。
它不能包含任何聚合函数（无论它是否显式或隐式分组）。
它不能包含 LIMIT 子句。
该语句不能在外部查询中使用 STRAIGHT_JOIN 连接类型。
不能存在 STRAIGHT_JOIN 修饰符。
外部表和内部表的数量总和必须小于连接中允许的最大表数。
子查询可以是关联的或非关联的。去关联操作会查看作为 EXISTS 参数使用的子查询的 WHERE 子句中简单的关联谓词，并使其可以像在 IN (SELECT b FROM ...) 中使用一样进行优化。术语 简单的关联 意味着谓词是等值谓词，它是 WHERE 子句中唯一的谓词（或与 AND 结合使用），并且一个操作数来自子查询中引用的表，另一个操作数来自外部查询块。
允许使用 DISTINCT 关键字，但会被忽略。半连接策略会自动处理重复删除。
允许使用 GROUP BY 子句，但会被忽略，除非子查询也包含一个或多个聚合函数。
允许使用 ORDER BY 子句，但会被忽略，因为排序与半连接策略的评估无关。

如果子查询满足前面的条件，MySQL 会将其转换为半连接（如果适用，则转换为反连接），并在这两种策略中选择一种基于成本的策略。

将子查询转换为连接，或使用表拉出并将查询作为子查询表和外部表之间的内部连接运行。表拉出将表从子查询中拉到外部查询中。
重复去除：像连接一样运行半连接，并使用临时表删除重复记录。
首个匹配：当扫描内部表以查找行组合并且存在给定值组的多个实例时，选择一个而不是返回所有实例。这将“缩短”扫描并消除不必要行的生成。
松散扫描：使用索引扫描子查询表，该索引允许从子查询的每个值组中选择一个值。
将子查询物化到一个索引化的临时表中，用于执行连接，其中索引用于删除重复项。索引也可以稍后用于在将临时表与外部表连接时进行查找；如果没有，则会扫描该表。有关物化的更多信息，请参见第 10.2.2.2 节，“使用物化优化子查询”。

可以使用以下 optimizer_switch 系统变量标志启用或禁用这些策略。

semijoin 标志控制是否使用半连接和反连接。
如果启用了 semijoin，则 firstmatch、loosescan、duplicateweedout 和 materialization 标志可以更精细地控制允许的半连接策略。
如果禁用了 duplicateweedout 半连接策略，则除非所有其他适用的策略也被禁用，否则它不会被使用。
如果禁用了 duplicateweedout，有时优化器可能会生成一个远非最佳的查询计划。这是由于贪婪搜索过程中的启发式修剪造成的，可以通过设置 optimizer_prune_level=0 来避免这种情况。

这些标志默认情况下是启用的。参见第 10.9.2 节，“可切换优化”。

优化器最大程度地减少了处理视图和派生表的差异。这会影响使用 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.a;

优化器首先查看视图并将 IN 子查询转换为半连接，然后检查是否可以将视图合并到外部查询中。因为外部查询中的 STRAIGHT_JOIN 修饰符阻止了半连接，所以优化器拒绝合并，导致使用物化表进行派生表评估。

EXPLAIN 输出指示使用半连接策略的方式如下。

对于扩展的 EXPLAIN 输出，以下 SHOW WARNINGS 显示的文本将显示重写的查询，该查询显示半连接结构。（参见第 10.8.3 节，“扩展 EXPLAIN 输出格式”。）从这里可以了解哪些表从半连接中拉出。如果子查询被转换为半连接，您应该看到子查询谓词消失了，并且它的表和 WHERE 子句被合并到外部查询连接列表和 WHERE 子句中。
用于重复去除的临时表的使用由 Start temporary 和 End temporary 指示，它们位于 Extra 列中。没有被拉出并且位于 EXPLAIN 输出行的范围内的表，这些行被 Start temporary 和 End temporary 覆盖，它们在临时表中具有 rowid。
FirstMatch(tbl_name) 在 Extra 列中指示连接短路。
LooseScan(m..n) 在 Extra 列中指示使用了松散扫描策略。 m 和 n 是关键部分编号。
用于物化的临时表的使用由具有 select_type 值为 MATERIALIZED 以及具有 table 值为 <subqueryN> 的行指示。

半连接转换也可以应用于使用 [NOT] IN 或 [NOT] EXISTS 子查询谓词的单表 UPDATE 或 DELETE 语句，前提是该语句不使用 ORDER BY 或 LIMIT，并且优化器提示或 optimizer_switch 设置允许半连接转换。

PREV HOME UP NEXT