聚集索引和非聚集索引的区别.doc

聚集索引和非聚集索引的区别 聚集索引和非聚集索引的区别:汉语字典的正文本身就是一个聚集索引.比如,我们要查安字,就会很自然地翻开字典的前几页,因为安的拼音是an,而按照拼音排序汉字的字典是以英文字母a开头并以z结尾的,那么安字就自然地排在字典的前部.如果您翻完了所有以a开头的部分仍然找不到这个字,那么就说明您的字典中没有这个字;同样的,如果查张字,那您也会将您的字典翻到最后部分,因为张的拼音是zhang.也就是说,字典的正文部分本身就是一个目录,您不需要再去查其他目录来找到您需要找的内容.正文内容本身就是一种按照一定规则排列的目录称为聚集索引.如果您认识某个字,您可以快速地从自动中查到这个字.但您也可能会遇到您不认识的字,不知道它的发音,这时候,您就不能按照刚才的方法找到您要查的字,而需要去根据偏旁部首查到您要找的字,然后根据这个字后的页码直接翻到某页来找到您要找的字.但您结合部首目录和检字表而查到的字的排序并不是真正的正文的排序方法,比如您查张字,我们可以看到在查部首之后的检字表中张的页码是672页,检字表中张的上面是驰字,但页码却是63页,张的下面是弩字,页面是390页.很显然,这些字并不是真正的分别位于张字的上下方,现在您看到的连续的驰、张、弩三字实际上就是他们在非聚集索引中的排序,是字典正文中的字在非聚集索引中的映射.我们可以通过这种方式来找到您所需要的字,但它需要两个过程,先找到目录中的结果,然后再翻到您所需要的页码.我们把这种目录纯粹是目录,正文纯粹是正文的排序方式称为非聚集索引.通过以上例子,我们可以理解到什么是聚集索引和非聚集索引.进一步引申一下,我们可以很容易的理解:每个表只能有一个聚集索引,因为目录只能按照一种方法进行排序.得出查询速度的方法是:在各个select语句前加:declare d datetimeset d=getdate()并在select语句后加:select 语句执行花费时间(毫秒)=datediff(ms,d,getdate()1、用聚合索引比用不是聚合索引的主键速度快2、用聚合索引比用一般的主键作order by时速度快,特别是在小数据量情况下事实上,如果数据量很小的话,用聚集索引作为排序列要比使用非聚集索引速度快得明显的多;而数据量如果很大的话,如10万以上,则二者的速度差别不明显.3、使用聚合索引内的时间段,搜索时间会按数据占整个数据表的百分比成比例减少,而无论聚合索引使用了多少个4 、日期列不会因为有分秒的输入而减慢查询速度从publish 表中取出第 n 条到第 m 条的记录:SELECT TOP m-n+1 *FROM publishWHERE (id NOT IN(SELECT TOP n-1 idFROM publish)id 为publish 表的关键字只所以把查询优化和分页算法这两个联系不是很大的论题放在一起,就是因为二者都需要一个非常重要的东西聚集索引.在前面的讨论中我们已经提到了,聚集索引有两个最大的优势:1、以最快的速度缩小查询范围.2、以最快的速度进行字段排序.第1条多用在查询优化时,而第2条多用在进行分页时的数据排序.而聚集索引在每个表内又只能建立一个,这使得聚集索引显得更加的重要.聚集索引的挑选可以说是实现查询优化和高效分页的最关键因素.但要既使聚集索引列既符合查询列的需要,又符合排序列的需要,这通常是一个矛盾.聚集索引是如此的重要和珍贵,所以一定要将聚集索引建立在:1、您最频繁使用的、用以缩小查询范围的字段上;2、您最频繁使用的、需要排序的字段上. 阅读(1944) 千11-NET开发 聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致, 别是表记录的排列顺序和与索引的排列顺序是聚集索引表记录的排列顺序与索引的排列顺序一致,优点是查询速度快,因为一旦具有第一个索引值的纪录被找到,具有连续索引值的记录也一定物理的紧跟其后.聚集索引的缺点是对表进行修改速度较慢,这是为了保持表中的记录的物理顺序与索引的顺序一致, 否一致,聚集索引表记录的排列顺序与索引 而把记录插入到数据页的相应位置,必须在数据页中进行数据重排 ,降低了执行速度.建议使用聚集索引的场合为:否一致,聚集索引表记录的排列顺序与索引 a.此列包含有限数目的不同值;b.查询的结果返回一个区间的值;c.查询的结果返回某值相同的大量结果集. 旦具有第一个索引值的纪录被找到,具有连续非聚集索引指定了表中记录的逻辑顺序,但记录的物理顺序和索引的顺序不一致,聚集索引和非聚集索引都采用了B+树的结构,但非聚集索引的叶子层并不与实际的数据页相重叠,而采用叶子层包含一个指向表中的记录在数据页中的指针的方式.非聚集索引比聚集索引层次多,添加记录不会引起数据顺序的重组.建议使用非聚集索引的场合为: 旦具有第一个索引值的纪录被找到,具有连续a.此列包含了大量数目不同的值;b.查询的结束返回的是少量的结果集; 聚集索引和非聚集索引的根本区c.order by 子句中使用了该列.否一致,聚集索引表记录的排列顺序与索引-不用索引查询Select * FROM IndexTestTable WHIT(INDEX(0) 别是表记录的排列顺序和与索引的排列顺序是Where Status=B-创建聚集索引 别是表记录的排列顺序和与索引的排列顺序是Create CLUSTERED INDEX icIndexTestTableON IndexTestTable(Status) 的排列顺序一致,优点是查询速度快,因为一GO-使用索引查询 聚集索引和非聚集索引的根本区Select * FROM IndexTestTable WITH(INDEX(icIndexTestTable)Where Status=B别是表记录的排列顺序和与索引的排列顺序是别是表记录的排列顺序和与索引的排列顺序是建了索引块后 查找的时候首先查找索引块 在查找索引块中的内容的排列顺序一致,优点是查询速度快,因为一 聚集索引的区别别是表记录的排列顺序和与索引的排列顺序是 聚集索引:物理存储按照索引排序旦具有第一个索引值的纪录被找到,具有连续 非聚集索引:物理存储不按照索引排序别是表记录的排列顺序和与索引的排列顺序是 优势与缺点聚集索引和非聚集索引的根本区 聚集索引:插入数据时速度要慢(时间花费在物理存储的排序上,也就是首先要找到位置然后插入),查询数据比非聚集数据的速度快旦具有第一个索引值的纪录被找到,具有连续聚簇索引非常象目录表,目录表的顺序与实际的页码顺序是一致的.非聚簇索引则更象书的标准索引表,索引表中的顺序通常与实际的页码顺序是不一致的.一本书也许有多个索引.例如,它也许同时有主题索引和作者索引.同样,一个表可以有多个非聚簇索引.聚集索引和非聚集索引的根本区非聚簇索引需要大量的硬盘空间和内存.另外,虽然非聚簇索引可以提高从表中取数据的速度,它也会降低向表中插入和更新数据的速度.每当你改变了一个建立了非聚簇索引的表中的数据时,必须同时更新索引.因此你对一个表建立非聚簇索引时要慎重考虑.如果你预计一个表需要频繁地更新数据,那么不要对它建立太多非聚簇索引.另外,如果硬盘和内存空间有限,也应该限制使用非聚簇索引的数量.旦具有第一个索引值的纪录被找到,具有连续索引是通过二叉树的数据结构来描述的,我们可以这么理解旦具有第一个索引值的纪录被找到,具有连续 聚簇索引:索引的叶节点就是数据节点.而非聚簇索引的叶节点仍然是索引节点,只不过有一个指针指向对应的数据块.否一致,聚集索引表记录的排列顺序与索引如下图:聚集索引和非聚集索引的根本区聚集索引和非聚集索引的根本区非聚集索引旦具有第一个索引值的纪录被找到,具有连续聚集索引否一致,聚集索引表记录的排列顺序与索引三、聚簇索引与非聚簇索引的本质区别聚集索引和非聚集索引的根本区现在可以讨论聚簇索引与非聚簇索引的本质区别了.正如本文最前面的两个图所示,聚簇索引的叶节点就是数据节点,而非聚簇索引的页节点仍然是索引检点,并保留一个链接指向对应数据块.还是通过一道数学题来看看它们的区别吧:假设有一8000条记录的表,表中每条记录在磁盘上占用1000字节,如果在一个10字节长的字段上建立非聚簇索引主键,需要二叉树节点16000个(这16000个节点中有8000个叶节点,每个页节点都指向一个数据记录),这样数据将占用8000条t1000字节 /8K字节=1000个页面;索引将占用16000个节点t10字节/8K字节=20个页面,共计1020个页面. 旦具有第一个索引值的纪录被找到,具有连续同样一张表,如果我们在对应字段上建立聚簇索引主键,由于聚簇索引的页节点就是数据节点,所以索引节点仅有8000个,占用10个页面,数据仍然占有1000个页面.旦具有第一个索引值的纪录被找到,具有连续下面我们看看在执行插入操作时,非聚簇索引的主键为什么比聚簇索引主键要快.主键约束要求主键不能出现重复,那么SQL Server是怎么知道不出现重复的呢?唯一的方法就是检索.对于非聚簇索引,只需要检索20个页面中的16000个节点就知道是否有重复,因为所有主键键值在这16000个索引节点中都包含了.但对于聚簇索引,索引节点仅仅包含了8000个中间节点,至于会不会出现重复必须检索另外8000个页数据节点才知道,那么相当于检索10+1000=1010个页面才知道是否有重复.所以聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多.让我们再来看看数据检索的效率,如果对上述两表进行检索,在使用索引的情况下(有些时候SQL Server执行计划会选择不使用索引,不过我们这里姑且假设一定使用索引),对于聚簇索引检索,我们可