第七章  索  引

数据库的索引类似于书籍中的目录。目录使用户不必翻阅整本书就能迅速找到所需要的信息，同样索引可以使数据库程序迅速找到数据库中的数据，而不必扫描整个数据库。这就大大节省了SQL Server查找数据的时间，从而提高了工作效率。

本章将介绍索引的概念和用法，包括以下内容：

n         索引的概念和类型

n         如何创建索引

n         如何创建索引选项

n         如何维护索引

7.1  概  述

创建索引可以大大提高数据库的性能，主要有以下原因：

n         创建了唯一性索引后，可以保证每行数据的唯一性。

n         创建索引可以大大提高数据库的检索效率。

n         在实现数据的参考完整性方面有很重要的意义，可以加速表与表之间的连接。

n         在使用ORDER BY和GROUP BY子句进行数据检索的，可以减少分组和排序的时间。

不过，虽然使用索引可以大大提高数据库的性能，但也不能对每一列都建立索引，因为创建索引和维护索引要耗费大量的时间和财力，并且，如果对表中的数据进行增加、删除和修改的时候，也要对索引进行维护，这也会降低数据库的维护速度。另外，使用索引要占用物理空间。

索引的结构为如图7-1所示的B-tree结构。B-tree一般分为三部分：根页（Root）、中间页（Intermediate）和末页（Leaf）。

图7-1  索引结构

在Microsoft SQL Server 2000中，根据索引的顺序与数据表的物理顺序是否相同，索引可以分为两种类型：簇索引和非簇索引。簇索引数据表的物理顺序和索引相同；非簇索引数据表的物理顺序和索引顺序不相同。

簇索引的结构如图7-2所示。

图7-2  簇索引结构图

簇索引的索引页和数据页构成了一个平衡树，索引的最低级是页级，页级的顺序是升序。簇索引应该创建在表中经常搜索的列。

创建簇索引的时候，应该考虑以下因素：

n         每个表只能有一个簇索引。

n         创建簇索引大约需要1.2倍的表空间，所以，要保证足够的磁盘空间。

n         表中行的顺序和索引中行的顺序是相同的。

在下列情况下，可以考虑建立簇索引：

（1）查询命令的回转结果是以该字段为排序顺序的

如果要检索表orders中的数据，并且以OrderID作为排序条件来返回数据，这时，可以考虑以OrderID为关键字建立簇索引，语法形式如下：

SELECT * FROM orders

ORDER BY OrderID

（2）表格中某个字段内容的重复性比较大

如果表格中某个字段的重复性比较大，可以考虑以此字段为关键字建立簇索引。比如，Score1和Score2是两个班级学生的成绩统计表，两个表中均有字段Score，表示成绩，由于成绩出现重复性的可能性比较大，以Score为关键字建立索引，语句如下：

SELECT * FROM Score1，Score2

WHERE Score1.Score=Score2.Score

（3）返回局部范围的大量数据

如果需要返回局部范围的大量数据，比如，现在需要返回orders表中OrderID在10269gc 10369之间的数据，就可以建立关于查询字段的簇索引。如下：

SELECT * FROM orders

WHERE OrderID>10269 AND OrderID<=10369

非簇索引结构如图7-3所示。

图7-3  非簇索引结构示意图

非簇索引结构一般分为四层：索引根层、索引中间层、索引末页和数据存储页，比簇索引多一层，所以执行速度没有簇索引快。

创建非簇索引的时候，应该考虑以下因素：

n         默认情况下，创建的索引是非簇索引。

n         每个表上可以创建的非簇索引最多为249个。

在下列情况下，可以考虑建立非簇索引：

（1）如果某个字段的数据唯一性比较高，可以考虑建立非簇索引。

（2）如果查询所得到的数据量比较少，可以考虑建立非簇索引。

@        如果在同一个表中既要建立簇索引，又要建立非簇索引，应该先建立簇索引，然后建立非簇索引。如果先建立非簇索引，在建立簇索引的时候，系统会自动删除非簇索引。

