数据库Join的实现原理

数据库Join的实现原理
引⽤⽂献：
Join的实现算法有三种，分别是Nested Loops Join,Merge Join,Hash Join。

DB2、SQL Server和Oracle都是使⽤这三种⽅式，不过Oracle选择使⽤nested loop的条件跟SQL Server有点差别，内存管理机制跟SQL Server不⼀样，因此查看执⾏计划，Oracle中nested loops运⽤⾮常多，⽽merge和hash⽅式相对较少，SQL Server中，merge跟hash⽅式则是⾮常普遍。

⼀.Nested Loopsb Join
1.定义
Nested Loops也称为嵌套迭代，它将⼀个联接输⼊⽤作外部输⼊表（显⽰为图形执⾏计划中的顶端输⼊），将另⼀个联接输⼊⽤作内部（底端）输⼊表。

外部循环逐⾏消耗外部输⼊表。

内部循环为每个外部⾏执⾏，在内部输⼊表中搜索匹配⾏。

最简单的情况是，搜索时扫描整个表或索引；这称为单纯嵌套循环联接。

如果搜索时使⽤索引，则称为索引嵌套循环联接。

如果将索引⽣成为查询计划的⼀部分（并在查询完成后⽴即将索引破坏），则称为临时索引嵌套循环联接。

伪码表⽰如下：
for each row R1 in the outer table
for each row R2 in the inner table
if R1 joins with R2
return (R1, R2)
2.应⽤场景
适⽤于outer table(有的地⽅叫Master table)的记录集⽐较少(<10000)⽽且inner table(有的地⽅叫Detail table)索引选择性较好的情况下(inner table要有index)。

inner table被outer table驱动，outer table返回的每⼀⾏都要在inner table中检索到与之匹配的⾏。

当然也可以⽤ORDERED 提⽰来改变CBO默认的驱动表，使⽤USE_NL(table_name1 table_name2)可是强制CBO 执⾏嵌套循环连接。

cost = outer access cost + (inner access cost * outer cardinality)
3.常⽤于执⾏的连接
Nested Loops常执⾏Inner Join(内部联接)、Left Outer Join(左外部联接)、Left Semi Join(左半部联接)和Left Anti Semi Join(左反半部联接)逻辑操作。

Nested Loops通常使⽤索引在内部表中搜索外部表的每⼀⾏。

根据预计的开销，Microsoft SQL Server决定是否对外部输⼊进⾏排序来改变内部输⼊索引的搜索位置。

将基于所执⾏的逻辑操作返回所有满⾜ Argument 列内的（可选）谓词的⾏。

⼆.Merge Join
1.定义
Merge Join第⼀个步骤是确保两个关联表都是按照关联的字段进⾏排序。

如果关联字段有可⽤的索引，并且排序⼀致，则可以直接进⾏Merge Join操作；否则，SQL Server需要先对关联的表按照关联字段进⾏⼀次排序（就是说在Merge Join前的两个输⼊上，可能都需要执⾏⼀个Sort操作，再进⾏Merge Join）。

两个表都按照关联字段排序好之后，Merge Join操作从每个表取⼀条记录开始匹配，如果符合关联条件，则放⼊结果集中；否则，将关联字段值较⼩的记录抛弃，从这条记录对应的表中取下⼀条记录继续进⾏匹配，直到整个循环结束。

在多对多的关联表上执⾏Merge Join时，通常需要使⽤临时表进⾏操作。

例如A join B使⽤Merge Join时，如果对于关联字段的某⼀组值，在A和B中都存在多条记录A1、A2...An、B1、B2...Bn，则为A中每⼀条记录A1、A2...An，都必须在B中对所有相等的记录B1、B2...Bn 进⾏⼀次匹配。

这样，指针需要多次从B1移动到Bn，每⼀次都需要读取相应的B1...Bn记录。

将B1...Bn的记录预先读出来放⼊内存临时表中，⽐从原数据页或磁盘读取要快。

2.应⽤场景另
⽤在数据没有索引但是已经排序的情况下。

通常情况下hash join的效果都⽐Sort merge join要好，然⽽如果⾏源已经被排过序，在执⾏排序合并连接时不需要再排序了，这时Sort merge join的性能会优于hash join。

可以使⽤USE_MERGE(table_name1 table_name2)来强制使⽤Sort merge join。

