淺談SQL SERVER中的物理聯結演算法

在深入叢集索引與非叢集索引（一）（二）中，（好吧，由於沒什麼人看，因此沒寫二），我們詳細的分析了SQL SERVER是如何用堆和B樹來組織表，並用這兩個資料結構協助我們查詢的。

 

這裡我們繼續的內容就是探討SQL SERVER中的串連演算法。

 

聯結演算法是指在物理上把多個資料來源如何聯結起來,SQL SERVER支援三種聯結演算法

1.nested loop 嵌套迴圈演算法

2.merge 合并演算法

3.hash 雜湊演算法

 

其實這幾種演算法我們在通常的編程中也經常會用到，並不是很難理解。

 

一、嵌套迴圈

一般來說嵌套迴圈就對應著兩層for迴圈，對於外層for中的每一個項，在內層迴圈中都要匹配一次。

相應的，外層for對應著外部輸入表，在執行計畫的圖示中排在上面，內層for則是內層輸入表，在執行計畫中排在下面。

這裡值得強調的是，外部輸入是每一行的都要使用來匹配的，而內部表卻不一定每一行都在匹配中使用。假設外部輸入有N行，內部輸入表有M行，最差的時間複雜度就是O(N*M)。而在這種最差的情況下，最佳化器不會再採用嵌套迴圈，而是採用Hash匹配演算法。關於Hash匹配演算法，後面會有說明。

因此我們可以得到下面的推論

1.外部輸入越小越好，因為外部輸入每一行都要被用來匹配，不能減少。

2.內部輸入表作為匹配的，則可以利用索引來減少匹配條件的範圍，這樣就可以通過少量的搜尋來擷取匹配行。

因此嵌套迴圈演算法在串連條件的選擇性比較強，而且在內部輸入的串連列上有可以利用的有效索引時，是最有效。

 

在下面這個例子中，Customers表的記錄數要遠比Orders表的記錄數少（客戶數肯定要比訂單數少），因此Customers表中的資料被最佳化器選擇作為外部輸入，可以看出Customers表是在Orders表的上方。

當我們直接查詢是，SQL SERVER還會很智能的告訴你缺少什麼索引。在下面這個例子中，我們缺少的正是作為內部輸入的“串連列 custid”和“查詢列 orderdate”上的非叢集索引

 

 

當我們輸入下面這條語句加上索引後

CREATE INDEX idx_nc_cid_od_i_oid_eid_sid
  ON dbo.Orders(custid, orderdate)
  INCLUDE(orderid, empid, shipperid);

 

SQL SERVER仍然報缺少索引，是因為我們對於外部輸入的表仍然是可以利用索引來先篩選一輪，以起到減小外部輸入的目的。

為了達到這個目的，缺少的是這個非叢集索引，custname作為篩選列，加上custid同樣是為了起到覆蓋作用。

CREATE INDEX idx_nc_cn_i_cid
  ON dbo.Customers(custname) INCLUDE(custid);

這時對於Customers表的index scan就會變成Index seek。

 

補全索引後，我們最終獲得的執行計畫

 

 

 

 

二、合并排序

對於兩個輸入列都有序的情況下，合并聯結的效率高。

排序的重要性毋庸置疑了，什麼二分尋找等等尋找都是建立在輸入序列有序的基礎上。

為什麼先講索引呢？我們可以從索引中發現有現成的排序好的資料結構嗎？有的，B樹中的葉層就是按照一定的邏輯順序維護的。也就是說，叢集索引和非聚集覆蓋索引，都可以通過對葉層的有序掃描以較小的代價就可以擷取有序的資料。在這種情況下，就算輸入表的規模比較大，合并聯結也相當給力。如果計劃分析器確定串連的一側記錄集中的元素是唯一確定的，那麼就會採用一對多的匹配方式（多指另一側的元素會有重複），在這種情況下，合并排序效率應該是幾種串連方式中最高的。

但如果所需的資料列並不存在上述的條件的時候，對於較大的輸入來說排序往往是一個開銷非常大的操作（因為基於比較的排序最快也就是n log n的），因此最佳化器通常不會在這種情況下選用合并聯結。但是對於較小的輸入排序的消耗還是可以接受的。較小的輸入可以像上例一樣通過對自身的篩選來獲得。

 

 

分析：

對於串連列custid,對於Customers表不用說，是該表的叢集索引的聚集鍵，對於order表來說，我們在上面給custid建立了非聚集覆蓋索引，所以也可以按照有序掃描以較小的代價擷取有序資料。

因為都可以從兩張表中以較小的代價擷取按照串連列custid的順序擷取有序的資料，因此最佳化器選擇了合并排序。

 

可以看出Orders表的掃描在整體開銷中所佔的比例是最大的達到68%，因為Orders表的資料非常多。那麼假設我們在Orders表上還有其他的篩選條件呢？比如對orderdate進行限定

 

由於這個篩選器的選擇性非常高，所擷取的結果才1000多行，只佔Orders表1000000總數下的0.1%。兩權之下，另可放棄有序的全表掃描，而是先過濾再排序。

當然，計劃器也有可能估計錯誤，如果所擷取的結果選擇性不高的話，排序所佔用的開銷往往非常大。這也是我們在發現最佳化器產生Merge計劃時要注意的地方。

 

 

三、Hash聯結

原理參照我寫的這篇文章，為了減少記憶體佔用因此使用資料量較小的表來構造hashtable,然後另漸進式掃描另一張表通過hash函數算出hashtable的某個位置上是否已存在值來判斷相等。

通常用到hash聯結,是因為缺少現成的索引，特別是在資料倉儲類型(OLTP)的應用中.

我們在未建立任何索引的第一個樣本中，採取的就是Hash匹配。漸進式掃描Customer表構造hashtable,因為我們where條件中有orderdate可以減小匹配的範圍，所以先用叢集索引減少Orders表中匹配的記錄數，然後再用hash函數逐行匹配前面構造好的hashtable.

 

缺少合適的索引也可能會採用Hash匹配。我們把orderdate的範圍增加了幾十倍，由一天改成了查詢幾個月，這時合并聯結演算法不再適合。

 

 

總結：

採用Hash聯結演算法，從時間複雜度上來說是最優的，聯結一張M條記錄的表和一張N條記錄的表的時間複雜度為O(M+N),好於帶有叢集索引的嵌套聯結演算法O（M * log2n）。但是如果在構造hashtable時記憶體不足以儲存Hashtable時，會產生臨時空間交換，導致而外大量的IO從而抵消了聯結時所產生的益處。

 

當然如果你覺得有協助，請點擊推薦，或者留點評論說說想法，討論討論。

下面打算寫寫我在學習Object C和Git中的一些心得體會系列文章。