数据库中聚簇索引与非聚簇索引的区别[图文](2)
来源:asp之家 发布时间:2012-02-25 19:38:23
二、索引优化技术
是不是有索引就一定检索的快呢?答案是否。有些时候用索引还不如不用索引快。比如说我们要检索上述表中的所有记录,如果不用索引,需要访问8000 条×1000字节/8K字节=1000个页面,如果使用索引的话,首先检索索引,访问8000条×10字节/8K字节=10个页面得到索引检索结果,再根 据索引检索结果去对应数据页面,由于是检索所有数据,所以需要再访问8000条×1000字节/8K字节=1000个页面将全部数据读取出来,一共访问了 1010个页面,这显然不如不用索引快。
SQL Server内部有一套完整的数据检索优化技术,在上述情况下,SQL Server的查询计划(Search Plan)会自动使用表扫描的方式检索数据而不会使用任何索引。那么SQL Server是怎么知道什么时候用索引,什么时候不用索引的呢?SQL Server除了日常维护数据信息外,还维护着数据统计信息,下图是数据库属性页面的一个截图:
从图中我们可以看到,SQL Server自动维护统计信息,这些统计信息包括数据密度信息以及数据分布信息,这些信息帮助SQL Server决定如何制定查询计划以及查询是是否使用索引以及使用什么样的索引(这里就不再解释它们到底如何帮助SQL Server建立查询计划的了)。我们还是来做个实验。建立一张表:tabTest(ID, unqValue,intValue),其中ID是整形自动编号主索引,unqValue是uniqueidentifier类型,在上面建立普通索 引,intValue 是整形,不建立索引。之所以挂上一个没有索引的intValue字段,就是防止SQL Server使用索引覆盖查询优化技术,这样实验就起不到作用了。向表中录入10000条随机记录,代码如下:
CREATE TABLE [dbo].[tabTest] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[unqValue] [uniqueidentifier] NOT NULL ,
[intValue] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tabTest] WITH NOCHECK ADD
CONSTRAINT [PK_tabTest] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tabTest] ADD
CONSTRAINT [DF_tabTest_unqValue] DEFAULT (newid()) FOR [unqValue]
GO
CREATE INDEX [IX_tabTest_unqValue] ON [dbo].[tabTest]([unqValue]) ON[PRIMARY]
GO
declare @i int
declare @v int
set @i=0
while @i<10000
begin
set @v=rand()*1000
insert into tabTest ([intValue]) values (@v)
set @i=@i+1
end
然后我们执行两个查询并查看执行计划,如图:(在查询分析器的查询菜单中可以打开查询计划,同时图上第一个查询的GUID是我从数据库中找的,大家做实验的时候可以根据自己数据库中的值来定):
从图中可以看出,在第一个查询中,SQL Server使用了IX_tabTest_unqValue索引,根据箭头方向,计算机先在索引范围内找,找到后,使用Bookmark Lookup将索引节点映射到数据节点上,最后给出SELECT结果。在第二个查询中,系统直接遍历表给出结果,不过它使用了聚簇索引,为什么呢?不要忘 了,聚簇索引的页节点就是数据节点!这样使用聚簇索引会更快一些(不受数据删除、更新留下的存储空洞的影响,直接遍历数据是要跳过这些空洞的)。
下面,我们在SQL Server中将ID字段的聚簇索引更改为非聚簇索引,然后再执行select * from tabTest,这回我们看到的执行计划变成了:
SQL Server没有使用任何索引,而是直接执行了Table Scan,因为只有这样,检索效率才是最高的。
三、聚簇索引与非聚簇索引的本质区别
现在可以讨论聚簇索引与非聚簇索引的本质区别了。正如本文最前面的两个图所示,聚簇索引的叶节点就是数据节点,而非聚簇索引的页节点仍然是索引检点,并保留一个链接指向对应数据块。
还是通过一道数学题来看看它们的区别吧:假设有一8000条记录的表,表中每条记录在磁盘上占用1000字节,如果在一个10字节长的字段上建立非 聚簇索引主键,需要二叉树节点16000个(这16000个节点中有8000个叶节点,每个页节点都指向一个数据记录),这样数据将占用8000条 ×1000字节/8K字节=1000个页面;索引将占用16000个节点×10字节/8K字节=20个页面,共计1020个页面。
同样一张表,如果我们在对应字段上建立聚簇索引主键,由于聚簇索引的页节点就是数据节点,所以索引节点仅有8000个,占用10个页面,数据仍然占有1000个页面。
下面我们看看在执行插入操作时,非聚簇索引的主键为什么比聚簇索引主键要快。主键约束要求主键不能出现重复,那么SQL Server是怎么知道不出现重复的呢?唯一的方法就是检索。对于非聚簇索引,只需要检索20个页面中的16000个节点就知道是否有重复,因为所有主键 键值在这16000个索引节点中都包含了。但对于聚簇索引,索引节点仅仅包含了8000个中间节点,至于会不会出现重复必须检索另外1000个页数据节点 才知道,那么相当于检索10+1000=1010个页面才知道是否有重复。所以聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多。
让我们再来看看数据检索的效率,如果对上述两表进行检索,在使用索引的情况下(有些时候SQL Server执行计划会选择不使用索引,不过我们这里姑且假设一定使用索引),对于聚簇索引检索,我们可能会访问10个索引页面外加1000个数据页面得 到结果(实际情况要比这个好),而对于非聚簇索引,系统会从20个页面中找到符合条件的节点,再映射到1000个数据页面上(这也是最糟糕的情况),比较 一下,一个访问了1010个页面而另一个访问了1020个页面,可见检索效率差异并不是很大。所以不管非聚簇索引也好还是聚簇索引也好,都适合排序,聚簇 索引仅仅比非聚簇索引快一点。
结语
好了,写了半天,手都累了。关于聚簇索引与非聚簇索引效率问题的实验就不做了,感兴趣的话可以自己使用查询分析器对查询计划进行分析。SQL Server是一个很复杂的系统,尤其是索引以及查询优化技术,Oracle就更复杂了。了解索引以及查询背后的事情不是什么坏事,它可以帮助我们更为深 刻的了解我们的系统。
猜你喜欢
- 科讯5.0 标签和之前版本变化不大,如果用老版本的科讯,可以参考这个标签使用。相关文章:新云4.0 模板通用标签说明 标签清单:======
- GetRepeatTimes(TheChar,TheString) 得到一个字符串在另一个字符串当中出现几次的函数(新)如:response
- 1 Video介绍引用我翻译文档《在HTML5页面中嵌入音频和视频》中的介绍文字:“当今,在网页上嵌入视频且所有用户不管使用任何浏览器或者操
- 安装时建议你为MySQL管理创建一个用户和组。由该组用户运行mysql服务器并执行管理任务。(也可以以root身份运行服务器,但是不推荐)第
- 首先呢,需要有两个mysql服务器。如果做测试的话可以在同一台机器上装两个mysql服务程序,注意要两个运行程序的端口不能一样。我用的是一个
- 版权所有:Copyright 1997 Netscape Communications Corporation原文链接:Object Hie
- 前段时间在网上找了一个“完美的”JavaScript对象克隆的函数,感觉还不错,但随后便出现了一些问题,发现这个克隆并不好用,在使用发现了如
- 如何在刷新链接之前验证文件是否存在?如何在每次刷新链接之前,验证链接文件是否确实存在?特别是对于数据库中的文件,我们必须在处理它之前确认一下
- 1引言实现磁带备份数据的功能有两方面的困难:首先,SQL Server(以下简称SQL)所提供的数据库的整体备份及恢复功能不能直接满足本系统
- 今天在给一个img加链接时发现的<body> <a href="#1" >&
- SQL语句优化的原则:◆1、使用索引来更快地遍历表缺省情况下建立的索引是非群集索引,但有时它并不是最佳的。在非群集索引下,数据在物理上随机存
- ajax开发中, 常遇到下面的几种情况: 1 服务端需要返回一段普通文本给客户端 2 服务端需要返回一段HTML代码给客户端 3 服务端需要
- 1、什么是超链接图标规范超链接图标规范是我根据《Iconize Textlinks with CSS》修改调整完成的。主要是整理出常用到的一
- 以这两个域名为例:http://www.knowsky.com/http://code.knowsky.com/这两个域名都是绑在同一个空间
- 内置300余汉字点阵.纯ASP实现汉字验证码.不读数据库.多种属性自由调节,其中包括:生成的图片长和宽,字符数,背景显示效果(渐变,杂色,固
- INI是微软Windows操作系统中的文件扩展名。这些字母表示初始化。正如该术语所表示的,INI文件被用来对操作系统或特定程序初始化或进行参
- 最简单的:<textarea name="A" cols="45" rows="2&
- ORA-00600:internal error code,arguments:[num],[?],[?],[?],[?]产生原因:这种错误
- 一、设计说明设计这个自动化的目的是想要交替、重复地使用固定的几个分区(分区编号01~05)来保存数据,当最后一个分区就是快满的时候,我们会把
- 周六。据闻北服美女甚多,于是应邀去做了一个关于UED的讲座。人不多,讲的很乱,但大家听的很认真,欣慰。讲完之后回答了很多关于社区、搜索、设计