索引及其应用.docx

1、索引及其应用第6章 索引及其应用 教学目标通过本章学习，使学生掌握索引的基本概念、分类和作用，掌握索引的建立和操作方法，掌握索引的维护方法，会根据实际问题的需要，能够熟练地建立表和视图的相关索引。 教学要求知识要点能力要求关联知识索引概念、分类和作用(1)掌握索引的基本概念、分类和作用索引概念、分类和作用索引的建立和操作(1)掌握索引的建立方法(2)掌握索引的操作方法SQL Server Management Studio建立和操作索引，CREATE INDEX等SQL命令索引的维护(1)掌握维护索引的常用方法DBCC SHOWCONTIG和DBCC INDEXDEFRAG 命令索引视图(1)

2、掌握索引视图的建立和应用方法CREATE VIEW，CREATE INDEX等SQL命令 重点难点 索引的概念、分类和作用 索引的建立和操作方法 索引视图的建立与应用6.1任务描述本章完成项目的第6个任务：在大学生选课管理数据库Student中，完成如下操作：1为课程信息表创建一个非聚集复合索引。2为教师教课信息表创建一个聚集复合索引。3为学生选课信息表创建一个唯一、聚集复合索引。6.2索引综述数据库中的索引可以快速找到表或索引视图中的特定信息。索引包含从表或视图中一个或多个列生成的键，以及映射到指定数据的存储位置的指针。通过创建、设计良好的索引以支持查询，可以显著提高数据库查询和用应程序的性

3、能。索引可以减少为返回查询结果集而必须读取的数据量。索引还可以强制表中的行具有唯一性，从而确保表数据的数据完整性。索引的概念数据库中的索引与书籍中的索引（目录）类似，在一本书中，利用索引可以快速查找所需信息，无须阅读整本书。在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。书中的索引是一个词语列表，其中注明了包含各个词的页码。而数据库中的索引是某个表中一列或者若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。 也可以这么说，数据库中某个表的索引是指，将这个表中数据行按照某一列或者若干列值的组合（称为索引键）的大小，只排列各个数据行的顺序，而不改变数据

4、行的存储位置，得到的一个非结构数据文件。2索引的作用 通过创建唯一索引，可以保证数据记录的唯一性。 通过创建和使用索引可以大大加快数据检索的速度。 通过创建和使用索引可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。 通过创建和使用索引使得在使用ORDER BY和GROUP BY子句中进行检索数据时，可以显著减少查询中分组和排序的时间。 通过索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。3索引类型表或视图可以包含以下类型的索引。（1）聚集索引聚集索引是指表中数据行的物理存储顺序与索引列顺序完全相同。聚集索引是根据数据行的键值在表或视图中排序而存储这些数据行。索引

5、定义中包含聚集索引列。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序方式排序。只有当表包含聚集索引时，表中的数据行才按排序顺序存储。如果表没有聚集索引，则其数据行存储在一个称为堆的无序结构中。（2）非聚集索引非聚集索引不改变表中数据行的物理存储位置，数据与索引分开存储，通过索引带有的指针与表中的数据发生联系。非聚集索引具有独立于数据行的结构。非聚集索引包含非聚集索引键值，并且每个键值项都是指向包含该键值的数据行的指针。一个表或视图可含有多个非聚集索引。聚集索引和非聚集索引都可以是唯一的。这意味着任何两行都不能有相同的索引键值。另外，索引也可以不是唯一的，即多行可以共享同一个索引键值。每

6、当修改了数据表内容后，都会自动维护表或视图的索引。（3）唯一索引唯一索引确保索引键不包含重复的值，因此，表或视图中的每一行在某种程度上是唯一的。（4）包含性列索引是一种非聚集索引，它扩展后不仅包含键列，还包含非键列。（5）索引视图视图的索引将具体化（执行）视图，并将结果集永久存储在唯一的聚集索引中，而且存储方法与带聚集索引的表的存储方法相同。创建聚集索引后，可以为视图添加非聚集索引。6.3创建索引使用索引要付出一定的空间和时间的代价，因此为表建立索引时，要根据实际情况，认真考虑哪些列应该索引，哪些列不应该索引。建立索引一般要遵循以下几条原则： 主键列上一定要建立索引; 外键列上可以建立索引;

7、在经常查询的字段上最好建立索引; 对于查询中很少涉及的列、重复值比较多的列不要建立索引; 对于定义为text、image和bit数据类型的列上不要建立索引;SQL Server 2008在创建主键约束或唯一约束时，自动创建唯一索引，以强制实施PRIMARY KEY 和UNIQUE约束的唯一性要求。如果需要创建不依赖于约束的索引，可以使用SQL Server Management Studio或者使用SQL命令创建索引。建立索引时要注意以下几点： 只有表或视图的所有者才有权建立索引。 在建立聚集索引时，将会对表进行复制，对表中的数据进行排序，然后删除原始的表。因此，数据库上必须有足够的空间，以容

