上一篇文章中 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'