数据库JOIN算法

   关系数据库中的查询中， JOIN 是将两个数据集合按照某个条件进行合并形成新的数据集合的操作，其理论基础是关系代数。根据进行合并时条件的要求不同， JOIN 操作又可以分成若干个类型。

JOIN类型及语义

关系数据库的理论基础是关系代数。在关系代数中，连接运算由一个迪卡尔积运算和一个选择运算构成。首先用笛卡尔积完成对两个数据集合的乘运算，然后对生成的结果集合进行选取运算，确保只把分别来自每个数据集合并且具有重叠部分的行合并在一起。连接的全部意义在于在水平方向上合并两个数据集合（通常是表），并产生一个新的结果集合，其方法是将一个数据源中的行于另一个数据源中和它匹配的行组合成一个新元组。

SQL提供了多种类型的连接方式，它们之间的区别在于：从相互交叠的不同数据集合中选择用于连接的行时所采用的方法不同。JOIN的SQL语法格式是：

数据集1 [连接类型] JOIN 数据集2 [连接条件]

连接类型主要有两大类：内连接（INNER JOIN）和外连接（OUTER JOIN）。其中外连接又包括左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）。内连接只连接匹配的行，假设有表A和表B，则（A INNER JOIN B ON A.a = B.b）的结果是这样的：先将A与B作迪卡尔积，然后做一个选择运算，只保留迪卡尔积中满足A.a=B.b的行。左外连接是指将左边集合中的每一个元组都作为结果的一个元组，并将右边集合中满足条件的元组对应的属性加到结果中，对于右边集合中不能满足谓词条件的元组，在最终结果中将其对应的属性设为NULL。从产生的结果看，左外连接相当于在内连接的基础上，增加了这样的一些元组：它们的属性值不能匹配任何右边的元组。右外连接与左外连接是对称的。而全外连接就是左外连接与右外连接的并集。

连接条件有三种：自然连接（NATURAL JOIN）、条件连接（ON 谓词条件）和指定属性的连接（USING 属性[，属性]）。自然连接就是以两个集合的公共属性作为条件进行等值连接，也就是对于A NATURAL JOIN B来说，等价于A JOIN B USING（A₁，A₂），其中A₁，A₂是A和B的所有公共属性。

还有一种连接类型叫交叉连接（CROSS JOIN），它等价于没有连接条件的NATURAL INNER JOIN。

JOIN操作应用举例

假设有学生表Stutbl (stu_no, stu_name, stu_grade)和课程表Cautbl(stu_no, cas_name, cas_score)。则查找所有学生所选的所有课程：SELECT * FROM Stutbl NATURAL INNER JOIN Cautbl，或者SELECT * FROM Stutbl INNER JOIN Cautbl ON Stutbl.stu_no = Cautbl.stu_no，或者SELECT * FROM Stutbl INNER JOIN Cautbl USING(stu_no)

查找所有学生选修课程的情况（包括一门没选的学生）：SELECT * FROM Stutbl LEFT OUTER JOIN Cautbl ON Stutbl.stu_no=Cautbl.stu_no

查找所有课程的选课情况（包括没有一个人选的课程）：SELECT * FROM Stutbl RIGHT OUTER JOIN Cautbl ON Stutbl.stu_no=Cautbl.stu_no

查找选了课的学生的姓名和所选课程的名称：SELECT stu_name, cas_name FROM (Stutbl RIGHT OUTER JOIN Cautbl USING(stu_no)) AS X WHERE X.stu_no IS NOT NULL

数据库JOIN实现概述

数据库中JOIN操作的实现主要有三种：嵌套循环连接（Nested Loop Join），归并连接（Merge Join）和散列连接或者哈稀连接（Hash Join）。其中嵌套循环连接又视情况又有两种变形：块嵌套循环连接和索引嵌套循环连接。

嵌套循环连接（Nested Loop Join）

嵌套循环连接由两个FOR循环构成，并因此而得名。假设要连接的两个关系为R和S，连接条件为P，则嵌套循环连接就选择其中一个作为外层循环，另一个作为内层循环，逐一比较R和S中的每个元组，从而找出所有匹配的元组。其算法如下：

1                                       For each tuple X in R do

1.1                                                    For each tuple Y in S do

1.1.1                                                             If (X, Y) 满足连接条件

1.1.2                                                             Then 将(X, Y)加入结果中

1.2                                                    End

2                                       End

