数据库索引研究

于绍娜;李霞丽;胥桂仙;杨智君

【摘 要】在数据库系统应用中,要进行频繁的数据台询操作.索引是与表或视图关联的磁盘上结构,有效的使用索引,可以快速找到表或视图中特定信息,减少系统的响应时间.本文介绍了索引的概念、分类、使用和维护,并就MS SQL SERVER索引进行了一些分析和实践.

【期刊名称】《电子测试》 【年(卷),期】2010(000)002 【总页数】5页(P84-87,91)

【关键词】聚集索引;非聚集索引;筛选索引;B树 【作 者】于绍娜;李霞丽;胥桂仙;杨智君

【作者单位】中央民族大学,北京,100081;中央民族大学,北京,100081;中央民族大学,北京,100081;中国计量科学研究院,北京,100013 【正文语种】中 文 【中图分类】TP311 0 引言

索引中保存着表或视图中排序的索引列，并且纪录了索引列在数据库表中的物理存储位置。通过索引查询，可以减少为返回查询结果集而必须读取的数据量。索引还可以强制表中的行具有唯一性，从而确保表数据的数据完整性。创建设计良好的索

引以支持查询，可以显著提高数据库查询和应用程序的性能。但是，索引并不总是提高系统的性能，表中建有大量索引会影响增、删、改语句的性能，因为当表中的数据更改时，所有索引都须进行适当的调整。因此，合理的设计索引对于提高数据库的性能具有重要意义。 1 索引的概念

索引包含由表或视图中的一列或多列生成的键。键存储在一个B树结构中，使SQL Server可以快速有效地查找与键值关联的行。

MS SQL SERVER中数据存储的基本单位是页(Page)，磁盘I/O操作在页级执行。SQL Server 数据页和索引页都是8K字节大。这意味着与8KB数据页相比，索引页可以有效地将与更多行相关的信息压缩到一个8KB页。当SQL查询要求某个表中的一个行集，这些行的某些值与查询中的列相匹配，SQL Server可以节省I/O操作和时间，因为可以只读取索引页来查找这些值，然后只访问表中满足查询的所需行，而无须执行I/O操作以扫描表中所有行来找到所需行。

MS SQL SERVER提供了2种索引：聚集索引和非聚集索引。二者的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。非聚集索引具有独立于数据行的结构，包含非聚集索引键值，并且每个键值项都有指向包含该键值的数据行的指针。

聚集索引和非聚集索引均建立在由8KB索引页所组成的B树结构上。它们的不同在于B树结构的底部，其底部在SQL Server文档中称为叶级，索引B树结构的上半部分称为非叶级索引。在聚集索引中，索引树的叶级页包含实际的数据：记录的索引顺序与物理顺序相同。在非聚集索引中，叶级页指向表中的记录：记录的物理顺序与逻辑顺序没有必然的联系。

聚簇索引类似汉语字典的拼音音节目录表，目录表的顺序与实际的页码顺序是一致的。非聚簇索引则类似汉语字典笔画检字表，笔画检字表中的顺序通常与实际的页

码顺序是不一致的。由此可以理解每个表只能有一个聚集索引，因为拼音音节目录表只能按照一种方法进行排序。但可以有多个非聚集索引，因为可能按笔画检索，也可能按部首检索。

图1给出了非聚集索引和聚集索引在结构上的不同。两种索引的所有关键字都出现在叶子结点中，并且是有序的，非叶子结点相当于是叶子结点的索引。聚集索引的叶级结点是数据页，在数据页中数据按照索引顺序存储；非聚集索引的叶级结点，不是数据，而是指向数据页（聚集索引的叶级结点或者堆）的页，叶节点页的次序和表的物理存储次序可能不同。 2 索引的使用

聚集索引，索引的叶级节点是表的实际数据行，通过聚集索引来检索SQL数据时不需要指针跳动就可以获得相关的数据页。 图1 聚集索引和非聚集索引B树结构

聚簇索引适用于具有下列属性的列：主键及外键列；经常使用的查询列；查询列中包含ORDER BY或GROUP BY子句。因为聚集索引已经按顺序排序，查询中不必再排序；不经常修改的列；在连接操作中使用的列；要求返回许多行的查询，因为索引的叶级节点是表的实际数据行，读索引已经把表里的数据全部读到；使用运算符（如BETWEEN、>、>=、<和<=）返回一个区间的值。例如，在“学生表”中“学号”列上建聚集索引，能根据学号快速检索到起始学号所在的行，然后检索此行后所有连续的行，直到检索到终止学号所在的行。

聚集索引不适用于具有下列属性的列：（1）经常修改的列，因为值修改后，索引需要重新排序，增加了维护开销。（2）索引列包含若干列或若干大型列的组合。因为非聚集索引项包含聚集索引键列，同时也包含为此非聚集索引定义的键列，聚集索引数据长度增大，同一表中的非聚集索引也将随之增大。

在非聚集索引中，叶级节点仅包含组成该索引的列中的所有数据以及快速找到相关

数据页上其它数据的指针。当用非聚集索引检索表中与键值匹配的信息时，将搜索整个索引B树，直到在索引叶级找到一个与键值匹配的值。如果需要的列不是组成索引的一部分，则会发生指针跳动，跳到所指向的聚集索引的叶级结点或者堆中。 非聚簇索引适用于具有下列属性的列：(1)主键及外键列。(2)在连接操作中使用的列。(3)查询列中包含GROUP BY或order by 子句。(4)常用于集合函数(如AVG，....)的列，因为可以直接通过索引键值计算需要的结果，不必访问数据块。(5)不返回大型结果集的查询。

