然而，微軟sql server在處理這類索引時(shí)，有個(gè)重要的缺陷，那就是把本該編譯成索引seek的操作編成了索引掃描，這可能導(dǎo)致嚴(yán)重性能下降

舉個(gè)例子來說明問題，假設(shè)某個(gè)表T有索引 ( cityid, sentdate, userid), 現(xiàn)在有個(gè)分頁列表功能，要獲得大于某個(gè)多列復(fù)合索引V0的若干個(gè)記錄的查詢，用最簡單表意的方式寫出來就是 V >= V0, 如果分解開來，就是:
cityid > @cityid0 or (cityid = @cityid0 and (sentdate > @sentdate0 or (sentdate = @sentdate0 and userid >= @userid0))),

當(dāng)你寫出上述查詢時(shí)，你會(huì)期待sql server會(huì)自動(dòng)的把上述識(shí)別為V >= V0類型的邊界條件，并使用index seek操作來實(shí)施該查詢。然而，微軟的sql server (2005版)有一個(gè)重要缺陷(其他的sql server如何還不得知), 當(dāng)它遇到這樣sql時(shí)，sql server就會(huì)采用index scan來實(shí)施，結(jié)果是您建立好的索引根本就沒有被使用，如果這個(gè)表的數(shù)據(jù)量很大，那所造成的性能下降是非常大的。
對(duì)于這個(gè)問題，我曾經(jīng)提交給微軟的有關(guān)人士，他們進(jìn)一步要求我去一個(gè)正式的網(wǎng)站上去提交這個(gè)缺陷，我懶得去做。

不過，對(duì)這個(gè)缺陷，還是有個(gè)辦法能夠繞過去的，只要把上面給出的條件變變形，sql server還是能夠變回到是用index seek, 而不是低性能的index scan. 具體請(qǐng)看我的英文原文吧(對(duì)不起了, 我一旦寫了中文,就不想翻成英文,反過來也一樣, 估計(jì)大家英文都還可以,實(shí)在不行的就看黑體部分吧, )：
The seek predicate of the form "x > bookmark_of_x" is needed in paging related query. The compiler has no difficulty to parse it correctly if x is a single column index, or two columns index, however, if x is a three columns index or more, then the compiler will have a hard time to recognize it. This failure will result in that the seek predicate ended up in residue predicate, which results in a much worse execution plan.
To illustrate the point, take a example,
Create table A( a int, b int, c int, d float, primary key (a, b, c))
now check the plan for the query:
select c, d from A where (a> 111 or a= 111 and
(b > 222 or b = 222 and c > 333))
you can see a table scan op is used, and the Where clause ended up in residue predicate.
However, if you rewrite the query in an equivalent form:
select c, d from A where a> 111 or a= 111 and b > 222 or a= 111 and b= 222 and c >333
Then the compiler can choose an index seek op, which is desired.
The problem is, the compiler should be able to recognize the first form of seek predicate on multiple columns index, it saves the user from having to pay extra time to figure out a get-around, not to mention the first form is a more efficient form of same expression.
上面的問題，可以說是部分的繞過去了，但是，也有繞不過的時(shí)候，接著看下面一段:
It looks like that sql server lacks a consept of vector bookmark, or vector comparison or whatever you like to call it.
The workaround is not a perfect workaround. If sql server were to understand the concept of vector bookmark, then the following two would be the same in execution plan and performance:
1. select top(n) * from A where vectorIndex >= @vectorIndex
2. select * from A where vectorIndex >= @vectorIndex and vectorIndex <=@vectorIndexEnd
-- @vectorIndexEnd corresponds to the last row of 1.
However, test has shown that, the second statement takes far more time than the first statement, and sql server actually only seek to the begining of the vector range and scan to the end of the whole Index, instead of stop at the end of the vector range.
Not only sql server compile badly when the vector bookmark has 3 columns, test has shown that even with as few as 2 columns, sql serer still can not correctly recognize this is actually a vector range, example:
3. select top (100) a, b, c, d from A where a> 60 or a= 60 and b > 20
4. select a, b, c, d from A where (a> 60 or a= 60 and b > 20) and
(a< 60 or a= 60 and b <= 21),

上面兩個(gè)查詢實(shí)質(zhì)相同(表中的數(shù)據(jù)剛好如此)，并且給出同業(yè)的結(jié)果集，但是，3比4的速度要快的多，如果去看execution plan也證明3確實(shí)應(yīng)當(dāng)比4快.
也就是說, 即使在索引vectorIndex只含兩列的情況下, sql server也無法正確的理解范圍表達(dá)式 @vectorIndex0 < vectorIndex < @vectorIndex1, 它能把前半部分正確的解讀為seek, 但是, 后半部分無法正確解讀, 導(dǎo)致, sql server會(huì)一直掃描到整個(gè)表的末尾, 而不是在@vectorIndex1處停下來.
以下測試代碼, 有興趣的人可以拿去自己玩:

最新評(píng)論