MySQL8.0官方文档第八章优化（十一）——外连接简化

8.2 优化SQL语句

8.2.1 优化 SELECT 语句

8.2.1.10 外连接简化

查询语句中&＃xff0c;FROM子句的表与表连接表达式在很多情况下都能得到简化。

在解析器处理阶段&＃xff0c;右外连接操作的查询都会被转换为仅包含左外连接操作的等效查询。通常&＃xff0c;都会对这样的右连接执行如下的等效转换&＃xff1a;

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


变成以下等效的左连接&＃xff1a;

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


所有的内连接表达式&＃xff0c;例如&＃xff1a;T1 INNER JOIN T2 ON P(T1,T2)形式都会替换为 T1,T2 列表&＃xff0c;而 P(T1,T2)作为把所有的WHERE的条件&＃xff08;或嵌套连接的连接条件&＃xff0c;如果有的话&＃xff09;连接后的表现形式。&＃xff08;译者&＃xff1a;这句话的意思是用 FROM T1, T2 ON P(T1, T2)表示表的内连接和WHERE条件&＃xff09;

当优化器安排表之间的连接操作计划时&＃xff0c;它只考虑以下计划&＃xff1a;对于每个外连接操作&＃xff0c;外部表都会先于内部表被访问。优化器的选择是有限的&＃xff0c;因为只有这样的计划才允许使用嵌套循环算法执行外连接。

考虑以下查询&＃xff0c;其中 R(T2) 条件会极大地减小表T2中匹配行的数量&＃xff1a;

SELECT * FROM T1 LEFT JOIN T2 ON P1(T1, T2) # 译者&＃xff1a;官方文档原文中漏写了关键字FROMWHERE P(T1, T2) AND R(T2)


如果按照上面书写的查询顺序执行&＃xff0c;那么优化器就别无选择&＃xff0c;只能先访问限制较少的表T1&＃xff0c;再访问限制较多的表T2&＃xff0c;这可能会导致非常低效的执行计划。

相反&＃xff0c;如果WHERE条件是空拒绝的&＃xff08;null-rejected&＃xff09;&＃xff0c;那么&＃xff0c;MySQL会将该查询转换为没有外连接操作的查询&＃xff08;也就是说&＃xff0c;它将外连接转换为内连接&＃xff09;。如果一个条件对于由连接操作生成的任何空补行&＃xff08;NULL-complemented row&＃xff09;的测试结果都为FALSE或UNKNOWN&＃xff08;不知道&＃xff09;&＃xff0c;则该条件被称为外连接操作的空拒绝条件。&＃xff08;译者&＃xff1a;这里的意思是&＃xff1a;如果连接操作会生成一些空补行&＃xff0c;而这些空补行在WHERE条件测试中结果一定是假&＃xff0c;就不能实际生成空补行&＃xff0c;因此&＃xff0c;这种外连接的结果与内连接是一样的。&＃xff09;

因此&＃xff0c;对于以下外连接&＃xff1a;

T1 LEFT JOIN T2 ON T1.A &＃61; T2.A


如果该外连接添加以下任意条件&＃xff0c;该外连接就会转成内连接。这是因为这些条件都是空拒绝的&＃xff0c;它们对于所有空补行&＃xff08;空补行是由于表T2的所有列都设置为NULL造成的&＃xff09;都不可能是真&＃xff1a;

T2.B IS NOT NULL
T2.B > 3
T2.C <&＃61; T1.C
T2.B < 2 OR T2.C > 1


而以下这些条件就不是空拒绝的&＃xff0c;因为它们对于空补行可能测试为真&＃xff1a;

T2.B IS NULL # 如果T2.B是NULL&＃xff0c;则该表达时为真
T1.B < 3 OR T2.B IS NOT NULL # 由于T1.B <3 可以是真&＃xff0c;可以不管T2如何&＃xff0c;整个表达式为真
T1.B < 3 OR T2.B > 3


检查外联接操作的条件是否为空拒绝的基本规则很简单&＃xff1a;

• 是 A IS NOT NULL的这种形式&＃xff0c;其中 A 是任何内部表的属性

• 是包含对内部表引用的谓词&＃xff0c;当其参数之一为NULL时&＃xff0c;该谓词的测试结果为未知&＃xff08;UNKNOWN&＃xff09;

• 是一个在连接操作时出现空拒绝条件的连词

• 是空拒绝条件的拆分形式&＃xff08;disjunction&＃xff09;

