`
liuzhifu123
  • 浏览: 34464 次
  • 性别: Icon_minigender_1
  • 来自: 广州
社区版块
存档分类
最新评论

聚簇索引与非聚簇索引的区别以及SQL Server查询优化技术

 
阅读更多

在《数据库原理》里面,对聚簇索引的解释是:聚簇索引的顺序就是数据的物理存储顺序,而对非聚簇索引的解释是:索引顺序与数据物理排列顺序无关。正式因为如此,所以一个表最多只能有一个聚簇索引。

不过这个定义太抽象了。在SQL Server中,索引是通过二叉树的数据结构来描述的,我们可以这么理解聚簇索引:索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点,只不过有一个指针指向对应的数据块。如下图:


非聚簇索引

 


聚簇索引


聚簇索引与非聚簇索引的本质区别到底是什么?什么时候用聚簇索引,什么时候用非聚簇索引?

这是一个很复杂的问题,很难用三言两语说清楚。我在这里从SQL Server索引优化查询的角度简单谈谈(如果对这方面感兴趣的话,可以读一读微软出版的《Microsoft SQL Server 2000数据库编程》第3单元的数据结构引论以及第6、13、14单元)。


一、索引块与数据块的区别

大家都知道,索引可以提高检索效率,因为它的二叉树结构以及占用空间小,所以访问速度块。让我们来算一道数学题:如果表中的一条记录在磁盘上占用1000字节的话,我们对其中10字节的一个字段建立索引,那么该记录对应的索引块的大小只有10字节。我们知道,SQL Server的最小空间分配单元是“页(Page)”,一个页在磁盘上占用8K空间,那么这一个页可以存储上述记录8条,但可以存储索引800条。现在我们要从一个有8000条记录的表中检索符合某个条件的记录,如果没有索引的话,我们可能需要遍历8000条×1000字节/8K字节=1000个页面才能够找到结果。如果在检索字段上有上述索引的话,那么我们可以在8000条×10字节/8K字节=10个页面中就检索到满足条件的索引块,然后根据索引块上的指针逐一找到结果数据块,这样IO访问量要少的多。


二、索引优化技术

是不是有索引就一定检索的快呢?答案是否。有些时候用索引还不如不用索引快。比如说我们要检索上述表中的所有记录,如果不用索引,需要访问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 (11NOT 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就更复杂了。了解索引以及查询背后的事情不是什么坏事,它可以帮助我们更为深刻的了解我们的系统。

分享到:
评论

相关推荐

    数据库中聚簇索引与非聚簇索引的区别[图文]

    在《数据库原理》里面,对聚簇索引的解释...聚簇索引与非聚簇索引的本质区别到底是什么?什么时候用聚簇索引,什么时候用非聚簇索引? 这是一个很复杂的问题,很难用三言两语说清楚。我在这里从SQL Server索引优化查询

    关于SQL Server中索引使用及维护简介

    SQL Server缺省情况下建立的索引是非聚簇索引,由于非聚簇索引不重新组织表中的数据,而是对每一行存储索引列值并用一个指针指向数据所在的页面。换句话 说非聚簇索引具有在索引结构和数据本身之间的一个额外级。一...

    建立合理的索引提高SQL Server的性能

    SQL Server缺省情况下建立的索引是非聚簇索引,由于非聚簇索引不重新组织表中的数据,而是对每一行存储索引列值并用一个指针指向数据所在的页面。换句话 说非聚簇索引具有在索引结构和数据本身之间的一个额外级。

    SQLServer2008查询性能优化 2/2

    4.3.2 与非聚簇索引的关系 110 4.3.3 聚簇索引建议 112 4.4 非聚簇索引 117 4.4.1 非聚簇索引维护 117 4.4.2 定义书签查找 117 4.4.3 非聚簇索引建议 118 4.5 聚簇索引vs.非聚簇索引 118 4.5.1 聚簇索引相...

    SQL SERVER建立索引.pdf

    二、聚簇索引和非聚簇索引 三、索引属性 四、用SQL建立索引 五、用事务管理器建立索引 六、创建索引的方法和索引的特征 1.创建索引的方法 2.索引的特征 七、索引的类型 1.聚簇索引的体系结构 2.非聚簇索引的体系...

    SQLServer2008查询性能优化 1/2

    4.3.2 与非聚簇索引的关系 110 4.3.3 聚簇索引建议 112 4.4 非聚簇索引 117 4.4.1 非聚簇索引维护 117 4.4.2 定义书签查找 117 4.4.3 非聚簇索引建议 118 4.5 聚簇索引vs.非聚簇索引 118 4.5.1 聚簇索引相...

    SQLServer 高级应用特性学习大纲(完整版)

    SQL Server 2005 核心技术 SQL Server 2005 服务简介 SSAS(Analysis Service)与 商业智能(BI) 数据挖掘算法简介(Data Mining) 联机数据分析技术(OLAP) ...聚簇索引、非聚簇索引、索引设计建议和优化

    SQL Server 聚集索引和非聚集索引的区别分析

    聚集索引:物理存储按照索引排序 非聚集索引:物理存储不按照索引排序

    SQLSERVER的非聚集索引结构深度理解

    我们知道SQLSERVER的数据行的存储有两种数据结构:A: 堆 B :B树(binary 二叉树) 数据按照这种两种的其中一种来排序和存储,学过数据结构的朋友应该知道二叉树,为什麽用二叉树,因为方便用二分查找法来快速 ...

    mssql 建立索引第1/2页

    表的索引与附在一本书后面的索引非常相似。它可以极大地提高查询的速度。对一个较大的表来说,通过加索引,一个通常要... 聚簇索引和非聚簇索引 假设你已经通过本书的索引找到了一个句子所在的页码。一旦已经知道了页

    SQL Server 数据库索引其索引的小技巧

    一、什么是索引 减少磁盘I/O和逻辑读次数的最佳方法之一就是使用【索引】 索引允许SQL Server在表中查找数据而不需要扫描整个表。...所以SQLServer能从一个非聚集索引的页面中读到比包含该列的表也

    sql2005全文检索.doc

    索引 大量使用非聚簇索引,一般保存在数据库中。 通过层次型、保存中间结果的方式,通过不同的轴向快速定位信息剖面。 基于XPath的索引,索引一般保存在数据库中。 基于关键字的索引,保存在文件系统中。每个表仅...

    EFCore的Index属性恢复 (带有模型构建的扩展 )-C#-下载

    您可以创建聚簇索引(这意味着您也可以创建非聚簇主键索引)。 如何使用? 将Toolbelt.EntityFrameworkCore.IndexAttribute包添加到您的项目中。 dotnet add package Toolbelt.EntityFrameworkCore.IndexAttribute ...

    Sybase ASE 15.7 开发文档:系统管理指南(卷二)

    打开的数据库、打开的索引和打开的对象 .......... 57 锁数目 .......... 57 数据库设备和磁盘 I/O 结构 .......... 58 使用内存的其它参数 .......... 58 并行处理 .......... 59 远程服务器 .......... 59 参照...

Global site tag (gtag.js) - Google Analytics