第8章 索引

返回目录
8.1 索引概念
8.1.2 聚簇索引和非聚簇索引
2．非聚簇索引（Nonclustered Index） 非聚集索引与书中内容的组织方式类似，数据存储在一个地 方，索引存储在另一个地方，索引带有指针指向数据的存储位置 ，索引中的项目按键值的顺序存储，而表中的数据按另一种顺序 存储，具有完全独立于数据行的结构，使用非聚集索引不用将物 理数据页中的数据按列排序。
返回目录
8.2 索引的创建与管理
8.2.1 用T-SQL语句创建索引
【例8.3】 使用Ｔ-SQL语句在“教务管理数据库”的“学生表”上创 建一名为“XS_XH_XM_INDEX”的唯一性聚集索引，索引关键字为 “学号”和“姓名”，均按降序排序，删除并重新创建同名的索
引文件。
CREATE UNIQUE CLUSTERED INDEX XS_ XH _XM_INDEX ON 学生表 (学号 DESC，姓名 DESC) WITH DROP_EXISTING
返回目录
8.2 索引的创建与管理
8.2.3 用SSMS创建与管理索引
2．使用SQL Server Management Studio 查看索引 1）打开SQL SBiblioteka Baidurver Management Studio。 2）在对象资源管理器中展开要查看索引的数据库，再展开“表”结点 3）展开要查看索引的表。 4）展开“索引”结点，则会出现表中已存在的索引列表。 5）双击某一索引名称，则出现索引属性对话框，用户可以查看索引的 信息。
返回目录
8.2 索引的创建与管理
8.2.1 用T-SQL语句创建索引
【例8.2】 使用Ｔ-SQL语句在“教务管理数据库”的“学生表”上创 建一个名为“XS_XH_INDEX”的唯一性聚集索引，索引关键字为“ 学号”，按降序排列，填充因子70%。
CREATE UNIQUE CLUSTERED INDEX XS_XH_INDEX ON 学生表(学号 DESC ) WITH FILLFACTOR = 70
返回目录
8.2 索引的创建与管理
8.2.2 用T-SQL语句管理索引
当索引创建之后，可以使用T-SQL语句查看、更名和删除索引。 1．查看索引 在创建索引之前或在创建索引之后，可以用sp_helpindex系统存 储过程查看表的所有索引信息。 语法格式： SP_HELPINDEX 表名
【例８.4】用系统存储过程sp_helpindex查看教务管理数据库中 “学生表”的索引信息。
在表中，最多可以建立250个非聚集索引，或者249个非聚集 索引和1个聚集索引。
返回目录
8.1 索引概念
8.1.3 创建索引的优缺点
1．创建索引的优点 • • • • 通过创建唯一索引，可以保证数据记录的唯一性。 可以大大加快数据检索速度。 可以加速表与表之间的连接，这一点在实现数据的参照完整性 方面有特别的意义。 在使用ORDER BY和GROUP BY子句中进行检索数据时，可以显著 减少查询中分组和排序的时间。
返回目录
8.2 索引的创建与管理
8.2.3 用SSMS创建与管理索引
1．使用SQL Server Management Studio 创建索引 1）打开SQL Server Management Studio。 2）展开要创建索引的数据库，再展开“表”结点。 3）展开要建索引的表。 4）在“索引”结点上单击右键，选择“新建索引”。 5）在“索引名称”文本框中，输入“ZYB_ _ZYH_INDEX”，在“索引 类型”列表框中选择索引类型为“非聚集”，“唯一”索引。 6）单击“添加”按钮，弹出对话框，选择要在其上创建索引的列。 7）单击“确定”按钮，回到“新建索引”对话框，其中，“排序”列 用于设置索引的排列顺序。如“升序”。 8）单击“确定”按钮，即完成了索引的创建过程。
返回目录
8.2 索引的创建与管理
8.2.1 用T-SQL语句创建索引
【例8.1】使用Ｔ-SQL语句在“教务管理数据库”数据库的“学生表” 上创建一个名“XS_XM_INDEX”的非唯一性非聚簇索引，索引关键 字为“姓名”，升序，填充因子50%。
CREATE NONCLUSTERED INDEX XS_XM_INDEX ON 学生表(姓名 ASC ) WITH FILLFACTOR = 50
返回目录
8.2 索引的创建与管理
8.2.1 用T-SQL语句创建索引
说明： UNIQUE：用于指定为表或视图创建唯一索引，即不允许存在索引值 相同的两行。 CLUSTERED：用于指定创建的索引为聚集索引。 NONCLUSTERED：用于指定创建的索引为非聚集索引。 PAD_INDEX：用于指定索引中间级中每个页（节点）上保持开放的 空间。 FILLFACTOR = 填充因子：用于指定在创建索引时，每个索引页的数 据占索引页大小的百分比，填充因子的值 为1到100。 DROP_EXISTING：用于指定应删除并重新创建已命名的先前存在的 聚集索引或者非聚集索引。
返回目录
8.3 索引的维护
在创建索引后，为了得到最佳的性能，必须对索引进行维护。 因为随着时间的推移，用户频繁执行插入、修改和删除等一系列 操作，这些操作会使数据变得支离破碎，从而造成索引性能的下 降。SQL Server提供了多种方法来维护索引。 1．更新索引的统计信息
在创建索引时，SQL Server会自动存储有关的统计信息，查 询优化器会利用索引的统计信息来估算使用该索引查询数据的成 本。但随着用户频繁执行插入、修改和删除等一系列操作后，这 些统计信息可能已经过时了，因此有必要对索引的这些统计信息 进行更新。
返回目录
8.2 索引的创建与管理
8.2.3 用SSMS创建与管理索引
3．使用SQL Server Management Studio 删除索引 1）打开SQL Server Management Studio。 2）展开要查删除索引的数据库，再展开“表”结点。 3）展开要删除索引的表。 4）展开择“索引”结点，则会出现表中已存在的索引列表。 5）选择某个需要删除的索引，如“ZYB_ _ZYH_INDEX”索引，单击鼠 标右键，在弹出的菜单中单击“删除”菜单项，则打开删除索引 对话框。 6）单击“确定”按钮后，完成删除操作。
EXEC SP_HELPINDEX 学生表
返回目录
8.2 索引的创建与管理
8.2.2 用T-SQL语句管理索引
2．重命名索引 创建索引后，可以用sp_rename系统存储过程来更改索引的名称。
语法格式： SP_RENAME 表名.原索引名,新索引名
【例８.5】用系统存储过程sp_ rename将“学生表”的索引 XS_XM_INDEX 重新命名为XS_XM_INDEX 2。
SP_RENAME '学生表.XS_XM_INDEX ',' XS_XM_INDEX2 '
返回目录
8.2 索引的创建与管理
8.2.2 用T-SQL语句管理索引
3．删除索引 在创建索引之后，如果该索引不再需要，使用DROP INDEX命令可 以删除一个或者多个当前数据库中的索引。 语法格式： DROP INDEX 表名.索引名[,...n]
第8章 索引
知识技能目标：
1．理解索引的概念、索引种类和创建索引的优缺 点 2．熟练掌握索引的创建和删除
第8章 索引
引导案例：
前面的章节中已经创建了教务管理数据库， 输入了相应的数据。数据库建立起来后，就可以 操作数据库中的数据了，其中最主要的操作就是 查询操作。为了提高查询的效率，就需要在相应 的表上创建索引。本章介绍这方面的技术。
返回目录
8.2 索引的创建与管理
8.2.1 用T-SQL语句创建索引
用T-SQL语句创建索引，通过使用CREATE INDEX语句来实现。 语法格式： CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX 索引名 ON {表名|视图名}(列名[ASC|DESC][ , ...n]) [WITH [PAD_INDEX] [[,] FILLFACTOR = 填充因子 ] [[,] DROP_EXISTING]]
第8章 索引
8.1 索引概念
8.2 索引的创建与管理
8.3 索引的维护
小结
8.1 索引概念
8.1.1 索引的概念
用户对数据库最频繁的操作是数据查询，一般情况下，执行查 询操作时，需要对整个表进行数据搜索。当表中的数据很多时， 搜索就需要很长的时间，为了提高检索数据的效率，数据库引入 了索引机制。 在数据库中，使用索引无须对整个表扫描，就可以找到所需要 的数据。索引是某个表中一列或者若干列值的集合和相应的指向 表中物理标识这些值的数据页的逻辑指针清单。可以在表或视图 的一列或多列上创建索引，基于两列或多列上建立的索引称为复 合索引。如果表中任意两行被索引的列值不允许出现重复值，那 么这种索引称为唯一（UNIQUE）索引。
返回目录
8.1 索引概念
8.1.2 聚簇索引和非聚簇索引
按照索引的组织方式，可以将索引分为聚簇索引和非聚簇索引。 1．聚簇索引（Clustered Index）
对表的物理数据页中的数据按列进行排序，然后再重新存储到磁 盘上，即聚簇索引与数据是混为一体的，它的叶节点中存储的是 实际的数据。
可以在表或视图的一列或多列上的组合上创建索引，在实际开发 中，一般在定义成主键的列建立聚集索引，且创建主键约束时， 若表中没有聚集索引，则SQL Server将主键列作为聚集索引列。 在每个表中只能创建一个聚集索引。
返回目录
8.1 索引概念
8.1.4考虑建索引的列和不考虑建索引的列
2．不考虑建索引的列 建立索引需要产生一定的存储开销，在进行插入和更新数据的操 作时，维护索引也要花费时间和空间。 一般来说，如下一些列不考虑建立索引： （1）很少或从来不在查询中引用的列，因为系统很少或从来不根据这 个列的值去查找数据行。 （2）只有两个或很少几个值的列（如性别，只有两个值“男”或“女 ”），以这样的列创建索引并不能实现建立索引的好处。 （3）以bit、text、image数据类型定义的列。 （4）数据行数很少的小表一般也没有必要创建索引。
返回目录
8.1 索引概念
8.1.3 创建索引的优缺点
2．创建索引的缺点 • • 创建索引要花费时间和占用存储空间。 建立索引加快了数据检索速度，却减慢了数据修改速度。
返回目录
8.1 索引概念
8.1.4考虑建索引的列和不考虑建索引的列
1．考虑建索引的列 如果在一个列上创建索引，该列就称为索引列。索引列中的值称 为关键字值。 考虑建索引的列有以下这些列： （1）主键：通常检索、存取表是通过主键来进行的。因此，应该考虑 在主键上建立索引。 （2）连接中频繁使用的列：用于连接的列若按顺序存放，系统可以很 快地执行连接。如外键，除用于实现参照完整性外，还经常用于 进行表的连接。 （3）在某一范围内频繁搜索的列和按排序顺序频繁检索的列。
返回目录
8.3 索引的维护
1．更新索引的统计信息
可以使用UPDATE STATISTICS命令来更新索引的统计信息。 语法格式： UPDATE STATISTICS 表名 索引名 【例8.7】使用UPDATE STATISTICS命令，更新在“教务管理数据 库”数据库的“学生表”的“XS_XH_INDEX”索引的统计信息。
UPDATE STATISTICS 学生表 XS_XH_INDEX
返回目录
8.3 索引的维护
2．使用DDCC SHOWCONTIG命令扫描表
对表的频繁操作可能会导致表碎片，而表碎片会造成数据库查询 性能的降底，用户可以使用DBCC SHOWCONTIG来扫描表，并 通过返回值确定该表的索引页是否已经严重不连续。 语法格式： DBCC SHOWCONTIG (表名，索引名)
返回目录
8.2 索引的创建与管理
8.2.2 用T-SQL语句管理索引
3．删除索引 【例８.6】 用DROP语句将“学生表”的索引XS_XM_INDEX1删除。
DROP INDEX 学生表.XS_XM_INDEX1
注意： 删除索引时要注意，如果索引是在CREATE TABLE语句中创建的，只能 用ALTER TABLE命令来删除索引，而不能用DROP INDEX来删除。