淺談SQL Server中的三種物理銜接操作(機能比擬)

淺談SQL Server中的三種物理銜接操作(機能比擬)。本站提示廣大學習愛好者：（淺談SQL Server中的三種物理銜接操作(機能比擬)）文章只能為提供參考，不一定能成為您想要的結果。以下是淺談SQL Server中的三種物理銜接操作(機能比擬)正文

---

在SQL Server中，我們所罕見的表與表之間的Inner Join,Outer Join都邑被履行引擎依據所選的列，數據上能否有索引，所選數據的選擇性轉化為Loop Join,Merge Join,Hash Join這三種物理銜接中的一種。懂得這三種物理銜接是懂得在表銜接時處理機能成績的基本，上面我來對這三種銜接的道理，實用場景停止描寫。

嵌套輪回銜接(Nested Loop Join)
輪回嵌套銜接是最根本的銜接,正如其名所示那樣，須要停止輪回嵌套，嵌套輪回是三種方法中獨一支撐不等式銜接的方法，這類銜接方法的進程可以簡略的用下圖展現:



圖1.輪回嵌套銜接的第一步 
    

     圖2.輪回嵌套銜接的第二步

由下面兩個圖不好看出，輪回嵌套銜接查找外部輪回表的次數等於內部輪回的行數，當內部輪回沒有更多的行時，輪回嵌套停止。別的，還可以看出，這類銜接方法須要外部輪回的表有序（也就是有索引），而且內部輪回表的行數要小於外部輪回的行數，不然查詢剖析器就更偏向於Hash Join(會在本文前面講到)。

    經由過程嵌套輪回銜接也能夠看出，跟著數據量的增加這類方法對機能的消費將出現出指數級其余增加，所以數據量到必定水平時，查詢剖析器常常就會采取這類方法。

    上面我們經由過程例子來看一下輪回嵌套銜接,應用微軟的AdventureWorks數據庫:

   

    圖3.一個簡略的嵌套輪回銜接   

    圖3中ProductID是有索引的，而且在輪回的內部表中（Product表）相符ProductID=870的行有4688條，是以，對應的SalesOrderDetail表須要查找4688次。讓我們在下面的查詢中再斟酌別的一個例子,如圖4所示。

   

    圖4.額定的列帶來的額定的書簽查找

  

    由圖4中可以看出，因為多選擇了一個UnitPrice列，招致了銜接的索引沒法籠罩所求查詢，必需經由過程書簽查找來停止，這也是為何我們要養成只Select須要的列的好習氣，為懂得決下面的成績，我們既可以用籠罩索引，也能夠削減所需的列來防止書簽查找。別的，下面相符ProductID的行僅僅只要5條，所以查詢剖析器會選擇書簽查找，假設我們將相符前提的行停止增年夜，查詢剖析器會偏向於表掃描（平日來講到達表中行數的1%以上常常就會停止table scan而不是書簽查找，但這其實不相對），如圖5所示。

   

    圖5.查詢剖析器選擇了表掃描

 

    可以看出，查詢剖析器此時選擇了表掃描來停止銜接，這類方法效力要低下許多，是以好的籠罩索引和Select *都是須要留意的處所。別的，下面情形即便觸及到表掃描，仍然是比擬幻想的情形，更蹩腳的情形是應用多個不等式作為銜接時，查詢剖析器即便曉得每個列的統計散布，但卻不曉得幾個前提的結合散布，從而發生毛病的履行籌劃,如圖6所示。

   

    圖6.因為沒法預估結合散布，招致的誤差

   由圖6中，我們可以看出，估量的行數和現實的行數存在偉大的誤差，從而應當應用表掃描但查詢剖析器選擇了書簽查找，這類情形對機能的影響將會比表掃描加倍偉大。詳細年夜到甚麼水平呢？我們可以經由過程強迫表掃描和查詢剖析器的默許籌劃停止比對，如圖7所示。

   

    圖7.強迫表掃描機能反而更好

 

歸並銜接(Merge Join)

    談到歸並銜接，我忽然想起在西雅圖加入SQL Pass峰會早晨酒吧列隊點酒，因為我和別的一哥們站錯了地位，貌似我們兩個在插隊一樣，我趕忙說:I'm sorry,i thought here is end of line。對方無不滑稽的說:”It's OK,In SQL Server,We called it merge join”。

    由下面的小故事不好看出，Merge Join其實上就是將兩個有序隊列停止銜接，須要兩頭都曾經有序，所以不用像Loop Join那樣赓續的查找輪回外部的表。其次，Merge Join須要表銜接前提中至多有一個等號查詢剖析器才會去選擇Merge Join。

    Merge Join的進程我們可以簡略用上面圖停止描寫:

   

    圖8.Merge Join第一步

 

    Merge Join起首從兩個輸出聚集中各取第一行，假如婚配，則前往婚配行。參加兩行不婚配，則有較小值的輸出聚集+1，如圖9所示。

   

    圖9.更小值的輸出聚集向下進1

    用C#代碼表現Merge Join的話如代碼1所示。

