多列复合索引的使用 绕过微软sql server的一个缺陷
来源:asp之家 发布时间:2012-08-21 10:37:36
然而,微软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)
猜你喜欢
- 在存储过程中,请问如何判断所指定表的字段的类型?由于表和视图的每一列在SYSCOLUMNS表中都有详细资料,所以,我们只管从中取用来即可,很
- 今天偶尔在一个学习网站技术的地方看到一个教程,关于html代码的,刚看到咱常用到的视频播放器html标签Object,平时用到他的时候都是为
- 现在越来越多的浏览器有拦截弹出窗口的功能。广告弹出来给拦掉了就无所谓,要是客户在付款时给拦掉了可就不能乱算了。Gmail的“哎呀”算是经典,
- 内容摘要:有很多朋友虽然安装好了mysql但却不知如何使用它。在这篇文章中我们就从连接mysql、修改密码、增加用户等方面来学习一些mysq
- 有过网页制作经验的朋友一定知道:一般来讲,把表格的边框定义为1px时,即border=1,而实际上是2
- 概要说明:自动更新程序主要负责从服务器中获取相应的更新文件,并且把这些文件下载到本地,替换现有的文件。达到修复Bug,更新功能的目的。本文作
- MySQL使用于认证目的的用户名,与Unix用户名(登录名字)或Windows用户名无关。缺省地,大多数MySQL客户尝试使用当前Unix用
- 通常我们会在一些javascript的书籍上看到,使用Javascript保留字作为标识符(变量名、函数名、循环标记等)时,会引起程序报错!
- 今天看了篇关于Web Form Design的成功案例,虽然讲的事情很简单,但总结了一些方法,翻译过来做个原始积累吧,以后写东西举例子时也好
- 在Dreamweaver 4.0中,我们就已接触了模板与库的概念,知道它们是批量生成风格类似的网页的好工具。如今在Dreamweaver M
- WEB程序员的技能要求: Web程序员不光要对 后端程序(如 php,jsp,asp.net)语言知识,对程序设计架构知识,数据库
- DIV与TABLE本身并不存在什么优缺点,所谓web标准只是推荐的是正确的使用标签,好比说:DIV用于布局,而TABLE则本来就是转二维数据
- CSS布局作为一个热门技术,发展的确有些缓慢。CSS最早被提议在1994年,最早被浏览器支持在1996年,CSS一直被力捧为传统的以HTML
- 本文主要介绍了一个获取SQL Server数据字典的经典SQL语句,大家可以根据各自的实际情况对这段语句进行相应的修改。SELECT sys
- 有一个表,用户需要在后台操作它,希望能对它动态进行添加删除字段。这个功能也许没有问题,但是它原有插入与更新的两个存储过程,也需要一起修改。因
- 50个常用sql语句 Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cname,T#) 课程表 SC(
- 【代码示例】 [code=SQL] DELIMITER $$ DROP FU
- JavaScript 中的并没有提供像 VBScript 里的 DateAdd 方法用于日
- IE>6 及其它浏览器不在此文论述范围内。img 的 onload 事件,是当图片加载完成之后,就处触发。img.complete 这
- 现在使用CSS网页布局,摒弃了传统Table表格布局的模式,但是Table表格在网页中还是少不了的,因为当需要输出表格类数据时,就应该使用表