通往性能优化的天堂-地狱 JOIN方法说明

核心提示： f) 构造返回结果集，跟1.4步骤d)一样，通往性能优化的天堂-地狱 JOIN方法说明(5)，1.6 nested loop使用条件任何一个join操作，如果满足nested loop使用条件，索引碎片程度很高等情况，可能导致Bookmark Lookup成本非常高，查询优化过程中SQL

f) 构造返回结果集。跟1.4步骤d)一样。

1.6 nested loop使用条件

任何一个join操作，如果满足nested loop使用条件，查询优化过程中SQL Server就会对nested loop的成本（I/O成本、CPU成本等）进行评估，基于评估结果确定是否使用这种join方式。

使用nested loop方式的条件是：a) outer input的记录数不大，最好是在1000-2000以下，一般超过3000就很难说了，基本不大会选择nested loop。b) 作为inner input的表中，有可用于这个查询的索引。

这是因为outer input记录数不大，意味着外层循环次数比较小；inner input上有可用的索引，意味着在循环里面搜索inner input表中是否存在匹配的记录时，效率会很高，哪怕inner input表实际记录数有几百万。基于这两个条件，nested loop的执行效率非常高，在三种join方式里面，是内存和CPU消耗最少的一种（不合理的强制指定nested loop方式除外）。

关于使用条件另外的说明：outer input的记录数，并不是指outer input表中实际记录数，例如示例SQL中，如果tableA在col2上有维护统计信息（存在col2的索引或者是单独维护的统计信息），并且tableA.col2=?的条件值符合SARG（可搜索参数）形式，那么查询编译时刻SQL Server就能够利用统计信息和条件值评估出符合条件的记录数，查询执行时刻符合条件tableA.col2=?的记录才被用于外层循环。inner input表中有可用的索引，是指inner input表中用于和outer input表关联的字段（一个或多个字段）能够命中某个索引（这些字段的部分或者全部出现在某个索引字段的前面）。

符合上面的条件，也不是说SQL Server 100%就会选择nested loop。因为SQL Server的查询优化器是基于成本评估的，如果其它方案评估出的成本胜过这个，SQL Server会选择其它的join方式。举个例子，如果inner input上符合条件的索引是非聚集索引，这样SQL Server可能需要一个额外的Bookmark Lookup操作获取实际记录数据，如果inner input表数据量非常大，索引碎片程度很高等情况，可能导致Bookmark Lookup成本非常高，SQL Server会尝试其它join方案的评估选择。