三、索引优化（2）聚集索引-白红宇

三、索引优化（2）聚集索引

阅读量：6256 次

发布时间：2019-06-22

本文共 4063 字，大约阅读时间需要 13 分钟。

一、聚集索引的概念

　　聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。索引定义中包含聚集索引列。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序排序。

　　只有当表包含聚集索引时，表中的数据行才按排序顺序存储。如果表具有聚集索引，则该表称为聚集表。如果表没有聚集索引，则其数据行存储在一个称为堆的无序结构中。

　　下图是聚集索引的示意图。与前文所述的非聚集索引相比，聚集索引在结构上的最大特点是：索引的叶级就是数据页。

二、实验[三C]：（非唯一）聚集索引

1. 创建非聚集索引

　　继续使用前一篇文章的测试专用表，首先删除原有的非聚集索引，然后创建一个聚集索引。

DROP INDEX IX_person1_UserID ON person1

CREATE CLUSTERED INDEX IX_person1_UserID ON person1 (UserID)

2. 查看索引的空间分配

DBCC SHOWCONTIG ('person1') WITH ALL_INDEXES

　　结果如下：

DBCC SHOWCONTIG 正在扫描 'person1' 表...

表: 'person1' (245575913)；索引 ID: 1，数据库 ID: 8

已执行 TABLE 级别的扫描。

- 扫描页数................................: 4009

- 扫描区数..............................: 502

- 区切换次数..............................: 501

- 每个区的平均页数........................: 8.0

- 扫描密度 [最佳计数:实际计数].......: 100.00% [502:502]

- 逻辑扫描碎片 ..................: 0.37%

- 区扫描碎片 ..................: 0.80%

- 每页的平均可用字节数.....................: 44.2

- 平均页密度(满).....................: 99.45%

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

　　将上述结果与前文的非聚集索引相比，很明显可见数据页与索引页合为一体（索引ID=1）。注意，此处自动增加了9个页，后文将会说明原因。

3. 查看索引的层次

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'),1,NULL,'DETAILED')

　　结果如下：

Index _depth	Index _level	Record _count	Page _count	MinLen	MaxLen	AvgLen	PageDensity
3	0	80000	4009	399	407	401.498	99.45
3	1	4009	10	14	22	16.632	92.26
3	2	10	1	14	22	18	2.45

　　根据上表的数据，可以看到该索引共有3层。最底层 level=0 是叶级，它有4009个页面，这个叶级实际上就是包含80000行数据的数据页。非叶级共有2层，其中level=1 是中间级，它有10个页面，保存了4009个指针分别指向叶级的4009页；level=2 是根，它只有1个页面，保存了10个指针分别指向中间级的10个索引页。

　　中间级索引的行长度为14~22个字节，根级索引的行长度也为14~22 个字节。详细的计算方法，见《估计聚集索引的大小》

4. 总结

　　索引的层次与页面结构如下图：

三、实验[三D]：（唯一）聚集索引

　　聚集索引中的数据即可以唯一，也可以不唯一，取决于定义这个索引时的 UNIQUE 设置。

　　如果未使用 UNIQUE 属性创建聚集索引，SQL Server 将向表自动添加一个 4 字节 uniqueifier 列，使每个键唯一。此列和列值仅供内部使用，用户不能查看或访问。

1. 创建测试用的表

　　创建一个新的表，并添加80000行记录。

create table person3 (UserID int not null,pwd char(20),OtherInfo char(360),modifydate datetime)

declare @i int

set @i=0

while @i<80000

begin

insert into person3

select @i,cast(floor(rand()*100000) as varchar(10)),

cast(floor(rand()*100000) as char(50)), GETDATE()

set @i=@i+1

end

2. 检查页数量

　　使用DBCC SHOWCONTIG，查得该表的页的数量为4000页。

3. 创建唯一聚集索引

CREATE UNIQUE CLUSTERED INDEX IX_person3_UserID

ON person3 (UserID)

4. 检查页的数量

　　再次使用DBCC SHOWCONTIG，查得该表的页的数量仍然为4000页。

5. 查看索引的层次

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('person3'),1,NULL,'DETAILED')

　　结果为：

Index _depth	Index _level	Record _count	Page _count	MinLen	MaxLen	AvgLen	PageDensity
3	0	80000	4000	399	399	399	99.06
3	1	4000	7	11	11	11	91.75
3	2	7	1	11	11	11	1.10

　　与前一个实验中的不带UNIQUE 的聚集索引相比，由于不需要创建一个 4 字节的uniqueifier 列，因此唯一聚集索引的数据行仍然是原来的固定长度，数据页的数量也不会增加。而且，由于数据行的唯一性，非叶级索引的宽度也减小到11个字节，其中4个字节用于定义聚集索引的 int 列（UserID列），6个字节用于页指针，1个字节用于行的系统开销。

6. 总结

　　索引的层次与页面结构如下图所示：

四、聚集索引与约束

1. 主键的概念

　　主键，也称主关键字（Primary Key），是表中的一个或多个列，它的值用于惟一地标识表中的某一条记录。在两个表的关系中，主键用来在一个表中引用来自于另一个表中的特定记录。主键是一种唯一键，是表定义的一部分。

　　一个表只能包含一个主键约束。如果在其他列上建立主键，则原来的主键就会取消。定义主键后，在主键的左侧会显示一个钥匙状的图标，表示该字段已被设为主键。

　　在主键约束中定义的所有列都必须定义为 NOT NULL。如果没有指定为 Null 性，则加入主键约束的所有列的为 Null 性都将设置为 NOT NULL。

2. 主键与索引的关系

　　创建主键将自动创建相应的唯一索引、聚集索引或非聚集索引。例如：

ALTER TABLE person

ADD CONSTRAINT PK_person_UserID PRIMARY KEY CLUSTERED (UserID)

　　数据库在创建主键同时，会自动建立一个唯一索引。