嵌套循环连接比较通用，即它与线性扫描一样，不需要索引，且不管是什么样的连接条件，该算法都适用。对于任何类型的JOIN，该算法都只需要做稍微的调整就能进行运算。如对于自然连接（Natural Join）该算法只需要在将（X, Y）加入结果之前消除其中的重复属性。

但是，嵌套循环连接的代价比较大。因为该算法过程中需要逐一比较R和S中的每一个元组，当数据规模较大而不能完全放入内存中时其引起的磁盘与内存交换比较频繁，即使数据能够完全放入内存，则嵌套循环连接执行过程中也会引起CPU的CACHE命中率低下，从而严重影响系统效率。

下面具体分析一下嵌套循环的复杂度。假设关系R有M个元组，每个元组有X个字节，S有N个元组，每个元组有Y个字节，即关系R数据大小是M*X字节，关系S数据大小是N*Y字节。再假设程序的缓冲区大小是B字节，R和S各四分之一，另外一半用来存入结果。则程序执行过程中对于R的每一次循环，需要访问4*N*Y/B次磁盘来取S中的数据。整个过程共需要访问4*M*N*Y/B次磁盘用于取S中的数据，而R中的数据需要4*M*X/B次访问磁盘。先不考虑结果与磁盘的交换，则共需要4*M*（N*Y+X）/B次磁盘访问。

块嵌套循环连接（Nested Block Loop Join）

块嵌套循环连接是采用了分块策略的嵌套循环连接，不过它有四层循环，最外面两层是块循环，里面两层是块内循环。对于关系R和S以及连接条件P，块嵌套循环连接的回想是将R和S分成块，对于逐一连接R和S中的每一块。其算法如下：

1                                       For each block A in R do

1.1                                                    For each block B in S do

1.1.1                                                             For each tuple X in A do

1.1.1.1                                                                                       For each tuple Y in B do

1.1.1.1.1                                                                                               If P(X, Y)

1.1.1.1.2                                                                                               Then Result=Result+(X, Y)

1.1.1.2                                                                                       End

1.1.2                                                             End

1.2                                                    End

2                                       End

相比嵌套循环连接，块嵌套循环连接在时间复杂度上没有改进，但是由于块循环连接是以块为单位进行处理，减少了磁盘交换次数。需要说明的是，对于外层循环的R而言，它并没有节省磁盘交换次数，关键在于每取S中的一块，处理了R中一块的数据，而不是R中的一条数据。如果一块有N个元组，则块嵌套循环连接节省了N倍的磁盘交换次数。

索引嵌套循环连接

对于关系R和S以及连接条件P，则如果S中存在一种索引，无论是临时和还是永久的，对于R中的任一元组X，能够通过该索引找到S中满足条件P的元组，则可以用索引查找替代文件扫描。这样的算法也更加简单：

1                                       For each tuple T X in R do

1.1                                                    Find all tuples {Y} in S that P(X, Y)

1.2                                                    For each y in {Y} do

1.2.1                                                             Result=Result+(X,y)

1.3                                                    End

2                                       End

索引嵌套循环连接在已经有索引的情况下使用，或者为了进行连接操作专门建立一个索引。如果已经有了索引，则其时间复杂度比较小，只需要扫描一个表，然后根据索引查找另一个表。

归并连接（Merge Join）

归并连接算法又称排序归并连接，主要用于计算自然连接和等值连接。假设有关系R和S，则在进行连接之前先让R和S是有序的。然后分别对两个表进行扫描一遍即可完成。其算法如下：

1                                       将R和S中元组都按连接属性从小到大排序

2                                       令i=j=0

3                                       While i<R中元组的总个数 And j<S中元组的总个数 do

3.1                                                    While S[j]<>R[i] do

3.1.1                                                             While S[j]<R[i] do

3.1.1.1                                                                                       j=j+1

3.1.2                                                             End

3.1.3                                                             While S[j]>R[i] do

3.1.3.1                                                                                       i=i+1

3.1.4                                                             End

3.2                                                    End

3.3                                                    m=i, n=j

3.4                                                    While S[m]=S[i] do

3.4.1                                                             M=m+1

3.5                                                    End

3.6                                                    While R[n]=R[j] do

3.6.1                                                             N=n+1

3.7                                                    End

3.8                                                    For each tuple X from R[i] to R[m - 1] do

3.8.1                                                             For each tuple Y from S[j] to S[n-1] do

3.8.1.1                                                                                       Result=Result+(X, Y)

3.8.2                                                             End

3.9                                                    End

4                                       End

