問題

看看下面的簡單的query語句，可能你已經(jīng)在你看到過幾百次了

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

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

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

 

解決方案

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

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

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

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

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

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

 

總結(jié)

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

 

譯后記

這也是我在看微軟SQL Server認證考試Exam70-461的TrainingKit的時候，它書里面反復強調(diào)的。簡單來講就是保證不要直接用函數(shù)作用在做過index的列上，要用函數(shù)的話，變通到表達式的右側(cè)來。至于為什么會影響性能。因為我對index還不熟悉，我理解的不是很清晰。

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

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