7.2  创建索引

在SQL Server 2000中，有以下两种创建索引的方法：

n         使用Transact-SQL语句建立索引。

n         使用Enterprise Manager建立索引。

7.2.1  使用Transact-SQL语句创建索引

使用Transact-SQL创建索引的语法形式如下：

CREATE [ UNIQUE ][ clustered|nonclustered ]

INDEX index_name

ON[ [ database.]owner. ] table_name(column_name1 [,column_name2，…])

[WITH

[FILLFACTOR=fillafctor]

[[，] I GNORE_DUP_DEY]

[[，]{SORTED_DATA | SORTED_DATA_REORG}]

[[，]{IGNORE_DUP_ROW | ALLOW_DUP_ROW}]]

[ON segment_name]

其中：

n         UNIQUE：使用该选项，则建立索引的字段上不允许有相同的记录。如果有两条或两条以上相同的记录，则在执行CREATE UNIQUE INDEX的时候会发生错误。

n         clustered：如果要建立簇索引，必须指定该选项，默认情况下，SQL Server 2000建立的是非簇索引。

n         nonclustered：指定建立非簇索引，最多可以建立249个非簇索引。

n         database：数据库名称。

n         owner：表格拥有者名称，确定索引的使用权限是表格的拥有者，不能转移给其他使用者使用。

n         index_name：索引名称。

n         table_name：建立索引所在的表名称。

n         column_name：字段名称。

n         FILLFACTOR：索引储存页的填充率。指定索引存储页保留的可利用空间，如果没有指定，SQL Server会用sp_configure命令设置系统的缺省fillfactor值，大小为0。如果是只读表，FILLFACTOR的值一般设置为100％。可以使用下面的语句查询FILLFACTOR的大小：

n         SELECT index_name，OrigFillFactor FROM sysindexes

n         IGNORE_DUP_KEY和INSERT、UPDATE配合使用的功能如表7-1所示。

n         SORTED_DATA | SORTED_DATA_REORG指定这两个选项之一，则在建立簇索引的时候不排列表中的数据。但SQL Server会对表中的数据进行检查，如果不符合要求，簇索引的建立就会失败。使用SORTED_DATA的执行速度较快，因为它不拷贝数据和重建非簇索引，执行SORTED_DATA_REORG的时候，会重建表格使数据连续存放，并保留存储页的可用空间。

表7-1  INSERT和UPDATE作用表

如果没有指定SORTED_DATA或SORTED_DATA_REORG选项，表格中的数据会自动重建。可以使用DBCC SHOWCONTIG命令查询表格中数据的情况，语法形式如下：

DBCC SHOWCONTIG（table_id，[index_id]）

其中，table_id是表格号码，index_id是索引号码。

n         INGORE_DUP_ROW|ALLOW_DUP_ROW：选择是否建立非唯一值簇索引。命令语句和索引类型如表7-2所示。

n         ON segment_name：指定建立索引的区段。

表7-2  命令语句与索引的对应关系

例如，在orders表的OrderID列上建立名为order_index的非簇索引，填充率为50%，代码如下：

CREATE nonclustered

INDEX order_index

ON orders（OrderID）

WITH

FILLFACTOR＝50

7.2.2  使用SQL Server Enterprise Manager创建索引

使用SQL Server Enterprise Manager创建索引的操作如下：

（1）运行SQL Server Enterprise Manager。

（2）选择要建立索引的表，然后单击鼠标右键，弹出一个快捷菜单，选择“所有任务”，展开其子菜单，如图7-4所示。

（3）选择Manage Indexes菜单项，打开如图7-5所示的对话框。

（4）在该对话框中，可以选择数据库和表为之创建索引。另外，也可以编辑索引、删除索引。

（5）如果要创建索引，在Database和Table/View下拉式列表中选择要创建的表所在的数据库和表。然后单击New按钮，打开如图7-6所示的窗口。