归并连接执行起来非常高效，其时间复杂度是线性的O（n），其中n为R和S中元组数最多的那个关系的元组个数。但是它只能进行等值连接和自然连接，对于其它谓词的连接显得力不从心。并且还要求连接之前对元组进行全排序，这在某些情况下或者是很困难的。

散列连接（Hash Join）

类似归并连接，散列连接可用于实现等值连接和自然连接。在散列连接算法中，散列函数H用于对两个关系进行划分。此算法的基本思想是把两个关系按连接属性划分成具有相同散列值的元组集合。

假设H是将JoinAttrs值映射到{0，1，2…，max}的散列函数，并且具有良好的随机性和均匀性，其中JoinAttrs表示关系R与S中的公共属性组，或者连接属性组。Hr1，Hr2，…，Hrmax表示关系R的元组划分，Hs1，Hs2，…，Hsmax表示关系S的元组划分，则散列连接算法的思想如下：如果关系R的一个元组与关系S的一个元组满足连接条件，那么它们在连接属性上有相同的值。若该元组在函数H中映射为i，则关系R的那个元组必在Hri中，关系S的那个元组必在His中。因此，Hri中的元组只需与His中的元组进行比较，而无需与S中其它元组进行比较。

散列连接不需要索引，并且与循环嵌套连接相比，散列连接更容易处理大结果集，唯一的遗憾就是它只能用作等值连接。

JOIN实现实例

本部分主要调研了市场主流数据库对于连接的实现方式。在介绍这些内容之前，首先分析一下索引对于JOIN操作的影响。

对于嵌入循环连接，索引有可能有用，但在归并连接和散列连接中，有无索引对JOIN操作并没有任何影响。

再总结一下各种JOIN适用的场景。嵌套循环连接比较通用，可用于任何类型的连接。而归并连接和歼列连接只能用于等值连接和自然连接。

MYSQL

在MYSQL中，只有一种JOIN算法，就是最简单最通用的嵌套循环连接。它没有散列连接和排序归并连接。MYSQL中JOIN操作操作最好避免，特别是对于大表的JOIN，速度特别低下。

但是对于效率，MYSQL社区谈到了HASH JOIN，但却把JOIN效率的提高寄希望于INTEL的SSD技术。即半导体硬盘带来的速度提高。

ORACLE

Oracle数据库中对三种连接算法都有实现，并分不同情况分别使用不同的JOIN算法。

嵌入循环连接是最慢的算法，只有在不得已时才使用。索引能够有效地提高其速度。

归并连接比嵌入循环连接快的多，但它也不是Oracle优先选择的算法。

Oracle Hash join 是一种非常高效的join 算法，主要以CPU(hash计算)和内存空间(创建hash table)为代价获得最大的效率。Hash join一般用于大表和小表之间的连接，我们将小表构建到内存中，称为Hash cluster，大表称为probe表。

SQL SERVER 7.0/2000

Microsoft SQL Server 7.0/2000的JOIN操作有三种类型：Nested-Loop Join， Merge Join和 Hash Join。

嵌套循环连接

Nested-Loop操作从关联的两个table中，选择一个作为外层循环，为每一条记录在另一个table中循环查找匹配的结果。作为外层循环的table为outer table，内层循环的table为inner table。在执行计划中，不管是图形还是文本显示方式，outer table位于上方，inner table位于下方。 SQL Server在自动选择join type时，大多数情况下使用Nested-Loop join的条件是：关联的两个Table中，有一个数据量比较小（记录数在2000左右以下），另外一个数据量大的Table又有对于关联条件可用的索引。

另外一种情况，假如查询语句类似如下：     SELECT [...] FROM A INNER JOIN B ON A.?=B.? WHERE A.?=? 如果A、B的记录数都是几万到几十万，B中有ON A.?=B.?可用的索引，A中有WHERE A.?=?可用的索引，并且A通过WHERE子句条件的过滤后记录数比较小（2000左右以下），这种情况下仍然会使用Nested-Loop join。但是如果WHERE子句中既包含A也包含B的限制条件，则会选择Merge或Hash join了。从Nested-Loop join的选择条件可以看出，Nested-Loop join的执行非常高效。outer table的记录数很小，因此外层循环次数少；在inner table中搜索匹配记录时使用索引，就算inner table的数据量非常大，搜索也是相当快而有效的。

