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

聚簇索引与非聚簇索引的区别资料库2009-06-2121:14阅读53评论2字号：大中小聚簇索引与非聚簇索引的区别(一)在《数据库原理》里面，对聚簇索引的解释是:聚簇索引的顺序就是数据的物理存储顺序，而对非聚簇索引的解释是:索引顺序与数据物理排列顺序无关。正式因为如此，所以一个表最多只能有一个聚簇索引。不过这个定义太抽象了。在SQLServer中，索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。如下图：聚簇索引与非聚簇索引的本质区别到底是什么？什么时候用聚簇索引，什么时候用非聚簇索引？这是一个很复杂的问题，很难用三言两语说清楚。我在这里从SQLServer索引优化查询的角度简单谈谈(如果对这方面感兴趣的话，可以读一读微软出版的《MicrosoftSQLServer2000数据库编程》第3单元的数据结构引论以及第6、13、14单元)。一、索引块与数据块的区别大家都知道，索引可以提高检索效率，因为它的二叉树结构以及占用空间小，所以访问速度块。让我们来算一道数学题：如果表中的一条记录在磁盘上占用1000字节的话，我们对其中10字节的一个字段建立索引，那么该记录对应的索引块的大小只有10字节。我们知道，SQLServer的最小空间分配单元是“页（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个页面，这显然不如不用索引快。SQLServer内部有一套完整的数据检索优化技术，在上述情况下，SQLServer的查询计划（SearchPlan）会自动使用表扫描的方式检索数据而不会使用任何索引。那么SQLServer是怎么知道什么时候用索引，什么时候不用索引的呢？SQLServer除了日常维护数据信息外，还维护着数据统计信息，下图是数据库属性页面的一个截图：聚簇索引与非聚簇索引的区别(二)从图中我们可以看到，SQLServer自动维护统计信息，这些统计信息包括数据密度信息以及数据分布信息，这些信息帮助SQLServer决定如何制定查询计划以及查询是是否使用索引以及使用什么样的索引（这里就不再解释它们到底如何帮助SQLServer建立查询计划的了）。我们还是来做个实验。建立一张表：tabTest(ID,unqValue，intValue)，其中ID是整形自动编号主索引，unqValue是uniqueidentifier类型，在上面建立普通索引，intValue是整形，不建立索引。之所以挂上一个没有索引的intValue字段，就是防止SQLServer使用索引覆盖查询优化技术，这样实验就起不到作用了。向表中录入10000条随机记录，代码如下：CREATETABLE[dbo].[tabTest]([ID][int]IDENTITY(1,1)NOTNULL,[unqValue][uniqueidentifier]NOTNULL,[intValue][int]NOTNULL)ON[PRIMARY]GOALTERTABLE[dbo].[tabTest]WITHNOCHECKADDCONSTRAINT[PK_tabTest]PRIMARYKEYCLUSTERED([ID])ON[PRIMARY]GOALTERTABLE[dbo].[tabTest]ADDCONSTRAINT[DF_tabTest_unqValue]DEFAULT(newid())FOR[unqValue]GOCREATEINDEX[IX_tabTest_unqValue]ON[dbo].[tabTest]([unqValue])ON[PRIMARY]GOdeclare@iintdeclare@vintset@i=0while@i10000beginset@v=rand()*1000insertintotabTest([intValue])values(@v)set@i=@i+1end