任务 5 索引及其应用

任务5 索引及其应用 • 通过本章的学习，应该掌握以下内容： • 索引的概念、创建和操作 • 索引的作用 • 规划和维护索引 • 使用索引来提高检索的效率

任务5 索引及其应用 • 5.1 索引概述 • 5.2 创建索引 • 5.3 管理和维护索引

任务5 索引及其应用 用户对数据库最频繁的操作是进行数据查询，一般情况下数据库在进行查询操作时需要对整个表进行数据搜索，当表中的数据很多时搜索数据就需要很长的时间，这就造成了服务器的资源浪费。为了加快查询速度，数据库引入了索引机制。

5.1 索引概述 • 索引是一个在表上或视图上创建的独立的物理数据库结构，在视图上创建索引只能针对架构绑定的视图。所以我们所讲的主要是针对表上的索引。

5.1 索引概述 索引相当于基本表的目录，如同有一本书，它前面设有目录，目录包括摘要内容和对应页码，索引则相应地含有索引项和元组地址。

基本表（无序） 基于学号的索引索引项元组地址索引包括两项内容：无序表和有序表的查询时间区别很大。

在没有索引的表上进行数据查询，显然，是对无序表的查询，需要花费相当多的时间，从第一行记录开始一行一行地进行查询，这种方法显然效率很低。那么，如果是对于一个有序表，在查询它的某一行记录时，就可采用查询速度快的折半查找法、B—树、B+树、平衡二叉树等方法。这样，对于数据含量非常大的表，就必须提取一列或若干列，建立有序索引，才能大大加快数据的查询速度。

索引是怎样提高查询速度的 由于基本表文件中列比较多，元组也比较多（大型数据库中元组可达数万个）。所以数据文件很大，进行数据查询时，如果不使用索引，则需要将数据文件分块，逐个读入内存，然后一条记录一条记录地进行查找比较，而在表的某列建立索引后，索引文件包括了所有该列的值的集合，并且按顺序排列，比如学号索引，索引文件中包含了学号的顺序序列，以及每个学号对应的记录的地址。查找时可以先将索引文件（相对很小，一般一次可以读入内存）读入内存，然后进行数据项的比较，由于索引文件中的索引项是经过排序的，我们可以采用较优的查找算法，比如折半查找、B树、B+树等，很快地找到记录值。而不用索引的基本表只能采用效率很低的顺序查询，通常需要花费很长时间。

如果要查询数据，比如： Select * from 客户信息 where 姓名=‘张三’ 如果订单信息的姓名列没有索引，那么SQL SERVER就会按照表中行的存储顺序一行一行地进行查询，查看每一行”姓名“是否符合查询条件，这样，对于一个具有成千上万行的大型表来说，这种全表搜索可能花费数小时时间。客户信息

如果在姓名字段建立索引，那么SQL SERVER就可以先搜索这个索引，在索引中找到符合查询条件的值，然后按照该值后的元组地址指针，轻松找到对应的元组记录，由于对有序的索引进行搜索速度会很快，那么相应查询速度也很快。

5.1.1 SQL Server 中数据的存储与访问 • 1．数据的存储 • 在SQL Server中，数据存储的基本单位是页。SQL Server 2000中，页的大小是8KB。每页的开始部分是96个字节的页首，用于存储系统信息，如页的类型、页的可用容量、拥有页的对象ID等。

2．数据的访问 • SQL Server 提供了两种数据访问的方法。 • （1）表扫描法 • 在没有建立索引的表内进行数据访问时，SQL Server通过表扫描法来获取所需要的数据。当SQL Server执行表扫描时，它从表的第一行开始进行逐行查找，直到找到符合查询条件的行。

（2）索引法 • 在建有索引的表内进行数据访问时，SQL Server通过使用索引来获取所需要的数据。当SQL Server使用索引时，它会通过遍历索引树来查找所需行的存储位置，并通过查找的结果提取所需的行。

（2）索引法 • SQL SERVER使用索引是查询时由系统自动执行的，不需要用户的干预，也不需要用户指定怎样使用索引和使用哪些索引。索引可以由系统自动创建也可以由用户手工创建，但是，具体索引是怎么工作的，不需要用户的干预。

5.1.2 索引的作用 • 创建索引的好处主要有以下两点： • （1）加快数据查询 • 在表中创建索引后，进行以索引为条件的查询时，由于索引是有序的，可以采用较优的算法来进行查找，这样就提高了查询速度。经常用作查询条件的列应当建立索引，而不经常作为查询条件的列则可不建立索引。在SQL Server中采用B_tree方法来建立索引页， B_tree 平衡树方法结构非常适合于查找数据，因而在SQL SERVER中采用该结构来建立索引页和数据页。聚集索引，非聚集索引都是通过B_tree来建立的。

5.1.2 索引的作用 • 创建索引的好处主要有以下两点： • （2）加快表的连接、排序和分组工作 • 这些操作要涉及到表的查询工作，而建立索引会提高表的查询速度，从而也加快了这些操作的速度。

