索引图

第6章索引及其应用

、教学目标

通过本章学习，使学生掌握索引的基木概念、分类和作用，掌握索引的建立和操作方法,

掌握索引的维护方法，会根据实际问题的需要，能够熟练地建立表和视图的相关索引。

教学要求

知识要点能力要求关联知识

索引概念、分类

和作用

(1)掌握索引的基本概念、分类和作用

索引概念、分类和作用

索引的建立和

操作

CI)掌握索引的建立方法

(2)掌握索引的操作方法

SQLSerVerManagement

StUdiO建立和操作索引，

CREATEINDEX等SQL命

令

索引的维护CI)掌握维护索引的常用方法

DBCCSHOWCONTlG和DBCC

INDEXDEFRAG命令

索引视图(1)掌握索引视图的建立和应用方法

CREATEVIEW,CREATE

INDEX等SQL命令

、重点难点

索引的概念、分类和作用

索引的建立和操作方法

索引视图的建立与应用

6-1任务描述

木章完成项目的第6个任务：在大学生选课管理数据库StUdent中，完成如下操作：

1.为课程信息表创建一个非聚集复合索引。

2.为教师教课信息表创建一个聚集复合索引。3.为学生选课信息表创建一个唯一、聚集复合索引。

6.2索引综述

数据库中的索引可以快速找到表或索引视图中的特定信息。索引包含从表或视图中一个或多个列生成的

键，以及映射到指定数据的存储位置的指针。通过创建、设计良好的索引以支持查询，可以显著提高数据库查

询和用应程序的性能。索引可以减少为返回查询结果集而必须读取的数据量。索引还可以强制表中的行具有唯

一性，从而确保表数据的数据完整性。

1•索引的概念

数据库中的索引与书籍中的索引（目录）类似，在一本书中，利用索引可以快速查找所需信息，无须阅

读整本书。在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。书中的索引

是一个词语列表，其中注明了包含各个词的页码。而数据库中的索引是某个表中一列或者若干列值的集合和相

应的指向表中物理标识这些值的数据页的逻辑指针清单。也可以这么说，数据库中某个表的索引是指，将这个

表中数据行按照某一列或者若干列值的组合

（称为索引键）的大小，只排列各个数据行的顺序，而不改变数据行的存储位置，得到的一个非结构数据文

件。

2•索引的作用

通过创建唯一索引，可以保证数据记录的唯一性。通过创建和使用索引可以大大加快数据检索的速

度。通过创建和使用索引可以加速表与表之间的连接，这一点在实现数据的参照完整性方而有特别

的意义。

通过创建和使用索引使得在使用ORDERB湘GROUPBY子句中进行检索数据时，可以显著减少

查询中分组和排序的时间。通过索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。

3.索引类型

表或视图可以包含以下类型的索引。

（1）聚集索引聚集索引是指表中数据行的物理存储顺序与索引列顺序完全相同。聚集索引是根据数据行的键值

在表或视图中排序而存储这些数据行。索引定义中包含聚集索引列。每个表只能有一个聚集索引，因为数据行本

身只能按一个顺序方式排序。

只有当表包含聚集索引时，表中的数据行才按排序顺序存储。如果表没有聚集索引，则其数据行存储在

一个称为堆的无序结构中。

（2）非聚集索引非聚集索引不改变表中数据行的物理存储位置，数据与索引分开存储，通过索引带有

的指针与表中的数据发生联系。非聚集索引具有独立于数据行的结构。非聚集索引包含非聚集索引键值，并且

每个键值项都是指向包含该键值的数据行的指针。

一个表或视图可含有多个非聚集索引。

聚集索引和非聚集索引都可以是唯一的。这意味着任何两行都不能有相同的索引键值。另外，索引也

可以不是唯一的，即多行可以共享同一个索引键值。每当修改了数据表内容后，都会自动维护表或视图的索

引。

（3）唯一索引唯一索引确保索引键不包含重复的值，因此，表或视图中的每一行在某种程度上是唯一

的。

（4）包含性列索引是一种非聚集索引，它扩展后不仅包含键列，还包含非键列。

（5）索引视图视图的索引将具体化（执行）视图，并将结果集永久存储在唯一的聚集索引中，而且存储

方法与带聚集索引的表的存储方法相同。创建聚集索引后，可以为视图添加非聚集索引。

6.3创建索引

