外连接优化

来自泡泡学习笔记
跳到导航 跳到搜索

外连接包括LEFT JOIN和RIGHT JOIN。


MySQL实现了A LEFT JOIN B join_specification的方式如下：

  • 将表B设置为依赖于表A和A所依赖的所有表。
  • 将表A设置为依赖于LEFT JOIN条件中使用的除B之外的所有表。
  • LEFT JOIN条件用于决定如何从表B中检索行。（换句话说，WHERE子句中的任何条件都不会被使用。）
  • 执行所有标准的连接优化，唯一的例外是始终在依赖的所有表之后读取表。
  • 执行所有标准的WHERE优化。
  • 如果表A中存在与WHERE子句匹配的行，但没有与ON条件匹配的表B行，则会生成一个额外的B行，其中所有列的值都设置为NULL。
  • 如果你使用LEFT JOIN来查找在某个表中不存在的行，并且在WHERE部分中使用了类似 col_name IS NULL 的测试，其中col_name是一个声明为NOT NULL的列，MySQL在找到与LEFT JOIN条件匹配的一行后，会停止继续搜索更多的行（对于特定的键组合）。


RIGHT JOIN的实现方式类似于左连接，只是表的角色被颠倒了。右连接会被转换为等价的左连接。


对于LEFT JOIN，如果生成的NULL行的WHERE条件始终为false，那么LEFT JOIN会被改为内连接。例如，如果在以下查询中t2.column1为NULL时，WHERE子句将为false： 

SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;


因此，将查询转换为内连接是安全的： 

SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;


在MySQL 8.0.14及更高版本中，由常量字面表达式引起的无关紧要的WHERE条件在准备阶段被移除，而不是在优化的后期阶段进行移除，此时连接已经被简化。提前移除无关紧要的条件允许优化器将外连接转换为内连接；这可以改善包含WHERE子句中无关紧要条件的外连接查询的执行计划，例如： 

SELECT * FROM t1 LEFT JOIN t2 ON condition_1 WHERE condition_2 OR 0 = 1


优化器在准备阶段就能看到0 = 1始终为false，使得OR 0 = 1成为多余的部分，因此会将其移除，最终查询变为： 

SELECT * FROM t1 LEFT JOIN t2 ON condition_1 where condition_2


现在优化器可以将查询重写为内连接，如下所示： 

SELECT * FROM t1 JOIN t2 WHERE condition_1 AND condition_2


现在，优化器可以在查询计划中更好地使用表t2，即使在表t1之前。如果要提供关于表连接顺序的提示，可以使用优化器提示。另外，也可以使用STRAIGHT_JOIN。但是，STRAIGHT_JOIN可能会阻止索引的使用，因为它禁用了半连接转换。

取自“http://bubblestudy.info/wiki/index.php?title=外连接优化&oldid=1457”