1. Nested Loop Join(嵌套循环联结)
算法:
其思路相当简单和直接:对于关系R每个元组 r 将其和关系S每个元组 s 在JOIN条件字段上直接比较并筛选出符合条件元组写成伪代码就是:
代价:
被联结表所处内层或外层顺序对磁盘I/O开销有着非常重要影响而CPU开销相对来说影响较小主要是元组读入内存以后(in-memory)开销是 O (n * m)
对于I/O开销根据 page-at-a-time 前提条件I/O cost = M + M * N
翻译下就是 I/O开销 = 读取M页I/O开销 + M次读取N页I/O开销
2. Sort-Merge Join (排序合并联结)
Nested Loop般在两个集合都很大情况下效率就相当差了而Sort-Merge在这种情况下就比它要高效不少尤其是当两个集合JOIN字段上都有聚集索引(clustered index)存在时Sort-Merge性能将达到最好
算法:
基本思路也很简单(复习下数据结构中合并排序吧)主要有两个步骤:
a.按JOIN字段进行排序
b.对两组已排序集合进行合并排序从来源端各自取得数据列后加以比较(需要根据是否在JOIN字段有重复值做特殊“分区”处理)
代价:(主要是I/O开销)
有两个原因左右Sort-Merge开销:JOIN字段是否已排序 以及 JOIN字段上重复值有多少
◆最好情况下(两列都已排序且至少有列没有重复值):O (n + m) 只需要对两个集合各扫描遍(这里mn如果都能用到索引那就更好了)
◆最差情况下(两列都未排序且两列上所有值都相同):O (n * log n + m * log m + n * m) 两次排序以及次全部元组间笛卡尔乘积
3. Hash Join (哈希联结)
Hash Join在本质上类似于两列都有重复值时Sort-Merge处理思想——分区(patitioning)但它们也有区别:Hash Join通过哈希来分区(每个桶就是个分区)而Sort-Merge通过排序来分区(每个重复值就是个分区)
值得注意是Hash Join和上述两种算法的间较大区别同时也是个较大限制是它只能应用于等值联结(equality join)这主要是由于哈希及其桶确定性及无序性所导致
算法:
基本Hash Join算法由以下两步组成:
同nested loop在执行计划中build input位于上方probe input位于下方
hash join操作分两个阶段完成:build(构造)阶段和probe(探测)阶段
a.Build Input Phase: 基于JOIN字段使用哈希h2为较小S集合构建内存中(in-memory)哈希表相同键值以linked list组成个桶(bucket)
b.Probe Input Phase: 在较大R集合上对哈希表进行核对以完成联结
代价:
值得注意是对于大集合R每个元组 r hash bucket中对应 r 那个bucket中每个元组都需要和 r 进行比较这也是算法最耗时地方所在
CPU开销是O (m + n * b) b是每个bucket平均元组数量
整理总结:
3种join思路方法都是拥有两个输入优化基本原则:
1.避免大数据hash join(hash join适合低并发情况他占用内存和io是很大);
2.尽量将其转化为高效merge join、nested loop join可能使用手段有表结构设计、索引调整设计、SQL优化以及业务设计优化
最新评论