cost = (outer access cost * # of hash partitions) + inner access cost
　3.常⽤于执⾏的连接
Merge Join常执⾏Inner Join(内部联接)、Left Outer Join(左外部联接)、Left Semi Join(左半部联接)、Left Anti Semi Join(左反半部联接)、Right Outer Join(右外部联接)、Right Semi Join(右半部联接)、Right Anti Semi Join(右反半部联接)和Union(联合)逻辑操作。

在 Argument 列中，如果操作执⾏⼀对多联接，则 Merge Join 运算符将包含 MERGE:() 谓词；如果操作执⾏多对多联接，则该运算符将
包含 MANY-TO-MANY MERGE:() 谓词。

Argument 列还包含⼀个⽤于执⾏操作的列的列表，该列表以逗号分隔。

Merge Join 运算符要求在各⾃的列上对两个输⼊进⾏排序，这可以通过在查询计划中插⼊显式排序操作来实现。

如果不需要显式排序（例如，如果数据库内有合适的
B 树索引或可以对多个操作（如合并联接和对汇总分组）使⽤排序顺序），则合并联接尤其有效。

三.Hash Join
　1.定义
Hash Match有两个输⼊：build input（也叫做outer input）和probe input（也叫做inner input），不仅⽤于inner/left/right join等，象union/group by等也会使⽤hash join进⾏操作，在group by中build input和probe input都是同⼀个记录集。

Hash Match操作分两个阶段完成：Build（构造）阶段和Probe（探测）阶段。

Build（构造）阶段主要构造哈希表(hash table)。

在inner/left/right join等操作中，表的关联字段作为hash key；在group by操作中，group by的字段作为hash key；在union或其它⼀些去除重复记录的操作中，hash key包括所有的select字段。

Build操作从build input输⼊中取出每⼀⾏记录，将该⾏记录关联字段的值使⽤hash函数⽣成hash值，这个hash值对应到hash table中的hash buckets（哈希表⽬）。

如果⼀个hash值对应到多个hash buckts，则这些hash buckets使⽤链表数据结构连接起来。

当整个build input 的table处理完毕后，build input中的所有记录都被hash table中的hash buckets引⽤/关联了。

Probe（探测）阶段，SQL Server从probe input输⼊中取出每⼀⾏记录，同样将该⾏记录关联字段的值，使⽤build阶段中相同的hash函数⽣成hash值，根据这个hash值，从build阶段构造的hash table中搜索对应的hash bucket。

hash算法中为了解决冲突，hash bucket可能会链接到其它的hash bucket，probe动作会搜索整个冲突链上的hash bucket，以查找匹配的记录。

　如果build input记录数⾮常⼤，构建的hash table⽆法在内存中容纳时，SQL Server分别将build input和probe input切分成多个分区部分（partition），每个partition都包括⼀个独⽴的、成对匹配的build input和probe input，这样就将⼀个⼤的hash join切分成多个独⽴、互相不影响的hash join，每⼀个分区的hash join都能够在内存中完成。

SQL Server将切分后的partition⽂件保存在磁盘上，每次装载⼀个分区的build input和probe input到内存中，进⾏⼀次hash join。

这种hash join叫做Grace Hash join，使⽤的Grace Hash Join算法。

2.应⽤场景
适⽤于两个表的数据量差别很⼤。

但需要注意的是：如果HASH表太⼤，⽆法⼀次构造在内存中，则分成若⼲个partition，写⼊磁盘的temporary segment，则会多⼀个I/O的代价，会降低效率，此时需要有较⼤的temporary segment从⽽尽量提⾼I/O的性能。

可以⽤USE_HASH(table_name1 table_name2)提⽰来强制使⽤散列连接。

如果使⽤散列连HASH_AREA_SIZE 初始化参数必须⾜够的⼤，如果是9i，Oracle建议使⽤SQL⼯作区⾃动管理，设置WORKAREA_SIZE_POLICY 为AUTO，然后调整
PGA_AGGREGATE_TARGET 即可。

也可以使⽤HASH_JOIN_ENABLED=FALSE(默认为TRUE)强制不使⽤hash join。

cost = (outer access cost * # of hash partitions) + inner access cost
　3.常⽤于执⾏的链接
Hash Match运算符通过计算其⽣成输⼊中每⾏的哈希值⽣成哈希表。

HASH:()谓词以及⼀个⽤于创建哈希值的列的列表出现在Argument列内。

然后，该谓词为每个探测⾏（如果适⽤）使⽤相同的哈希函数计算哈希值并在哈希表内查找匹配项。

如果存在残留谓词（由 Argument 列中的 RESIDUAL:() 标识），则还须满⾜此残留谓词，只有这样⾏才能被视为是匹配项。

⾏为取决于所执⾏的逻辑操作： (1)对于联接，使⽤第⼀个（顶端）输⼊⽣成哈希表，使⽤第⼆个（底端）输⼊探测哈希表。

按联接类型规定的模式输出匹配项（或不匹配项）。

如果多个联接使⽤相同的联接列，这些操作将分组为⼀个哈希组。

(2)对于⾮重复或聚合运算符，使⽤输⼊⽣成哈希表（删除重复项并计算聚合表达式）。

⽣成哈希表时，扫描该表并输出所有项。

(3)对于 union 运算符，使⽤第⼀个输⼊⽣成哈希表（删除重复项）。

使⽤第⼆个输⼊（它必须没有重复项）探测哈希表，返回所有没有匹配项的⾏，然后扫描该哈希表并返回所有项。

四.性能分析
Hash join的主要资源消耗在于CPU(在内存中创建临时的hash表，并进⾏hash计算)，⽽merge join的资源消耗主要在于磁盘I/O(扫描表或索引)。

在并⾏系统中，hash join对CPU的消耗更加明显。

所以在CPU紧张时，最好限制使⽤hash join。

　在绝⼤多数情况下，hash join效率⽐其他join⽅式效率更⾼：
　在Sort-Merge Join(SMJ)，两张表的数据都需要先做排序，然后做merge。

因此效率相对最差;
Nested-Loop Join(NL)效率⽐SMJ更⾼。

特别是当驱动表的数据量很⼤(集的势⾼)时。

这样可以并⾏扫描内表。

Hash join效率最⾼，因为只要对两张表扫描⼀次
Merge Join(合并联接)本⾝的速度很快，但如果需要排序操作，选择合并联接就会⾮常费时。

然⽽，如果数据量很⼤且能够从现有 B 树索引中获得预排序的所需数据，则合并联接通常是最快的可⽤联接算法。

如果是⽆序的数据，Merge Join⾸先做的是排序，如果数据量⼤，排序就会溢出到tempdb, 效率就将低了。

如果外部输⼊很⼩(<10000)⽽内部输⼊很⼤且预先创建了索引，则Nested Loops(嵌套循环联接)尤其有效。

在许多⼩事务中（如那些只影响较⼩的⼀组⾏的事务），索引嵌套循环联接远⽐合并联接和哈希联接优越。

但在⼤查询中，嵌套循环联接通常不是最佳选择。

如果两个表的数据量差别很⼤,则使⽤Hash Match。

但需要注意的是：如果HASH表太⼤，⽆法⼀次构造在内存中，则分成若⼲个partition，写⼊磁盘的temporary segment，则会多⼀个I/O的代价，会降低效率，此时需要有较⼤的temporary segment从⽽尽量提⾼I/O的性能。

Hash join的主要资源消耗在于CPU(在内存中创建临时的HASH表，并进⾏HASH计算)，⽽Merge join的资源消耗主要在于磁盘I/O(扫描表或索引)。

五.优化原则
　1.若有单⾏谓词，则他的表⼀定是驱动表（select * from employees e,departments d where e.department_id=d.department_id and
e.department_id=100 and salary=10000; 上⾯的语句中e.department_id=d.department_id是连接谓词，e.department_id=100是⾮连接谓词（对连接列的限制），salary=10000是单⾏谓词（对⾮连接列的限制））
　2.外连接时，⼀定是⽤显⽰的⾏数⽐较多的那个表作为驱动表。

如：
select e.employee_id,e.department_id,d.manager_id,d.location_id from employees e right join departments d on
e.department_id=d.department_id
则departments表显⽰的⾏数⼀定⼤于等于employees表，所以应该要以departments表作为驱动表，如果以employees表作为驱动表，则departments表中多显⽰的那⼏⾏就显⽰不出来了
4.⼀般情况下，Hash Join处理代价⾮常⾼，是数据库服务器内存和CPU的头号杀⼿之⼀，尤其是涉及到分区（数据量太⼤导致内存不够的情况，或者并发访问很⾼导致当前处理线程⽆法获得⾜够的内存，那么数据量不是特⼤的情况下也可能需要进⾏分区），为了尽快的完成所有的分区步骤，将使⽤⼤量异步的I/O操作，因此期间单⼀⼀个线程就可能导致多个磁盘驱动器出于忙碌状态，这很有可能阻塞其它线程的执⾏。

因此，
5. 要避免⼤数据的Hash Join，尽量将其转化为⾼效的Merge Join、Nested Loops。

可能使⽤的⼿段有表结构设计、索引调整设计、SQL 优化，以及业务设计优化。

例如冗余字段的运⽤，将统计分析结果⽤service定期跑到静态表中，适当的冗余表，使⽤AOP或类似机制同步更新等。

6. 尽量减少join两个输⼊端的数据量。

这⼀点⽐较常犯的⽑病是，条件不符合SARG((Searchable Arguments)，在⼦查询内部条件给的不充分（SQL过于复杂情况下SQL Server查询优化器经常犯傻，写在⼦查询外部的条件不会被⽤在⼦查询内部，影响⼦查询内部的效率或者是跟⼦查询再join时候的效率）。