Sql Server 索引总结(整理)

一、数据页和索引页
SQL Server 数据页和索引页都是 8K 字节大。 SQL Server 数据页包含除了文本和图像数据以外所有与表的某一行相关的数据。对于文本和图像数据，包含与文本 / 图像列有关的行的 SQL Server 数据页将包含一个指针，该指针指向一个包含一个或多个 8 KB 页的 B 树结构，文本 / 图像数据便包含在在该 B 树结构中。

SQL Server 索引页仅包含组成特定索引的列中的数据。这意味着与 8 KB 数据页相比，索引页可以有效地将与更多行相关的信息压缩到一个 8 KB 页。可以想象索引的 I/O 性能可由此获得改进。如果所提取的列（这些列构成了索引的一部分）占表的行大小的百分比相对较低，这种推想是对的。当 SQL 查询要求某个表中的一个行集，这些行的某些值与查询中的列相匹配， SQL Server 可以节省 I/O 操作和时间，因为可以只读取索引页来查找这些值，然后只访问表中满足查询的所需行，而无须执行 I/O 操作以扫描表中所有行来找到所需行。如果定义索引时选择得很好，那么实际情况就是这样。

二、深入浅出理解索引结构
　　 实际上，您可以把索引理解为一种特殊的目录。微软的 SQL SERVER 提供了两种索引：聚集索引（ clustered index ，也称聚类索引、簇集索引）和非聚集索引（ nonclustered index ，也称非聚类索引、非簇集索引）。下面，我们举例来说明一下聚集索引和非聚集索引的区别：
　　其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查 “ 安 ” 字，就会很自然地翻开字典的前几页，因为 “ 安 ” 的拼音是 “an” ，而按照拼音排序汉字的字典是以英文字母 “a” 开头并以 “z” 结尾的，那么 “ 安 ” 字就自然地排在字典的前部。如果您翻完了所有以 “a” 开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字；同样的，如果查 “ 张 ” 字，那您也会将您的字典翻到最后部分，因为 “ 张 ” 的拼音是 “zhang” 。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。我们把这种正文内容本身就是一种按照一定规则排列的目录称为 “ 聚集索引 ” 。
　　如果您认识某个字，您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据 “ 偏旁部首 ” 查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合 “ 部首目录 ” 和 “ 检字表 ” 而查到的字的排序并不是真正的正文的排序方法，比如您查 “ 张 ” 字，我们可以看到在查部首之后的检字表中 “ 张 ” 的页码是 672 页，检字表中 “ 张 ” 的上面是 “ 驰 ” 字，但页码却是 63 页， “ 张 ” 的下面是 “ 弩 ” 字，页面是 390 页。很显然，这些字并不是真正的分别位于 “ 张 ” 字的上下方，现在您看到的连续的 “ 驰、张、弩 ” 三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为 “ 非聚集索引 ” 。
　　通过以上例子，我们可以理解到什么是 “ 聚集索引 ” 和 “ 非聚集索引 ” 。进一步引申一下，我们可以很容易的理解：每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。

三、聚集索引或非聚集索引的区别：

在非聚集索引中，叶级节点仅包含参与索引的数据以及快速找到相关数据页上其它行数据的指针。 最糟糕的情况是，从非聚集索引中获得的每一行都要求一个额外的不连续磁盘 I/O 才能检索行数据。最好的情况是，所需要的行有许多都位于相同的数据页，因此在提取每个数据页时可检索多行。如果是聚集索引，索引的叶级节点是表的实际数据行。因此，检索表数据时不需要指针跳动。基于聚集索引的范围扫描执行情况很好，因为聚集索引的叶级（即表的所有行）在物理上按照组成聚集索引的列顺序排列在磁盘上，因此，可以执行 64 KB 扩展盘区 I/O 。并且，如果聚集索引 B 树（非叶级和叶级）上没有大量分割的页，这些 64 KB I/O 还可以在物理上连续。

聚集索引

每个表中只能有一个聚集索引。物理原因很简单。尽管聚集索引 B 树结构的上半部分（在 SQL Server 文档中通常称为非叶级）与非聚集索引 B 树的结构相似，但是聚集索引 B 树的下半部分是与表相关的实际的 8 KB 数据页。这里暗含着两种性能：

通过聚集索引基于关键字搜索来检索 SQL 数据时不需要指针跳动（类似于硬盘上位置的不连续改变）就可以获得相关的数据页，因为叶级聚集索引已经是相关的数据页。

聚集索引的叶级按照组成聚集索引的列排序。因为聚集索引的叶级包含表的实际 8 KB 数据页，这意味着整个表的行数据在物理上按照聚集索引确定的顺序排列在磁盘驱动器上。当根据聚集索引的值从这个表中提取大量行时，这种排列提供了一种潜在的 I/O 性能优势，因为使用的是连续磁盘 I/O（除非该表上发生了页拆分，我们将在后面的“FILLFACTOR 和 PAD_INDEX 的重要性”中讨论这个问题）。这就是根据执行范围扫描检索大量的行时所使用的列来提取表中的聚集索引很重要的原因。

 

非聚集索引

