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

版本 8.4

9.0

MySQL 8.4 参考手册 / ... / 使用半连接和反连接转换优化 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 修饰。
它必须是单个不带 UNION 构造的 SELECT。
它不能包含 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 子句中。
Extra 列中的 Start temporary 和 End temporary 指示使用临时表进行重复项剔除。未被提取出来且位于 Start temporary 和 End temporary 覆盖的 EXPLAIN 输出行范围内的表的 rowid 位于临时表中。
Extra 列中的 FirstMatch(tbl_name) 指示连接捷径。
Extra 列中的 LooseScan(m..n) 指示使用了 LooseScan 策略。m 和 n 是键部分编号。
select_type 值为 MATERIALIZED 的行和 table 值为 <subqueryN> 的行指示使用临时表进行物化。

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