数据库原理与应用(11)第11章 索引

（2）展开“数据库”|“school”|“表”|“dbo.student”|“索引” 节点，在其下方列出所有已建的索引，如图11.9所示，其中列 出了PK_student（聚集）、IQ_bh（非聚集）和IDX_bhname （非聚集）三个索引名，前者是在创建student表时指定学号为 主键，由SQL Server自动创建的聚集索引，后两个分别是在例 11.1和例11.4中创建的索引。
语法格式如下：
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX 索
引名称
ON { 表名 | 视图名} ( 列名 [ASC | DESC][,…n]) [WITH index_option [,…n]]
[ON [ filegroup | default ]]
说明：当用户创建一个索引被存储到SQL Server 2005 系统中后，每个索引对应sysindexes系统表中一条记录，该 表中name列包含索引的名称。用户可以通过查找该表中的 记录判断某索引是否被创建。
11.3.2 使用CREATE INDEX语句创建索引
可以直接使用CREATE INDEX语句来创建索引，其基本
索引具有下述优点： 提高查询速度。 提高连接、ORDER BY和GROUP BY执行的速度。 查询优化器依靠索引起作用。 强制实施行的唯一性。
由于建立索引需要一定的开销，而且当使用INSERT或者 UPDATE对数据进行插入和更新操作时，维护索引也是需要花费 时间和空间的。因此，没有必要对表中所有的列建立索引。下面 的情况则不考虑建立索引： 从来不或者很少在查询中引用的列。 只有两个或者若干个值的列，例如性别（男或女）。 记录数目很少的表。
一个聚集索引、两 个非聚集索引
（4）为了修改IQ_bh索引的属性，选中IQ_bh索引项，单 击鼠标右键，在出现的快捷菜单中选择“属性”命令，如图 11.10所示，出现如图11.11所示的“索引属性”对话框，在其中 对索引的各选项进行修改，其方法与“新建索引”对话框的操 作类似。
图11.10 图11.11
11.5 删除索引
11.5.1 使用SQL Server控制管理器删除索引 使用SQL Server控制管理器十分容易删除索引 【例11.8】 使用SQL Server管理控制器删除student表上 已建立的IQ_bh索引。 解：其操作步骤如下： （1）启动SQL Server管理控制器。在“对象资源管理器” 中展开“LCB-PC”服务器节点。 （2）展开“数据库”|“school”|“表”|“dbo.student”|“索 引”节点，在其下方列出所有已建的索引，选中IQ_bh索引， 单击鼠标右键，在出现的快捷菜单中选择“删除”命令。 （3）出现“删除对象”对话框，单击“确定”按钮即删 除了IQ_bh索引。
11.4.2 使用T-SQL语句查看和修改索引属性 1. 查看索引信息 为了查看索引信息，可使用存储过程sp_helpindex。其使 用语法如下： EXEC sp_helpindex 对象名 在这里指定“对象名”为需查看其索引的表。
【例11.6】 采用sp_helpindex存储过程查看student表上所 创建的索引。 解：对应的程序如下：
USE school GO EXEC sp_helpindex student GO
2. 修改索引属性
修改索引属性使用ALTER INDEX语句，其基本语法格
式如下：
ALTER INDEX { 索引名 | ALL } ON 表或视图名称
REBUILD [ WITH ( rebuild_index_option ) ]
【例11.3】 给出为student表的班号和姓名列创建非聚集索 引IDX_bhname，并且强制唯一性的程序。 解：对应的程序如下：
USE school --判断是否存在IDX_tno索引,若存在,则删除之 IF EXISTS(SELECT name FROM sysindexes WHERE name='IDX_bhname') DROP INDEX score.IDX_bhname GO --创建IDX_tno索引 CREATE UNIQUE NONCLUSTERED INDEX IDX_bhname ON student(班号,姓名) GO
11.3 创建索引
在创建索引时，需要指定索引的特征。这些特征如下：
聚集还是非聚集索引。 唯一还是不唯一索引。 单列还是多列索引。 索引中的列顺序为升序还是降序。 覆盖还是非覆盖索引。
11.3.1 使用SQL Server控制管理器创建索引 使用SQL Server控制管理器可以对索引进行全面的管理， 包括创建索引、查看索引、删除索引和重新组织索引等。
（8）单击“存储”选项卡，如图11.8所示，该选项卡用于 设置索引的文件组和分区属性。其默设的文件组为 “PRIMARY”（主文件组）。这里不做任何修改，保持默设值。
（9）单击“确定”按钮返回到SQL Server管理控制器， 这样就建立了IQ_bh非聚集索引。此时可以在student表的 “索引”项下面看到新增了“IQ_bh（不唯一，非聚集）”项。
第11章 索引
11.1 什么是索引
索引用于快速访问数据库表中的特定数据，它是对数 据库表中一个或多个列的值进行排序的结构。 索引提供指针以指向存储在表中指定列的数据值，然 后根据指定的排序次序排列这些指针。 数据库使用索引的方式与使用书的目录很相似：通过 搜索索引找到特定的值，然后跟随指针到达包含该值的行。
index_option：指定创建索引的选项，其定义为：
{ PAD_INDEX={ ON | OFF } | FILLFACTOR = fillfactor | IGNORE_DUP_KEY = { ON | OFF } | DROP_EXISTING = { ON | OFF } | STATISTICS_NORECOMPUTE ={ ON | OFF } | SORT_IN_TEMPDB { ON | OFF } }
11.2.2 非聚集索引 一个数据表中只能有一个聚集索引，而表中的每一列上都 可以建立自己的非聚集索引。 非聚集索引与书中的索引类似。数据存储在一个地方，索 引存储在另一个地方，索引带有指针指向数据的存储位置。索 引中的项目按索引键值的顺序存储，而表中的信息按另一种顺 序存储（这可以由聚集索引规定）。
【例11.1】 使用SQL Server管理控制器，在school数据 库中student表的班号列上创建一个升序的非聚集索引IQ_bh。 解：其操作步骤如下： （1）启动SQL Server管理控制器，在“对象资源管理 器”中展开“LCB-PC”服务器节点。
（2）展开“数据库”|“school”|“表”|“dbo.student”|“索 引”节点，单击鼠标右键，在出现的快捷菜单中选择“新建 索引”命令，如图11.2所示。
11.3.3 使用CREATE TABLE语句创建索引 使用CREATE TABLE（或ALTER TABLE）语句创建表 时，如果指定PRIMARY KEY约束或者UNIQUE约束，则SQL Server自动为这些约束创建索引。 其语法参见第8章，这里不再介绍。
11.4 查看和修改索引属性
在索引创建好后，有时需要查看和修改索引属性，其方 法主要有两种：使用SQL Server控制管理器和T-SQL语句。 1.4.1 使用SQL Server控制管理器查看和修改索引属性 使用SQL Server控制管理器十分容易查看和修改索引属 性 【例11.5】 使用SQL Server管理控制器查看school数据 库中student表上已建立的索引。 解：其操作步骤如下： （1）启动SQL Server管理控制器。在“对象资源管理 器”中展开“LCB-PC”服务器节点。
如果一个表中没有创建其他的聚集索引，则在表的主键列 上自动创建聚集索引，如下图所示是student表中主键对应的聚 集索引PK_student。
在创建聚集索引之前，应该先了解数据是如何被访问的。 可考虑将聚集索引用于下面几种情况： 包含大量非重复值的列。 使用下列运算符返回一个范围值的查询：BETWEEN、 >、>=、<和<=。 被连续访问的列。 返回大型结果集的查询。 经常被使用连接或GROUP BY子句的查询访问的列。 一般来说，这些是外键列。对ORDER BY或GROUP BY子句中指定的列进行索引，可以使SQL Server不必 对数据进行排序，因为这些行已经排序。这样可以提 高查询性能。 OLTP（联机事务处理）类型的应用程序，这些程序要 求进行非常快速的单行查找（一般通过主键）。应在 主键上创建聚集索引。
11.2 索引类型
在SQL Server的数据库中按照存储结构的不同将索引分 为两类，即聚集索引和非聚集索引。 11.2.1 聚集索引 聚集索引对表在物理数据页中的数据按列进行排序， 然后再重新存储到磁盘上，即聚集索引与数据是混为一体 的，它的叶节点中存储的是实际的数据。 也就是说在聚集索引中，数据表中记录的物理顺序与 索引顺序相同，即索引顺序决定了表中记录行的存储顺序， 因为记录行是经过排序的，所以每个表只能有一个聚集索 引。
在创建非聚集索引之前，同样需要了解数据是如何被访问 的。可考虑将非聚集索引用于下面的情况：
包含大量非重复值的列，如姓和名的组合（如果聚集索 引用于其他列）。如果只有很少的非重复值，如只有1和 0，则大多数查询将不使用索引，因为此时表扫描通常更 有效。 不返回大型结果集的查询。 返回精确匹配的查询的搜索条件（WHERE子句）中经常 使用的列。 经常需要连接和分组的决策支持系统应用程序。应在连 接和分组操作中使用的列上创建多个非聚集索引，在任 何外键列上创建一个聚集索引。 在特定的查询中覆盖一个表中的所有列。这将完全消除 对表或聚集索引的访问。
（5）设置排序顺序为“升序”，如图11.5所示。单击左上 角的“选项”选项卡。
（6）进入“选项”选项卡，设置结果如图11.6所示。
（7）单击“包含性列”选项卡，如图11.7所示，该选项卡 只对非聚集索引有用。如果在索引键中增加新的列，可单击 “添加”按钮进行操作。这里只对student表的班号列创建非聚 集索引，所以在本选项卡中不做任何操作。
【例11.7】 修改例11.4创建的索引IDX_bhname，将 FILLFACTOR为90。 解：对应的程序如下：
USE school
ALTER INDEX IDX_bhname ON student REBUILD WITH (PAD_INDEX=ON, FILLFACTOR = 90) GO
百度文库
11.5.2 使用T-SQL语言删除索引
删除索引使用DROP INDEX语句，其基本语法格式如下：
DROP INDEX 表名.索引名 【例11.8】 使用DROP INDEX语句删除前面创建的索引
（3）此时，打开“新建查询”对话框，进入“常规”选 项卡，设置如图11.3所示。
（4）设置完成后，单击“添加”按钮开好创建一个新 的索引，出现如图11.4所示的从“dbo.student”中选择列对 话框，从“表列”列表中勾选要建立索引的列，一次可以选 择一列或多列。这里勾选“班号”列，单击“确定”按钮。
【例11.2】 给出在school数据库中的teacher表中的编号列 上创建一个非聚集索引的程序。 解：对应的程序如下：
USE school --判断是否存在IDX_tno索引,若存在,则删除之 IF EXISTS(SELECT name FROM sysindexes WHERE name='IDX_tno') DROP INDEX teacher.IDX_tno GO --创建IDX_tno索引 CREATE INDEX IDX_tno ON teacher(编号) GO