mysql left jion 优化_MySQL LEFT JOIN实例及优化分析

摘要：在本教程中，我们将学习有关MySQL LEFT JOIN子句以及如何应用它来从两个或多个数据库表中查询数据，之后你将更深入的了解MySQL LEFT JOIN优化分析。

MySQL LEFT JOIN

MySQL的LEFT JOIN子句允许从两个或多个数据库表中查询数据。LEFT JOIN子句是SELECT语句的可选部分，该语句在FROM子句之后出现。

假设我们打算从两个表：T1 和 T2 中查询数据。以下是连接两个表LEFT JOIN子句的语法：

SELECT

T1.c1, T1.c2, T2.c1, T2.c2

FROM

T1

LEFT JOIN

T2 ON T1.c1 = T2.c1;

当我们使用 LEFT JOIN 子句将 T1 表连接 T2 表， 如果左表 T1 中的一行基于连接条件(T1.c1=T2.c1)与右表 T2 一行相匹配， 此行将被包括在结果集中。如果左表中的行不能匹配右表中的行，则左边的表中的行也被选择，并与右表中的一个“伪造”的行合并。这个”伪造”的行包含 SELECT 子句中，它的所有列相对应的值为：NULL。

换句话说，LEFT JOIN子句允许从左，右表中选择匹配的行记录，再加上左表(T1)中的所有行，即使没有在右表(T2)中找到匹配。

下面的图示，可帮助您更直观地理解 MySQL LEFT JOIN子句是如何工作的。两个圆之间的交点表示那些符合两个表中的行记录，左圆的其余部分表示 T1表没有与 T2表相匹配的记录。左表中的所有行都会被包括在结果集中。

MySQL LEFT JOIN - 维恩图

请注意，返回的行也必须在 WHERE 条件和 HAVING 子句匹配(如果这些子句有在查询中使用的话)。

MySQL LEFT JOIN示例

MySQL LEFT JOIN子句 - 连接2个表示例

让我们来看看customers和orders表。

在上面的数据库图表：

order 表中的每一个订单必须属于customer 表中的某一客户；

在 customer 表中每个客户在order 表中有零个或多个订单；

要查找每个客户自己的所有订单，可以使用LEFT JOIN 子句如下

SELECT

c.customerNumber,

c.customerName,

orderNumber,

o. STATUS

FROM

customers c

LEFT JOIN orders o ON c.customerNumber = o.customerNumber;

结果如下：

左表是 customer 表，因此所有的客户信息都包含在结果集中。但是在结果集中也有的行记录拥有客户数据，但没有订单数据，例如，上面图中查询结果的第一和第二行记录。这些行中的订单数据均为：NULL。这意味着，这些客户在 order表中没有任何订单数据(他们没有下过订单)。

如果使用 INNER JOIN 子句来替换 LEFT JOIN 子句，那么只得到在 order 表有记录的客户，也就是说，只获得那些下过订单的客户。

MySQL LEFT JOIN子句来查找不匹配的行

当我们想找到左表中那些不与右表中的行匹配的行记录，LEFT JOIN子句是非常有用的。要查找那些两个表之间不匹配的行记录，需要在 SELECT 语句添加 WHERE 子句条件，只选择在右表中的列值包含 NULL 值的所有行记录。

例如，要查找出没有订购的任何产品的所有客户信息，使用下面的查询：

SELECT

c.customerNumber,

c.customerName,

orderNumber,

o. STATUS

FROM

customers c

LEFT JOIN orders o ON c.customerNumber = o.customerNumber

WHERE

orderNumber IS NULL;

结果如下：

MySQL LEFT JOIN优化分析

在MySQL中，A LEFT JOIN B join_condition执行过程如下：

1)根据表A和A依赖的所有表设置表B。

2)根据LEFT JOIN条件中使用的所有表(除了B)设置表A。

3)LEFT JOIN条件用于确定如何从表B搜索行。(换句话说，不使用WHERE子句中的任何条件)。

4)可以对所有标准联接进行优化，只是只有从它所依赖的所有表读取的表例外。如果出现循环依赖关系，MySQL提示出现一个错误。

5)进行所有标准WHERE优化。

6)如果A中有一行匹配WHERE子句，但B中没有一行匹配ON条件，则生成另一个B行，其中所有列设置为NULL。

7)如果使用LEFT JOIN找出在某些表中不存在的行，并且进行了下面的测试：WHERE部分的col_name IS NULL，其中col_name是一个声明为 NOT NULL的列，MySQL找到匹配LEFT JOIN条件的一个行后停止(为具体的关键字组合)搜索其它行。

RIGHT JOIN的执行类似LEFT JOIN，只是表的角色反过来。

联接优化器计算表应联接的顺序。LEFT JOIN和STRAIGHT_JOIN强制的表读顺序可以帮助联接优化器更快地工作，因为检查的表交换更少。请注意这说明如果执行下面类型的查询，MySQL进行全扫描b，因为LEFT JOIN强制它在d之前读取：

SELECT *

FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)

WHERE b.key=d.key;

在这种情况下修复时用a的相反顺序，b列于FROM子句中：

SELECT *

FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)

WHERE b.key=d.key;

MySQL可以进行下面的LEFT JOIN优化：如果对于产生的NULL行，WHERE条件总为假，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可以在表t1之前使用表t2。为了强制使用表顺序，使用STRAIGHT_JOIN。