public class MergeJoin
{
// Assume that left and right are already sorted
public static Relation Sort(Relation left, Relation right)
{
Relation output = new Relation();
while (!left.IsPastEnd() && !right.IsPastEnd())
{
if (left.Key == right.Key)
{
output.Add(left.Key);
left.Advance();
right.Advance();
}
else if (left.Key < right.Key)
left.Advance();
else //(left.Key > right.Key)
right.Advance();
}
return output;
}
}

代碼1.Merge Join的C#代碼表現

    是以，平日來講Merge Join假如輸出兩頭有序，則Merge Join效力會異常高，然則假如須要應用顯式Sort來包管有序完成Merge Join的話，那末Hash Join將會是效力更高的選擇。然則也有一種破例，那就是查詢中存在order by,group by,distinct等能夠招致查詢剖析器不能不停止顯式排序，那末關於查詢剖析器來講，橫豎都曾經停止顯式Sort了,何紛歧石二鳥的直接應用Sort後的成果停止本錢更小的MERGE JOIN？在這類情形下，Merge Join將會是更好的選擇。

    別的，我們可以由Merge Join的道理看出，當銜接前提為不等式(但不包含!=)，好比說> < >=等方法時，Merge Join有著更好的效力。

    上面我們來看一個簡略的Merge Join,這個Merge Join是由集合索引和非集合索引來包管Merge Join的兩頭有序，如圖10所示。

   

    圖10.由集合索引和非集合索引包管輸出兩頭有序

 

    固然，當Order By,Group By時查詢剖析器不能不用顯式Sort,從而可以一舉兩得時，也會選擇Merge Join而不是Hash Join,如圖11所示。

   

    圖11.一舉兩得的Merge Join

哈希婚配(Hash Join)

    哈希婚配銜接絕對後面兩種方法加倍龐雜一些，然則哈希婚配關於年夜量數據，而且無序的情形下機能均好過Merge Join和Loop Join。關於銜接列沒有排序的情形下(也就是沒有索引)，查詢剖析器會偏向於應用Hash Join。

    哈希婚配分為兩個階段,分離為生成和探測階段，起首是生成階段，第一階段生成階段詳細的進程可以如圖12所示。

   

    圖12.哈希婚配的第一階段

    圖12中，將輸出源中的每個條目經由散列函數的盤算都放到分歧的Hash Bucket中，個中Hash Function的選擇和Hash Bucket的數目都是黑盒，微軟並沒有頒布詳細的算法，但我信任曾經長短常好的算法了。別的在Hash Bucket以內的條目是無序的。平日來說，查詢優化器都邑應用銜接兩頭中比擬小的哪一個輸出集來作為第一階段的輸出源。

    接上去是探測階段，關於另外一個輸出聚集，異樣針對每行停止散列函數，肯定其所應在的Hash Bucket,在針對這行和對應Hash Bucket中的每行停止婚配，假如婚配則前往對應的行。

    經由過程懂得哈希婚配的道理不好看出，哈希婚配觸及到散列函數，所以對CPU的消費會異常高，另外，在Hash Bucket中的行是無序的，所以輸入成果也是無序的。圖13是一個典范的哈希婚配，個中查詢剖析器應用了表數據量比擬小的Product表作為生成，而應用數據量年夜的SalesOrderDetail表作為探測。

   

    圖13.一個典范的哈希婚配銜接

下面的情形都是內存可以包容下生成階段所需的內存，假如內存吃緊，則還會觸及到Grace哈希婚配和遞歸哈希婚配，這便可能會用到TempDB從而吃失落年夜量的IO。這裡就不細說了,有興致的同窗可以移步:http://msdn.microsoft.com/zh-cn/library/aa178403(v=SQL.80).aspx。


總結

上面我們經由過程一個表格簡略總結這幾種銜接方法的消費和應用場景:

嵌套輪回銜接 歸並銜接 哈希銜接 實用場景 外層輪回小，內存輪回前提列有序 輸出兩頭都有序 數據量年夜，且沒有索引 CPU 低 低（假如沒有顯式排序） 高 內存 低 低（假如沒有顯式排序） 高 IO 能夠高能夠低 低 能夠高能夠低
懂得SQL Server這幾種物理銜接方法關於機能調優來講必弗成少，許多時刻當挑選前提多表銜接多時，查詢剖析器便可能不是那末智能了，是以懂得這幾種銜接方法關於定位成績變得尤其主要。另外，我們也能夠經由過程從營業角度削減查詢規模來削減低下機能銜接的能夠性。

參考文獻:

http://msdn.microsoft.com/zh-cn/library/aa178403(v=SQL.80).aspx
http://www.dbsophic.com/SQL-Server-Articles/physical-join-operators-merge-operator.html

文章來自：http://www.cnblogs.com/CareySon/