常见的join算法

转载 2018年04月16日 18:07:15

大表的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


WordPress博客模板系统开发

-
  • 1970年01月01日 08:00

用std::set来保存char*/const char*

我们如何能在c++中使用set来保存char*/const char*呢? 答案是提供定制的template argument -> Compare. #include #include ...
  • sunlylorn
  • sunlylorn
  • 2011-08-24 14:51:18
  • 1067

CSDN博客客户端

  • 2013年05月25日 14:00
  • 682KB
  • 下载

cdsn ——博客

你丑啥
  • rzxtest005
  • rzxtest005
  • 2016-10-19 18:24:33
  • 3842

hash join 原理和算法

  • 2011年03月21日 16:21
  • 50KB
  • 下载

数据库常见的三种join方式

数据库常见的join方式有三种:inner join, left outter join, right outter join(还有一种full join,因不常用,本文不讨论)。这三种连接方式都是将...
  • blueheart20
  • blueheart20
  • 2007-06-18 16:36:00
  • 878

MySQL中Join算法实现原理分析

在MySQL 中,只有一种 Join 算法,就是大名鼎鼎的 Nested Loop Join,他没有其他很多数据库所提供的 Hash Join,也没有 Sort Merge Join。顾名思义,Nes...
  • xxrena
  • xxrena
  • 2015-06-08 17:38:33
  • 673

MySQL Join算法与调优白皮书(三)

Batched Key Access Join Index Nested-Loop Join虽好,但是通过辅助索引进行链接后需要回表,这里需要大量的随机I/O操作。若能优化随机I/O,那么就能极...
  • shaochenshuo
  • shaochenshuo
  • 2015-09-18 15:47:13
  • 539

浅谈查询优化器中的JOIN算法

  查询优化器都是支持JOIN操作的,而SQL Server 中主要有以下三类JOIN算法:Nested Loop、Sort-Merge以及Hash Join。尽管每种算法都并不是很复杂,但考虑到性能...
  • hdy007
  • hdy007
  • 2007-02-28 09:53:00
  • 2269

Sort-Merge Joins(归并连接算法)

看到一篇较好的归并连接算法描述,是我苦苦寻找的文章,转载之。 Sort-Merge Joins       As we have seen from the discussion...
  • fz2543122681
  • fz2543122681
  • 2017-08-07 21:37:16
  • 608
收藏助手
不良信息举报
您举报文章:常见的join算法
举报原因:
原因补充:

(最多只允许输入30个字)