三、索引优化（1）堆上的非聚集索引

一、索引概述

1、概念

　　可以把索引理解为一种特殊的目录。就好比《新华字典》为了加快查找的速度，提供了几套目录，分别按拼音、偏旁部首、难检字等排序，这样我们就可以方便地找到需要的字。

　　与书中的索引一样，数据库中的索引使您可以快速找到表或索引视图中的特定信息。索引包含从表或视图中一个或多个列生成的键，以及映射到指定数据的存储位置的指针。通过创建设计良好的索引以支持查询，可以显著提高数据库查询和应用程序的性能。索引可以减少为返回查询结果集而必须读取的数据量。索引还可以强制表中的行具有唯一性，从而确保表数据的数据完整性。

 

2. 分类

　　SQL SERVER提供了两种索引：聚集索引（Clustered Index）和非聚集索引（Nonclustered Index）。 

 

3. 索引B树

　　索引是按照B树结构组织的。如下图。

 

　　在上图中，底部是叶级（leaf level），用于保存指向数据行的指针；非叶级用于导航到下一个非叶级或者叶级，非叶级包括2部分，即顶部的根（root）和中间部分的中间级（intermediate level）。

　　假设数据页每页可以保存2条记录，索引的平均宽度为20字节，则索引的每个页（8KB）可以保存约400个行指针，理论上（排除碎片等因素）上图所示的4级树能够搜索的记录行可以达到：2*400*400*400=1.28亿。这表示查询时如果使用此索引，只需要4次I/O操作就可以导航至对应的数据行。

 

 

二、堆的物理结构

　　SQL Server 的数据组织结构为HOBT（堆或平衡树），详见《SQL Server 数据文件存储结构》 http://jimshu.blog.51cto.com/3171847/987275。

　　如果数据以堆的方式组织，那么数据行不按任何特殊的顺序存储，数据页也没有任何特殊的顺序。 可以通过扫描 IAM （索引分配映射）页实现对堆的表扫描或串行读操作来找到容纳该堆的页的区。

 

　　从上图可见，数据页之间没有任何关系，完全依赖IAM页进行组织。对于一个查询，需要首先查询IAM页，然后根据IAM页提供的指针去遍历对应的每个区，然后返回这些区内符合查询条件的页。如果IAM页损坏，则整张表的结构被破坏，数据基本上不能被修复。 

 

三、堆上的索引

　　索引中的每个索引行都包含非聚集键值和行定位符。此定位符指向堆中包含该键值的数据行。

　　索引行中的行定位器是指向行的指针。该指针由文件标识符 (ID)、页码和页上的行数生成。整个指针称为行 ID (RID)。 

 

　　从上表可见，这是一个完整的树状结构。索引的最顶层是根，根页存放的指针指向中间级（下一级的非叶级索引）或者指向叶级。索引的最底层是叶级，只能有一个叶级，叶级页存放的指针指向真实的数据行。

 

 

四、实验[三A]：堆上的非聚集索引

1. 构建一个堆结构的表

　　创建一张没有聚集索引的表，并为这张表添加80000条记录。

create table person1 (UserID int,pwd char(20),OtherInfo char(360),modifydate datetime) declare @i int set @i=0 while @i<80000 begin   insert into person1    select cast(floor(rand()*100000) as int), cast(floor(rand()*100000) as varchar(20)),   cast(floor(rand()*100000) as char(360)), GETDATE()   set @i=@i+1 end

 

2. 添加一个非聚集索引

　　为这个堆结构的表创建一个非聚集索引。

CREATE NONCLUSTERED INDEX IX_person1_UserID      ON person1 (UserID)

 

3. 表和索引的页面分配统计

　　使用DBCC命令查看表和索引的页面分配情况。

DBCC SHOWCONTIG ('person1') WITH ALL_INDEXES

 

　　显示结果如下：

