Documentation Home

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修饰符不得存在 。

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

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

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

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

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

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

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

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

除了 Duplicate Weedout 之外,这些策略中的每一个都可以使用 optimizer_switch系统变量启用或禁用:

默认情况下启用这些标志。请参阅 第 8.9.2 节,“可切换优化”

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

  • 半连接表显示在外部选择中。对于扩展EXPLAIN输出,以下 SHOW WARNINGS显示的文本显示了重写的查询,其中显示了半连接结构。(请参阅第 8.8.3 节,“扩展的 EXPLAIN 输出格式”。)由此您可以了解哪些表已从半连接中提取出来。如果子查询转换为半连接,您可以看到子查询谓词消失了,它的表和WHERE子句被合并到外部查询连接列表和WHERE子句中。

  • Duplicate Weedout 的临时表使用由 Start temporaryEnd temporaryExtra 列中指示。未被拉出且位于临时表所涵盖的输出 行 范围 EXPLAIN内的表。Start temporaryEnd temporaryrowid

  • FirstMatch(tbl_name) 列中的Extra表示连接快捷方式。

  • LooseScan(m..n) 列中的Extra表示使用 LooseScan 策略。m并且 n是关键部件号。

  • 用于具体化的临时表由select_type值为 的 MATERIALIZED行和 table值为 的行指示。 <subqueryN>