sql server 索引存储和索引结构

上一篇文章中 https://blog.csdn.net/mmlamei?spm=1010.2135.3001.5343的最后，通过对锁的分析，可以知道：当使用聚集索引作为条件更新数据时，会先找到这条数据，给它加上互斥锁更新数据；如果不使用索引更新数据时，会把每一条数据都加上更新锁，然后释放更新锁，直到找到满足条件的数据，加上互斥锁更新数据。使用聚集索引只查找了1条数据内容，但是不使用索引相当于把所有数据都扫描了一遍。

一、sql server 的索引是使用何种结构呢？

SELECT * 
FROM sys.indexes 
WHERE object_id = OBJECT_ID('tablename')

索引的类型

sys.indexes字段说明：https://docs.microsoft.com/zh-cn/sql/relational-databases/system-catalog-views/sys-indexes-transact-sql?view=sql-server-ver16

二、B+树的结构

B树

B+树

三、sql server 索引的存储

1、查看AIM映射

DBCC IND(数据库名称,表名称,-1)

 select DB_ID('数据库名称')  查找数据库的ID，(DBCC  Page 的第一个参数)
 DBCC TRACEON (3604,2588)
 DBCC Page (9,1,1222393,3)
 DBCC Page (9,1,1222398,3)

2、分析索引的存储位置

DBCC IND(数据库名称,表名,-1)

SELECT FirstIAM, * FROM sys.sysindexes WHERE id = OBJECT_ID('表名')

indid=1表示这是一个聚集索引

FirstIAM  0xF9A612000100（1:1222393）
0xF9A612000100
从右往左读，两数字为一组
00 01 00  12 A6 F9
前两组表示文件编  （PageFID），即0X0001  （1）
，后四组表示页编号（PagePID）,即0X0012A6F9  (1222393)

那么FirstIAM的地址指针0xF9A612000100又是如何跟IAM页关联的呢? 
首先必须拆分这个地址指针，从右往左读，两数字为一组，得到0X 00 01 00  12 A6 F9。前两组表示文件编号（PageFID），即0X0001（1），后四组表示页编号（PagePID），即0X0012A6F9（1222393）。这样FirstIAM就与IAM页关联起来了。


SQL-Server在接到查询请求后，便会首先分析sys.sysindexes的索引标识符indid，可以看到聚集索引结构的indid=1，这时就会查找root的字段，而root指向的是聚集索引根级索引页0xF8B638000100 (1:3716856)。数据页的结构几乎完全相同，也是8KB固定大小

NextPagePID和PrePagePID将数据页串联成一个数据链表，和堆结构的数据页是有明显的区别的。这是B+树的一个特点所在

3、查看索引的数据，从root开始

root指向的是聚集索引根级索引页0xF8B638000100 (1:3716856)

 DBCC TRACEON (3604)
 DBCC Page (9,1,3716856,3)

可以看到，索引页共有3行，分别指向了3个页面，ChlidPageId是指向数据页的页编号。

下一个page的数据

查看数据内容,即可查到数据的内容

 DBCC TRACEON (3604,2588)
 DBCC Page (9,1,3728528,3)

由于没有记录，找了个类似的页面

PAGE: (1:3716728)


BUFFER:


BUF @0x000002A9044D7A40

bpage = 0x000002A86D0F4000          bhash = 0x0000000000000000          bpageno = (1:3716728)
bdbid = 9                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 23417                       bstat = 0x9
blog = 0x2121215a                   bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0                        

PAGE HEADER:


Page @0x000002A86D0F4000

m_pageId = (1:3716728)              m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 31191 m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057596082061312                                
Metadata: PartitionId = 72057595966193664                                Metadata: IndexId = 1
Metadata: ObjectId = 277576027      m_prevPage = (0:0)                  m_nextPage = (1:3720432)
pminlen = 41                        m_slotCnt = 76                      m_freeCnt = 663
m_freeData = 7377                   m_reservedCnt = 0                   m_lsn = (26367:276841:40)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 1906398491             DB Frag ID = 1                      

Allocation Status

GAM (1:3578624) = ALLOCATED         SGAM (1:3578625) = NOT ALLOCATED    
PFS (1:3712392) = 0x40 ALLOCATED   0_PCT_FULL                            DIFF (1:3578630) = NOT CHANGED
ML (1:3578631) = NOT MIN_LOGGED     

Slot 0 Offset 0x60 Length 69

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 69                    
Memory Dump @0x000000E401B3A060

0000000000000000:   30002900 01000000 0a000000 2679ec00 61aa0000  0.).........&y..a...
0000000000000014:   01000000 d2000000 00cfffb8 0060aa00 00010000  .............`......
0000000000000028:   000e0000 0003003d 00410045 00e16dca 6c0d5429  .......=.A.E..m.l.T)
000000000000003C:   52303030 30303030 31                          R00000001

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

CategoryID = 1                      

Slot 0 Column 2 Offset 0x35 Length 8 Length (physical) 8

CategoryName = 淡泊名利                 

Slot 0 Column 3 Offset 0x3d Length 4 Length (physical) 4

ParentCategory = 0000               

Slot 0 Column 4 Offset 0x41 Length 4 Length (physical) 4

CategoryCode = 0001                 

Slot 0 Column 5 Offset 0x0 Length 0 Length (physical) 0

Img =                               

Slot 0 Column 6 Offset 0x0 Length 0 Length (physical) 0

Thumb =                             

Slot 0 Column 7 Offset 0x8 Length 4 Length (physical) 4

Sort = 10                           

Slot 0 Column 8 Offset 0xc Length 8 Length (physical) 8

CreateTime = 2019-06-03 14:20:58.367

Slot 0 Column 9 Offset 0x14 Length 4 Length (physical) 4

CreateBy = 1                        

Slot 0 Column 10 Offset 0x18 Length 1 (Bit position 0)

Status = 0                          

Slot 0 Column 11 Offset 0x18 Length 1 (Bit position 1)

IsShow = 1                          

Slot 0 Column 12 Offset 0x19 Length 4 Length (physical) 4

ServiceProviderID = 0               

Slot 0 Column 13 Offset 0x1d Length 8 Length (physical) 8

UpdateTime = 2019-06-02 11:13:33.703

Slot 0 Column 14 Offset 0x25 Length 4 Length (physical) 4

UpdateBy = 1                        

Slot 0 Offset 0x0 Length 0 Length (physical) 0


--===============KeyHashValue根据这个值，可以知道锁住了那一行数据======================


KeyHashValue = (8194443284a0)
Slot 1 Offset 0xa5 Length 73

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 73                    
Memory Dump @0x000000E401B3A0A5
..........

四、索引回溯

1、从索引中查询到所有所需字段

如果索引中包含了所需的全部字段，那么只要查询索引页即可

SELECT SaleOrderCode
FROM Order
WHERE SaleOrderCode='70843654756'

2、需要查询索引外的其他字段

SELECT *
FROM Order
WHERE SaleOrderCode='70843654756'