《sql数据库SQLServer2008索引》由会员分享，可在线阅读，更多相关《sql数据库SQLServer2008索引（48页珍藏版）》请在金锄头文库上搜索。
1、SQL Server 数据库程序设计授课教师：姜 姗本章学习目标理解索引的作用和分类；熟练掌握索引的创建、编辑和删除；熟练掌握索引的管理方法。第9章 索引在应用系统中，尤其在联机事务处理系统中，对数据查询及处理速度已成为衡量应用系统成败的标准。而采用索引来加快数据处理速度通常是最普遍采用的优化方法。索引的概念数据库中的索引与书籍中的目录类似。 在一本书中，利用索引可以快速查找所需信息 ，无须阅读整本书。书中的目录是一个词语列表 ，其中注明了包含各个词的页码。 在数据库中，索引使数据库程序无须对整个表 进行扫描，就可以在其中找到所需数据。而数据 库中的索引是一个表中所包含的值的列表，其中 注明了
2、表中包含各个值的行所在的存储位置。 SQL Server中数据的访问方法： 表扫描法：当访问未建索引的表内数据时，从 表的起始处逐行查找，直到符合查询条件为止。 使用索引：当使用索引访问建有索引的表内数 据时,系统会通过遍历索引树结构来查找行的存 储位置，效率非常高。通过创建唯一索引，可以增强数据记录的唯一 性。可以大大加快数据检索速度。可以加速表与表之间的连接，这一点在实现数 据的参照完整性方面有特别的意义。在使用ORDER BY和GROUP BY子句中进行检索数 据时，可以显著减少查询中分组和排序的时间。使用索引可以在检索数据的过程中使用优化隐 藏器，提高系统性能。索引的作用不过，索引为性
3、能所带来的好处却是有代价的。 带索引的表在数据库中会占据更多的空间。另外 ，为了维护索引，对数据进行插入、更新、删除 操作的命令所花费的时间会更长。 创建索引所需的工作空间约为数据库表的1.2倍 ，在建立索引时，数据被复制以便建立索引。索 引建立后，旧的未加索引的表被删除，创建索引 时使用的硬盘空间由系统自动收回。 在设计和创建索引时，应确保对性能的提高程度 大于在存储空间和处理资源方面的代价。 索引的注意事项因创建索引要耗一定的系统性能，因此要考察对某列 创建索引的必要性。定义有主关键字和 外部关键字的列需在指定范围中快速 或频繁查询的列需要按排序顺序快速 或频繁检索的列在集合过程中需要快速
4、 或频繁组合到一起的列 这些情 况要考虑 创建索引在查询中几乎不涉 及的列 很少有唯一值的列由text,ntext或image 数据类型定义的列 只有较少行数的表没 必要建索引 可不考虑 创建索引 如果以存储结构来区分，则有“聚集索引”（ Clustered Index，也称聚类索引、簇集索引）和“ 非聚集索引”（Nonclustered Index，也称非聚类 索引、非簇集索引）的区别； 如果以数据的唯一性来区别，则有“唯一索引”（ Unique Index）和“非唯一索引”（Nonunique Index）的不同； 若以键列的个数来区分，则有“单列索引”与“多 列索引”的分别。索引的分类1
5、聚集索引聚集索引将数据行的键值在表内排序并存储对应的数据记录，使得数据表物理顺序与索引顺序一致。当以某字段作为关键字建立聚集索引时，表中数据以该字段作为排序根据。因此，一个表只能建立一个聚集索引，但该索引可以包含多个列（组合索引） 2非聚集索引非聚集索引完全独立于数据行的结构。数据存储 在一个地方，索引存储在另一个地方。非聚集索 引中的数据排列顺序并不是表格中数据的排列顺 序。SQL Server默认情况下建立的索引是非聚集索引。一个表可以拥有多个非聚集索引，每个非聚 集索引提供访问数据的不同排序顺序。关于非聚集索引 非聚簇索引提高的存取速度，但降低了表的更新的速 度 如果硬盘和内存空间有限，
6、应限制非聚簇索引的使用 修改一个表的数据时，同时要维护索引存取速度索引的数量所需空间聚集索引非聚集索引快一表一个少慢一表可以多个多聚集型索引和非聚集型索引的比较建立聚集索引 的必要性查询命令的回传结果是以 该字段为排序条件需要回传局部范围的大量 数据 表格中某字段内容的重复 性比较大 要考虑建非聚集 索引的情况查询所获数据量较少时 某字段中的数据的唯 一性比较高时3唯一索引唯一索引是指索引值必须是唯一的。聚集索引和非聚集索引均可用于强制表内的唯一性，方法是在现有表上创建索引时指定UNIQUE关键字。确保表内唯一性的另一种方法是使用UNIQUE约束。4索引视图 对视图创建唯一聚集索引后，结果集将
7、存储在数 据库中，就像带有聚集索引的表一样，这样的视 图称为索引视图，即是为了实现快速访问而将其 结果持续存放于数据库内并创建索引的视图。 索引视图在基础数据不经常更新的情况下效果 最佳。维护索引视图的成本可能高于维护表索引 的成本。如果基础数据更新频繁，索引视图数据 的维护成本就可能超过使用索引视图带来的性能 收益。5全文索引全文索引可以对存储在数据库中的文本数据进行快速检索。全文索引是一种特殊类型的基于标记的功能性索引，它是由 SQL Server 全文引擎生成和维护的。每个表只允许有一个全文索引。 1系统自动创建索引系统在创建表中的其他对象时可以附带地创建新索引 。通常情况下，在创建UN
8、IQUE约束或PRIMARY KEY 约束时，SQL Server会自动为这些约束列创建聚集索引。 2用户创建索引除了系统自动生成的索引外，也可以根据实际需要， 使用对象资源管理器或利用SQL语句中的CREATE INDEX命令直接创建索引。创建索引的方法利用资源管理器创建索引语法形式CREATE UNIQUE /*是否为唯一索引*/ CLUSTERED | NONCLUSTERED /*索引的组织方式*/INDEX index_name /*索引名称*/ON table | view ( column ASC | DESC ,.n ) /*指定索引定义依据的对象*/ WITH /*索引选项*
9、/, FILLFACTOR = fillfactor , IGNORE_DUP_KEY , DROP_EXISTING , STATISTICS_NORECOMPUTE , SORT_IN_TEMPDB ON filegroup /*指定索引文件所在的文件组*/默认值命令方式创建索引参数说明如下： CLUSTERED：用于指定创建的索引为聚集索引。 NONCLUSTERED：用于指定创建的索引为非聚集索引。 ASC|DESC：用于指定某个具体索引列的升序或降序排序 方式。 FILLFACTOR：填充因子，或填充率。IGNORE_DUP_KEY:当向包含于一个唯一聚集索引的列 中插入重复数据时，
10、将忽略该insert或update语句。 DROP_EXISTING：用于指定应删除并重新创建同名的先 前存在的聚集索引或非聚集索引。 STATISTICS_NORECOMPUTE：用于指定过期的索引统 计不自动重新计算。 SORT_IN_TEMPDB：用于指定创建索引时的中间排序结 果将存储在tempdb数据库中。【例9-1】使用CREATE INDEX语句为表stu_info创建一个非聚集索引，索引字段为name，索引名为idx_name。CREATE INDEX idx_nameON stu_info ( name ) 例1：根据student表的学号和姓名列创建索引 idx_xhxm。
11、 Use xskc Create 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
12、_info创建 一个唯一聚集索引，索引字段为course_id，索引名为 idx_course_id，要求成批插入数据时忽略重复值，不重新 计算统计信息，填充因子取40。CREATE UNIQUE CLUSTERED INDEX idx_course_idON course_info ( course_id )WITH PAD_INDEX, FILLFACTOR = 40, IGNORE_DUP_KEY , STATISTICS_NORECOMPUTE 1利用对象资源管理器查看索引定义管理索引2利用系统存储过程查看索引定义利用系统提供的存储过程sp_helpindex可以查看索引信息，其语法格
13、式如下：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）所要更名索
14、引的索引名处于编辑状态，输入新的索引名称。更名索引 2利用系统存储过程更名索引利用系统提供的存储过程sp_rename可以对索引进行重命名 例：将student表中的索引idx_name更名为idx_stu_name。 Exec sp_rename student.idx_name ,idx_stu_name1利用对象管理器删除索引选择“数据库”| student |“表”| dbo.stu_info |“索引”| idx_name，单击鼠标右键，然后从弹出的快捷菜单中选择“删除”命令，打开“删除对象”对话框。删除索引2利用T-SQL语句删除索引删除索引的语法格式如下：DROP INDEX t
15、able_name.index_name ，n 其中，index_name为所要删除的索引的名称。删除索引时，不仅要指定索引，而且必须要指定索引所属的表。【例9-5】删除stu_info表中的idx_name索引。DROP INDEX stu_info.idx_nameDROP INDEX不能删除系统自动创建的索引，如主键或唯一性约束索引，也不能删除系统表中的索引。某些不合适的索引影响到SQL Server的性能，随着应用系统的运行，数据不断地发生变化，当数据变化达到某一个程度时将会影响到索引的使用。这时需要对索引进行维护。索引的维护包括重建索引和更新索引统计信息。维护索引 随着另外应用在执行大块I/O的时候，重建非聚 集索引可以降低分片，重建索引实际上是重新组 织B-树空间。 无论何时对基础数据执行插入、更新或删除操作 ，SQL Server 2008数据库引擎都会自动维护索引 。 在SQL Server 2008中，可以通过重新组织索引或 重新生成索引来修复索引碎片，维护大块I/O的 效率。 SQL Server提供了多种维护索引的方法。重建索引1检查整理索引碎片 使用DBCC SHOWCONTIG检查有无索引碎片， 或使用DBCC INDEXDEFRAG整理索引碎片。 DBCC SHOWCONTIG语句
