sql_数据库__第九章_SQL_Server_2008索引

SQLServer数据库程序设计授课教师：姜姗本章学习目标理解索引的作用和分类；熟练掌握索引的创建、编辑和删除；熟练掌握索引的管理方法。第9章索引在应用系统中，尤其在联机事务处理系统中，对数据查询及处理速度已成为衡量应用系统成败的标准。而采用索引来加快数据处理速度通常是最普遍采用的优化方法。索引的概念数据库中的索引与书籍中的目录类似。在一本书中，利用索引可以快速查找所需信息，无须阅读整本书。书中的目录是一个词语列表，其中注明了包含各个词的页码。在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。而数据库中的索引是一个表中所包含的值的列表，其中注明了表中包含各个值的行所在的存储位置。SQLServer中数据的访问方法：表扫描法：当访问未建索引的表内数据时，从表的起始处逐行查找，直到符合查询条件为止。使用索引：当使用索引访问建有索引的表内数据时,系统会通过遍历索引树结构来查找行的存储位置，效率非常高。通过创建唯一索引，可以增强数据记录的唯一性。可以大大加快数据检索速度。可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。在使用ORDERBY和GROUPBY子句中进行检索数据时，可以显著减少查询中分组和排序的时间。使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。索引的作用不过，索引为性能所带来的好处却是有代价的。带索引的表在数据库中会占据更多的空间。另外，为了维护索引，对数据进行插入、更新、删除操作的命令所花费的时间会更长。创建索引所需的工作空间约为数据库表的1.2倍，在建立索引时，数据被复制以便建立索引。索引建立后，旧的未加索引的表被删除，创建索引时使用的硬盘空间由系统自动收回。在设计和创建索引时，应确保对性能的提高程度大于在存储空间和处理资源方面的代价。索引的注意事项因创建索引要耗一定的系统性能，因此要考察对某列创建索引的必要性。定义有主关键字和外部关键字的列需在指定范围中快速或频繁查询的列需要按排序顺序快速或频繁检索的列在集合过程中需要快速或频繁组合到一起的列这些情况要考虑创建索引在查询中几乎不涉及的列很少有唯一值的列由text,ntext或image数据类型定义的列只有较少行数的表没必要建索引可不考虑创建索引如果以存储结构来区分，则有“聚集索引”（ClusteredIndex，也称聚类索引、簇集索引）和“非聚集索引”（NonclusteredIndex，也称非聚类索引、非簇集索引）的区别；如果以数据的唯一性来区别，则有“唯一索引”（UniqueIndex）和“非唯一索引”（NonuniqueIndex）的不同；若以键列的个数来区分，则有“单列索引”与“多列索引”的分别。索引的分类1．聚集索引聚集索引将数据行的键值在表内排序并存储对应的数据记录，使得数据表物理顺序与索引顺序一致。当以某字段作为关键字建立聚集索引时，表中数据以该字段作为排序根据。因此，一个表只能建立一个聚集索引，但该索引可以包含多个列（组合索引）2．非聚集索引非聚集索引完全独立于数据行的结构。数据存储在一个地方，索引存储在另一个地方。非聚集索引中的数据排列顺序并不是表格中数据的排列顺序。SQLServer默认情况下建立的索引是非聚集索引。一个表可以拥有多个非聚集索引，每个非聚集索引提供访问数据的不同排序顺序。关于非聚集索引非聚簇索引提高的存取速度，但降低了表的更新的速度如果硬盘和内存空间有限，应限制非聚簇索引的使用修改一个表的数据时，同时要维护索引存取速度索引的数量所需空间聚集索引非聚集索引快一表一个少慢一表可以多个多聚集型索引和非聚集型索引的比较建立聚集索引的必要性查询命令的回传结果是以该字段为排序条件需要回传局部范围的大量数据表格中某字段内容的重复性比较大要考虑建非聚集索引的情况查询所获数据量较少时某字段中的数据的唯一性比较高时3．唯一索引唯一索引是指索引值必须是唯一的。聚集索引和非聚集索引均可用于强制表内的唯一性，方法是在现有表上创建索引时指定UNIQUE关键字。确保表内唯一性的另一种方法是使用UNIQUE约束。4．索引视图对视图创建唯一聚集索引后，结果集将存储在数据库中，就像带有聚集索引的表一样，这样的视图称为索引视图，即是为了实现快速访问而将其结果持续存放于数据库内并创建索引的视图。索引视图在基础数据不经常更新的情况下效果最佳。维护索引视图的成本可能高于维护表索引的成本。如果基础数据更新频繁，索引视图数据的维护成本就可能超过使用索引视图带来的性能收益。5．全文索引全文索引可以对存储在数据库中的文本数据进行快速检索。全文索引是一种特殊类型的基于标记的功能性索引，它是由SQLServer全文引擎生成和维护的。每个表只允许有一个全文索引。1．系统自动创建索引系统在创建表中的其他对象时可以附带地创建新索引。通常情况下，在创建UNIQUE约束或PRIMARYKEY约束时，SQLServer会自动为这些约束列创建聚集索引。2．用户创建索引除了系统自动生成的索引外，也可以根据实际需要，使用对象资源管理器或利用SQL语句中的CREATEINDEX命令直接创建索引。创建索引的方法利用资源管理器创建索引语法形式CREATE[UNIQUE]/*是否为唯一索引*/[CLUSTERED|NONCLUSTERED]/*索引的组织方式*/INDEXindex_name/*索引名称*/ON{table|view}(column[ASC|DESC][,...n])/*指定索引定义依据的对象*/[WITHindex_option/*索引选项*/[,]FILLFACTOR=fillfactor]][[,]IGNORE_DUP_KEY][[,]DROP_EXISTING][[,]STATISTICS_NORECOMPUTE][[,]SORT_IN_TEMPDB][ONfilegroup]/*指定索引文件所在的文件组*/默认值命令方式创建索引参数说明如下：CLUSTERED：用于指定创建的索引为聚集索引。NONCLUSTERED：用于指定创建的索引为非聚集索引。ASC|DESC：用于指定某个具体索引列的升序或降序排序方式。FILLFACTOR：填充因子，或填充率。IGNORE_DUP_KEY:当向包含于一个唯一聚集索引的列中插入重复数据时，将忽略该insert或update语句。DROP_EXISTING：用于指定应删除并重新创建同名的先前存在的聚集索引或非聚集索引。STATISTICS_NORECOMPUTE：用于指定过期的索引统计不自动重新计算。SORT_IN_TEMPDB：用于指定创建索引时的中间排序结果将存储在tempdb数据库中。【例9-1】使用CREATEINDEX语句为表stu_info创建一个非聚集索引，索引字段为name，索引名为idx_name。CREATEINDEXidx_nameONstu_info(name)例1：根据student表的学号和姓名列创建索引idx_xhxm。UsexskcCreateIndexidx_xhxmonstudent(sno,sname)•例2：根据sc表的学号列创建唯一聚集索引。如果输入重复键值，将忽略该insert或update语句。CreateuniqueclusteredIndexidx_sno_uniqueonsc(sno)withignore_dup_key例3：根据sc表的学号创建索引，使用降序排列，填满率为60％。CreateIndexidx_snoonsc(snodesc)withfilefacter=60【例9-2】使用CREATEINDEX语句为表course_info创建一个唯一聚集索引，索引字段为course_id，索引名为idx_course_id，要求成批插入数据时忽略重复值，不重新计算统计信息，填充因子取40。CREATEUNIQUECLUSTEREDINDEXidx_course_idONcourse_info(course_id)WITHPAD_INDEX,FILLFACTOR=40,IGNORE_DUP_KEY,STATISTICS_NORECOMPUTE1．利用对象资源管理器查看索引定义管理索引2．利用系统存储过程查看索引定义利用系统提供的存储过程sp_helpindex可以查看索引信息，其语法格式如下：sp_helpindex[@objname=]‘object_name’,其中，[@objname=]‘object_name’表示所要查看的当前数据库中表的名称。例：查看xskc数据库中student表的索引信息。Execsp_helpindexstudent1．利用对象资源管理器更名索引（1）启动SQLServerManagementStudio。（2）在对象资源管理器窗口里，展开SQLServer实例，选择“数据库”|student|“表”|dbo.stu_info|“索引”|idx_name，单击鼠标右键，然后从弹出的快捷菜单中选择“重命名”命令。3）所要更名索引的索引名处于编辑状态，输入新的索引名称。更名索引2．利用系统存储过程更名索引利用系统提供的存储过程sp_rename可以对索引进行重命名例：将student表中的索引idx_name更名为idx_stu_name。Execsp_rename‘student.idx_name’,’idx_stu_name’1．利用对象管理器删除索引选择“数据库”|student|“表”|dbo.stu_info|“索引”|idx_name，单击鼠标右键，然后从弹出的快捷菜单中选择“删除”命令，打开“删除对象”对话框。删除索引2．利用T-SQL语句删除索引删除索引的语法格式如下：DROPINDEXtable_name.index_name[，．．．n]其中，index_name为所要删除的索引的名称。删除索引时，不仅要指定索引，而且必须要指定索引所属的表。【例9-5】删除stu_info表中的idx_name索引。DROPINDEXstu_info.idx_nameDROPINDEX不能删除系统自动创建的索引，如主键或唯一性约束索引，也不能删除系统表中的索引。某些不合适的索引影响到SQLServer的性能，随着应用系统的运行，数据不断地发生变化，当数据变化达到某一个程度时将会影响到索引的使用。这时需要对索引进行维护。索引的维护包括重建索引和更新索引统计信息。维护索引随着另外应用在执行大块I/O的时候，重建非聚集索引可以降低分片，重建索引实际上是重新组织B-树空间。无论何时对基础数据执行插入、更新或删除操作，SQLServer2008数据库引擎都会自动维护索引。在SQLServer2008中，可以通过重新组织索引或重新生成索引来修复索引碎片，维护大块I/O的效率。SQLServer提供了多种维护索引的方法。重建索引1．检查整理索引碎片使用DBCCSHOWCONTIG检查有无索引碎片，或使用DBCCINDEXDEFRAG整理索引碎片。DBCCSHOWCONTIG语句用来显示指定表的数据和索引的碎片信息。当对表进行大量的修改或添加数据之后，应该执行此语句来查看有无碎片。检查碎片其语法格式如下：DBCCSHOWCONTIG([{table_name|table_id|view_name|view_id},index_name|index_id])例：检查student表的索引idx_stu_name的碎片信息。DBCCSHOWCONTIG(student,idx_stu_name)整理碎片DBCCINDEXDEFRAG([{database_name|database_id},{table_name|table_id|view_name|view_id},index_name|index_id])【例9-7】整理student数据库中stu_info表的索引idx_name上的碎片。DBC