SQL Server执行计划那些事儿(1)——哈希、合并、嵌套联接的选择

时间:2022-05-23 06:55:01

接下来的文章是记录自己曾经的盲点,同时也透漏了自己的发展历程(可能发展也算不上,只能说是瞎混)。当然,一些盲点也在工作和探究过程中慢慢有些眉目,现在也愿意发扬博客园的奉献精神,拿出来和大家分享一下。

开门见山,直接入题

在进行Join的时候,数据库优化器是怎么进行联接呢?下面我们也详细的讲述。

在SQL Server中,有3中Join的策略——哈希匹配(Hash)、合并(Merge)、嵌套循环(Nested Loop).

在理解者三种联接策略之前,我们先来简单了解下哈希匹配联接、合并联接、嵌套循环联接,

哈希匹配联接:这种联接有两种输入,即建立输入和探测输入。首先SQL Server会根据统计信息从两张表中筛选出较小的表作为建立输入,并且读入所有行,然后在内存中根据关联条件建立一个哈希表。在整个建立阶段完成之后就进入探测阶段。以后一行一行的对探测输入进行扫描和计算,并为每个探测行计算哈希值,然后进行匹配(当然这里也分多种情况,建立输入大于可用内存时等其他情况)。

合并联接:合并连接要求两个输入都要在合并列上排序。由于每个输入都已排序,因此Merge Join运算符将从每个输入中获取一行进行比较,如果行相等则进行返回,不等则舍弃。当数据量不大的时候,这种联接方式比哈希匹配更加有效。

嵌套循环联接:嵌套循环也称“嵌套迭代”,他将一个联接输入用作外部输入表,将另一个联接输入用作内部输入表。外部循环逐行处理外部输入表。内部循环逐行处理外部输入表,内部循环会针对每个外部行执行,在内部输入表中搜索匹配的行。

通过上面的介绍,我们也能分析出来(可以根据时间复杂度,和空间复杂度),以上三种联接并没有绝对的优劣。

大致可以分一下几种情况:

(1)当数据量容量很大,且未排序的情况下,哈希匹配要优于其他两种。

(2)当属数据已经排序,且数据量不大的之后,合并连接更加有效。

(3)当结果集比较小,且数据容量不大的时候嵌套循环比较合适。

下面我们可以通过测试来查看SQL Server优化器的选择。

我们先创建两张表(Headers和Details):

1.执行下面查询,查看执行计划:

select *

from Headers

inner join Details on Headers.ID=Details.HeaderID

go

2.查看执行计划,可以看出查询优化器使用了哈希匹配:

SQL Server执行计划那些事儿(1)——哈希、合并、嵌套联接的选择

3.在两表中创建聚集索引

create nonclustered index index_details_headerID on details(headerID)

create unique clustered index index_details_ID_headerID on details(headerID,ID)

4、执行上面查询,开启执行计划,可以看出此时优化器使用了合并联接

SQL Server执行计划那些事儿(1)——哈希、合并、嵌套联接的选择

5.现在执行下面查询语句(带where 过滤):

select *from Headers inner join Details on Headers.ID=Details.HeaderID where Details.ID=500

6.通过查看执行计划得出,当结果集比较小的时候优化器选择了嵌套循环:

SQL Server执行计划那些事儿(1)——哈希、合并、嵌套联接的选择

总结

通过上面的我们可以得出,三种联接各有优略,视乎情况而定。但是如果可以的话,应该在关联列上建立索引。