索引及其应用.docx
《索引及其应用.docx》由会员分享，可在线阅读，更多相关《索引及其应用.docx（17页珍藏版）》请在冰点文库上搜索。
索引及其应用
第6章索引及其应用
教学目标
通过本章学习，使学生掌握索引的基本概念、分类和作用，掌握索引的建立和操作方法，掌握索引的维护方法，会根据实际问题的需要，能够熟练地建立表和视图的相关索引。
教学要求
知识要点
能力要求
关联知识
索引概念、分类和作用
（1）掌握索引的基本概念、分类和作用
索引概念、分类和作用
索引的建立和操作
（1）掌握索引的建立方法
（2）掌握索引的操作方法
SQLServerManagementStudio建立和操作索引，
CREATEINDEX等SQL命令
索引的维护
（1）掌握维护索引的常用方法
DBCCSHOWCONTIG和DBCCINDEXDEFRAG命令
索引视图
（1）掌握索引视图的建立和应用方法
CREATEVIEW，CREATEINDEX等SQL命令
重点难点
索引的概念、分类和作用
索引的建立和操作方法
索引视图的建立与应用
6.1任务描述
本章完成项目的第6个任务：
在大学生选课管理数据库Student中，完成如下操作：
1．为课程信息表创建一个非聚集复合索引。
2．为教师教课信息表创建一个聚集复合索引。
3．为学生选课信息表创建一个唯一、聚集复合索引。
6.2索引综述
数据库中的索引可以快速找到表或索引视图中的特定信息。
索引包含从表或视图中一个或多个列生成的键，以及映射到指定数据的存储位置的指针。
通过创建、设计良好的索引以支持查询，可以显著提高数据库查询和用应程序的性能。
索引可以减少为返回查询结果集而必须读取的数据量。
索引还可以强制表中的行具有唯一性，从而确保表数据的数据完整性。
１．索引的概念
数据库中的索引与书籍中的索引（目录）类似，在一本书中，利用索引可以快速查找所需信息，无须阅读整本书。
在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。
书中的索引是一个词语列表，其中注明了包含各个词的页码。
而数据库中的索引是某个表中一列或者若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
也可以这么说，数据库中某个表的索引是指，将这个表中数据行按照某一列或者若干列值的组合（称为索引键）的大小，只排列各个数据行的顺序，而不改变数据行的存储位置，得到的一个非结构数据文件。
2．索引的作用
●通过创建唯一索引，可以保证数据记录的唯一性。
●通过创建和使用索引可以大大加快数据检索的速度。
●通过创建和使用索引可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。
●通过创建和使用索引使得在使用ORDERBY和GROUPBY子句中进行检索数据时，可以显著减少查询中分组和排序的时间。
●通过索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。
3．索引类型
表或视图可以包含以下类型的索引。
（1）聚集索引
聚集索引是指表中数据行的物理存储顺序与索引列顺序完全相同。
聚集索引是根据数据行的键值在表或视图中排序而存储这些数据行。
索引定义中包含聚集索引列。
每个表只能有一个聚集索引，因为数据行本身只能按一个顺序方式排序。
只有当表包含聚集索引时，表中的数据行才按排序顺序存储。
如果表没有聚集索引，则其数据行存储在一个称为堆的无序结构中。
（2）非聚集索引
非聚集索引不改变表中数据行的物理存储位置，数据与索引分开存储，通过索引带有的指针与表中的数据发生联系。
非聚集索引具有独立于数据行的结构。
非聚集索引包含非聚集索引键值，并且每个键值项都是指向包含该键值的数据行的指针。
一个表或视图可含有多个非聚集索引。
聚集索引和非聚集索引都可以是唯一的。
这意味着任何两行都不能有相同的索引键值。
另外，索引也可以不是唯一的，即多行可以共享同一个索引键值。
每当修改了数据表内容后，都会自动维护表或视图的索引。
（3）唯一索引
唯一索引确保索引键不包含重复的值，因此，表或视图中的每一行在某种程度上是唯一的。
（4）包含性列索引
是一种非聚集索引，它扩展后不仅包含键列，还包含非键列。
（5）索引视图
视图的索引将具体化（执行）视图，并将结果集永久存储在唯一的聚集索引中，而且存储方法与带聚集索引的表的存储方法相同。
创建聚集索引后，可以为视图添加非聚集索引。
6.3创建索引
使用索引要付出一定的空间和时间的代价，因此为表建立索引时，要根据实际情况，认真考虑哪些列应该索引，哪些列不应该索引。
建立索引一般要遵循以下几条原则：
●主键列上一定要建立索引;
●外键列上可以建立索引;
●在经常查询的字段上最好建立索引;
●对于查询中很少涉及的列、重复值比较多的列不要建立索引;
●对于定义为text、image和bit数据类型的列上不要建立索引;
SQLServer2008在创建主键约束或唯一约束时，自动创建唯一索引，以强制实施PRIMARYKEY和UNIQUE约束的唯一性要求。
如果需要创建不依赖于约束的索引，可以使用SQLServerManagementStudio或者使用SQL命令创建索引。
建立索引时要注意以下几点：
●只有表或视图的所有者才有权建立索引。
●在建立聚集索引时，将会对表进行复制，对表中的数据进行排序，然后删除原始的表。
因此，数据库上必须有足够的空间，以容纳数据复本。
●在使用CREATEINDEX命令建立索引时，必须指定索引名称、表名称及索引所应用的各列名称（即索引键）。
●在一个表中最多可建立249个非聚集索引。
默认情况下，建立的索引是非聚集索引。
●复合索引的列的最大数目为16，各列组合的最大长度为900字节。
6.3.1使用SQLServerManagementStudio创建索引
1．启动SQLServerManagementStudio，并连接到SQLServer2008中的数据库，在“对象资源管理器”窗口中展开“数据库”节点，再展开建立索引的表所属的数据库名（比如Student），再展开其“表”节点，展开要建立索引的表名（比如Stab），右击其“索引”节点，出现弹出菜单，如图6-1所示。
图6-1新建索引
2．执行弹出菜单中的【新建索引】命令，系统则出现“新建索引”对话框，如图6-2所示。
图6-2“新建索引”对话框
3．在新建索引对话框中，于“索引名称”文本框中输入新建索引的名称，可于“索引类型”下拉框中选择新建索引的类型，可单击“索引键列”列表框后的“添加”按钮，系统出现“选择索引键列”对话框，如图6-3所示。
图6-3选择索引键列
４．在选择索引键列对话框中，列出了建立索引的表的所有字段，从中选择新建索引所应用的各个列名（即选择作为索引键的各个列），选择完毕后，单击“确定“按钮，系统返回“新建索引”对话框，如图6-4所示。
图6-4“新建索引”对话框
５．在该新建索引对话框中，可通过“索引键列”列表框中的“排序顺序”下拉框，设置相应的索引键列的排序顺序。
（1）可选择“选项”选择页，进入“选项”设置界面，在此，可根据实际需要,设置应用索引时的相关选项，如图6-5所示。
（2）可选择“包含性列”选择页，进入“包含性列”设置界面，在此，可设置另一个表中的列，只有非聚集索引，该选择页才可用。
（3）可选择“存储“选择页，进入“存储”设置界面，在此，可设置对指定的
文件组或方案创建索引。
图6-5“新建索引”对话框
6.3.2使用SQL命令创建索引
语法形式：
CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]
INDEXindex_name
ON{table|view}（column[ASC|DESC][,...n]）
[WITH
[PAD_INDEX]
[[,]FILLFACTOR=fillfactor]
[[,]IGNORE_DUP_KEY]
[[,]DROP_EXISTING]
[[,]STATISTICS_NORECOMPUTE]
[[,]SORT_IN_TEMPDB]]
[ONfilegroup]
其中：
●UNIQUE：
用于指定为表或视图创建唯一索引；
●CLUSTERED：
用于指定创建的索引为聚集索引；
●NONCLUSTERED：
用于指定创建的索引为非聚集索引，默认为非聚集索引；
●index_name：
用于指定所创建的索引名称；
●table：
用于指定创建索引的表的名称；
●view：
用于指定创建索引的视图的名称。
●column：
用于指定被索引的列，即索引所应用的列（索引键中的列）；
●ASC|DESC：
用于指定具体某个索引列的升序或降序排序方向；
●PAD_INDEX：
用于指定索引中间级中每个页（节点）上保持开放的空间；
●FILLFACTOR=fillfactor：
用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，fillfactor的值为1到100。
●IGNORE_DUP_KEY：
用于控制当往包含于一个唯一聚集索引中的列中插入重复数据时SQLServer所作的反应。
●DROP_EXISTING：
用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集索引。
●STATISTICS_NORECOMPUTE：
用于指定过期的索引统计不会自动重新计算。
●SORT_IN_TEMPDB：
用于指定创建索引时的中间排序结果将存储在tempdb数据库中。
●ONfilegroup：
用于指定存放索引的文件组。
【例6-1】在数据库Teaching中，为学生成绩表sgrade建立一个基于“学号，姓名”组合列的唯一、非聚集复合索引s_index1。
UseTeaching
Go
CreateUNIQUEIndexs_index1ONsgrade（xh,xm）
Go
【例6-2】在数据库Teaching中，为学生成绩表sgrade建立一个基于“所在系，班级，姓名”组合列的聚集复合索引s_index2。
UseTeaching
Go
CreateCLUSTEREDIndexs_index2
ONsgrade（szx,bj,xm）
Go
【例6-3】在数据库Teaching中，为学生成绩表sgrade建立一个基于“姓名”列的非聚集索引s_index3。
UseTeaching
Go
CreateIndexs_index3ONsgrade（xmDESC）
Go
6.4操作索引
6.4.1使用SQLServerManagementStudio操作索引
启动SQLServerManagementStudio，并连接到SQLServer2008中的数据库，在“对象资源管理器”窗口中展开“数据库”节点，再展开操作索引的表所属的数据库名（比如Student），再展开其“表”节点，展开索引所属的表名（比如Stab），展开其“索引”节点，右击要操作的索引名，出现弹出菜单，如图6-6所示。
图6-6操作索引
1．查看和修改索引属性
执行图6-6弹出菜单中的【属性】命令，进入“索引属性”对话框，在此，可查看和修改当前索引的有关属性，如图6-7所示。
图6-7“索引属性”对话框
2．重命名索引
执行图6-6弹出菜单中的【重命名】命令，可以重命名当前索引。
3．删除索引
执行图6-6弹出菜单中的【删除】命令，可以删除当前索引。
6.4.2使用SQL命令操作索引
1．查看表的索引信息
语法格式：
EXECsp_helpindex表名
2．重命名索引
语法格式：
EXECsp_rename‘表名.原索引名’,‘表名.新索引名’
3．删除表索引
语法格式：
DROPINDEX表名.索引名
或者
DROPINDEX索引名ON表名或视图名
【例6-4】删除数据库Teaching中，表sgrade的索引s_index2。
UseTeaching
Go
DropIndexsgrade.s_index2
Go
6.5索引的维护
索引创建之后，由于数据的增加、删除和修改等操作会使索引页发生碎块，因此必须对索引进行维护。
SQLServer提供了多种维护索引的方法，常用的有DBCCSHOWCONTIG和DBCCINDEXDEFRAG命令。
1．查看表的数据和索引的碎片信息
语法格式：
（1）DBCCSHOWCONTIG表名或视图名
（2）DBCCSHOWCONTIG（表名或视图名,索引名）
2．整理表中索引碎片
语法格式：
DBCCINDEXDEFRAG（数据库名,表名或视图名[,索引名]）
【例6-5】清除数据库Teaching中的表sgrade的所有索引碎片。
UseTeaching
Go
DBCCINDEXDEFRAG（Teaching,sgrade）
Go
附注：
可在“索引属性”对话框中（如上图6-7所示）通过“碎片”选择页进入“碎片”界面，在此可查看和整理当前索引的碎片情况。
说明：
当数据进行大量的修改后，这时可将原索引删掉，再重新建立索引。
6.6索引视图
对于视图而言，系统为它们动态生成结果集的开销很大，尤其是对于那些涉及大量行进行复杂处理（如聚集大量数据或连接许多行）的视图。
如果在查询中频繁地使用这类视图，应该对视图创建唯一聚集索引，形成索引视图。
索引视图中存放着查询得到的结果集，它在数据库中的存储方式与具有聚集索引的表的存储方式相同，从而提高查询性能。
创建索引视图除要遵照创建标准视图的要求外，还应注意如下几点：
●索引视图只能引用基表，不能引用其他视图。
●索引视图引用的所有基表必须与视图位于同一数据库中，且所有者也与视图相同。
●索引视图引用的基表名称必须由两部分组成，即架构名.表名
●创建索引视图时必须使用WITHSCHEMABINDING选项
●若索引视图定义中使用聚集函数，SELECT列表中必须包括COUNT_BIG（*）。
●索引视图中的表达式引用的所有函数必须是确定的。
【例6-6】在大学生选课管理数据库Student中，创建一个xk_view的索引视图，该视图可查询每个学生的学号、姓名、所选课门数和所选课程的平均成绩。
UseStudent
Go
/*使用WITHSCHEMABINDING选项创建视图xk_view*/
CreateViewxk_view
WITHSCHEMABINDING
AS
Selectdbo.stab.xh学号,dbo.stab.xm姓名,
Count_Big（*）选课门数,Avg（dbo.sctab.cj）平均成绩
Fromdbo.stab,dbo.sctab
Wheredbo.stab.xh=dbo.sctab.xh
GroupBydbo.stab.xh,dbo.stab.xm
Go
/*为视图建立一个基于“学号,姓名”组合列的唯一聚集索引xk_index，形成索引视图*/
CreateUNIQUECLUSTEREDIndexxk_index
ONxk_view（学号,姓名）
Go
/*使用该索引视图xk_view，查看学生选课情况*/
Select*Fromxk_view
Go
6.7任务实现
1．为数据库Student中的课程信息表创建一个复合索引
在数据库Student中，为课程信息表创建一个基于“课程号，课程名”组合列的非聚集复合索引ctab_index1。
UseStudent
Go
CreateIndexctab_index1ONctab（kch,kcm）
Go
2．为数据库Student中的教师教课信息表创建一个聚集复合索引
在数据库Student中，为教师教课信息表创建一个基于“教师号，课程号”组合列的聚集复合索引tctab_index1。
UseStudent
Go
CreateCLUSTEREDIndextctab_index1
ONtctab（jsh,kch）
Go
3．为数据库Student中的学生选课信息表创建一个唯一聚集复合索引
在数据库Student中，为学生选课信息表创建一个基于“学号，课程号”组合列的唯一聚集复合索引sctab_index1。
UseStudent
Go
CreateUNIQUECLUSTEREDIndexsctab_index1
ONsctab（xh,kch）
Go
6.8练习题
1．在客户订货管理数据库goods中，完成如下操作：
（要求使用SQLServerManagementStudio完成）
（1）为商品信息表创建一个基于“商品名，品牌，型号，单价”组合列的聚集索引stab_index1，要求商品单价降序排列。
（2）为客户订货信息表创建一个基于“客户号，商品号”组合列的唯一、聚集复合索引kstab_index1。
2．在图书管理数据库books中，完成如下操作：
（要求使用SQL命令完成）
（1）为图书信息表创建一个基于“出版社，图书名，作者名，单价”组合列的聚集索引book_index1，要求图书单价降序排列。
（2）为读者借阅登记信息表创建一个基于“读者号，图书号”组合列的唯一聚集复合索引record_index1。
升级会员 