数据库的表连接方式详解

数据库的连接方式：

Nested loop join

Hash join

Merge join

Nested loop join:

Nested loop join的思路：

将Outer relation的每一行数据和Inner relation的每一行做对比，如果符合关联条件的话才会返回关联的数据集。

伪代码：

nested_loop_join(array outer, array inner)
  for each row a in outer
    for each row b in inner
      if (match_join_condition(a,b))
        write_result_in_output(a,b)
      end if
    end for
   end for

因此这是一个两层的循环，它的算法时间复杂度是 O(N*M)。

根据磁盘的I/O,Outer relation有N行数据，Inner relation有M行数据。那么根据这个算法就需要从磁盘上读N+N*M行数据。但是如果Inner relation的数据量足够小可以存在内存里面，那么磁盘的I/O就只有N+M了。

总结：

如果Inner relation小的话比较合适使用这样方式。

合理的在关联字段上面建索引也能减少I/O。

Hash join：

Hash join的算法实现比Nested loop会更加复杂，但是更多的情况下面消耗更小成本。

Hash join的思路：

1、从Inner relation取出所有的元素

2、将取出的元素在内存里建立哈希表

3、从outer relation一行一行的取出所有的元素

4、计算从outer relation取出的元素的哈希值，跟据哈希值去找到哈希表里面想对应的桶

5、然后在桶里面再找是否有匹配元素

桶（bucket）

在算法时间复杂度方面我们作出下面的假设：

1、Inner relation的元素分别放到了X个桶里

2、哈希值是均衡的，所以桶里面的数据应该也是相当的

3、outer relation需要到桶里面匹配到一个元素

因此算法的时间复杂度是

(M/X) * N + cost_to_create_hash_table(M) + cost_of_hash_function*N

Merge join:

Merge join的实现思路：

Merge join没有区分Inner table或者Outer table，对于两个数据集都用了相同的规则去处理。

1、将两个数据集都按关联join的条件排序

2、将两个排序好的数据集归并到一起

Merge join伪码：

mergeJoin(relation a, relation b)
  relation output
  integer a_key:=0;
  integer b_key:=0;
 
  while (a[a_key]!=null or b[b_key]!=null)
    if (a[a_key] < b[b_key])
      a_key++;
    else if (a[a_key] > b[b_key])
      b_key++;
    else //Join predicate satisfied
	//i.e. a[a_key] == b[b_key]

      //count the number of duplicates in relation a
      integer nb_dup_in_a = 1:
      while (a[a_key]==a[a_key+nb_dup_in_a])
        nb_dup_in_a++;
		
      //count the number of duplicates in relation b
      integer dup_in_b = 1:
      while (b[b_key]==b[b_key+nb_dup_in_b])
        nb_dup_in_b++;
		
	  //write the duplicates in output
	   for (int i = 0 ; i< nb_dup_in_a ; i++)
	     for (int j = 0 ; i< nb_dup_in_b ; i++)	   
	       write_result_in_output(a[a_key+i],b[b_key+j])
		   
      a_key=a_key + nb_dup_in_a-1;
      b_key=b_key + nb_dup_in_b-1;

    end if
  end while

怎么选择合适的算法：

    如果没有足够的内存就不要考虑hash join（或者说是full in-memory的hash join）

    两个不同size的数据集，如果一个数据集大一个数据集很小，使用nested loop会比hash join达到更高的效率，如果是两个都很大的数据集使用nested loop的话会需要更多的cpu和磁盘开销。

   如果存在索引的话，如果关联条件上面有B+ Tree的话就可以考虑是用merge join

   如果你需要排序好的数据集，那么你也可以使用merge join,因为merge join的结果集就是一个排序后的结果。