陈晨辰 · 2020年01月10日

MySQL之LEFT JOIN问题汇总

使用ON和WHRERE对表数据过滤

背景

left join在我们使用mysql查询的过程中可谓非常常见,比如博客里一篇文章有多少条评论、商城里一个货物有多少评论、一条评论有多少个赞等等。但是由于对join、on、where等关键字的不熟悉,有时候会导致查询结果与预期不符,所以今天我就来总结一下,一起避坑。

这里我先给出一个场景,并抛出两个问题,如果你都能答对那这篇文章就不用看了。

假设有一个班级管理应用,有一个表classes,存了所有的班级;有一个表students,存了所有的学生,具体数据如下(在线SQL:https://www.liaoxuefeng.com/w...):

SELECT * FROM classes;
id name
1 一班
2 二班
3 三班
4 四班

SELECT * FROM students;
id class_id name gender
1 1 小明 M
2 1 小红 F
3 1 小军 M
4 1 小米 F
5 2 小白 F
6 2 小兵 M
7 2 小林 M
8 3 小新 F
9 3 小王 M
10 3 小丽 F

那么现在有两个需求:
1、找出每个班级的名称及其对应的女同学数量
2、找出一班的同学总数

对于需求1,大多数人不假思索就能想出如下两种sql写法,请问哪种是对的?

SELECT c.name, count(s.name) as num

FROM classes c left join students s 
on s.class_id = c.id 
and s.gender = 'F'
group by c.name

或者
SELECT c.name, count(s.name) as num

FROM classes c left join students s 
on s.class_id = c.id 
where s.gender = 'F'
group by c.name

对于需求2,大多数人也可以不假思索的想出如下两种sql写法,请问哪种是对的?

SELECT c.name, count(s.name) as num

FROM classes c left join students s 
on s.class_id = c.id 
where c.name = '一班' 
group by c.name

或者
SELECT c.name, count(s.name) as num

FROM classes c left join students s 
on s.class_id = c.id 
and c.name = '一班' 
group by c.name

请不要继续往下翻 !!先给出你自己的答案,正确答案就在下面。
.
.
.
.
.
.
.
.
答案是两个需求都是第一条语句是正确的,要搞清楚这个问题,就得明白mysql对于left join的执行原理,下节进行展开。

原理

mysql 对于left join的采用类似嵌套循环的方式来进行从处理,以下面的语句为例:

SELECT * FROM LT LEFT JOIN RT ON P1(LT,RT)) WHERE P2(LT,RT)
其中P1是on过滤条件,缺失则认为是TRUE,P2是where过滤条件,缺失也认为是TRUE

该语句的执行逻辑可以描述为:
FOR each row lt in LT {// 遍历左表的每一行
BOOL b = FALSE;
FOR each row rt in RT such that P1(lt, rt) {// 遍历右表每一行,找到满足join条件的行

IF P2(lt, rt) {//满足 where 过滤条件
  t:=lt||rt;//合并行,输出该行
}
b=TRUE;// lt在RT中有对应的行

}
IF (!b) { // 遍历完RT,发现lt在RT中没有有对应的行,则尝试用null补一行

IF P2(lt,NULL) {// 补上null后满足 where 过滤条件
  t:=lt||NULL; // 输出lt和null补上的行
}         

}
}
当然,实际情况中MySQL会使用buffer的方式进行优化,减少行比较次数,不过这不影响关键的执行流程,不在本文讨论范围之内。

从这个伪代码中,我们可以看出两点:
1、右表限制用ON
如果想对右表进行限制,则一定要在on条件中进行,若在where中进行则可能导致数据缺失,导致左表在右表中无匹配行的行在最终结果中不出现,违背了我们对left join的理解。因为对左表无右表匹配行的行而言,遍历右表后b=FALSE,所以会尝试用NULL补齐右表,但是此时我们的P2对右表行进行了限制,NULL若不满足P2(NULL一般都不会满足限制条件,除非IS NULL这种),则不会加入最终的结果中,导致结果缺失。
2、左表限制用WHERE
如果没有where条件,无论on条件对左表进行怎样的限制,左表的每一行都至少会有一行的合成结果,对左表行而言,若右表若没有对应的行,则右表遍历结束后b=FALSE,会用一行NULL来生成数据,而这个数据是多余的。所以对左表进行过滤必须用where。

