索引英雄---数据检索之谜

索引英雄---数据检索之谜

朱二（2006.1 转载请注明作者）

　　一条原本需要10秒才能完成的数据库查询操作，在适当的列上建立了索引，只需要2秒就可以完成，速度提高了5倍！
为何如此呢？下面就让我们来揭开索引英雄的神勇之谜...

一、数据库如何检索数据

SQL Server检索数据的方式有如下两种:
1、全表扫描
2、使用索引

这两种方式有何不同的，因而会产生如此大的差异呢？还是让我们从下面这个例子说起吧。
　　有一家图书馆，馆内的图书无次序的摆放在书架上。读者借书时需要把书名告诉图书管理员，然后管理员从馆内第一张书架的第一本书找起，一直要找到最后一张书架的最后一本书，期间凡是发现和读者所需书名一样的书籍都要取下，然后把所有找到的书交给读者（管理员找书的方式就相当于全表扫描）。如果馆内图书数量不多，仅有几十本或者更少，那管理员在找书过程所耗用的时间也时非常短暂的。可是，随着馆内图书数量的不断增加，找一次书所耗用的时间也从几分钟增加到几十分钟甚至几小时。于是，管理员想一个办法：
　　第一步：找来一些小卡片，我们就称做书目卡片，每张书目卡片都依次编上一个卡号。把每本书的书名按照字母排序，依次记录在书目卡片上，并且把书目卡片按卡号装订成册。
　　第二步：所有书，按照该书在书目卡片上出现的先后次序排列在书架上。
　　第三步：在书目卡片上每本书的书名后面记录上该书在图书馆内的位置，因此，书目卡片上的每一条索引都有两部分组成：书名和书的位置。如：SQL3----0110123，就表示SQL3这本书位于第11张书架的第123个位置。
这样一来，只要读者要书，管理员先在书目卡片上找到该书的条目，然后记录下该书位置，然后直接去相应的书架取书,效率提高了很多。
可是因为书实在是太多，用掉了几十张卡片，有时候找一本书需要翻几十张书目卡片才能找到，为了进一步提高速度，管理员又采取了第四步。
　　第四步：又找来一张卡片，我们称做根卡片，根卡片上专门记录每一张书目卡片的第一条书目的书名，在书名后记录该书目卡片卡号。因此，根卡片上的每一条索引都有两部分组成：书名和索引卡片的卡号。如：

SQL3--------------------023
WINDOWS 2000 Server-----024 

就表示SQL3这本书是编号为023的卡片上的第一条书目,WINDOWS 2000 Server这本书是编号为024卡片上的第一条书目。
此时，如果此时某读者欲借名为Thinking in Java的书籍,管理员翻开根卡片，因为是T字开头，T字在S后并且在W前，所以管理员找到编号为023的索引卡片，在此卡片上查找名为Thinking in Java的书籍。
上面这种利用根卡片和书目卡片查找书的过程类似于SQL Server利用聚簇索引（关于什么是聚簇索引下面即将讨论到）查找数据的过程，每张书目卡片就是一个索引页，根卡片就是根索引页。SQL Server在处理查询时，如果发现有可用的索引，首先利用根索引页定位到索引页，再由索引页定位到数据所在的数据页。

二、索引的分类

索引分为两种：当聚簇索引和非聚簇索引。
聚簇索引中行的顺序和物理表中行的顺序是一致的。显然物理表中的行只能有一种顺序，就象上例中，图书馆内书的在任一时刻顺序都是固定的，只有一种。因此每个表中的聚簇索引最多只能有一个。而非聚簇索引仅仅反映行的逻辑顺序，每个表中的非聚簇索引可有多个。
　　我们再来看上面的图书馆例子，有些读者借书的的时候，往往会告诉管理员：我要借某某作者的书。而不是提供一个书名。并且这种情况不在少数。对于这样的情况，上面建立的书目卡片其实已经起不到任何作用,于是管理员针对书的作者，另外建立一套索引卡片，卡片格式和上述卡片格式类似。那么这么这套新建立的书目卡片就相当于一个非簇索引。


三、到底需要建立多少索引

是不是要在表的每一列都建立一个索引？当然不是。就象建立索引卡片需要卡片一样，建立索引需要额外的磁盘空间。
如果你并不在乎多买几张，那么我想有一个问题你可能会在乎：一段时间后，图书馆新购买了一批书籍，这些书籍也要按照书名插入到适当的书架、适当的位置，还要修改索引卡片，想象一下，这里有多少工作量吧！（关于卡片的维护问题，我们把它放到第四部分来讨论。）
　　我就从来没看过一个读者对管理员说：嗨！给我来一本价格是12.5元的书。所以为书的价格建立一个索引显然是不明智的。那么哪些列上需要建立索引呢？下面有给出一些原则仅供参考：
(1)主关键字和外部关键字
(2)频繁在范围中搜索的列
(3)频繁按顺序访问的列
(4)经常以该列为过滤条件的列
(5)具有高度选择性的列，即大部分数据是唯一的列或列组合


四、索引的维护 

图书馆可能会不断购买新书，当出现上述情况时，馆内图书的位置需要调整，需要在索引卡片上增加书的条目。如果此时原有卡片上已经没有位置，管理员就必须增加插入新的卡片，那么在新插入卡片后的所有卡片的卡号都必须要修改。比如在卡片023和024之间插入一张新的卡片，那么新卡片的卡号应该为024，而原来的024卡片的卡号应该变动为025。如果频繁的插入新的书目，那么会大大增加卡片的维护的工作量。那么在数据库中又是什么样子的呢？
在SQL Server中，当我们在表中插入新的数据行或更改索引列中的值时，SQL Server 可能必须重新组织表中的数据存储，以便为新行腾出空间，保持数据的有序存储。向一个已满的索引页添加某个新行时，SQL Server 把大约一半的行移到新页中以便为新行腾出空间。这种重组称为页拆分。页拆分会降低性能并使表中的数据存储产生碎片。
　　怎么解决这样的问题呢？我们可以在建立索引页的时候预留一些空间，就象在每张索引卡片上都预留一片空白，供以后添加的数目使用。
　　那么到底需要保留多大的空间呢，我们建索引时，可以指定一个填充因子，以便在索引的每个叶级页上留出额外的间隙和保留一定百分比的空间，供将来表的数据存储容量进行扩充和减少页拆分的可能性。
可以在创建索引的时候使用FILLFACTOR 选项指定填充因子，详细的方法参加SQL Server的联机帮助。