数据库索引研究_于绍娜

2010年2月第2期

电子测试

ELECTRONIC TEST

Feb.2010

No.2

数据库索引研究

于绍娜1，李霞丽1，胥桂仙1，杨智君2

(1. 中央民族大学，北京 100081；2. 中国计量科学研究院，北京 100013)

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

关键词：聚集索引；非聚集索引；筛选索引；B树

中图分类号：TP311 文献标识码：A

Study on database index

Yu Shaona1, Li Xiali1, Xu Guixian1, Yang Zhijun2

(1. Minzu University of China, Beijing 100081;2. National Institute of Metrology P.R.China, Beijing 100013)

Abstract: In the application of database system, data query is done frequently. Index is a disk structure associating with table or view. Using index in database effectively, it’s good for finding information fast in the table or view and reducing system’s answering time. In this paper, we introduce classify, using and maintenance of index. In addition, we analysis and practice the index in MS SQL SERVER.

Keywords: clustered index；nonclustered index；Filtered Indexes；B tree

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）等，如果表中的列属于这些数据类型，则最好不要为其建立索引。这些字段长度不确定，一般是空字符串或者长字符串。若这些列上建立索引，要占用空间和维护困难，反