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

8.2.2.1 使用半连接转换优化子查询、派生表和视图引用

半连接是一种准备时间转换，支持多种执行策略，例如表拉出、重复剔除、首次匹配、松散扫描和物化。优化器使用半连接策略来改进子查询执行，如本节所述。

对于两个表之间的内部联接，联接从一个表返回行的次数与另一个表中的匹配项相同。但对于某些问题，唯一重要的信息是是否存在匹配项，而不是匹配项的数量。假设有一些表 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。

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

在 MySQL 中，子查询必须满足这些条件才能作为半连接处理：

  • 它必须是出现在or 子句顶层的IN(or ) 子查询，可能作为表达式中的一个术语 。例如： =ANYWHEREONAND

    SELECT ...
FROM ot1, ...
WHERE (oe1, ...) IN (SELECT ie1, ... FROM it1, ... WHERE ...);

    在这里， and 表示查询的外部和内部部分中的表， 并且 表示引用外部表和内部表中的列的表达式。 ot_iit_ioe_iie_i

  • 它必须是一个SELECT 没有UNION构造的。

  • 它不得包含GROUP BYor HAVING子句。

  • 它不能隐式分组（它不能包含聚合函数）。

  • 它不能有ORDER BYwith LIMIT。

  • 该语句不得 STRAIGHT_JOIN在外部查询中使用连接类型。

  • STRAIGHT_JOIN修饰符不得存在 。

  • 外部表和内部表的总数必须小于连接中允许的最大表数。

子查询可以是相关的或不相关的。 DISTINCT是允许的， LIMIT除非ORDER BY也被使用。

如果子查询满足上述条件，MySQL 将其转换为半连接并从这些策略中做出基于成本的选择：

  • 将子查询转换为连接，或使用表拉出并将查询作为子查询表和外部表之间的内部连接运行。表拉出将子查询中的表拉出到外部查询。

  • Duplicate Weedout：像连接一样运行半连接，并使用临时表删除重复记录。

  • FirstMatch：当扫描内表的行组合并且存在给定值组的多个实例时，选择一个而不是全部返回。这“快捷方式”扫描并消除了不必要行的产生。

  • LooseScan：使用索引扫描子查询表，该索引允许从每个子查询的值组中选择单个值。

  • 将子查询具体化为用于执行连接的索引临时表，其中索引用于删除重复项。在将临时表与外部表连接时，该索引也可能在以后用于查找；如果不是，则扫描该表。有关物化的更多信息，请参阅 第 8.2.2.2 节，“使用物化优化子查询”。

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

默认情况下启用这些标志。请参阅 第 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>