如果要根据键值从大型 SQL Server 表提取具有良好选择性的少数几行，非聚集索引最有用。以前已提到过，非聚集索引是由 8 KB 索引页组成的 B 树。索引页的 B 树的底部或叶级包含组成该索引的列中的所有数据。当用非聚集索引检索表中与键值匹配的信息时，将搜索整个索引 B 树，直到在索引叶级找到一个与键值匹配的值。如果需要的列不是索引组成的一部分，则会发生指针跳动。该指针跳动可能需要在磁盘上进行一个不连续 I/O 操作。如果表以及它相应的索引 B 树很大，甚至可能要求从另一个磁盘中读取数据。如果多个指针指向同一个 8 KB 数据页，则对 I/O 性能的影响比较小，因为只须将该数据页读入数据高速缓存一次。如果某个 SQL 查询涉及到要用非聚集索引进行搜索，那么对于所返回的每一行，均需要一次指针跳动。这些指针跳动可以解释为什么非聚集索引更适合于只返回表中一行或几行的 SQL 查询，而聚集索引更适合于要求返回许多行的查询。

覆盖索引

非聚集索引的一个特例是覆盖索引。覆盖索引的定义是在选择条件和 WHERE 谓词上均满足 SQL 查询的所有列的基础上建立的非聚集索引。覆盖索引可以节省大量的 I/O，因此可极大地改善查询的性能。但是有必要在新建索引（以及与它相关的 B 树索引结构维护）所需要的代价和覆盖索引所带来的 I/O 性能增益之间进行权衡。如果覆盖索引对于 SQL Server 上经常运行的查询或查询组极其有利，那么创建覆盖索引是值得的。
注： 如果非聚簇索引中包含结果数据 , 那么它的查询速度将快于聚簇索引。

四、何时使用聚集索引或非聚集索引
下面的表总结了何时使用聚集索引或非聚集索引（很重要）：

动作描述	使用聚集索引	使用非聚集索引
列经常被分组排序	应	应
返回某范围内的数据	应	不应
一个或极少不同值	不应	不应
小数目的不同值	应	不应
大数目的不同值	不应	应
频繁更新的列	不应	应
外键列	应	应
主键列	应	应
频繁修改索引列	不应	应

事实上，我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如：返回某范围内的数据一项。比如您的某个表有一个时间列，恰好您把聚合索引建立在了该列，这时您查询 2004 年 1 月 1 日 至 2004 年 10 月 1 日 之间的全部数据时，这个速度就将是很快的，因为您的这本字典正文是按日期进行排序的，聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可；而不像非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容。

 

五：建立索引的思路

 

(1) 、主键时常作为 where 子句的条件，应在表的主键列上建立聚簇索引，尤其当经常用它作为连接的时候。

(2) 、有大量重复值且经常有范围查询和排序、分组发生的列，或者非常频繁地被访问的列，可考虑建立聚簇索引。

(3) 、经常同时存取多列，且每列都含有重复值可考虑建立复合索引来覆盖一个或一组查询，并把查询引用最频繁的列作为前导列，如果可能尽量使关键查询形成覆盖查询。

(4) 、如果知道索引键的所有值都是唯一的，那么确保把索引定义成唯一索引。

(5) 、在一个经常做插入操作的表上建索引时，使用 fillfactor( 填充因子 ) 来减少页分裂，同时提高并发度降低死锁的发生。如果在只读表上建索引，则可以把 fillfactor 置为 100 。

(6) 、在选择索引键时，设法选择那些采用小数据类型的列作为键以使每个索

　　引页能够容纳尽可能多的索引键和指针，通过这种方式，可使一个查询必须遍历的索引页面降到最小。此外，尽可能地使用整数为键值，因为它能够提供比任何数据类型都快的访问速度。

 

六：不合理的索引：

1、             在表中建立了过多的索引。

2、             在键值大的列上建立索引。

3、             不合理的使用聚集索引。

4、             不合理的使用填充因子。

 

七：有关索引的其他知识点：

在 sql server 中，确定表中记录顺序的是聚集索引，如果表中没有聚集索引，则表中数据是无序的，而不是按照插入记录的顺序

1、 索引对排序的影响。

查询结果的显示顺序取决于引用的索引，而不是预想的记录存储顺序。由于查询的顺序受到表中索引的影响 ( 无索引时，查询的结果为记录的存储顺序 ) 。因此如果在乎查询结果的顺序，就应该显示的使用 order by 字句指定查询结果顺序，而不要依赖于记录的存储数据。

2、 在 order by 字段没有索引的情况下，系统只能按照连接条件和 where 里的条件选择索引。
在 order by 字段有索引的情况下，系统优先选择 order by 字段的索引。
可以用 with(index=) 指定索引，不过，使用 with(index=) 指定索引必须自己经过测试，确定指定的索引的查询速度是可以接受的

3、 组合索引：组合索引即多列索引，指一个索引含有多个列。一个组合索引相当于多个单列索引，如索引 (ColA, ColB, ColC) 至少相当于 (ColA) 、 (ColA, ColB) 、 (ColA, ColB, ColC) 三个索引。