（6）在Index Name文本框中键入新索引的名称，并在字段列表中选择要创建索引的关键字。如果需要，可以在Index Options区进行高级设置。

（7）设置完成后，单击OK按钮。就可以建立需要的索引。

图7-4  所有任务子菜单

图7-5  Manage Indexes窗口

图7-6  Create New Index窗口

7.3  更改索引名称

有时候，为了操作方便，需要更改索引的名称，在SQL Server 2000中，更改索引的名称有以下两种方法：

n         使用Transact-SQL语句更改索引名称。

n         使用SQL Server Enterprise Manager更改索引名称。

7.3.1  使用Transact-SQL语句更改索引名称

更改索引名称的语句如下：

EXEC sp_raname Oldanme，Newname[，column]

其中，Oldname是需要更改的索引原来的名称：Newname是索引更改以后的名称；column用于对字段进行更改的情况。

下面的例子把authors表中的索引名aunmind改为mind：

EXEC sp_rename‘authors.aunmind’，‘mind’

7.3.2  使用Enterprise Manager更改索引名称

在图7-5所示窗口中，选择需要更改名称的索引，然后单击Edit按钮，打开如图7-7所示窗口，在Index Name框中键入新的名称。然后单击OK按钮即可。

图7-7  编辑索引

7.4  删除索引

删除索引也有以下两种方法：

n         使用Transact-SQL语句删除索引。

n         使用SQL Server Enterprise Manager删除索引。

7.4.1  使用Transact_SQL语句删除索引

删除索引的语句如下：

DROP INDEX[owner.]table_name.index_name

[，[owner，]table_name.index_name…]

其中，owner是表格拥有者名称：table_name是索引所在表格的名称；index_name是索引的名称。

下面的例子删除表authors中的索引aunmind：

DROP INDEX authors.aunmind

7.4.2  使用Enterprise Manager删除索引

在图7-5所示的窗口中，选中需要删除的索引，然后单击Delete按钮，弹出如图7-8所示的对话框。

图7-8  确认删除索引窗口

单击“是”按钮删除选中的索引，单击“否”按钮取消删除操作。

7.5  创建索引选项

Microsoft SQL Server 2000提供了一些创建索引的选项，包括FILLFACTOR、PAD_INDEX、SORTED_DATA_REORG。使用这些选项可以加快索引的创建速度并增强索引的性能。下面分别介绍这些选项。

7.5.1  FILLFACTOR选项

使用FILLFACTOR选项，可以优化INSERT语句和UPDATE语句，并在页级索引页上分配一定百分比的自由空间，以减少页的分解时间。

使用FILLFACTOR选项，应该考虑以下方面：

n         填充度的取值范围是1％到100％，默认情况下，填充度为0。

n         使用系统存储过程sp_configure可以改变填充度的默认值。

n         填充度的值存储在表sysindexes中。

n         填充度表示页级应该填充多少。

n         在在线事务处理环境中，使用较低的填充度。

n         在数据存储或者决策支持环境中，使用较高的填充度。

7.5.2  PAD_INDEX选项

PAD_INDEX选项指定非页级索引页的填充度。只有在使用FILLFACTOR选项的时候，才能使用PAD_INDEX。

使用PAD_INDEX选项，应该考虑以下方面：

n         FILLFACTOR选项指定的值应用于页级页和非页级页。

n         默认情况下，SQL Server系统在非页级页保留可以容纳两行的空间。

下面的例子在authors表的字段titile上创建一个索引title_index，指定FILLFACTOR选项和PAD_INDEX选项使用SQL Server系统在页级和非页级占10％。

USE pubs

CREATE INDEX title_index

ON title（authors）

WITH PAD_INDEX，FILLFACTOR，DROP_EXISTING

7.5.3  SORTED_DATA_REORG选项

SORTED_DATA_REORG清除字段排序，可以减少创建索引的时间。

在使用SORTED_DATA_REORG选项的时候，应该考虑以下方面：

