SqlServer查詢計劃

 

對於一個比較慢的查詢來說，我們通常首先要知道哪些步驟的成本比較高，進而，可以嘗試一些改進的方法。一般來說，如果您不能通過：提高硬體效能或者調整OS,SqlServer的設定之類的方式來解決問題，那麼剩下的可選方法通常也只有以下這些了：
1. 為【scan】這類操作增加相應欄位的索引。
2. 有時重建索引或許也是有效，具體情形請參考後文。
3. 調整語句結構，引導SqlServer採用其它的查詢方案去執行。
4. 調整表結構（分表或者分區）。

 

Sql Server 尋找記錄的方法

 

說到這裡，不得不說SqlServer的索引了。SqlServer有二種索引：叢集索引和非叢集索引。二者的差別在於：【叢集索引】直接決定了記錄的存放位置，或者說：根據叢集索引可以直接擷取到記錄。【非叢集索引】儲存了二個資訊：1.相應索引欄位的值，2.記錄對應叢集索引的位置（如果表沒有叢集索引則儲存記錄指標）。因此，如果能通過【叢集索引】來尋找記錄，顯然也是最快的。

Sql Server 會有以下方法來尋找您需要的資料記錄：
1. 【Table Scan】：遍曆整個表，尋找所匹配的記錄行。這個操作將會一行一行的檢查，當然，效率也是最差的。
2. 【Index Scan】：根據索引，從表中過濾出來一部分記錄，再尋找所匹配的記錄行，顯示比第一種方式的尋找範圍要小，因此比【Table Scan】要快。
3. 【Index Seek】：根據索引，定位（擷取）記錄的存放位置，然後取得記錄，因此，比起前二種方式會更快。
4. 【Clustered Index Scan】：和【Table Scan】一樣。注意：不要以為這裡有個Index，就認為不一樣了。其實它的意思是說：按叢集索引來漸進式掃描每一行記錄，因為記錄就是按叢集索引來順序存放的。而【Table Scan】只是說：要掃描的表沒有叢集索引而已，因此這二個操作本質上也是一樣的。
5. 【Clustered Index Seek】：直接根據叢集索引擷取記錄，最快！

所以，當發現某個查詢比較慢時，可以首先檢查哪些操作的成本比較高，再看看那些操作是尋找記錄時，是不是【Table Scan】或者【Clustered Index Scan】，如果確實和這二種操作類型有關，則要考慮增加索引來解決了。不過，增加索引後，也會影響資料表的修改動作，因為修改資料表時，要更新相應欄位的索引。所以索引過多，也會影響效能。還有一種情況是不適合增加索引的：某個欄位用0或1表示的狀態。例如可能有絕大多數是1，那麼此時加索引根本就沒有意義。這時只能考慮為0或者1這二種情況分開來儲存了，分表或者分區都是不錯的選擇。

如果不能通過增加索引和調整表來解決，那麼可以試試調整語句結構，引導SqlServer採用其它的查詢方案去執行。這種方法要求： 1.對語句所要完成的功能很清楚， 2.對要查詢的資料表結構很清楚， 3.對相關的業務背景知識很清楚。如果能通過這種方法去解決，當然也是很好的解決方案了。不過，有時SqlServer比較智能，即使你調整語句結構，也不會影響它的執行計畫。

如何比較二個同樣功能的語句的效能好壞呢，我建議採用二種方法： 1. 直接把二個查詢語句放在【SQL Server Management Studio】，然後去看它們的【執行計畫】，SqlServer會以百分比的方式告訴你二個查詢的【查詢開銷】。這種方法簡單，通常也是可以參考的，不過，有時也會不準，具體原因請接著往下看(可能索引統計資訊過舊)。
2. 根據真實的程式調用，寫相應的測試代碼去調用：這種方法就麻煩一些，但是它更能代表現實調用情況，得到的結果也是更具有參考價值的，因此也是值得的。

Sql Server Join 方式

在Sql Server中，我們每個join命令，都會在內部執行時，採用三種更具體的方式來運行：

1. 【Nested Loops join】，如果一個聯結輸入很小，而另一個聯結輸入很大而且已在其聯結列上建立了索引，則索引 Nested Loops 串連是最快的聯結操作，因為它們需要的 I/O 和比較都最少。

巢狀迴圈聯結也稱為“嵌套迭代”，它將一個聯結輸入用作外部輸入表（顯示為圖形執行計畫中的頂端輸入），將另一個聯結輸入用作內部（底端）輸入表。外部迴圈逐行處理外部輸入表。內部迴圈會針對每個外部行執行，在內部輸入表中搜尋匹配行。可以用下面的偽碼來理解：

foreach(row r1 in outer table)    foreach(row r2 in inner table)        if( r1, r2 符合匹配條件 )            output(r1, r2);

最簡單的情況是，搜尋時掃描整個表或索引；這稱為“單純巢狀迴圈聯結”。如果搜尋時使用索引，則稱為“索引巢狀迴圈聯結”。如果將索引產生為查詢計劃的一部分（並在查詢完成後立即將索引破壞），則稱為“臨時索引巢狀迴圈聯結”。查詢最佳化工具考慮了所有這些不同情況。

如果外部輸入較小而內部輸入較大且預先建立了索引，則巢狀迴圈聯結尤其有效。在許多小事務中（如那些隻影響較小的一組行的事務），索引巢狀迴圈聯結優於合并聯結和雜湊聯結。但在大型查詢中，巢狀迴圈聯結通常不是最佳選擇。

