本文问题

1. 在包括JOIN操作的查询中，括号有什么影响？
2. MySQL中，是否可以更改联接的顺序？
3. 什么是嵌套循环，对于嵌套循环，如何进行优化？

JOIN顺序

括号对于联接操作的影响

通常，在只包含内连接操作的语句中可以去除括号。直接去除外联接操作中的括号可能会改变表达式的结果。

示例

假设有以下状态的表t1,t2,t3

• t1包括行(1),(2)
• t2包括行(1,101)
• t3包括行(101)

外联接示例

# 初始语句
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

这两个表达式的结果是不相同的。
第一个表达值返回结果集(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;
+------+------+------+------+
| 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 |
+------+------+------+------+

结论

如果忽略join表达式中外联接操作中的括号，可能会更改原始表达式的结果。更确切的说，不能忽略左联接右侧操作符中的的括号，或者右联接左侧操作符中的括号。换句话说，我们不能忽略在外联接操作中的内联接操作的括号。其他操作符中（外联接外部）的括号可以被忽略。
比如下面这个表达式：

(t1,t2) LEFT JOIN t3 ON P(t2.b,t3.b)
等同与：
t1, t2 LEFT JOIN t3 ON P(t2.b,t3.b)
# 因为（t1,t2）在LEFT JOIN的左侧，所以可以直接忽略该括号。
# 或者说，因为（t1,t2）在外联接（LEFT JOIN）的外侧，所以可以忽略该括号

嵌套联接

嵌套联接定义

当在join表达式中执行join操作的顺序不是直接从左到右执行时，我们称之为嵌套联接。
例如以下查询：

SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b) ON t1.a=t2.a
  WHERE t1.a > 1
# 先执行t2 left join t3，然后再执行t1 left join上一步的结果

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,t3）然后执行t2 left join结果
# join(t2,t3)=join(j2 inner join t3) 

上述表达式中的t2 LEFT JOIN t3 ON t2.b=t3.b和t2, t3就是嵌套联接内层的联接。
在第一个查询中是一个左联接操作，在第二个查询中是个内联接操作。

括号对于嵌套联接的影响

在第一个查询中，可以忽略括号，join表达式的语法结果决定连接执行的顺序是一致的。
但是第二个查询不能忽略括号，尽管这里的join表达式可以在忽略括号的情况下明确地解释。在我们的语法中，第二个查询中(t2,t3)需要使用括号，尽管理论上可以在忽略括号的情况下进行查询：我们仍然需要对这个查询使用明确的语法结果，因为LEFT JOIN和ON是表达式(t2,t3)的左右分隔符的角色。

结论

上述示例表明：

• 对于只调用inner joins的join表达式，可以忽略括号，并且left join等同于right join。事实上，表在任何顺序上join结果都是一致的
• 但是，对于外连接或者混合了内连接的外连接，删除括号可能会导致结果集变更。

嵌套外连接查询执行过程

嵌套外联接查询的执行类似于内连接。更确切地说，是利用nested-loop join算法的变体。回想一下使用NLJ执行查询的算法。假设有一个覆盖了T1,T2,T33个表的join查询：

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)是join条件。而P(T1,T2,T3)是一个跨越3个表的WHERE条件。
nested-loop join算法将会按照以下顺序执行查询：

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中的行，并使用NULL表示t3中的每一列。这种行被称为NULL-complemented（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 { #对于每个T1表中的行
  BOOL f1:=FALSE; # 标志f1=false
  FOR each row t2 in T2 such that P1(t1,t2) { # 对于每个符合t1,t2条件的T2中的行
    BOOL f2:=FALSE; # 标志f2=false
    FOR each row t3 in T3 such that P2(t2,t3) { #对于每个符合t2,t3条件的 T3中的行
      IF P(t1,t2,t3) { #如果符合WHERE条件
        t:=t1||t2||t3; OUTPUT t; #输出3个表的列值
      }  # 判断结束
      f2=TRUE; # f2=Ture
      f1=TRUE; # f2=True
    } # T3表循环结束
    IF (!f2) { # 如果f2=false
      IF P(t1,t2,NULL) { #如果符合t2,t2表的条件
        t:=t1||t2||NULL; OUTPUT t; # 输出符合条件的t1,t2表的列值，t3列值用NULL代替
      }  # 判断结束
      f1=TRUE; # f1=true
    } 
  }  #T2循环结束
  IF (!f1) {
    IF P(t1,NULL,NULL) { # 如果符合t1表的条件
      t:=t1||NULL||NULL; OUTPUT t; #输出符合t1表条件的列值，t2,t3表中的列值都用null代替
    }
  }
} #T1循环结束

通常，对于外连接的每个内部的循环操作，会设置一个标志，这个标志在循环开始时关闭，并在循环结束后检查标志。如果当前循环外表中的行的内部操作中发现了匹配的行，标志开启。在循环结束后，如果标志仍然是关闭的，表示当前外表中的行在内层表中没有发现匹配的数据，在这种情况下，使用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;
    }
  }
}

条件外推（"pushed-down" conditions）

内联接优化示例

在上面讨论内联接的嵌套循环算法时，没有提到可能对查询性能有巨大影响的条件。没有提到"pushed-down" conditions。假设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表中进入内循环的行数。然后，查询的执行效率可以大量提升。
pushed-down条件，将循环内部的WHERE条件，外推到相关表的循环外部处理。

外联接优化示例

对于外连接查询，只有外部表在内部表中具有匹配的行之后才对WHERE条件进行检查。
也就是说，对于外联接查询，不能将嵌套循环内部的条件直接外推到循环外部进行处理。

还是上面的示例

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

对于这个示例，嵌套循环算法使用如下使用pushed-down条件：

FOR each row t1 in T1 such that C1(t1) { #对于每个符合条件的t1表行
  BOOL f1:=FALSE; # 设置标志f1为FALSE
  FOR each row t2 in T2 
      such that P1(t1,t2) AND (f1?C2(t2):TRUE) { # 对于T2中行，满足P1(t1,t2)条件，如果f1=TRUE,还需要满足C2(t2)条件，否则只满足P1条件即可
    BOOL f2:=FALSE; #设置标志f2为FALSE
    FOR each row t3 in T3
        such that P2(t2,t3) AND (f1&&f2?C3(t3):TRUE) { #对于T3中的行
      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;
  }
}

问题答案

1. 在包括JOIN操作的查询中，括号有什么影响？
   在只包含内连接操作的语句中可以直接去除括号，更改内联接顺序不会变更结果集
   但是不能直接忽略在外联接操作中的内联接操作的括号，会改变联接顺序，导致结果集变更。
2. MySQL中，是否可以更改联接的顺序？
   对于内联接，可以任意更改处理联接中的表的顺序。
   对于外联接，必须先处理外部表，然后再处理内部表。
3. 什么是嵌套循环，对于嵌套循环，如何进行优化？

• 从联接顺序方面
  嵌套内联接可以任意更改嵌套顺序
  嵌套外联接，外联接的顺序不能更改。如果一个内联接在嵌套连接的最内层，可以更改这个内联接相关的表的顺序。
• 从WHERE条件方面
  嵌套内联接可以直接将WHERE条件外推到循环外部进行处理，对于不符合WHERE的行，不需要进入循环。
  嵌套内联接无法直接将WHERE条件外推到循环外部，只有在循环内部的表中查找到匹配的行后才能进行WHERE条件判断