1
第十章 管理索引
? 了解索引的概念和作用
? 了解索引的类型
? 使用企业管理器创建和管理索引
? 使用 T-SQL语句创建和管理索引
? 选择和维护索引
2
什么叫索引
? 数据库中的索引与书籍中的索引类似，在一本
书中，利用索引可以快速查找所需信息，无须
阅读整本书。在数据库中，索引使数据库程序
无须对整个表进行扫描，就可以在其中找到所
需数据。书中的索引是一个词语列表，其中注
明了包含各个词的页码。
3
? 索引是一个单独的, 物理的数据库结构,是对
数据库表中一个或多个列的值进行排序的结构。
索引提供指针以指向存储在表中指定列的数据
值，然后根据指定的排序次序排列这些指针。
? 索引是依赖于表建立的, 它提供了数据库中编
排表中数据的内部方法 。 一个表的存储是由两
部分组成的, 一部分用来存放表的数据页面,
另一部分存放索引页面 。 索引就存放在索引页
面上, 通常索引页面相对于数据页面来说小得
多 。 当进行数据检索时, 系统先搜索索引页面,
从中找到所需数据的指针, 再直接通过指针从
数据页面中读取数据 。 从某种程度上可以把数
据库看作一本书, 把索引看作书的目录, 通过
目录查找书中的信息显然比没有目录的书方便
快捷。
4
索引（一）
? 在数据库中，索引使数据库程序无须对
整个表进行扫描，就可以在其中找到所
需数据。利用索引可以大大提高系统的
性能，表现在,
? 通过创建惟一索引，可以保证数据记录
的惟一性。
? 大大加快数据检索速度
? 加速表与表之间的连接
5
索引（二）
? 缺点,
? 占用储存空间
? 在表中插入或更新表中数据时，将有额外
的操作来维护索引
? 过多的索引起反作用。
? 分类,
聚集索引
非聚集索引
6
聚集索引
? 聚集索引对表的物理数据页中的数据按列进行排序，
然后再重新存储到磁盘上。换句话说，如果在一个
表中建立了聚集索引，则表中的数据页会按照该索
引的顺序来存放。
? 优点：查找数据很快
? 缺点：完全重排数据，要相当于数据所占用空间的
120%
? 注意,
– 由于一个表只能有一种实际的存储顺序，因此，一个表只
能有一个聚集索引
– 创建非聚集索引之前要先创建聚集索引
– 关键值的惟一性使用 UNIQUE或标识符明确维护
– 保证有足够的临时空间来创建聚集索引
7
非聚集索引
? 非聚集索引具有完全独立于数据行的结
构。使用非聚集索引不用将物理数据页
中的数据按列排序。通俗地说，不会影
响数据表中记录的实际存储顺序。因此，
可以在一个表中创建多个非聚集索引。
? 非聚集索引需要更多的空间，检索效率
也较低。一个表中除建立一个聚集索引
外，还可以建立 249个非聚集索引。
? 例子：聚集索引和非聚集索引的例子见
p226的表 10－ 1和表 10－ 2
8
唯一索引和组合索引
? 无论是聚集索引还是非聚集索引，根据
索引键值是否重复，可以判定为是否为
唯一索引；根据索引字段的组成情况，
可以判定是否为组合索引。
9
唯一索引
? 若希望在表中创建唯一索引，则该字段或字段
组合的值在表中必须具有唯一性，即表中任何
两条记录的索引值都不能相同。
? 同样，若表中基于某个字段或字段组合存在两
条以上的记录中有相同的值，将不能基于该字
段或字段组合创建唯一索引。
? 注意：建立唯一索引的字段最后也设置为 not
null,因为两个 null值将被认为是重复的字段
值。
? 向表中添加记录或修改记录时，系统将检查添
加的记录或修改后的记录是否会造成唯一索引
键值的重复。若造成唯一索引键值的重复，系
统将拒绝执行该操作。
10
复合索引
? 若基于多个字段的组合创建索引，则称
该索引为复合索引。
? 注意：符合索引既可以是唯一索引，也
可以不是唯一索引。即使是唯一索引，
这个字段的组合的取值不能重复，但是
单独的字段值依然可以重复。
11
设置索引的注意事项
? SQL Server 2000在执行查询时，查询优
化器会对可用的数据检索方法的成本进
行评估，从中选择最有效的方法。因此，
只要数据表中存在索引,SQL Server
就会使用索引进行查询。若不使用索引,
SQL Server 将执行完整表扫描。若使用
索引,SQL Server 将会查找查询所需
的行的存储位置，并直接提取出所需的
行。
12
? 在考虑是否为数据表中的某个列创建索引时，
应该考虑按列进行查询的方式。若需要对该列
进行如下查询，则可以考虑在该列上创建索引,
? 1、需要在该列搜索符合特定搜索关键字的行，
即精确匹配查询。
? 2、需要在该列搜索关键字值属于某一特定范
围值的行，即范围查询。
? 3、在表 table1中搜索根据联接谓词与表
table2中的某个行匹配的行。
? 4、搜索已定义了 foreign key约束的两个表之
间匹配的行。
? 5、使用 like比较进行查询，且模式以特定的
字符串如,a%开头。等等
13
建立索引的准则
? 若确定要创建索引，则需要考虑如下准则,
? 1、尽管对于不需要修改数据的查询语句，大
量索引将有助于提高性能。但如果数据表中存
在大量索引，则 insert,update,delete语句
的性能将会受到影响，因为在数据表中的数据
进行更改时，系统需要对这些索引进行适当的
调整。
? 2、在小型表上创建索引可能不会产生太多的
优化效果，而且 SQL Server 在遍历索引以搜
索数据所花费的时间可能比简单的完整表扫描
还长。
14
? 3,若查询指定的所有数据列都包含在索
引中，如在员工的编号、姓名和基本工
资列上创建的组合索引，则从员工数据
表中查询编号和姓名列的查询被视为覆
盖的查询。创建覆盖查询的索引可以提
高性能，因为该查询所检索的所有数据
都包含在索引中，系统在检索数据时只
需要引用该数据表的索引页，不必引用
数据页，从而减少了输入输出总量。
15
索引的操作 —— 创建索引
? 利用企业管理器的索引向导创建；
? 利用企业管理器直接创建；
? 利用 T-SQL语句；
? 新建表时创建。
16
系统自动创建索引
? 在 SQL Server2000中建立或修改数据表
时，若创建或添加了主键约束或唯一性约束，
则系统会基于添加约束的字段自动创建唯一
性索引。
17
系统自动创建索引
? 唯一约束字段,
? 若为数据表中的某一字段设置了唯一性约束，
系统将基于这个字段自动创建一个非聚集的唯
一索引，以确保该字段的唯一性。这个索引可
以时聚集的，也可以是非聚集的。取决于是否
使用 clustered关键字。若使用了 clustered关
键字，则该索引是聚集的；否则就是非聚集的。
18
用存储过程查看和更改索引名称
? 系统存储过程 sp_helpindex可以返回表
的所有索引信息, 其语法形式如下,
? sp_helpindex [@objname=]’name’
? 其中 [@objname=]’name’参数用于指定
当前数据库中的表的名称。
? 另外, 系统存储过程 sp_rename可以用
来更改索引的名称 。
19
?例,sp_helpindex project查看表
project上的索引
?例：更改 orders 表中的索引 orders_quan
名称为 orders_quantity
?exec sp_rename
'orders.[orders_quan]',
'orders_quantity','index'
20
? 例：下面的例子在数据库 company中创建
一个表 product,其中将产品名称设置为
聚集的唯一索引。
? Use company
? Go
? Create table product
? (
– 产品 ID int,
– 产品名称 char(20) unique clustered
? )
21
主键约束字段
? 在数据表中的某个字段上设置主键约束时，系
统将在该字段上自动创建唯一索引，该索引可
以时聚集的，也可以是非聚集的，取决于在
primary key后面使用的关键字。
? 1、若使用 nonclustered关键字，则生成非聚集
的唯一索引。
? 2、若使用 clustered关键字，则生成聚集的唯
一索引。
? 3、若不使用关键字，并且在其它的字段上不存
在聚集索引，则生成聚集的唯一索引。
? 4、若不使用关键字，但是在其它的字段上存在
聚集索引，则生成非聚集的唯一索引。
22
? 例：在数据库 company中创建一个表
product,并设置, 产品 ID”为主键,
? Use company
? Go
? Create table product
? (
– 产品 ID int primary key,
– 产品名称 char(20)
? )
23
若将上例中改为如下情况，结
果如何呢？
? Use company
? Go
? Create table product
? (
– 产品 ID int primary key,
– 产品名称 char (20) unique clustered
? )
24
使用企业管理器管理索引
? 其具体步骤如下,
? （１）在企业管理器中，展开指定的服务器和
数据库，选择要创建索引的表，用右键单击该
表，从弹出的快捷菜单中选择所有任务项的管
理索引选项（如图 1所示），就会出现管理索引
对话框，如图 2所示。在该对话框中，可以选择
要处理的数据库和表 。
? （２）单击, 新建, 按钮，则出现新建索引对
话框，如图 3所示。
? （３）选择完成后单击, 确定, 按钮，即可生
成新的索引；单击, 取消, 按钮，则取消新建
索引的操作。
25
图 1 选择管理索引选项对话框
26
图 2 管理索引对话框
27
图 3 新建索引对话框
28
索引属性的设置
? 更改列顺序：单击右边的按钮可以使选中的字
段向上或向下移动。
? 聚集索引：指定该索引为聚集索引。注意：若
表中已经存在聚集索引，则该选项不能选。
? 唯一性：指定将该索引创建为唯一性索引。
? 忽略重复的值：指定忽略重复值。若选择此选
项，并且执行了创建重复键的 insert语句，则
SQL Server将发出警告消息并忽略重复的记录。
若不选择这个选项，系统将发出一个错误信息，
并拒绝这个插入操作。
29
? 不重新计算统计（不推荐使用）,指定
在索引创建以后不重新计算统计信息。
统计信息使系统用以提供检索速度的数
据库性能的统计资料，在创建索引时，
若选择此项，系统将使用索引创建以前
的统计信息对数据库进行评估，而旧的
统计信息并没有反应数据库中创建索引
以后的变化，所以可能会导致系统不优
化的决策。
30
? 文件组：子定索引所在的文件组。默认情况下索
引会和表存放在同一个文件组中。
? 填充索引：指定填充索引在索引的每个内部节点
上留出空格。指定填充索引的内部节点的行数至
少应大于等于两行。填充索引 选项只有在填充因
子选项指定后才起作用。因为填充索引 使用与填
充因子 相同的百分比。缺省时 SQL Server 确保
每个索引页至少有能容纳一条最大索引行数据的
空闲空间，如果填充因子指定的百分比不够容纳
一行数据,SQL Server 会自动内部更改百分比。
? 除去现有索引：指定在创建新索引之前删除任何
现有的同名索引。
31
? 填充因子：它指定创建索引时每个索引页的数据
占索引页大小的百分比。填充因子 的值为 1 到
100 。它其实同时指出了索引页保留的自由空间
占索引页大小的百分比，即 100- fillfactor 。
对于那些频繁进行大量数据插入或删除的表，在
建索引时应该为将来生成的索引数据预留较大的
空间，即将 fillfactor 设得较小；否则索引页会
因数据的插入而很快填满并产生分页，而分页会
大大增加系统的开销。但如果设得过小又会浪费
大量的磁盘空间，降低查询性能。因此对于此类
表通常设一个大约为 10的 fillfactor,而对于数
据不更改的高并发的只读的表，填充因子 可以设
到 95 以上乃至 100。
? 如果没有指定此选项,SQL Server 默认其值为 0。
32
利用索引向导创建索引
? 如图 4 --图 9 所示。
图 4 欢迎使用创建索引向导对话框
33
图 5 选择数据库和对象对话框
34
图 6 显示当前索引信息对话框
35
图 7 选择创建索引的列对话框
36
图 8 指定索引选项对话框
37
图 9 指定索引名称对话框
38
利用 T-SQL语句管理索引
?其语法形式如下,
?CREATE [UNIQUE] [CLUSTERED|
NONCLUSTERED ]
?INDEX index_name ON table
? ( column [,...n ] )
?[with[PAD_INDEX]
[[,]FILLFACTOR=fillfactor][[,]
?IGNORE_DUP_KEY]
[[,]DROP_EXISTING]
[[,]STATISTICS_NORECOMPUTE]]
?[ ON filegroup ]
39
各参数说明如下,
UNIQUE,用于指定为表或视图创建唯一索引，即不
允许存在索引值相同的两行。
CLUSTERED,用于指定创建的索引为聚集索引。
NONCLUSTERED,用于指定创建的索引为非聚集索引。
index_name,用于指定所创建的索引的名称。
table,用于指定创建索引的表的名称。
Column,用于指定被索引的列。
PAD_INDEX,用于指定索引中间级中每个页（节点）
上保持开放的空间。 PAD_INDEX选项只有在指定
了 FILLFACTOR时才有用，因为 PAD_INDEX使用由
FILLFACTOR所指定的百分比。
40
FILLFACTOR = fillfactor,用于指定在创建索引时，
每个索引页的数据占索引页大小的百分比，
fillfactor的值为 1到 100。默认值为 0。
IGNORE_DUP_KEY,用于控制当向包含于一个唯一聚
集索引中的列中插入重复数据时 SQL Server所作
的反应。
DROP_EXISTING,用于指定应删除并重新创建已命名
的先前存在的聚集索引或者非聚集索引。
STATISTICS_NORECOMPUTE,用于指定过期的索引统
计不会自动重新计算。
ON filegroup,用于指定存放索引的文件组。
41
?例：在数据库 company中创建一个表 employee1,
表中包含字段, 员工 ID”,,姓,,, 名, 和
,工资,, 然后使用 Create Index语句基于, 员
工 ID” 传见聚集的唯一索引, 基于, 姓, 和, 名,
的字段组合创建唯一索引, 设置填充, 忽略重复
值, 并查看索引信息 。 其程序清单如下,
?Use company
?Go
?Create table employee1(
?员工 ID int not null,
?姓 char(10),
?名 char(10),
?工资 int )
42
? CREATE UNIQUE CLUSTERED INDEX IX_EID
? ON employee1 (员工 ID)
? Create Unique Index IX_Elanamefname
? with
? pad_index,
? fillfactor=20,
? ignore_dup_key,
? On
? Employee1(姓, 名 )
43
要查看和修改索引的详细信息, 可以在企
业管理器中, 展开指定的服务器和数据库项,
用右键单击要查看的表, 从弹出的快捷菜单
中选择所有任务子菜单中的, 管理索引, 选
项, 则会出现管理索引对话框 。 选择要查看
或者修改的索引, 单击, 编辑, 按钮, 就会
出现修改索引对话框 。 在该对话框中, 可以
修改索引的大部分设置, 还可以直接修改其
SQL脚本, 只需按下, 编辑 SQL…,按钮, 即可
出现编辑 SQL脚本对话框, 其中可以编辑, 测
试和运行索引的 SQL脚本 。
利用企业管理器查看、修改索引
44
利用企业管理器删除索引
? 要删除索引，可以在企业管理器中，从
管理索引对话框中或者表的属性对话框
中，选择要删除的索引，单击, 删除,
按钮，即可删除索引。
45
使用 T-SQL语句删除索引
? 当不再需要某个索引时, 可以将其删除,
DROP INDEX命令可以删除一个或者多个
当前数据库中的索引, 其语法形式如下,
? DROP INDEX table.index [,...n ]
? 其中, table 用于指定索引列所在的表；
index用于指定要删除的索引名称 。 注意,
DROP INDEX 命 令 不 能 删 除 由 CREATE
TABLE或者 ALTER TABLE命令创建的主键
或者唯一性约束索引, 也不能删除系统
表中的索引 。
46
? 注意：若希望删除在设置主键约束和唯
一性约束时系统自动创建的约束，可以
使用 alter table语句中的 drop
constraint子句将约束除掉，系统将会
自动删除该索引。
47
索引选择
? 在表中适宜于对哪一列建立索引呢？
? 适宜于建立索引的列包括：主关键字列、外关
键字列、要排序的列、要分组的列、以及
where子句中精确指定的列。
? 可搜索参数（ SARGS） 是可以为 SQL Server
利用的参数，包括精确匹配和非精确匹配。如
where name like ‘w％ ’是可搜索的，而 where
name like ‘%w%’ 则是不可搜索的，需要遍历
整个表。
48
下列情况不能建立索引
? 1,列中数据类型为文本，图像或为
? 2、列中唯一性不强，如性别
? 3、列的范围太多，不能有效索引。
49
聚集索引的选择
? 在决定了对哪一列或哪些列建立索引后，要决
定采用哪一种索引：聚集索引或非聚集索引。
? 聚集索引的理想选择是,
? 1、专用的查询。如,where number=‘111’
? 2,查询的数据由固定的范围。如 where
col1>20
? 3,对编号或分组的列的经常性查询。
50
非聚集索引的选择
? 非聚集索引合适的选择包括,
? 1、专门查询（如前所述）
? 2、查询可以用索引完全回答。
? 例如：若由 pubs数据库中 authors表的
au_lname和 au_fname列的索引。下面的查询
能够完全由索引回答而无需访问数据库,
select au_fname from pubs.authors where
au_lname=‘white’
? 3、进行编号或分组的列
? 4、要对之使用函数的列（如 min,max）
51
更新维护
? 索引需要维护，以保持有效和有用。
52
更新统计
? SQL Server通过一张叫做分布页的特殊页来了
解数据，分布页中含有数据在表中分布的信息，
并计算索引列的平均复制数目。
? 当创建索引后，若表中已有数据，就会创建分
布页并记录索引列的信息。当先创建索引表再
添加数据时，则不会有分布页。分布页越新，
则 SQL Server就越可能选择正确的索引（或决
定用表来扫描）。
? 但是,SQL Server不会自动维护分布页，可
以通过如下命令来用当前信息更新分布页。
53
? 语法为,Update STATISTICS
[[database.]owner.]table_name
[index_name]
? 注意：当数据变化时，运行 Update
STATISTICS 可让 SQL Server了解数
据的物理分布。除非数据是只读的，否
则应尽可能频繁地更新统计，一般来说
对数据库中表格每周更新一次。
54
DBCC UpdateUSAGE
? DBCC UpdateUSAGE校正 SQL Server系
统表 sysindexs中的信息，该表中的数据
告诉 SQL Server表中有多少页、每个索
引有多少页等信息。这种信息也不是系
统自动维护的，运行 DBCC
UpdateUSAGE更新这种信息。
55
? 语法如下,
? DBCC UpdateUSAGE
({0|database_name}
? [,table_name[,index_id]])
? 若指定了数据库名,sysindexs中的信息
将对库中所有表的索引更新；若只指定
一张表，有关该表的索引数据将更新；
若只更新一条索引，必须指定索引号。
56
? 对数据库运行 DBCC UpdateUSAGE 不
仅有助于优化程序，而且有助于
sp_spaceused的输出。 sp_spaceused存储
过程会展示数据库中对象占了多少空间，
它是通过读 sysindexs表来获取信息的。
若 sysindexs表中信息不正确,
sp_spaceused的结果也是错误的。因此，
推荐周期性地运行 DBCC UpdateUSAGE
57
DBCC ShowContid
? 在对表进行数据修改（ INSERT,UPDATE 和
DELETE 语句）的过程中会出现表碎片现象。
由于这些修改通常并不在表的行中进行平均分
布，所以每页的填满状态会随时间而改变。对
于扫描部分或全部表的查询，这些表碎片会导
致额外的页读取，这将防碍数据的并行扫描。
因此索引需要周期性地重组。可用 DBCC
ShowContID确定表是否高度碎片化。
58
? 当索引的碎片非常多时，有两种减少碎片的方法,
? 除去并重新创建聚集索引。 重新创建聚集索引将对
数据进行重新组织，其结果是使数据页填满。填满
程度可以使用 FILLFACTOR 选项进行配置。这种
方法的缺点是索引在除去 /重新创建周期内为脱机状
态，并且操作属原子级。如果中断索引创建，则不
会重新创建该索引。
? 使用 DBCC INDEXDEFRAG 按逻辑顺序重新排序
索引的叶级页。 DBCC INDEXDEFRAG 命令是联
机操作，所以索引只有在该命令正在运行时才可用。
而且可以在不丢失已完成工作的情况下中断该操作。
这种方法的缺点是在重新组织数据方面没有聚集索
引的除去 /重新创建操作有效。
59
? 语法为,
? DBCC ShowContID (table_ID,[index_id])
? 其中,Table_ID是要对其碎片信息进行
检查的表。如果未指定，则对当前数据
库中的所有表和索引视图进行检查。若
要获得表或视图 ID,可使用
OBJECT_ID 函数。
? index_id,是要对其碎片信息进行检查的
索引。如果未指定，则该语句对指定表
或视图的基索引进行处理。若要获得索
引 ID,可使用 sysindexes。
60
DBCC DBReIndex
? DBCC DBREINDEX 重建表的索引或为
表定义的所有索引。通过允许动态重建
索引，可以重建强制 PRIMARY KEY 或
UNIQUE 约束的索引，而不必除去并重
新创建这些约束。这意味着不必知道表
的结构或约束就可以重建索引，将数据
大容量复制到表中后就会出现这种情况。
61
? DBCC DBREINDEX 可以使用一条语句重建
表的所有索引，这比对多个 DROP INDEX 和
CREATE INDEX 语句进行编码容易。由于该
工作是通过一条语句完成的，所以 DBCC
DBREINDEX 自动为原子性，而单个 DROP
INDEX 和 CREATE INDEX 语句要成为原子
性则必须放在事务中。另外，与使用单个
DROP INDEX 和 CREATE INDEX 语句相比，
DBCC DBREINDEX 可从 DBCC
DBREINDEX 的优化性能中更多地获益。
注意：不支持在系统表上使用 DBCC
DBREINDEX。
62
? 语法如下,
? DBCC DBREINDEX
( [ 'database.owner.table_name'
[,index_name
[,fillfactor ]
]
]
) [ WITH NO_INFOMSGS ]
? 参数说明如下,
63
? 'database.owner.table_name‘, 是要重建
其指定的索引的表名。数据库、所有者
和表名必须符合标识符的规则。如果提
供 database 或 owner 部分，则必须使用
单引号 (') 将整个
database.owner.table_name 括起来。如果
只指定 table_name,则不需要单引号。
? index_name,是要重建的索引名。索引
名必须符合标识符的规则。如果未指定
index_name 或指定为 ' '，就要对表的所
有索引进行重建。
64
? Fillfactor,是创建索引时每个索引页上
要用于存储数据的空间百分比。 fillfactor
替换起始填充因子以作为索引或任何其
它重建的非聚集索引（因为已重建聚集
索引）的新默认值。如果 fillfactor 为 0，
DBCC DBREINDEX 在创建索引时将使
用指定的起始 fillfactor。
? WITH NO_INFOMSGS,禁止显示所有
信息性消息（具有从 0 到 10 的严重级
别）。
第十章 管理索引
? 了解索引的概念和作用
? 了解索引的类型
? 使用企业管理器创建和管理索引
? 使用 T-SQL语句创建和管理索引
? 选择和维护索引
2
什么叫索引
? 数据库中的索引与书籍中的索引类似，在一本
书中，利用索引可以快速查找所需信息，无须
阅读整本书。在数据库中，索引使数据库程序
无须对整个表进行扫描，就可以在其中找到所
需数据。书中的索引是一个词语列表，其中注
明了包含各个词的页码。
3
? 索引是一个单独的, 物理的数据库结构,是对
数据库表中一个或多个列的值进行排序的结构。
索引提供指针以指向存储在表中指定列的数据
值，然后根据指定的排序次序排列这些指针。
? 索引是依赖于表建立的, 它提供了数据库中编
排表中数据的内部方法 。 一个表的存储是由两
部分组成的, 一部分用来存放表的数据页面,
另一部分存放索引页面 。 索引就存放在索引页
面上, 通常索引页面相对于数据页面来说小得
多 。 当进行数据检索时, 系统先搜索索引页面,
从中找到所需数据的指针, 再直接通过指针从
数据页面中读取数据 。 从某种程度上可以把数
据库看作一本书, 把索引看作书的目录, 通过
目录查找书中的信息显然比没有目录的书方便
快捷。
4
索引（一）
? 在数据库中，索引使数据库程序无须对
整个表进行扫描，就可以在其中找到所
需数据。利用索引可以大大提高系统的
性能，表现在,
? 通过创建惟一索引，可以保证数据记录
的惟一性。
? 大大加快数据检索速度
? 加速表与表之间的连接
5
索引（二）
? 缺点,
? 占用储存空间
? 在表中插入或更新表中数据时，将有额外
的操作来维护索引
? 过多的索引起反作用。
? 分类,
聚集索引
非聚集索引
6
聚集索引
? 聚集索引对表的物理数据页中的数据按列进行排序，
然后再重新存储到磁盘上。换句话说，如果在一个
表中建立了聚集索引，则表中的数据页会按照该索
引的顺序来存放。
? 优点：查找数据很快
? 缺点：完全重排数据，要相当于数据所占用空间的
120%
? 注意,
– 由于一个表只能有一种实际的存储顺序，因此，一个表只
能有一个聚集索引
– 创建非聚集索引之前要先创建聚集索引
– 关键值的惟一性使用 UNIQUE或标识符明确维护
– 保证有足够的临时空间来创建聚集索引
7
非聚集索引
? 非聚集索引具有完全独立于数据行的结
构。使用非聚集索引不用将物理数据页
中的数据按列排序。通俗地说，不会影
响数据表中记录的实际存储顺序。因此，
可以在一个表中创建多个非聚集索引。
? 非聚集索引需要更多的空间，检索效率
也较低。一个表中除建立一个聚集索引
外，还可以建立 249个非聚集索引。
? 例子：聚集索引和非聚集索引的例子见
p226的表 10－ 1和表 10－ 2
8
唯一索引和组合索引
? 无论是聚集索引还是非聚集索引，根据
索引键值是否重复，可以判定为是否为
唯一索引；根据索引字段的组成情况，
可以判定是否为组合索引。
9
唯一索引
? 若希望在表中创建唯一索引，则该字段或字段
组合的值在表中必须具有唯一性，即表中任何
两条记录的索引值都不能相同。
? 同样，若表中基于某个字段或字段组合存在两
条以上的记录中有相同的值，将不能基于该字
段或字段组合创建唯一索引。
? 注意：建立唯一索引的字段最后也设置为 not
null,因为两个 null值将被认为是重复的字段
值。
? 向表中添加记录或修改记录时，系统将检查添
加的记录或修改后的记录是否会造成唯一索引
键值的重复。若造成唯一索引键值的重复，系
统将拒绝执行该操作。
10
复合索引
? 若基于多个字段的组合创建索引，则称
该索引为复合索引。
? 注意：符合索引既可以是唯一索引，也
可以不是唯一索引。即使是唯一索引，
这个字段的组合的取值不能重复，但是
单独的字段值依然可以重复。
11
设置索引的注意事项
? SQL Server 2000在执行查询时，查询优
化器会对可用的数据检索方法的成本进
行评估，从中选择最有效的方法。因此，
只要数据表中存在索引,SQL Server
就会使用索引进行查询。若不使用索引,
SQL Server 将执行完整表扫描。若使用
索引,SQL Server 将会查找查询所需
的行的存储位置，并直接提取出所需的
行。
12
? 在考虑是否为数据表中的某个列创建索引时，
应该考虑按列进行查询的方式。若需要对该列
进行如下查询，则可以考虑在该列上创建索引,
? 1、需要在该列搜索符合特定搜索关键字的行，
即精确匹配查询。
? 2、需要在该列搜索关键字值属于某一特定范
围值的行，即范围查询。
? 3、在表 table1中搜索根据联接谓词与表
table2中的某个行匹配的行。
? 4、搜索已定义了 foreign key约束的两个表之
间匹配的行。
? 5、使用 like比较进行查询，且模式以特定的
字符串如,a%开头。等等
13
建立索引的准则
? 若确定要创建索引，则需要考虑如下准则,
? 1、尽管对于不需要修改数据的查询语句，大
量索引将有助于提高性能。但如果数据表中存
在大量索引，则 insert,update,delete语句
的性能将会受到影响，因为在数据表中的数据
进行更改时，系统需要对这些索引进行适当的
调整。
? 2、在小型表上创建索引可能不会产生太多的
优化效果，而且 SQL Server 在遍历索引以搜
索数据所花费的时间可能比简单的完整表扫描
还长。
14
? 3,若查询指定的所有数据列都包含在索
引中，如在员工的编号、姓名和基本工
资列上创建的组合索引，则从员工数据
表中查询编号和姓名列的查询被视为覆
盖的查询。创建覆盖查询的索引可以提
高性能，因为该查询所检索的所有数据
都包含在索引中，系统在检索数据时只
需要引用该数据表的索引页，不必引用
数据页，从而减少了输入输出总量。
15
索引的操作 —— 创建索引
? 利用企业管理器的索引向导创建；
? 利用企业管理器直接创建；
? 利用 T-SQL语句；
? 新建表时创建。
16
系统自动创建索引
? 在 SQL Server2000中建立或修改数据表
时，若创建或添加了主键约束或唯一性约束，
则系统会基于添加约束的字段自动创建唯一
性索引。
17
系统自动创建索引
? 唯一约束字段,
? 若为数据表中的某一字段设置了唯一性约束，
系统将基于这个字段自动创建一个非聚集的唯
一索引，以确保该字段的唯一性。这个索引可
以时聚集的，也可以是非聚集的。取决于是否
使用 clustered关键字。若使用了 clustered关
键字，则该索引是聚集的；否则就是非聚集的。
18
用存储过程查看和更改索引名称
? 系统存储过程 sp_helpindex可以返回表
的所有索引信息, 其语法形式如下,
? sp_helpindex [@objname=]’name’
? 其中 [@objname=]’name’参数用于指定
当前数据库中的表的名称。
? 另外, 系统存储过程 sp_rename可以用
来更改索引的名称 。
19
?例,sp_helpindex project查看表
project上的索引
?例：更改 orders 表中的索引 orders_quan
名称为 orders_quantity
?exec sp_rename
'orders.[orders_quan]',
'orders_quantity','index'
20
? 例：下面的例子在数据库 company中创建
一个表 product,其中将产品名称设置为
聚集的唯一索引。
? Use company
? Go
? Create table product
? (
– 产品 ID int,
– 产品名称 char(20) unique clustered
? )
21
主键约束字段
? 在数据表中的某个字段上设置主键约束时，系
统将在该字段上自动创建唯一索引，该索引可
以时聚集的，也可以是非聚集的，取决于在
primary key后面使用的关键字。
? 1、若使用 nonclustered关键字，则生成非聚集
的唯一索引。
? 2、若使用 clustered关键字，则生成聚集的唯
一索引。
? 3、若不使用关键字，并且在其它的字段上不存
在聚集索引，则生成聚集的唯一索引。
? 4、若不使用关键字，但是在其它的字段上存在
聚集索引，则生成非聚集的唯一索引。
22
? 例：在数据库 company中创建一个表
product,并设置, 产品 ID”为主键,
? Use company
? Go
? Create table product
? (
– 产品 ID int primary key,
– 产品名称 char(20)
? )
23
若将上例中改为如下情况，结
果如何呢？
? Use company
? Go
? Create table product
? (
– 产品 ID int primary key,
– 产品名称 char (20) unique clustered
? )
24
使用企业管理器管理索引
? 其具体步骤如下,
? （１）在企业管理器中，展开指定的服务器和
数据库，选择要创建索引的表，用右键单击该
表，从弹出的快捷菜单中选择所有任务项的管
理索引选项（如图 1所示），就会出现管理索引
对话框，如图 2所示。在该对话框中，可以选择
要处理的数据库和表 。
? （２）单击, 新建, 按钮，则出现新建索引对
话框，如图 3所示。
? （３）选择完成后单击, 确定, 按钮，即可生
成新的索引；单击, 取消, 按钮，则取消新建
索引的操作。
25
图 1 选择管理索引选项对话框
26
图 2 管理索引对话框
27
图 3 新建索引对话框
28
索引属性的设置
? 更改列顺序：单击右边的按钮可以使选中的字
段向上或向下移动。
? 聚集索引：指定该索引为聚集索引。注意：若
表中已经存在聚集索引，则该选项不能选。
? 唯一性：指定将该索引创建为唯一性索引。
? 忽略重复的值：指定忽略重复值。若选择此选
项，并且执行了创建重复键的 insert语句，则
SQL Server将发出警告消息并忽略重复的记录。
若不选择这个选项，系统将发出一个错误信息，
并拒绝这个插入操作。
29
? 不重新计算统计（不推荐使用）,指定
在索引创建以后不重新计算统计信息。
统计信息使系统用以提供检索速度的数
据库性能的统计资料，在创建索引时，
若选择此项，系统将使用索引创建以前
的统计信息对数据库进行评估，而旧的
统计信息并没有反应数据库中创建索引
以后的变化，所以可能会导致系统不优
化的决策。
30
? 文件组：子定索引所在的文件组。默认情况下索
引会和表存放在同一个文件组中。
? 填充索引：指定填充索引在索引的每个内部节点
上留出空格。指定填充索引的内部节点的行数至
少应大于等于两行。填充索引 选项只有在填充因
子选项指定后才起作用。因为填充索引 使用与填
充因子 相同的百分比。缺省时 SQL Server 确保
每个索引页至少有能容纳一条最大索引行数据的
空闲空间，如果填充因子指定的百分比不够容纳
一行数据,SQL Server 会自动内部更改百分比。
? 除去现有索引：指定在创建新索引之前删除任何
现有的同名索引。
31
? 填充因子：它指定创建索引时每个索引页的数据
占索引页大小的百分比。填充因子 的值为 1 到
100 。它其实同时指出了索引页保留的自由空间
占索引页大小的百分比，即 100- fillfactor 。
对于那些频繁进行大量数据插入或删除的表，在
建索引时应该为将来生成的索引数据预留较大的
空间，即将 fillfactor 设得较小；否则索引页会
因数据的插入而很快填满并产生分页，而分页会
大大增加系统的开销。但如果设得过小又会浪费
大量的磁盘空间，降低查询性能。因此对于此类
表通常设一个大约为 10的 fillfactor,而对于数
据不更改的高并发的只读的表，填充因子 可以设
到 95 以上乃至 100。
? 如果没有指定此选项,SQL Server 默认其值为 0。
32
利用索引向导创建索引
? 如图 4 --图 9 所示。
图 4 欢迎使用创建索引向导对话框
33
图 5 选择数据库和对象对话框
34
图 6 显示当前索引信息对话框
35
图 7 选择创建索引的列对话框
36
图 8 指定索引选项对话框
37
图 9 指定索引名称对话框
38
利用 T-SQL语句管理索引
?其语法形式如下,
?CREATE [UNIQUE] [CLUSTERED|
NONCLUSTERED ]
?INDEX index_name ON table
? ( column [,...n ] )
?[with[PAD_INDEX]
[[,]FILLFACTOR=fillfactor][[,]
?IGNORE_DUP_KEY]
[[,]DROP_EXISTING]
[[,]STATISTICS_NORECOMPUTE]]
?[ ON filegroup ]
39
各参数说明如下,
UNIQUE,用于指定为表或视图创建唯一索引，即不
允许存在索引值相同的两行。
CLUSTERED,用于指定创建的索引为聚集索引。
NONCLUSTERED,用于指定创建的索引为非聚集索引。
index_name,用于指定所创建的索引的名称。
table,用于指定创建索引的表的名称。
Column,用于指定被索引的列。
PAD_INDEX,用于指定索引中间级中每个页（节点）
上保持开放的空间。 PAD_INDEX选项只有在指定
了 FILLFACTOR时才有用，因为 PAD_INDEX使用由
FILLFACTOR所指定的百分比。
40
FILLFACTOR = fillfactor,用于指定在创建索引时，
每个索引页的数据占索引页大小的百分比，
fillfactor的值为 1到 100。默认值为 0。
IGNORE_DUP_KEY,用于控制当向包含于一个唯一聚
集索引中的列中插入重复数据时 SQL Server所作
的反应。
DROP_EXISTING,用于指定应删除并重新创建已命名
的先前存在的聚集索引或者非聚集索引。
STATISTICS_NORECOMPUTE,用于指定过期的索引统
计不会自动重新计算。
ON filegroup,用于指定存放索引的文件组。
41
?例：在数据库 company中创建一个表 employee1,
表中包含字段, 员工 ID”,,姓,,, 名, 和
,工资,, 然后使用 Create Index语句基于, 员
工 ID” 传见聚集的唯一索引, 基于, 姓, 和, 名,
的字段组合创建唯一索引, 设置填充, 忽略重复
值, 并查看索引信息 。 其程序清单如下,
?Use company
?Go
?Create table employee1(
?员工 ID int not null,
?姓 char(10),
?名 char(10),
?工资 int )
42
? CREATE UNIQUE CLUSTERED INDEX IX_EID
? ON employee1 (员工 ID)
? Create Unique Index IX_Elanamefname
? with
? pad_index,
? fillfactor=20,
? ignore_dup_key,
? On
? Employee1(姓, 名 )
43
要查看和修改索引的详细信息, 可以在企
业管理器中, 展开指定的服务器和数据库项,
用右键单击要查看的表, 从弹出的快捷菜单
中选择所有任务子菜单中的, 管理索引, 选
项, 则会出现管理索引对话框 。 选择要查看
或者修改的索引, 单击, 编辑, 按钮, 就会
出现修改索引对话框 。 在该对话框中, 可以
修改索引的大部分设置, 还可以直接修改其
SQL脚本, 只需按下, 编辑 SQL…,按钮, 即可
出现编辑 SQL脚本对话框, 其中可以编辑, 测
试和运行索引的 SQL脚本 。
利用企业管理器查看、修改索引
44
利用企业管理器删除索引
? 要删除索引，可以在企业管理器中，从
管理索引对话框中或者表的属性对话框
中，选择要删除的索引，单击, 删除,
按钮，即可删除索引。
45
使用 T-SQL语句删除索引
? 当不再需要某个索引时, 可以将其删除,
DROP INDEX命令可以删除一个或者多个
当前数据库中的索引, 其语法形式如下,
? DROP INDEX table.index [,...n ]
? 其中, table 用于指定索引列所在的表；
index用于指定要删除的索引名称 。 注意,
DROP INDEX 命 令 不 能 删 除 由 CREATE
TABLE或者 ALTER TABLE命令创建的主键
或者唯一性约束索引, 也不能删除系统
表中的索引 。
46
? 注意：若希望删除在设置主键约束和唯
一性约束时系统自动创建的约束，可以
使用 alter table语句中的 drop
constraint子句将约束除掉，系统将会
自动删除该索引。
47
索引选择
? 在表中适宜于对哪一列建立索引呢？
? 适宜于建立索引的列包括：主关键字列、外关
键字列、要排序的列、要分组的列、以及
where子句中精确指定的列。
? 可搜索参数（ SARGS） 是可以为 SQL Server
利用的参数，包括精确匹配和非精确匹配。如
where name like ‘w％ ’是可搜索的，而 where
name like ‘%w%’ 则是不可搜索的，需要遍历
整个表。
48
下列情况不能建立索引
? 1,列中数据类型为文本，图像或为
? 2、列中唯一性不强，如性别
? 3、列的范围太多，不能有效索引。
49
聚集索引的选择
? 在决定了对哪一列或哪些列建立索引后，要决
定采用哪一种索引：聚集索引或非聚集索引。
? 聚集索引的理想选择是,
? 1、专用的查询。如,where number=‘111’
? 2,查询的数据由固定的范围。如 where
col1>20
? 3,对编号或分组的列的经常性查询。
50
非聚集索引的选择
? 非聚集索引合适的选择包括,
? 1、专门查询（如前所述）
? 2、查询可以用索引完全回答。
? 例如：若由 pubs数据库中 authors表的
au_lname和 au_fname列的索引。下面的查询
能够完全由索引回答而无需访问数据库,
select au_fname from pubs.authors where
au_lname=‘white’
? 3、进行编号或分组的列
? 4、要对之使用函数的列（如 min,max）
51
更新维护
? 索引需要维护，以保持有效和有用。
52
更新统计
? SQL Server通过一张叫做分布页的特殊页来了
解数据，分布页中含有数据在表中分布的信息，
并计算索引列的平均复制数目。
? 当创建索引后，若表中已有数据，就会创建分
布页并记录索引列的信息。当先创建索引表再
添加数据时，则不会有分布页。分布页越新，
则 SQL Server就越可能选择正确的索引（或决
定用表来扫描）。
? 但是,SQL Server不会自动维护分布页，可
以通过如下命令来用当前信息更新分布页。
53
? 语法为,Update STATISTICS
[[database.]owner.]table_name
[index_name]
? 注意：当数据变化时，运行 Update
STATISTICS 可让 SQL Server了解数
据的物理分布。除非数据是只读的，否
则应尽可能频繁地更新统计，一般来说
对数据库中表格每周更新一次。
54
DBCC UpdateUSAGE
? DBCC UpdateUSAGE校正 SQL Server系
统表 sysindexs中的信息，该表中的数据
告诉 SQL Server表中有多少页、每个索
引有多少页等信息。这种信息也不是系
统自动维护的，运行 DBCC
UpdateUSAGE更新这种信息。
55
? 语法如下,
? DBCC UpdateUSAGE
({0|database_name}
? [,table_name[,index_id]])
? 若指定了数据库名,sysindexs中的信息
将对库中所有表的索引更新；若只指定
一张表，有关该表的索引数据将更新；
若只更新一条索引，必须指定索引号。
56
? 对数据库运行 DBCC UpdateUSAGE 不
仅有助于优化程序，而且有助于
sp_spaceused的输出。 sp_spaceused存储
过程会展示数据库中对象占了多少空间，
它是通过读 sysindexs表来获取信息的。
若 sysindexs表中信息不正确,
sp_spaceused的结果也是错误的。因此，
推荐周期性地运行 DBCC UpdateUSAGE
57
DBCC ShowContid
? 在对表进行数据修改（ INSERT,UPDATE 和
DELETE 语句）的过程中会出现表碎片现象。
由于这些修改通常并不在表的行中进行平均分
布，所以每页的填满状态会随时间而改变。对
于扫描部分或全部表的查询，这些表碎片会导
致额外的页读取，这将防碍数据的并行扫描。
因此索引需要周期性地重组。可用 DBCC
ShowContID确定表是否高度碎片化。
58
? 当索引的碎片非常多时，有两种减少碎片的方法,
? 除去并重新创建聚集索引。 重新创建聚集索引将对
数据进行重新组织，其结果是使数据页填满。填满
程度可以使用 FILLFACTOR 选项进行配置。这种
方法的缺点是索引在除去 /重新创建周期内为脱机状
态，并且操作属原子级。如果中断索引创建，则不
会重新创建该索引。
? 使用 DBCC INDEXDEFRAG 按逻辑顺序重新排序
索引的叶级页。 DBCC INDEXDEFRAG 命令是联
机操作，所以索引只有在该命令正在运行时才可用。
而且可以在不丢失已完成工作的情况下中断该操作。
这种方法的缺点是在重新组织数据方面没有聚集索
引的除去 /重新创建操作有效。
59
? 语法为,
? DBCC ShowContID (table_ID,[index_id])
? 其中,Table_ID是要对其碎片信息进行
检查的表。如果未指定，则对当前数据
库中的所有表和索引视图进行检查。若
要获得表或视图 ID,可使用
OBJECT_ID 函数。
? index_id,是要对其碎片信息进行检查的
索引。如果未指定，则该语句对指定表
或视图的基索引进行处理。若要获得索
引 ID,可使用 sysindexes。
60
DBCC DBReIndex
? DBCC DBREINDEX 重建表的索引或为
表定义的所有索引。通过允许动态重建
索引，可以重建强制 PRIMARY KEY 或
UNIQUE 约束的索引，而不必除去并重
新创建这些约束。这意味着不必知道表
的结构或约束就可以重建索引，将数据
大容量复制到表中后就会出现这种情况。
61
? DBCC DBREINDEX 可以使用一条语句重建
表的所有索引，这比对多个 DROP INDEX 和
CREATE INDEX 语句进行编码容易。由于该
工作是通过一条语句完成的，所以 DBCC
DBREINDEX 自动为原子性，而单个 DROP
INDEX 和 CREATE INDEX 语句要成为原子
性则必须放在事务中。另外，与使用单个
DROP INDEX 和 CREATE INDEX 语句相比，
DBCC DBREINDEX 可从 DBCC
DBREINDEX 的优化性能中更多地获益。
注意：不支持在系统表上使用 DBCC
DBREINDEX。
62
? 语法如下,
? DBCC DBREINDEX
( [ 'database.owner.table_name'
[,index_name
[,fillfactor ]
]
]
) [ WITH NO_INFOMSGS ]
? 参数说明如下,
63
? 'database.owner.table_name‘, 是要重建
其指定的索引的表名。数据库、所有者
和表名必须符合标识符的规则。如果提
供 database 或 owner 部分，则必须使用
单引号 (') 将整个
database.owner.table_name 括起来。如果
只指定 table_name,则不需要单引号。
? index_name,是要重建的索引名。索引
名必须符合标识符的规则。如果未指定
index_name 或指定为 ' '，就要对表的所
有索引进行重建。
64
? Fillfactor,是创建索引时每个索引页上
要用于存储数据的空间百分比。 fillfactor
替换起始填充因子以作为索引或任何其
它重建的非聚集索引（因为已重建聚集
索引）的新默认值。如果 fillfactor 为 0，
DBCC DBREINDEX 在创建索引时将使
用指定的起始 fillfactor。
? WITH NO_INFOMSGS,禁止显示所有
信息性消息（具有从 0 到 10 的严重级
别）。
