常见的join算法

大表的Join算法无论在传统的关系数据库、在OLAP数据仓库还是在离线批处理系统中都是至关重要的一个算法。大表Join利用的数据冗余特性（join列在两个表都做存储），为用户的数据统计需求，提供了一个单间的视图，不需关心底层数据的存储格式、join的处理过程等等。

关于Join算法的选择、多表Join的顺序等问题，涉及到比较复杂的查询优化的技术，采用了动态规划、遗传算法、启发式搜索等比较前沿性技术，本文不做深究，只讨论下几种常见的Jion算法和其变体，及其适用的场景，顺便引出Join实现过程中需要考虑的技术因素。

基本的Jion算法分为三种

假定有小表R，大表S，R与S做自然连接；

1. nested loop join：从R中依次取出关系r，以r的连接key值去扫描大表S；外层循环扫描小表，内存循环扫描大表
2. sort-merge join：R与S分别按照连接键做全排序，然后对两个顺序数组做归并，归并的过程中输出key值相等的关系；
3. hash join：将小表R按照key生成一个内存的hash表（buid过程）；然后顺序扫描大表S，扫描过程中去查询上一步生成hash表确定是否满足等值条件（probe过程）。这是最简单的simple hash join。
   

 先看nested loop join的改进，

• block nested loop：小表如果一次无法完全load到内存，则对小表每次导入的block做一个hash table，R中一个block内key值相等的tuple，只需要扫描一次S。

hash join的改进

• grace hash join：内存很可能放不下小表key值，那么要分两步走。
  • build过程：将两个表扫描方式load到内存，针对连接键用同一个hash函数对两个表进行分区并写入到磁盘（跟map reduce的shuffle很像）；
  • probe过程：针对partion下表相同的分区表，做simple hash join；
  • 如果第一次分区后，小表分区后的key值内存仍然不够，则需要对上次的结果进一步做hash分区的递归操作，直到内存全部放下。
• hybrid hash join：针对grace hash join的一些优化，即内存够用的情况下，R的第一个分区的pation0的hash表和S的partion0，在build过程中保留在内存，这样build过程一结束，不需要要partion0再次从磁盘load到内存
• hybrid hybird grace join：Hive采用的一种hash join的变体，跟简单的hybrid join不同，针对大内存的场景做了很大优化。
  • build过程，针对R建多个hash表分区，如果内存不够，就把某个最大的hash表分区spill到磁盘，spill之后对应该分区的R的tuple会写到另外一个文件，在下一步probe的时候针对spill之前和之后的文件做merge；build过程中，先build R的数据，针对R中的key构建一个bloom filter，buildS表分区时，首先查询下这个bloom filter，如果不存在则直接丢弃。
  • probe过程，开始后，内存已经有尽量多的R的hash表，这就保证了内存缓存最大利用；如果某个分区的hash表并全在内存中，需要跟磁盘上的数据做merge。

补充，

• 以mapredcue方式做分布式的大表join，第一步跟单击的grace hash join很像，对两个表按照key做一个redistribute，同一个key的连接放到同一个node。
• 不同的算法适用于不同的场景。查询优化器对于join算法的选择，会根据不同的join算法计算一个代价的预估，包括io时间、cpu计算时间等，选择预估时间最小的join算法。
  

参考

http://dev.mysql.com/doc/refman/5.6/en/bnl-bka-optimization.html
https://cwiki.apache.org/confluence/display/Hive/Hybrid+Hybrid+Grace+Hash+Join,+v1.0
https://technet.microsoft.com/en-us/library/ms189313(v=sql.105).aspx
https://en.wikipedia.org/wiki/Nested_loop_join
https://en.wikipedia.org/wiki/Hash_join
https://en.wikipedia.org/wiki/Sort-merge_join