SQLServer2012 (非)聚集索引存储探究

时间：2022-03-14 01:12

SQLServer2012 (非)聚集索引存储探究

Author:zfive5(zidong)

Email:zfive5@163.com

引子

由于写了前一篇文字《SQLServer2012 表IAM存储结构探究》，所有促成了写这一篇的缘由，其实之前也断断续续研究过，记得第一次对索引有感觉是2010年解决一客户查账时的效率问题，从5分钟到5秒的效率提升，这个之后也一直成为面试人时必问之题。

现在大学教育应该让学生学理论时对低效和高效有一个强烈的感官冲击，而不是死记硬背。

记得当时学校里讲的空间域和频域转化的傅里叶（虽然记不住具体算法，但中间的原理还是知道个80%）。

工作时间久了，真想在回学校再深造一下了。

最后还是想大家推荐《深入解析SQLServer2008》。

正文

通过下面创建实例表和索引

还是通过DBCC IND和 DBCC PAGE来观察数据和线索，具体说明参见

《SQLServer2012 表IAM存储结构探究》中“两个命令”章节

gxlsystem.com,布布扣

聚集索引

执行DBCC IND(‘A‘,‘table1‘,1)得到如下结果：

gxlsystem.com,布布扣

通过 indexLevel 、 NextPageFID、NextPagePID 、PrevPageFID、NextPagePID 可以构造出来一棵ｂｔｒｅｅ

1: (1:35644)

/ ...... \

0:(1,1627840)<-->(1,1627841)<--> .......(1,1627872)<-->(1,1627873)

Level-1

通过DBCC Page(‘A‘,1,35644,1)命令得到这个索引页的具体数据如下：

Level-0（叶子节点）

（1，35644）Page中的每一个记录里面有一个指向下层的页指针，

第一个指针（1,1627840）level为0，具体数据如下：

根据可以构造出来一棵BTree

2：（1，1627930）

/ \

1:(1,1627928)<-->(1,1627928)<-->(1,1627929)<-->(1,1627931)

/ \

0:(1,1627904)<-->(1,1627905)<-->(1,1627906)......... (1,1627960)<-->(1,1627961)

Level-2

通过DBCC Page(‘A‘,1, 1627930,1)命令，得到这个索引页的具体数据

Level-1

通过DBCC PAGE(‘A‘,1,1627928,1)命令，可以得到索引PAGE数据如下：

Level-0（叶子节点）

通过DBCC PAGE(‘A‘,1, 1627904,1)命令得到如下数据：

结论

1、 非聚集索引的叶子节点存储的是聚集索引的键值（如果不唯一会增加4字节的编号，如果没有聚集索引就是RID=FILE：PAGE：SLOT）

2、 每一个非聚集索引节点都保存了索引键值，所以覆盖索引不用再查询数据PAGE里，因此效率会高

查询过程分析

带聚集条件查询

select *fromTable1wherea=100

gxlsystem.com,布布扣

步骤1

DBCC Page(‘A‘,1,35644,3)

gxlsystem.com,布布扣

步骤2

91<=100<106

DBCC Page(‘A‘,1, 1627846,1)

叶子节点它就是数据PAGE，加黑的部分就是数据

步骤1

DBCC Page(‘A‘,1, 1627930,3)

gxlsystem.com,布布扣

步骤2

DBCC Page(‘A‘,1, 1627931,3)

gxlsystem.com,布布扣

步骤3

DBCC Page(‘A‘,1, 1627961,3)

gxlsystem.com,布布扣

步骤4

DBCC Page(‘A‘,1,35644,3)

gxlsystem.com,布布扣

步骤5

DBCC Page(‘A‘,1, 1627873,3)

PAGE: (1:1627873)

BUFFER:

BUF @0x000000046E033540

bpage = 0x0000000463C08000 bhash = 0x0000000000000000 bpageno = (1:1627873)

bdbid = 7 breferences = 0 bcputicks = 0

bsampleCount = 0 bUse1 = 22473 bstat = 0x9

blog = 0x1c9a bnext = 0x0000000000000000

PAGE HEADER:

Page @0x0000000463C08000

m_pageId = (1:1627873) m_headerVersion = 1 m_type = 1

m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x200

m_objId (AllocUnitId.idObj) = 99 m_indexId (AllocUnitId.idInd) = 256

Metadata: AllocUnitId = 72057594044416000

Metadata: PartitionId = 72057594040090624 Metadata: IndexId = 1

Metadata: ObjectId = 245575913 m_prevPage = (1:1627872) m_nextPage = (0:0)

pminlen = 508 m_slotCnt = 4 m_freeCnt = 6020

m_freeData = 2164 m_reservedCnt = 0 m_lsn = (2320:18122:23)

m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0

m_tornBits = 300036918 DB Frag ID = 1

Allocation Status

GAM (1:1533696) = ALLOCATED SGAM (1:1533697) = NOT ALLOCATED

PFS (1:1625688) = 0x40 ALLOCATED 0_PCT_FULL DIFF (1:1533702) = CHANGED

ML (1:1533703) = NOT MIN_LOGGED

DATA:

Slot 0, Offset 0x60, Length 517, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Record Size = 517

Memory Dump @0x00000000024EA060

0000000000000000: 3000fc01 f0010000 41414141 41414141 41414141 0.......AAAAAAAAAAAA

0000000000000014: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA

0000000000000028: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA

000000000000003C: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA

0000000000000050: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA

0000000000000064: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA

0000000000000078: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA

000000000000008C: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA

00000000000000A0: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA

00000000000000B4: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA

00000000000000C8: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA

00000000000000DC: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA

00000000000000F0: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA

0000000000000104: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA

0000000000000118: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA

000000000000012C: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA

0000000000000140: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA

0000000000000154: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA

0000000000000168: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA

000000000000017C: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA

0000000000000190: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA

00000000000001A4: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA

00000000000001B8: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA

00000000000001CC: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA

00000000000001E0: 41414141 41414141 41414141 41414141 41414141 AAAAAAAAAAAAAAAAAAAA

00000000000001F4: 41414141 41414141 03000001 00050243 00 AAAAAAAA.......C.

Slot 1, Offset 0x265, Length 517, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Record Size = 517

Memory Dump @0x00000000024EA265

0000000000000000: 3000fc01 f1010000 41414141 41414141 41414141 0.......AAAAAAAAAAAA