《SQL server数据库》课件第6章 索引及应用 .ppt

1、6.1 任务描述任务描述本章完成项目的第6个任务：在大学生选课管理数据库Student中，完成如下操作：1为课程信息表创建一个非聚集复合索引。2为教师教课信息表创建一个聚集复合索引。3为学生选课信息表创建一个唯一、聚集复合索引。6.2 索引综述索引综述 索引的概念索引的概念 数据库中的索引与书籍中的索引（目录）类似，在一本书中，利用索引可以快速查找所需信息，无须阅读整本书。在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。书中的索引是一个词语列表，其中注明了包含各个词的页码。而数据库中的索引是某个表中一列或者若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指

2、针清单。也可以这么说，数据库中某个表的索引是指，将这个表中数据行按照某一列或者若干列值的组合（称为索引键）的大小，只排列各个数据行的顺序，而不改变数据行的存储位置，得到的一个非结构数据文件。2索引的作用索引的作用l 通过创建唯一索引，可以保证数据记录的唯一性。l 通过创建和使用索引可以大大加快数据检索的速度。l 通过创建和使用索引可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。l 通过创建和使用索引使得在使用ORDER BY和GROUP BY子句中进行检索数据时，可以显著减少查询中分组和排序的时间。l 通过索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。6.2

3、索引综述索引综述3索引类型索引类型表或视图可以包含以下类型的索引。（1）聚集索引（2）非聚集索引（3）唯一索引（4）包含性列索引（5）索引视图6.2 索引综述索引综述6.3 创建索引创建索引 使用索引要付出一定的空间和时间的代价，因此为表建立索引时，要根据实际情况，认真考虑哪些列应该索引，哪些列不应该索引。建立索引一般要遵循以下几条原则：主键列上一定要建立索引。外键列上可以建立索引。在经常查询的字段上最好建立索引。对于查询中很少涉及的列、重复值比较多的列不要建立索引。对于定义为text、image和bit数据类型的列上不要建立索引。SQL Server 2008在创建主键约束或唯一约束时，自动

4、创建唯一索引，以强制实施PRIMARY KEY 和UNIQUE约束的唯一性要求。如果需要创建不依赖于约束的索引，可以使用SQL Server Management Studio或者使用SQL命令创建索引。建立索引时要注意以下几点：只有表或视图的所有者才有权建立索引。在建立聚集索引时，将会对表进行复制，对表中的数据进行排序，然后删除原始的表。因此，数据库上必须有足够的空间，以容纳数据复本。在使用CREATE INDEX命令建立索引时，必须指定索引名称、表名称及索引所应用的各列名称（即索引键）。在一个表中最多可建立249个非聚集索引。默认情况下，建立的索引是非聚集索引。复合索引的列的最大数目为16

5、，各列组合的最大长度为900字节。6.3 创建索引创建索引 6.3.1 使用使用SQL Server Management Studio创建索引创建索引 1启动SQL Server Management Studio，并连接到SQL Server 2008中的数据库，在“对象资源管理器”窗口中展开“数据库”节点，再展开建立索引的表所属的数据库名（比如Student），再展开其“表”节点，展开要建立索引的表名（比如Stab），右击其“索引”节点，出现弹出菜单，如图6-1所示。图6-1 新建索引2执行弹出菜单中的【新建索引】命令，系统则出现“新建索引”对话框，如图6-2所示。图6-2“新建索引”对

6、话框6.3.1 使用使用SQL Server Management Studio创建索引创建索引 3在新建索引对话框中，于“索引名称”文本框中输入新建索引的名称，可于“索引类型”下拉框中选择新建索引的类型，可单击“索引键列”列表框后的“添加”按钮，系统出现“选择索引键列”对话框，如图6-3所示。图6-3 选择索引键列 6.3.1 使用使用SQL Server Management Studio创建索引创建索引 在选择索引键列对话框中，列出了建立索引的表的所有字段，从中选择新建索引所应用的各个列名（即选择作为索引键的各个列），选择完毕后，单击“确定“按钮，系统返回“新建索引”对话框，如图6-4所

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

8、 Server Management Studio创建索引创建索引 6.3.2 使用使用SQL命令创建索引命令创建索引 语法形式：语法形式：CREATE UNIQUE CLUSTERED|NONCLUSTERED 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：用于指定为表或视图创建唯一索

