MySQL 8.0 官方文档第八章优化（九）—— 嵌套连接优化

最新推荐文章于 2023-11-19 23:44:05 发布

HZ_Samuel

最新推荐文章于 2023-11-19 23:44:05 发布

阅读量287

点赞数

文章标签： MySQL 8.0 MySQL优化

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

版权

第八章优化（九）—— 嵌套连接优化

8.2 优化SQL语句

8.2.1 优化 SELECT 语句

8.2.1.8 嵌套连接优化

本节阐述允许嵌套连接基础上的连接语法。下面的讨论参照13.2.10.2节“连接子句”中描述的连接语法。

与SQL标准相比，表因子（table_factor）的语法被扩展了。SQL标准只接受表引用（table_reference）（译者：例如，SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id，其中的t2就是一个表引用），而不能接受由一对括号包裹起来的一个表引用的列表。如果我们把表引用项列表中的每个逗号都等效为一个内连接，那么这是一个保守的扩展。例如：

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)            # 这是表因子语法，在嵌套交叉连接基础上再进行左连接
                 ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c)

等同于：

SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
                 ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c)

在MySQL中，交叉连接（CROSS JOIN）在语法上等价于内连接（INNER JOIN），它们可以互相替代。而在标准SQL中，它们不是等价的。内连接要与ON子句一起使用，否则就是使用交叉连接。

通常，在只包含内连接操作的连接表达式中可以忽略括号。考虑以下连接表达式：

t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b = t3.b OR t2.b IS NULL)
   			ON t1.a = t2.a

在删除括号并把左边的操作进行分组后，该连接表达式转换为以下格式：

(t1 LEFT JOIN t2 ON t1.a = t2.a) LEFT JOIN t3
    		ON t2.b = t3.b OR t2.b IS NULL

然而，这两种表达方式并不等价。想要看到这一点，假设表 t1、t2和 t3具有以下状态：

表 t1 有两行记录 (1), (2)
表 t2 有一行记录 (1,101)

表 t3 有一行记录(101)

【译者：为了能够清楚地表达，译者根据原文进行表的创建和查询：

CREATE TABLE t1(
	a INT
);

CREATE TABLE t2(
	a INT,
	b INT
);

CREATE TABLE t3(
	b INT
);

INSERT INTO t1(a) VALUES(1),(2);
INSERT INTO t2(a,b) VALUES(1, 101);
INSERT INTO t3(b) VALUES(101);

】

在这种情况下，第一个表达式返回包含两行(1, 1, 101, 101)，(2, NULL, NULL, NULL)的结果集，而第二个表达式返回两行(1, 1, 101, 101)，(2, NULL, NULL,101)（译者：结果集是不同的）：

mysql> SELECT * FROM t1
            LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL)
            ON t1.a=t2.a;

在这里插入图片描述

mysql> SELECT * FROM (t1 LEFT JOIN t2 ON t1.a=t2.a)
            LEFT JOIN t3
            ON t2.b=t3.b OR t2.b IS NULL;

在这里插入图片描述

在以下示例中，外连接操作与内连接操作一起使用：

t1 LEFT JOIN (t2, t3) ON t1.a = t2.a

该表达式无法转换为以下表达式：

t1 LEFT JOIN t2 ON t1.a = t2.a, t3

对于上面给定的表状态，这两个表达式返回不同的行集：

SELECT * FROM t1 LEFT JOIN (t2, t3) ON t1.a = t2.a;

在这里插入图片描述

mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.a, t3;

在这里插入图片描述

因此，在带有外连接运算符的连接表达式中，如果省略括号，则可能会改变原始表达式的结果集。

更确切地说，我们不能忽略左外连接操作的右操作数和右连接操作的左操作数上的括号。换句话说，我们不能忽略外连接操作中的内部表的表达式上的括号。而另一个操作数（外连接表的操作数）上的括号可以忽略。

以下表达式：

(t1,t2) LEFT JOIN t3 ON P(t2.b, t3.b)

等价于任何表t1、t2、t3上的以下表达式和在属性t2.b和t3.b上的任何条件P：

t1, t2 LEFT JOIN t3 ON P(t2.b, t3.b)

每当出现连接表达式（连接表）中连接操作的执行顺序不是从左到右时，那就是说我们在讨论的是嵌套连接。考虑以下查询：

SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b = t3.b) ON t1.a = t2.a
  		WHERE t1.a > 1

SELECT * FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a
  		WHERE (t2.b = t3.b OR t2.b IS NULL) AND t1.a > 1