使用索引要付出一定的空间和时间的代价，因此为表建立索引时，要根据实际情况，认真考虑哪些列应

该索引，哪些列不应该索引。

建立索引一般要遵循以下几条原则：

主键列上一定要建立索引；

外键列上可以建立索引；

在经常查询的字段上最好建立索引；对于查询中很少涉及的列、重复值比较多的列不要建立索引；对于

定义为text、image和bit数据类型的列上不要建立索引；

SQLSerVer2008在创建主键约束或唯一约束时，自动创建唯一索引，以强制实施PRIMARYKE丫

和UNlQUE约束的唯一性要求。如果需要创建不依赖于约束的索引，可以使用SQLSerVerManagement

StUdiO或者使用SQL命令创建索引。

建立索引时要注意以下几点：

只有表或视图的所有者才有权建立索引。

在建立聚集索引时，将会对表进行复制，对表中的数据进行排序，然后删除原始的表。因此，数据库上

必须有足够的空间，以容纳数据复本。

在使用CREATENDEX命令建立索引时，必须指定索引名称、表名称及索引所应用的各列名称（即索

引键）O

在一个表中最多可建立249个非聚集索引。默认情况下，建立的索引是非聚集索引。复合索引的列的最

大数目为16,各列组合的最大长度为900字节。

6.3.1使用SQLSerVerManagementStUCIiO创建索引

1.启动SQLSerVerManagementStudio,并连接到SQLSerVer2008中的数据库，

在“对象资源管理器”窗口中展开“数据库”节点，再展开建立索引的表所属的数据库名（比如StUdent）,

再展开其“表”节点，展开要建立索引的表名（比如Stab）,

右击其“索引”节点，出现弹出菜单，如图6-1所示。

RJRePoTiervierTempDB

BJStUdet

田口数据库关系匡1曰丄哀+LJ果城震

E

XZ

冈.

二列

-L键

3釣克

±1一送0(・S」堀计

1_J

≡V

E口叵

二田口视图田LI同

文词÷J可编程

性+jSer-iceBnake田」存储

尹安全性

3TeaCliefSLJ安全

刷新卩

图6」新建索引

2•执行弹出菜单中的【新建索引】命令，系统则出现“新建索引”对话框，如图&2所示。

新逹索引迪…全

部丢器生成同

全郃垂新型只◎

全部禁淫①

启动POVerShelQJ

图6・2“新建索引”对话框

3•在新建索引对话框中，于“索引名称”文木框中输入新建索引的名称，可于“索引类型”下拉框中选

择新建索引的类型，可单击“索引键列”列表框后的“添加”按钮，系统出现“选择索引键列”对话框，如图

6・3所示。

,,心需肋

初曲：

素引名桶心.

案引宾型叩.

厂唯1切

∣5t∙bJrxΓl

ΞJ

徘序IM祎SSSt式水萍识朮许NULL佰

1-：nlxl

JOUNDEH-5774G30⅛unfcu

出害百I军捋廣峙

隔利

KiLrmER-5774030

图6・3选择索引键

4∙在选择索引键列对话框中，列出了建立索引的表的所有字段，从中选择新建索引所应用的各个列名(即

选择作为索引键的各个列)，选择完毕后，单击“确

定“按钮，系统返回“新建索引”对话框，如图6・4所示。

SLS

.晦

TO≡ER-5T74CCO

y0VMI∣Eft"5774C(3O,'SgfQ吃31兀

Jj盘看连接恋空

图6・4“新建索引”对话框

5•在该新建索引对话框中，可通过“索引键列”列表框中的“排序顺序”下拉框，设置相应的索引键列

的排序顺序。

(1)可选择“选项”选择页，进入“选项”设置界面，在此，可根据实际需要

设置应用索引时的相关选项，如图6・5所示。

(2)可选择“包含性列”选择页，进入“包含性列”设置界面，在此，可设置另一个表中的列，只有非聚

集索引，该选择页才可用。

(3)可选择“存储“选择页，进入“存储”设置界面，在此，可设置对指定的文件组或方案创建索引。

*∙⅜悭I

■BD

暨砸

袒Sa)

索引塔称

CD：寿引

融”

厂皆IsI)

I帶期序IHHaiI大水I乐识

|允许

NULLfiS

升序WChirl20)m舌

否

bj

JWCher¢20)20S否

雨加g

爭引健列B

图6・5“新建索引”对话框

