使用半联接转换优化 IN 和 EXISTS 子查询

MySQL查询优化器有多种策略可用于评估子查询：

对于具有使用子查询IN，= ANY或EXISTS谓词，优化器具有以下选择：
- 半连接
- 物化
- EXISTS战略
对于一个使用子查询NOT IN，<> ALL或NOT EXISTS谓语，优化有以下选择：
- 物化
- EXISTS战略

对于派生表，优化器具有以下选择（这也适用于视图引用和公用表表达式）：

将派生表合并到外部查询块中
将派生表具体化为内部临时表

以下讨论提供了有关前面的优化策略的更多信息。

注意

使用子查询修改单个表的UPDATE和DELETE语句的限制是，优化器不使用半联接或实现子查询优化。解决方法是，尝试将它们重写为使用联接而不是子查询的多表UPDATE和DELETE语句。

半联接是准备时转换，它启用多种执行策略，例如表提取，重复删除，首次匹配，松散扫描和实现。如本节所述，优化器使用半联接策略来改善子查询的执行。

对于两个表之间的内部联接，该联接从一个表返回一行的次数是另一表中存在匹配项的次数。但是对于某些问题，唯一重要的信息是是否存在匹配项，而不是匹配项的数量。假设在课程表和班级名册（每个班级都有学生）中分别有命名的表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 ...)或的子查询的任何否定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中，子查询必须满足以下条件才能作为半联接（或在MySQL 8.0.17及更高版本中，如果NOT修改了子查询，则为反联接）：

它必须是一个的一部分IN，= ANY或EXISTS谓词出现在顶层WHERE或ON条款，可能作为在一个术语AND表达。例如：
```
SELECT ...
    FROM ot1, ...
    WHERE (oe1, ...) IN
        (SELECT ie1, ... FROM it1, ... WHERE ...);
```
在此，和分别代表查询的外部和内部部分中的表，以及和代表引用外部和内部表中的列的表达式。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及更高版本中，去相关性会在WHERE用作参数的子查询的子句中参见琐碎相关的谓词EXISTS，并使其能够像在内一样进行优化IN(SELECT b FROM ...)。术语“琐碎相关”表示谓词是相等谓词，它是WHERE子句中唯一的谓词（或与组合AND），并且一个操作数来自子查询中引用的表，另一个操作数来自外部查询块。
该DISTINCT关键字是允许的，但忽略。半联接策略自动处理重复项删除。
GROUP BY子句允许的，但忽略，除非子查询还包含一个或多个聚合函数。
一个ORDER BY条款是允许的，但忽略，因为排序是无关的半连接策略的评价。

如果子查询满足上述条件，MySQL会将其转换为半联接（或者，在适用于MySQL 8.0.17或更高版本的情况下，为反联接），并从以下策略中选择基于成本的选择：

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

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

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

默认情况下启用这些标志。请参见“可切换的优化”。

优化器将视图和派生表的处理差异最小化。这会影响使用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. ;

优化器首先参见视图，然后将IN子查询转换为半联接，然后检查是否有可能将视图合并到外部查询中。因为STRAIGHT_JOIN外部查询中的修饰符防止半联接，所以优化程序拒绝合并，从而导致使用物化表进行派生表评估。

EXPLAIN输出表明使用了半连接策略，如下所示：

对于扩展EXPLAIN输出，以下内容显示的文本显示SHOW WARNINGS了重写的查询，该查询显示了半联接结构。（请参见“扩展的EXPLAIN输出格式”。）由此，您可以了解哪些表从半联接中被拉出。如果将子查询转换为半联接，则将看到该子查询谓词已消失，并且其表和WHERE子句已合并到外部查询联接列表和WHERE子句中。
对于重复Weedout临时表的使用是由指示Start temporary和End temporary在Extr 列。那些没有拉出表是在范围内EXPLAIN所涵盖的输出行Start temporary，并End temporary有自己rowid的临时表。
FirstMatch(tbl_name)在Extr 列表示加入shortcutting。
LooseScan(m..n)在Extr 列指示使用LooseScan策略。m和n是关键部件号。
用于实现的临时表由select_type值为的MATERIALIZED行和的table值为的行指示。<subqueryN>