【数据库内核】物理算子之Join算子实现

 

概述

JOIN的本质是分别从N(N>=1)张表中获取不同的字段，进而得到最完整的记录行。比如我们有一个查询需求：在学生表(学号，姓名，性别)，课程表(课程号，课程名，学分)和成绩表(学号，课程号，分数)中查询所有学生的姓名，课程名和考试分数。如下：

 

名词介绍

• 驱动表: 数据量比较小的表。
• 被驱动表: 数据量比较大的表。
• Build表: 数据量比较小的表。
• probe表: 数据比较大的表。
• 哈希表（HashTable）: 又叫做散列表，是根据关键码值（即键值对）而直接访问的数据结构。也就是说，它通过把关键码映射到表中一个位置来访问记录，以加快查找速度。这个映射函数叫做散列函数。哈希表的查询时间复杂度是O(1)。

 

数据库为什么需要Join

关系数据库最重要的是"关系"，那么如何描述表与表之间的"关系"呢？这就不得不提下Join的语法了，数据库中Join操作是最复杂、代价最大的操作类型，也是OLAP场景中使用相对较多的操作。

另外，从业务层面来讲，用户在数仓建设的时候也会涉及Join使用的问题。通常情况下，数据仓库中的表一般会分为”低层次表”和“高层次表”。

所谓”低层次表”，就是数据源导入数据仓库之后直接生成的表，单表列值较少，一般可以明显归为维度表或者事实表，表和表之间大多存在外键依赖，所以查询起来会遇到大量Join运算，查询效率相对比较差。而“高层次表”是在”低层次表”的基础上加工转换而来，通常做法是使用SQL语句将需要Join的表预先进行合并形成“宽表”，在宽表上的查询因为不需要执行大量Join因而效率相对较高，很明显，宽表缺点是数据会有大量冗余，而且生成相对比较滞后，查询结果可能并不及时。

因此，为了获得实效性更高的查询结果，大多数场景还是需要进行复杂的Join操作。Join操作之所以复杂，不仅仅因为通常情况下其时间空间复杂度高，更重要的是它有很多算法，在不同场景下需要选择特定算法才能获得最好的优化效果。

下面描述下数据库中Join常用的算法还有哪些？

 

Nested Loop Join算法

Simple Nested Loop Join算法

一、原理和伪代码描述

<1> 原理

嵌套循环连接算法就是一个双层for循环 ，通过循环外层表的行数据，逐个与内层表的所有行数据进行比较来获取结果。

Nested-Loop Join 简单粗暴容易理解，就是通过双层循环比较数据来获得结果，但是这种算法显然太过于粗鲁，如果每个表有1万条数据，那么对数据比较的次数=1万 * 1万 =1亿次，很显然这种查询效率会非常慢。

 

<2> 伪代码描述

For each r in R do  For each s in S do    if join_condition(r, s) then       output r,s pair

 

二、案例

测试SQL如下所示

SELECT name,  score FROM  student  JOIN score ON student.sno = score.sno

 

算法推演如下:

 

 

Block Nested Loop Join算法

一、原理和伪代码描述

<1> 原理

缓存块嵌套循环连接算法意在通过一次性缓存外层表的多条数据，以此来减少内层表的扫表次数，从而达到提升性能的目的。

对比Simple Nested Loop Join算法的缺点在于其对于内表的扫描次数太多，从而导致扫描的记录太过庞大。Block Nested Loop Join算法较Simple Nested Loop Join的改进就在于可以减少内表的扫描次数，如果Join Buffer够大甚至可以和Hash Join算法一样，仅需扫描内表一次。

Join Buffer含义是用以缓存连接需要的列，然后以Join Buffer批量的形式和内表中的数据进行链接比较。

 

<2> 伪代码描述

For each tuple r in R dostore used columns from R in join buffer     For each tuple s in S do        If join_condition(r, s) then           Then output the tuple <r,s>

 

二、案例

测试SQL如下所示

SELECT name,  score FROM  student  JOIN score ON student.sno = score.sno

 

算法推演如下:

 

Index Nested Loop Join算法

一、原理和伪代码描述

<1> 原理

索引嵌套循环连接是基于索引进行连接的算法，索引是基于内层表的，通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录进行比较， 从而利用索引的查询减少了对内层表的匹配次数，优势极大的提升了join的性能。

 

<2> 伪代码描述

For each r in R do  For each si in SIndex do    If join_condition(r, si) then       output r,s pair

 

二、案例

测试SQL如下所示

