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

2024-12-09 技术教程

一、索引结构

　　在聚集索引上建立非聚集索引，在日常应用中经常发生。

　　非聚集索引具有独立于数据行的结构。非聚集索引包含非聚集索引键值，并且每个键值项都有指向包含该键值的数据行的指针。

　　从非聚集索引中的索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页是存储在堆中还是聚集表中，对于聚集表，行定位器是聚集索引键。

二、实验[三E]

　　继续使用上一篇文章中创建的唯一聚集索引，在此基础之上新建一个非聚集索引。

1.创建非聚集索引

CREATEINDEXIX_person1_UserIDModifyDate
ONperson1(UserID,ModifyDate)

2.查看索引占用的空间

DBCCSHOWCONTIG('person1')WITHALL_INDEXES

　　结果如下：

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

3.查看索引的层次

　　对于建立在聚集索引上的非聚集索引，

SELECTindex_depth,index_level,record_count,page_count,
min_record_size_in_bytesas'MinLen',
max_record_size_in_bytesas'MaxLen',
avg_record_size_in_bytesas'AvgLen',
convert(decimal(6,2),avg_page_space_used_in_percent)as'PageDensity'
FROMsys.dm_db_index_physical_stats
(8,OBJECT_ID('person1'),2,NULL,'DETAILED')

　　结果如下表所示：

index

_depth

Index

_level

Record

_count

Page

_count

MinLen

MaxLen

AvgLen

PageDensity

80000

179

99.36

179

53.05

　　根据上表的数据，可以发现它与堆上的非聚集索引的数据是一样的。该索引共有2层。level=0是叶级，它有179个页面，指向底层的聚集索引的根页；level=1是这个非聚集索引的根页，它只有1个页面，指向叶级的179个索引页。

三、比较三类索引占用的页数

　　比较前面几个实验，各类索引占用的页数如下：

1.堆

　　在实验[三A]中，堆是最原始的结构，index_id=0，存储了80000条记录，占用了4000页。

2.聚集索引

　　聚集索引的index_id=1。

　　唯一聚集索引在叶级将数据页重新进行物理排序，不会额外增加数据页。由于索引宽度固定，因此在根级只占用了1个页，中间级占用了7个页。一共占用了1+7+4000=4008页。与堆相比，非叶级的索引页多了8页。

　　非唯一聚集索引需要在后台保持数据的唯一，因此在后台增加了一个4字节的uniqueifier列，有可能需要增加额外的数据页。在前面的案例中，非唯一聚集索引使用了4009页，也就是多了9个页。同时由于索引宽度的开销较大，中间级占用了10个页，加上根级占用了1个页，一共占用了1+10+4009=4020页。与堆相比，叶级索引页（数据页）多了9页，非叶级的索引页多了11页。

3.非聚集索引

　　堆上的非聚集索引与聚集索引上的非聚集索引，index_id>=2，占用了相同数量的索引页面，页面数量为：179+1=180页。