聚簇索引与非聚簇索引学习总结

聚簇索引与非聚簇索引的区别

    通常情况下，建立索引是加快查询速度的有效手段。但索引不是万能的，靠索引并不能实现对所有


数据的快速存取。事实上，如果索引策略和数据检索需求严重不符的话，建立索引反而会降低查询性能


。因此在实际使用当中，应该充分考虑到索引的开销，包括磁盘空间的开销及处理开销（如资源竞争和


加锁）。例如，如果数据频繁的更新或删加，就不宜建立索引。
    本文简要讨论一下聚簇索引的特点及其与非聚簇索引的区别。
建立索引：
在SQL语言中，建立聚簇索引使用CREATE INDEX语句，格式为：CREATE CLUSTER INDEX index_name ON 


table_name(column_name1,column_name2,...);
存储特点：
聚集索引。表数据按照索引的顺序来存储的，也就是说索引项的顺序与表中记录的物理顺序一致。对于


聚集索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。 在一张表上最多只能创建一个


聚集索引，因为真实数据的物理顺序只能有一种。
非聚集索引。表数据存储顺序与索引顺序无关。对于非聚集索引，叶结点包含索引字段值及指向数据页


数据行的逻辑指针，其行数量与数据表行数据量一致。
    总结一下：聚集索引是一种稀疏索引，数据页上一级的索引页存储的是页指针，而不是行指针。而


对于非聚集索引，则是密集索引，在数据页的上一级索引页它为每一个数据行存储一条索引记录。
更新表数据
1、向表中插入新数据行
    如果一张表没有聚集索引，那么它被称为“堆集”（Heap）。这样的表中的数据行没有特定的顺序


，所有的新行将被添加到表的末尾位置。而建立了聚簇索引的数据表则不同：最简单的情况下，插入操


作根据索引找到对应的数据页，然后通过挪动已有的记录为新数据腾出空间，最后插入数据。如果数据


页已满，则需要拆分数据页，调整索引指针（且如果表还有非聚集索引，还需要更新这些索引指向新的


数据页）。而类似于自增列为聚集索引的，数据库系统可能并不拆分数据页，而只是简单的新添数据页


。
2、从表中删除数据行
    对删除数据行来说：删除行将导致其下方的数据行向上移动以填充删除记录造成的空白。如果删除


的行是该数据页中的最后一行，那么该数据页将被回收，相应的索引页中的记录将被删除。对于数据的


删除操作，可能导致索引页中仅有一条记录，这时，该记录可能会被移至邻近的索引页中，原索引页将


被回收，即所谓的“索引合并”。
========

数据库中聚簇索引与非聚簇索引的区别

在《数据库原理》里面，对聚簇索引的解释是:聚簇索引的顺序就是数据的物理存储顺序，而对非聚簇索


引的解释是:索引顺序与数据物理排列顺序无关。正式因为如此，所以一个表最多只能有一个聚簇索引


在《数据库原理》里面，对聚簇索引的解释是:聚簇索引的顺序就是数据的物理存储顺序，而对非聚簇索


引的解释是:索引顺序与数据物理排列顺序无关。正式因为如此，所以一个表最多只能有一个聚簇索引。
不过这个定义太抽象了。在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 (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就更复杂了。了解索引以及查询背后的事情不是什么坏事，它可以帮助我们更为深 刻的了解


我们的系统。
========