8.2.2.1 使用半联接转换优化子查询,派生 table 和视图引用

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

对于两个 table 之间的内部联接,该联接从一个 table 返回一行的次数是另一 table 中存在匹配项的次数。但是对于某些问题,唯一重要的信息是是否存在匹配项,而不是匹配数。假设存在名为classroster的 table,这些 table 分别列出了类 table 中的类和类花名册(每个类的学生人数)。要列出实际招收学生的类,您可以使用以下联接:

SELECT class.class_num, class.class_name
FROM class INNER JOIN roster
WHERE class.class_num = roster.class_num;

但是,结果为每个注册学生列出一次每个类。对于所提出的问题,这是不必要的信息重复。

假设class_numclasstable 中的主键,则可以通过使用SELECT DISTINCT来抑制重复,但是先生成所有匹配的行仅用于稍后消除重复是无效的。

可以通过使用子查询获得相同的无重复结果:

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

在这里,优化器可以识别IN子句要求子查询仅返回rostertable 中每个类号的一个实例。在这种情况下,查询可以使用半联接。也就是说,该操作仅返回class中的每一行的一个实例,该实例与roster中的行匹配。

外部查询规范中允许使用外部联接和内部联接语法,并且 table 引用可以是基 table,派生 table 或视图引用。

在 MySQL 中,子查询必须满足以下条件才能作为半联接进行处理:

  • 它必须是出现在WHEREON子句的顶级(可能作为ANDtable 达式中的术语)的IN(或=ANY)子查询。例如:
SELECT ...
FROM ot1, ...
WHERE (oe1, ...) IN (SELECT ie1, ... FROM it1, ... WHERE ...);

在这里,ot_iit_itable 示查询的外部和内部部分中的 table,oe_iie_itable 示引用外部和内部 table 中的列的 table 达式。

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

  • 它不能包含GROUP BYHAVING子句。

  • 不能将其隐式分组(不能包含任何聚合函数)。

  • 它不能带有ORDER BYLIMIT

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

  • STRAIGHT_JOIN修饰符不能存在。

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

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

如果子查询满足上述条件,MySQL 会将其转换为半联接并从以下策略中进行基于成本的选择:

  • 将子查询转换为联接,或使用 table 提取,并将查询作为子查询 table 与外部 table 之间的内部联接运行。table 提取将 table 从子查询中拉出到外部查询。

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

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

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

  • 将子查询具体化到用于执行联接的索引临时 table 中,在该临时 table 中,索引用于删除重复项。当将临时 table 与外部 table 连接时,该索引以后也可能用于查找。如果不是,则扫描 table。有关实现的更多信息,请参见第 8.2.2.2 节“通过实现来优化子查询”

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

  • semijoin标志控制是否使用半联接。

  • 如果启用了semijoin,则firstmatchloosescanduplicateweedoutmaterialization标志可对允许的半联接策略进行更好的控制。

  • 如果禁用了duplicateweedout半联接策略,则除非所有其他适用的策略也都被禁用,否则将不使用它。

  • 如果禁用duplicateweedout,则有时优化器可能会生成一个远非最佳的查询计划。这是由于贪婪搜索期间的启发式修剪而发生的,可以通过设置optimizer_prune_level=0来避免。

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

优化器将视图和派生 table 的处理差异最小化。这会影响使用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修饰符防止半联接,所以优化器拒绝合并,从而导致使用物化 table 进行派生 table 评估。

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

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

  • Extra列中的Start temporaryEnd temporarytable 示用于重复除草的临时 table。未拉出并且在Start temporaryEnd temporary覆盖的EXPLAIN输出行范围内的 table 在临时 table 中具有rowid

  • Extra列中的FirstMatch(tbl_name)table 示加入快捷方式。

  • Extra列中的LooseScan(m..n)table 示使用了 LooseScan 策略。 * m n *是关键 Component 编号。

  • 用于实现的临时 table 由select_type值为MATERIALIZED的行和table值为<subqueryN>的行指示。