SELECT name,  score FROM  student  JOIN score ON student.sno = score.sno

 

算法推演如下:

 

 

 

Merge Sort Join算法

Merge Sort Join算法

一、原理和伪代码描述

<1> 原理

Nested Loop Join嵌套循环是一种比较古老的连接匹配方式，特点是通过两层的循环结构，将符合条件的数据行整理出来。嵌套循环的最大缺陷之一，就是伴随着驱动表被驱动表之间的选择，以及大量随机读现象。

Merge Sort Join连接的优势就是可以一定程度上减少随机读的情况。合并排序连接的最大特征是在一次扫描的同时，就判断连接。不会像Nest Loop Join那样频繁的进行数据读取。使用这种方式的前提，就是连接的两个数据集合必须按照连接列的顺序进行排序。

在以下情况，优化器在连接大数据集的时候会考虑使用排序合并连接来替代哈希连接：

• 连接条件是不等式，比如：<，<=，>或者>=，相对应的，哈希连接要求是等式条件。
• 因为其他的一些操作要求排序，优化器会认为使用排序合并连接的成本更低。同时，如果有索引的话，那么第一个数据集就能够避免使用排序。但是，第二个数据集不论有没有索引，都会要求排序

 

<2> 伪代码描述

READ data_set_1 SORT BY JOIN KEY TO temp_ds1READ data_set_2 SORT BY JOIN KEY TO temp_ds2​READ ds1_row FROM temp_ds1READ ds2_row FROM temp_ds2​WHILE NOT eof ON temp_ds1,temp_ds2LOOP　　IF ( temp_ds1.key = temp_ds2.key ) OUTPUT JOIN ds1_row,ds2_row　　ELSIF ( temp_ds1.key <= temp_ds2.key ) READ ds1_row FROM temp_ds1　　ELSIF ( temp_ds1.key => temp_ds2.key ) READ ds2_row FROM temp_ds2END LOOP

 

二、案例

测试SQL如下所示

SELECT/*+use_merge(student_,score_)*/  * FROM  student_,  score_ WHERE  student_.sno = score_.sno;

 

Oracle计划如下:

 

算法推演如下:

 

 

 

Hash Join算法

In Memory Hash Join算法

一、原理和伪代码描述

<1> 原理

哈希连接主要分为两个阶段：

• 建立阶段（build phase)
• 探测阶段（probe phase)

 

建立阶段（build phase)

选择一个表（一般情况下是较小的那个表，以减少建立哈希表的时间和空间），对其中每个元组上的连接属性（join attribute)采用哈希函数得到哈希值，从而建立一个哈希表。

 

探测阶段（probe phase)

对另一个表，扫描它的每一行并计算连接属性的哈希值，与bulid phase建立的哈希表对比，若有落在同一个bucket的，如果满足连接谓词（predicate)则连接成新的表。

在内存足够大的情况下建立哈希表的过程时整个表都在内存中，完成连接操作后才放到磁盘里。但这个过程也会带来很多的I/O操作。

 

<2> 伪代码描述

for each row R1 in the build table    begin        calculate hash value on R1 join key(s)        insert R1 into the appropriate hash bucket    endfor each row R2 in the probe table    begin        calculate hash value on R2 join key(s)        for each row R1 in the corresponding hash bucket            if R1 joins with R2                return (R1, R2)    end

 

二、案例

测试SQL如下所示

SELECT  * FROM  student_,  score_ WHERE  student_.sno = score_.sno;

Oracle计划如下:

 

算法推演如下:

 

Grace Hash Join算法

一、原理和伪代码描述

<1> 原理

当 build 表数据量比较大，无法在内存中全部存下的时候，我们需要利用磁盘来完成 join 操作。

第一阶段分区阶段(Partition Phase)：把每个关系（relation）分别用同一个哈希函数h(x)在连接属性上进行分区（partition）。分区后每个元组分配到对应的bucket，然后分别把这些buckets写到磁盘当中。

第二阶段和普通的哈希连接类似，将分别来自于两个关系对应的bucket加载到内存中，为较小的那个bucket构建哈希表（注意，这里一定要用不同的哈希函数，因为数据很多的情况下不同值的哈希值可能相同，但不同值的两个哈希值都相同可能性非常小）

也有可能出现一个或多个bucket扔无法写入到内存的情况，这时可递归对每一个bucket采用该算法。与此同时这会增加很多时间，所以最好尽可能通过选择合理的哈希函数形成小的bucket来减少这种情况的发生。这是典型的分治的思想。

 

