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

version 8.4

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

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

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

对于两个表之间的内连接，连接返回一个表中的行，次数与另一个表中的匹配次数相同。但是，对于某些问题，唯一重要的是是否存在匹配，而不是匹配的数量。假设有两个表名为class和roster，它们列举课程课程表和学生名单（每个班级的学生），respectively。要列出实际有学生报名的班级，可以使用以下连接：

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表中的每个班级号的唯一实例。因此，查询可以使用semijoin；即返回class表中的每个行的唯一实例，其中每个行在roster表中匹配。

以下包含EXISTS子句谓词的语句与前面的语句等效：

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

任何包含EXISTS子句谓词的语句都受同样的semijoin变换的影响，如同包含等效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修改了子查询）：

它必须是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修饰符不能存在。
外部和内部表的总数不能超过允许的最大表数。
子查询可以是相关的或无关的。Decorrelation可以将子查询中的简单相关谓词转换为在IN (SELECT b FROM ...)中优化它。术语简单相关指的是谓词是等值谓词，且是唯一的WHERE子句谓词（或与AND组合），且一个操作数来自子查询中的表，另一个操作数来自外部查询块。
允许但忽略的关键字是DISTINCT。半连接策略自动处理重复项删除。
允许但忽略的子句是GROUP BY，除非子查询还包含一个或多个聚合函数。
允许但忽略的子句是ORDER BY，因为排序对半连接策略的评估是无关的。

如果子查询满足前提条件，MySQL 将其转换为半连接（或反连接，如果适用），并根据成本选择以下策略：

将子查询转换为连接，或者使用表提取并将查询作为内连接运行于子查询表和外部表之间。表提取将一个表从子查询中提取到外部查询中。
重复weedout：将半连接作为连接运行，并使用临时表删除重复记录。
FirstMatch：在扫描内表时，选择一个值组中的一个，而不是返回所有值。这“简化”了扫描，并消除了生产不必要记录的需求。
LooseScan：使用索引扫描一个子查询表，以便从每个子查询值组中选择一个值。
将子查询物化为一个索引临时表，然后使用该索引删除重复项。索引也可能在后续用于查找时与临时表进行连接；如果不是，则扫描该表。关于物化的更多信息，请参见第10.2.2.2节，“Optimizing Subqueries with Materialization”。

每种策略都可以使用以下optimizer_switch系统变量标志启用或禁用：

半连接标志semijoin控制是否使用半连接和反连接。
如果semijoin启用，则firstmatch、loosescan、duplicateweedout和materialization标志启用更细的控制半连接策略。
如果duplicateweedout半连接策略禁用，则在所有其他可应用策略都禁用时不使用该策略。
如果duplicateweedout禁用，optimizer可能生成的查询计划远远不optimal。这是由于贪婪搜索中的启发式剪枝，可以通过设置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节，“Extended 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