【译者：进行了测试，以上两条查询语句执行的结果是一样的，如下：
在这里插入图片描述
】

这些查询被认为包含以下嵌套连接：

t2 LEFT JOIN t3 ON t2.b = t3.b
t2, t3

在第一个查询中，嵌套连接是用左连接操作符组成的。在第二个查询中，它由一个内连接操作符组成的。

在第一个查询中，括号可以省略：连接表达式的语法结构规定了相同的连接操作的执行顺序。对于第二个查询，不能省略圆括号，尽管这里的连接表达式可以在没有它们的情况下被明确地解释。在我们的扩展语法中，第二个查询中的 (t2、t3)表达式中的圆括号是必需的，虽然理论上在没有它们情况下也可以被解析执行：对本次查询仍然有明确的语法结构，这是因为 LEFT JOIN和 ON为表达式(t2、t3)扮演左右分隔符的角色。

【译者：对以下查询语句进行操作时，提示有语法错误，错误代码是：1064。注意，译者使用的软件是SQLyog。

SELECT * FROM t1 LEFT JOIN t2, t3 ON t1.a=t2.a
  		WHERE (t2.b = t3.b OR t2.b IS NULL) AND t1.a > 1;

】

前面的例子说明了以下几点：

对于只涉及内连接（而不涉及外连接）的连接表达式，可以删除括号，其连接操作顺序是从左到右。事实上，可以以任何顺序对表进行连接操作。
一般来说，对于外连接或与内连接混合的外连接，情况并非如此。删除括号可能会改变结果。

带嵌套外连接的查询和带内连接的查询都是在相同的管道方式中执行的。更确切地说，都是利用了嵌套循环连接算法的一种变体。回想一下嵌套循环连接执行查询时使用的算法（请参阅第8.2.1.7节“嵌套循环连接算法”）。假设3个表T1、T2、T3上的连接查询具有以下形式：

SELECT * FROM T1 INNER JOIN T2 ON P1(T1,T2)
                 INNER JOIN T3 ON P2(T2,T3)
  				 WHERE P(T1,T2,T3)

这里，P1(T1,T2)和 P2(T3,T3)是在 ON表达式中的一些连接条件，而 P(T1, T2, T3)是加在表 T1, T2, T3中的一些列上的过滤条件。

该嵌套循环连接算法可以以下列方式执行查询：

FOR each row t1 in T1 {                               # 循环T1表中的每一行t1
	FOR each row t2 in T2 such that P1(t1, t2) {      # 循环T2表，取出符合P1(t1, t2)条件的每一行t2
		FOR each row t3 in T3 such that P2(t2, t3) {  # 循环T3表，取出符合P1(t2, t3)条件的每一行t3
			IF P(t1, t2, t3) {                        # 如果符合P(t1, t2, t3)过滤条件
			    t := t1 || t2 || t3; OUTPUT t;        # 组合t1，t2，t3中的列成一结果行，赋值给t，并输出t
			 }
		}
	}
}

上面的符号 t1 || t2 || t3表示通过连接行t1、t2和t3中的列构造的一结果行。在下面的一些例子中，如果表名的位置出现的是NULL，表示对该表的每一列都使用NULL。例如，t1 || t2 || NULL表示通过连接行 t1和行 t2的列以及NULL来构造的行，而NULL表示行t3的每个列。这样的行称为空补行（NULL-complemented）。

现在考虑一个带嵌套外连接的查询：

SELECT * FROM T1 LEFT JOIN 
			(T2 LEFT JOIN T3 ON P2(T2,T3))
            ON P1(T1,T2)
  			WHERE P(T1,T2,T3)

对于此查询，是通过修改嵌套循环模式以获得左连接的结果：

