MySQL :: MySQL 8.3 Reference Manual :: 10.2.2.1 Optimizing IN and EXISTS Subquery Predicates with Semijoin and Antijoin Transformations

version 8.3

MySQL 8.3 Reference Manual / ... / Optimizing IN and EXISTS Subquery Predicates with Semijoin and Antijoin Transformations

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

半连接是一种准备时间变换，启用多种执行策略，如表拉取、重复消除、首次匹配、松散扫描和实体化。优化器使用半连接策略来改善子查询执行，如本节所述。

对于两个表之间的内部连接，连接将从一个表中返回一行，以便在另一个表中找到匹配项。但是，对于某些问题，只关心是否存在匹配，而不是匹配的数量。假设有两个表，class 和 roster，分别列出课程curriculum中的课程和课程中的学生名单。要列出有学生报名的课程，可以使用以下连接：

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 表中没有匹配项的行。这意味着，对于 class 表中的每一行，只要在 roster 表中找到匹配项，该行就可以被丢弃。

在大多数情况下，反连接变换不能应用于可为空的表达式比较。一个例外是 (... NOT IN (SELECT ...)) IS NOT FALSE 和其等效的 (... IN (SELECT ...)) IS NOT TRUE 可以被转换为反连接。

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

在 MySQL 中，子查询必须满足以下条件以被处理为半连接（或反连接，如果 NOT 修饰子查询）：

它必须是 IN、= ANY 或 EXISTS 谓词的一部分，该谓词出现在 WHERE 或 ON 子句的顶层，可能作为 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 修饰符。
外部表和内部表的总数必须小于允许的最大连接表数。
子查询可以是相关的或不相关的。去相关性检查 trivially 相关的谓词在子查询的 WHERE 子句中，并使其可以像在 IN (SELECT b FROM ...) 中一样进行优化。术语 trivially 相关 表示谓词是一个等式谓词，它是 WHERE 子句中的唯一谓词（或与 AND 结合），并且一个操作数来自子查询引用的表，而另一个操作数来自外部查询块。
允许使用 DISTINCT 关键字，但忽略它。半连接策略自动处理重复删除。
允许使用 GROUP BY 子句，但忽略它，除非子查询也包含一个或多个聚合函数。
允许使用 ORDER BY 子句，但忽略它，因为排序对半连接策略的评估无关。

如果子查询满足上述条件，MySQL 将其转换为半连接（或反连接，如果适用），并从以下策略中选择成本最低的策略：

将子查询转换为连接，或者使用表提取并将查询作为子查询表和外部表之间的内部连接。表提取将表从子查询中提取到外部查询中。
重复删除：以连接的方式运行半连接，并使用临时表删除重复记录。
FirstMatch：在扫描内部表以查找行组合时，如果存在多个实例的给定值组，选择一个而不是返回所有实例。这“shortcut”扫描并消除不必要的行的生成。
LooseScan：使用索引扫描子查询表，以便从每个子查询值组中选择一个值。
将子查询物化为带索引的临时表，以便与外部表进行连接。索引也可能在后续与外部表的连接中用于查找；否则，表将被扫描。有关物化的更多信息，请参阅第 10.2.2.2 节，“使用 Materialization 优化子查询”。

每个策略都可以使用以下 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 子句中。
临时表的使用以 Duplicate Weedout 表示为 Start temporary 和 End temporary 在 Extra 列中。未被拉出的表和在 EXPLAIN 输出行中被 Start temporary 和 End temporary 覆盖的表具有临时表中的 rowid。
FirstMatch(tbl_name) 在 Extra 列中表明连接快捷方式。
LooseScan(m..n) 在 Extra 列中表明了 LooseScan 策略的使用。m 和 n 是键部分编号。
临时表的使用以物化表示为行具有 select_type 值为 MATERIALIZED 的行和具有 table 值为 <subqueryN> 的行。

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

PREV HOME UP NEXT