前言

上一篇我們介紹了如何查看查詢計劃，本篇將介紹在我們查看的查詢計劃時的分析技巧，以及幾種我們常用的運算符優化技巧，同樣側重基礎知識的掌握。

通過本篇可以了解我們平常所寫的T-SQL語句，在SQL Server數據庫系統中是如何分解執行的，數據結果如何通過各個運算符組織形成的。

技術準備

基于SQL Server2008R2版本，利用微軟的一個更簡潔的案例庫（Northwind）進行解析。

一、數據連接

數據連接是我們在寫T-SQL語句的時候最常用的，通過兩個表之間關聯獲取想要的數據。

SQL Server默認支持三種物理連接運算符：嵌套循環連接、合并連接以及哈希連接。三種連接各有用途，各有特點，不同的場景會數據庫會為我們選擇最優的連接方式。

a、嵌套循環連接（nested loops join)

嵌套循環連接是最簡單也是最基礎的連接方式。兩張表通過關鍵字進行關聯，然后通過雙層循環依次進行兩張表的行進行關聯，然后通過關鍵字進行篩選。

可以參照下圖進行理解分析

其實嵌套掃描是很簡單的獲取數據的方式，簡單點就是兩層循環過濾出結果值。

我們可以通過如下代碼加深理解

for each row R1 in the outer table   for each row R2 int the inner table       if R1 join with R2       return (R1,R2)

舉個列子

SELECT o.OrderIDFROM Customers C JOIN Orders OON C.CustomerID=O.CustomerIDWHERE C.City=N'London'

以上這個圖標就是嵌套循環連接的圖標了。而且解釋的很明確。

這種方法的消耗就是外表和內表的乘積，其實就是我們所稱呼的笛卡爾積。所以消耗的大小是隨著兩張表的數據量增大而增加的，尤其是內部表，因為它是多次重復掃描的，所以我們在實踐中的采取的措施就是減少每個外表或者內表的行數來減少消耗。

對于這種算法還有一種提高性能的方式，因為兩張表是通過關鍵字進行關聯的，所以在查詢的時候對于底層的數據獲取速度直接關乎著此算法的性能，這里優化的方式盡量使用兩個表關鍵字為索引查詢，提高查詢速度。

還有一點就是在嵌套循環連接中，在兩張表關聯的時候，對外表都是有篩選條件的，比如上面例子中【WHERE C.City=N'London'】就是對外表（Customers）的篩選，并且這里的City列在該表中存在索引，所以該語句的兩個子查詢都為索引查找（Index Seek）。

但是，有些情況我們的查詢條件不是索引所覆蓋的，這時候，在嵌套循環連接下的子運算符就變成了索引掃描（Index scan）或者RID查找。

舉個例子

SELECT E1.EmployeeID,COUNT(*)FROM Employees E1 JOIN Employees E2ON E1.HireDate>E2.HireDateGROUP BY E1.EmployeeID

以上代碼是從職工表中獲取出每位職工入職前的人員數。我們看一下該查詢的執行計劃

這里很顯然兩個表的關聯通過的是HireDate列進行，而此列又不為索引項所覆蓋，所以兩張表的獲取只能通過全表的聚集索引掃描進行，如果這兩張表數據量特別大的話，無疑又是一個非常耗性能的查詢。

通過文本可以看出，該T-SQL的查詢結果的獲取是通過在嵌套循環運算符中，對兩個表經過全表掃描之后形成的笛卡兒積進行過濾篩選的。這種方式其實不是一個最優的方式，因為我們獲取的結果其實是可以先通過兩個表過濾之后，再通過嵌套循環運算符獲取結果，這樣的話性能會好很多。

我們嘗試改一下這個語句

SELECT E1.EmployeeID,ECNT.CNT FROM Employees E1 CROSS APPLY(   SELECT COUNT(*) CNT   FROM Employees E2   WHERE E1.HireDate<E2.HireDate)ECNT

通過上述代碼查詢的結果項，和上面的是一樣的，只是我們根據外部表的結果對內部表進行了過濾，這樣執行的時候就不需要獲取全部數據項了。

我們查看下文本執行計劃

我們比較一下，前后兩條語句的執行消耗，對比一下執行效率

執行時間從1秒179毫秒減少至93毫秒。效果明顯。

對比CPU消耗、內存、編譯時間等總體消耗都有所降低，參考上圖。

