简明易懂的数据库join 算法说明

本文主要讲述数据库中的 nested loop 类的算法，包括nested loop join 和 block nested join算法。对于新的mysql 8中的 hash join 算法 可参见 https://blog.csdn.net/youyou1543724847/article/details/106908864

原文地址：https://www.geeksforgeeks.org/join-algorithms-in-database/

nested join 系统算法 的主要特定是需要考虑内层join 表。

下面我们以两表join 为例来仅需说明。

假设有两个表R表 和S 表，其中R表有Tr 行，占用了 Br 个数据块；S表有Ts 行，占用了 Bs个数据块。

1. nested loop join 算法

对于 nested loop join 算法，对于外join 表中的每个数据行，都需要遍历所有的 内join 表的行，检查满足条件的数据行。过程伪代码代码如下所示：

for each tuple tR in TR do
 for each tuple ts in Ts do
  compare (tR, ts) if they satisfies the condition
  add them in the result of the join
 end 
end

使用上面的例子来说明上面的算法处理流程。

case1: 假设mysq 中，对于每个join 操作，只允许有连个内存块来缓存表的磁盘数据块

如果只允许加载两个数据块到内存中，则在join 时，将R表的一个数据块、S 表的一个数据块加载到内存中。整个处理如下：

上图中，有如下几点需要注意：

1. 因为只能加载两个数据块，则先加载R表的第一个数据块，然后加载S表的第一个数据块。加载数据完后，对于R表数据块的第一行数据，遍历S数据块的每一行，进行匹配，如果匹配成功，满足条件，则将结果加入到结果集中，直到S表的一个数据块处理完毕，然后加载S表的第二个数据块，继续处理，直到比较完S表的所有数据块。然后继续比较R表第一个数据块的第二行。直到处理完R表的所有的数据块、所有的数据行

都需要依次加载所有的S表的所有数据块进行匹配完毕后。再加载R表的下一个数据块，进行处理。
2. 所以，总共需要加载 TR * BS + BR 个数据块。

在上述case 中，性能比较低，对于R 表的每个数据行，都要依次加载比较 S表的每个数据块

case 2:假设内存可以完成容纳一张数据表 + 一个额外的数据块

如果R,S 中某一张表比较小（这里假定S表比较小），则改种场景下，可将 R表的某一个数据块 、S表全表加载到内存中，则join 处理过程中内存块的情况如下所示：

特点：

1. 在该种场景下，对于R表，每加载一个数据块，则可直接到内存中比较S表的数据块中的数据。处理完第一个数据块后，就可以加载R表的下一个数据块。直到处理完所有的数据块。
2. 需要加载的总块数：BR + BS

从上面场景中，我们可知：需要将小表放到内存join 中，以减少加载的数据块数。另外，内存需要加载的内存块次数为：min(BR, BS)+1 。 （注意：是加载次数，不是加载块数）。

2. Block Nested Loop Join

在 Block Nested Loop Join 算法中，对于外层join 表中每一个数据块， 会将 块中的每一行数据 与内存join 表数据块的那个每一行数据进行匹配，然后将所有的匹配满足条件的行，加到结果集中。（对于 Nested Loop Join 算法，是一次，只比较 外层join 数据块中的一行数据）。

算法伪代码如下：

for each block bR in BR do
 for each block bs in BS do
 
  for each tuple tR in TR do
   for each tuple ts in Ts do
    compare (tR, ts) if they satisfies the condition
    add them in the result of the join

   end
  end 
 end
end 

case 1: 假设在join 时，只允许分配两个内存块缓存数据

场景特点总结：

1. 对于R表的每个数据块，只加载一次；
2. 对于S表的每一个数据块，加载 BR * BS 次；
3. 总共加载：BR+ BR * BS 次；

case 2: 内存可以加载一个数据表 + 一个数据块

Block Nested Loop Join的join buffer

在 block nested loop join 中，要实现 一个块与另一块的全量匹配，还用到了一个join buffer。

将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数.

举例来说：
外层循环的结果集是100行，则

1. 使用NLJ 算法需要扫描内部表100次（每行扫描匹配一次）；
2. 如果使用BNL算法，先把对Outer Loop表(外部表)每次读取的10行记录放到join buffer,然后在InnerLoop表(内部表)中直接匹配这10行数据，内存循环就可以一次与这10行进行比较, 这样只需要比较10次，对内部表的扫描减少了9/10。所以BNL算法就能够显著减少内层循环表扫描的次数。

MySQL使用Join Buffer有以下要点:

1. join_buffer_size变量决定buffer大小。
2. 只有在join类型为all, index, range的时候才可以使用join buffer。
3. 能够被buffer的每一个join都会分配一个buffer, 也就是说一个query最终可能会使用多个join buffer。
4. 第一个nonconst table不会分配join buffer, 即便其扫描类型是all或者index。
5. 在join之前就会分配join buffer, 在query执行完毕即释放。
6. join buffer中只会保存参与join的列, 并非整个数据行。