创建索引也有它的不足，如下所述： • （1）创建索引需要占用数据空间和时间创建索引时所需的工作空间大概是数据表空间的1.2倍，还要占用一定的时间。 • （2）建立索引会减慢数据修改的速度 • 在有索引的数据表中，进行数据修改时，包括记录的插入、删除和修改，都要对索引进行更新，修改的数据越多，索引的维护开销就越大，所以索引的存在减慢了数据修改速度。

因此，没必要对所有的列都建立索引。下面的情况不考虑建立索引：因此，没必要对所有的列都建立索引。下面的情况不考虑建立索引： • 从来不或很少在查询中引用的列。 • 只有若干个值的列。 • 记录数目很少的列。

5.1.3 索引的分类 • 按照索引值的特点分类，可以将索引分为惟一索引和非惟一索引；按照索引结构的特点分类，可以将索引分为聚集索引和非聚集索引。

1．惟一索引和非惟一索引 • 惟一索引要求所有数据行中任意两行中的被索引列或索引列组合不能存在重复值，包括不能有两个空值NULL，而非惟一索引则不存在这样的限制。 • 也就是说，对于表中的任何两行记录来说，索引键的值都是不同的，若表中有多行的记录在某字段上具有相同的值，则不能在该字段上建立唯一索引。

2．聚集索引和非聚集索引 • 根据索引的顺序与数据表的物理顺序是否相同，可以把索引分为聚集索引和非聚集索引。聚集索引会对磁盘上的数据进行物理排序，所以这种索引对查询非常有效。表中只能有一个聚集索引。当建立主键约束时，如果表中没有聚集索引，SQL Server会用主键列作为聚集索引键。聚集索引：聚集索引将数据行的键值在表内排序并存储对应的数据记录，使数据表的物理顺序与索引顺序相同。非聚集索引不会对表进行物理排序。

5.1.3 索引的分类 • 在创建了聚集索引的表上执行查询操作速度很快，但是，执行修改的操作时，却比只创建了非聚集索引的表要慢，因为表数据的插入、删除等操作需要有更长时间来维护聚集索引。 • 单列索引和复合索引 • 单列索引是对表中单个字段建立的索引，复合索引是对表中的两个或两个以上的字段的组合建立的索引。

5.2 创建索引 • 索引可以在创建表的约束时由系统自动创建，也可以通过企业管理器或CREATE INDEX语句来创建。

5.2 创建索引 • 创建索引需要注意以下事项： • 1.每张表只能有一个聚集索引。 • 2.创建聚集索引时所需要的可用空间是表数据量的120%。所以要求数据库应有足够的空间。 • 3.主键一般都建有聚集索引。 • 4.唯一键（UNIQUE）将建为非聚集索引。

5.2.1 系统自动创建索引 • 在创建或修改表时，如果添加了一个主键或惟一键约束，则系统将自动在该表上，以该键值作为索引列，创建一个惟一索引。 • 一个表上至多有一个聚集索引，和249个非聚集索引。

演示系统自动创建索引： Create table product (产品号 int , 产品名称 char(20) unique clustered) 使用UNIQUE ，系统自动创建非聚集索引。添加主键，系统自动创建聚集索引。索引项可以是主键列，也可以是设定UNIQUE的列。系统在创过了主键列或UNIQUE列，系统自动在该列上建立索引。一个基本表可以根据需要建立多个索引，以加快数据查询速度。

在SQL Server 2000中，只有表或视图的拥有者才可以为表创建索引，即使表中没有数据也可以创建索引。除了系统自动创建索引，我们也可以手工地创建索引。创建索引有3种的方法： ·使用企业管理器创建索引 ·使用向导创建索引 ·使用CREATE INDEX语句创建索引此外，当对表中的字段设置主键约束时，如果表中没有聚集索引，且主键约束未使用关键字NONCLUSTERED系统会自动创建一个唯一性索引。

例7-2：在查询分析器下，使用存储过程sp_helpindex，察看表“客户信息”的索引情况。例7-2：在查询分析器下，使用存储过程sp_helpindex，察看表“客户信息”的索引情况。 Exec sp_helpindex 客户信息可以看到该表只有一个索引并且是聚集索引，索引名为主键编号。例7-3：在查询分析器下，为“客户信息”的“电话”字段添加唯一键约束，然后，使用存储过程sp_helpindex，查看表“客户信息”的索引情况。 Alter table 客户信息 add unique(电话) 可以看到，该表有两个索引，主键是聚集索引，新添加的唯一键为非聚集索引，索引列为唯一键列“电话”。

例7-4：在查询分析器下，为“客户信息”的电话字段添加唯一键约束建立聚集索引的出错演示。例7-4：在查询分析器下，为“客户信息”的电话字段添加唯一键约束建立聚集索引的出错演示。 Alter table 客户信息 add unique clustered(电话)

5.2.2 在图形界面下创建索引 • 在企业管理器的表设计器下建立和修改索引很便捷，这里通过实例说明其使用方法。

5.2.2 在图形界面下创建索引 • 例7-5：在“客户信息”表上，由于需要经常按“姓名”进行查找，所以考虑基于“姓名”字段建立索引。因为存在同名的可能，所以建立非唯一索引。又因为一般都是以主键列作为聚集索引键，所以建立非唯一的非聚集索引，将该索引命名为IX—客户信息—姓名。