2. 【Merge Join】，如果兩個聯結輸入並不小但已在二者聯結列上排序（例如，如果它們是通過掃描已排序的索引獲得的），則合并聯結是最快的聯結操作。如果兩個聯結輸入都很大，而且這兩個輸入的大小差不多，則預先排序的合并聯結提供的效能與雜湊聯結相近。但是，如果這兩個輸入的大小相差很大，則雜湊聯結操作通常快得多。

合并聯結要求兩個輸入都在合并列上排序，而合并列由聯結謂詞的等效 (ON) 子句定義。通常，查詢最佳化工具掃描索引（如果在適當的一組列上存在索引），或在合并聯結的下面放一個排序運算子。在極少數情況下，雖然可能有多個等效子句，但只用其中一些可用的等效子句獲得合并列。

由於每個輸入都已排序，因此 Merge Join 運算子將從每個輸入擷取一行並將其進行比較。例如，對於內聯結操作，如果行相等則返回。如果行不相等，則廢棄值較小的行並從該輸入獲得另一行。這一過程將重複進行，直到處理完所有的行為止。

合并聯結操作可以是常規操作，也可以是多對多操作。多對多合并聯結使用暫存資料表儲存行（會影響效率）。如果每個輸入中有重複值，則在處理其中一個輸入中的每個重複項時，另一個輸入必須重繞到重複項的開始位置。可以建立唯一索引告訴SqlServer不會有重複值。

如果存在駐留謂詞，則所有滿足合并謂詞的行都將對該駐留謂詞取值，而只返回那些滿足該駐留謂詞的行。

合并聯結本身的速度很快，但如果需要排序操作，選擇合并聯結就會非常費時。然而，如果資料量很大且能夠從現有 B 樹索引中獲得預排序的所需資料，則合并聯結通常是最快的可用聯結演算法。

3. 【Hash Join】，雜湊聯結可以有效處理未排序的大型非索引輸入。它們對複雜查詢的中間結果很有用，因為： 1. 中間結果未經索引（除非已經顯式儲存到磁碟上然後建立索引），而且通常不為查詢計劃中的下一個操作進行適當的排序。 2. 查詢最佳化工具只估計中間結果的大小。由於對於複雜查詢，估計可能有很大的誤差，因此如果中間結果比預期的大得多，則處理中間結果的演算法不僅必須有效而且必須適度弱化。

雜湊聯結可以減少使用非正常化。非正常化一般通過減少聯結操作獲得更好的效能，儘管這樣做有冗餘之險（如不一致的更新）。雜湊聯結則減少使用非正常化的需要。雜湊聯結使垂直資料分割（用單獨的檔案或索引代表單個表中的幾組列）得以成為物理資料庫設計的可行選項。

雜湊聯結有兩種輸入：產生輸入和探測輸入。查詢最佳化工具指派這些角色，使兩個輸入中較小的那個作為產生輸入。

雜湊聯結用於多種設定匹配操作：內部聯結；左方外部聯結、右方外部聯結和完整外部聯結；左半聯結和右半聯結；交集；聯合和差異。此外，雜湊聯結的某種變形可以進行重複刪除和分組，例如 SUM(salary) GROUP BY department。這些修改對產生和探測角色只使用一個輸入。

雜湊聯結又分為3個類型：記憶體中的雜湊聯結、Grace 雜湊聯結和遞迴雜湊聯結。

記憶體中的雜湊聯結：雜湊聯結先掃描或計算整個產生輸入，然後在記憶體中產生雜湊表。根據計算得出的雜湊鍵的雜湊值，將每行插入雜湊儲存桶。如果整個產生輸入小於可用記憶體，則可以將所有行都插入雜湊表中。產生階段之後是探測階段。一次一行地對整個探測輸入進行掃描或計算，並為每個探測行計算雜湊鍵的值，掃描相應的雜湊儲存桶並產生匹配項。

Grace 雜湊聯結：如果產生輸入大於記憶體，雜湊聯結將分為幾步進行。這稱為“Grace 雜湊聯結”。每一步都分為產生階段和探測階段。首先，消耗整個產生和探測輸入並將其分區（使用雜湊鍵上的雜湊函數）為多個檔案。對雜湊鍵使用雜湊函數可以保證任意兩個聯結記錄一定位於相同的檔案對中。因此，聯結兩個大輸入的任務簡化為相同任務的多個較小的執行個體。然後將雜湊聯結應用於每對分區檔案。

遞迴雜湊聯結：如果產生輸入非常大，以至於標準外部合并的輸入需要多個合并層級，則需要多個分區步驟和多個分區層級。如果只有某些分區較大，則只需對那些分區使用附加的分區步驟。為了使所有分區步驟儘可能快，將使用大的非同步 I/O 操作以便單個線程就能使多個磁碟機繁忙工作。

在最佳化過程中不能始終確定使用哪種雜湊聯結。因此，SQL Server 開始時使用記憶體中的雜湊聯結，然後根據產生輸入的大小逐漸轉換到 Grace 雜湊聯結和遞迴雜湊聯結。

如果最佳化器錯誤地預計兩個輸入中哪個較小並由此確定哪個作為產生輸入，產生角色和探測角色將動態反轉。雜湊聯結確保使用較小的溢出檔案作為產生輸入。這一技術稱為“角色反轉”。至少一個檔案溢出到磁碟後，雜湊聯結中才會發生角色反轉。

說明：您也可以顯式的指定聯結方式，SqlServer會盡量尊重您的選擇。比如你可以這樣寫：inner loop join, left outer merge join, inner hash join

但是，我還是建議您不要這樣做，因為SqlServer的選擇基本上都是正確的，不信您可以試一下。