SQL Server索引基础知识(10)-Join时的三种算法

核心提示： Nested Loop Join (嵌套循环联结)下图是 SQL Server 标示这种联结的图标，从图标我们就可以看到这个查询的逻辑，SQL Server索引基础知识(10)-Join时的三种算法(2)，逻辑步骤，如下图：从外层的数据表取出一笔记录 使用这个记录扫描内层的数据表 再回到外

Nested Loop Join (嵌套循环联结)

下图是 SQL Server 标示这种联结的图标，从图标我们就可以看到这个查询的逻辑。

逻辑步骤，如下图：

从外层的数据表取出一笔记录

使用这个记录扫描内层的数据表

再回到外层的数据表，重复上述的步骤。

特点：

适用于一个集合大而另一个集合小的情况(将小集合做为外循环)，I/O性能不错。

当外循环输入相当小而内循环非常大且有索引建立在JOIN字段上时，I/O性能相当不错。

当两个集合中只有一个在JOIN字段上建立索引时，一定要将该集合作为内循环。

对于一对一的匹配关系(两个具有唯一约束字段的联结)，可以在找到匹配元组后跳过该次内循环的剩余部分(类似于编程语言循环语句中的continue)。

可以不是等值联结

Merge Join (合并联结)

下图是 SQL Server 标示这种联结的图标，从图标我们就可以看到这个查询的逻辑。

逻辑步骤。如下图：

使用两个数据表用来 Join 的字段既有的索引

两边的数据表以光标由小到大比较，一边移动到比另一个数据表大时，换移另一个数据表。

特点：

可以不是等值联结

MERGE JOIN 必须等待两个单独的SORT JOIN完成(如果使用索引作为数据源,可以跳过SORT JOIN这个步骤), 如果两个表的规模相差很大, 会很影响查询的性能。

当查询的两个表都很大或者都很小的时候， 则应该只用MERGE JOIN，如果两个表都很小，则表扫描和分类将进行的很快;

分别使用这三种 Join 的例子：

create table t1 ( i int not null )
create table t2 ( i int not null )
go
set showplan_text on
go
--　 Hash Match(Inner Join, HASH:([ghj_Demo].[dbo].[t2].[i])=([ghj_Demo].[dbo].[t1].[i]))
select * from t1 join t2 on t1.i = t2.i
go
set showplan_text off
go
alter table t1 add primary key ( i )
alter table t2 add primary key ( i )
go
set showplan_text on
go
--　 |--Nested Loops(Inner Join, OUTER REFERENCES:([ghj_Demo].[dbo].[t1].[i]))
select * from t1 join t2 on t1.i = t2.i
go
set showplan_text off
go
set showplan_text on
go
-- Merge Join(Inner Join, MERGE:([ghj_Demo].[dbo].[t1].[i])=([ghj_Demo].[dbo].[t2].[i]),
-- RESIDUAL:([ghj_Demo].[dbo].[t2].[i]=[ghj_Demo].[dbo].[t1].[i]))
select * from t1 join t2 on t1.i = t2.i option(merge join)
go
set showplan_text off
go
drop table t1, t2