[ACDU 翻译 ] 8.2.2.1使用半联接转换优化IN和EXISTS子查询谓词

原创由迪 2021-04-16

749

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

对于两个表之间的内部联接，该联接从一个表返回一行的次数是另一表中存在匹配项的次数。但是对于某些问题，唯一重要的信息是是否存在匹配项，而不是匹配项的数量。假设在课程表和班级花名册（每个班级中都有学生）中分别有命名的表 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_*i*``it_*i*``oe_*i*``ie_*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：使用索引扫描子查询表，该索引允许从每个子查询的值组中选择一个值。
将子查询具体化到用于执行连接的索引临时表中，在该临时表中，索引用于删除重复项。当将临时表与外部表连接时，该索引以后也可能用于查找。如果不是，则扫描表。有关实现的更多信息，请参见第8.2.2.2节“通过实现来优化子查询”。

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

该semijoin标志控制是否使用半联接。从MySQL 8.0.17开始，这也适用于防联接。
如果semijoin使能，firstmatch， loosescan， duplicateweedout，和 materialization标志enable更好地控制在允许的半连接策略。
如果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子句中。
对于重复Weedout临时表的使用是由指示 Start temporary和End temporary在Extra 列。那些没有拉出表是在范围内EXPLAIN所涵盖的输出行Start temporary，并 End temporary有自己 rowid的临时表。
FirstMatch(*tbl_name*) 在Extra列表示加入shortcutting。
LooseScan(*m*..*n*) 在Extra列指示使用LooseScan策略。*m*和 *n*是关键部件号。
用于实现的临时表由select_type值为的 MATERIALIZED行和的table值为的行指示。 <subquery*N*>

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