非聚集索引由于B树的节点不是具体数据页，有时候可能导致非聚集索引甚至不如扫描表快。但如果要查询的内容，在非聚集索引中被覆盖了，则不需要继续到聚集索引中寻找数据了，这时候可以创建覆盖索引，使索引项中包含查寻所需要的全部信息。如果非聚簇索引中包含结果数据，那么它的查询速度将快于聚簇索引。但由于覆盖索引的索引项比较多，要占用比较大的空间，更新操作会引起索引值改变。所以如果潜在的覆盖查询并不常用或不太关键，则覆盖索引的增加反而会降低性能。 另外，SQL Server 2008增加了筛选索引这一新特性，它使我们可以向索引增加WHERE子句，这样就可以将索引聚焦到被选中的行上，信息更加准确，提高了查询性能。对表更新时，仅在对索引中的数据产生影响时才进行维护，减少了索引维护开销。，创建筛选索引还可以减少非聚集索引的磁盘存储开销。 3 索引使用的误区

通过索引，可以加快数据的查询速度和减少系统的响应时间；可以使表和表之间的连接速度加快。但是，不是在任何时候使用索引都能够达到这种效果。若在不恰当的场合下，使用索引反而会事与愿违。下面谈一下索引使用的误区。 (1)索引多多益善

索引的优点有目共睹，但创建索引和维护索引都需要花费时间与精力。索引是数据库中实际存在的对象，占用一定的物理空间。若索引多了，不但会占用大量的物理

空间，而且也会影响到整个数据库的运行性能。

有些列其数据类型较特殊，如文本类型（TXT）、图像类型（IMAGE）等，如果表中的列属于这些数据类型，则最好不要为其建立索引。这些字段长度不确定，一般是空字符串或者长字符串。若这些列上建立索引，要占用空间和维护困难，反而会降低数据库的整体性能。

(2)只要建立索引就能显著提高查询速度

在“教材调查表”中“是否为新书”列取值只有“0”和“1”，在“是否为新书”列上添加索引就不会显著的增加查询速度。相反，因为需要占用空间，反而会降低数据库的整体性能。教材调查表中共有20万条记录，列“是否为新书”取值为“1”的记录共17万条，在“是否为新书”列上不创建索引和创建非聚集索引查询的速度为：

（a）在“是否为新书”列上不创建任何索引：

select * from dbo.jiaocai where sfxs='1' 用时：13374 ms （b）在“是否为新书”列上创建非聚集索引：

select * from dbo.jiaocai where sfxs='1' 用时：13234 ms

通过例子可以发现，并非在任何字段上简单地建立索引就能提高查询速度。因为此查询返回大量结果集，不仅要读索引页，还要读大部分数据页，所以性能无显著提高。

(3)采用MS SQL SERVER默认的聚集索引

“教材调查表”的ID列为主键，设置为自动增长，步长为1，MS SQL SERVER默认在主键上建立聚集索引，那么数据将按照ID列的值进行排序。但实际上很少根据这个列值对表进行操作，所以表中唯一的聚集索引并不能起到其作用。 4 索引的维护

底层表的数据在添加、更新、删除操作中会产生索引碎片，导致查询速度变慢，需

要对某些重要的表进行定期索引扫描并针对合符要求的索引进行重新组织或重新生成工作。一般碎片>5%并且<=30%时，使用重新组织的方法速度比索引重建快；碎片>30%时，索引重建的速度比重新组织要快。 整理索引碎片的方法如下： (1)重新组织索引

索引碎片不太多时，可以重新组织索引。重新组织索引是通过对叶页进行物理重新排序，使其与叶节点的逻辑顺序相匹配，从而对表或视图的聚集索引和非聚集索引的叶级别进行碎片整理。重新组织还会压缩索引页。采用ALTER INDEX ......REORGANIZE语句重组索引。 (2)重新生成索引

重新生成索引将会删除并重新创建索引。这将根据指定的或现有的填充因子设置压缩页来删除碎片、回收磁盘空间，然后对连续页中的索引行重新排序。这样可以减少获取所请求数据所需的页读取数，从而提高磁盘性能。采用ALTER INDEX ......REBUILD语句重新生成索引。

(3)DROP INDEX 语句删除索引，然后使用一个单独的CREATE INDEX 语句重新创建该索引。通过这种方式重新生成索引，索引将彻底重建。但是这此会阻塞所有的查询，最好是在索引碎片十分严重，并且使用REBUILD无法达到效果的情况下使用。 5 结束语

索引就好像一把双刃剑，即可以提高数据库的性能，也可能对数据库的性能起到反面作用。要建立一个好的索引体系，特别是对聚合索引的创建，更应精益求精，以使数据库能得到高性能的发挥。 参考文献

[1]翟羽佳. 一体化网络下移动性管理的索引结构模型[J]. 电子学报，2009(4)：36-

42.

[2]马亚明等. 空间索引与多尺度表达的一体化模型研究[J]. 武汉大学学报:信息科学 版，2008(12)：1237-1241.

[3]王磊. 索引在查询优化中的作用[J]. 长春理工大学学报 ,2009(2)：181-182. [4]姚徐等. 多级索引的藏语分词词典设计[J]. 计算机应用，2009(S1)：185-187. [5]张庆扬等. 使用二级索引的中文分词词典[J]. 计算机工程与应用，2009(19)：143-145.

[6]应俊等. 一种基于多重索引的大规模数据快速查找算法[J]. 计算机科学，2009(3)：264-266.

[7]王珊，萨师宣. 数据库系统概论[M].4版. 北京：高等教育出版社，2007. [8]严蔚敏，吴伟民. 数据结构(C语言版)[M]. 北京：清华大学出版社，2004.