舉個例子來說明問題，假設(shè)某個表T有索引 ( cityid, sentdate, userid), 現(xiàn)在有個分頁列表功能，要獲得大于某個多列復(fù)合索引V0的若干個記錄的查詢，用最簡單表意的方式寫出來就是 V >= V0, 如果分解開來，就是:
cityid > @cityid0 or (cityid = @cityid0 and (sentdate > @sentdate0 or (sentdate = @sentdate0 and userid >= @userid0))), 當(dāng)你寫出上述查詢時，你會期待sql server會自動的把上述識別為V >= V0類型的邊界條件，并使用index seek操作來實施該查詢。然而，微軟的sql server (2005版)有一個重要缺陷(其他的sql server如何還不得知), 當(dāng)它遇到這樣sql時，sql server就會采用index scan來實施，結(jié)果是您建立好的索引根本就沒有被使用，如果這個表的數(shù)據(jù)量很大，那所造成的性能下降是非常大的。
對于這個問題，我曾經(jīng)提交給微軟的有關(guān)人士，他們進一步要求我去一個正式的網(wǎng)站上去提交這個缺陷，我懶得去做。 不過，對這個缺陷，還是有個辦法能夠繞過去的，只要把上面給出的條件變變形，sql server還是能夠變回到是用index seek, 而不是低性能的index scan. 具體請看我的英文原文吧(對不起了, 我一旦寫了中文,就不想翻成英文,反過來也一樣, 估計大家英文都還可以,實在不行的就看黑體部分吧, )：
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.
上面的問題，可以說是部分的繞過去了，但是，也有繞不過的時候，接著看下面一段:
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), 上面兩個查詢實質(zhì)相同(表中的數(shù)據(jù)剛好如此)，并且給出同業(yè)的結(jié)果集，但是，3比4的速度要快的多，如果去看execution plan也證明3確實應(yīng)當(dāng)比4快.
也就是說, 即使在索引vectorIndex只含兩列的情況下, sql server也無法正確的理解范圍表達式 @vectorIndex0 < vectorIndex < @vectorIndex1, 它能把前半部分正確的解讀為seek, 但是, 后半部分無法正確解讀, 導(dǎo)致, sql server會一直掃描到整個表的末尾, 而不是在@vectorIndex1處停下來.
以下測試代碼, 有興趣的人可以拿去自己玩:

版權(quán)聲明：本站文章來源標注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。