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

MySQL 8.0 参考手册 / 第8章优化 / 8.2 优化SQL语句 / 8.2.2 优化子查询、派生表、视图引用和公用表表达式 / 8.2.2.1 使用半连接转换优化 IN 和 EXISTS 子查询谓词

8.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);

在 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 ...)or形式的子查询的任何否定EXISTS (SELECT ... FROM ...)都会转换为反连接。

反连接是一种仅返回没有匹配项的行的操作。考虑此处显示的查询：

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

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

如果被比较的表达式可以为空，则在大多数情况下不能应用反连接转换。此规则的一个例外是(... NOT IN (SELECT ...)) IS NOT FALSE它的等价物(... IN (SELECT ...)) IS NOT TRUE可以转换为反连接。

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

在 MySQL 中，子查询必须满足这些条件才能作为半连接处理（或者，在 MySQL 8.0.17 及更高版本中，如果 NOT修改子查询则为反连接）：

它必须是出现在 or子句顶层的、或谓词的IN一部分= ANY，可能作为表达式中的术语。例如： EXISTSWHEREONAND
```
SELECT ...
    FROM ot1, ...
    WHERE (oe1, ...) IN
        (SELECT ie1, ... FROM it1, ... WHERE ...);
```
在这里， and 表示查询的外部和内部部分中的表，并且表示引用外部表和内部表中的列的表达式。 ot_iit_ioe_iie_i
在 MySQL 8.0.17 及更高版本中，子查询还可以作为由、或修饰的表达式 NOT的IS [NOT] TRUE参数IS [NOT] FALSE。
它必须是一个SELECT 没有UNION构造的。
它不得包含HAVING子句。
它不得包含任何聚合函数（无论是显式还是隐式分组）。
它不能有LIMIT子句。
该语句不得 STRAIGHT_JOIN在外部查询中使用连接类型。
STRAIGHT_JOIN修饰符不得存在。
外部表和内部表的总数必须小于连接中允许的最大表数。
子查询可以是相关的或不相关的。在 MySQL 8.0.16 及更高版本中，decorrelation 查看WHERE 子查询子句中简单相关的谓词作为的参数 EXISTS，并使得优化它成为可能，就好像它在中使用一样IN (SELECT b FROM ...)。术语trivially correlated意味着谓词是一个等式谓词，它是 WHERE子句中的唯一谓词（或与结合 AND），并且一个操作数来自子查询中引用的表，另一个操作数来自外部查询堵塞。
DISTINCT允许但忽略该关键字。半连接策略自动处理重复删除。
GROUP BY允许但忽略子句，除非子查询还包含一个或多个聚合函数。
允许但忽略子句，因为排序与半连接策略的ORDER BY评估无关。

如果子查询满足上述条件，MySQL 将其转换为半连接（或者，在 MySQL 8.0.17 或更高版本中，如果适用，则为反连接）并从这些策略中做出基于成本的选择：

将子查询转换为连接，或使用表拉出并将查询作为子查询表和外部表之间的内部连接运行。表拉出将子查询中的表拉出到外部查询。
Duplicate Weedout：运行半连接，就好像它是连接一样，并使用临时表删除重复记录。
FirstMatch：当扫描内表的行组合并且存在给定值组的多个实例时，选择一个而不是全部返回。这“快捷方式”扫描并消除了不必要行的产生。
LooseScan：使用索引扫描子查询表，该索引允许从每个子查询的值组中选择单个值。
将子查询具体化为用于执行连接的索引临时表，其中索引用于删除重复项。在将临时表与外部表连接时，该索引也可能在以后用于查找；如果不是，则扫描该表。有关物化的更多信息，请参阅第 8.2.2.2 节，“使用物化优化子查询”。

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

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

默认情况下启用这些标志。请参阅第 8.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显示的文本显示了重写的查询，其中显示了半连接结构。（请参阅第 8.8.3 节，“扩展的 EXPLAIN 输出格式”。）由此您可以了解哪些表已从半连接中提取出来。如果子查询被转换为半连接，您应该看到子查询谓词消失了，它的表和WHERE子句被合并到外部查询连接列表和 WHERE子句中。
Duplicate Weedout 的临时表使用由 Start temporary和End temporary在Extra 列中指示。未被拉出且位于临时表所涵盖的输出行范围 EXPLAIN内的表。Start temporaryEnd temporaryrowid
FirstMatch(tbl_name) 列中的Extra表示连接快捷方式。
LooseScan(m..n) 列中的Extra表示使用 LooseScan 策略。m并且 n是关键部件号。
用于具体化的临时表由select_type值为的 MATERIALIZED行和 table值为的行指示。 <subqueryN>

在 MySQL 8.0.21 及更高版本中，半连接转换也可以应用于使用 or子查询谓词的单表UPDATE或语句，前提是该语句不使用 or ，并且优化器提示或通过允许半连接转换设置。 DELETE[NOT] IN[NOT] EXISTSORDER BYLIMIToptimizer_switch