35. SQL -- 聚集索引和非聚集索引（1）

索引概述：

用户对数据库最频繁的操作是进行数据查询。一般情况下，数据库在进行查询操作时需要对整个表进行数据搜索。当表中的数据很多时，搜索数据就需要很长的时间，这就造成了服务器的资源浪费。为了提高检索数据的能力，数据库引入了索引机制。数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

例如这样一个查询：

select * from table1 where id=44。

如果没有索引，必须遍历整个表，直到ID 等于44 的这一行被找到为止；有了索引之后(必须是在ID 这一列上建立的索引)，直接在索引里面找44（也就是在ID 这一列找），就可以得知这一行的位置，也就是找到了这一行。可见，索引是用来定位的。

索引的定义：

索引是一个单独的、物理的数据库结构，是数据库的一个对象，它是某个表中一列或若干列的集合，和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引是依赖于表建立的，它提供了数据库中编排表中数据的内部方法。一个表的存储是由两部分组成的，一部分用来存放表的数据页面，另一部分存放索引页面。索引就存放在索引页面上，通常，索引页面相对于数据页面来说小得多。

建立索引的优缺点：

优点：

创建索引可以大大提高系统的性能。

通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点：

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

需创建索引的列的特点：

当列满足以下特性时，应创建索引：

在经常需要搜索的列上，可以加快搜索的速度；

在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

在经常使用在WHERE 子句中的列上面创建索引，加快条件的判断速度。

不应创建索引的列的特点：

当列有以下特性时，不应创建索引：

对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

对于那些定义为text, image 和bit 数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少,不利于使用索引。

当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改操作远远多于检索操作时，不应该创建索引。

索引的特性：

索引加快了表联接查询的速度，以及完成排序、分组的速度。

索引可以用于实施行的唯一性。

索引适用于大部分数据都是唯一的那些列。在包含大量重复值的列上建立索引是无用的。

当你修改一个索引列的数据时，相关索引将自动更新。

维护索引需要时间和资源。你不应该创建一个利用率很底的索引。

聚集索引应当创建于非聚集索引创建之前。聚集索引改变行的次序，如果非聚集索引创建于聚集索引之前，则它将被重新构建。

通常，非聚集索引创建在外关键字之上。

索引的结构：

索引使用B-树结构(BalancedTree 平衡树)

SQL Server 中的索引是以B-树结构来维护的。B-树是一个多层次、自维护的结构。一个B-树包括一个顶层，称为根节点（Root Node）；0 到多个中间层（Intermediate）；一个底层（Level 0），底层中包括若干叶子节点（Leaf Node）。

索引的分类：

索引分为聚簇索引和非聚簇索引两种；

聚簇索引（Cluster Index）：是按照数据存放的物理位置为顺序的，提高多行检索的速度

非聚簇索引（None-Cluster Index）：对于单行的检索很快。

聚集索引：

SQL 中查询时，有2 种方式：一种是索引扫描；二是表扫描。

表扫描：SQL 查询时，读取表中所有页

索引扫描：SQL 查询时，使用索引扫描，先读取索引，根据索引来查找数据实际所在页.每张表只能创建一个聚集索引，聚集索引把数据在物理上被排序。一般创建在不常被修改的属性上。若索引项经常被修改，会造成页分页，对数据查询有影响。

聚集索引在查询时，使用索引扫描。

聚集索引工作原理：

聚集索引把数据在物理上被排序。每张表中只能创建一个聚集索引，所以，你要将其创建在唯一值百分比最高、且不常被修改的属性上。当SQL Server使用聚集索引搜索某个值时，遵循下列步骤：

SQL Server从表sysindexes 中获得根页的地址。

将搜索值和根页中的关键字值进行比较。

找到包含小于或等于搜索值的最大关键字值的那一页。

页指针下到索引的下一层。

重复步骤c 和d ，直到到达数据页。

在数据页上搜索数据行，直到找到搜索值为止。如果没有在数据页上找到搜索值，则查询不返回任何行。

非聚集索引：

若有聚集索引，非聚集索引的指向是聚集索引的某个页面数据.它通常创建在用于联接、WHERE 子句和其值频繁修改的列上非聚集索引具有与表的数据完全分离的结构，使用非聚集索引不用将物理数据页中的数据按列排序。

表的数据行只能以一种排序方式存储在磁盘上，所以一个表只能有一个聚集索引。一个表最多可以建249 个非聚集索引。

非聚集索引工作原理：

非聚集索引行的物理顺序和索引顺序不同。它通常创建在用于联接、WHERE 子句和其值频繁修改的列上。当SQL Server 使用非聚集索引搜索值时，遵循以下步骤：

SQL Server从sysindexes 表中获取根页的地址。

将搜索值和根页中的关键字值比较。

找到小于或等于搜索值的最大关键字值所在的页。

页指针下到索引的下一层。

重复步骤c 和d，直到到达数据页。

搜索叶节点页上的行以寻找指定值。若未找到匹配的值，则说明表中不包含匹配行。

若找到了匹配行，则指针下到数据页及表中的行号（Eid），从而检索到了所需的行。

本文出自 “Ricky's Blog” 博客，请务必保留此出处http://57388.blog.51cto.com/47388/1628993