1、SQL Server 数据库程序设计,授课教师：姜 姗,本章学习目标 理解索引的作用和分类； 熟练掌握索引的创建、编辑和删除； 熟练掌握索引的管理方法。,第9章 索引,在应用系统中，尤其在联机事务处理系统中，对数据查询及处理速度已成为衡量应用系统成败的标准。而采用索引来加快数据处理速度通常是最普遍采用的优化方法。,索引的概念,数据库中的索引与书籍中的目录类似。 在一本书中，利用索引可以快速查找所需信息，无须阅读整本书。书中的目录是一个词语列表，其中注明了包含各个词的页码。 在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。而数据库中的索引是一个表中所包含的值的列表，其
2、中注明了表中包含各个值的行所在的存储位置。,SQL Server中数据的访问方法： 表扫描法：当访问未建索引的表内数据时，从表的起始处逐行查找，直到符合查询条件为止。 使用索引：当使用索引访问建有索引的表内数据时,系统会通过遍历索引树结构来查找行的存储位置，效率非常高。,通过创建唯一索引，可以增强数据记录的唯一性。 可以大大加快数据检索速度。 可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。 在使用ORDER BY和GROUP BY子句中进行检索数据时，可以显著减少查询中分组和排序的时间。 使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。,索引的作用,不过，
3、索引为性能所带来的好处却是有代价的。带索引的表在数据库中会占据更多的空间。另外，为了维护索引，对数据进行插入、更新、删除操作的命令所花费的时间会更长。 创建索引所需的工作空间约为数据库表的1.2倍，在建立索引时，数据被复制以便建立索引。索引建立后，旧的未加索引的表被删除，创建索引时使用的硬盘空间由系统自动收回。 在设计和创建索引时，应确保对性能的提高程度大于在存储空间和处理资源方面的代价。,索引的注意事项,因创建索引要耗一定的系统性能，因此要考察对某列创建索引的必要性。,如果以存储结构来区分，则有“聚集索引”（Clustered Index，也称聚类索引、簇集索引）和“非聚集索引”（Noncl
4、ustered Index，也称非聚类索引、非簇集索引）的区别； 如果以数据的唯一性来区别，则有“唯一索引”（Unique Index）和“非唯一索引”（Nonunique Index）的不同； 若以键列的个数来区分，则有“单列索引”与“多列索引”的分别。,索引的分类,1聚集索引 聚集索引将数据行的键值在表内排序并存储对应的数据记录，使得数据表物理顺序与索引顺序一致。当以某字段作为关键字建立聚集索引时，表中数据以该字段作为排序根据。因此，一个表只能建立一个聚集索引，但该索引可以包含多个列（组合索引）,2非聚集索引 非聚集索引完全独立于数据行的结构。数据存储在一个地方，索引存储在另一个地方。非聚
5、集索引中的数据排列顺序并不是表格中数据的排列顺序。 SQL Server默认情况下建立的索引是非聚集索引。一个表可以拥有多个非聚集索引，每个非聚集索引提供访问数据的不同排序顺序。,关于非聚集索引 非聚簇索引提高的存取速度，但降低了表的更新的速度 如果硬盘和内存空间有限，应限制非聚簇索引的使用 修改一个表的数据时，同时要维护索引,聚集型索引和非聚集型索引的比较,3唯一索引 唯一索引是指索引值必须是唯一的。聚集索引和非聚集索引均可用于强制表内的唯一性，方法是在现有表上创建索引时指定UNIQUE关键字。确保表内唯一性的另一种方法是使用UNIQUE约束。,4索引视图 对视图创建唯一聚集索引后，结果集将
6、存储在数据库中，就像带有聚集索引的表一样，这样的视图称为索引视图，即是为了实现快速访问而将其结果持续存放于数据库内并创建索引的视图。 索引视图在基础数据不经常更新的情况下效果最佳。维护索引视图的成本可能高于维护表索引的成本。如果基础数据更新频繁，索引视图数据的维护成本就可能超过使用索引视图带来的性能收益。,5全文索引 全文索引可以对存储在数据库中的文本数据进行快速检索。全文索引是一种特殊类型的基于标记的功能性索引，它是由 SQL Server 全文引擎生成和维护的。 每个表只允许有一个全文索引。,1系统自动创建索引 系统在创建表中的其他对象时可以附带地创建新索引。通常情况下，在创建UNIQUE
7、约束或PRIMARY KEY约束时，SQL Server会自动为这些约束列创建聚集索引。 2用户创建索引 除了系统自动生成的索引外，也可以根据实际需要，使用对象资源管理器或利用SQL语句中的CREATE INDEX命令直接创建索引。,创建索引的方法,利用资源管理器创建索引,语法形式CREATE UNIQUE /*是否为唯一索引*/ CLUSTERED | NONCLUSTERED /*索引的组织方式*/INDEX index_name /*索引名称*/ON table | view ( column ASC | DESC ,.n ) /*指定索引定义依据的对象*/ WITH /*索引选项*/,
8、 FILLFACTOR = fillfactor , IGNORE_DUP_KEY , DROP_EXISTING , STATISTICS_NORECOMPUTE , SORT_IN_TEMPDB ON filegroup /*指定索引文件所在的文件组*/,默认值,命令方式创建索引,参数说明如下： CLUSTERED：用于指定创建的索引为聚集索引。 NONCLUSTERED：用于指定创建的索引为非聚集索引。 ASC|DESC：用于指定某个具体索引列的升序或降序排序方式。 FILLFACTOR：填充因子，或填充率。IGNORE_DUP_KEY:当向包含于一个唯一聚集索引的列中插入重复数据时，将
9、忽略该insert或update语句。 DROP_EXISTING：用于指定应删除并重新创建同名的先前存在的聚集索引或非聚集索引。 STATISTICS_NORECOMPUTE：用于指定过期的索引统计不自动重新计算。 SORT_IN_TEMPDB：用于指定创建索引时的中间排序结果将存储在tempdb数据库中。,【例9-1】使用CREATE INDEX语句为表stu_info创建一个非聚集索引，索引字段为name，索引名为idx_name。 CREATE INDEX idx_name ON stu_info ( name ),例1：根据student表的学号和姓名列创建索引idx_xhxm。Us
10、e xskcCreate Index idx_xhxm on student(sno,sname),例2：根据sc表的学号列创建唯一聚集索引。如果输入重复键值，将忽略该insert或update语句。Create unique clustered Index idx_sno_unique on sc (sno)with ignore_dup_key,例3：根据sc表的学号创建索引，使用降序排列，填满率为60。Create Index idx_sno on sc(sno desc)with filefacter = 60,【例9-2】使用CREATE INDEX语句为表course_info创建
11、一个唯一聚集索引，索引字段为course_id，索引名为idx_course_id，要求成批插入数据时忽略重复值，不重新计算统计信息，填充因子取40。 CREATE UNIQUE CLUSTERED INDEX idx_course_id ON course_info ( course_id ) WITH PAD_INDEX, FILLFACTOR = 40, IGNORE_DUP_KEY , STATISTICS_NORECOMPUTE,1利用对象资源管理器查看索引定义,管理索引,2利用系统存储过程查看索引定义 利用系统提供的存储过程sp_helpindex可以查看索引信息，其语法格式如下：
12、 sp_helpindex objname = object_name, 其中， objname = object_name 表示所要查看的当前数据库中表的名称。,例：查看xskc数据库中student表的索引信息。 Exec sp_helpindex student,1利用对象资源管理器更名索引 （1）启动SQL Server Management Studio。 （2）在对象资源管理器窗口里，展开SQL Server实例，选择“数据库”| student |“表”| dbo.stu_info |“索引”| idx_name，单击鼠标右键，然后从弹出的快捷菜单中选择“重命名”命令。 3）所要
13、更名索引的索引名处于编辑状态，输入新的索引名称。,更名索引,2利用系统存储过程更名索引 利用系统提供的存储过程sp_rename可以对索引进行重命名 例：将student表中的索引idx_name更名为idx_stu_name。 Exec sp_rename student.idx_name ,idx_stu_name,1利用对象管理器删除索引 选择“数据库”| student |“表”| dbo.stu_info |“索引”| idx_name，单击鼠标右键，然后从弹出的快捷菜单中选择“删除”命令，打开“删除对象”对话框。,删除索引,2利用T-SQL语句删除索引 删除索引的语法格式如下： D
14、ROP INDEX table_name.index_name ，n 其中，index_name为所要删除的索引的名称。删除索引时，不仅要指定索引，而且必须要指定索引所属的表。,【例9-5】删除stu_info表中的idx_name索引。 DROP INDEX stu_info.idx_name DROP INDEX不能删除系统自动创建的索引，如主键或唯一性约束索引，也不能删除系统表中的索引。,某些不合适的索引影响到SQL Server的性能，随着应用系统的运行，数据不断地发生变化，当数据变化达到某一个程度时将会影响到索引的使用。这时需要对索引进行维护。索引的维护包括重建索引和更新索引统计信息
15、。,维护索引,随着另外应用在执行大块I/O的时候，重建非聚集索引可以降低分片，重建索引实际上是重新组织B-树空间。 无论何时对基础数据执行插入、更新或删除操作，SQL Server 2008数据库引擎都会自动维护索引。 在SQL Server 2008中，可以通过重新组织索引或重新生成索引来修复索引碎片，维护大块I/O的效率。 SQL Server提供了多种维护索引的方法。,重建索引,1检查整理索引碎片 使用DBCC SHOWCONTIG检查有无索引碎片，或使用DBCC INDEXDEFRAG整理索引碎片。 DBCC SHOWCONTIG语句用来显示指定表的数据和索引的碎片信息。当对表进行大量
16、的修改或添加数据之后，应该执行此语句来查看有无碎片。,检查碎片,其语法格式如下： DBCC SHOWCONTIG ( table_name | table_id | view_name | view_id , index_name | index_id ) 例：检查student表的索引idx_stu_name的碎片信息。 DBCC SHOWCONTIG ( student, idx_stu_name ),整理碎片,DBCC INDEXDEFRAG ( database_name | database_id, table_name | table_id | view_name | view_i
17、d , index_name | index_id ) 【例9-7】整理student数据库中stu_info表的索引idx_name上的碎片。 DBCC INDEXDEFRAG ( student, stu_info, idx_name ),2重新组织索引 重新组织索引是通过对叶级页进行物理重新排序，使其与叶节点的逻辑顺序（从左到右）相匹配，从而对表或视图的聚集索引和非聚集索引的叶级别进行碎片整理，使页有序可以提高索引扫描的性能。,使用ALTER INDEX REORGANIZE按逻辑顺序重新排序索引的叶级页。由于这是联机操作，因此在语句运行时仍可使用索引。此方法的缺点是在重新组织数据方面不
18、如索引重新生成操作的效果好，而且不更新统计信息。,3重新生成索引 重新生成索引将删除原索引并创建一个新索引。此过程中将删除碎片，通过使用指定的或现有的填充因子设置压缩页来回收磁盘空间，并在连续页中对索引行重新排序（根据需要分配新页）。可以使用两种方法重新生成聚集索引和非聚集索引： 带 REBUILD 子句的 ALTER INDEX。 带 DROP_EXISTING 子句的 CREATE INDEX。 这种方法的缺点是索引在删除和重新创建周期内为脱机状态，并且操作属原子级。如果中断索引创建，则不会重新创建该索引。,当在一个包含数据的表上创建索引的时候，SQL Server会创建分布数据页来存放有
19、关索引的两种统计信息：分布表和密度表。优化器利用这个页来判断该索引对某个特定查询是否有用。当表的数据改变之后，统计信息有可能是过时的，从而影响优化器追求最有工作的目标。因此，需要对索引统计信息进行更新。,索引统计信息的更新,其语法格式如下： UPDATE STATISTICS table_or_indexed_view_name index_or_statistics_name | ( index_or_statistics_name ,.n ) WITH FULLSCAN | SAMPLE number PERCENT | ROWS | RESAMPLE | ,.n , ALL | COLU
20、MNS | INDEX , NORECOMPUTE ;,参数说明如下。 table_or_indexed_view_name：要更新其统计信息的表或索引视图的名称。 index_or_statistics_name：要更新其统计信息的索引的名称，或要更新的统计信息的名称。 FULLSCAN：通过扫描表或索引视图中的所有行来计算统计信息。 SAMPLE number PERCENT | ROWS ：当查询优化器更新统计信息时要使用的表或索引视图中近似的百分比或行数。 RESAMPLE：使用最近的采样速率更新每个统计信息。 ALL | COLUMNS | INDEX：指定 UPDATE STATISTICS 语句是否影响列统计信息、索引统计信息或所有现有统计信息。 NORECOMPUTE：指定不自动重新计算过期统计信息。,【例9-8】更新student数据库中stu_info表中全部索引的统计信息。 UPDATE STATISTICS stu_info,
