日期：2012-07-30 14:59:00 来源：本站整理

多列复合索引的利用绕过微软sql server的一个缺陷[MSSQL防范]

赞助商链接

分享到： QQ空间新浪微博腾讯微博人人网

　　本文“多列复合索引的利用绕过微软sql server的一个缺陷[MSSQL防范]”是由七道奇为您精心收集，来源于网络转载，文章版权归文章作者所有，本站不对其观点以及内容做任何评价，请读者自行判断，以下是其具体内容：
但是,微软 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)

　　以上是“多列复合索引的利用绕过微软sql server的一个缺陷[MSSQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：

多列复合索引的利用绕过微软sql server的一个缺陷

本文地址：

与您的QQ/BBS好友分享!

[] [返回上一页] [打印]