FOR each row t1 in T1 {                               # 循环T1表中的每一行t1
	BOOL f1 := FALSE;                                 # 定义布尔类型变量f1，并赋值为FALSE
	FOR each row t2 in T2 such that P1(t1, t2) {      # 循环T2表，取出符合P1(t1, t2)条件的每一行t2
		BOOL f2 := FALSE;                             # 定义布尔类型变量f2，并赋值为FALSE
		FOR each row t3 in T3 such that P2(t2, t3) {  # 循环T3表，取出符合P1(t2, t3)条件的每一行t3
			IF P(t1, t2, t3) {                        # 如果符合P(t1, t2, t3)过滤条件
				t:=t1 || t2 || t3; OUTPUT t;          # 组合t1，t2，t3中的列成一行，赋值给t，输出t
		    }
		    f2 = TRUE;        # 如果进入第三层循环，说明符合T1，T2和T3表的连接条件，对f2重新赋值为TRUE
		    f1 = TRUE;        # 如果进入第三层循环，说明符合T1，T2和T3表的连接条件，对f1重新赋值为TRUE
	  	}
	                        # 上面的代码其实是处理了三个表的内连接
	                        # 下面的代码是在处理好T1和T2的内连接后，对T3不符合连接条件的补上NULL
		IF (!f2) {                                    # 如果f2是FALSE，说明T3表的行都不符合P2(t2, t3)条件
			IF P(t1, t2, NULL) {                      # 如果符合P(t1, t2, NULL)过滤条件
				t :=t1 || t2 || NULL; OUTPUT t;       # 组合t1，t2，NULL中的列成一行，赋值给t，输出t
			}
			f1 = TRUE;      # 如果程序执行到此，说明T1，T2进行了连接，并处理了T3表，对f1重新赋值为TRUE
		}
	}
	IF (!f1) {                                        # 如果f1是FALSE，说明T2表的行都不符合P1(t1, t2)条件
		IF P(t1, NULL, NULL) {                        # 如果符合P(t1, NULL, NULL)过滤条件
			t := t1 || NULL || NULL; OUTPUT t;        # 组合t1，NULL，NULL中的列成一行，赋值给t，输出t
		}
	}
}

通常，对于外连接操作中的第一个内部表的任何嵌套循环，都会引入一个标志，该标志在循环之前被关闭（即设置为FALSE），并在循环之后进行检查。当外部表的当前行与表示内部操作数的表匹配时，该标志将打开。如果在循环结束时该标志仍然是关闭的，则代表外部表的当前行找不到匹配项。在这种情况下，结果行中需要由内部表中需要有列组成的位置由NULL值来填充。结果行被传递给输出前的最终检查或传递给下一个嵌套循环，条件是仅当该行满足所有嵌入外连接的连接条件。

在本例中，嵌入了由以下表达式表示的外连接表：

(T2 LEFT JOIN T3 ON P2(T2,T3))

对于是内连接的查询，优化器可以选择不同顺序的嵌套循环，例如：

FOR each row t3 in T3 {
	FOR each row t2 in T2 such that P2(t2, t3) {
		FOR each row t1 in T1 such that P1(t1, t2) {
		  IF P(t1, t2, t3) {
		  	t: = t1 || t2 || t3; OUTPUT t;
		  }
		}
	}
}

对于外连接的查询，优化器只能选择这样的顺序，即外部表的循环优先于内部表的循环。因此，对于带外连接的查询，只能有一种嵌套顺序。对于下面的查询，优化器会评估两种不同性质的嵌套。在这两种嵌套中，T1必须在外部循环中处理，因为它用在外连接中。T2和T3用于内连接，因此两表连接必须在内部循环中处理。但是，因为该连接是内连接，所以可以按任意顺序处理T2和T3。

SELECT * T1 LEFT JOIN (T2, T3) ON P1(T1, T2) AND P2(T1, T3)
  				WHERE P(T1, T2, T3)

一个嵌套先处理T2，然后处理T3：

FOR each row t1 in T1 {
	BOOL f1: = FALSE;
	FOR each row t2 in T2 such that P1(t1, t2) {
		FOR each row t3 in T3 such that P2(t1, t3) {
			IF P(t1, t2, t3) {
				t:=t1 || t2 || t3; OUTPUT t;
			}
			f1: = TRUE
		}
	}
	IF (!f1) {
		IF P(t1, NULL, NULL) {
			t: = t1 || NULL || NULL; OUTPUT t;
		}
	}
}

另一个嵌套先处理T3，然后处理T2：

FOR each row t1 in T1 {
	BOOL f1:=FALSE;
	FOR each row t3 in T3 such that P2(t1,t3) {
		FOR each row t2 in T2 such that P1(t1,t2) {
			IF P(t1,t2,t3) {
				t:=t1||t2||t3; OUTPUT t;
			}
			f1:=TRUE
		}
	}
	IF (!f1) {
		IF P(t1,NULL,NULL) {
			t:=t1||NULL||NULL; OUTPUT t;
		}
	}
}

