一、索引概述

1、概念

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

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

 

2. 分类

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

 

3. 索引B树

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

oralce 创建非聚集索引_数据库

 

　　在上图中，底部是叶级（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 （索引分配映射）页实现对堆的表扫描或串行读操作来找到容纳该堆的页的区。

oralce 创建非聚集索引_非聚集索引_02

 

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

 

三、堆上的索引

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

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

oralce 创建非聚集索引_非聚集索引_03

 

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

 

 

四、实验[三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') 类似方式取得。

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个索引页。

<?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. 总结

oralce 创建非聚集索引_数据库_04

 

 

五、实验[三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)

扫描页数”）减少了1页，即已经释放了1个页面。 

 

4. 收缩表的空间

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

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

扫描页数”=0），即已经释放了所有的空间。 

 

5. 总结

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

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

 


转载于:https://blog.51cto.com/jimshu/1252419