在查询语句中，可以强制SQL Server使用Nested-Loop方式关联两个table，例如：SELECT A.PONO,B.VCODE,B.VNAME FROM TBLPO A INNER JOIN TBLVENDOR B ON A.VENDORID=B.ID OPTION (LOOP JOIN) 。如果没有十分的把握，让SQL Server Optimizer自动选择join type。Optimizer总是尝试合理的确定inner talbe和outer table。通常情况下总是选择有可用索引的一个作为inner table，即使这个table的数据量可能会比另外一个多。如果两个table都没有可用索引，则选择数据量较小的一个作为outer table。这种情况下，如果数据量大的table记录数太多或内存有限，无法将inner table的数据全部读入内存中，则SQL Server会尝试将数据量小的作为inner table，以使inner table的数据全部驻留内存中，提高inner table循环的速度。在进行Nested-Loop join操作时，SQL Server Optimizer可能会对inner table进行一次排序，以提高对inner table搜索的速度。

归并连接

SQL SERVER中归并连接是效率最好的连接算法。当两个表都按照连接属性值排序时将使用归并连接进行JOIN操作。

If you have two tables (Table1 and Table2), where n1 - the count of rows in the Table1 table, and n2 - the count of rows in the Table2 table, and there is no index on the column that joins this tables, then in the worse case (with Nested-Loop join) SQL Server will scan n1xn2 rows to return a results set (for each row from the outer table the inner table will be completely scanned).

 In the best case, if Table1 table has a clustered index on the column that joins the tables, and Table2 table has a clustered index on the column that joins the tables, and there is one-to-many relationship between Table1 and Table2, then Merge join will be used, and SQL Server will scan n1 + n2 rows to return a results set.

This is the example:

If object_id('dbo.Table1') is not null drop table Table1 GO CREATE TABLE Table1 (Table1_id int primary key CLUSTERED, name char(10)) GO if object_id('dbo.Table2') is not null drop table Table2 GO CREATE TABLE Table2 (   Table2_id int primary key NONCLUSTERED,   Table1_id int,   name char(10)) GO CREATE CLUSTERED INDEX indTable2 ON Table2 (Table1_id) GO   DECLARE @i int SELECT @i = 1 WHILE @i < 1000   BEGIN     INSERT INTO Table1 VALUES (@i, LTRIM(str(@i)))     SELECT @i = @i + 1   END GO   DECLARE @i int SELECT @i = 1 WHILE @i < 1000   BEGIN     INSERT INTO Table2 VALUES (@i, @i, LTRIM(str(@i)))     SELECT @i = @i + 1   END GO   SET SHOWPLAN_TEXT ON GO SELECT a.Table1_id, b.Table1_id FROM Table 1 a INNER JOIN Table2 b   ON a.Table1_id = b.Table1_id GO SET SHOWPLAN_TEXT OFF GO

This is the algorithm of the Merge join (the description of its work in general case, for many-to-many relationship):

while (not Table1.eof) and (not Table2.eof) do   begin     while Table2.Table1_id > Table1.Table1_id do Table1.MoveToNextRecord();     value = Table1.Table1_id;     while Table2.Table1_id < value do Table2.MoveToNextRecord();     RID = Table1.RowID();     while Table2.Table1_id = value do       begin         while Table1.Table1_id = value do           begin             < SELECT Table1.Table1_id, Table2.Table1_id >             Table1.MoveToNextRecord();           end         Table1.MoveTo(RID);         Table2.MoveToNextRecord();       end   end

Note. If the joined tables are small (contain only one data page, for example), and at least one of the joined tables have index on the column that joins the tables, then SQL Server will use Nested-Loop join instead of Merge join or Hash join (usually).

Because the query optimizer usually selects the best execution plan for a given select statement, it is not necessary to enforce the desirable join type, but sometimes it can be useful. You can enforce the desirable join type by using the OPTION clause.

This is the example to enforce Merge join:

USE pubs GO SET SHOWPLAN_TEXT ON GO SELECT a.au_id FROM authors a JOIN titleauthor b    ON a.au_id = b.au_id OPTION (MERGE JOIN) GO SET SHOWPLAN_TEXT OFF GO

This is the result:

StmtText ------------------------------------------------------------------------------------------------ SELECT a.au_id FROM authors a JOIN titleauthor b    ON a.au_id = b.au_id OPTION (MERGE JOIN) (1 row(s) affected) StmtText ------------------------------------------------------------------------------------------------ |--Merge Join(Inner Join, MERGE:([a].[au_id])=([b].[au_id]), RESIDUAL:([a].[au_id]=[b].[au_id]))      |--Clustered Index Scan(OBJECT:([pubs].[dbo].[authors].[UPKCL_auidind] AS [a]), ORDERED)      |--Index Scan(OBJECT:([pubs].[dbo].[titleauthor].[auidind] AS [b]), ORDERED) (3 row(s) affected)

 

