引申
Join是SQL语句中的常用操作,良好的表结构能够将数据分散在不同的表中,使其符合某种范式,减少表冗余、更新容错等。而建立表和表之间关系的最佳方式就是Join操作
join 连接,本质上是数据集的关联操作,不管是传统的rdbms 关系型数据库如oracle、mysql 还是现在大数据平台组件如hive 、spark sql都常用此连接逻辑
而hash join 是实现join操作的重要方式之一,此外还有nested loop、sort merge join。很多文章都直接写原理,业务一笔带过,所以写下记录一下加深自己的理解
业务分类
nested loop 场景:
- 数据量大表和小表
- 连接字段是大表类似id类的唯一性高值的字段,并且有索引
- 结果集量不大,10w以内比较理想(比如大表是某省运营商全量终端imei表,小表是终端tac标示(2g、3g、4g、5g) 限制小表2g关联取2g终端的统计情况)
hash join 场景
- 数据量大表和小表
- 小表最好最够小,能全部加载到内存里面(大数据组件会要求好很多)
- 结果集大(基本上大表的数据全部都会保留,如大表是某省交警总队违法表,小表是违法代码维表,直接关联 取违法大类的统计情况)
sort merge join
两张表按照关联字段排好序直接从第一个顺次关联(实际没用过 不多写了)
hash join实现原理
Hash join算法的一个基本思想就是根据小的row sources(称作build input 也就是前文提到的build table,我们记较小的表为S,较大的表为B)
建立一个可以存在于hash area内存中的hash table,然后用大的row sources(称作probe input,也就是前文提到的probe table) 来探测前面所建的hash table
hash join 实现过程
1. 一张小表被hash在内存中。因为数据量小,所以这张小表的大多数数据已经驻入在内存中,剩下的少量数据被放置在临时表空间中;
2. 每读取大表的一条记录,就和小表中内存中的数据进行比较,如果符合,则立即输出数据(也就是说没有读取临时表空间中的小表的数
据)。而如果大表的数据与小表中临时表空间的数据相符合,则不直接输出,而是也被存储临时表空间中。
3. 当大表的所有数据都读取完毕,将临时表空间中的数据以其输出。
如果小表的数据量足够小(小于hash area size),那所有数据就都在内存中了,可以避免对临时表空间的读写。 如果是并行环境下,前面中的第2步就变成如下了:
4. 每读取一条大表的记录,和内存中小表的数据比较,如果符合先做join,而不直接输出,直到整张大表数据读取完毕。如果内存足够,
Join好的数据就保存在内存中。否则,就保存在临时表空间中。
简略示意图:
假设大表R 小表是S,则hash join的时间复杂度是O(R+S) 。而nested loop的是O(R*S)
附加深入原理介绍参考
https://zhuanlan.zhihu.com/p/74491867
https://zhuanlan.zhihu.com/p/121301503
https://zhuanlan.zhihu.com/p/81398139