通過(guò)使用正確的search arguments來(lái)提高SQL Server數(shù)據(jù)庫(kù)的性能

原文地址：http://www.sqlpassion.at/archive/2014/04/08/improving-query-performance-by-using-correct-search-arguments/

今天的文章給大家談?wù)勗赟QL Server上關(guān)于indexing的一個(gè)特定的性能問(wèn)題。

問(wèn)題

看看下面的簡(jiǎn)單的query語(yǔ)句，可能你已經(jīng)在你看到過(guò)幾百次了

-- Results in an Index Scan
SELECT * FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = 2005 AND MONTH(OrderDate) = 7
GO

上門的代碼查詢一個(gè)銷售信息，需要一個(gè)特定的月份和年份的，這不是很復(fù)雜。但是不幸的的事，這個(gè)qeury的效率不行，即使OrderDate這一列已經(jīng)做了Non-Clustered Index?？梢钥纯聪旅娴膓eury執(zhí)行圖，你能看到Query Optimizer已經(jīng)選擇了定義在列OrderDate下的Non-Clustered Index，但是SQL Server卻做了Index的一個(gè)完整掃描，而不是期待中的Seek operation。

這實(shí)際上不是SQL Server的限制，而是relational database都是這樣的。只要你對(duì)一個(gè)做了index的列（Search Argument）加了函數(shù)操作，數(shù)據(jù)庫(kù)引擎就必須再次掃描這個(gè)index，而不是去直接執(zhí)行seek operation

解決方案

為了解決上門的問(wèn)題，必須要避免在列上門直接應(yīng)該函數(shù)，比如上面的問(wèn)題可以用下面的代碼來(lái)代替

-- Results in an Index Seek
SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate >= '20050701' AND OrderDate  '20050801'
GO

我們重寫的這個(gè)query語(yǔ)句，能達(dá)到同樣的效果，不用函數(shù)MONTH了。從此query的執(zhí)行圖來(lái)看，SQL Server執(zhí)行了seek operation，在查詢的范圍內(nèi)進(jìn)行的scan。所以，如果你要在where查詢中用到函數(shù)，用到表達(dá)式的右側(cè)，來(lái)避免性能問(wèn)題。比如下面的例子。

-- Results in an Index Scan
SELECT * FROM Sales.SalesOrderHeader
WHERE CAST(CreditCardID AS CHAR(4)) = '1347'
GO

這個(gè)query會(huì)使SQL Server掃描了整個(gè)Non-Clustered Index。所以當(dāng)表變得更大的時(shí)候，這個(gè)擴(kuò)展性等各方面就很差了。如果把函數(shù)放在表達(dá)式的右側(cè)，SQL Server就能執(zhí)行seek operation了

-- Results in an Index Seek
SELECT * FROM Sales.SalesOrderHeader
WHERE CreditCardID = CAST('1347' AS INT)
GO

總結(jié)

通過(guò)今天的blog，我想你們已經(jīng)認(rèn)識(shí)到了不要在做過(guò)indexed的列上直接應(yīng)用函數(shù)，不然SQL Server會(huì)掃描你整個(gè)index，而不是做seek operation。當(dāng)你的表變得越來(lái)越大的時(shí)，你會(huì)崩潰的。

譯后記

這也是我在看微軟SQL Server認(rèn)證考試Exam70-461的TrainingKit的時(shí)候，它書里面反復(fù)強(qiáng)調(diào)的。簡(jiǎn)單來(lái)講就是保證不要直接用函數(shù)作用在做過(guò)index的列上，要用函數(shù)的話，變通到表達(dá)式的右側(cè)來(lái)。至于為什么會(huì)影響性能。因?yàn)槲覍?duì)index還不熟悉，我理解的不是很清晰。

我大概猜想如下，先記下，歡迎討論。

對(duì)某一個(gè)列做index，是不是類似對(duì)這一列的數(shù)據(jù)做一個(gè)hash映射，當(dāng)在查找這一列的數(shù)據(jù)的時(shí)候，直接可以做O(1)的操作（是不是就是它講的seek operation）。如果對(duì)這一列使用了函數(shù)，SQL Server的機(jī)制就是不會(huì)重新做一個(gè)作用了函數(shù)后的列的hash，它就簡(jiǎn)單的一個(gè)一個(gè)的比較了。是O(N)的操作了。