* 为什么要使用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