第10章 索引的使用

第10章 索引的使用

在关系型数据库中，索引是一种可以加快数据检索速度的数据库结构，其主要用于提高操作的性能。索引可以不需要检索整个数据库，就能够从大量的数据记录中迅速地找到所需要的数据，从而增强了检索的效率。

10.1 索引概述
在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。简单来说，索引是某个表中一列或者若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

10.1.1 索引介绍
在理解索引的概念和用途时，可以将索引看作书籍的目录。索引与书籍的目录或标签的作用是一样的。用户在使用书籍时，能够通过目录迅速地找到所要查看的内容，从而得到所需要的信息。如果把数据库中的数据看作是书籍中的内容，那么索引就是书籍的目录。索引是数据库中表的关键字，其指向表中每一行的数据。当要查找指定的数据块时，索引可以作为一个逻辑指针指向它的物理位置。
数据库中的索引是某个表中一列或者若干列值的集合。索引是一个独立的、物理的数据库结构。索引一般是与表或视图中的列相关联，数据库用户可以利用索引快速地访问数据表中的数据信息。
通常情况下，只有在需要经常查询索引列中的数据时，才在表上创建索引。因为索引的使用会占用磁盘的空间，并且降低添加、删除和更新数据行的速度。所以如果应用程序需要频繁地更新数据，或磁盘空间有限，那么最好不要在数据表中创建索引。

10.1.2 索引的分类
在SQL Server中，按存储结构的不同将索引分为两类：聚集索引（Clustered index，也称聚类索引、簇集索引）和非聚集索引（Nonclustered index，也称非聚类索引、非簇集索引）。
（1）聚集索引
聚集索引基于聚集索引键按顺序排序和存储表或视图中的数据行。即对表的物理数据页中的数据按索引的字段排列进行排序，然后再重新存储到磁盘上。聚集索引与数据是混为一体的，它的叶节点中存储的是实际的数据。
例如，有一个Books表，为BookID字段建立聚集索引，其典型结构可表示为图10.1所示。

图10.1 聚集索引的结构
因为表的数据行只能以一种排序方式存储在磁盘上，所以一个表只能有一个聚集索引。由于聚集索引对表中数据存放的位置一一进行了排序，因此使用聚集索引查找数据很快。
（2）非聚集索引
非聚集索引具有独立于数据行的结构。非聚集索引包含非聚集索引键值，并且每个键值项都有指向包含该键值的数据行的指针。 从非聚集索引中的索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页是存储在堆中还是聚集表中。对于堆，行定位器是指向行的指针。对于聚集表，行定位器是聚集索引键。
与使用书中索引的方式相似，查询优化器在搜索数据值时，先搜索非聚集索引以找到数据值在表中的位置，然后直接从该位置检索数据。这使非聚集索引成为完全匹配查询的最佳选择，因为索引包含说明查询所搜索的数据值在表中的精确位置的项。
同样，对Books表中的BookID字段建立非聚集索引，其典型结构可表示为图10.2所示。

图10.2 非聚集索引的结构
可以对表或索引视图创建多个非聚集索引，每个表的非聚集索引数最多不超过249个。通常，创建非聚集索引是为了提高聚集索引未包含的常用查询的性能。
（3）唯一索引
唯一索引表示表中每一个索引值只对应唯一的数据记录，这与表的PRIMARY KEY的特性类似，因此唯一性索引常用于PRIMARY KEY的字段上，以区别每一笔记录。
当表中有被设置为UNIQUE的字段时，SQL Server会自动建立一个非聚集的唯一性索引。而当表中有PRIMARY KEY的字段时，SQL Server会在PRIMARY KEY字段建立一个聚集索引。
10.1.3 索引的作用
在数据库中，索引主要有下面的一些作用。

• 通过创建唯一索引，可以保证数据记录的唯一性。
• 可以大大加快数据检索速度。
• 可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。
• 在使用ORDER BY和GROUP BY子句进行检索数据时，可以显著减少查询中分组和排序的时间。
  使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。

10.1.4 索引的结构
一个数据库索引就是一个树形结构。每一个有索引的页面都被称之为一个索引页或者一个索引节点。索引结构的最高级别是根节点。根节点用于声明索引的开始，它是数据搜索需要访问的第一个数据。根节点拥有许多索引行。这些索引行包含一个关键字数值和一个指针，一个索引由成百上千个索引页组成，索引的结构如图10.3所示。

图10.3 索引的结构
每一个索引行都指向另外一个树枝节点或者叶节点。与根节点不同，在同一个级别中，每一个枝节点都与另外一个枝节点有关联，如图10.4所示。

图10.4 叶节点

由于索引的结构是树形，树形结构的同一个级别的每一个组都有树枝节点。如果数据表中包含很少的数据，那么根节点可以直接和叶节点相连接，索引不需要树枝节点。
10.2 索引的创建及使用
在了解索引的概念及作用之后，就可以开始创建索引了。在SQL中，通过CREATE INDEX语句来创建索引。下面就讲解一下创建索引的过程。
10.2.1 创建索引的语法结构
在Transact-SQL中，建立索引的语句是CREATE INDEX。
CREATE INDEX语句的语法结构如下所示。

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON table_or_view_name
( column [ ASC | DESC ] [ ,...n ] )
[with
[PAD_INDEX = { ON | OFF }]
[[,]FILLFACTOR=fillfactor][[,]IGNORE_DUP_KEY = { ON | OFF }]
[[,]DROP_EXISTING = { ON | OFF }]
      [[,]STATISTICS_NORECOMPUTE = { ON | OFF }]
[[,]SORT_IN_TEMPDB = { ON | OFF }]
]
[ ON filegroup ]

