「这是我参与11月更文挑战的第1天，活动详情查看：2021最后一次更文挑战」

join是一个很重要的算子，但是它有很多实现的具体方式，需要根据不同的使用场景来选择。

1. nested loop join

一般来说，都是期望较小的table作为外表（outer table）。

外层循环的每一行都需要遍历内层循环来找到相匹配的行（如果内层有索引则可以直接进行index seek）。

所以外层循环次数少，内层循环有序的情况可以使用。

通常选用smaller table作为outer table；期望尽可能的将outer table放在buffer中；期望尽可能使用index来在inner table中查找匹配。

simple nested loop join

纯遍历，无cache， M + （m * N），m是外表中的数据count·

For every tuple in R, it scans S once.

block nested loop join

For every block in R, it scans R once.

对outer table中的每一页数据，从inner table中遍历每一页来比对。这意味着outer中的block（page）需要放在缓存中。 M + （M * N）。

如果DBMS有B个buffers，那么用一个buffer来存放inner的block（每次读入一个来进行join），一个buffer来存放join操作的output，有B-2个buffer可以用来存放outer的数据，这样每次外表可以一次性读入b-2个page在内存中。

index nested loop join

Basic nested loop join为啥不好？因为对外表中的每个tuple，都需要顺序的检查一遍内表是否有匹配。

为了避免顺序扫描查找，可以使用索引在内表上进行匹配。

• 使用已存在的索引
• 临时建一个（哈希表或b+树）

需要内层有index。M + （m * C）

总结

总结一下nested loop join

• 选择小的表作为外表
• 将外表中的page尽可能多的放入内存缓冲区中
• 使用索引来检索内表会大大提高效率

2. sort-merge join

• phase1: sort 在join key上进行排序，可以使用外部归并排序
• phase2: 两个指针同时在排序后的两张表上进行遍历，命中了就输出。有的时候需要回溯（左表有重复值的时候）

什么时候使用sort-merge join？

输入的两端有序的情况下，可以用合并有序数组的思想进行join。效率很高。

要求输出在join key上有序的时候，干脆直接用这个先排好序。一举两得。

如果需要额外进行显式sort，那么sort的成本就不如直接使用hash join；

如果查询中存在order by, group by, distinct等可能触发sort的情况，那么就可以顺势使用merge join。

排序成本 + （M+N)的合并成本

3. hash join

数据量大且没有索引的时候，对数据进行hash操作，然后两端分别从对应的hash bucket中去进行匹配。

hash的属性就是join key。只有equi-joins才能用（因为才有join key）。

basic hash join

• build：在join key上构建哈希。
• probe（探测）：去哈希表中探查命中的数据。

所以也称为一个是build table（外表）一个是probe table（内表）。

hash表中，value的部分可以存放full tuple，或者只存放tuple identifier。优劣对比类似后面说的两种output的类型。

哈希表中的value

• Full tuple：需要占用更多的内存，但是不用再去外表的tuple中找了
• Tuple id：对于列存储比较好，因为本身也不是直接能获取到full tuple。如果join的选择性不高的话，也很好。

• probe phase optimization：可以增加一个Bloom filter。在prob hash table之前，先去check filter，来排除不在可能域内的情况。也叫做sips。

也就是，build table在构建哈希表的时候同时生成一个bloom filter。probe table在探查哈希表之前先通过filter进行一次粗筛选过滤。

SIPS（sideways information passing strategy)

一种消除多余计算的手段。例如上面说的，在join算子的一端提取出一个过滤集，把它放在join的另一端，这样对另一端关系中的访问和计算过程都起到了限制的作用。

例子：

• 目标 找到部门预算>100000的年轻人（age<30）中，薪水高于部门的数据
• 优化 PartialResult先筛选出了所有符合要求的部门中的年轻人
• 优化 Filter构造了一个过滤器视图，从PR视图中找到了包含这些年轻人的所有部门did
• 优化 LimitedDevAvgSal视图，只需要从Filter中包含的部门里面进行平均薪水计算
• query：只需要比较PR视图中的年轻人薪水，和他们所属部门的平均薪水

grace hash join

如果buffer不够大，会造成table频繁从buffer中置换。

• Build phase：先把两个表需要join的列，首先用一个哈希去进行分区partition
• Probe phase：在对应的分区上进行对应的probe操作（线性探查）

为了防止单个hash bucket无法在内存中放得下，可以再进行递归划分recursive partitioning 来对bucket再进行后续的哈希划分。

检索过程中，对每一级bucket，找到内外表中对应的page，然后在两个page上应用nested loop join（线性探查）。两个page都在内存中，所以很快。

如下图，这里是因为经过h1后的bucket还是太大，所以进一步进行h2来对其进行分解；所以在另一侧，对落入1中的数据，也需要在进行h2来找到对应的二级bucket。

3.总结

• 哈希join通常都是最快的。但是它只适用于等值连接。
• 排序可以适用于，结果需要有序的时候，或者数据不均匀的情况（使用哈希会导致数据倾斜比较严重）。