所以對嵌套循環連接連接的優化方式就是集中在這幾點：對兩張表數據量的減少、連接關鍵字上建立索引、謂詞查詢條件上覆蓋索引最好能減少符合謂詞條件的記錄數。

b、合并連接(merge join)

上面提到的嵌套循環連接方式存在著諸多的問題，尤其不適合兩張表都是大表的情況下，因為它會產生N多次的全表掃描，很顯然這種方式會嚴重的消耗資源。

鑒于上述原因，在數據庫里又提供了另外一種連接方式：合并連接。記住這里沒有說SQL Server所提供的，是因為此連接算法是市面所有的RDBMS所共同使用的一種連接算法。

合并連接是依次讀取兩張表的一行進行對比。如果兩個行是相同的，則輸出一個連接后的行并繼續下一行的讀取。如果行是不相同的，則舍棄兩個輸入中較少的那個并繼續讀取，一直到兩個表中某一個表的行掃描結束，則執行完畢，所以該算法執行只會產生每張表一次掃描，并且不需要整張表掃描完就可以停止。

該算法要求按照兩張表進行依次掃描對比，但是有兩個前提條件：1、必須預先將兩張表的對應列進行排序；2、對兩張表進行合并連接的條件必須存在等值連接。

我們可以通過以下代碼進行理解

get first row R1 from input1get first row R2 from input2while not at the end of either inputbegin     if R1 joins with R2         begin              output(R1,R2)              get next row R2 from input2         end     else if R1<R2                get next row R1 from input1          else             get next row R2 from input2end              

合并連接運算符總的消耗是和輸入表中的行數成正比的，而且對表最多讀取一次，這個和嵌套循環連接不一樣。因此，合并連接對于大表的連接操作是一個比較好的選擇項。

對于合并連接可以從如下幾點提高性能：

1. 兩張表間的連接值內容列類型，如果兩張表中的關聯列都為唯一列，也就說都不存在重復值，這種關聯性能是最好的，或者有一張表存在唯一列也可以，這種方式關聯為一對多關聯方式，這種方式也是我們最常用的，比我們經常使用的主從表關聯查詢；如果兩張表中的關聯列存在重復值，這樣在兩表進行關聯的時候還需要借助第三張表來暫存重復的值，這第三張表叫做”worktable “是存放在Tempdb或者內存中，而這樣性能就會有所影響。所以鑒于此，我們常做的優化方式有：關聯連盡量采用聚集索引（唯一性）
2. 我們知道采用該種算法的前提是，兩張表都經過排序，所以我們在應用的時候，最好優先使用排序后的表關聯。如果沒有排序，也要選擇的關聯項為索引覆蓋項，因為大表的排序是一個很耗資源的過程，我們選擇索引覆蓋列進行排序性能要遠遠好于普通列的排序。

我們來舉個例子

SELECT O.CustomerID,C.CustomerID,C.ContactName FROM Orders O JOIN Customers CON O.CustomerID=C.CustomerID

我們知道這段T-SQL語句中關聯項用的是CustomerID，而此列為主鍵聚集索引，都是唯一的并且經過排序的，所以這里面沒有顯示的排序操作。

而且凡是采用合并連接的所有輸出結果項，都是已經經過排序的。

我們找一個稍復雜的情況，沒有提前排序的利用合并查詢的T-SQL

SELECT O.OrderID,C.CustomerID,C.ContactNameFROM Orders O JOIN Customers CON O.CustomerID=C.CustomerID AND O.ShipCity<>C.CityORDER BY C.CustomerID

上述代碼返回那些客戶的發貨訂單不在客戶本地的。

上面的查詢計劃可以看出，排序的消耗總是巨大的，其實我們上面的語句按照邏輯應該是在合并連接獲取數據后，才采用顯示的按照CustomerID進行排序。

但是因為合并連接運算符之前本身就需要排序，所以此處SQL Server采取了優先排序的策略，把排序操作提前到了合并連接之前進行，并且在合并連接之后，就不需要在做額外的排序了。

這其實這里我們要求對查詢結果排序，正好也利用了合并連接的特點。

c、哈希連接(hash join)

我們分析了上面的兩種連接算法，兩種算法各有特點，也各有自己的應用場景：嵌套循環連接適合于相對小的數據集連接，合并連接則應對與中型的數據集，但是又有它自己的缺點，比如要求必須有等值連接，并且需要預先排序等。

