MySQL 8.0 官方文档第八章优化（二十四）—— 用半连接转换优化IN和EXISTS子查询谓词

最新推荐文章于 2023-11-06 17:41:25 发布

HZ_Samuel

最新推荐文章于 2023-11-06 17:41:25 发布

阅读量677

点赞数

文章标签： MySQL 8.0 MySQL优化’

本文链接：https://blog.csdn.net/qq_39785418/article/details/116933597

版权

@[TOC](目录)

第八章优化（二十四）—— 用半连接转换优化 `IN`和 `EXISTS`子查询谓词

8.2 优化SQL语句

8.2.2 优化子查询、派生表、视图引用和公共表表达式

8.2.2.1 用半连接转换优化`IN`和`EXISTS`子查询谓词

半连接是一种在准备阶段所进行的转换，它支持多种执行策略，例如：表上拉（table pullout）、重复消除（duplicate weedout）、首次匹配、松散扫描和物化。优化器使用半连接策略以改进子查询的执行效率，如本节所述。

两表间的内连接会使一个表中的一行的返回次数与另一个表中匹配的次数相等。但对于某些需求而言，它唯一关心的重要信息是有无匹配，而不是匹配的数量。假设有两张表名称分别为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);

在MySQL8.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…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表的这一行就会被丢弃。

如果要比较的表达式为null，则在大多数情况下不能应用反连接转换。这条规则的一个例外是：(... NOT IN (SELECT ...)) IS NOT FALSE，以及它的等价表达式：(... IN (SELECT ...)) IS NOT TRUE可以被转换为反连接。

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

在MySQL中，子查询必须满足以下条件才能转换为半连接（或者，在MySQL 8.0.17及更高版本中，如果是相反的条件，将修改子查询成反连接）：

它必须出现在WHERE或ON子句顶层的IN、= ANY或EXISTS谓词组成部分中，也可以作为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修饰的表达式参数。

它必须是没有UNION结构的单个 SELECT语句。
它不能包含HAVING子句。
它不能包含任何聚合函数（无论是显式分组还是隐式分组）。
它不能含有LIMIT子句。
在外部查询中语句不能使用STRAIGHT_JOIN连接。
不能出现STRAIGHT_JOIN修饰符。
外部表和内部表的总数必须小于连接查询所允许的最大表的数量。
子查询可以是关联的，也可以是不关联的（译者：关联子查询是一种包含了对外部查询中的表进行引用的子查询）。在MySQL 8.0.16及以后版本中，解关联先看到子查询的WHERE子子句中作为EXISTS参数中的平凡关联（trivially correlated）的谓词，并使优化它成为可能，就像使用在IN (SELECT b FROM…)中一样。术语“平凡关联（trivially correlated）”意味着谓词是相等谓词，它是WHERE子句中的唯一可以使用的谓词（或可与AND组合使用），并且一个操作数来自子查询中引用的表，另一个操作数来自外部查询块。
DISTINCT关键字是允许的，但被忽略。半连接策略自动处理重复删除。
GROUP BY子句是允许的，但被忽略，除非子查询中也包含一个或多个聚合函数。
允许使用ORDER BY子句，但被忽略，因为排序与半连接策略的计算无关。

如果子查询满足上述条件，MySQL会将其转换为半联接（或者，在MySQL 8.0.17或更高版本中，如果适合，则转换为反联接），并从以下策略中基于成本做出选择：

将子查询转换为一个连接，即使用表上拉（table pullout），就像是子查询表和外部表之间进行内连接一样运行该查询。表上拉就是把表从子查询中上拉到外部查询中。
重复消除（Duplicate Weedout）：就像是表连接一样运行半连接，并使用临时表删除重复的记录。
首次匹配（FirstMatch）：在扫描内部表得到行组合时，如果给定的值组有多个实例，只选择其中的一个实例返回，而不返回全部实例。这种“快捷”的扫描方式可以产生消除不必要的行。
松散扫描（LooseScan）：使用索引扫描子查询表，该索引允许从每个子查询的值组中选择单个值。
将子查询 物化（materialization） 成为一个带索引的临时表，其中的索引用于删除重复记录，然后，该临时表用于执行连接查询。当将临时表与外部表连接后，该索引也可以用于查找；如果没有该索引，则扫描表。有关物化的更多信息，请参阅第8.2.2.2节“使用物化优化子查询”。