8、纳数据复本。 在使用CREATE INDEX命令建立索引时，必须指定索引名称、表名称及索引所应用的各列名称（即索引键）。 在一个表中最多可建立249个非聚集索引。默认情况下，建立的索引是非聚集索引。 复合索引的列的最大数目为16，各列组合的最大长度为900字节。6.3.1 使用SQL Server Management Studio创建索引1启动SQL Server Management Studio，并连接到SQL Server 2008中的数据库，在“对象资源管理器”窗口中展开“数据库”节点，再展开建立索引的表所属的数据库名（比如Student），再展开其“表”节点，展开要建立索引的表名（

9、比如Stab），右击其“索引”节点，出现弹出菜单，如图6-1所示。图6-1 新建索引2执行弹出菜单中的【新建索引】命令，系统则出现“新建索引”对话框，如图6-2所示。图6-2“新建索引”对话框3在新建索引对话框中，于“索引名称”文本框中输入新建索引的名称，可于“索引类型”下拉框中选择新建索引的类型，可单击“索引键列”列表框后的“添加”按钮，系统出现“选择索引键列”对话框，如图6-3所示。图6-3 选择索引键列在选择索引键列对话框中，列出了建立索引的表的所有字段，从中选择新建索引所应用的各个列名（即选择作为索引键的各个列），选择完毕后，单击“确定“按钮，系统返回“新建索引”对话框，如图6-4所示

10、。 图6-4“新建索引”对话框在该新建索引对话框中，可通过“索引键列”列表框中的“排序顺序”下拉框，设置相应的索引键列的排序顺序。(1) 可选择“选项”选择页，进入“选项”设置界面，在此，可根据实际需要,设置应用索引时的相关选项，如图6-5所示。(2) 可选择“包含性列”选择页，进入“包含性列”设置界面，在此，可设置另一个表中的列，只有非聚集索引，该选择页才可用。(3) 可选择“存储“选择页，进入“存储”设置界面，在此，可设置对指定的文件组或方案创建索引。 图6-5“新建索引”对话框6.3.2 使用SQL命令创建索引语法形式：CREATE UNIQUE CLUSTERED| NONCLUSTE

11、RED INDEX index_name ON table | view ( column ASC | DESC ,.n ) WITHPAD_INDEX,FILLFACTOR=fillfactor,IGNORE_DUP_KEY,DROP_EXISTING,STATISTICS_NORECOMPUTE,SORT_IN_TEMPDB ON filegroup 其中： UNIQUE：用于指定为表或视图创建唯一索引； CLUSTERED：用于指定创建的索引为聚集索引； NONCLUSTERED：用于指定创建的索引为非聚集索引，默认为非聚集索引； index_name：用于指定所创建的索引名称； tab

12、le：用于指定创建索引的表的名称； view：用于指定创建索引的视图的名称。 column：用于指定被索引的列，即索引所应用的列（索引键中的列）； ASC|DESC：用于指定具体某个索引列的升序或降序排序方向； PAD_INDEX：用于指定索引中间级中每个页（节点）上保持开放的空间 ； FILLFACTOR = fillfactor：用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，fillfactor的值为1到100。 IGNORE_DUP_KEY：用于控制当往包含于一个唯一聚集索引中的列中插入重复数据时SQL Server所作的反应。 DROP_EXISTING：用于指定应删除并

13、重新创建已命名的先前存在的聚集索引或者非聚集索引。 STATISTICS_NORECOMPUTE：用于指定过期的索引统计不会自动重新计算。 SORT_IN_TEMPDB：用于指定创建索引时的中间排序结果将存储在 tempdb 数据库中。 ON filegroup：用于指定存放索引的文件组。 【例6-1】在数据库Teaching中，为学生成绩表sgrade建立一个基于“学号，姓名” 组合列的唯一、非聚集复合索引s_index1。Use Teaching GoCreate UNIQUE Index s_index1 ON sgrade(xh , xm)Go【例6-2】在数据库Teaching中，为

14、学生成绩表sgrade建立一个基于“所在系，班级，姓名”组合列的聚集复合索引s_index2。Use Teaching GoCreate CLUSTERED Index s_index2 ON sgrade(szx , bj , xm) Go【例6-3】在数据库Teaching中，为学生成绩表sgrade建立一个基于“姓名”列的非聚集索引s_index3。Use Teaching GoCreate Index s_index3 ON sgrade(xm DESC) Go6.4操作索引6.4.1使用SQL Server Management Studio操作索引启动SQL Server Mana

15、gement Studio，并连接到SQL Server 2008中的数据库，在“对象资源管理器”窗口中展开“数据库”节点，再展开操作索引的表所属的数据库名（比如Student），再展开其“表”节点，展开索引所属的表名（比如Stab），展开其“索引”节点，右击要操作的索引名，出现弹出菜单，如图6-6所示。图6-6 操作索引1查看和修改索引属性执行图6-6弹出菜单中的【属性】命令，进入“索引属性”对话框，在此，可查看和修改当前索引的有关属性，如图6-7所示。图6-7“索引属性”对话框2重命名索引执行图6-6弹出菜单中的【重命名】命令，可以重命名当前索引。3删除索引执行图6-6弹出菜单中的【删除】