分析总结

下面展开两个需求的错误语句的执行结果和错误原因:

需求1
name num
一班 2
二班 1
三班 2
需求1由于在where条件中对右表限制,导致数据缺失(四班应该有个为0的结果)

需求2
name num
一班 4
二班 0
三班 0
四班 0
需求2由于在on条件中对左表限制,导致数据多余(其他班的结果也出来了,还是错的)

通过上面的问题现象和分析,可以得出了结论:
在left join语句中,左表过滤必须放where条件中,右表过滤必须放on条件中

SQL 看似简单,其实也有很多细节原理在里面,一个小小的混淆就会造成结果与预期不符,所以平时要注意这些细节原理,避免关键时候出错。

小表与大表关联(join)的性能分析

误区

            经常看到一些Hive优化的建议中说当小表与大表做关联时,把小表写在前面,这样可以使Hive的关联速度更快,提到的原因都是说因为小表可以先放到内存中,然后大表的每条记录再去内存中检测,最终完成关联查询。这样的原因看似合理,但是仔细推敲,又站不住脚跟。
            
   多小的表算小表?如果所谓的小表在内存中放不下怎么办?我用2个只有几条记录的表做关联查询,这应该算是小表了,在查看reduce的执行日志时依然是有写磁盘的操作的。实际上reduce在接收全部map的输出后一定会有一个排序所有键值对并合并写入磁盘文件的操作。写入磁盘(spill)有可能是多次的,因此有可能会生成多个临时文件,但是最终都要合并成一个文件,即最终每一个reduce都只处理一个文件。 
   我做了一个实验,用1条记录的表和3亿多条记录的表做join,无论小表是放在join的前面还是join的后面,执行的时间几乎都是相同的。再去看reduce的执行日志,1条记录的表在join前或者join后两次查询的reduce日志几乎也是一摸一样的。如果按照上面的说法把join左侧的表放内存等待join右侧的表到内存中去检测,那么当3亿多条记录的表放在join左侧时,内存肯定是无法容下这么多记录的,势必要进行写磁盘的操作,那它的执行时间应该会比小表在join前时长很多才对,但事实并不是这样,也就说明了上面说到的原因并不合理。 

原理

         事实上“把小表放在前面做关联可以提高效率”这种说法是错误的。
         
         正确的说法应该是“把重复关联键少的表放在join前面做关联可以提高join的效率。” 
         
   分析一下Hive对于两表关联在底层是如何实现的。因为不论多复杂的Hive查询,最终都要转化成mapreduce的JOB去执行,因此Hive对于关联的实现应该和mapreduce对于关联的实现类似。而mapreduce对于关联的实现,简单来说,是把关联键和标记是在join左边还是右边的标识位作为组合键(key),把一条记录以及标记是在join左边还是右边的标识位组合起来作为值(value)。在reduce的shuffle阶段,按照组合键的关联键进行主排序,当关联键相同时,再按照标识位进行辅助排序。而在分区段时,只用关联键中的关联键进行分区段,这样关联键相同的记录就会放在同一个value list中,同时保证了join左边的表的记录在value list的前面,而join右边的表的记录在value list的后面。 
         

例子

