SQL Server 网络数据库管理项目教程课件8项目八 索引及其应用

项目八:索引及其应用索引的概念创建索引索引的删除任务一任务二任务三索引及其应用任务四索引的优化8.1任务1:索引的概念索引及其应用索引是与表关联的页的集合，用于提高查询的性能或增强唯一性。如果把数据库表看做一本书，则表的索引就如同书的目录一样，通过索引可以大大提高查询的速度。常用的关系数据库如SQLServer、Sybase、Oracle、DB2等，为了提高性能，都提供了相应的索引机制。8.1.1相关知识索引的概念使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。在关系数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的SQL语句执行得更快。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。如图8‑1所示：（1）索引的概念相关知识索引的概念（1）索引的概念图8-1SQL-Server访问数据方式相关知识索引的概念在SQLServer2008中，从物理结构上主要有两种常用索引，包括聚集索引、非聚集索引。其中：聚集索引：聚集索引将数据行的键值在表内排序并存储对应的数据记录，使得数据表物理顺序与索引顺序一致，当以某字段作为关键字建立聚集索引时，表中数据以该字段作为排序根据。因此一张表只能建立一个聚集索引。如果表中没有创建其他的聚集索引，则会在表的主键上自动创建聚集索引。（2）索引的分类图8-2聚集索引相关知识索引的概念非聚集索引：非聚集索引并不物理上改变表中数据行的排列，即索引中的逻辑顺序并不等同于表中数据行的物理顺序，索引仅记录指向表中数据行位置的指针。因此，在非聚集索引中，数据和索引是分开存储的，索引的叶级由索引页，而不是数据页组成。一个表最多可以有249个非聚集索引，无论表是否使用聚集索引，都可以对表定义非聚集索引。（2）索引的分类图8-3非聚集索引相关知识索引的概念唯一性索引：一个唯一索引，不允许具有索引值相同的行，从而禁止重复的索引或键值。系统在创建该索引时检查是否有重复的键值，并在每次使用INSERT或UPDATE语句添加数据时进行检查，聚集索引和非聚集索引都可以是唯一性索引。非唯一性索引：表中索引列的值不唯一。（2）索引的分类相关知识索引的概念索引的优点通过创建唯一索引，可以增强数据记录的唯一性可以大大加快数据检索的速度。可以加速表与表之间的连接。特别是在实现数据的参照完整性方面。使用索引可以再检索数据的过程中使用优化隐藏器，提高系统性能。在使用ORDERBY和GROUPBY子句中进行检索数据时，可以显著减少查询中分组和排序的时间。创建索引的注意事项带索引的表在数据库中会占据更多的空间，对数据进行插入、更新、删除操作的命令所花费的时间会更长。创建索引所需的工作空间约为数据库表的1.2倍。在设计和创建索引时，应确保对性能的提高程度大于在存储空间和处理资源方面的代价。（3）索引的优点与注意事项8.2任务2:创建索引8.2.1相关知识创建索引索引的创建方式有两种，使用图形工具和使用T-SQL语句。1.什么情况需要创建索引：因为创建索引需要耗费一定的系统性能，所以当出现以下情况时间可以考虑创建索引。定义有主关键字和外部关键字的列需在指定范围中快速或频繁查询的列需要按排序顺序快速或频繁检索的列在集合过程中需要快速或频繁组合到一起的列2.什么情况不需要创建索引：在下列情况时，可以考虑不创建索引。在查询中几乎不涉及的列很少有唯一值的列由text，ntext或image数据类型定义的列只有较少行数的表（1）创建索引的方法相关知识创建索引使用Transact-SQL的CREATINDEX语句创建索引的语法结构和参数说明如下：基本语法：CREATE[UNIQUE][CLUSTERED][NONCLUSTERED]INDEXindex_nameONtable_or_view_name(colum[ASC|DESC][,…n])[INCLUDE(colum_name[,…n])][WITH(PAD_INDEX={ON|OFF}FILLFACTOR=fillfactorSORT_IN_TEMPDB={ON|OFF}IGNORE_DUP_KEY={ON|OFF}STATISTICS_NORECOMPUTE={ON|OFF}DROP_EXISTING={ON|OFF}ONLINE={ON|OFF}ALLOW_ROW_LOCKS={ON|OFF}ALLOW_PAGE_LOCKS={ON|OFF}MAXDOP=max_degree_of_parallelism)[,…n]ON{_schema_name(column_name)|filegroup_name|default}（2）创建索引的语法其中：参数代表的意义如下所示UNIQUE 该选项表示创建唯一性的索引，在索引列中不能有相同的列值存在。CLUSTERED 该选项表示创建聚集索引NONCLUSTERED 该选项表示创建非聚集索引，这是CREAEINDEX的默认值。INCLUDE 该选项用于指导将要包含到非聚集索引的页级中的非键列。PAD_INDEX 该选项用于指定索引的中间页级，也就是说为非叶级索引指定填充度。这时的填充度由FILLFACT选项指定。FIllFACTOR 该选项用于指定非页级索引页的填充度。SORT_INT_TEMPDB 该选项为ON时，用于指定创建索引时产生的中间结果，在tempdb数据库中进行排序。为OFF时，在当前数据库中排序。IGNORE_DUP_KEY 该选项用于指定唯一性索引键冗余数据的系统行为。当为ON时，系统发出警告信息，违反唯一性行的数据插入失败。为OFF时，取消整个INSERT语句，并且发出错误信息。STATISTICS_NORECOMPUTE 该选项用于是否重新计算索引统计信息。为ON时，不自动计算过期的索引统计信息。为OFF时，启动自动计算能力。DROP_EXIXTING 该选项用于是否可以删除指定的索引，并且重建已有的索引。为OFF时，不能删除重建。ONLINE 该选项用于指定索引操作期间基础表和关联索引是否可用于查询。为ON为，不持有表锁，允许用于查询。为OFF时，持有表锁，索引操作期间不能指定查询。ALLOW_ROW_LOCKS: 该选项用于指定是否使用行锁，为ON，表示使用行锁。ALLOW_PAGE_LOCKS 该选项用于指定是否使用页锁，为ON，表示使用页锁。MAXDOP 该选项用于指定索引操作期间覆盖最大并行度的配置选项。主要目的是限制执行并行计划过程中使用的处理器数量8.2.2任务实施创建索引步骤1：在“SQLServerManagermentStudio”管理工具中展开数据库对象文件夹，选择并展要创建索引的数据库如“student”。展开要创建索引的数据表，右键单击“索引”节点，在弹出的快捷菜单中，选择“新建索引”命令。方法1：使用“SSMS”管理工具创建索引操作图示步骤2：在弹出的“新建索引”对话框中，可以输入索引的名称、选择索引的类型、选择是否是唯一索引等如图8‑5所示。在此例中，我们在“班级”表中建立“班级名称”的非聚集索引。操作图示8.2.2任务实施创建索引步骤3：单击“添加”按钮，从“班级”表中选择“班级名称”复选框。方法1：使用“SSMS”管理工具创建索引操作图示步骤4：单击“确定”按钮，返回“新建索引”窗口，然后单击“确定”就生成了“班级名称”索引。操作图示8.2.2任务实施创建索引操作1：在数据库（Student）中对“学生”表中的“学生姓名”列，创建的索引“学生姓名”，该索引要求为非聚集索引，创建语句如下：CREATENONCLUSTEREDINDEX学生姓名ON学生表(姓名)打开表“学生”表在索引节点可以看到新建的（非唯一、非聚集索引“学生姓名”）。方法2：使用Transaction-SQL语句创建索引操作图示8.2.2任务实施创建索引操作2：在数据库（Student）中对“课程”表中的课程编号,建立唯一性索引“编号”，创建语句如下：CREATEUNIQUEINDEX编号ON课程表(课程编号)打开表“课程”在索引节点可以看到新建的（唯一性、非聚集索引“编号”）如图8-9所示。方法2：使用Transaction-SQL语句创建索引操作图示8.3任务3:索引的删除8.3.1相关知识索引的删除删除索引也有两种方式：使用SQLServerManagementStudio管理工具和使用T-SQL语句。（2）.删除索引的语法使用Transact-SQL的DROP语句修改索引的基本语法如下：DROPINDEX<indexname>on<tableorviewname>（1）.删除索引的方法8.3.2任务实施索引的删除步骤1：打开“SQLServerManagementStudio”管理工具，选择要删除的索引。方法1.使用“SSMS”管理工具删除索引步骤2：单击右键，选择“删除”，如图8-10所示。操作图示8.2.2任务实施索引的删除操作1：在数据库“Student”中的“学生”表中删除索引“学生姓名”，执行语句如下：DROPINDEX学生姓名ON学生表方法2：使用T-SQL语言删除索引8.4任务4:索引的优化8.4.1相关知识索引的优化数据库的总体性能很大程度上取决于索引的效率，因此，有必要确保以最有利于应用程序的方式设计并实现索引。实现索引后，必须维护索引，以确保其一致保持最佳性能。随着数据库中的数据增加、更改和删除，索引将变得分碎。根据业务环境及数据库应用程序的用途，碎片可能有利于性能，也可能有碍于性能，但是无论如何都需要对索引进行优化，并将索引碎片控制在适当的程度。索引的优化主要手段就是索引碎片整理。8.4.1相关知识索引的优化1.索引碎片的优化整理方式索引碎片的优化整理主要有两种方式：重新组织和重新生成，通常情况下，如果碎片度低于30%，那么应该重新组织索引，如果碎片度高于30%应该重新生成索引。重新组织和重新生成的区别如表8-2所示。操作图示重新组织重新生成重新组织索引是在物理上对叶级页重新排序，使其与叶节点的逻辑顺序（从左到右）相符，从而对表的聚集索引和非聚集索引的叶级进行碎片整理重新生成索引将删除原索引，并重新生成一个新索引，重新生成将删除碎片，回收并重新分配磁盘空间，从而提高磁盘性能。表8‑2重新组织和重新生成区别表索引的优化1.索引优化的主要方法：索引优化的主要方法也分为两种：使用SQLServerManagementStudio管理工具和使用T-SQL语句。2.索引优化的主要语法：使用Transact-SQL的ALTER语句修改索引的语法结构和参数说明如下：重新组织基本语法：ALTERINDEX<indexname>on<tableorviewname>REORGANIZE重新生成基本语法：ALTERINDEX<indexname>on<tableorviewname>REBUILD2．查看索引碎片信息索引的优化步骤1：在SQLServerManagementStudio中，选择要查看的索引单击右键，弹出索引属性菜单。如图8-11所示。操作图示方法1．使用“SSMS”管理工具查看索引碎片信息步骤2：在索引属性窗口中，单击“碎片”选项，可以看到当前索引的碎片状态如图8-12所示。操作图示3.索引优化索引的优化步骤1：打开“SQLServerManagementStudio”管理工具，选择要优化的索引。方法1:：使用“SQLServerManagementStudio”管理工具优化索引步骤2：单击右键，选择“重新生成”或“重新组织”，如图8-13所示。操作图示3.索引优化索引的优化如重新组织“班级”表中的索引“班级名称”执行语法如下：ALTERINDEX班级名称ON班级表REORGANIZE方法2：使用T-SQL语句优化索引项目小结本项目主要讲解了索引的作用及分类，索引根据物理结构可以分为聚集索引和非聚集索引，根据唯一性又可以分为唯一索引和非唯一索引，接着介绍了索引的优点及创建索引时需要注意的情况，然后介绍了如何使用管理工具或T-SQL