上述代码中的各项参数说明如下所示。

• UNIQUE表明为表或视图创建唯一索引。唯一索引不允许两行具有相同的索引键值，视图的聚集索引必须唯一。
• CLUSTERED表示为表或视图建立聚集索引。在创建任何非聚集索引之前创建聚集索引，因为创建聚集索引时会重新生成表中现有的非聚集索引。如果没有指定
  CLUSTERED，则创建非聚集索引。
• NONCLUSTERED表示为表或视图建立非聚集索引。无论是使用PRIMARY KEY和UNIQUE约束隐式创建索引，还是使用CREATE
  INDEX显式创建索引，每个表都最多可包含249个非聚集索引。
• index_name为索引的名称，索引名称在表或视图中必须唯一，但在数据库中不必唯一。
• column为索引所基于的一列或多列。指定两个或多个列名，可为指定列的组合值创建组合索引。在table_or_view_name后的括号中，按排序优先级列出组合索引中要包括的列。

说明：组合索引是将两个字段或多个字段组合起来建立的索引，而单独的字段允许有重复的值。

• ASC | DESC确定特定索引列的升序或降序排序方向，默认值为ASC。

PAD_INDEX用于指定索引中间级中每个页（节点）上保持开放的空间。
FILLFACTOR = fillfactor用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，fillfactor的值为1到100，默认值为0。如果fillfactor为100或0，则数据库引擎会创建叶级页的填充已达到其容量的索引，但在索引树的较高级别中预留空间，以容纳至少一个额外的索引行。

IGNORE_DUP_KEY用于控制当往包含于一个唯一聚集索引中的列中插入重复数据时SQL Server所做的反应，默认值为OFF，即发出错误消息，并回滚整个INSERT事务。
DROP_EXISTING用于指定应删除，并重新创建已命名的先前存在的聚集索引或者非聚集索引，默认值为OFF，即如果指定的索引名已存在，则会显示一条错误。
STATISTICS_NORECOMPUTE用于指定过期的索引统计不会自动重新计算。
SORT_IN_TEMPDB用于指定创建索引时的中间排序结果将存储在tempdb数据库中。默认值为OFF，即中间排序结果与索引存储在同一数据库中。
ON filegroup用于指定存放索引的文件组。
创建索引时，索引键的最大大小为900字节。如果为varchar列创建索引时，varchar列中的现有数据未超过900字节，则可以对这些列创建超过900字节的索引；但是，以后在这些列上进行插入或更新操作时，如果导致总大小超过900字节，则会引发操作失败。

10.2.2 创建唯一索引
本节中将通过一个示例来讲解唯一索引的创建和使用。
【上机实战】
在员工信息表（T_EMPLOY）中，创建一个索引“IND_EMPLOY”，为了便于比较，首先查询员工信息表（T_EMPLOY）中的数据信息，如下面的代码所示。

SELECT * FROM T_EMPLOY

代码执行以后，其数据信息如图10.5所示。

图10.5 员工信息表（T_EMPLOY）中的数据信息
此时向员工信息表（T_EMPLOY）中的插入员工编号（E_ID）已经存在的数据记录，如下面的代码所示。

INSERT INTO T_EMPLOY(E_ID,E_NAME,E_BM) VALUES('1010','刘刚','信息部');
SELECT * FROM T_EMPLOY

代码执行以后，查询员工信息表（T_EMPLOY）中的数据记录如图10.6所示。

图10.6 插入重复记录后的数据表记录
通过下面的语句将员工编号（E_ID）设置为聚集索引。

CREATE UNIQUE INDEX IND_EMPLOY
ON T_EMPLOY(E_ID);

代码执行以后，索引创建完成。
注意：在创建索引之前，应该将数据表中编号重复的记录信息删除，否则将无法创建索引。
创建完索引之后，通过下面的代码再次向员工信息表（T_EMPLOY）中插入员工编号重复的数据记录，如下面的代码所示。

INSERT INTO T_EMPLOY(E_ID,E_NAME,E_BM) VALUES('1003','刘刚','信息部');
SELECT * FROM T_EMPLOY

代码执行以后，将弹出如图10.7所示的错误提示信息，索引在创建聚集索引之后，不可以再向创建索引的数据表中插入重复数据记录。

图10.7 提示违反唯一约束条件的提示对话框

说明：如果对表中的某列定义了UNIQUE约束，则系统自动为该列建立唯一非聚集索引。

10.2.3 创建多字段索引
复合索引表示的是表中包含多个索引列。
【上机实战】
创建符合索引IND_FH_EMPLOY，使得在查询员工信息表（T_EMPLOY）时，使查询结果能够同时按照员工编号（E_ID）和员工姓名（E_NAME）进行查询,其实现的代码如下所示。

CREATE INDEX IND_FH_EMPLOY
ON T_EMPLOY(E_ID,E_NAME);

通过上述语句查询数据时，同时查询员工编号（E_ID）和员工姓名（E_NAME），这样就能够提高查询数据的效率。语句执行后，复合索引被创建完成。
10.3 删除索引
在SQL中使用DROP INDEX语句可以达到删除索引的目的。
【语法说明】
DROP INDEX语句的语法格式如下所示。

DROP INDEX table_name.index_name

语句中table_name表示要删除索引所在的数据表名称，index_name表示要删除的索引名称。

注意：在SQL Server数据库中，创建主键（PRIMARY KEY）或者惟一（UNIQUE）约束后，SQL Server会自动为其表创建索引，在删除这些索引之前，必须先删除主键或惟一约束。

【上机实战】
删除本章10.2.2节中创建的索引“IND_EMPLOY”。

DROP INDEX IND_EMPLOY

语句执行以后，索引“IND_EMPLOY”被删除。