哈希匹配和嵌套循环

[xiangaylian]

问下大虾们，哈希匹配和嵌套循环的区别在哪里，发现在前者的基础上做表扫描的时候，明显快了很大一截，什么时候会选择前者而不是后者，求真相。。

[xiangaylian]

引用 13 楼 DBA_Huangzj 的回复:

索引查找、扫描其实是指非聚集索引的行为，而非聚集索引的强项在范围查找。这样的话如果范围相对较大，它还是可能会做哈希匹配。你的EmployeeAbnormityInfor表上employeeid有没有索引？

没有索引，只有一个主键EmployeeAbnormityInfor.AbnormityID

[發糞塗牆]

主键默认会有聚集索引，如果没有，那这么它需要借助rid来找数据，称为书签查找，是其中一种最低效的查询，所以主键默认会有聚集索引。

[發糞塗牆]

非常简单的一个区别就是哈希适用于没有索引、没有预先排序，且数据量巨大的关联。而嵌套循环主要用于小数据量，所有索引的情况下，是非常高效的，但是哈希往往又意味着缺失了适当的索引，到时只能大表关联。对于一个表，特别是大表，不应该没有索引，所以哈希往往也是性能问题的其中一个标志。

[xiangaylian]

上面图的情况是，我多关联了一个大表，Employees的表扫描就会在“嵌套循环”上，少关联这个表，就在“哈希匹配”上，为什么会有此区别？为什么...为什么...为什么...

[xiangaylian]

语句1：

select EmployeeAbnormityInfor.AbnormityID 
from EmployeeAbnormityInfor 
inner join WorkFlows on EmployeeAbnormityInfor.WorkFlowId = WorkFlows.WorkFlowID and WorkFlows.SurveillantId = 65928 
inner join Employees on EmployeeAbnormityInfor.EmployeeID = Employees.EmployeeID 
inner join Departments on Employees.DepartmentID = Departments.DepartmentID 

执行计划1：

[發糞塗牆]

你可以看到你的表都是表扫描或者索引扫描（等同表扫描），也就以为这你的查询其实没用到索引。如果是2008的话应该会有缺失索引的提示的。

[發糞塗牆]

顺便建议引用一下我的回复，不然不知道你回复了。

[xiangaylian]

语句2：

;with FlowLastStep as
(
	select max(FlowDetailID) as FlowDetailID,FlowDataID,ModuleCode from FlowStepDetail 
	where ModuleCode = '000010000600002'
	group by FlowDataID,ModuleCode
)
select EmployeeAbnormityInfor.AbnormityID 
from EmployeeAbnormityInfor 
inner join WorkFlows on EmployeeAbnormityInfor.WorkFlowId = WorkFlows.WorkFlowID and WorkFlows.SurveillantId = 65928 
inner join Employees on EmployeeAbnormityInfor.EmployeeID = Employees.EmployeeID 
inner join Departments on Employees.DepartmentID = Departments.DepartmentID  
inner join FlowLastStep on FlowLastStep.FlowDataID = EmployeeAbnormityInfor.AbnormityID

[發糞塗牆]

究竟哪句是快的？分开来说看不懂呢。

[xiangaylian]

引用 4 楼 DBA_Huangzj 的回复:

你可以看到你的表都是表扫描或者索引扫描（等同表扫描），也就以为这你的查询其实没用到索引。如果是2008的话应该会有缺失索引的提示的。

谢谢，我用的是2008，创建了缺失的索引后快了，Employees的表扫描变成了索引查找。 但还是没明白，为什么Employees的表扫描或索引查找，第一次是在“哈希匹配”上，第二次是在“嵌套循环”上，是什么对这种数据的查找方式构成了影响...

[發糞塗牆]

如果where条件一样且数据量没有太大变化，那么主要是索引影响的。但是写法也会对关联方式有影响。

[xiangaylian]

引用 7 楼 DBA_Huangzj 的回复:

究竟哪句是快的？分开来说看不懂呢。

6#的那句是慢的，慢得离谱，对应的执行计划是0#的那个图
3#的那句是快的
另外创建了索引后的执行计划如下：

[發糞塗牆]

FlowLastStep由于是临时数据，没有索引，所以如果数据量很大的话，类似于一个没有任何索引的大表。关联的时候无法找到好的关联方式，所以使用哈希来关联。

[xiangaylian]

引用 11 楼 DBA_Huangzj 的回复:

FlowLastStep由于是临时数据，没有索引，所以如果数据量很大的话，类似于一个没有任何索引的大表。关联的时候无法找到好的关联方式，所以使用哈希来关联。

从#0和#10的计划执行情况来看，貌似不是你说的那样额.... 两个的区别只在于一个有索引一个没索引，没索引的为何也没有用哈希来关联呢？

[發糞塗牆]

索引查找、扫描其实是指非聚集索引的行为，而非聚集索引的强项在范围查找。这样的话如果范围相对较大，它还是可能会做哈希匹配。你的EmployeeAbnormityInfor表上employeeid有没有索引？

[DBA_磊仔]

哈希匹配和嵌套循环是不同的机制，适用于不同的情况，对数据库而言，选择何种连接方式是基于开销的，数据库会根据统计信息选择开销相对较小的连接方式，除非你用了提示。 如果一定要找规律，大概 排序、索引查找后面接的多是嵌套循环 位图、表扫描、并行度后面接的多是哈希匹配

[xiangaylian]

很感谢版主的热情回答。。