第10章 索引及其应用

10.3.3考虑创建索引的列
10.2.3 删除索引
【例10-3】删除“stuinfo”上的“XH_学 号”索引，可执行如下语句： USE cjgl DROP INDEX stuinfo.XH_学号 执行这个语句，则在查询窗口中将显示“ 命令已成功完成”，如图10-27所示。 “stuinfo”上的“XH_学号”索引将被删除 ，用户可用前面介绍的方法查看结果。

10.2.1 创建索引
图10-17 【例10-2】创建索引结果
10.2.1 创建索引



以上SQL语句代码中，UNIQUE关键字代表创建惟一性 索引，CLUSTERED关键字代表创建聚集索引(该关键 字不可以省略，因为SQL Server 默认创建的是非聚集 索引)，“KZM_课程名”是用户自定义的索引名。 用户不能在一个表上创建多个聚集索引。如果创建过聚 集索引，将给出不能创建的显示消息。如本例中事先创 建过一个名为„PK_classinfo‟的聚集索引，执行时将显 示如下消息： “不能在表„classinfo‟表上创建多个聚集索引。请在创 建新聚集索引前除去现有的聚集索引„PK_stuinfo‟”。
10.3.1 创建索引的理由
1.大大加快数据的检索速度; 2.创建唯一性索引，保证数据库表中每一行 数据的唯一性; 3.加速表和表之间的连接; 4.在使用分组和排序子句进行数据检索时， 可以显著减少查询中分组和排序的时间

10.3.2不在每一列上建索引的理由
1.索引需要占物理空间。 2.当对表中的数据进行增加、删除和修 改的时候，索引也要动态的维护，降低 了数据的维护速度。

10.1.2 索引
4．复合索引(Compound Index) 如果需要对多个字段的组合创建索引， 即一个索引中含有多个字段，可以建立 复合索引。一个复合索引中最多可以有 16个字段组合，并且复合索引中的所有 字段必须在同一个表中。

10.2 操作索引
10.2.1 10.2.2 10.2.3
10.2.2查看索引
2．使用Transact-SQL语句查看索引
Exec sp_helpindex table_name
10.2.3 删除索引


2．使用Transact-SQL语句删除索引 DROP INDEX语句用来删除不再需要的索引 。 执行DROP INDEX后，SQL Server将重新获 得以前由索引占用的空间。此后可将该空间用 于任何数据库对象。 DROP INDEX语句的语法如下： DROP INDEX table. index [,...n]
10.1.2 索引


2．非聚集索引(Nonclustered Index)
非聚集索引与聚集索引不同，它具有完全独立于数据行 的结构，使用非聚集索引不会影响数据表中记录的实际 存储顺序。因为，它并不在物理上排列数据，即索引中 的逻辑顺序并不等同于表中行的物理顺序。索引仅仅记 录指向表中行的位置的指针，这些指针本身是有序的， 通过这些指针可以在表中快速地定位数据。 非聚集索引使用索引存储，因此它比聚集索引需要较少 的存储空间。聚集索引中行的物理排序与索引排序相同 ，并且聚集索引的最低一级包含实际的数据行。非聚集 索引中各行的物理顺序与键值的逻辑顺序不匹配。

10.1.2 索引
创建非聚集索引应该注意以下事项： SQL Server允许一个表上最多可以有249 个非聚集索引。 为一个表建立索引默认都是非聚集索引。 在一列上设置惟一性约束时也自动在该列 上创建非聚集索引 .

10.1.2 索引
3．惟一索引(Unique Index) 惟一索引既可以采用聚集索引的结构，又可 以采用非聚集索引的结构，被特殊地创建成 惟一的。插入到键值列或列组的值必须是惟 一的，不允许有重复的键值。对于复合键， 这意味着值的组合必须是惟一的。
10.1.2 索引
另外，还可以通过创建UNIQUE约束的方法强制特定 列的数据的惟一性。当创建UNIQUE约束时，SQL Server自动地创建一个惟一非聚集索引以强制该约束 。一旦你在一个表上拥有惟一索引，SQL Server会 阻止向表中输入相同的数据，并将给出一条错误消息 ，指出试图插入相同的行。 所以，创建惟一性索引时，应注意如下事项： 创建惟一性索引时，应保证创建索引的列不包括重复 的数据，并且没有两个或两个以上的空值。因为创建 索引时将两个空值也视为重复的数据，所以如果有这 种数据，则必须先将其删除，否则索引不能成功创建 。
创建索引 查看索引 删除索引
10.2.1 创建索引


2．使用Transact-SQL语句创建索引 利用Transact-SQL语句中的CREATE INDEX命令可以 创建索引，既可以创建一个可改变表的物理顺序的聚集 索引，也可以创建提高查询性能的非聚集索引，该语法 如下： CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ ,...n ] ) [ WITH < index_option > [ ,...n] ] [ ON filegroup ] 。
10.1.2 索引

