海量数据 - join处理

本周我们进入join的处理环节，其实在一开始学“连接”这个概念的时候，我感觉最晕菜的事儿是个类Join的区别。。left join 、right join 、outer join、inner join 、cross join 。看起来好晕。

依照惯例，我主要还是希望从原理的角度来介绍一下join的主要处理方式，这篇只会讲单机处理方式，多机模式我们会在之后的分布式章节进行介绍。

 

先是场景

TableA(col1)

0

1

2

3

 

 

TableB(col1)

1

2

3

4

 

 

 

OK，让我们从最常见的inner join开始吧~

Inner join是我们日常最常见的join类型，也是默认的join类型，当我们输入TableA a join TableB b on a.col1=b.col1，我们会获得这样的一个结果：

Inner join result
TableA(col1)	TableB(col1)
1	1
2	2
3	3

可以看到，inner join是一种最容易理解的join类型，也就是“寻找那些在TableA和TableB中符合col1 = col2的数据”

在这基础之上，我们再来看left outer join 和right outer join。

当我们需要left outer join的时候，我们会使用 TableA a left outer join TableB b on a.col1 = b.col1，我们会获得如下结果：

Left outer join result
TableA(col1)	TableB(col1)
0	NULL
1	1
2	2
3	3

可以看到，最明显的变化就是，TableA（左表）中的所有数据都被保留了，而如果这行数据在TableB(右表)中没有找到对应，这就是left outer join的含义。

同理，Right outer join 自然可以进行知识迁移：保留在TableB(右表)中的所有数据，如果这些数据在左表中没能找到匹配数据，则用NULL填充。

 

right join result
TableA(col1)	TableB(col1)
1	1
2	2
3	3
NULL	4

 

而full outer join呢？其实就是保留TableA和TableB中的所有数据，并尽可能找到对应表中的匹配行进行对应，如果没能找到对应的行，则用NULL代替。

Full join result
TableA(col1)	TableB(col1)
0	NULL
1	1
2	2
3	3
NULL	4

 

最后还有一种join的方式，叫cross join .也叫笛卡尔积，一般来说会比较容易和full outer join混淆，但其实不是一个东西。笛卡尔积就是列出所有的可能性的join方式。

cross join result
TableA(col1)	TableB(col1)
0	1
0	2
0	3
0	4
1	1
1	2
1	3
1	4
2	1
2	2
2	3
2	4
3	1
3	2
3	3
3	4

 

好了，在复习一下：

inner join ，最强约束匹配，只有全部符合的数据才会留下。

Left join ,保留左表内所有数据，右表无对应的用NULL代替。

right join 保留右表内所有数据，左表无对应的用NULL代替。

Full join 保留左右表内所有数据，对应表内无对应的用NULL代替。

Cross join 将左表内的每一行数据与右表内的每一行数据进行关联后输出。

 

在了解了join语法的基本原理后，我们进入到join算法的部分。其实这方面oracle总结的已经很好了，join的算法主要就三种：nested loop join ,Hash Join 和sort merge join 。理解join的算法后，其实会发现这是一把好锤子，到处都有钉子可以敲~这个我们后面再来八~

 nested loop join 

Nested loop的做法其实就是大家能想到的最简单，也是最笨的方法：若表A和表B使用nested loop做join,那么就是从A中取一条记录，然后在右表中用等值查询的方式进行匹配，当然，为了效率，他会自动选择B表中的索引进行这种匹配以加速查找。

举个例子：

TableA(col1)

0

1

2

3

 

TableB(col1)

1

2

3

4

 

如果这两个表要使用nest loop算法的话，会如何处理呢？

我们假设TableA表是驱动表，那么运算过程如下：

从TableA中取出一行记录，我们认为是0 这一行，然后，去TableB中查找，是否有col1 = 0的记录呢？ 发现没有，于是进行下一行，从Table1中找到下一行，也就是1这一行，去B中做查找操作，是否有col1 = 1的记录呢？发现有，于是就拼成一组记录后返回。

很容易可以发现，在nested loop join的过程中，驱动表(TableA)是无法使用任何索引的，而目标表(TableB)是可以使用二分查找来加速的，因此，原则上驱动表必须是一个数据量较小的表，被驱动表则最好有针对匹配条件的索引，这样就可以达到最优查找效率。

 Hash Join

HashJoin的思路则是使用Hash 映射表的方式进行匹配的。

他的假定也是一个大表和一个小表进行join，如果小表能全部的放到内存中，那么，如果能够将小表以匹配条件作为key，记录作为value，组成一个hashMap，我们就可以利用Map的contains方法来快速的找到所有符合join要求的结果了。

还是以上面的表格来举个例子：

假设TableA是小表，TableB是大表，那么先将TableA放到内存Hash表内，然后从B表中取出每一条记录，去TableA的Hash表内进行匹配即可。

 sort merge join

最后的一种join策略，sort merge join ,若两个表都能够按照匹配条件进行排序，那么sort merge 就能够用很低的内存消耗来完成join的操作了。因为两张表都是有序的，所以只需要顺序的从左表和右表中取出数据，进行拼装即可。

具体的做法是：首先，如果TableA和TableB没有按照col1 asc排序，那么先进行排序，然后，重复以下步骤：第一轮1： 从TableA中取一条记录，0， 从TableB中取出一条记录，1，发现不匹配，但TableA: 0于是TableA指针下移，获得下一个数据1 ，TableB 不动。第二轮，TableA : 1 = TableB:1 ，于是找到一行符合要求的记录，TableA指针下移，TableB指针也下移。不断重复，直到找到所有符合要求的记录。

可以看到，这种方式在处理两个有序大表join的时候，效率远远超过其他方式，并且 Sort merge join是处理full outer join的效率最高的方法之一（当然一般也没人用这种join方式- -）。。

 

每一种方式，都有其主要的适应场景，因此需要根据表的实际大小来做动态决策~ 小表join大表，一般选择nested loop或hashJoin，而大表join 大表，往往选择sort merge join的方式。

 

再稍微扩展一点点，不知道大家看到sort merge 算法以后是不是有种似曾相识的感觉~？ 我们在列存，倒排索引中，似乎都看到了类似的算法。

没错，他们都是一类问题，解决方法也都是类似的~

可以认为，join，尤其是inner join，就是多个集合取公共交集的过程，在倒排索引中是在多个搜索词中取交集，在列存中是在符合要求的多列中取交集，行存join语法是在多个表格中取交集的过程，在where条件中就是and条件，甚至连使用索引的过程其实都是索引集合和主表数据集合之间取交集的过程。

 

因此，如果我们能了解在各类场景中如何进行取交集的操作，并且了解在各类场景中取交集的过程有什么限制，应该如何进行优化的话，那我们就能够很轻松的处理数据查询中的很大一部分场景了~是不是很有吸引力呢？

 

做下简单的小结吧：

Nested loop 和hash join ，都能够比较轻松的处理小表和大表的取交集场景，其中nested loop要求大表有索引，如果小表可以完全的被放到内存中，那么hash join是一个比较好的处理大小表join的方式。

Sort merge join 则要求两张表都需要按照匹配条件排序，这个的构建成本略高，但它的优势是，排序过程对内存要求较低，并且可以充分的并行执行，因此可以发现，它经常出现在列存，倒排索引等各类条件变化频繁，数据量非常大的场景中。

转自:http://blog.sina.com.cn/s/blog_693f08470101rjt0.html