第七章 －－ 数据的 索引及其应用
7.1 索引的基础知识
7.2 索引的分类
7.3 索引的操作
7.6 案例中的索引
7.4 设置索引的选项
7.5 索引的分析与维护目录索引是一种特殊类型的数据库对象,它保存着数据表中一列或几列组合的排序结构 。 为数据表增加索引,可以大大提高数据的检索效率 。
索引是数据库中一个重要的对象,本章将详细介绍索引的基本概念,使用索引的意义,创建索引的方法以及对索引的操作 。
7.1 索引的基础知识
7.1.1 数据存储在 SQL Server 2000中，数据存储的基本单位是页，其大小是 8KB。每页的开始部分是 96 B的页首，用于存储系统信息，如页的类型、页的可用空间量、拥有页的对象 ID等。
7.1.2索引
1.索引的概念索引是以表列为基础建立的数据库对象,它保存着表中排序的索引列,并且记录了索引列在数据表中的物理存储位置,实现了表中数据的逻辑排序 。 SQL
Server 2000将索引组织为 B树,索引内的每一页包含一个页首,页首后面跟着索引行 。 每个索引行都包含一个键值以及一个指向较低级页或数据行的指针 。
索引的每个页称为索引节点 。 B树的顶端节点称为根节点,索引的底层节点称为叶节点,根和叶之间的任何索引级统称为中间级 。
2.使用索引的意义索引在数据库中的作用与目录在书籍中的作用类似,都用来提高查找信息的速度,查找内容时,先在目录中找到相关的页码,然后按照页码找到内容 。 两者相比,利用目录查找内容要比一页一页地查找速度快很多 。 在数据库中查找数据,也存在两种方法：一种是全表扫描,
与一页一页地翻书查找信息类似,用这种方法查找数据要从表的第一行开始逐行扫描查找,
直到找到所需信息；另一种是使用索引,索引是一个表中所包含值的列表,其中注明了表中包含各个值的行所在的存储位置,使用索引查找数据时,先从索引对象中获得
3.使用索引的代价为每一列都建立索引是不明智的,因为使用索引要付出一定的代价：
● 索引需要占用数据表以外的物理存储空间 。 例如,要建立一个聚集索引,需要大约１,２倍于数据大小的空间 。
● 创建索引和维护索引要花费一定的时间 。
● 当对表进行更新操作时,索引需要被重建,这样降低了数据的维护速度 。
4.建立索引的原则一般原则是：
● 主键列上一定要建立索引 。
● 外键列可以建索引 。
● 在经常查询的字段上最好建立索引 。
● 对于那些查询中很少涉及的列,重复值比较多的列不要建索引 。
对于定义为 text,Image和 Bit数据类型的列上不要建立索引。
7.2 索引的分类在 SQL Server数据库中,根据索引的存储结构不同将其分为两类：聚集索引和非聚集索引 。
7.2.1 聚集索引聚集索引是指表中数据行的物理存储顺序与索引顺序完全相同 。 每个表只能创建一个聚集索引 。
图 7.1 聚集索引的结构示意图
7.2.2 非聚集索引非聚集索引与聚集索引一样有 B 树结构,但是有两个重大差别：
● 数据行不按非聚集索引键的顺序排序和存储 。
● 非聚集索引的叶层不包含数据页 。
非聚集索引可以在有聚集索引的表,堆集或索引视图上定义 。 非聚集索引中的行定位器有两种形式：
● 如果表是堆集 （ 没有聚集索引 ）,行定位器就是指向行的指针 。 该指针用文件标识符
(ID),页码和页上的行数生成 。 整个指针称为行 ID。
● 如果表没有聚集索引,或者索引在索引视图上,则行定位器就是行的聚集索引键 。 如果聚集索引不是唯一的索引,SQL Server将添加在内部生成的值以使重复的键唯一 。 用户看不到这个值,它用于使非聚集索引内的键唯一,
图 7.2 非聚集索引的结构示意图
7.2.3 聚集和非聚集索引的性能比较当进行单行查找时,聚集索引的输入输出速度比非聚集索引快,因为聚集索引的索引级别较小 。 聚集索引非常适合于范围查询,因为服务器可以缩小数据范围,先得到第一行,再进行扫描,无需再次使用索引；非聚集索引速度稍慢,占用空间特别大,但也是一种较好的表扫描方法 。 非聚集索引可能覆盖了查询的全部过程 。 也就是说,假如所需数据在索引中,服务器就不必再返回到数据行中 。
7.3 索引 的操作
7.3.1 创建索引
SQL Server 2000可以自动创建唯一索引,以强制实施 PRIMARY KEY和 UNIQUE约束的唯一性要求 。 如果需要创建不依赖于约束的索引,
可以使用企业管理器创建索引,也可 以使用创建索引向导创建索引,还可以在查询分析器中用 SQL语句创建索引 。
创建索引时要注意：
● 只有表或视图的所有者才能创建索引,并且可以随时创建 。
● 对表中已依次排列的列集合只能定义一个索引 。
● 在创建聚集索引时,将会对表进行复制,
对表中的数据进行排序,然后删除原始的表 。 因此,数据库上必须有足够的空闲空间,以容纳数据副本 。
● 在使用 CREATE INDEX语句创建索引时,
必须指定索引,表以及索引所应用的列的名称 。
● 在一个表中最多可以创建 249个非聚集索引。默认情况下，创建的索引是非聚集索引。
●复合索引的列的最大数目为 16，各列组合的最大长度为 900B。
●要特别注意 WHERE子句中数据类型不匹配的问题，特别是 char和 varchar类型。它们不会被很好地优化，因为优化程序不能对索引使用数据分配统计。
在存储过程中很容易造成类型不匹配，
使用用户定义的数据类型有助于避免这个问题。
下面，我们分别介绍三种方式创建索引。
1.使用企业管理器创建索引
2.使用创建索引向导
3.使用 CREATE INDEX语句创建索引
7.3.2 查询索引信息
1.使用企业管理器查看索引信息
2.使用系统存储过程查看索引信息在查询分析器中执行系统存储过程
sp_helpindex或 sp_help查看数据表的索引信息,sp_helpindex只显示表的索引信息,sp_help除了显示索引信息外,还有表的定义,约束等其他信息 。
两者的语法格式基本相同，
我们以 sp_helpindesx为例介绍。其语法格式如下：
[EXEC] sp_helpindex
[ @objname = ] ‘name’
其中：
[@objname =] ‘name’是当前数据库中表或视图的名称。
7.3.3 索引更名系统存储过程 sp_rename可以用来更改索引的名称,其语法格式如下：
sp_rename[@objname=]’object_name
’，
[@newname,]’new_name’
[,[@objtype,]’object_type’]
其中：
● object_name是需要更改的对象原名。如果要重命名的对象是表中的一列，那么 object_name 必须为
table.column 形式。如果要重命名的是索引，那么 object_name 必须为 table.index 形式。
● new_name是对象更改后的名称。
new_name 必须是名称的一部分，并且要遵循标识符的规则。
● object_type是对象类型。
7.3.4 删除索引
1.使用企业管理器删除索引
2.使用 DROPINDEX语句删除索引使用 DROPINDEX语句可以删除表中的索引,其语法格式如下：
DROP INDEX表名,索引名 [,…n]
7.4.1 设置 FILLFACTOR选项当向一个已满的索引页添加新行时,SQL
Server要将该页进行拆分,将大约一半的行移到新页中,以便为新的记录行腾出空间,
这需要很大的开销 。 为了尽量减少页拆分,
在创建索引时,可以选择 FILLFACTOR(称为填充因子 )选项,此选项用来指定各索引页叶级的填满程度,这样在索引页上就可以留出额外的间隙和保留一定百分比的空间,供将来表的数据存储容量进行扩充和减少页拆分 。
7.4 设置索引的选项设置 FILLFACTOR值时，应考虑如下因素：
．填充因子的值是从 0到 100之间的百分比数值，用来指定在创建索引后对数据页的 填充比例。
．值为 100时表示页将填满，所留出的存储空间量最小。只有当不会对数据进行更改 时才会使用此设置。
．值越小则数据页上的空闲空间越大，
这样可以减少在索引增长过程中对数据页进行拆分的需要，但需要更多的存储空间。当表中数据会发生更改时，这种设置更为适当。
．使用 sp_configure系统存储过程可以在服务器级别设置默认的填充因子。
．填充因子只在创建索引时执行。
索引创建后，当表中进行数据的添加、
删除或更新时，不会保持填充因子。
7.4.2 设置 PAD_INDEX选项
FILLFACTOR选项用来指定各索引页叶级的填满程度,对于非叶级索引页需要使用
PAD_INDEX选项设置其预留空间的大小 。
PAD_INDEX选项只有在指定了 FILLFACTOR时才有用,因为 PAD_INDEX使用由 FILLFACTOR所指定的百分比 。 默认情况下,给定中间级页上的键集,SQL Server将确保每个索引页上的可用空间至少可以容纳一个索引允许的最大行 。 如果为 FILLFACTOR指定的百分比不够大,
无法容纳一行,SQL Server将在内部使用允许的最小值替代该百分比 。
7.5 索引的分析与维护索引创建之后,由于数据的增加,删除和修改等操作会使索引页发生碎块,因此必须对索引进行分析和维护 。
7.5.1 索引的分析常用的有 SHOWPLAN和 STATISTICS IO语句 。
1,SHOWPLAN
SHOWPLAN语句用来显示查询语句的执行信息,包含查询过程中连接表时所采取的每个步骤以及选择哪个索引 。 其语法格式为：
SETSHOWPLAN_ALL{ON|OFF} 和
SETSHOWPLAN_TEXT{ON|OFF}
其中,ON为显示查询执行信息,OFF为不显示查询执行信息 (系统默认 )。
7.5.2 索引的维护
1.使用 DBCCSHOWCONTIG语句该语句用来显示指定表的数据和索引的碎片信息 。 当对表进行大量的修改或添加数据之后,
应该执行此语句来查看有无碎片 。 其语法格式如下：
DBCCSHOWCONTIG[{table_name|
table_id|view_name|view，
index_name|index_id])]
其中：
● table_name|table_id|view_
name|view id是要对其碎片信息进行检查的表或视图。如果未指定任何名称，则对当前数据库中的所有表和索引视图进行检查。
2.使用 DBCC INDEXDEFRAG语句该语句的作用是整理指定的表或视图的聚集索引和辅助索引的碎片,其语法格式为：
DBCC INDEXDEFRAG
({daabase_name|database_id|0}
，
{table_name|table_id|’view_name’|view_
id}
,{index_name|index_id}
) [ WITH NO_INFOMSGS ]
其中：
● database_name,database_id|0指对其索引进行碎片整理的数据库。数据库名称必须符合标识符的规则。如果指定 0，则使用当前数据库。
● table_name|table_id|’view_name’|view_
id指对其索引进行碎片整理的表或视图。
● index_name|index_id是需要进行碎片整理的索引名称。
● WITH NO_INFOMSGS禁止显示所有信息性消息（具有从 0 到 10的严重级别）。
7.6 案例中的索引
1.创建一个复合索引为了方便按系部和专业查找指定的学生,
为学生表创建一个基于,系部代码,专业代码,组合列的非聚集,复合索引
xb_zy_index。 其程序清单如下：
USE Student
GO
CREATE INDEX xb_zy_index ON 学生
(系部代码,专业代码 )
GO
2.创建一个聚集,复合索引为,教师任课,表创建一个基于,教师编号,课程号,组合列的聚集,复合索引
jskc_index。 其程序清单如下：
USE Student
GO
CREATE CLUSTERED INDEX
jskc_index
ON 教师任课 (教师编号,课程号 )
GO
3.创建一个唯一、聚集、复合索引为教学计划表创建一个基于,课程号,专业代码,
组合列的唯一,聚集,复合索引 jxkc_zy_ndex。 其程序清单如下：
USE Student
GO
CREATE UNIQUE CLUSTERED
INDEX jxkc_zy_index ON 教学计划
(课程号,专业代码 )
WITH
PAD_INDEX,FILLFACTOR=70,
IGNORE_DUP_KEY
GO
练 习 题
1,什么是索引?使用索引有什么意义?
2,聚集索引和非聚集索引有何区别?
3,创建索引时要考虑哪些事项?
4,修改索引可以便用 ALTER INDEX命令吗,如果不能,说明修改索引的方法 。
5,如何查看表中的碎片信息？ 如何清除索引碎片?
谢谢各位！
返回主页
7.1 索引的基础知识
7.2 索引的分类
7.3 索引的操作
7.6 案例中的索引
7.4 设置索引的选项
7.5 索引的分析与维护目录索引是一种特殊类型的数据库对象,它保存着数据表中一列或几列组合的排序结构 。 为数据表增加索引,可以大大提高数据的检索效率 。
索引是数据库中一个重要的对象,本章将详细介绍索引的基本概念,使用索引的意义,创建索引的方法以及对索引的操作 。
7.1 索引的基础知识
7.1.1 数据存储在 SQL Server 2000中，数据存储的基本单位是页，其大小是 8KB。每页的开始部分是 96 B的页首，用于存储系统信息，如页的类型、页的可用空间量、拥有页的对象 ID等。
7.1.2索引
1.索引的概念索引是以表列为基础建立的数据库对象,它保存着表中排序的索引列,并且记录了索引列在数据表中的物理存储位置,实现了表中数据的逻辑排序 。 SQL
Server 2000将索引组织为 B树,索引内的每一页包含一个页首,页首后面跟着索引行 。 每个索引行都包含一个键值以及一个指向较低级页或数据行的指针 。
索引的每个页称为索引节点 。 B树的顶端节点称为根节点,索引的底层节点称为叶节点,根和叶之间的任何索引级统称为中间级 。
2.使用索引的意义索引在数据库中的作用与目录在书籍中的作用类似,都用来提高查找信息的速度,查找内容时,先在目录中找到相关的页码,然后按照页码找到内容 。 两者相比,利用目录查找内容要比一页一页地查找速度快很多 。 在数据库中查找数据,也存在两种方法：一种是全表扫描,
与一页一页地翻书查找信息类似,用这种方法查找数据要从表的第一行开始逐行扫描查找,
直到找到所需信息；另一种是使用索引,索引是一个表中所包含值的列表,其中注明了表中包含各个值的行所在的存储位置,使用索引查找数据时,先从索引对象中获得
3.使用索引的代价为每一列都建立索引是不明智的,因为使用索引要付出一定的代价：
● 索引需要占用数据表以外的物理存储空间 。 例如,要建立一个聚集索引,需要大约１,２倍于数据大小的空间 。
● 创建索引和维护索引要花费一定的时间 。
● 当对表进行更新操作时,索引需要被重建,这样降低了数据的维护速度 。
4.建立索引的原则一般原则是：
● 主键列上一定要建立索引 。
● 外键列可以建索引 。
● 在经常查询的字段上最好建立索引 。
● 对于那些查询中很少涉及的列,重复值比较多的列不要建索引 。
对于定义为 text,Image和 Bit数据类型的列上不要建立索引。
7.2 索引的分类在 SQL Server数据库中,根据索引的存储结构不同将其分为两类：聚集索引和非聚集索引 。
7.2.1 聚集索引聚集索引是指表中数据行的物理存储顺序与索引顺序完全相同 。 每个表只能创建一个聚集索引 。
图 7.1 聚集索引的结构示意图
7.2.2 非聚集索引非聚集索引与聚集索引一样有 B 树结构,但是有两个重大差别：
● 数据行不按非聚集索引键的顺序排序和存储 。
● 非聚集索引的叶层不包含数据页 。
非聚集索引可以在有聚集索引的表,堆集或索引视图上定义 。 非聚集索引中的行定位器有两种形式：
● 如果表是堆集 （ 没有聚集索引 ）,行定位器就是指向行的指针 。 该指针用文件标识符
(ID),页码和页上的行数生成 。 整个指针称为行 ID。
● 如果表没有聚集索引,或者索引在索引视图上,则行定位器就是行的聚集索引键 。 如果聚集索引不是唯一的索引,SQL Server将添加在内部生成的值以使重复的键唯一 。 用户看不到这个值,它用于使非聚集索引内的键唯一,
图 7.2 非聚集索引的结构示意图
7.2.3 聚集和非聚集索引的性能比较当进行单行查找时,聚集索引的输入输出速度比非聚集索引快,因为聚集索引的索引级别较小 。 聚集索引非常适合于范围查询,因为服务器可以缩小数据范围,先得到第一行,再进行扫描,无需再次使用索引；非聚集索引速度稍慢,占用空间特别大,但也是一种较好的表扫描方法 。 非聚集索引可能覆盖了查询的全部过程 。 也就是说,假如所需数据在索引中,服务器就不必再返回到数据行中 。
7.3 索引 的操作
7.3.1 创建索引
SQL Server 2000可以自动创建唯一索引,以强制实施 PRIMARY KEY和 UNIQUE约束的唯一性要求 。 如果需要创建不依赖于约束的索引,
可以使用企业管理器创建索引,也可 以使用创建索引向导创建索引,还可以在查询分析器中用 SQL语句创建索引 。
创建索引时要注意：
● 只有表或视图的所有者才能创建索引,并且可以随时创建 。
● 对表中已依次排列的列集合只能定义一个索引 。
● 在创建聚集索引时,将会对表进行复制,
对表中的数据进行排序,然后删除原始的表 。 因此,数据库上必须有足够的空闲空间,以容纳数据副本 。
● 在使用 CREATE INDEX语句创建索引时,
必须指定索引,表以及索引所应用的列的名称 。
● 在一个表中最多可以创建 249个非聚集索引。默认情况下，创建的索引是非聚集索引。
●复合索引的列的最大数目为 16，各列组合的最大长度为 900B。
●要特别注意 WHERE子句中数据类型不匹配的问题，特别是 char和 varchar类型。它们不会被很好地优化，因为优化程序不能对索引使用数据分配统计。
在存储过程中很容易造成类型不匹配，
使用用户定义的数据类型有助于避免这个问题。
下面，我们分别介绍三种方式创建索引。
1.使用企业管理器创建索引
2.使用创建索引向导
3.使用 CREATE INDEX语句创建索引
7.3.2 查询索引信息
1.使用企业管理器查看索引信息
2.使用系统存储过程查看索引信息在查询分析器中执行系统存储过程
sp_helpindex或 sp_help查看数据表的索引信息,sp_helpindex只显示表的索引信息,sp_help除了显示索引信息外,还有表的定义,约束等其他信息 。
两者的语法格式基本相同，
我们以 sp_helpindesx为例介绍。其语法格式如下：
[EXEC] sp_helpindex
[ @objname = ] ‘name’
其中：
[@objname =] ‘name’是当前数据库中表或视图的名称。
7.3.3 索引更名系统存储过程 sp_rename可以用来更改索引的名称,其语法格式如下：
sp_rename[@objname=]’object_name
’，
[@newname,]’new_name’
[,[@objtype,]’object_type’]
其中：
● object_name是需要更改的对象原名。如果要重命名的对象是表中的一列，那么 object_name 必须为
table.column 形式。如果要重命名的是索引，那么 object_name 必须为 table.index 形式。
● new_name是对象更改后的名称。
new_name 必须是名称的一部分，并且要遵循标识符的规则。
● object_type是对象类型。
7.3.4 删除索引
1.使用企业管理器删除索引
2.使用 DROPINDEX语句删除索引使用 DROPINDEX语句可以删除表中的索引,其语法格式如下：
DROP INDEX表名,索引名 [,…n]
7.4.1 设置 FILLFACTOR选项当向一个已满的索引页添加新行时,SQL
Server要将该页进行拆分,将大约一半的行移到新页中,以便为新的记录行腾出空间,
这需要很大的开销 。 为了尽量减少页拆分,
在创建索引时,可以选择 FILLFACTOR(称为填充因子 )选项,此选项用来指定各索引页叶级的填满程度,这样在索引页上就可以留出额外的间隙和保留一定百分比的空间,供将来表的数据存储容量进行扩充和减少页拆分 。
7.4 设置索引的选项设置 FILLFACTOR值时，应考虑如下因素：
．填充因子的值是从 0到 100之间的百分比数值，用来指定在创建索引后对数据页的 填充比例。
．值为 100时表示页将填满，所留出的存储空间量最小。只有当不会对数据进行更改 时才会使用此设置。
．值越小则数据页上的空闲空间越大，
这样可以减少在索引增长过程中对数据页进行拆分的需要，但需要更多的存储空间。当表中数据会发生更改时，这种设置更为适当。
．使用 sp_configure系统存储过程可以在服务器级别设置默认的填充因子。
．填充因子只在创建索引时执行。
索引创建后，当表中进行数据的添加、
删除或更新时，不会保持填充因子。
7.4.2 设置 PAD_INDEX选项
FILLFACTOR选项用来指定各索引页叶级的填满程度,对于非叶级索引页需要使用
PAD_INDEX选项设置其预留空间的大小 。
PAD_INDEX选项只有在指定了 FILLFACTOR时才有用,因为 PAD_INDEX使用由 FILLFACTOR所指定的百分比 。 默认情况下,给定中间级页上的键集,SQL Server将确保每个索引页上的可用空间至少可以容纳一个索引允许的最大行 。 如果为 FILLFACTOR指定的百分比不够大,
无法容纳一行,SQL Server将在内部使用允许的最小值替代该百分比 。
7.5 索引的分析与维护索引创建之后,由于数据的增加,删除和修改等操作会使索引页发生碎块,因此必须对索引进行分析和维护 。
7.5.1 索引的分析常用的有 SHOWPLAN和 STATISTICS IO语句 。
1,SHOWPLAN
SHOWPLAN语句用来显示查询语句的执行信息,包含查询过程中连接表时所采取的每个步骤以及选择哪个索引 。 其语法格式为：
SETSHOWPLAN_ALL{ON|OFF} 和
SETSHOWPLAN_TEXT{ON|OFF}
其中,ON为显示查询执行信息,OFF为不显示查询执行信息 (系统默认 )。
7.5.2 索引的维护
1.使用 DBCCSHOWCONTIG语句该语句用来显示指定表的数据和索引的碎片信息 。 当对表进行大量的修改或添加数据之后,
应该执行此语句来查看有无碎片 。 其语法格式如下：
DBCCSHOWCONTIG[{table_name|
table_id|view_name|view，
index_name|index_id])]
其中：
● table_name|table_id|view_
name|view id是要对其碎片信息进行检查的表或视图。如果未指定任何名称，则对当前数据库中的所有表和索引视图进行检查。
2.使用 DBCC INDEXDEFRAG语句该语句的作用是整理指定的表或视图的聚集索引和辅助索引的碎片,其语法格式为：
DBCC INDEXDEFRAG
({daabase_name|database_id|0}
，
{table_name|table_id|’view_name’|view_
id}
,{index_name|index_id}
) [ WITH NO_INFOMSGS ]
其中：
● database_name,database_id|0指对其索引进行碎片整理的数据库。数据库名称必须符合标识符的规则。如果指定 0，则使用当前数据库。
● table_name|table_id|’view_name’|view_
id指对其索引进行碎片整理的表或视图。
● index_name|index_id是需要进行碎片整理的索引名称。
● WITH NO_INFOMSGS禁止显示所有信息性消息（具有从 0 到 10的严重级别）。
7.6 案例中的索引
1.创建一个复合索引为了方便按系部和专业查找指定的学生,
为学生表创建一个基于,系部代码,专业代码,组合列的非聚集,复合索引
xb_zy_index。 其程序清单如下：
USE Student
GO
CREATE INDEX xb_zy_index ON 学生
(系部代码,专业代码 )
GO
2.创建一个聚集,复合索引为,教师任课,表创建一个基于,教师编号,课程号,组合列的聚集,复合索引
jskc_index。 其程序清单如下：
USE Student
GO
CREATE CLUSTERED INDEX
jskc_index
ON 教师任课 (教师编号,课程号 )
GO
3.创建一个唯一、聚集、复合索引为教学计划表创建一个基于,课程号,专业代码,
组合列的唯一,聚集,复合索引 jxkc_zy_ndex。 其程序清单如下：
USE Student
GO
CREATE UNIQUE CLUSTERED
INDEX jxkc_zy_index ON 教学计划
(课程号,专业代码 )
WITH
PAD_INDEX,FILLFACTOR=70,
IGNORE_DUP_KEY
GO
练 习 题
1,什么是索引?使用索引有什么意义?
2,聚集索引和非聚集索引有何区别?
3,创建索引时要考虑哪些事项?
4,修改索引可以便用 ALTER INDEX命令吗,如果不能,说明修改索引的方法 。
5,如何查看表中的碎片信息？ 如何清除索引碎片?
谢谢各位！
返回主页