在讨论内连接的嵌套循环算法时，我们省略了一些对查询执行性能可能产生巨大影响的细节。我们没有提到所谓的“下推”条件。假设WHERE条件P(T1,T2,T3)可以用一个合取范式（conjunctive formula，合取公式，连接公式）表示：

P(T1, T2, T2) = C1(T1) AND C2(T2) AND C3(T3).

在本例中，MySQL实际上使用以下嵌套循环算法执行带有内连接的查询：

FOR each row t1 in T1 such that C1(t1) {
	FOR each row t2 in T2 such that P1(t1, t2) AND C2(t2)  {
		FOR each row t3 in T3 such that P2(t2, t3) AND C3(t3) {
			IF P(t1, t2, t3) {
				t: = t1 || t2 || t3; OUTPUT t;
			}
		}
	}
}

您可以看到每个合取条件C1(T1)， C2(T2)，C3(T3)都从最内层循环推到最外层循环，并可以在最外层循环中就执行过滤。如果C1(T1)是一个非常严格的条件，那么该下推条件可能会大大减少从表T1中传递到内部循环的行数。因此，查询的执行时间可能会大大缩短。

但是，对于带有外连接的查询，只有在出现外部表的当前行在内部表中有匹配后，才进行WHERE条件的检查。因此，将条件从内部嵌套循环中推出的优化不能直接应用于带外连接的查询。这里，我们必须引入条件下推谓词，这些谓词在遇到匹配时由处于打开的标志（译者：把标志设置为TRUE）进行保护。

回想一下这个带有外连接的示例：

P(T1, T2, T3) = C1(T1) AND C(T2) AND C3(T3)

对于该示例，使用有保护的下推条件的嵌套循环算法如下所示：

FOR each row t1 in T1 such that C1(t1) {                   # 把C1(t1)条件放在第一层进行过滤
	BOOL f1:=FALSE;
	FOR each row t2 in T2
			such that P1(t1,t2) AND (f1?C2(t2):TRUE) {     # 与前面相比，新增一个三元运算符组成的条件，如果f1是真，
			                                               # 说明在T2表内已有匹配项，执行C2(t2)条件，否则不进行过滤
		BOOL f2:=FALSE;
		FOR each row t3 in T3
			such that P2(t2,t3) AND (f1&&f2?C3(t3):TRUE) { # 如果f1及f2同时为真，说明在T3表内也有匹配项，执行C3(t3)，
			                                               # 否则不进行条件过滤                            
			IF (f1&&f2?TRUE:(C2(t2) AND C3(t3))) {         # 如果f1和f2同时为真，说明符合三个表之间连接条件，直接组合
			                                               # 结果行，否则检查C2(t2) AND C3(t3)条件
			  	t:=t1||t2||t3; OUTPUT t;
			}
			f2=TRUE;
			f1=TRUE;
		}
		IF (!f2) {                                         # 如果T2表都不符合与T3表连接条件
			IF (f1?TRUE:C2(t2) && P(t1,t2,NULL)) {         # 检查T1表与T2表是否符合连接条件，然后进行操作
			   	t:=t1||t2||NULL; OUTPUT t;
			 }
			 f1=TRUE;
		}
	}
	IF (!f1 && P(t1,NULL,NULL)) {
		t:=t1||NULL||NULL; OUTPUT t;
	}
}

通常，可以从连接条件(如：P1(T1,T2)和 P(T2,T3))中提取出下推谓词。在本例中，下推谓词还由一个标志保护，该标志防止检查谓词以使相应的外连接操作生成空补行（NULL-complemented）。

如果是由来自WHERE条件的谓词引发的，则在同一个嵌套连接中禁止通过键从一个内部表访问另一个表。

上一集 MySQL 8.0 官方文档第八章优化（八）—— 嵌套循环连接算法

下一集 MySQL 8.0 官方文档第八章优化（十）—— 外连接优化

HZ_Samuel

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL 8.0 官方文档第八章优化（九）—— 嵌套连接优化

目录第八章优化（九）—— 嵌套连接优化8.2 优化SQL语句8.2.1 优化 SELECT 语句8.2.1.8 嵌套连接优化第八章优化（九）—— 嵌套连接优化8.2 优化SQL语句8.2.1 优化 SELECT 语句8.2.1.8 嵌套连接优化表述允许嵌套连接的连接语法。下面的讨论参照13.2.10.2节“连接子句”中描述的连接语法。与SQL标准相比，表因子（table_factor）的语法被扩展了。SQL标准只接受表引用（table_reference）（译者：例如，SELECT * FR
复制链接

扫一扫