通往性能优化的JOIN方法说明(3)

1.6 nested loop使用条件 7I1tz 6K  
V30qE  
任何一个join操作，如果满足nested loop使用条件，查询优化过程中SQL Server就会对nested loop的成本（I/O成本、CPU成本等）进行评估，基于评估结果确定是否使用这种join方式。 /wZ'k  
B,3;fw`Y   
使用nested loop方式的条件是：a) outer input的记录数不大，最好是在1000-2000以下，一般超过3000就很难说了，基本不大会选择nested loop。b) 作为inner input的表中，有可用于这个查询的索引。 n"Ttez6  
B78rP )  
这是因为outer input记录数不大，意味着外层循环次数比较小；inner input上有可用的索引，意味着在循环里面搜索inner input表中是否存在匹配的记录时，效率会很高，哪怕inner input表实际记录数有几百万。基于这两个条件，nested loop的执行效率非常高，在三种join方式里面，是内存和CPU消耗最少的一种（不合理的强制指定nested loop方式除外）。 mEBQ/dl  
jgR`^j  
关于使用条件另外的说明：outer input的记录数，并不是指outer input表中实际记录数，例如示例SQL中，如果tableA在col2上有维护统计信息（存在col2的索引或者是单独维护的统计信息），并且tableA.col2=?的条件值符合SARG（可搜索参数）形式，那么查询编译时刻SQL Server就能够利用统计信息和条件值评估出符合条件的记录数，查询执行时刻符合条件tableA.col2=?的记录才被用于外层循环。inner input表中有可用的索引，是指inner input表中用于和outer input表关联的字段（一个或多个字段）能够命中某个索引（这些字段的部分或者全部出现在某个索引字段的前面）。 p[y=p
r  
p[ (.;KB (  
符合上面的条件，也不是说SQL Server 100%就会选择nested loop。因为SQL Server的查询优化器是基于成本评估的，如果其它方案评估出的成本胜过这个，SQL Server会选择其它的join方式。举个例子，如果inner input上符合条件的索引是非聚集索引，这样SQL Server可能需要一个额外的Bookmark Lookup操作获取实际记录数据，如果inner input表数据量非常大，索引碎片程度很高等情况，可能导致Bookmark Lookup成本非常高，SQL Server会尝试其它join方案的评估选择。 ,qbpw&xN  
h0,E1")b  
1.7 强制指定nested loop方式 E&oP%
O  

ZG~zp#CG0  
使用loop关键字实现，例如tableA inner loop join tableB，将强制SQL Server使用nested loop方式执行这个join操作。或者使用option选项，例如tableA inner join tableB option(loop join)　nested loop算法有它适用的范围，在这个范围之内效率是最高的，超出这个范围效率反而很差，除非你有十分的把握，不要随意强制指定join方式。 jw+:T<q  
~[RGK  
接下来就不再象上面这样详细的讲述了。 ^%<&HXx,  
!W;mTw;  
2. merge join kb |7XH  
Cw%&G0+  
merge join第一个步骤是确保两个关联表都是按照关联的字段进行排序。如果关联字段有可用的索引，并且排序一致，则可以直接进行merge join操作；否则，SQL Server需要先对关联的表按照关联字段进行一次排序（就是说在merge join前的两个输入上，可能都需要执行一个Sort操作，再进行merge join）。 8 LxJ_  
%' $Rp <d  
两个表都按照关联字段排序好之后，merge join操作从每个表取一条记录开始匹配，如果符合关联条件，则放入结果集中；否则，将关联字段值较小的记录抛弃，从这条记录对应的表中取下一条记录继续进行匹配，直到整个循环结束。 !eD? ;}mj  
 RM.H)
 
在多对多的关联表上执行merge join时，通常需要使用临时表进行操作。例如A join B使用merge join时，如果对于关联字段的某一组值，在A和B中都存在多条记录A1、A2...An、B1、B2...Bn，则为A中每一条记录A1、A2...An，都必须在B中对所有相等的记录B1、B2...Bn进行一次匹配。这样，指针需要多次从B1移动到Bn，每一次都需要读取相应的B1...Bn记录。将B1...Bn的记录预先读出来放入内存临时表中，比从原数据页或磁盘读取要快。 ^[7_
?  
)D 9}It=  
merge join操作本身是非常快的，但是merge join前进行的排序可能会相当耗时（SQL Server最消耗内存和CPU的操作，一个是大数据排序，一个是大数据的hash运算，这都是指查询计划里面的Sort以及Hash相关的操作，例如hash join、使用hash算法实现的Distinct操作等，而不是指你的SQL中order by关键字），尤其是对数据量非常大的记录集，因此导致使用merge join的查询成本变得非常高。对于数据量非常大的表，如果merge join的关联字段可以使用聚集索引，merge join是最快的Join方法之一。因此优化方案是在表结构设计层面良好的设计关联关系和表的索引结构，SQL语句充分利用索引，尽可能减少merge join前的排序操作，减少Bookmark Lookup操作。 e0jti]+n`  
fiK7rKJOq  
一般情况下，如果无法满足nested loop条件，会考虑对merge join方法的评估。merge join的选择，主要是考虑两个输入的数据量，以及分别对应于关联字段是否能够命中索引。例如tableA join tableB，关联字段在两个表中都能命中索引，数据量超过了nested loop的选择范围，则会考虑使用merge join方法。当然，如果tableA和tableB的数据量过大导致评估出来的成本过高，则会放弃merge join而评估hash join了。 Y[ t, AfI  
j> iK5'iq