CMU15445-11章-join连接算法の笔记

* 为什么要使用join连接：因为个关系模型relational model，在存储的时候就是按照它的连接关系给分开了，在数据库里面存数据的时候，就是按照它的关系分开去存储的，在使用这个数据的时候就需要使用join算子将割开的这些按照连接关系割开的这个数据给它再重重组起来。

* join连接原则：进行连接的时候尽量将小的表放在前面/连接的左侧/外表outer table/驱动表

* 需要以磁盘IO的方式分析R join S的开销（原因：磁盘IO相比于读取内存或CPU计算来说，数量级明显要高），在SQL语句里面，或者是执行计划里面，join是最常见的一个算子，是经常是需要被小心的优化的，因为join在执行计划里面它是最耗时的，也最容易出问题的。有时R join S可以用RxS列举出来一个巨大的表，如果r有10行，s有10行，它俩交叉组合变成一个100行的表，然后再用谓词去筛选，这种方法呢是非常低效的。除了笛卡尔基之外，我们更倾向于后面的几种比较高效的为了降低开销的几种算法。

join算法开销：

两个表：

R表：M页，每页有m条数据

S表：N页，每页有n条数据

join算法：

Nested Loop Join：嵌套循环——Simple / Stupid、Block、Index

Sort-merge Join：排序归并

Hash Join：哈希连接

总结：哈希连接在执行时绝大多数时是最好的。
但是——对于非均匀数据（相同的值特别多），Sort-Merge排序更好。
——当结果需要排序时，Sort-Merge排序更好。
好的数据库管理系统会同时使用哈希和排序（或两者都使用）。

Simple / Stupid Nested Loop Join：简单嵌套循环连接

嵌套循环伪代码：

foreach tuple r∈R:   —— OUTER

foreach tuple s∈S:   ——INNER

emit,if r and s match

外层循环遍历R表的所有行，每对于R表的每一个行，开一个内层循环，遍历S表的所有行。其中，小的要做做外表（因为一般对于这种磁盘型的，面向磁盘的数据库来说，所谓的小表一般是指的是文件页少的，R表虽然行数多，但是比较窄，所以它的页数少，页数遍历R表需要的磁盘IO次数就少）

开销：Cost = M + (m * N)

eg：

R表：M=1000，m=100,000

S表：N=500，n=40,000

R为外循环：cost=M+ (m*N)= 1000 + (100000*500) = 50,001,000 IOs

S为外循环：cost=N+ (n*M)= 500 + (40,000*1000) = 40,000,500 IOs

Block Nested-Loop Join：BNLJ  缓存块嵌套循环连接：

在把被驱动表的记录加载到内存的时候，一次性和多条驱动表中的记录做匹配，可以大大减少重复从磁盘上加载被驱动表的代价了

也就是说在有索引的情况下，MySQL会尝试去使用Index Nested-Loop Join算法，在有些情况下，可能Join的列就是没有索引，那么这时MySQL的选择绝对不会是最先介绍的Simple Nested-Loop Join算法，SNLJ算法是最慢的join，毕竟是笛卡尔积！

而Block Nested-Loop Join算法较Simple Nested-Loop Join的改进就在于可以减少内表的扫描次数，甚至可以和Hash Join算法一样，仅需扫描内表一次。其使用Join Buffer（联接缓冲）来减少内部循环读取表的次数。

伪代码：

foreach block Br∈R;

foreach block Bs∈S;

foreach tuple r∈Br;

foreach tuple S∈Bs;

emit, if r and s match;

Cost: M + (M * N)

Cost Analysis:  M+(M*N)= 1000 + (1000*500) = 501,000 IOs

内存池有富裕时，可以最大化利用内存池，若join有b个页内存的话，首先有一个页做输出缓存，这样就有B-1能用在缓存输入，需要看具体的数字，一般多缓存外循环表，1页给内表缓存，B-2个页作为外表的缓存, 思路是尽量的通过增大m的缓存来减小n的遍历次数。

Cost=M + ( ⌈ M / (B - 2)⌉ * N)

Index Nested-Loop Join：INLJ，索引嵌套循环连接

假设每个元组的索引探测的成本是C次磁盘IO。

foreach tuple r∈R:

foreach tuple s∈Index(ri = sj):

emit, if r and s match

