资源简介

(共47张PPT)
网站数据库应用技术SQL Server 2008
（项目教程）
项目八: 索引及其应用
索引的概念
创建索引
索引的删除
任务一
任务二
任务三
索引及其应用
任务四
索引的优化
8.1 任务1: 索引的概念
索引及其应用
索引是与表关联的页的集合，用于提高查询的性能或增强唯一性。如果把数据库表看做一本书，则表的索引就如同书的目录一样，通过索引可以大大提高查询的速度。常用的关系数据库如SQL Server、Sybase、Oracle、DB2等，为了提高性能，都提供了相应的索引机制。
8.1.1相关知识
索引的概念
使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。
在关系数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的SQL语句执行得更快。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。如图 8 1所示：
（1）索引的概念
相关知识
索引的概念
（1）索引的概念
图8-1 SQL-Server访问数据方式
相关知识
索引的概念
在SQL Server2008中，从物理结构上主要有两种常用索引，包括聚集索引、非聚集索引。
其中：
聚集索引：
聚集索引将数据行的键值在表内排序并存储对应的数据记录，使得数据表物理顺序与索引顺序一致，当以某字段作为关键字建立聚集索引时，表中数据以该字段作为排序根据。因此一张表只能建立一个聚集索引。如果表中没有创建其他的聚集索引，则会在表的主键上自动创建聚集索引。
（2）索引的分类
图8-2 聚集索引
相关知识
索引的概念
非聚集索引：
非聚集索引并不物理上改变表中数据行的排列，即索引中的逻辑顺序并不等同于表中数据行的物理顺序，索引仅记录指向表中数据行位置的指针。因此，在非聚集索引中，数据和索引是分开存储的，索引的叶级由索引页，而不是数据页组成。
一个表最多可以有249个非聚集索引，无论表是否使用聚集索引，都可以对表定义非聚集索引。
（2）索引的分类
图8-3 非聚集索引
相关知识
索引的概念
唯一性索引：
一个唯一索引，不允许具有索引值相同的行，从而禁止重复的索引或键值。系统在创建该索引时检查是否有重复的键值，并在每次使用 INSERT 或 UPDATE 语句添加数据时进行检查，聚集索引和非聚集索引都可以是唯一性索引。
非唯一性索引：
表中索引列的值不唯一。
（2）索引的分类
相关知识
索引的概念
索引的优点
通过创建唯一索引，可以增强数据记录的唯一性
可以大大加快数据检索的速度。
可以加速表与表之间的连接。特别是在实现数据的参照完整性方面。
使用索引可以再检索数据的过程中使用优化隐藏器，提高系统性能。
在使用ORDER BY和GROUP BY子句中进行检索数据时，可以显著减少查询中分组和排序的时间。
创建索引的注意事项
带索引的表在数据库中会占据更多的空间，对数据进行插入、更新、删除操作的命令所花费的时间会更长。
创建索引所需的工作空间约为数据库表的1.2倍。
在设计和创建索引时，应确保对性能的提高程度大于在存储空间和处理资源方面的代价。
（3）索引的优点与注意事项
8.2 任务2:创建索引
8.2.1 相关知识
创建索引
索引的创建方式有两种，使用图形工具和使用T-SQL 语句。
1.什么情况需要创建索引：
因为创建索引需要耗费一定的系统性能，所以当出现以下情况时间可以考虑创建索引。
定义有主关键字和外部关键字的列
需在指定范围中快速或频繁查询的列
需要按排序顺序快速或频繁检索的列
在集合过程中需要快速或频繁组合到一起的列
2.什么情况不需要创建索引：
在下列情况时，可以考虑不创建索引。
在查询中几乎不涉及的列
很少有唯一值的列
由text，ntext或image数据类型定义的列
只有较少行数的表
（1）创建索引的方法
相关知识
创建索引
使用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}
（2）创建索引的语法
其中：参数代表的意义如下所示
UNIQUE 该选项表示创建唯一性的索引，在索引列中不能有相同的列值存在。
CLUSTERED 该选项表示创建聚集索引
NONCLUSTERED 该选项表示创建非聚集索引，这是CREAE INDEX的默认值。
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：在“SQL Server Managerment Studio”管理工具中展开数据库对象文件夹，选择并展要创建索引的数据库如“student”。展开要创建索引的数据表，右键单击“索引”节点，在弹出的快捷菜单中，选择“新建索引”命令。
方法1：使用“SSMS”管理工具创建索引
操作图示
步骤2：在弹出的 “新建索引”对话框中，可以输入索引的名称、选择索引的类型、选择是否是唯一索引等如图 8 5所示。在此例中，我们在“班级”表中建立 “班级名称”的非聚集索引。
操作图示
8.2.2 任务实施
创建索引
步骤3：单击“添加”按钮，从“班级”表中选择“班级名称”复选框。
方法1：使用“SSMS”管理工具创建索引
操作图示
步骤4：单击“确定”按钮，返回“新建索引”窗口，然后单击“确定”就生成了“班级名称”索引。
操作图示
8.2.2 任务实施
创建索引
操作1：在数据库（Student）中对“学生”表中的“学生姓名”列，创建的索引“学生姓名”，该索引要求为非聚集索引，创建语句如下：
CREATE NONCLUSTERED INDEX 学生姓名
ON 学生表(姓名)
打开表“学生”表在索引节点可以看到新建的（非唯一、非聚集索引“学生姓名”）。
方法2：使用Transaction-SQL语句创建索引
操作图示
8.2.2 任务实施
创建索引
操作2：在数据库（Student）中对“课程”表中的课程编号,建立唯一性索引“编号”，创建语句如下：
CREATE UNIQUE INDEX 编号
ON 课程表 (课程编号)
打开表“课程”在索引节点可以看到新建的（唯一性、非聚集索引“编号”）如图8-9所示。
方法2：使用Transaction-SQL语句创建索引
操作图示
8.3 任务3:索引的删除
8.3.1 相关知识
索引的删除
删除索引也有两种方式：使用SQL Server Management Studio管理工具和使用T-SQL语句。
（2）. 删除索引的语法
使用Transact-SQL的DROP语句修改索引的基本语法如下：
DROP INDEX on < table or view name >
（1）.删除索引的方法
8.3.2 任务实施
索引的删除
步骤1：打开“SQL Server Management Studio”管理工具，选择要删除的索引。
方法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.索引优化的主要方法：
索引优化的主要方法也分为两种：使用SQL Server Management Studio管理工具和使用T-SQL语句。
2.索引优化的主要语法：
使用Transact-SQL的ALTER语句修改索引的语法结构和参数说明
如下：
重新组织基本语法：
ALTER INDEX on < table or view name > REORGANIZE
重新生成基本语法：
ALTER INDEX on < table or view name > REBUILD
2．查看索引碎片信息
索引的优化
步骤1：在SQL Server Management Studio中，选择要查看的索引单击右键，弹出索引属性菜单。如图8-11所示。
操作图示
方法1．使用“SSMS”管理工具查看索引碎片信息
步骤2：在索引属性窗口中，单击“碎片”选项，可以看到当前索引的碎片状态如图8-12所示。
操作图示
3.索引优化
索引的优化
步骤1：打开“SQL Server Management Studio”管理工具，选择要优化的索引。
方法1:：使用“SQL Server Management Studio”管理工具优化索引
步骤2：单击右键，选择“重新生成”或“重新组织”，如图8-13所示。
操作图示
3.索引优化
索引的优化
如重新组织“班级”表中的索引“班级名称”执行语法如下：
ALTERINDEX班级名称ON班级表REORGANIZE
方法2：使用T-SQL语句优化索引
项目小结
本项目主要讲解了索引的作用及分类，索引根据物理结构可以分为聚集索引和非聚集索引，根据唯一性又可以分为唯一索引和非唯一索引，接着介绍了索引的优点及创建索引时需要注意的情况，然后介绍了如何使用管理工具或T-SQL语句来创建和删除索引，还介绍了索引的优化整理，索引优化主要有两种方式重新组织和重新生成。最后介绍了如何使用管理工具和T-SQL语言来优化索引并介绍了索引优化的意义。
实 训 项 目
综合实训1：创建索引
实训项目
实训目的：
掌握通过“SQL Server Management Studio”管理工具和T-SQL语句完成索引的创建、修改和删除。
掌握使用工具检测索引碎片并才用不同方式对索引进行优化。
实训内容：
在数据库“Library”中完成下列操作：
在表（borrow）中使用“SQL Server Management Studio”管理工具针对列（borrow_Date）创建非聚集索引。
删除索引（return_Date）。
通过“SQL Server Management Studio”管理工具对索引（borrow_date）进行碎片分析并用管理工具或T-SQL语句对该索引进行优化。
实训操作步骤：
实训项目
步骤1：在表Borrow中，右键单击“索引”节点，在弹出的快捷菜单中，选择“新建索引”命令，创建索引如图8-14所示。
步骤2：删除索引的语法如下：
DROP INDEX Borrow_date ON Borrow_Info
操作图示
步骤3：优化索引的语法如下：
ALTER INDEX Borrow_date ON Borrow_Info REORGANIZE
THANKS

展开更多......

收起↑