SQL开发中容易忽视的一些小地方(六)
2010-04-28 15:44:08 来源:WEB开发网本文主旨:条件列上的索引对数据库delete操作的影响。
事由:今天在博客园北京俱乐部MSN群中和网友讨论了关于索引对delete的影响问题,事后感觉非常汗颜,因为我的随口导致错误连篇。大致话题是这样的,并非原话:
[讨论:] delete course where classID=500001 classID上没有创建任何索引,为了提高删除效率,如果在classID上创建一个非聚集索引会不会提高删除的效率呢?
我当时的观点:不能。
我当时的理由:数据库在执行删除时,如果在classID上创建了非聚集索引,首先按这个非聚集索引查找数据,找到索引行后,根据索引行后面带的聚集索引地址最后找到真正的物理数据行,并且执行删除,这个过程看起来没有作用,只能创建聚集索引来提高删除效率,因为如果classID是聚集索引,那么直接聚集索引删除,此时的效率最高。
下班后对这个话题再次想了下,觉的自己的观点都自相矛盾,既然知道删除时,会在条件列上试图应用已经存在的索引,那么为什么创建非聚集索引会无效呢?如果表的数据相当大,classID上如果没有任何索引,查找数据时就要执行表扫描,而表扫描的速度是相当慢的,为此为了证明下这个问题,我特意做了一个示意性的实验。
创建两个表course 和course2,创建语句如下,它们唯一的区别就在于索引,course表中classID上创建了非聚集索引,而course2上没有创建任何索引。
CREATE TABLE [dbo].[course](
[ID] [int] IDENTITY(1,1) NOT NULL,
[sCourseName] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[classID] [int] NULL,
CONSTRAINT [PK_CKH] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
--创建索引
create index IX_classID
on course(classID)
CREATE TABLE [dbo].[course2](
[ID] [int] IDENTITY(1,1) NOT NULL,
[sCourseName] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[classID] [int] NULL,
CONSTRAINT [PK_CKH2] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
实验过程:
第一步:分别给两个表插入相当的数据1000行,然后删除第500条记录。
delete course
where classID=500
delete course2
where classID=500
更多精彩
赞助商链接