632使用SQL命令创建索引

语法形式：

CREATE[UNIQUE][CLUSTERED∣NONCLUSTERED]INDEXindex_name

ON{tableIVieW}(COlUmn[ASC∣DESC][,...n])[WITH

[PAD_INDEX]

[[JFILLFACTOR=fillfactor]

[[,]IGNORE_DUP_KEY]

[[,]DR0P_EXISTlNG]

[[,]STATISTICS_NORECOMPUTEJ[[,]SORT_IN_TEMPDB]]

[ONfilegroup]

其中：

UNIQUE:用于指定为表或视图创建唯一索引；

CLUSTERED:用于指定创建的索引为聚集索引；

NONCLUSTERED:用于指定创建的索引为非聚集索引，默认为非聚集索弓index_name:用于指定

所创建的索引名称；table:用于指定创建索引的表的名称；view：用于指定创建索引的视图的名

称。

column：用于指定被索引的列，即索引所应用的列(索引键中的列)；

ASCIDESC:用于指定具体某个索引列的升序或降序排序方向；

PADJNDEX:用于指定索引中间级中每个页(节点)上保持开放的空间；FlLLFACTOR=fillfactor:用

于指定在创建索引时，每个索引页的数据占索引页大小的百分比，fillfactor的值为1到100。

IGNORE_DUP_KEY:用于控制当往包含于一个唯一聚集索引中的列中插入重复数据时SQL

SerVer所作的反应。

DROP,EXISTING:用于指定应删除并重新创建己命名的先前存在的聚集索引或者非聚集索引。

STATlSTlCS_NORECOMPUTE:用于指定过期的索引统计不会自动重新计算。

SORTJN_TEMPDB:用于指定创建索引时的中间排序结果将存储在tempdb数据库中。

ONfilegroup:用于指定存放索引的文件组。

【例6-1】在数据库TeaChing中，为学生成绩表Sgrade建立一个基于“学号，姓名”组合列的唯一、

非聚集复合索引SJndeXI

o

USeTeaChing

GO

CreateUNIQUEIndeXSJndeXIONSgrade(Xh,Xm)

GO

【例6-2】在数据库TeaChing中，为学生成绩表Sgrade建立一个基于“所在系，班级，姓名”组合

列的聚集复合索引SJndeX2。

USeTeaChing

GO

CreateCLIJSTEREDIndeXSJndeX2

ONSgrade(SZX,bj,Xm)

GO

【例6-3】在数据库TeaChing中，为学生成绩表Sgrade建立一个基于“姓

1

名”列的非聚集索引SJndeX3。

USeTeaChing

GO

CreateIndexSJndex3ONSgrade(XmDESC)

GO

6.4操作索引

641使用SQLSerVerManagementStUCIiO操作索引

启动SQLSerVerManagementStudio,并连接至USQLSerVer2008中的数据库，在

“对象资源管理器”窗口中展开“数据库”节点，再展开操作索引的表所属的数据

库名（比如StUdent）,再展开其“表”节点，展开索引所属的表名（比如Stab）,展

开其“索弓I”节点，右击要操作的索引名，出现弹出菜单，如图6・6所示。

E_JStUdent庄_1逊据库关累閨匕摂

田口系裁表

S二

511

图6・6操作索引

∣5t^bindex1(

计信息_

二烝tab

新连索引蚀…编写索

引脚本逖⑤垂新生成

⑪

棊用①

E_□视圉圧同艾词

I+：」可编程性

E丄WtfliOeBrOker

策略回

万面因

启动POWerShell

13IjTeaCher严」

安全性

⅛-i服务囲锯

El_J矣制

0.丄菅理

出至SQLSerVer代理

t!)

垂命呂画

删除（Q）

1

图6・7“索引属性”对话框

2•重命名索引

执行图6・6弹岀菜单中的【重命名】命令，可以重命名当前索弓I。

3•删除索引

执行图6・6弹出菜单中的【删除】命令，可以删除当前索引。

1.查看和修改索引属性

执行图6・6弹岀菜单中的【属性】命令，进入“索引属性”对话框，在此，可查看和修改当前索

引的有关属性，如图6・7所示。

：.弄引圧性SUtUndeXl回E3

J

l

W4：▼吐稱助

连接

•吨

TolWIIER-5774(KO∖

SmLfeMqiM

,誉吿浮1⅞Γ⅞P

*

-*α⅛⅛

-

J

l*

4

*

衰名①：

案引名覇

QL）：餐引

现）：

^l≡Uφ:

1

642使用SQL命令操作索引

1.查看表的索引信息

语法格式：

EXECSP_helpindex表名

2.重命名索引

语法格式：

EXECsp_rename'表名•原索引名’，'表名•新索引名’

3.删除表索引

语法格式：

DROPlNDEX表名•索引名

或者

DROPINDEX索引名ON表名或视图名

【例6-4］删除数据库TeaChing中,表Sgrade的索引SJndeX2。

USeTeaChing

GO

DrOPIndeXSgrade.s_index2

GO

6.5索引的维护

索引创建之后，由于数据的增加、删除和修改等操作会使索引页发生碎块，因此必须对索引进行维

护。

SQLSerVer提供了多种维护索引的方法，常用的有DBCCSHOWCONTlG和DBCC

INDEXDEFRAG命令。

1.查看表的数据和索引的碎片信息

语法格式：

(1)DBCCSHOWCONTlG表名或视图名

(2)DBCCSHOWCONTIG(表名或视图名，索引名)

2.整理表中索引碎片

1

语法格式：

DBCCINDEXDEFRAG数据库名,表名或视图名［,索引名］)

【例6-5］清除数据库TeaChing中的表Sgrade的所有索引碎片。

USeTeaChing

GO

DBCCINDEXDEFRAG(TeaChing,Sgrade)

GO

附注：可在“索引属性”对话框中(如上图6・7所示)通过“碎片”选择页进入“碎片”界面，在此可查

看和整理当前索引的碎片情况。

说明：当数据进行大量的修改后，这时可将原索引删掉，再重新建立索引。

6.6索引视图

对于视图而言，系统为它们动态生成结果集的开销很大，尤其是对于那些涉及大量行进行复杂处理

(如聚集大量数据或连接许多行)的视图。如果在查询中频繁地使用这类视图，应该对视图创建唯一聚集索

引，形成索引视图。索引视图中存放着查询得到的结果集，它在数据库中的存储方式与具有聚集索引的表

的存储方式相同，从而提高查询性能。

创建索引视图除要遵照创建标准视图的要求外，还应注意如下几点：

索引视图只能引用基表，不能引用其他视图。

索引视图引用的所有基表必须与视图位于同一数据库中，且所有者也与视图相同。

索引视图引用的基表名称必须由两部分组成，即架构名•表名

创建索引视图时必须使用WlTHSCHEMABINDlNG选项

若索引视图定义中使用聚集函数，SELEC例表中必须包括COUNT

-

BIG(*)

0

索引视图中的表

达式引用的所有函数必须是确定的。

【例6-6］在大学生选课管理数据库StUdent中，创建一个Xk_VieW的索引视图，该视图可查

询每个学生的学号、姓名、所选课门数和所选课程的平均成绩。

USeStUdent

GO

广使用WITHSCHEMABINDlNG选项创建视图Xk_view*/

CreateVieWXk_VieW

WITHSCHEMABINDING

AS

学号，姓名，

COUnt_Big(*)选课门数，Avg()平均成绩

FrOmdbo・Stab,dbo・SCtab

1

=

GrOUPBydbo・,dbo・

GO

广为视图建立一个基于〃学号，姓名”组合列的唯一聚集索引XkJndex,形成索引视

图*/

CreateIJNlQUECLUSTEREDIndeXXkJndeX

ONXk_VieW(学号，姓名)

GO

广使用该索引视图Xk_VieW,查看学生选课情况*/

SeleCt*FrOmXk_VieW

GO

6.7任务实现

1.为数据库StUdent中的课程信息表创建一个复合索引

在数据库StUdent中，为课程信息表创建一个基于“课程号，课程名”组合列的非聚集复合索弓I

Ctab_index1。

USeStUdent

GO

CreateIndeXCtabJndeXIONctab(kch,kcm)

GO

2.为数据库StUdent中的教师教课信息表创建一个聚集复合索引

在数据库StUdent中，为教师教课信息表创建一个基于“教师号，课程号”组合列的聚集复合索引

tctabindex1

0

USeStUdent

GO

CreateCLUSTEREDIndeXtctabjndex1

ONtctab(jsh,kch)

GO

3.为数据库StUdent中的学生选课信息表创建一个唯一聚集复合索引