[转]SQL Server 2000执行计划成本(4/5)

循环、哈希与合并连接比较

      在分别讨论了循环、哈希与合并连接的执行计划成本规则之后，现在将3个连接类型放到适合各自条件的环境下一起比较。
下面的图2-27显示了循环、哈希与合并连接在1P/2P系统上的启动成本。外部源和内部源启动成本描述了索引搜索的基本成本包括每行成本0.0000011。连接成本也除去第一行，这样似乎仅有个案是少量变化的。4P系统的启动成本仅在索引搜索基本成本（0.003283025）上不同。

图2-27.循环、哈希与合并连接在1P/2P系统上索引搜索的启动成本

      表2-1显示了在一对一的循环、哈希与合并连接在两个表都有明确的SARG下的每行成本和每页成本。每行（/r）成本适用于所有行，每页（/p）成本仅适用于需要增加的叶页。每行和每页总成本的规则需要将每页条件计算在内。

Per row costs 1-1	Outer Source	Inner Source	Join	Total
Loop	0.00000110/r	0.00007960-	0.00000418/r	0.000084880-
Hash	+0.00074074/p	~0.00015400/r	~0.00001881/r	~0.000155280
Merge	0.00000110/r	0.00000110/r	~0.00000446/r	+0.00074074/p

表2-1.连接里增加行的成本规则构成

 

      表2-2显示了每页10行和100行时计算得到的每行总成本，此时还要计算增加的叶页成本。

Per row costs 1-1	10 rows / page	100 rows / page
Loop	0.0002294	0.0001627
Hash	0.0001692	0.0000358
Merge	0.0001548	0.0000215

表2-2.连接里增加行的成本规则构成

      循环连接的启动成本最低且确定。这主要是由于循环连接操作自身没有而哈希与合并连接各自有~0.017770与~0.0056046的基本成本。
3个连接的外部源成本相同，都是索引搜索操作。3个连接类型在每行成本结构上的不同大部分来自内部源，部分来自连接自身。循环连接的内部源成本结构反映了重复的索引搜索操作。哈希与合并连接的内部源成本结构仅仅是索引搜索操作。连接自身的每行成本循环连接是最低的，紧随的是合并连接而哈希连接大约是循环连接的4倍。
      少量行参与时循环连接的成本结构有最低的计划成本。合并连接有最低的总的每行成本，接着是哈希连接，而循环连接有最高每行成本。合并连接总是比哈希连接的计划成本低。在一些行参与时，合并连接的开销小于循环连接。在大类行参与时，哈希连接也比循环连接的开销低。合并与哈希连接的好处在于对于高密度（每页行数）索引来说每行成本是最好的。当每页里的行数即密度低时，内部源的每行成本抵消了合并与哈希连接的这一好处。
表2-3显示了内部源增加行的每行成本。

 

Additional IS costs per row	Inner Source	Join
Loop	0.00000110/r +0.00074074/p	0.00000418/r
Hash	0.00000110/r +0.00074074/p	0.00000523/r
Merge	0.00000110/r +0.00074074/p	0.00000237/r

表2-3增加行的内部源成本

      注意循环连接的每页组件仅适用于外部源每行连接到内部源足够多的行以要求额外的叶页的情形，而哈希与合并连接的每页组件适用于内部源的所有行的总和要求额外的叶页的情形。如果每个表的行数差别很大，那么哈希和合并连接就会丧失其好处，因为循环连接有更低的内部源每行成本。
图2-28a和2-28b显示了循环、哈希与合并连接在为内部源和外部源表指定明确的搜索参数情形下1P/2P系统的正常的总成本，此时每个表有适当的索引，且外部源的每一行正好连接到内部源的一行。查询成本的计算和1P/2P系统的单行索引搜索成本（0.0064081）有关。

图2-28a.1P/2P系统上的循环、哈希与合并连接的总成本（对数比例）

图2-28b.1P/2P系统上的循环、哈希与合并连接的正常成本（线性比例）

      循环连接操作自身没有启动成本，仅在内部源和外部源表的索引搜索操作上有启动成本。哈希连接操作的启动成本在1P/2P系统上刚刚少于索引搜索的3倍而总的启动成本刚刚少于索引搜索操作的5倍。合并索引的启动成本在1P/2P系统上刚刚少于单个索引搜索而总的启动成本也稍微少于索引搜索的3倍。当两个表上有适当的的聚集索引或覆盖索引可用时，合并连接变得比循环连接在40行时有利，哈希连接则在～160行时比循环连接有利。在这个例子里，执行计划使用紧凑的高密度（每页超过400行）的覆盖索引。对于低密度的索引来说交叉点在更高的位置。
      图2-29显示了4P系统的循环、哈希与合并连接的成本。4P系统的3个类型的连接的交叉点与1P/2P系统稍微有些不同。在4P服务器上，索引搜索基本I/O成本大约是1P/2P系统的一半。哈希与合并连接基本成本在1P/2P和4P系统上没有变化，所以启动成本在绝对数值上更低，但4P系统的索引搜索成本更高。

图2-29.4P系统上的循环、哈希与合并连接的正常成本（线性比例）

      图2-30显示了带排序的合并和多对多合并连接和标准的循环、哈希、合并连接的比较。

图2-30.带排序的合并和多对多合并连接与循环、哈希、合并连接的比较（都在4p系统上）

      带排序的开销在参与行不多时稍微少于哈希合并连接，但在参与行很多的时候开销就更高了。多对多合并连接在参与行不多时成本介于常规的合并和哈希连接之间，但在参与行很多的时候开销就要比两者都高。
      在两个表中仅有一个存在搜索条件的情形下，哈希与合并连接会要求对另一个表进行表扫描。循环连接可以使用连接条件上的索引来避免表扫描。由于在1P/2P系统上表扫描基本成本是索引搜索基本成本的6倍（4P系统上是12倍），并且比增加的行成本高得多。所以合并与循环、哈希与循环的交叉点位于参与行充分多的时候（500-700行）。
      当然，循环、哈希与合并连接的特征在BOL的“优化数据库性能”一节的“高级查询调优概念”里有描述。然而，看看实际的数字而不是依赖于定性的描述如小规模相似是值得的。