【MySQL精通之路】SQL优化(1)-查询优化(10)-外部联接简化

主博客：

查询时FROM子句中的表达式在许多情况下都得到了简化。

在解析器阶段，具有右外部联接操作的查询被转换为仅包含左联接操作的等效查询。

在一般情况下，执行转换时，此右联接：

(T1, ...) RIGHT JOIN (T2, ...) ON P(T1, ..., T2, ...)

成为此等效的左联接：

(T2, ...) LEFT JOIN (T1, ...) ON P(T1, ..., T2, ...)

形式为T1 inner join T2 ON P（T1，T2）的所有内部联接表达式被作为联接到WHERE条件（或嵌入联接的联接条件，如果有的话）表达式 T1，T2，P（T1、T2）所代替。

当优化器评估外部联接操作的计划时，它只考虑这样的计划，即对于每个这样的操作，外部表在内部表之前被访问。优化器的选择是有限的，因为只有这样的计划才能使用嵌套循环算法执行外部联接。

考虑这种形式的查询，其中R（T2）极大地缩小了表T2中匹配行的数量：

SELECT * T1 FROM T1
  LEFT JOIN T2 ON P1(T1,T2)
  WHERE P(T1,T2) AND R(T2)

如果按写入的方式执行查询，则优化器别无选择，只能在更受限制的表T2之前访问限制较少的表T1，这可能会产生非常低效的执行计划。

相反，如果拒绝WHERE条件为null，MySQL会将查询转换为不包含外部联接操作的查询。（也就是说，它将外部联接转换为内部联接。）如果为外部联接操作生成的任何NULL补齐行的条件计算结果为FALSE或UNKNOWN，则称该条件为NULL拒绝。

因此，对于这种外部连接：

T1 LEFT JOIN T2 ON T1.A=T2.A

此类条件被拒绝为null，因为它们对于任何NULL补齐行（T2列设置为null）都不能为true：

T2.B IS NOT NULL
T2.B > 3
T2.C <= T1.C
T2.B < 2 OR T2.C > 1

这样的条件不会被NULL拒绝，因为它们对于NULL补齐行可能为true：

T2.B IS NULL
T1.B < 3 OR T2.B IS NOT NULL
T1.B < 3 OR T2.B > 3

检查条件是否为null的一般规则对于外部联接操作是拒绝的，这些规则很简单：

1.它的形式为A IS NOT NULL，其中A是任何内部表的属性

2.它是一个断言，包含对内部表的引用，当其中一个参数为NULL时，该表的计算结果为UNKNOWN

3.它是一个连词，包含一个空拒绝条件作为连词

4.它是空拒绝条件的变体

对于查询中的一个外部联接操作，条件可以为NULL拒绝，而对于另一个，条件不能为NULL拒绝。在这个查询中，WHERE条件对于第二个外部联接操作是NULL拒绝的，但是对于第一个操作不是NULL拒绝的：

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 LEFT JOIN T3 ON T3.B=T1.B
  WHERE T3.C > 0

如果查询中的外部联接操作拒绝WHERE条件为null，则外部联接操作将替换为内部联接操作。

例如，在前面的查询中，第二个外部联接被null拒绝，可以用内部联接代替：

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 INNER JOIN T3 ON T3.B=T1.B
  WHERE T3.C > 0

对于原始查询，优化器仅评估与单表访问顺序T1、T2、T3兼容的计划。对于重写的查询，它另外考虑访问顺序T3、T1、T2。

一个外部联接操作的转换可能会触发另一个的转换。因此，查询：

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 LEFT JOIN T3 ON T3.B=T2.B
  WHERE T3.C > 0

首先转换为查询：

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 INNER JOIN T3 ON T3.B=T2.B
  WHERE T3.C > 0

这相当于查询：

SELECT * FROM (T1 LEFT JOIN T2 ON T2.A=T1.A), T3
  WHERE T3.C > 0 AND T3.B=T2.B

剩余的外部联接操作也可以由内部联接代替，因为条件T3.B＝T2.B为空被拒绝。这将导致一个根本没有外部联接的查询：

SELECT * FROM (T1 INNER JOIN T2 ON T2.A=T1.A), T3
  WHERE T3.C > 0 AND T3.B=T2.B

有时，优化器成功地替换了嵌入的外部联接操作，但无法转换嵌入的外部连接。以下查询：

SELECT * FROM T1 LEFT JOIN
              (T2 LEFT JOIN T3 ON T3.B=T2.B)
              ON T2.A=T1.A
  WHERE T3.C > 0

转换为：

SELECT * FROM T1 LEFT JOIN
              (T2 INNER JOIN T3 ON T3.B=T2.B)
              ON T2.A=T1.A
  WHERE T3.C > 0

只能重写为仍包含嵌入外部联接操作的形式：

SELECT * FROM T1 LEFT JOIN
              (T2,T3)
              ON (T2.A=T1.A AND T3.B=T2.B)
  WHERE T3.C > 0

任何转换查询中嵌入外部联接操作的尝试都必须将嵌入外部联接的联接条件与WHERE条件一起考虑在内。在该查询中，嵌入的外部联接的WHERE条件不是null拒绝，而是嵌入的外部连接的联接条件T2.A=T1.A AND T3.C=T1.C是null拒绝：

SELECT * FROM T1 LEFT JOIN
              (T2 LEFT JOIN T3 ON T3.B=T2.B)
              ON T2.A=T1.A AND T3.C=T1.C
  WHERE T3.D > 0 OR T1.D > 0

因此，查询可以转换为：

SELECT * FROM T1 LEFT JOIN
              (T2, T3)
              ON T2.A=T1.A AND T3.C=T1.C AND T3.B=T2.B
  WHERE T3.D > 0 OR T1.D > 0