[15-445]Join Algorithms memo （Join 为什么要用小表做驱动表）
Nested Loop Join
这一章节主要讲解 join 的算法，我想记录一些重点的地方。有趣的是 关于 Nested Loop join 对驱动表为什么小表会更好这个问题，搜遍简中的 blog 都是一些错误的描述而且排名还贼高。而且大家都是互相拷贝。。互相抄内容，错来错去都差不多。
我们基于上面的数据集来探讨这个问题。并且我们暂时假设，我们不会使用到任何索引。
首先需要申明一个重要的点，那些有问题的描述多数在讨论外表有多少行，内部表有多少行。然后为了减少连接次数，所以我们应该在外部用小表作为驱动表去驱动内部的表可以减少连接次数。
这个说法，也不能说不对，但是他肯定不完全对。怎么来定义一个小表，我们不只用行数。因为我们真正在读取数据的时候我们读的是 数据页 行是数据的延伸是 tuple。所以归根到底如果我们要 follow 上面的说法
我们应该说我们应该在外部尽量少的扫描数据页，这样可以减少连接消耗。而不是数据行。
同时我们还需要意识到一点，减少连接消耗的本质。。其实就是减少数据页的扫描。
如果我们拿 R 表做外表 S 表做内部表我们需要扫描的数据总数大概是
M + (m * N)
如果我们拿 S 表做外部表 R 表做内部表我们需要扫描的总数大概是
N + (M * n)
如下图
这样可以看出来，即使我们使用最笨的办法，但是我们将小表放在外面，依然是可以节省不少 io 的。
我们外部 for 循环遍历 500page 然后拿着每一条 40000 * 每一条扫一次 page，R 表有 1000 个 page。数据就是这么来的。
那么有没有更好的办法呢🤔
当然有！这里我就集中说了。
1. 可以将驱动表的单页数据都读完之后再换页，而不是以 tuple 为单位进行换页扫描。这样的话时间会下降到 N + (N * M)
2. 当我们内存足够大的时候，我们可以完全将驱动表缓存起来，进一步减少对磁盘的扫描，这样时间会下降到 N + M, N 整个被缓存起来了，不需要再读盘，所以只需要读一遍 M 就行了。
所以简中上的错误其实还蛮离谱的。。。基本没有讲为什么驱动表要用小表，所以我觉得有必要记录澄清一下。
Index Nested Loop Join
刚才其实我们考虑的情况都是硬来的情况，实际情况会好一些，我们经常在 join 的时候可以用上索引。来看这个图
看 cost 就行了，不可避免的我们还是要至少扫描一次外表。但是我们可以选择页数少的那个表，然后命中了索引里面我们通过索引查可以简单类比成一个常数复杂度。然我们我们仅需 m * C 或者 M * C 扫盘即可。
SORT-MERGE JOIN
这种办法有两个阶段，首选我们需要对两个需要 join 的表进行排序，然后我们用 merge 的方法依次滑动两个表上的指针来查看是否被 join 上，来决定是否输出结果。
唯一值得 High light 的一点是我们可能会因为两边有重复数据而进行指针的回退。最差的情况可能会回退回 M*N 次扫描。
他的复杂度会加上一个排序的复杂度。所以如果两个表本身就是有序的，或者我们对结果要求有序。那么就比较适合使用 merge-join sort。另外说一下上图的 B 代表内存可以存下的 page 数量。
Hash Join
好了重头戏来了，Andy 在课程的最开始就说了，hash join 是现在 join 最先进高效的方法。我正好也查了一些资料了解到，我最熟悉的 MySQL 也是到 8.0.18 约 2019 年 11 月才将之前的 Nested loop join 换成了 hash join，并保持了 Nested loop 的支持。但是会尽量使用 hash join 来帮助多表连接以
显著提升效率。
同样实现它有两个步骤
1. 构建表的 hash table。 使用哈希算法对外表相关键进行 hash。
2. 对内部表使用相同的 hash 函数进行 hash。如果匹配到，检查是否碰撞，如果没有我们就可以输出结果。
这里同样涉及到之前会遇到的问题，我们的 键值对 键进行 hash，那么值我们却有多种选择。我们可以选择存储所有该行数据，也可以选择只存储相关的 identifier。比如主键 id 最后再用回表查找。只是对数据量大小的权衡操作。这里我多提一点。
像 MySQL 这样的数据库之前在构建二级索引的时候一般就会使用第二种方案，然后这种如果没有索引覆盖就会有一次回表在查询大规模的数据的时候多一次回表的操作远超你的想象。所以之前我用过 aliyun 的一款产品叫 hybirddb（HTAP数据库现在已经没有维护了），它支持我可以再除了 cluster 以外的 key 上同样建立完整的数据存储，用大量空间换取了不回表大幅加快了 OLAP 查询的效率。扯远了。
这样做基本就 M+N 就能得到结果。外表扫一遍算出 hash。
右边探测表扫一遍，直接看有没有左边表的值即可（这里会有一个碰撞的问题，咱们暂时忽略碰撞问题下面有优化手段）。
如果咱们内存不够大，无法放下 hash 表用于对比，我们还要对数据进行拆分。于是又引出了 partitioned hash join
这个算法会将不同行的数据 hash 之后放进不同的桶里。如果在同一个桶里的 page 我们可以再继续进行常规的 simple hash join 即可。
另外还有比较极端的情况，如果我们分区完毕之后还不够，我们可能需要继续递归不够的桶再继续进行分区，使用不同的 hash 算法再进行一次 hash 针对第一次 hash 完之后得到的桶里的值再用二号 hash 得到跟多的桶。
另外针对 step2 也就是要检查是否碰撞这件事情上，我们还能通过布隆过滤器进行优化，逻辑大概是，我们在 build 外表的时候同时生成一个布隆过滤器，然后 probe 表在探测的时候先过布隆过滤器通过之后再去原标查。这样优化了每个表都去 hash 表匹配，并且遇到重复的情况还需要进行 hash 检测的效率问题。因为通过布隆过滤器相当于一定有值，一定有值我们再进行相关的 hash 冲突探查就不会浪费时间了。
最终的效率，我们可以参考下图。
Reference:
https://15445.courses.cs.cmu.edu/fall2022/slides/11-joins.pdf
http://mysql.taobao.org/monthly/2019/11/02/