使用以下的优化器开关（optimizer_switch）系统变量标志，这些策略中的每一个都可以启动或禁用：

semijoin标志控制是否使用半连接。从MySQL8.0.17开始，这也适用于反连接。
如果启用了semijoin（不连接标志），则firstmatch、loosescan、duplicateweedout和 materialization标志可以更好地控制允许的半连接策略。
如果duplicateweedout（重复消除）半联接策略被禁用，则除非所有其他适用策略也被禁用，否则不会使用它。
如果禁用了duplicateweedout，有时优化器可能会生成一个远非最优的查询计划。这是由于在贪婪搜索期间使用了启发式剪枝造成的，可以通过设置optimizer_prune_level=0（优化器剪枝界别=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修饰符就阻止了半连接，所以优化器拒绝合并，导致使用物化表来计算派生表。

【译者对以上语句进行了测试，具体如下：

mysql> EXPLAIN SELECT * 
	FROM t3 JOIN v 
	ON t3.x = v.a\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 33.33
        Extra: Using where; FirstMatch(t1); Using join buffer (hash join)  # 使用首次匹配，连接缓冲区（哈希连接）
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 25.00
        Extra: Using where; Using join buffer (hash join)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `mysql_test`.`t3`.`x` AS `x`,`mysql_test`.`t1`.`a` AS `a` 
from `mysql_test`.`t3` join `mysql_test`.`t1` semi join (`mysql_test`.`t2`) 
where ((`mysql_test`.`t2`.`b` = `mysql_test`.`t1`.`a`) and 
(`mysql_test`.`t3`.`x` = `mysql_test`.`t1`.`a`))

# 这里看到使用了半连接（semi join），并把视图合并到外连接中。

mysql> EXPLAIN SELECT STRAIGHT_JOIN *
    -> FROM t3
    -> JOIN v ON t3.x = v.a\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t3
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ref
possible_keys: <auto_key0>
          key: <auto_key0>          # 出现了自动索引，而本身这些表中无索引。
      key_len: 5
          ref: mysql_test.t3.x
         rows: 2
     filtered: 100.00
        Extra: Using index
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: NULL
*************************** 4. row ***************************
           id: 2
  select_type: DERIVED       # 这里出现了派生表
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 33.33
        Extra: Using where; FirstMatch(t1); Using join buffer (hash join)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select straight_join `mysql_test`.`t3`.`x` AS `x`,`mysql_test`.`v`.`a` AS `a` 
from `mysql_test`.`t3` join `mysql_test`.`v` 
where (`mysql_test`.`v`.`a` = `mysql_test`.`t3`.`x`)

】

EXPLAIN输出中表示半连接策略的使用提示，具体如下：

对于扩展EXPLAIN输出，接着使用SHOW WARNINGS显示的文本提示重写的查询，该查询显示半连接结构（请参阅第8.8.3节“扩展EXPLAIN输出格式”）。从中可以了解哪些表从半连接中上拉出来。如果子查询被转换为半连接，您应该看到子查询的谓词消失了，其表和WHERE子句被合并到外部查询的连接列表和WHERE子句中。
用于重复消除的临时表由Extra列中的Start Temporary（临时表开始）和End Temporary（临时表结束）进行提示。未被上拉的表、以及位于被Start temporary和End temporary覆盖的EXPLAIN输出行范围内的表，在临时表中都有它们的rowid（行号）。
Extra列中的FirstMatch(tbl_name)（首次匹配(表名)）表示连接捷径。
Extra列中的LooseScan(m..n)提示使用了松散扫描策略。其中的m和n分别是键组成部分的编号。
用于物化的临时表由select_type值为MATERIALIZED（选择类型为物化）的行和表值为<subqueryN>的行进行提示。