DBCC SHOWCONTIG 正在扫描 'person1' 表... 表: 'person1' (245575913)；索引 ID: 0，数据库 ID: 8 已执行 TABLE 级别的扫描。 - 扫描页数................................: 4000 - 扫描区数..............................: 502 - 区切换次数..............................: 501 - 每个区的平均页数........................: 8.0 - 扫描密度 [最佳计数:实际计数].......: 99.60% [500:502] - 区扫描碎片 ..................: 1.79% - 每页的平均可用字节数.....................: 76.0 - 平均页密度(满).....................: 99.06% DBCC SHOWCONTIG 正在扫描 'person1' 表... 表: 'person1' (245575913)；索引 ID: 2，数据库 ID: 8 已执行 LEAF 级别的扫描。 - 扫描页数................................: 179 - 扫描区数..............................: 23 - 区切换次数..............................: 22 - 每个区的平均页数........................: 7.8 - 扫描密度 [最佳计数:实际计数].......: 100.00% [23:23] - 逻辑扫描碎片 ..................: 0.00% - 区扫描碎片 ..................: 4.35% - 每页的平均可用字节数.....................: 51.3 - 平均页密度(满).....................: 99.37% DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

 

　　以上结果显示，数据页共有4000页（即4,000*8=32,000KB），占用502个区（即502*64=32,128KB）；索引的叶级共有179页（即179*8=1,432KB），占用23个区（即23*64=1,72KB）。 

 

4. 查看索引的级数

　　根据DBCC SHOWCONTIG的结果，我们现在可以结合DMV来查看该索引的每一级是如何分布。

SELECT index_depth, index_level, record_count, page_count, min_record_size_in_bytes as 'MinLen', max_record_size_in_bytes as 'MaxLen', avg_record_size_in_bytes as 'AvgLen', convert(decimal(6,2),avg_page_space_used_in_percent) as 'PageDensity' FROM sys.dm_db_index_physical_stats (8, OBJECT_ID('person1'),2,NULL,'DETAILED')

　　注意，sys.dm_db_index_physical_stats 函数的5个参数分别表示如下意义：

　　第1个参数为该数据库的 ID。在本例中，DBCC SHOWCONTIG 已经显示了该数据库的 ID=8。也可以通过 DB_ID('DatabaseName') 类似方式取得。

　　第2个参数为该表的 ID。可以通过 OBJECT_ID('TableName') 类似方式取得。或者通过 select * from sys.objects where name='person' 查找到具体的OBJECT_ID 。

　　第3个参数为该索引的 ID。在本例中，DBCC SHOWCONTIG 已经显示了该索引的 ID=2。如果 ID=0，则指向数据页（堆或聚集索引）。NULL表示需要获得所有的索引。

　　第4个参数表示分区号。NULL表示需要获得所有分区的信息。

　　第5个参数表示希望返回的信息级别。NULL表示不返回所有的信息。

　　结果如下表所示：

index _depth	Index _level	Record _count	Page _count	MinLen	MaxLen	AvgLen	PageDensity
2	0	80000	179	16	16	16	99.37
2	1	179	1	22	22	22	53.05

 　　根据上表的数据，可以看到该索引共有2层。level=0 是叶级，它有179个页面，指向包含80000行数据的数据页；level=1 是根页，它只有1个页面，指向叶级的179个索引页。

　　叶级索引的行长度为16字节，它包括：4 个字节对应于 int 列（UserID列），8 个字节对应于<?xml:namespace prefix="[default]" ns="http://www.w3.org/1999/xhtml">?xml:namespace>数据行定位符（即堆 RID），1个字节对应于索引行的行标题开销，3个字节用于NULL位图（UserID列可以为空）。详细的计算方法，见《估计非聚集索引的大小》 http://technet.microsoft.com/zh-cn/library/ms190620.aspx

　　根页的行长度为22字节，即在叶级的行长度加6字节，这6个字节对应下一级索引页的指针。

 

5. 查看堆的分布

　　查看索引 ID=0 的分布，实际上就是查看堆的页面分布情况。

SELECT index_depth, index_level, record_count, page_count, min_record_size_in_bytes as 'MinLen',max_record_size_in_bytes as 'MaxLen', avg_record_size_in_bytes as 'AvgLen', convert(decimal(6,2),avg_page_space_used_in_percent) as 'PageDensity' FROM sys.dm_db_index_physical_stats (8, OBJECT_ID('person1'),0,NULL,'DETAILED')

　　结果如下：

index _depth	Index _level	Record _count	Page _count	MinLen	MaxLen	AvgLen	PageDensity
1	0	80000	4000	399	399	399	99.06

 

6. 总结

 

 

