常见的join算法

大表的Join在OLTP/OLAP领域很常见。对于使用SQL的用户,不需关心底层数据的存储格式、join的处理算法。

       CBO(cost based optimizor)会根据join table的统计信息,选择Join算法、多表Join还有顺序问题。例如join顺序的选择就很讲究,pgsql的优化器就就采用了动态规划、遗传算法、启发式搜索等比较复杂的技术。对CBO部分,本文不做深究,只讨论下几种常见的Join算法和其变体,及其适用的场景,顺便引出Join实现过程中需要考虑的技术因素。

基本的Jion算法分为三种

假定有小表R,大表S,R与S做自然连接;

  1. nested loop join:从R中依次取出关系r,以r的连接key值去扫描大表S;外层循环扫描小表,内存循环扫描大表。这是最简的一种join方式。
  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算法。
  • mysql只有最简单的nested loop join。pgsql支持hash join。tidb支持hash join。

以上这些都是单击引擎的Join算法。分布式系统的Join跟单机join有所区别。 SparkSQL实现了三种,

  • broadcast join,小表广播,大表按照key range切分
  • shuffle join,跟hybrid hash join有些类似,数据按照hash进行切分,但不生成临时文件而是发送到不同节点,相当于内存数据做一些resharding
  • sorted merge join 比较适合两个大表连接,跟单机对比要做一些key range的resharding
  • sparkSQL2.2以前的版本join算法的选择是基于规则的,2.2开始支持CBO,今年夏天刚release。

参考

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

https://databricks.com/blog/2017/08/31/cost-based-optimizer-in-apache-spark-2-2.html


阅读更多
文章标签: join join算法 算法
个人分类: 算法
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