8--索引及其应用

42/63
复合索引
•应创建在需被频繁访问的列上； •最多可组合16个列； •组合列的总长度最大可以达到900字节，考 虑到查询效率，列总长度应尽量短； •所组合的列必须来自同一张表； •要使用复合索引，则查询的WHERE 子句必须参照复合索引的第一列； •首先定义最具唯一性的列。
43/63
8.4.2 在企业管理器上建立索引
页的类型 页的可用空间量 拥有页的对象的对象ID
3/63
数据 索引
八 种 页 类 型
文本/图像 全局/辅助全局分配映射表 页的可用空间 索引分配映射表
大容量更改映射表 差异更改映射表
4/63
存储在 单独的 页中
数据页包含除 text、 ntext和image数据 外的所有数据。
5/63
8.1.2 SQL Server中数据的访问
表扫描法
方 法
使用索引
6/63
表扫描法：当访问未建 立索引的表内数据时， 从表的起始处逐行查找， 直到找到符合条件的行 为止。
效 率 低
7/63
索引法：当使用索引
访问建有索引的表内 数据时，系统会通过 遍历索引树结构来查 找行的存储位置 。
效 率 高
8/63
8.2 创建索引的原因 和选择索引列
DROP INDEX SC.SC_Index
go
53/63
例：在表SC上建立Fillfactor选项 值为60的聚集索引SC_Index
CREATE CLUSTERED INDEX SC_Index ON SC(Sno,Cno) WITH FILLFACTOR=60
54/63
8.8.2 设置PAD_INDEX选项
存在 tempdb库中
不自动重 新计算
34/63
注意事项：
(1)若创建了 PRIMARY KEY约束或 UNIQUE约 束时
系统将自动为建 有这些约束的列 创建聚集索引
当删除PRIMARY KEY 约束或UNIQUE约束时， 这些列上创建的聚集索 引也会被自动删除
35/63
(2)执行CREATE INDEX语句创建索引必 须由是表的所有者完成 (3)每张表只能存在一个聚集索引
40/63
不仅可以为表中的单个列建立索引， 也可以为表中的一组列建立索引，即 复合索引 例：在Student数据库中的SC表的Sno 和Cno列上创建复合索引SC_Index
41/63
USE Student GO CREATE INDEX SC_Index ON dbo.SC(Sno,Cno) ON PRIMARY
37/63
例：在Student数据库中的Course表 的Cno列上创建唯一索引CnoIndex
USE Student GO CREATE UNIQUE INDEX CnoIndex ON dbo.Course(Cno) ON PRIMARY
38/63
若在表中存在重复值的列上创建唯一 索引, CREATE INDEX语句将失败并 返回带有第一个重复值的错误信息
但其他重 复的值也 可能存在
39/63
用户可通过SQL命令来查找到该 列上存在的所有重复行
SELECT Column_Name , COUNT (Column_Name) FROM table GROUP BY Column_Name HAVING COUNT (Column_Name)>1 ORDER BY Column_Name
(4)聚集索引的平均大小是表大小的5％， 但是，由于被索引列的不同聚集索引的大 小也会发生变化
(5)当数据页中的数据行被删除时，所占空 间将被回收并用于新的数据行
36/63
例：在Student数据库中的Course表 的Cn列上创建非聚集索引CnIndex
USE Student GO CREATE NONCLUSTERED INDEX CnIndex ON dbo.Course(Cn) ON PRIMARY
44/63
45/63
46/63
8.5 索引信息的查询
索引建立之后，在实际应用中，可 能需要查询索引信息，以查看建立了哪 些索引。查询索引命令如下：
EXEC sp_helpindex table_name
47/63
8.6 更改索引的名称
有时为了某种操作的方便，需要更改 索引的名称，更改索引名称的命令如下： EXEC sp_rename OldName, NewName
48/63
8.7 删除索引
在实际使用中，若不需要再使用 表上的某个索引或是表上的某个索引 已经对系统性能起负面影响时，那么 就需要删除该索引。
49/63
DROP INDEX [owner.]table_name.index_name [,[owner.]table_name.index_name…]
17/63
8.2.2 选择创建索引的数据列
因为创建索引要消耗一定的系 统性能，因此要考察对某列创建索 引的必要性。
1Leabharlann Baidu/63
定义有主关 键字和外部 关键字的列 需要按排 序顺序快 速或频繁 检索的列
需在指定范围 中快速或频繁 查询的列
这些情 况要考虑 创建索引 在集合过程
中需要快速 或频繁组合 到一起的列
21/63
8.3.1 聚集索引
聚集索引确定表中数据的物理顺序。 当以某字段作为关键字建立聚集索引时， 表中数据以该字段作为排序依据。因此， 一个表只能建立一个聚集索引，但该索 引可以包含多个列（组合索引）。
22/63
聚集 索引 由上 下两 层组 成 包含有实际的 数据页面，其 中存放着表中 的数据
含表中 的索引 页面， 用于数 据检索
23/63
SQL Server 是如何在一个 已经创建有聚 集索引的表上 检 索数据的？
24/63
25/63
8.3.2 非聚集索引
非聚集索引：数据存储在一 个地方，索引存储在另一个 地方。
26/63
特 点
非聚集索引 中的数据排 列顺序并不 是表格中数 据的排列顺 序 索引中的项目按索引键值的顺序存储， 而表中的信息按另一种顺序存储。
设置显示查询计划： SET SHOWPLAN_ALL ON 和 SET SHOWPLAN_TEXT ON Off： 不显示
57/63
例: 在表SC上查询Sno=“20070001”， Cno=“2”的信息，并显示查询处理过程 USE Student go SET SHOWPLAN_TEXT ON go SELECT Sno,Cno,grade FROM SC WHERE Sno=„20070001‟ AND Cno=„2‟
使用PAD_INDEX选项值来指定非叶 级索引页中的预留空间大小
例：在表SC上建立PAD_INDEX和Fillfactor 均为60的聚集索引SC_Index
CREATE CLUSTERED INDEX SC_Index ON SC(Sno,Cno) WITH PAD_INDEX,FILLFACTOR=60
33/63
续上页
索引存储页 修改数据时 [WITH 的填充率 控制的反应 [FILLFACTOR=fillfactor] 指定应 [[,]IGNORE_DUP_KEY] 除去并 [[,]DROP_EXISTING] 重建已 命名的 [[,]STATISTICS_NORECOMPUTE] 先前存 [[,]SORT_IN_TEMPDB ] ] 在的聚 [ON filegroup] 过期的 集/非聚 索引统计 集索引 生成索引的中间结果
55/63
8.9 索引的分析
索引建立后，用户应根据实际可能出 现的数据检索工作对查询进行分析，以判 定其是否能提高SQL Server的数据检索速 度。 常用的分析索引和查询性能的方法有 SHOWPLAN和STATISTIC IO。
56/63
设置SHOWPLAN选项以选择 是否让SQL Server显示查询计划
第8章 索引及其应用
1/63
8.1有关索引的基础知识
在数据库中，索引使数据库程序无须对 整个表进行扫描，就可以在其中找到所需数 据。 数据库中的索引是一个表中所包含的值 的列表，其中注明了表中包含各个值的行所 在的存储位置 。
2/63
8.1.1 SQL Server中数据的存储
数据存储的基本单位是页，其大 小是 8 KB，每页的开始部分是用于存 储系统信息的96字节的页首。
带有指向数 据存储位置 的指针
27/63
SQL Server在搜索数据值时，先 对非聚集索引进行搜索，找到数据 值在表中的位置，然后从该位置直 接检索数据。这使非聚集索引成为 精确匹配查询的最佳方法。
28/63
查询所获数据量较少时
考虑建 立非聚 集索引 的情况
某字段中的数据的唯一 性比较高时
29/63
9/63
8.2.1 创建索引的考虑因素
因素之一：加快数据检索
索引是针对表而建立的，创建索 引后，将为表建立索引页面。
每个索引页面中的行都 含有逻辑指针，以加快 数据检索。
10/63
如检索Student库中Stu表中的学生信息 为例，执行命令如下:
use Student go SELECT Sno,Sn FROM Stu WHERE Sn=„李连杰'
若在此列上不存在索引，查询要按表中数 据行的物理顺序逐行进行，这对于大型表 来说，甚至要花费几小时的时间。
11/63
因素之二：加快表的连接， 排序和分组工作
排序和分组工作要涉及到数 据的检索工作，因此建立了索引 后，通过提高数据检索的速度就 可以加快表的连接，排序和分组 工作。
12/63
因素之三：增强数据行的唯一性 通过在创建索引时定义唯 一性，可以增强表中数据行的 唯一性，从而保证表中的数据 不重复。
如下命令将默认Fillfactor 选项的设置 值由0改为50： exec sp_configure 'fill factor' , '50'
52/63
例:检查表SC上是否存在聚集索引 SC_Index，有则将其删除。 USE Student go IF EXISTS (SELECT name FROM sysindexes WHERE name='SC_Index')
13/63
创建索引要牺牲一定的磁盘空 间和系统性能。
在创建聚集索引期间，系统将 暂时使用当前数据库的硬盘空间。
14/63
创建索引所需的工作空间约为数 据库表的1.2倍，在建立索引时，数据 被复制以便建立索引。 索引建立后，旧的未加索引的表 被删除，创建索引时使用的硬盘空间 由系统自动收回。
15/63
30/63
8.4 建立索引
索引的建立可以通过两种途 径实现：一是在查询分析器窗口 中用SQL命令建立；另一种是利 用企业管理器以图形化的界面建 立。
31/63
建立不能被SQL Server所采用的 索引只会增加系 统的负担，降低 检索的速度
若用索引查询的 速度不如表扫描 法速度快，系统 仍会采用表扫描 法进行检索
19/63
很少有唯 在查询中 一值的列 几乎不涉 如记录性 及的列 别的列 可不考虑
创建索引
由text,ntext 或image数据类 型定义的列 只有较少 行数的表没 必要建索引
20/63
8.3 索引的分类
聚集索引 Clustered Index 非聚集索引 Nonclustered Index
使用权限为表 格拥有者，数 据库拥有者和 系统监督者
50/63
例：删除表SC中的索引SC_Index， 命令如下：
DROP INDEX SC.SC_Index go
51/63
8.8 设置创建索引的选项
8.8.1 设置Fillfactor选项
使用该选项可以在叶级索引页上分配自由 空间百分比以减少页的分离 1<FILLFACTOR<100
SQL Server只选用那些能加快数 据查询速度的索引，可利用性是 建立索引的首要条件。
32/63
8.4.1 SQL命令建立索引
索引关键字字段上不 允许有相同内容的记 录
簇/非簇 索引
CREATE[UNIQUE][clustered|nonclustered] INDEX index_name ON[[database.]ower.]{table_name|view_name} (column_name1 [,column_name2…])
存在索引的表，在修改数据时 （如插入、删除、更新），要对索引 进行更新。修改的数据越多，索引的 维护开销也就越大。因此，建立了索 引的列在执行修改操作时所花费的时 间更长。
16/63
若一个数据页已满或将要满时， 此时再插入数据，该数据页中最后一 些数据就必须转移到下一个页面中。 这样，就必须修改索引页中的内容， 以保证数据顺序的正确性，这需要花 费一定的时间。