聚集和非聚集索引

聚集和非聚集索引

以前一直不是很注意聚集索引和非聚集索引的关系。今天因为设计一个统计系统对性能要求要高一点，所以把聚集索引和非聚集索引进行一下重温。同时也写出来让大家拍转。

         索引，就好像一个方便快速查找的东西。就好像把物品按照一定顺序排列在一个地方一样，而索引就好像是针对这些排序编译的一个排序目录。

         那什么是非聚集索引呢？

         我们还是以一本书来作为例子,非聚集索引就好像是书的目录，当你想找某个章节的内容的时候，你直接查看目录，然后就能翻到你所要查找的详细书页去了。这样就能大大的提高你的查询速度。

         那为什么有聚集索引呢？

         还是以书来作为例子。如果我们对书比较熟悉了，直接就能跳过查找目录的这一操作，直接就往目标书页翻过去了，这样就能避免中间的查找目录的过程，从而又再次提交了效率。

 

 

看上面的图：

  这是一个聚集索引的一个数据结构。

索引页：是数据库针对所有的数据会生成一个索引页，当你对数据进行查询的时候，他会检索相关的索引页，从而找到相关的数据页。

数据页：是指的详细的数据。同时需要注意的是：聚集索引的数据的物理排序都是按照索引顺序进行的排序。

 

他引页得到相关的数据的范围，从而快速的找到详细的目标数据。而索引页其实他是一个树形菜单的方式，这里就会涉及到B+Tree和B-Tree的相关的知识了。暂时还没有详细去看相关的资料。

举例：

例如：我们想找到JAC这一条数据。

则数据库则先会在 页7找到下一集的索引页 页6 而 页6则会继续查找他下面 页4从而找到详细数据并返回。

 

然后我们再来看看非聚集索引的结构。

  非聚集索引和聚集索引最大的区别在于，他针对数据的索引列生成了一个已经进行了排序的索引页。而他本身的数据在物理存储上是没有顺序可言的。

 

也因为这样的结构的不同，所以聚集索引和非聚集索引有一个增加、修改、删除时候的效率比较。

聚集所以因为他的排序是直接建立在数据上面的，所以当你对数据进行修改（涉及到索引列的操作）操作的时候他会需要对原来的数据进行相关的排序，而非聚集索引的排序是建立在自己创建的索引上，他只需要更新自己的索引页就好了。所以，从这点考虑，非聚集索引比聚集索引效率更高。但是因为聚集所以他直接面对数据，在查找数据的时候不用进行排序索引页的操作这一操作，所以他的查找效率更高。

但是在插入和删除的时候，聚集索引比非聚集索引块。道理和上面的差不多。

同时还有一个需要注意的问题就是：聚集索引如果你插入的列的索引列是自增的时候，你在插入的时候因为他不需要分页操作，所以效率会更快，但是如果你插入的数据在已经有的数据的中间，那么他会引起一个分页这样的一个操作，这样会让你的操作更消耗一些。

还有一个问题就是，如果你的非聚集索引中使用到了聚集索引的列，可能最造成一些不必要的性能消耗。我在网上查找了下一些资料，说的意思是：当你的非聚集索引使用到了聚集索引的列的时候，他会多进行一些不必要的聚集索引分页的这样的一个操作。从而效率的你的性能。

 

然后我们再看看，因为在什么时候去使用不同的索引。

首先：不要过度的去建立索引，过多的无用索引只会加重你的更新的速度，让你的操作更加的迟缓。同时过多的索引页不好进行管理，会让的你的索引变得更加的混乱。大大加重后期的优化难度。

一般来说，我们在进行索引建立的时候需要用到一些工具。例如：Activity Monitor, Profiler

去不断的分析你的系统使用最多的查询的语句，以及在性能对你的系统造成堵塞的源头，针对他进行相关的优化。

一般来说：

1.      主键，候选键和外键需要建立相关的索引。因为他的连接查询的可能性很高。

2.      频繁检索的列和按排序顺序频繁检索的列。

 

但是针对频繁操作的列，有些列也不同进行相关的索引

1.      列不同的值比较少。例如性别，状态等。

2.      过长的值的列。例如：备注。

 

在删除聚集索引之前，先删除非聚集索引。如果你不按照这条方针做，则会导致无意义的重建非聚集索引。将表由聚集索引变为堆会使得表上的非聚集索引重建，因为非聚集索引的书签由聚集索引键变为RID。（摘抄）

 

考虑使用填充因子来减少页分裂
         加入表中的数据已经达到了页所能容纳的最大值。那么再插入数据就会导致页分裂了。因此重建索引时可以使用填充因子，如果数据库写大于读的话，设置填充因子为75，如果读写大致相等的话，设置填充因子为90到95。（这一点是我一直没有注意的事情。）

填充因子是什么呢？

盗用别人的解释：

    在创建聚集索引和非聚集索引时，表中的数据按照索引列中的值的顺序存储在数据库的数据页中。在表中插入新的数据行或更改索引列中的值时， SQL Server 可能必须重新组织表中的数据存储，以便为新行腾出空间，保持数据的有序存储。向一个已满的索引页添加某个新行时，SQL Server  把大约一半的行移到新页中以便为新行腾出空间。这种重组称为页拆分。页拆分会降低性能并使表中的数据存储产生碎片。
    创建索引时，可以指定一个填充因子，以便在索引的每个叶级页上留出额外的间隙和保留一定百分比的空间，供将来表的数据存储容量进行扩充和减少页拆分的可能性。填充因子的值是从 0  到 100  的百分比数值，指定在创建索引后对数据页的填充比例。值为 100  时表示页将填满，所留出的存储空间量最小。只有当不会对数据进行更改时（例如，在只读表中）才会使用此设置。值越小则数据页上的空闲空间越大，这样可以减少在索引增长过程中对数据页进行拆分的需要，但需要更多的存储空间。当表中数据会发生更改时，这种设置更为适当。提供填充因子选项是为了对性能进行微调。但是，使用 sp_configure  系统存储过程指定的服务器范围的默认填充因子，在大多数情况下都是最佳的选择。 

即使对于一个面向许多插入和更新操作的应用程序来说，数据库读取次数一般也超过数据库写入次数的 5  到 10  倍。因此，指定一个不同于默认设置的填充因子会降低数据库的读取性能，而降低量与填充因子设置值成反比。
只有当在表中根据现有数据创建新索引，并且可以精确预见将来会对这些数据进行哪些更改时，将填充因子选项设置为另一个值才有用。
填充因子只在创建索引时执行；索引创建后，当表中进行数据的添加、删除或更新时，不会保持填充因子。如果试图在数据页上保持额外的空间，则将有背于使用填充因子的本意，因为随着数据的输入，SQL Server 必须在每个页上进行页拆分，以保持填充因子指定的空闲空间百分比。因此，如果表中的数据进行了较大的变动，添加了新数据，可以填充数据页的空闲空间。在这种情况下，可以重新创建索引，重新指定填充因子，以重新分布数据。

归根到底，填充因子的作用就是防止因为数据的增加或者修改操作去进行不必要的重建索引页这样的操作，从而提高操作速度。

 

这篇文章可能和其他一些文章有部分雷同。因为主要还是看了其他人的一些文章，从里面提取出来的，再进行自己的理解的一些梳理。