9、引。CLUSTERED：用于指定创建的索引为聚集索引。NONCLUSTERED：用于指定创建的索引为非聚集索引，默认为非聚集索引。index_name：用于指定所创建的索引名称。table：用于指定创建索引的表的名称。view：用于指定创建索引的视图的名称。column：用于指定被索引的列，即索引所应用的列（索引键中的列）。ASC|DESC：用于指定具体某个索引列的升序或降序排序方向。PAD_INDEX：用于指定索引中间级中每个页（节点）上保持开放的空间。FILLFACTOR=fillfactor：用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，fillfactor的值为1到100

10、。IGNORE_DUP_KEY：用于控制当往包含于一个唯一聚集索引中的列中插入重复数据时SQL Server所作的反应。DROP_EXISTING：用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集索引。STATISTICS_NORECOMPUTE：用于指定过期的索引统计不会自动重新计算。SORT_IN_TEMPDB：用于指定创建索引时的中间排序结果将存储在 tempdb 数据库中。ON filegroup：用于指定存放索引的文件组。6.3.2 使用使用SQL命令创建索引命令创建索引【例例6-1】在数据库Teaching中，为学生成绩表sgrade建立一个基于“学号，姓名”组合列的唯

11、一、非聚集复合索引s_index1。Use Teaching GoCreate UNIQUE Index s_index1 ON sgrade(xh,xm)Go【例例6-2】在数据库Teaching中，为学生成绩表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

12、 Index s_index2 ON sgrade(xm DESC)Go6.3.2 使用使用SQL命令创建索引命令创建索引 6.4.1使用使用SQL Server Management Studio操作索引操作索引 启动SQL Server Management Studio，并连接到SQL Server 2008中的数据库，在“对象资源管理器”窗口中展开“数据库”节点，再展开操作索引的表所属的数据库名（比如Student），再展开其“表”节点，展开索引所属的表名（比如Stab），展开其“索引”节点，右击要操作的索引名，出现弹出菜单，如图6-6所示。图6-6 操作索引6.4 操作索引操作索引

13、1查看和修改索引属性查看和修改索引属性 执行图6-6弹出菜单中的【属性】命令，进入“索引属性”对话框，在此，可查看和修改当前索引的有关属性，如图6-7所示。图6-7“索引属性”对话框2重命名索引重命名索引 执行图6-6弹出菜单中的【重命名】命令，可以重命名当前索引。3删除索引删除索引 执行图6-6弹出菜单中的【删除】命令，可以删除当前索引。6.4.1使用使用SQL Server Management Studio操作索引操作索引6.4.2 使用使用SQL命令操作索引命令操作索引 1查看表的索引信息查看表的索引信息语法格式：语法格式：EXEC sp_helpindex 表名表名2重命名索引重命名

14、索引语法格式：语法格式：EXEC sp_rename 表名表名.原索引名原索引名 ,表名表名.新索引名新索引名3删除表索引删除表索引语法格式：DROP INDEX 表名.索引名或者DROP INDEX 索引名 ON 表名或视图名【例例6-4】删除数据库Teaching中，表sgrade的索引s_index2。Use TeachingGoDrop Index sgrade.s_index2Go6.4.2 使用使用SQL命令操作索引命令操作索引 6.5 索引的维护索引的维护 索引创建之后，由于数据的增加、删除和修改等操作会使索引页发生碎块，因此必须对索引进行维护。SQL Server提供了多种维护

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

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

17、不能引用其他视图。索引视图引用的所有基表必须与视图位于同一数据库中，且所有者也与视图相同。索引视图引用的基表名称必须由两部分组成，即 架构名.表名 创建索引视图时必须使用 WITH SCHEMABINDING选项 若索引视图定义中使用聚集函数，SELECT列表中必须包括COUNT_BIG(*)。索引视图中的表达式引用的所有函数必须是确定的。6.6 索引视图索引视图 Use StudentGo/*使用WITH SCHEMABINDING选项创建视图xk_view*/Create View xk_viewWITH SCHEMABINDINGAS Select dbo.stab.xh学号 ,dbo.

18、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【例例6-6】在大学生选课管理数据库Student中，创建一个xk_view的索引视图，该视图可查询每个学生的学号、姓名、所选课门数和所选课程的平均成绩。/*为视图建立一个基于“学号,姓名”组合列的唯一聚集索引xk_index，形成索引视图*/Create UNIQUE CLUSTERED Index xk_inde

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

20、创建一个聚集复合索引在数据库Student中，为教师教课信息表创建一个基于“教师号,课程号“组合列的聚集复合索引tctab_index1。Use StudentGoCreate 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.7 任务实现任务实现