查询中一个外连接操作中的条件可以是空拒绝的&＃xff0c;而另一个连接操作的条件是不空拒绝的。在以下查询中&＃xff0c;第二个外连接操作中的WHERE条件是空拒绝的&＃xff0c;而第一个不是。

SELECT * FROM T1 LEFT JOIN T2 ON T2.A &＃61; T1.ALEFT JOIN T3 ON T3.B &＃61; T1.BWHERE T3.C > 0 # 该条件是空拒绝的


对于原始查询&＃xff0c;优化器只评估与单个表 T1、T2、T3 这样的访问顺序兼容的执行计划。但是&＃xff0c;对于重写的查询&＃xff0c;它还可考虑为 T3、T1、T2 这样的访问顺序。

一个外连接操作的转换可以触发另一个外连接操作的转换。因此&＃xff0c;该查询&＃xff1a;

SELECT * FROM T1 LEFT JOIN T2 ON T2.A &＃61; T1.ALEFT JOIN T3 ON T3.B &＃61; T2.B # 译者不太明白为何可以把T1.B变成T2.B&＃xff1f;&＃xff08;与上面语句对比&＃xff09;WHERE T3.C > 0


首先转换为以下查询&＃xff1a;&＃xff08;译者&＃xff1a;原文说&＃xff0c;这是第一转换&＃xff0c;说明上面第二个连接条件本来是&＃xff1a;T3.B &＃61; T2.B&＃xff0c;所以&＃xff0c;以上两条查询语句本身是没有关系的。&＃xff09;

SELECT * FROM T1 LEFT JOIN T2 ON T2.A &＃61; T1.AINNER JOIN T3 ON T3.B &＃61; T2.B # 由于T3表是空拒绝的&＃xff0c;所以把左连接转换为内连接WHERE T3.C > 0


第二次转换&＃xff0c;等效于以下查询&＃xff1a;

SELECT * FROM (T1 LEFT JOIN T2 ON T2.A &＃61; T1.A), T3WHERE T3.C > 0 AND T3.B &＃61; T2.B


因为条件T3.B &＃61; T2.B是空拒绝的&＃xff0c;所以留下的外连接操作也可转换为内连接。这将导致最终转换为一个完全没有外部联接的查询&＃xff1a;

SELECT * FROM (T1 INNER JOIN T2 ON T2.A &＃61; T1.A), T3WHERE T3.C > 0 AND T3.B &＃61; T2.B


有时&＃xff0c;优化器能成功地替换内嵌的外连接操作&＃xff0c;但无法转换嵌入的外连接。看以下查询&＃xff1a;

SELECT * FROM T1 LEFT JOIN(T2 LEFT JOIN T3 ON T3.B &＃61; T2.B)ON T2.A &＃61; T1.AWHERE T3.C > 0


转换为&＃xff1a;

SELECT * FROM T1 LEFT JOIN(T2 INNER JOIN T3 ON T3.B &＃61; T2.B)ON T2.A &＃61; T1.AWHERE T3.C > 0


只能重写为仍然包含内嵌的外连接操作的形式&＃xff1a;

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


在查询中&＃xff0c;任何尝试转换内嵌外连接的操作都必须考虑内嵌外连接的连接条件和WHERE条件。在以下查询中&＃xff0c;对于嵌入的外连接&＃xff0c;WHERE条件不是空拒绝的&＃xff0c;但是&＃xff0c;嵌入外连接的连接条件 T2.A &＃61; T1.A AND T3.C &＃61; T1.C是空拒绝的&＃xff1a;

SELECT * FROM T1 LEFT JOIN(T2 LEFT JOIN T3 ON T3.B &＃61; T2.B)ON T2.A &＃61; T1.A AND T3.C &＃61; T1.C # 应该是 T3.C &＃61; T1.C 条件可能为假&＃xff0c;导致整个条件为假。WHERE T3.D > 0 OR T1.D > 0 # T1.D > 0 可以为真&＃xff0c;导致WHERE子句为真&＃xff0c;所以不是空拒绝的。


因此&＃xff0c;该查询可以转换为&＃xff1a;

SELECT * FROM T1 LEFT JOIN(T2, T3)ON T2.A &＃61; T1.A AND T3.C &＃61; T1.C AND T3.B &＃61; T2.BWHERE T3.D > 0 OR T1.D > 0


上一集 MySQL 8.0 官方文档 第八章 优化&＃xff08;十&＃xff09;—— 外连接优化

下一集 MySQL 8.0 官方文档 第八章 优化&＃xff08;十二&＃xff09;—— 多范围读优化