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

一、聚集索引的概念

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

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

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

 

 

 

二、实验[三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 个字节。详细的计算方法，见《估计聚集索引的大小》http://technet.microsoft.com/zh-cn/library/ms178085.aspx

 

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)

　　数据库在创建主键同时，会自动建立一个唯一索引。
　　如果这个表之前没有聚集索引，同时建立主键时候没有强制指定使用非聚集索引，则建立主键时候，同时建立一个唯一的聚集索引。例如，下例中将自动创建一个聚集、唯一索引。

create table person (UserID int not null,pwd char(20),modifydate datetime) ALTER TABLE person  ADD CONSTRAINT PK_person_UserID PRIMARY KEY (UserID)

 

3. 唯一约束与索引的关系

　　唯一约束（UNIQUE CONSTRAINT）与主键约束相反，除非显式指定了聚集索引，否则，默认情况下将创建唯一的非聚集索引以强制执行唯一约束。

 

4. 约束与索引的主要区别

　　主键约束和唯一约束的主要目的都是为了保持数据行的唯一性，不允许有重复的数据行；而聚集索引的主要目的是为了使数据按照一定的顺序进行物理排序以加快查询的速度，并且允许有重复的数据行。