12.MySQL优化Outer Join Simplification讲解

介绍

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

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

在一般情况下，转换是这样执行的:

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

等价的left join为：

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

所有的inner join表达式form T1 INNER JOIN T2 ON P(T1,T2)被替换成列表T1,T2, P(T1,T2)然后加入Where条件作为连接的条件（或嵌入连接的连接条件）

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

考虑此表单的查询，其中R(T2)极大地缩小表T2中匹配行的数目：

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

如果查询是按照写入的方式执行的，那么优化器别无选择，只能在限制程度较高的表T2之前访问限制程度较低的表T1，这可能产生非常低效的执行计划。
相反，如果WHERE条件为null-rejected，MySQL会将查询转换为没有外连接操作的查询。（也就是说，它将外连接转换为内连接）。一个outer join操作，假如他对任何NULL-complemented的一行生成的结果是FALSE or UNKNOWN那么他是null-rejected的。

因此，对于这个外连接：

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

这些条件属于null-rejected，因为他们对于任何null-complemented的行都不能为真（T2列设置为NULL）：

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

以下这些条件是不能为null-rejected，因为对于null-complemented的行这些条件可能为真：

# 上面讲解的比较绕，其实原因很简单，因为下面的条件是T1.B OR T2.B所以满足了T1的时候可能T2的值是NULL
T2.B IS NULL
T1.B < 3 OR T2.B IS NOT NULL
T1.B < 3 OR T2.B > 3

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

• 在建表时设置字段A为 IS NOT NULL
• 它包含一个null-rejected条件
• 一个谓词包含的参考是，当有一个参数为NULL时内连接的表的计算结果为UNKNOWN。
• 是否为一个null-rejected条件的分离

一个条件在一个查询的outer join条件中可以是null-rejected的而在另一个是非null-rejected的。在下面这个查询中，对于第二个outer join条件他是null-rejected，但是对于第一个则不是。

# 这里也比较绕，因为假如T3.B=T1.B不成立，T3的所有列的值都是NULL，所以T3.C > 0能去掉T3中所有不符合null-rejected的列
SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 LEFT JOIN T3 ON T3.B=T1.B
  WHERE T3.C > 0

在一个查询中如果某个outer join的条件是null-rejected的，那么这个outer join条件可以转变为inner join条件，上面的例子等价于如下SQL

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

对于原始的SQL，执行计划计算之后只有一个访问方式，T1、T2、T3，在改写之后又多了一个可访问方式T3、T1、T2。

一个outer join的转换可以出发另外一个outer 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

其余的outer join也可以被替换为inner join因为T3.B=T2.B是null-rejected,替换完后所有的outer join都消失了：

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

有时，一个成功的优化可以替换一个嵌入式的outer join操作，但是不能转换一个嵌入式的outer 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

这只能重写为仍包含嵌入外连接操作的表单：

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

在查询中转换嵌入式outer join操作的任何尝试都必须考虑嵌入outer join和WHERE条件的连接条件。

在这个查询中，这个Where条件对于outer join不是null-rejected，但是这个join条件outer join条件T2.A=T1.A和T3.C=T1.C是null-rejected:

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