散列连接

当连接属性上没有合适的索引时将使用散列连接算法。如果待连接的两个表的规模相差很大，则散列连接的效率显得最为突出。

查询优化器将散列连接分成两个阶段：建立和查找。因此，散列连接有两个输入：建立输入和查找输入。（The query optimizer makes a Hash join in two phases: build and probe. So, Hash join has two inputs: the build input and the probe input.）

On the build phase, hash table will be created by scanning each value in the build input and applying the hashing algorithm to the key.

Let me to describe Hash join on the example with two tables.

Look at this example:

if object_id('dbo.Table1') is not null drop table Table1 GO CREATE TABLE Table1 (id int, Name char(10)) GO if object_id('dbo.Table2') is not null drop table Table2 GO CREATE TABLE Table1 (id int, Name char(20)) GO   DECLARE @i int SELECT @i = 1 WHILE @i < 1000   BEGIN     INSERT INTO Table1 VALUES (@i, LTRIM(str(@i)))     SELECT @i = @i + 1   END GO   DECLARE @i int SELECT @i = 1 WHILE @i < 1000   BEGIN     INSERT INTO Table2 VALUES (@i, LTRIM(str(@i)))     SELECT @i = @i + 1   END GO   SET SHOWPLAN_TEXT ON GO SELECT a.Name FROM Table 1 a INNER JOIN Table2 b   ON a.Name = b.Name GO SET SHOWPLAN_TEXT OFF GO

This is the result:

StmtText ------------------------------------------------------------------------------------------- |--Hash Match(Inner Join, HASH:([a].[Name])=([b].[Name]), RESIDUAL:([a].[Name]=[b].[Name]))      |--Table Scan(OBJECT:([pubs].[dbo].[Table1] AS [a]))      |--Table Scan(OBJECT:([pubs].[dbo].[Table2] AS [b]))

The smaller table will be build input, the other - probe input. Field Name (column that joins the tables) is called hash key. The hash table consists of linked lists called hash buckets. The result of using a hash function on a hash key is called hash value. Hash value and RID (row identifier) will be placed into hash table.

Hash value must be smaller than hash key. So, query processor economies on the size of the hash table. The real example of hashing is notebook. You can open notebook on the appropriate letter and scan all surnames on this letter to find necessary ones. So, notebook is the example of hash table, and pages on the appropriate letter are the example of hash bucket.

During the probe phase, the entire probe input is scanned, and for each probe row computes the same hash value on the hash key to find any matches in the corresponding hash bucket.

There are two main kinds of Hash join:

  In-memory Hash join

  Grace Hash join

In-memory Hash Join will be used if entire build input can be placed into memory.

Grace Hash join will be used if your server has not enough memory to hold the entire build input. In this case, query processor proceeds Hash Join in several steps (hash table will be divided into multiple partitions and relevant partition will be loaded as need).

Because the query optimizer usually selects the best execution plan for a given select statement, it is not necessary to enforce the desirable join type, but sometimes it can be useful. You can enforce the desirable join type by using the OPTION clause.

This is the example to enforce Hash join:

USE pubs GO SET SHOWPLAN_TEXT ON GO SELECT a.au_id FROM authors a JOIN titleauthor b    ON a.au_id = b.au_id OPTION (HASH JOIN) GO SET SHOWPLAN_TEXT OFF GO

This is the result:

StmtText ----------------------------------------------------------------------------------------------- SELECT a.au_id FROM authors a JOIN titleauthor b    ON a.au_id = b.au_id OPTION (HASH JOIN) (1 row(s) affected) StmtText ----------------------------------------------------------------------------------------------- |--Hash Match(Inner Join, HASH:([a].[au_id])=([b].[au_id]), RESIDUAL:([a].[au_id]=[b].[au_id]))      |--Index Scan(OBJECT:([pubs].[dbo].[authors].[aunmind] AS [a]))      |--Index Scan(OBJECT:([pubs].[dbo].[titleauthor].[titleidind] AS [b])) (3 row(s) affected)

 

DB2

DB2也提供了所有三种算法。DB2 UDB 优化器可以在执行连接时选择不同方法：在缺省情况下，它在嵌套循环连接（nested loop join）与合并连接（merge join）之间选择。当设置了特殊环境变量时，它还可以选择散列连接（hash join）。散列连接可显著提高某些查询的性能，在决策支持系统（Decision Support System，DSS）环境中尤为突出，因为该环境中的查询比较复杂。