那對于大型的數據集合的連接數據庫是怎么應對的呢？那就是哈希連接算法的應用場景了。

哈希連接對于大型數據集合的并行操作上都比其它方式要好很多，尤其適用于OLAP數據倉庫的應用場景中。

哈希連接很多地方和合并連接類似，比如都需要至少一個等值連接，同樣支持所有的外連接操作。但不同于合并連接的是，哈希連接不需要預先對輸入數據集合排序，我們知道對于大表的排序操作是一個很大的消耗，所以去除排序操作，哈希操作性能無疑會提升很多。

哈希連接在執行的時候分為兩個階段：

• 構建階段

在構建階段，哈希連接從一個表中讀入所有的行，將等值連接鍵的行機型哈希話處理，然后創建形成一個內存哈希表，而將原來列中行數據依次放入不同的哈希桶中。

• 探索階段

在第一個階段完成之后，開始進入第二個階段探索階段，該階段哈希連接從第二個數據表中讀入所有的行，同樣也是在相同的等值連接鍵上進行哈希。哈希過程桶上一階段，然后再從哈希表中探索匹配的行。

上述的過程中，在第一個階段的構建階段是阻塞的，也就是說在，哈希連接必須讀入和處理所有的構建輸入，之后才能返回行。而且這一過程是需要一塊內存存儲提供支持，并且利用的是哈希函數，所以相應的也會消耗CPU等。

并且上述流程過程中一般采用的是并發處理，充分利用資源，當然系統會對哈希的數量有所限制，如果數據量超大，也會發生內存溢出等問題，而對于這些問題的解決，SQL Server有它自身的處理方式。

我們可通過以下代碼進行理解

--構建階段for each row R1 in the build tablebegin   calculate hash value on R1 join key(s)   insert R1 into the apPRopriate hash bucketend--探索階段for each row R2 in the probe tablebegin   calculate hash value on R2 join key(s)      for each row R1 in the corresponding hash bucket       if R1 joins with R2          output(R1,R2)end    

在哈希連接執行之前，SQL Server會估算需要多少內存來構建哈希表。基本估算的方式就是通過表的統計信息來估算，所以有時候統計信息不準確，會直接影響其運算性能。

SQL Server默認會盡力預留足夠的內存來保證哈希連接成功的構建，但是有時候內存不足的情況下，就必須采取將一小部分的哈希表分配到硬盤中，這里就存入到了tempdb庫中，而這一過程會反復多次循環執行。

舉個列子來看看

SELECT O.OrderID,O.OrderDate,C.CustomerID,C.ContactNameFROM Orders O JOIN Customers CON O.CustomerID=C.CustomerID

我們來分析上面的執行語句，上面的執行結果通過CustomerID列進行關聯，理論將最合適的應該是采用合并連接操作，但是合并連接需要排序，但是我們在語句中沒有指定Order by 選項，所以經過評估，此語句采用了哈希連接的方式進行了連接。

我們給它加上一個顯示的排序，它就選用合并連接作為最優的連接方式

我們來總結一下這個算法的特點

• 和合并連接一樣算法復雜度基本就是分別遍歷兩邊的數據集各一遍
• 它不需要對數據集事先排序，也不要求上面有什么索引，通過的是哈希算法進行處理
• 基本采取并行的執行計劃的方式

但是，該算法也有它自身的缺點，因為其利用的是哈希函數，所以運行時對CPU消耗高，同樣對內存也比較大，但是它可以采用并行處理的方式，所以該算法用于超大數據表的連接查詢上顯示出自己獨有的優勢。

關于哈希算法在哈希處理過程的時候對內存的占用和分配方式，是有它自己獨有哈希方法，比如：左深度樹、右深度樹、濃密哈希連接樹等，這里不做詳細介紹了，只需要知道其使用方式就可以了。

Hash Join并不是一種最優的連接算法，只是它對輸入不優化，因為輸入數據集特別大，并且對連接符上有沒有索引也沒要求。其實這也是一種不得已的選擇，但是該算法又有它適應的場景，尤其在OLAP的數據倉庫中，在一個系統資源相對充足的環境下，該算法就得到了它發揮的場景。

當然前面所介紹的兩種算法也并不是一無是處，在業務的OLTP系統庫中，這兩種輕量級的連接算法，以其自身的優越性也獲得了認可。