16、命令，可以删除当前索引。6.4.2使用SQL命令操作索引1查看表的索引信息语法格式：EXEC sp_helpindex 表名2重命名索引语法格式：EXEC sp_rename 表名.原索引名 , 表名.新索引名3删除表索引语法格式：DROP INDEX 表名.索引名或者DROP INDEX 索引名 ON 表名或视图名【例6-4】删除数据库Teaching中，表sgrade的索引s_index2。Use TeachingGoDrop Index sgrade.s_index2Go6.5索引的维护索引创建之后，由于数据的增加、删除和修改等操作会使索引页发生碎块，因此必须对索引进行维护。SQL Se

17、rver提供了多种维护索引的方法，常用的有DBCC SHOWCONTIG和DBCC INDEXDEFRAG 命令。1查看表的数据和索引的碎片信息语法格式：（1）DBCC SHOWCONTIG 表名或视图名（2）DBCC SHOWCONTIG （表名或视图名 , 索引名）2整理表中索引碎片语法格式：DBCC INDEXDEFRAG（数据库名 , 表名或视图名 , 索引名）【例6-5】清除数据库Teaching中的表sgrade的所有索引碎片。Use TeachingGoDBCC INDEXDEFRAG(Teaching , sgrade)Go附注：可在“索引属性”对话框中（如上图6-7所示）通过

18、“碎片”选择页进入“碎片”界面，在此可查看和整理当前索引的碎片情况。说明：当数据进行大量的修改后，这时可将原索引删掉，再重新建立索引。6.6索引视图对于视图而言，系统为它们动态生成结果集的开销很大，尤其是对于那些涉及大量行进行复杂处理（如聚集大量数据或连接许多行）的视图。如果在查询中频繁地使用这类视图，应该对视图创建唯一聚集索引，形成索引视图。索引视图中存放着查询得到的结果集，它在数据库中的存储方式与具有聚集索引的表的存储方式相同，从而提高查询性能。创建索引视图除要遵照创建标准视图的要求外，还应注意如下几点： 索引视图只能引用基表，不能引用其他视图。 索引视图引用的所有基表必须与视图位于同一数

19、据库中，且所有者也与视图相同。 索引视图引用的基表名称必须由两部分组成，即 架构名.表名 创建索引视图时必须使用 WITH SCHEMABINDING选项 若索引视图定义中使用聚集函数，SELECT列表中必须包括COUNT_BIG(*)。 索引视图中的表达式引用的所有函数必须是确定的。【例6-6】在大学生选课管理数据库Student中，创建一个xk_view的索引视图，该视图可查询每个学生的学号、姓名、所选课门数和所选课程的平均成绩。Use StudentGo/*使用WITH SCHEMABINDING选项创建视图xk_view*/Create View xk_viewWITH SCHEMAB

20、INDINGAS Select dbo.stab.xh学号 , dbo.stab.xm 姓名 , Count_Big(*) 选课门数 , Avg(dbo.sctab.cj) 平均成绩 From dbo.stab , dbo.sctab Where dbo.stab.xh=dbo.sctab.xhGroup By dbo.stab.xh , dbo.stab.xmGo/*为视图建立一个基于“学号, 姓名”组合列的唯一聚集索引xk_index，形成索引视图*/Create UNIQUE CLUSTERED Index xk_index ON xk_view (学号 , 姓名)Go/*使用该索引视图

21、xk_view，查看学生选课情况*/Select * From xk_viewGo6.7任务实现1为数据库Student中的课程信息表创建一个复合索引在数据库Student中，为课程信息表创建一个基于“课程号，课程名”组合列的非聚集复合索引ctab_index1。Use StudentGoCreate Index ctab_index1 ON ctab(kch , kcm)Go2为数据库Student中的教师教课信息表创建一个聚集复合索引在数据库Student中，为教师教课信息表创建一个基于“教师号，课程号”组合列的聚集复合索引tctab_index1。Use StudentGoCreate

22、CLUSTERED Index tctab_index1 ON tctab(jsh , kch)Go3为数据库Student中的学生选课信息表创建一个唯一聚集复合索引在数据库Student中，为学生选课信息表创建一个基于“学号，课程号”组合列的唯一聚集复合索引sctab_index1。Use StudentGoCreate UNIQUE CLUSTERED Index sctab_index1 ON sctab(xh , kch )Go6.8练习题1在客户订货管理数据库goods中，完成如下操作：（要求使用SQL Server Management Studio完成）（1）为商品信息表创建一个基于“商品名，品牌，型号，单价”组合列的聚集索引stab_index1，要求商品单价降序排列。（2）为客户订货信息表创建一个基于“客户号，商品号”组合列的唯一、聚集复合索引kstab_index1。2在图书管理数据库books中，完成如下操作：（要求使用SQL命令完成）（1）为图书信息表创建一个基于“出版社，图书名，作者名，单价”组合列的聚集索引book_index1，要求图书单价降序排列。（2）为读者借阅登记信息表创建一个基于“读者号，图书号”组合列的唯一聚集复合索引record_index1。