例如A join B ON (A.id = b.id) ,假设A表和B表都有1条id = 3的记录,那么A表这条记录的组合键是(3,0),B表这条记录的组合键是(3,1)。排序时可以保证A表的记录在B表的记录的前面。而在reduce做处理时,把id=3的放在同一个value list中,形成 key = 3,value list = [A表id=3的记录,B表id=3的记录]

   接下来我们再来看当两个表做关联时reduce做了什么。Reduce会一起处理id相同的所有记录。我们把value list用数组来表示。 
         
   1)   Reduce先读取第一条记录v[0],如果发现v[0]是B表的记录,那说明没有A表的记录,最终不会关联输出,因此不用再继续处理这个id了,读取v[0]用了1次读取操作。 
         
   2)   如果发现v[0]到v[length-1]全部是A表的记录,那说明没有B表的记录,同样最终不会关联输出,但是这里注意,已经对value做了length次的读取操作。 
         
   3)   例如A表id=3有1条记录,B表id=3有10条记录。首先读取v[0]发现是A表的记录,用了1次读取操作。然后再读取v[1]发现是B表的操作,这时v[0]和v[1]可以直接关联输出了,累计用了2次操作。这时候reduce已经知道从v[1]开始后面都是B 表的记录了,因此可以直接用v[0]依次和v[2],v[3]……v[10]做关联操作并输出,累计用了11次操作。
         
   4)   换过来,假设A表id=3有10条记录,B表id=3有1条记录。首先读取v[0]发现是A表的记录,用了1次读取操作。然后再读取v[1]发现依然是A表的记录,累计用了2次读取操作。以此类推,读取v[9]时发现还是A表的记录,累计用了10次读取操作。然后读取最后1条记录v[10]发现是B表的记录,可以将v[0]和v[10]进行关联输出,累计用了11次操作。接下来可以直接把v[1]~v[9]分别与v[10]进行关联输出,累计用了20次操作。 
         
   5)   再复杂一点,假设A表id=3有2条记录,B表id=3有5条记录。首先读取v[0]发现是A表的记录,用了1次读取操作。然后再读取v[1]发现依然是A表的记录,累计用了2次读取操作。然后读取v[2]发现是B表的记录,此时v[0]和v[2]可以直接关联输出,累计用了3次操作。接下来v[0]可以依次和v[3]~v[6]进行关联输出,累计用了7次操作。接下来v[1]再依次和v[2]~v[6]进行关联输出,累计用了12次操作。 
         
   6)   把5的例子调过来,假设A表id=3有5条记录,B表id=3有2条记录。先读取v[0]发现是A表的记录,用了1次读取操作。然后再读取v[1]发现依然是A表的记录,累计用了2次读取操作。以此类推,读取到v[4]发现依然是A表的记录,累计用了5次读取操作。接下来读取v[5],发现是B表的记录,此时v[0]和v[5]可以直接关联输出,累计用了6次操作。然后v[0]和v[6]进行关联输出,累计用了7次操作。然后v[1]分别与v[5]、v[6]关联输出,累计用了9次操作。V[2] 分别与v[5]、v[6]关联输出,累计用了11次操作。以此类推,最后v[4] 分别与v[5]、v[6]关联输出,累计用了15次操作。 
         
   7)   额外提一下,当reduce检测A表的记录时,还要记录A表同一个key的记录的条数,当发现同一个key的记录个数超过hive.skewjoin.key的值(默认为1000000)时,会在reduce的日志中打印出该key,并标记为倾斜的关联键。 

结论

   写在关联左侧的表每有1条重复的关联键时底层就会多1次运算处理。
         最终得出的结论是:把重复关联键少的表放在join前面做关联可以提高join的效率
   假设A表有一千万个id,平均每个id有3条重复值,那么把A表放在前面做关联就会多做三千万次的运算处理,这时候谁写在前谁写在后就看出性能的差别来了。

参考:
1、https://segmentfault.com/a/11...
2、https://blog.csdn.net/qq_2644...

推荐阅读
关注数
2
文章数
95
目录
极术微信服务号
关注极术微信号
实时接收点赞提醒和评论通知
安谋科技学堂公众号
关注安谋科技学堂
实时获取安谋科技及 Arm 教学资源
安谋科技招聘公众号
关注安谋科技招聘
实时获取安谋科技中国职位信息