1．聚集索引(Clustered Index) 聚集索引，对表中的数据按列进行排序，然后再重新存储 到磁盘上。是一种指明表数据物理存储顺序的索引。在聚 集索引中，行的物理存储顺序与索引顺序完全相同，即索 引的顺序决定了表中行的存储顺序。表数据按照指定作为 聚集索引的一个或多个键列排序并存储。聚集索引类似于 一本字典，字典按照字母顺序存储信息，并提供引导字帮 助用户快速定位信息的位置，在同一页上找到数据和它的 引导字。在表中的数据行只能以一种排序方式存储在磁盘 上。所以一个表只能有一个聚集索引。
10.2.1 创建索引
【例10-1】在“cjgl”的“stuinfo”表中创建一个惟
一性的非聚集索引“XH_学号”，具体语名如下： USE cjgl CREATE UNIQUE NONCLUSTERED INDEX XH_学号 ON stuinfo(学号) 执行这个语句，在查询窗口中将显示“命令已成功 完成”，如图10-16所示。它表明索引“XH_学号” 已被创建。
10.2.1 创建索引
图10-16 【例10-1】创建索引结 果
10.2.1 创建索引

以上SQL语句代码中，UNIQUE关键字代表创建惟一 性索引，NONCLUSTERED关键字代表创建非聚集索 引(该关键字可以省略，因为SQL Server 默认创建非 聚集索引)，“XH_学号”是用户自定义的索引名。
10.2.1 创建索引
【例10-2】 在“cjgl”的“classinfo”表中创建一个惟一 性的聚集索引”KZM_课程名”，具体语句如下： USE cjgl CREATE UNIQUE CLUSTERED INDEX KZM_课程名 ON classinfo(课程名) 执行这个语句，在查询窗口中将显示“命令已成功 完成”。它表明索引“KZM_课程名”已被创建。
10.1.1 数据存储

索引提供指向存储在表的指定列中的 数据值的指针，然后根据用户指定的 排序顺序对这些指针排序。合理地利 用索引，将大大提高数据库的检索速 度，同时也可以提高数据库的性能。 索引既可在定义表时创建，也可以在 定义了表之后随时创建。
10.1.1 数据存储

SQL Server索引的结构一般是一个B树，B树结构以 一个根节点开始，这个根节点是索引的起始点，如图 10-1所示。根节点包含索引行(索引数据行)，索引行含 有索引键值的范围(A～H)和指向下一个索引节点—— 分支节点(A～D，E～H)的指针。分支节点依次含有索 引行，它们带有指向其他分支节点的更细化的值(A～B ，…，G～H)。每级分支节点被称为一个索引级别。 在B树最底层的节点称为叶子节点。叶子节点含有索引 键数据(A，…，H)加上被引用数据位置或数据自身的 信息，这主要依赖于索引是非聚集的还是聚集的(这两 种索引类型在下一小节介绍)。到达一个叶子节点必须 经过的索引级数，决定了找到需要的数据行所必需的 I/O数量。
10.1.2 索引


由于聚集索引的顺序与数据行存放的物理顺序相同， 因此，聚集索引最适合于范围搜索。因为相邻的行将 被物理地存放在相同的页面上或相邻近的页面上，所 以当在用于查找一定范围值的列上创建聚集索引时， 聚集索引非常有效。 例如，在图书馆中存放着很多书，这些书可以按照作 者顺序存放，也可以按照书名顺序存放，还可以按照 书的出版社排序存放。假设现在这些书是杂乱存放的 ，并且在书名列上建立了聚集索引，那么这些书就必 须按照书名的顺序重新排放，使得数据(书)按照索引 排序。这就是聚集索引。
图10-1 索引示例
10.1.1 数据存储
索引行 根 节 点
索引行
索引行 …
索引行
索引行
分 支 节 点 数据 … 数据
数据 … 数据
数据 … 数据
数据 … 数据
叶子节点

图10-2 索引结构
10.1.2 索引


SQL Server索引有4种类型，即聚集索引、非聚集索引 、唯一索引及复合索引。其中，聚集索引和非聚集索引 是两种主要的索引类型。对于这两种索引类型之中的任 何一种索引，你都可以指定该索引是否应该是一个唯一 索引(实际上它是一种特殊的聚集或非聚集索引)。 下面将分别介绍这几种索引。
10.1.2 索引


创建聚集索引应该注意以下事项： 每张表只能有一个聚集索引。 创建聚集索引时所需的可用空间应是数据库表 中数据量的120%。这是因为在创建聚集索引 时表中的数据将被复制以便进行排序，排序完 成后，再将旧的未加索引的表删除，所以数据 库必须有足够的用来复制的空间。 主键是聚集索引的良好候选者。 缺省设置是非聚集索引。