8.2.1.11 Nested Join Optimization 嵌套关联优化

8.2.1.11 Nested Join Optimization 嵌套关联优化

连接的关联语法运行嵌套循环,下面的讨论是指13.2.9.2章节的描述,“JOIN Syntax”.

table_factor 的语法是被扩展的 比较SQL标准 。后者只接受table_reference，

不是它们的列表在一对圆括号内,这是一个保守的延伸,如果我们考虑每个逗号

在一个 table_reference的引用。

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,它们是不相等的,INNER JOIN 是用于ON子句,CROSS JOIN 是用于其他:

在一般情况下,括号可以被忽视在关联表达式只包含inner join操作。

当去掉括号和分组操作,join表达式如下:

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 有下面的状态:

Table t1 contains rows (1), (2)

Table t2 contains row (1,101)

Table t3 contains row (101)

在这种情况下, 第一个表达式返回一个结果集包括记录

(1,1,101,101), (2,NULL,NULL,NULL),

第2个表达式返回 (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;
+——+——+——+——+
| a | a | b | b |
+——+——+——+——+
| 1 | 1 | 101 | 101 |
| 2 | NULL | NULL | NULL |
+——+——+——+——+

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;
+——+——+——+——+
| a | a | b | b |
+——+——+——+——+
| 1 | 1 | 101 | 101 |
| 2 | NULL | NULL | 101 |
+——+——+——+——+

在下面的例子中,一个外关联表达式用于一个内连接操作:

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

表达式不能转换为下面的表达式：

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

对于给定表的状态, 两个表达式返回不同的记录:

mysql> SELECT *
-> FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a;
+——+——+——+——+
| a | a | b | b |
+——+——+——+——+
| 1 | 1 | 101 | 101 |
| 2 | NULL | NULL | NULL |
+——+——+——+——+

mysql> SELECT *
-> FROM t1 LEFT JOIN t2 ON t1.a=t2.a, t3;
+——+——+——+——+
| a | a | b | b |
+——+——+——+——+
| 1 | 1 | 101 | 101 |
| 2 | NULL | NULL | 101 |
+——+——+——+——+

因此, 如果我们省略括号在一个关联表达式 外连接,我们可能改变原始表达式的结果集:

更确切的说,我们不能忽略括号左外连接操作的右操作数

和右连接的左操作数。

换句话说,我们不能忽略括号对于外连接的内部表

The following expression:

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

is equivalent to this expression:

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

对于任何表t1,t2,t3 任何条件P

每当关联操作的执行顺序不是从左到右,我们讨论嵌套连接,如下:

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

嵌套连接是第一个左连接的查询形成的,而在第2个查询它是有内部连接形成的。

在第一个查询, 括号可以被省略,关联语法上的结构将决定关联的同样的执行顺序。

对于第2个查询,括号不能被省略,尽管关联表达式可以被明确的解释。

在我们的扩展语句 括号 (t2,t3) 第2个查询的是需要的,

前面的例子演示了这点:

对于关联表达式 只涉及 inner joins（不是外连接), 括号可以被删除.

你可以删除括号和从左到右的评估(事实上,你可以用任何顺序来评估表)

同样是不真实的,通常,对于外部关联或者外部关联混合内部关联。

删除括号可能改变结果:

外连接的查询被执行在同一管道方式作为查询在内部连接,更确切的说,

一个嵌套循环算法的变化被利用。

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(T2，T3) 是相同的关联条件.P(T1,T2,T3)是表T1，T2，T3的条件

嵌套循环关联算法会执行这个查询:

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

符号 t1||t2||t3 意味着 一行通过把t1,t2,t3的列联系起来,下面的一些例子,

NULL 一个行的名字出现意味着 NULL是用于那行的每个列。

比如, t1||t2||NULL 意思是 一个通过连接t1,t2 t3的每个列是NULL的

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 {
BOOL f1:=FALSE;
FOR each row t2 in T2 such that P1(t1,t2) {
BOOL f2:=FALSE;
FOR each row t3 in T3 such that P2(t2,t3) {
IF P(t1,t2,t3) {
t:=t1||t2||t3; OUTPUT t;
}
f2=TRUE;
f1=TRUE;
}
IF (!f2) {
IF P(t1,t2,NULL) {
t:=t1||t2||NULL; OUTPUT t;
}
f1=TRUE;
}
}
IF (!f1) {
IF P(t1,NULL,NULL) {
t:=t1||NULL||NULL; OUTPUT t;
}
}
}