MySQL查询优化之八-外部Join简化(Outer Join Simplification)

如需转载请标明出处
QQ技术交流群：129518033

环境：
MySQL版本：5.5.15
操作系统：windows

本文讨论外部Join简化(Outer Join Simplification)。

在很多情况下，查询的FROM子句中的表达式被简化了。

在解析器阶段，具有右外join操作的查询将转换为仅包含左join操作的等同查询。在一般情况下，转换是这样进行的：

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

等效的左join：

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

所有形式为T1 INNER JOIN T2 ON P（T1，T2）的内部join表达式都被替换为列表T1，T2，P（T1，T2）作为与WHERE条件（或者联接条件嵌入连接，如果有的话）。

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

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

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

如果查询按照书面执行，则优化器别无选择，只能访问限制较多的表T2之前的限制较少的表T1，这可能会产生非常低效的执行计划。

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

因此，对于这个外join：

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

诸如这些的条件被空拒绝，因为它们对于任何NULL补充行都不为真（将T2列设置为NULL）：

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

诸如这些条件不是空的，因为它们对于NULL补充行可能是真的：

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

用于检查外部join操作的条件是否为空的一般规则很简单：

对于查询中的一个外join操作，条件可以为空，否则为空。在此查询中，第二个外join操作的WHERE条件为空，但第一个外join操作不为null：

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

如果查询中的外join操作的WHERE条件为空，则外join操作被内join操作取代。

例如，在前面的查询中，第二个外join是null拒绝的，并且可以由内join替换：

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。

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

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

其余的外部join操作也可以由内部join替换，因为条件T3.B = T2.B为空 - 拒绝。这导致一个查询没有外join：

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

有时候，优化器会成功替换嵌入的外部join操作，但不能转换嵌入的外部join。以下查询：

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

只能对仍包含嵌入外join操作的表单重写：

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

任何尝试在查询中转换嵌入的外join操作都必须考虑嵌入外join的连接条件以及WHERE条件。在此查询中，对于嵌入式外join，WHERE条件不为null，但嵌入外join T2.A = T1.A AND T3.C = T1.C的连接条件为空拒绝：

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

Reference：
dev.mysql.com/doc/refman/…

如需转载请标明出处
QQ技术交流群：129518033