MySQL5.7性能优化系列（二）——SQL语句优化（2）——使用 Semi-Join半连接变换优化子查询，派生表和视图

时间：2021-01-14 06:04:38

优化器使用半连接策略来改进子查询执行，如本节所述。

对于两个表之间的内部连接，连接从一个表返回一行多于另一个表中的匹配项。但是对于一些问题，唯一重要的信息是是否有匹配，而不是匹配的数量

假设有一个名为class和roster的表分别列出课程表和课程名单（每个班级的学生）。要列出实际注册学生的课程，您可以使用此连接：

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

但是，结果列出了每个注册学生的每个课程一次。对于所问的问题，这是不必要的重复信息。

假设class_num是类表中的主键，则可以通过使用SELECT DISTINCT来去除重复，但是其效率非常低，因为只有一开始就生成所有匹配行的以后才能消除重复。

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

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

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

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

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

  • 它必须是出现在WHERE或ON子句顶层的IN（或= ANY）子查询，可能作为AND表达式中的术语。例如：
SELECT ...
FROM ot1, ...
WHERE (oe1, ...) IN (SELECT ie1, ... FROM it1, ... WHERE ...);
这里，ot_i和it_i表示查询的外部和内部部分的表，oe_i和ie_i表示引用外部和内部表中的列的表达式。
  • 它必须是没有UNION结构的单个SELECT。
  • 它不能包含GROUP BY或HAVING子句。
  • 它不能被隐式分组（它不能包含聚合函数）
  • 它不能有限制的ORDER BY。
  • 语句不能在外部查询中使用STRAIGHT_JOIN连接类型。
  • STRAIGHT_JOIN修饰符不能存在。
  • 外表和内表的数量必须小于连接中允许的最大表数。

子查询可能相关或不相关。允许使用DISTINCT，除非使用ORDER BY，否则为LIMIT。

如果子查询符合上述条件，MySQL将其转换为半连接，并从以下策略中进行基于成本的选择：

  • 将子查询转换为连接，或使用表拉出，并将查询作为子查询表和外部表之间的内部连接运行。表拉出将子查询中的表从外部查询中拉出。
  • 重复删除：运行半连接，就像它是一个连接，并使用临时表删除重复的记录。
  • FirstMatch：扫描内部表格的行组合，并且有多个给定值组的实例时，选择一个，而不是全部返回。这种“快捷方式”扫描并消除了不必要的行的生成。
  • LooseScan：使用启用从每个子查询的值组中选择单个值的索引来扫描子查询表。
  • 将子查询实现为用于执行连接的索引临时表，其中索引用于删除重复项。当使用外部表格加入临时表时，索引也可能用于查找;如果没有，表被扫描。

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

  • semijoin标志控制是否使用半连接。
  • 如果semijoin被启用，首先匹配，失去扫描，重复输出和实现标志可以对允许的半连接策略进行更好的控制。
  • 如果禁用重复输入半连接策略，则除非所有其他适用的策略也被禁用，否则不会使用它。
  • 如果重复输入被禁用，有时优化器可能生成远离最优的查询计划。这是由于在贪婪搜索期间启发式修剪，这可以通过设置optimizer_prune_level = 0来避免。

默认情况下启用这些标志

优化器可最大限度地减少视图和派生表（FROM子句中的子查询）的处理差异。这会影响使用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输出表示使用半连接策略如下：

  • Duplicate Weedout的临时表使用由“Extra”（临时）和“Extra”（临时）临时表示。没有被拉出并且在Start临时和End临时覆盖的EXPLAIN输出行范围内的表在临时表中具有rowid。
  • Extra列中的FirstMatch（tbl_name）表示连接快捷方式。
  • Extra列中的LooseScan（m..n）表示使用LooseScan策略。 m和n是关键零件号。
  • 用于实现的临时表用法由select_type值为MATERIALIZED的行和表值为的行指示。
标签：

相关文章