n         创建全部非簇索引。

n         后一个关键值是否比前一个关键值高，如果不高，则不能创建索引。

n         SQL Server需要1.2倍的表空间来组织数据。

n         删除行之后，其所占的空间可以重新使用。

n         可以同时使用FILLFACTOR和SORTED_DATA_REORG选项把页级页填充到一定的百分比。

下面是一个使用SORTED_DATA_REORG的例子。

CREATE CLUSTERED INDEX score_index

ON score（English，Chinese）

WITH SORTED_DATA_REORG

7.6  维护索引

为了维护索引的性能，在索引创建之后，还要定期进行维护，本节主要介绍索引维护方面的知识。

7.6.1  DBCC SHOWCONTIG语句

DBCC SHOWCONTIG语句可以显示表的数据和索引信息。如果对表做了大量的修改或增加了许多数据之后，应该执行DBCC SHOWCONTIG命令，其语法形式如下：

DBCC SHOWCONTIG（table_id[，index_id]）

在执行DBCC SHOWCONTIG语句的时候，应该考虑以下因素：

 

n         在执行DBCC SHOWCONTIG语句的时候，需要指定表的ID号和索引的ID号，这些ID号保存在sysindexes表中。

n         需要确定DBCC SHOWCONTIG语句的周期。

7.6.2  DBCC DBREINDEX语句

使用DBCC DBREINDEX语句可以重建表的一个或多个索引。如果表上有PRIMARYKEY或者UNIQUE关键字，也可以执行DBCC DBREINDEX语句。在使用DBCC DBREINDEX的时候，应该考虑以下因素：

n         DBCC DBREINDEX不支持系统表。

n         使用Database Maintenance Plan可自动进行索引重建。

n         可以使用DBCC DBREINDEX重建PRIMARY KEY约束或者UNIQUE约束。

DBCC DBREINDEX语句的语法形式如下：

DBCC DBREINDEX（[‘database.owner.table_name’[，index_name[，fillfactor]]]）

下面的例子重建表score 的全部索引：

USE student

DBCC DBREINDEX（score）

7.6.3  索引统计

当表中的数据发生变化时，SQL Server系统会周期性地修改统计信息。索引统计信息也被修改。另外，也可以通过UPDATE STATISTICS语句或者sp_updatestats系统存储过程手动修改统计信息。

UPDATE STATISTICS语句的语法形式如下：

UPDATE STATISTICS{table}[index[，…]]

下面的例子修改表score上的所有索引的统计信息：

USE student

UPDATE STATISTICS score

下面的例子修改表score中的索引chinese_index的统计信息：

USE student

UPDATE STATISTICS score chinese_index

7.6.4  索引分析

使用SHOWPLAN语句和STATISTICS IO进行索引分析和查询。SHOWPLAN语句的语法形式如下：

SET SHOWPLAN_ALL{ON|OFF}

SET SHOWPLAN_TEXT{ON|OFF}

使用SHOWPLAN语句的时候，应该考虑以下因素：

n         SET SHOWPLAN_ALL语句可以返回比SET SHOWPLAN_TEX更加详细的信息，并且它的输出结果能够接受应用程序的处理。

n         SHOWPLAN_ALL语句的输出信息只在一个会话期内有效。

STATISTICS IO语句表明输入输出的数量信息。其语法形式如下：

SET STATISTICS IO{ON | OFF}

7.7  小  结

本章介绍了索引的概念、类型、特征和用法。通过本章的学习，应该掌握下列内容：

n         索引可以加快数据的检索速度，但降低了数据维护的速度。

n         索引有两种类型，即簇索引和非簇索引。

n         在簇索引中，数据的物理顺序与索引顺序相同，每一个表最有一个簇索引。

n         在非簇索引中，数据的物理顺序与索引顺序不相同，每一个表最多有249个非簇索引。

n         使用Transact-SQL语句建立和编辑索引。

n         使用Enterprise Manager建立和编辑索引。