SQL Server調優系列基礎篇（索引運算總結）
前言 上幾篇文章我們介紹了如何查看查詢計劃、常用運算符的介紹、并行運算的方式，有興趣的可以點擊查看。 本篇將分析在SQL Server中，如何利用先有索引項進行查詢性能優化，通過了解這些索引項的應用方式可以指導我們如何建立索引、調整我們的查詢語句，達到性能優化的目的。 閑言少敘，進入本篇的正題。
技術準備 基于SQL Server2008R2版本，利用微軟的一個更簡潔的案例庫（Northwind）進行解析。
簡介 所謂的索引應用就是在我們日常寫的T-SQL語句中，如何利用現有的索引項，再分析的話就是我們所寫的查詢條件，其實大部分情況也無非以下幾種：
1、等于謂詞：select …where…column=@parameter
2、比較謂詞：select …where…column> or < or <> or <= or >= @parameter
3、范圍謂詞：select …where…column in or not in or between and @parameter
4、邏輯謂詞：select …where…一個謂詞 or、and 其它謂詞 or、and 更多謂詞…. 我們就依次分析上面幾種情況下，如何利用索引進行查詢優化的
一、動態索引查找
所謂的動態索引查找就是SQL Server在執行語句的時候，才格式化查詢條件，然后根據查詢條件的不同自動的去匹配索引項，達到性能提升的目的。 來舉個例子
SET SHOWPLAN_TEXT ON GO SELECT OrderID FROM Orders WHERE ShipPostalCode IN (N'05022',N'99362')
因為我們在表Orders的列ShipPostalCode列中建立了非聚集索引列，所以這里查詢的計劃利用了索引查找的方式。這也是需要建立索引的地方。 我們來利用文本的方式來查看該語句的詳細的執行計劃腳本，語句比較長，我用記事本換行，格式化查看 
我們知道這張表的該列里存在一個非聚集索引，所以在查詢的時候要盡量使用，如果通過索引掃描的方式消耗就比價大了，所以SQL Server盡量想采取索引查找的方式，其實IN關鍵字和OR關鍵字邏輯是一樣的。
于是上面的查詢條件就轉換成了：
[Northwind].[dbo].[Orders].[ShipPostalCode]=N’05022′
OR
[Northwind].[dbo].[Orders].[ShipPostalCode]=N’99362′
這樣就可以采用索引查找了，先查找第一個結果，然后再查找第二個，而這個過程在SQL Server中就被稱為：動態索引查找。
是不是有點智能的感覺了….
所以有時候我們寫語句的時候，盡量要使用SQL Server的這點智能了，讓其能自動的查找到索引，提升性能。
有時候偏偏我們寫的語句讓SQL Server的智能消失，舉個例子：
--參數化查詢條件 DECLARE @Parameter1 NVARCHAR(20),@Parameter2 NVARCHAR(20) SELECT @Parameter1=N'05022',@Parameter2=N'99362' SELECT OrderID FROM Orders WHERE ShipPostalCode IN (@Parameter1,@Parameter2)
我們將這兩個靜態的篩序值改成參數，有時候我們寫的存儲過程灰常喜歡這么做！我們來看這種方式的生成的查詢計劃 
本來很簡單的一個非聚集索引查找搞定的執行計劃，我們只是將這兩個數值沒有直接寫入IN關鍵字中，而是利用了兩個變量來代替。
看看上面SQL Server生成的查詢計劃！尼瑪…這都是些啥？？？還用起來嵌套循環，我就查詢了一個Orders表…你嵌套循環個啥….上面動態索引查找的能力去哪了？？？ 好吧，我們用文本查詢計劃來查看下，這個簡單的語句到底在干些啥…
|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1009], [Expr1010], [Expr1011]))
|--Merge Interval
| |--Sort(TOP 2, ORDER BY:([Expr1012] DESC, [Expr1013] ASC, [Expr1009] ASC, [Expr1014] DESC))
| |--Compute Scalar(DEFINE:([Expr1012]=((4)&[Expr1011]) = (4) AND NULL = [Expr1009], [Expr1013]=(4)&[Expr1011], [Expr1014]=(16)&[Expr1011]))
| |--Concatenation
| |--Compute Scalar(DEFINE:([Expr1004]=[@Parameter2], [Expr1005]=[@Parameter2], [Expr1003]=(62)))
| | |--Constant Scan
| |--Compute Scalar(DEFINE:([Expr1007]=[@Parameter1], [Expr1008]=[@Parameter1], [Expr1006]=(62)))
| |--Constant Scan
|--Index Seek(OBJECT:([Northwind].[dbo].[Orders].[ShipPostalCode]), SEEK:([Northwind].[dbo].[Orders].[ShipPostalCode] > [Expr1009] AND [Northwind].[dbo].[Orders].[ShipPostalCode] < [Expr1010]) ORDERED FORWARD)挺復雜的是吧，其實我分析了一下腳本，關于為什么會生成這個計劃腳本的原因，是為了解決如下幾個問題：
1、前面我們寫的腳本在IN里面寫的是兩個常量值，并且是不同的值，所以形成了兩個索引值的查找通過OR關鍵字組合， 這種方式貌似沒問題，但是我們將這兩個數值變成了參數，這就引來了新的問題，假如這兩個參數我們輸入的是相等的，那么利用前面的執行計劃就會生成如下
[Northwind].[dbo].[Orders].[ShipPostalCode]=N’05022′
OR
[Northwind].[dbo].[Orders].[ShipPostalCode]=N’05022′
這樣執行產生的輸出結果就是2條一樣的輸出值！…但是表里面確實只有1條數據…所以這樣輸出結果不正確！ 所以變成參數后首先解決的問題就是去重問題，2個一樣的變成1個。
2、上面變成參數，還引入了另外一個問題，加入我們兩個值有一個傳入的為Null值，或者兩個都為Null值，同樣輸出結果面臨著這樣的問題。所以這里還要解決的去Null值的問題。 為了解決上面的問題，我們來粗略的分析一下執行計劃，看SQL Server如何解決這個問題的 
簡單點將就是通過掃描變量中的值，然后將內容進行匯總值，然后在進行排序，再將參數中的重復值去掉，這樣獲取的值就是一個正確的值，最后拿這些去重后的參數值參與到嵌套循環中，和表Orders進行索引查找。
但是分析的過程中，有一個問題我也沒看明白，就是最好的經過去重之后的常量匯總值，用來嵌套循環連接的時候，在下面的索引查找的時候的過濾條件變成了 and 查找 
我將上面的最后的索引查找條件，整理如下：
|–Index Seek(OBJECT:([Northwind].[dbo].[Orders].[ShipPostalCode]), SEEK:
(
[Northwind].[dbo].[Orders].[ShipPostalCode] > [Expr1009]
AND
[Northwind].[dbo].[Orders].[ShipPostalCode] < [Expr1010]
) ORDERED FORWARD)
這個地方怎么搞的？我也沒弄清楚，還望有看明白童鞋的稍加指導下….
好了，我們繼續
上面的執行計劃中，提到了一個新的運算符：合并間隔（merge interval operator）
我們來分析下這個運算符的作用，其實在上面我們已經在執行計劃的圖中標示出該運算符的作用了，去掉重復值。
其實關于去重的操作有很多的，比如前面文章中我們提到的各種去重操作。
這里怎么又冒出個合并間隔去重？其實原因很簡單，因為我們在使用這個運算符之前已經對結果進行了排序操作，排序后的結果項重復值是緊緊靠在一起的，所以就引入了合并間隔的方式去處理，這樣性能是最好的。
更重要的是合并間隔這種運算符應用場景不僅僅局限于重復值的去除，更重要的是還應用于重復區間的去除。 來看下面的例子
--參數化查詢條件 DECLARE @Parameter1 DATETIME,@Parameter2 DATETIME SELECT @Parameter1='1998-01-01',@Parameter2='1998-01-04' SELECT OrderID FROM ORDERS WHERE OrderDate BETWEEN @Parameter1 AND DATEADD(DAY,6,@Parameter1) OR OrderDate BETWEEN @Parameter2 AND DATEADD(DAY,6,@Parameter2)
我們看看這個生成的查詢計劃項 
可以看到，SQL Server為我們生成的查詢計劃，和前面我們寫的語句是一模一樣的，當然我們的語句也沒做多少改動，改動的地方就是查詢條件上。
我們來分析下這個查詢條件：
WHERE OrderDate BETWEEN @Parameter1 AND DATEADD(DAY,6,@Parameter1)
OR OrderDate BETWEEN @Parameter2 AND DATEADD(DAY,6,@Parameter2)
很簡單的篩選條件，要獲取訂單日期在1998-01-01開始到1998-01-07內的值或者1998-01-04開始到1998-01-10內的值（不包含開始日期）
這里用的邏輯謂詞為：OR…其實也就等同于我們前面寫的IN
但是我們這里再分析一下，你會發現這兩個時間段是重疊的 
這個重復的區間值，如果用到前面的直接索引查找，在這段區間之內的搜索出來的范圍值就是重復的，所以為了避免這種問題，SQL Server又引入了“合并間隔”這個運算符。
其實，經過上面的分析，我們已經分析出這種動態索引查找的優缺點了，有時候我們為了避免這種復雜的執行計劃生成，使用最簡單的方式就是直接傳值進入語句中（當然這里需要重編譯），當然大部分的情況我們寫的程序都是只定義的參數，然后進行的運算。可能帶來的麻煩就是上面的問題，當然有時候參數多了，為了合并間隔所應用的排序就消耗的內存就會增長。怎么使用，根據場景自己酌情分析。
二、索引聯合 所謂的索引聯合，就是根據就是根據篩選條件的不同，拆分成不同的條件，去匹配不同的索引項。 舉個例子
SELECT OrderID FROM ORDERS WHERE OrderDate BETWEEN '1998-01-01' AND '1998-01-07' OR ShippedDate BETWEEN '1998-01-01' AND '1998-01-07'
這段代碼是查詢出訂單中的訂單日期在1998年1月1日到1998年1月7日的或者發貨日期同樣在1998年1月1日到1998年1月7日的。
邏輯很簡單，我們知道在這種表里面這兩個字段都有索引項。所以這個查詢在SQL Server中就有了兩個選擇：
1、一次性的來個索引掃描根據匹配結果項輸出，這樣簡單有效，但是如果訂單表數據量比較大的話，性能就會很差，因為大部分數據就根本不是我們想要的，還要浪費時間去掃描。
2、就是通過兩列的索引字段直接查找獲取這部分數據，這樣可以直接減少數據表的掃描量，但是帶來的問題就是，如果分開掃描，有一部分數據就是重復的：那些同時在1998年1月1日到1998年1月7日的訂單，發貨日期也在這段時間內，因為兩個掃描項都包含，所以再輸出的時候需要將這部分重復數據去掉。
我們來看SQL Server如何選擇
看來SQL Server經過評估選擇了第2中方法。但是上面的方法也不盡完美，采用去重操作耗費了64%的資源。
其實，上面的方法，我們根據生成的查詢計劃可以變通的使用以下邏輯，其效果和上面的語句是一樣的，并且生成的查詢計劃也一樣
SELECT OrderID FROM ORDERS WHERE OrderDate BETWEEN '1998-01-01' AND '1998-01-07' UNION SELECT OrderID FROM ORDERS WHERE ShippedDate BETWEEN '1998-01-01' AND '1998-01-07'
我們再來看一個索引聯合的例子
SELECT OrderID FROM ORDERS WHERE OrderDate = '1998-01-01' OR ShippedDate = '1998-01-01'
我們將上面的Between and不等式篩選條件改成等式篩選條件，我們來看一下這樣形成的執行計劃
基本相同的語句，只是我們改變了不同的查詢條件，但是生成的查詢計劃還是變化蠻大的，有幾點不同之處：
1、前面的用between…and 的篩選條件，通過索引查找返回的值進行組合是用的串聯的方式，所謂的串聯就是兩個數據集拼湊在一起就行，無所謂順序連接什么的。
2、前面的用between…and 的篩選條件，通過串聯拼湊的結果集去重的方式，是排序去重（Sort Distinct）…并且耗費了大量的資源。這里采用了流聚合來干這個事，基本不消耗
我們來分析以下產生著兩點不同的原因有哪些：
首先、這里改變了篩選條件為等式連接，所通過索引查找所產生的結果項是排序的，并且按照我們所要查詢的OrderID列排序，因此在兩個數據集進行匯總的時候，正適合合并連接的條件！需要提前排序。所以這里最優的方式就是采用合并連接！
那么前面我們用between…and 的篩選條件通過索引查找獲取的結果項也是排序的，但是這里它沒有按照OrderID排序，它是按照OrderDate或者ShippedDate列排序的，而我們的結果是要OrderID列，所以這里的排序是沒用的……所以SQL Server只能選擇一個串聯操作，將結果匯聚到一起，然后在排序了……我希望這里我已經講明白了…
其次、關于去重操作，毫無疑問采用流聚合（Aggregate）這種方式最好，消耗內存少，速度又快…但是前提是要提前排序…前面選用的排序去重（Sort Distinct）純屬無奈之舉…
總結下：我們在寫語句的時候能確定為等式連接，最好采用等式連接。還有就是如果能確定輸出條件的最好能寫入，避免多余的書簽查找，還有萬惡的SELEECT *….
如果寫了萬惡的SELECT *…那么你所寫的語句基本上就可以和非聚集索引查找告別了….頂多就是聚集索引掃描或者RID查找…
瞅瞅以下語句
SELECT * FROM ORDERS WHERE OrderDate = '1998-01-01' OR ShippedDate = '1998-01-01'
最后，奉上一個AND的一個連接謂詞的操作方式，這個方式被稱為：索引交叉，意思就是說如果兩個或多個篩選條件如果采用的索引是交叉進行的，那么使用一個就可以進行查詢。 來看個語句就明白了
SELECT OrderID FROM ORDERS WHERE OrderDate = '1998-01-01' AND ShippedDate = '1998-03-05'
這里我們采用了的謂詞連接方式為AND，所以在實際執行的時候，雖然兩列都存在非聚集索引，理論都可以使用，但是我們只要選一個最優的索引進行查找，另外一個直接使用書簽查找出來就可以。省去了前面介紹的各種神馬排序去重….流聚合去重….等等不人性的操作。 看來AND連接符是一個很帥的運算符…所以很多時候我們在嘗試寫OR的情況下，不如換個思路改用AND更高效。 參考文獻
- 微軟聯機叢書邏輯運算符和物理運算符引用
- 參照書籍《SQL.Server.2005.技術內幕》系列
結語 此篇文章主要介紹了索引運算的一些方式，主要是描述了我們平常在寫語句的時候所應用的方式，并且舉了幾個例子，算作拋磚引玉吧，其實我們平常所寫的語句中無非也就本篇文章中介紹的各種方式的更改，拼湊。而且根據此，我們該怎樣建立索引也作為一個指導項。 下一篇我們介紹子查詢一系列的內容，有興趣可提前關注，關于SQL Server性能調優的內容涉及面很廣，后續文章中依次展開分析。 有問題可以留言或者私信，隨時恭候有興趣的童鞋加入SQL SERVER的深入研究。共同學習，一起進步。
原文出處： 指尖流淌-吳學雷的博客