Grace hash join会将所有数据全部写入磁盘, 这样是比较影响性能的，从而衍生出了Hybrid grace hash join算法。

 

<2> 伪代码描述

// t1, t2 是待join两个表// PartCount是分片的个数// PartSize是一个分片下行的个数def graceJoin(t1, t2):    for row in t1:        hashValue = hash_func(row)        N = hashValue % PartCount;        write row to file t1_N;​    for row in t2:        hashValue = hash_func(row)        N = hashValue % PartCount;        write row to file t2_N;​    for i in range(0, PartSize):        if join_condition(t1_i, t2_i) then           Then output the tuple <t1_i, t2_i>

 

二、案例

测试SQL如下所示

SELECT  * FROM  student_,  score_ WHERE  student_.sno = score_.sno;

 

算法推演如下:

 

Hybrid Hash Join算法

一、原理和伪代码描述

<1> 原理

Hybrid hash join是 grace hash join之上的一个优化：第一个分区不必写入磁盘，可以避免第一个分区的磁盘io。

 

Hybrid hash join首先对小表进行分区，根据grace hash join的算法计算出分区号，如果数据属于第一个分区，则加入内存中的hashtable中，否则则写入该分区对应的磁盘文件中。第一个分区不用写入磁盘文件中。

 

<2> 伪代码描述

// t1, t2 是待join两个表// PartCount是分片的个数// PartSize是一个分片下行的个数// MemHT是partiion 0号的内存HashTabledef graceJoin(t1, t2):    for row in t1:        hashValue = hash_func(row)        N = hashValue % PartCount;        If N == 0 then            MemHT.insert(row)        ELSE            write row to file t1_N;​    for row in t2:        hashValue = hash_func(row)        N = hashValue % PartCount;        IF N == 0 then            IF MemHT.find(row) == True Then                Then output the tuple        write row to file t2_N;​    for i in range(1, PartSize):        if join_condition(t1_i, t2_i) then           Then output the tuple <t1_i, t2_i>

 

二、案例

测试SQL如下所示

SELECT  * FROM  student_,  score_ WHERE  student_.sno = score_.sno;

 

算法推演如下:

 

Hybrid hash join无法高效的利用内存的资源，目前随着大数据时代的来临，内存的成本不像原先那么昂贵，大内存的硬件越来越普及。所以我们是不是可以优化一下这个算法如下所示

 

 

 

三类算法对比情况说明

Nested Loop，Hash Join，Merge Join对比

类别	Nested Loop	Hash Join	Merge Join
使用条件	任何条件	等值连接（=）	等值或非等值连接(>，<，=，>=，<=)，‘<>’除外
相关资源	CPU、磁盘I/O	内存、临时空间	内存、临时空间
特点	当有高选择性索引或进行限制性搜索时效率比较高，能够快速返回第一次的搜索结果。	当缺乏索引或者索引条件模糊时，Hash Join比Nested Loop有效。通常比Merge Join快。在数据仓库环境下，如果表的纪录数多，效率高。	当缺乏索引或者索引条件模糊时，Merge Join比Nested Loop有效。非等值连接时，Merge Join比Hash Join更有效。
缺点	当索引丢失或者查询条件限制不够时，效率很低；当表的纪录数多时，效率低。	为建立哈希表，需要大量内存。第一次的结果返回较慢。	所有的表都需要排序。它为最优化的吞吐量而设计，并且在结果没有全部找到前不返回数据。

 

 

结论

本文讲述了Join算子的几种实现方式，每一种方式都有自己适用的场景，那么适用哪一种Join算法是有我们数据库的大脑优化器来决定的。

 

参考资料

• https://blog.csdn.net/gp_community/article/details/104778618/
• https://www.docs4dev.com/docs/zh/apache-hive/3.1.1/reference/Hybrid_Hybrid_Grace_Hash_Join__v1.0.html
• https://openproceedings.org/2012/conf/edbt/CaoZCT12.pdf
• https://mysqlserverteam.com/hash-join-in-mysql-8/
• https://www.youtube.com/watch?v=59C8c7p_hII
• https://zhuanlan.zhihu.com/p/35040231
• https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms189313(v=sql.105)?redirectedfrom=MSDN
• https://www.geeksforgeeks.org/join-algorithms-in-database/

分享大数据行业的一些前沿技术和手撕一些开源库的源代码
微信公众号名称：技术茶馆
微信公众号ID    :    Night_ZW