多列復(fù)合索引的使用 繞過微軟sql server的一個(gè)缺陷
舉個(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處停下來.
以下測試代碼, 有興趣的人可以拿去自己玩:
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)
相關(guān)文章
sqlserver 文件數(shù)據(jù)庫和關(guān)系數(shù)據(jù)庫的比較
本文概要地從數(shù)據(jù)格式、數(shù)據(jù)庫結(jié)構(gòu)和WEB發(fā)布數(shù)據(jù)三個(gè)方面比較了文件數(shù)據(jù)庫和關(guān)系數(shù)據(jù)庫的異同,同時(shí)差別了文件數(shù)據(jù)庫和過去存儲(chǔ)數(shù)據(jù)的文件系統(tǒng)的不同2011-10-10sqlserver還原數(shù)據(jù)庫的時(shí)候出現(xiàn)提示無法打開備份設(shè)備的解決方法(設(shè)備出現(xiàn)錯(cuò)誤或設(shè)備脫)
今天在恢復(fù)數(shù)據(jù)庫的時(shí)候,因?yàn)槭钱惖夭糠诌€原,出現(xiàn)提示 無法打開備份設(shè)備 E:\自動(dòng)備份\ufidau8xTmp\UFDATA.BAK 。設(shè)備出現(xiàn)錯(cuò)誤或設(shè)備脫,這里分享一下解決方法,需要的朋友可以參考一下2016-03-03關(guān)于SQL 存儲(chǔ)過程入門基礎(chǔ)(流程控制)
本篇文章,小編為大家介紹關(guān)于SQL 存儲(chǔ)過程入門基礎(chǔ)(流程控制),有需要的可以參考一下2013-04-04行轉(zhuǎn)列之SQL SERVER PIVOT與用法詳解
這篇文章主要給大家介紹了關(guān)于行轉(zhuǎn)列之SQL SERVER PIVOT與用法的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家學(xué)習(xí)或者使用SQL SERVER具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-09-09sqlserverdriver配置方法 jdbc連接sqlserver
這篇文章主要介紹了sqlserverdriver配置方法,介紹了連接SqlServer2000 和連接SqlServer2005的方法,大家參考使用吧2014-01-01SQL Server 2012 安裝圖解教程(附sql2012下載地址)
在安裝微軟最新數(shù)據(jù)庫SQL Server 2012之前,編者先確定一下安裝環(huán)境:Windonws 7 SP1,32位操作系統(tǒng)、CPU是2.1GHz賽揚(yáng)雙核T3500,內(nèi)存2.93GB2013-04-04Windows環(huán)境下實(shí)現(xiàn)批量執(zhí)行Sql文件
這篇文章主要介紹了Windows環(huán)境下實(shí)現(xiàn)批量執(zhí)行Sql文件的相關(guān)資料,需要的朋友可以參考下2021-10-10