8项目八 索引及其应用

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