五、实验[三B]：堆上的（唯一、非空值）非聚集索引

1. 构建一个堆结构的表

　　创建一张没有聚集索引的表，并为这张表添加80000条记录。注意，与前一个实验不同的是，UserID列是唯一且非空。

create table person2 (UserID int not null,pwd char(20),OtherInfo char(360),modifydate datetime) declare @i int set @i=0 while @i<80000 begin   insert into person2    select @i, cast(floor(rand()*100000) as varchar(20)),   cast(floor(rand()*100000) as char(360)), GETDATE()   set @i=@i+1 end

 

2. 添加一个非聚集索引

　　为这个堆结构的表创建一个唯一、非聚集索引。

CREATE UNIQUE NONCLUSTERED INDEX IX_person2_UserID      ON person2 (UserID)

 

 

3. 查看索引的级数

　　使用DMV来查看所有的索引分布。

SELECT index_depth, index_level, record_count, page_count, min_record_size_in_bytes as 'MinLen', max_record_size_in_bytes as 'MaxLen', avg_record_size_in_bytes as 'AvgLen', convert(decimal(6,2),avg_page_space_used_in_percent) as 'PageDensity' FROM sys.dm_db_index_physical_stats (8, OBJECT_ID('person2'),NULL,NULL,'DETAILED')

　　结果如下表所示：

index _depth	Index _level	Record _count	Page _count	MinLen	MaxLen	AvgLen	PageDensity
1	0	80000	4000	399	399	399	99.06
2	0	80000	179	16	16	16	99.37
2	1	179	1	11	11	11	53.05

　　根据上表的数据，可以看到该聚集索引共有2层（仅指index_depth=2的索引）。叶级索引的行长度为16字节不变。但根页的行长度从22字节降为11字节。

 

 

六、删除堆中的数据

1. 使用delete删除所有数据

delete person2

 

2. 查看表的空间

　　使用DBCC SHOWCONTIG查看该表

DBCC SHOWCONTIG ('person2') WITH ALL_INDEXES

 

　　发现仍然占用着数据页（“扫描页数”>0）。 

DBCC SHOWCONTIG 正在扫描 'person2' 表... 表: 'person2' (261575970)；索引 ID: 0，数据库 ID: 8 已执行 TABLE 级别的扫描。 - 扫描页数................................: 296 - 扫描区数..............................: 39 - 区切换次数..............................: 38 - 每个区的平均页数........................: 7.6 - 扫描密度 [最佳计数:实际计数].......: 94.87% [37:39] - 区扫描碎片 ..................: 7.69% - 每页的平均可用字节数.....................: 8056.0 - 平均页密度(满).....................: 0.47% DBCC SHOWCONTIG 正在扫描 'person2' 表... 表: 'person2' (261575970)；索引 ID: 2，数据库 ID: 8 已执行 LEAF 级别的扫描。 - 扫描页数................................: 1 - 扫描区数..............................: 1 - 区切换次数..............................: 0 - 每个区的平均页数........................: 1.0 - 扫描密度 [最佳计数:实际计数].......: 100.00% [1:1] - 逻辑扫描碎片 ..................: 0.00% - 区扫描碎片 ..................: 0.00% - 每页的平均可用字节数.....................: 8078.0 - 平均页密度(满).....................: 0.20% DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

 

 

3. 使用表锁删除数据

　　往该表中添加 1 条记录，然后再使用以下命令删除。

insert person2 values (1,'abc','abc',GETDATE()) delete person2 with (TABLOCK)

　　再次使用DBCC SHOWCONTIG查看该表，可以看到占用的页数（“扫描页数”）减少了1页，即已经释放了1个页面。 

 

4. 收缩表的空间

　　使用以下命令将该数据库的数据文件进行收缩。

DBCC SHRINKFILE (N'db01' , 0, TRUNCATEONLY)

　　再次使用DBCC SHOWCONTIG查看该表，发现仍然占用的页数为零（“扫描页数”=0），即已经释放了所有的空间。 

 

5. 总结

　　SQL Server 默认在 delete 时不会加上表锁（即TABLOCK），此时堆结构的表不会释放空间给其它数据复用。

　　在删除堆中的行数据时加上TABLOCK，或者手动执行SHRINKFILE （或SHRINKDB）才能释放堆中的空闲页面。