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