Cost: M + (m * C)

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

2、使用场景：只有内层表join的列有索引时，才能用到Index Nested-LoopJoin进行连接。

3、由于用到索引，如果索引是辅助索引而且返回的数据还包括内层表的其他数据，则会回内层表查询数据，多了一些IO操作。回表查询会导致INLJ变慢。

关键要点:
——选择较小的表作为外表。

——尽可能多地将外表缓存到内存中。

——循环遍历内表(或使用索引)。

Sort-Merge Joins归并连接算法

1、排序
——根据连接键对两个表进行排序。
——可以使用外部合并排序算法。

2、合并
——使用游标遍历两个排序后的表，并输出匹配的元组。
——根据连接类型可能需要回溯。(若未匹配存在回退)

sort R,S on join keys

cursor_R <- R_sorted, cursor_s <- S_sorted

while cursor_R and cursor_S:

if cursor_R > cursor_S:

increment cursor_S

if cursor_R < cursor_S:

increment cursor_R

elif cursor_R and cursor_S match:

emit

increment cursor_S

Sort Cost (R): 2M *(1 + ⌈logB-⌈M/B⌉⌉)

Sort Cost(S): 2N *(1 + ⌈logB-1⌈N/B⌉⌉)

Merge Cost: (M + N)

Total Cost: Sort + Merge

sorted in two passes:

——Sort Cost (R)= 2000*(1 + 「log99 1000 /100⌉) = 4000 IOs

——Sort Cost(S)= 1000*(1 +「log99 500/ 100⌉) = 2000 IOs

——Merge Cost= (1000 + 500) = 1500 IOs

——Total Cost = 4000+ 2000 + 1500 = 7500 IOs

两个连接列全重复时会退化为stupid，已经排好序时开销最少，要求结果按顺序输出时最佳。

hash join 哈希连接

如果元组r∈R和元组s∈S满足连接条件，则它们在连接属性上具有相同的值。如果该值被哈希到某个分区i，则R元组必须在ri中；S元组在Si中。
因此，在ri中的R元组只需要与Si中的S元组进行比较。

外表R做哈希表，用内表S去遍历

1、构建
——扫描外部关系并使用哈希函数h，在连接属性上填充哈希表。
2、探测
——扫描内部关系并对每个元组使用哈希函数h，在哈希表中跳转到位置并找到匹配的元组。

build hash table HTR for R

foreach tuple s∈S

output, if h1(s)∈HTR

键：查询正在连接表的属性。
值：根据具体实施而异——取决于查询计划中连接上方运算符对其输入的期望

在探测阶段优化的上下文中，一种方法是在构建阶段创建布隆过滤器Bloom Filter，当键可能不存在于哈希表中时使用。多个线程可以在探测哈希表之前检查过滤器，这样可以提高性能，因为过滤器可以适应CPU缓存，从而加快速度。这种优化技术有时被称为侧向信息传递。

Grace hash join

Hash join是将R的所有数据按key散列，构成哈希表，value就是原来的行数据；再扫描S，计算key的哈希值并观察是否在哈希表内，输出结果。其中，第1步名为build阶段（建立哈希表），第2步名为probe阶段（探测哈希表）。相应地，小表R就称为build table，大表S就称为probe table。Hash join的I/O复杂度是O(|R| + |S|)，效率很高，但是它受到两点限制：

1.小表必须保证能完全放入内存；

2.只适用于equi join，即仅包含"="条件的连接。

如果小表不能完全放入内存，就只能分批加载，实质上就退化成了BNL join算法。为了避免这种退化，也有一个优化方案，即Grace hash join算法。它的思想也很直接：将R、S两张表都以join key散列到分区，然后对于划分到同一个分区的R、S分片数据分别再进行原生Hash join的build与probe过程（注意，分区与join两个阶段所用的哈希函数是不同的），将所有分片合并起来就是最终的join结果。

由此可见，Grace hash join消灭了对S表的重复扫描，I/O复杂度为O[p(R) + p(S)]，理论效率比BNL join高很多了。

Grace并非人名，而是首个采用这种算法的数据库系统的名字。

哈希连接的成本——假设有足够的缓冲区

COST=3(M + N)

构建阶段——读写两个表格2(M+N) IOs

探测阶段——读两个表格M+N IOs