Merge join、Hash join、Nested loop join对比分析

最新推荐文章于 2024-04-03 17:03:00 发布

KPLives

最新推荐文章于 2024-04-03 17:03:00 发布

阅读量1.8w

点赞数 4

分类专栏： MS SQL Server

MS SQL Server 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

SQL server 内部实现了三种类型的内连接运算，大多数人从来没有听说过这些连接类型，因为它们不是逻辑连接也很少被用于代码中。那么它们什么时候会被用到呢？答案是要依情况而定。这就意味着要依赖于记录集和索引。查询优化器总是智能的选择最优的物理连接类型。我们知道SQL优化器创建一个计划开销是基于查询开销的，并依据此来选择最佳连接类型。

那查询优化器究竟是怎样从内部选择连接类型的呢？

SQLServer在内部为查询优化器对连接类型的选择实现了一些算法，让我们来看下面的一些练习示例，最后来做总结。

首先我给出一些基本的思想，连接是怎样工作什么时候工作，优化器又是怎样决定使用哪种类型的内连接。

· 取决于表大小

· 取决于连接列是否有索引

· 取决于连接列是否排序

测试环境：

内存：4GB

数据库服务器：SQLServer 2008 (RTM)

create table tableA (id int identity ,name varchar(50))
declare @i int
set @i=0
while (@i<100)
begin
insert into tableA (name)
select name from master.dbo.spt_values
set @i=@i+1
end
--select COUNT(*) from dbo.tableA --250600
go
create table tableB (id int identity ,name varchar(50))
declare @i int
set @i=0
while (@i<100)
begin
insert into tableB (name)
select name from master.dbo.spt_values
set @i=@i+1
end
-- select COUNT(*) from dbo.tableB --250600
select * from dbo.tableA A join tableB B
on (a.id=b.id)

测试1：大表，没有索引

现在来创建一个聚族索引：

create unique clustered index cx_tableA on tableA (id)
create unique clustered index cx_tableB on tableB (id)

测试1：大表，有索引

如果连接中的任何一个表有索引那么将采用Hash Join。我并没有贴上所有结果截图，如果你感兴趣你可以删除任何一个表中的索引来做测试。

测试2：中表，没有索引

首先创建表：

create table tableC (id int identity,name varchar(50))
insert into tableC (name)
select name from master.dbo.spt_values
-- select COUNT(*) from dbo.tableC --2506
create table tableD (id int identity,name varchar(50))
insert into tableD (name)
select name from master.dbo.spt_values
select * from dbo.tableC C join tableD D
on (C.id=D.id)
-- select COUNT(*) from dbo.tableD --2506

测试2：中表，有索引

首先还是创建一个聚族索引：

create unique clustered index cx_tableC on tableC (id)
create unique clustered index cx_tableD on tableD (id)

对于中等大小的表，如果连接中的任何一个表有索引，那么将采用Merge Join。

测试3：小表，没有索引

create table tableE (id int identity,name varchar(50))
insert into tableE (name)
select top 10 name from master.dbo.spt_values
-- select COUNT(*) from dbo.tableE --10
create table tableF (id int identity,name varchar(50))
insert into tableF (name)
select top 10 name from master.dbo.spt_values
-- select COUNT(*) from dbo.tableF --10

测试3：小表，有索引

创建聚族索引：

create unique clustered index cx_tableE on tableE (id)
create unique clustered index cx_tableF on tableF (id)

对于小表，如果任何一个表中有索引，那么将采用Nested Loop Join。

同样也可以从另一个方向来做比较，比如大表对比中表对比小表。

select * from dbo.tableA A join tableC C
on (a.id=C.id)
 
select * from dbo.tableA A join tableE E
on (a.id=E.id)
 
select * from dbo.tableC C join tableE E
on (C.id=E.id)

在这种情况下若所有或部分表都有索引则采用Nested Loop Join，如果都没有则使用HashJoin。

当然你也可以强制优化器使用任何一种连接类型，但这并不是一种值得推荐的做法。查询优化器很智能，能够动态的选择最优的一个。这里我只是显示调用了MergeJoin，所以优化器使用MergeJoin替代本来应使用HashJoin (测试1没有索引)。

select * from dbo.tableA A join tableB B
on (A.id=B.id)option (merge join)
 
select * from dbo.tableA A inner merge join tableB B
on (A.id=B.id)

表1 测试唯一聚族索引

根据上表：

Ø 如果两个表都没有索引则查询优化器内部会选择Hash Join

Ø 如果两个表都有索引则内部会选择Merge Join(大表)/NestedLoop Join(小表)

Ø 如果其中的一个表有索引则查询优化器内部会选择Merge Join(中表)/HashJoin(大表)/NestedLoop Join(小表&大表 vs 小表)

表2 测试聚族索引(createclustered indexcx_tableA ontableA (id))


Table size	With index (Both)	Without Index(Both)	Either of table has index
Big (Both)	HASH	HASH	HASH
Medium (Both)	HASH	HASH	HASH
Small (Both)	NESTED LOOP	NESTED LOOP	HASH
Big Vs Small(medium)	HASH	HASH	HASH