多列複合索引的使用 繞過微軟sql server的一個缺陷

然而，微軟sql server在處理這類索引時，有個重要的缺陷，那就是把本該編譯成索引seek的操作編成了索引掃描，這可能導致嚴重性能下降

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

當你寫出上述查詢時，你會期待sql server會自動的把上述識別為V >= V0類型的邊界條件，並使用index seek操作來實施該查詢。然而，微軟的sql server (2005版)有一個重要缺陷(其他的sql server如何還不得知), 當它遇到這樣sql時，sql server就會採用index scan來實施，結果是您建立好的索引根本就沒有被使用，如果這個表的資料量很大，那所造成的效能下降是非常大的。
對於這個問題，我曾經提交給微軟的有關人士，他們進一步要求我去一個正式的網站上去提交這個缺陷，我懶得去做。

不過，對這個缺陷，還是有個辦法能夠繞過去的，只要把上面給出的條件變變形，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),

上面兩個查詢實質相同(表中的資料剛好如此)，並且給出同業的結果集，但是，3比4的速度要快的多，如果去看execution plan也證明3確實應當比4快.
也就是說, 即使在索引vectorIndex只含兩列的情況下, sql server也無法正確的理解範圍運算式 @vectorIndex0 < vectorIndex < @vectorIndex1, 它能把前半部分正確的解讀為seek, 但是, 後半部分無法正確解讀, 導致, sql server會一直掃描到整個表的末尾, 而不是在@vectorIndex1處停下來.
以下測試代碼, 有興趣的人可以拿去自己玩:

複製代碼 代碼如下:CREATE TABLE [dbo].[A](
[a] [int] NOT NULL,
[b] [int] NOT NULL,
[c] [int] NOT NULL,
[d] [float] NULL,
PRIMARY KEY CLUSTERED ([a] ASC, [b] ASC, [c] ASC)
)
declare @a int, @b int, @c int
set @a =1
while @a <= 100
begin
set @b = 1
begin tran
while @b <= 100
begin
set @c = 1
while @c <= 100
begin
INSERT INTO A (a, b, c, d)
VALUES (@a,@b,@c,@a+@b+@c)
set @c = @c + 1
end
set @b = @b + 1
end
commit
set @a = @a + 1
end
SET STATISTICS PROFILE ON
SET STATISTICS time ON
SET STATISTICS io ON

select top (10) a, b, c, d from A where (a> 60 or a= 60 and
(b > 20 or b = 20 and c >= 31))
select a, b, c, d from A where (a> 60 or a= 60 and
(b > 20 or b = 20 and c >= 31)) and (a< 60 or a= 60 and
(b < 20 or b = 20 and c <= 40))

select top (10) a, b, c, d from A where a> 60 or a= 60 and b > 20 or a= 60 and b= 20 and c >= 31
select a, b, c, d from A where (a> 60 or a= 60 and b > 20 or a= 60 and b= 20 and c >= 31) and
(a< 60 or a= 60 and b < 20 or a= 60 and b= 20 and c <= 40)
select top (100) a, b, c, d from A where a> 60 or a= 60 and b > 20
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)
select top (100) a, b, c, d from A where a> 60 or a= 60 and b > 20
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)