当前位置：首页 > news >正文

【MySQL精通之路】SQL优化(1)-查询优化(8)-嵌套联接优化

news 来源：原创 2024/6/25 14:50:18

主博客：

【MySQL精通之路】SQL优化(1)-查询优化-CSDN博客

【MySQL精通之路】SQL优化(1)-查询优化(7)-嵌套循环联接-CSDN博客

【MySQL精通之路】SQL优化(1)-查询优化(9)-外部联接优化-CSDN博客

与SQL标准相比，table_factor的语法得到了扩展。后者只接受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。

通常，在只包含内部联接操作的联接表达式中，可以忽略圆括号。

例如下面联接表达式：

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 t3ON 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),的结果集，而第二个表达式返回行(1,1,101,101), (2,NULL,NULL,101)

mysql> SELECT *FROM t1LEFT 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 t3ON 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 |
+------+------+------+------+

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

更确切地说，我们不能忽略左外部联接运算的右操作数和右联接运算的左操作数中的括号。换句话说，对于外部联接操作的内部表表达式，我们不能忽略括号。可以忽略其他操作数（外部表的操作数）的括号。

以下表达式：

(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)

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

SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b) ON t1.a=t2.aWHERE t1.a > 1SELECT * FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.aWHERE (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）的左右分隔符的角色。

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

对于只涉及内部联接（而不涉及外部联接）的联接表达式，可以删除圆括号，并从左到右计算联接。事实上，表可以按任何顺序进行评估。

博主ps，你可以理解为内联接查询（求交集的过程），就是先把所有表都先求交集拼接起来，先拼谁都差不多

通常，对于外部联接或与内部联接混合的外部联接，情况并非如此。删除括号可能会改变结果。

具有嵌套外部联接的查询以与具有内部联接的查询相同的管道方式执行。

更确切地说，利用了嵌套循环连接算法的变体。调用嵌套循环联接执行查询的算法

（请参阅“嵌套循环联接算法”）。

假设对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）是一些连接条件（关于表达式），而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表示该表的每一列的行。

例如：

t1 || t2 || NULL表示通过串联行t1和t2的列而构造的行，并且对于t3的每一列设为NULL。

这样的行被称为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;}}
}

通常，对于外部联接操作中第一个内部表的任何嵌套循环，都会引入一个标志，该标志在循环之前为false，在循环之后检查状态。

当外部表中的当前行与表示内部操作的表匹配时，该标志为true。

如果在循环周期结束时，标志仍然为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）可以用一个连接公式表示：

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传递到内部循环的行数。因此，查询的执行时间可能会大大提高。

博主PS：

这里意思是如果可以减少外层for循环的查询次数，将大大减少内层for循环的循环次数。这就是条件下推的意思。也就是将内层for循环的匹配条件推到外层判断。这样如果不匹配的话，之间终结外层循环。

对于具有外部联接的查询，只有在发现外部表中的当前行在内部表中匹配后，才检查WHERE条件。

因此，将判断条件推出内部嵌套循环的优化不能直接应用于具有外部联接的查询。

在这里，我们必须介绍条件下推在标志打开时有保留的断言。

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

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

例如，使用受限的下推条件嵌套循环算法如下所示：

FOR each row t1 in T1 such that C1(t1) {BOOL f1:=FALSE;FOR each row t2 in T2such that P1(t1,t2) AND (f1?C2(t2):TRUE) {BOOL f2:=FALSE;FOR each row t3 in T3such that P2(t2,t3) AND (f1&&f2?C3(t3):TRUE) {IF (f1&&f2?TRUE:(C2(t2) AND C3(t3))) {t:=t1||t2||t3; OUTPUT t;}f2=TRUE;f1=TRUE;}IF (!f2) {IF (f1?TRUE:C2(t2) && P(t1,t2,NULL)) {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补码行。

通过键从一个内部表访问同一嵌套联接中的另一个表是被禁止的，如果它是由WHERE条件中的谓词引发的