主頁(yè) > 知識(shí)庫(kù) > 多列復(fù)合索引的使用 繞過(guò)微軟sql server的一個(gè)缺陷

多列復(fù)合索引的使用 繞過(guò)微軟sql server的一個(gè)缺陷

熱門(mén)標(biāo)簽:400電話申請(qǐng)需要開(kāi)戶費(fèi)嗎 北京辦理400電話多少 智能語(yǔ)音外呼系統(tǒng)哪個(gè)牌子好 威海智能語(yǔ)音外呼系統(tǒng) 山西語(yǔ)音外呼系統(tǒng)價(jià)格 西安青牛防封電銷(xiāo)卡 重慶防封電銷(xiāo)機(jī)器人供應(yīng)商 南京電銷(xiāo)外呼系統(tǒng)運(yùn)營(yíng)商 溫州語(yǔ)音外呼系統(tǒng)代理
然而,微軟sql server在處理這類(lèi)索引時(shí),有個(gè)重要的缺陷,那就是把本該編譯成索引seek的操作編成了索引掃描,這可能導(dǎo)致嚴(yán)重性能下降

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

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

不過(guò),對(duì)這個(gè)缺陷,還是有個(gè)辦法能夠繞過(guò)去的,只要把上面給出的條件變變形,sql server還是能夠變回到是用index seek, 而不是低性能的index scan. 具體請(qǐng)看我的英文原文吧(對(duì)不起了, 我一旦寫(xiě)了中文,就不想翻成英文,反過(guò)來(lái)也一樣, 估計(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.
上面的問(wèn)題,可以說(shuō)是部分的繞過(guò)去了,但是,也有繞不過(guò)的時(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快.
也就是說(shuō), 即使在索引vectorIndex只含兩列的情況下, sql server也無(wú)法正確的理解范圍表達(dá)式 @vectorIndex0 vectorIndex @vectorIndex1, 它能把前半部分正確的解讀為seek, 但是, 后半部分無(wú)法正確解讀, 導(dǎo)致, sql server會(huì)一直掃描到整個(gè)表的末尾, 而不是在@vectorIndex1處停下來(lái).
以下測(cè)試代碼, 有興趣的人可以拿去自己玩:

復(fù)制代碼 代碼如下:

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)
您可能感興趣的文章:
  • 防止xss和sql注入:JS特殊字符過(guò)濾正則
  • 一個(gè)過(guò)濾重復(fù)數(shù)據(jù)的 SQL 語(yǔ)句
  • MySQL注入繞開(kāi)過(guò)濾的技巧總結(jié)
  • SQL注入中繞過(guò) 單引號(hào) 限制繼續(xù)注入
  • SQL注入繞過(guò)的技巧總結(jié)
  • 關(guān)于SQL注入繞過(guò)的一些知識(shí)點(diǎn)
  • SQL Server簡(jiǎn)單模式下誤刪除堆表記錄恢復(fù)方法(繞過(guò)頁(yè)眉校驗(yàn))
  • Mysql如何巧妙的繞過(guò)未知字段名詳解
  • SQL注入技巧之顯注與盲注中過(guò)濾逗號(hào)繞過(guò)詳析

標(biāo)簽:河源 新余 宜春 金昌 貸款群呼 黃山 濟(jì)寧 中衛(wèi)

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《多列復(fù)合索引的使用 繞過(guò)微軟sql server的一個(gè)缺陷》,本文關(guān)鍵詞  多列,復(fù)合,索引,的,使用,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無(wú)關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《多列復(fù)合索引的使用 繞過(guò)微軟sql server的一個(gè)缺陷》相關(guān)的同類(lèi)信息!
  • 本頁(yè)收集關(guān)于多列復(fù)合索引的使用 繞過(guò)微軟sql server的一個(gè)缺陷的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章