5.2.2 在图形界面下创建索引 • 例7-6：通过企业管理器下的“管理索引”对话框，管理“客户信息”上的索引，查看索引定义的语句。

5.2.3 使用CREATE INDEX语句创建索引 • 创建索引命令常用格式如下: • CREATE[UNIQUE][CLUSTERED|NCLUSTERED] INDEX索引名ON 表名 (字段名[,…n]) • [WITH [索引选项 [,…n] ] • [ON 文件组]

例7-7：在查询分析器中，使用CREATE INDEX 语句，在表“订单信息”上创建名为“IX_订单信息_客户货品”的非聚集、复合索引，该索引基于“客户编号”列和“货品编码”列。 Create nonclustered unique index ix_订单信息_客户货品 on 订单信息(客户编号,货品编码) exec sp_helpindex 订单信息 Create index IX_客户信息_电话 on 客户信息(电话) 在建有聚集唯一索引的表上，执行INSERT语句或UPDATE语句时，SQL Server自动检查新的数据中是否有重复值，如果有，警告。如果表中已有数据，那么在创建唯一索引时，SQL Server将自动检查是否存在重复值，如果存在，就不能创建唯一索引。

5.3 管理和维护索引 • 5.3.1 查看和修改索引信息 • 可以使用企业管理器查看、修改索引的定义，或者使用sp_helpindex系统存储过程或有关表上的索引信息。 • 创建和修改聚集索引时，SQL Server 要在磁盘上对表进行重组，当表中存储了大量记录时，会产生很大的系统开销，花的时间可能很长。

5.3.2 删除索引 • 1．使用企业管理器删除索引 • 2．使用Transact-SQL语句删除索引 • 删除索引命令常用格式如下: • DROP INDEX 表名.索引名[,…n]

5.3.2 删除索引 • 例7-9：使用DROP INDEX语句删除例7-7建立的索引。 • Drop index 订单信息.ix_订单信息_客户货品

那么，用DROP INDEX 是否可以删除所有的索引呢？答案是否定的，它不能删除由PRIMARY KEY 约束或UNIQUE约束创建的索引，这些索引必须通过删除PRIMARY KEY约束或UNIQUE约束，由系统自动删除。例7-10：尝试使用DROP INDEX 表名.索引名命令语句删除客户信息表中的主键索引PK_客户信息。

5.3.3 索引的分析与维护 SQL Server 内部存在一个查询优化器，如何进行查询是由它决定的，包括如何使用索引，无需程序员或数据库管理员的干预，查询优化器总能针对数据库的状态为每个查询生成一个最优的查询计划。

5.3.3 索引的分析与维护 • 1．索引的分析 • （1）显示查询计划 • SQL Server提供了两种显示查询中的数据处理步骤以及如何访问数据的方式。 • ① 以图形方式显示执行计划

图7-13 客户订单查询的执行计划

图7-14 扫描订单信息表聚集索引的说明

② 以表格方式显示执行计划 • 通过在查询语句中设置SHOWPLAN选项，我们可以选择是否让SQL Server显示查询计划。 • 设置是否显示查询计划的命令为： • SET SHOWPLAN_ALL ON|OFF • 或 • SET SHOWPLAN_TEXT ON|OFF

例7-12：将例7-11的执行计划以表格的方式显示。例7-12：将例7-11的执行计划以表格的方式显示。 Set showplan_text on Go Select * from 客户信息 A inner join 订单信息 B On A.编号=B.客户编号 go

2．索引的维护 • 在创建索引后，为了得到最佳的性能，必须对索引进行维护。因为随着时间的推移，用户需要在数据库上进行插入、更新和删除等一系列操作，这将使数据变得支离破碎，从而造成索引性能的下降。

（1）统计信息更新 • 在企业管理器中通过设置数据库的属性决定是否实现统计的自动更新。 • 使用UPDATE STATISTICS命令更新“客户信息”表主键索引的统计信息。

例7-13：给出执行客户订单信息的查询的I/O统计。例7-13：给出执行客户订单信息的查询的I/O统计。 SET STATISTICS IO ON GO SELECT * FROM 客户信息 A INNER JOIN 订单信息 B ON A.编号=B.客户编号 GO SET STATISTICS IO OFF

图7-17 设置数据库的属性决定是否实现统计的自动更新

（2）使用DBCC SHOWCONTIG语句扫描表 • 对表进行数据操作可能会导致表碎片，而表碎片会导致额外的页读取，从而造成数据库查询性能的降低。此时用户可以通过使用DBCC SHOWCONTIG语句来扫描表，并通过其返回值确定该表的索引页是否已经严重不连续。

（3）使用DBCC INDEXDEFRAG语句进行碎片整理 • 例7-16：利用DBCC SHOWCONTIG 获取“客户信息”表中主键索引的碎片信息。 • DBCC showcontig(客户信息,pk_客户信息) • 在返回的统计信息中，需要注意到的是扫描密度，其理想数是100%，如果百分比较低，就需要整理表上的碎片了。

任务 5 索 引 及 其 应 用