• 通过DBCC PAGE查看页信息验证聚集索引和非聚集索引节点信息

    前言

    在阅读之前可以参考之前的一篇博客：通过DBCC IND分析表组织和索引组织

    1.DBCC PAGE基础

    page是sql server中最小的IO单位，在数据库中如果我们只是查询一行记录，也会读取这一行所在的整个页信息。

    那么page里面是如何存储信息的呢？我们可以通过dbcc page解析页的相信信息。当我们知道page是如何存储数据以后，对于我们后面解析聚集索引和非聚集索引的叶子节点非常有帮助。因为聚集索引的叶子节点是data page，那么我们dbcc page聚集索引的叶子节点，得到的应该就是真实的数据。如果我们dbcc pag非聚集索引的叶子节点，得到的可能是聚集索引的键值，也可能得到的是具体file:page:slot这样的信息，这个叫做RID指向具体的数据行。如果使用过dbcc page，可能对slot不陌生。这时候我们也终于明白为什么说非聚集索引的叶子节点是行标示符的意思了。

    首先通过以下实验来解析一页存储一条记录的情况。

    USE TESTDB3;
GO
--1.创建表
CREATE TABLE Clustered_Dupes
(
    Col1 char(5)   NOT NULL, --5字节
    Col2 int     NOT NULL,   --4字节 
    Col3 char(3)   NULL,     --3字节
    Col4 char(6)   NOT NULL  --6字节
);
GO
--2.此时没有索引,索引查询结果indid=0,表示堆结构,keycnt=0,表示没有key
SELECT  [first],[indid],[keycnt],[name] FROM sysindexes WHERE id = object_id ('Clustered_Dupes');

--3.创建聚集索引
CREATE CLUSTERED INDEX Cl_dupes_col1 ON Clustered_Dupes(col1);

--4.此时有聚集索引了,所以indid=1,keycnt=2不明白
SELECT  [first],[indid],[keycnt],[name] FROM sysindexes WHERE id = object_id ('Clustered_Dupes');


--5.插入一行数据
INSERT Clustered_Dupes VALUES ('ABCDE', 123, null, 'CCCC');

--6.查看表的页信息
dbcc ind ( TESTDB3, Clustered_Dupes, -1)

--7.找到data page,并使用dbcc page查看data page 信息
DBCC TRACEON (3604);
GO
DBCC PAGE (TESTDB3,1,2206, 1);
--DBCC PAGE (TESTDB3,1,2206, 1) with tableresults;

    执行完第7不以后的结果如下图所示，我们将解析其中的主要信息：

    首先我们发现length=25，但是我们发现我们的四个列的字段长度加起来只有5+4+3+6=18个字节，为什么是25呢？之前写过一篇文章：SQL Server计算数据库中表、堆、聚集索引和非聚集索引的大小。通过这篇文章中提到的方法，我们可以算出来，row size=25，也就是这里的length。其实上图中的一行加起来也刚还是25字节。

    仔细观察可以发现，数据是以16进制形式存储的，所以每2位是一个字节，表示一个字符。我们已经将4列的信息标注出来了。

    上图说Col1中的"ABCDE"在页中的存储形式是“4142434445”，这是通过ASCII来存储，如下图所示，我们发现字符A在ASCII表中对应的hex的值是41。

    接下来是Col2中存储的123对应页中"7b000000"，这是因为123的16进制等于7b，这个我们可以使用calc计算得到。

    Col3中插入的是NULL，在page中以00000000来表示，这也可以理解为什么Col2中7b以后使用000000来填充。

    Col4中我们插入的是“CCCC”，那么在Page中应该是“43434343”，但是上图显示的是“434343432020”，查看ASCII码表我们发现20表示的是空格。而我们Col4的定义是：

    Col4 char(6)

    索引在Col4长度不到6的时候，用space，也就是20来填充page中的信息。而我们Col1因为长度为5，刚好又插入了5个字母，所以才没有出现填充。我们可以通过以下实验来验证。

    --8.再次插入一条记录
INSERT Clustered_Dupes VALUES ('ABCD', 123, null, '中国');

--再次查看,发现m_slotCnt由1变成2了,DATA中有Slot0跟Slot1.
DBCC PAGE (TESTDB3,1,2206, 1);

    查询结果如下：

    DATA:
Slot 0, Offset 0x79, Length 25, DumpStyle BYTE
Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 25
Memory Dump @0x000000000E18A079
0000000000000000:   10001600 41424344 207b0000 00000000 †....ABCD {...... 
0000000000000010:   d6d0b9fa 20200500 08†††††††††††††††††Öйú  ...    
        
Slot     1, Offset 0x60, Length 25, DumpStyle BYTE
Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 25
Memory Dump @0x000000000E18A060
0000000000000000:   10001600 41424344 457b0000 00000000 †....ABCDE{...... 
0000000000000010:   43434343 20200500 08†††††††††††††††††CCCC  ...

    从上面的查询结果，对于第一次执行的查询，我们发现：

    1. 原来的那条记录由slot0变成了slot1；
    2. 新插入Col1的值是“ABCD”，只有4个字节，所以在“41424344”后面使用了"20"也就是空格进行填充；
    3. 新插入的Col4是汉字“中国”，对应的是"d6d0b9fa"以及因为没有达到6字节而出现的2个空格填充“2020”；
    4. 查看汉字与16进制编码的对照有两种方法：1.下载6. 编码：GB2312汉字对应表，查看汉字与16进制的互换。2.使用edit plus，输入汉字，然后点击Hex Viewer查看汉字所对应的16进制编码。

     2.DBCC PAGE在索引节点上的引用

    下面我们将通过dbcc page来展示聚集索引的叶子节点。因为我们都说聚集索引的叶子节点就是data page，包含真实的数据，我们可以使用dbcc page来验证。

    2.1.dbcc page聚集索引的叶子节点

    首先我们执行如下实验：

    use TESTDB3
--1.创建表,有主键,sql server默认设置为聚集索引
CREATE TABLE Suppliers
(
  supplierid   INT          NOT NULL IDENTITY,
  companyname  CHAR(10) NOT NULL,
  address  CHAR(10) NOT NULL,
  CONSTRAINT PK_Suppliers PRIMARY KEY(supplierid)
);

--2.创建非聚集索引
CREATE NONCLUSTERED INDEX idx_nc_companyname ON dbo.Suppliers(companyname);

--3.插入一条记录
insert into Suppliers values('Microsoft','紫竹');

--4.查看页信息，结果不为null,有两条记录。发现IndexID=1表示聚集索引。
dbcc ind ( TESTDB3, [dbo.Suppliers], -1)

--5.查看数据页PageType=1的这个page的信息
DBCC PAGE (TESTDB3,1,2184, 1);

    上面的主要操作是创建一张含有三个字段的表，在id字段上有聚集索引，在companyname上有非聚集索引，而address字段上没有索引。然后插入一条记录，查看聚集索引的叶子节点，也就是index=0，pagetype=1的页。dbcc page查询结果如下：

    DATA:
Slot 0, Offset 0x60, Length 31, DumpStyle BYTE
Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 31
Memory Dump @0x00000000119EA060
0000000000000000:   10001c00 01000000 4d696372 6f736f66 †........Microsof 
0000000000000010:   7420d7cf d6f12020 20202020 030000††††t ×ÏÖñ      ...

    我们首先解析上面的DATA信息。Length=31不再解释。Microsoft我们可以通过查看ASCII得出他的16进制代码是"4D 69 63 72 6F 73 6F 66  74"我们在上面已经用相应的颜色标注，而主键supplierid的值为1，所对应的值为01000000。最后address的值是“紫竹”，对应上面的16进制代码是“d7cf d6f12020 20202020”。

    这说明：在聚集索引的叶子节点上，包含了一条记录的所有数据。

    2.2.dbcc page非聚集索引的叶子节点，表上有聚集索引

     接着上面的实验我们继续执行下面的命令：

    --查找出非聚集索引的叶子节点的位置
dbcc ind ( TESTDB3, [dbo.Suppliers], -1)

--5.查看非聚集索引叶子节点上的index page.pagetype=2,indexlevel=0.
DBCC PAGE (TESTDB3,1,2188, 1);

    执行DBCC PAGE的查询结果如下：

    Slot 0, Offset 0x60, Length 15, DumpStyle BYTE
Record Type = INDEX_RECORD           Record Attributes =                  Record Size = 15
Memory Dump @0x000000000EDBC060
0000000000000000:   064d6963 726f736f 66742001 000000††††.Microsoft ....

    如上查询结果所示，在非聚集索引叶子节点上，包含了非聚集索引的key(4d6963 726f736f 667420,对应companyname=Microsoft)，如果有聚集索引，那么还包含聚集索引的key(01,对应supplierid=1)。

    然后我们再插入一条记录看看非聚集索引叶子节点上的变化

    --7.再插入一条记录
insert into Suppliers values('Intel','紫竹');

--8.查看非聚集索引叶子节点
DBCC PAGE (TESTDB3,1,2188, 1);

    查询结果如下：

    Slot 0, Offset 0x6f, Length 15, DumpStyle BYTE
Record Type = INDEX_RECORD           Record Attributes =                  Record Size = 15
Memory Dump @0x000000000EDBC06F
0000000000000000:   06496e74 656c2020 20202002 000000††††.Intel     ....  

Slot 1, Offset 0x60, Length 15, DumpStyle BYTE
Record Type = INDEX_RECORD           Record Attributes =                  Record Size = 15
Memory Dump @0x000000000EDBC060
0000000000000000:   064d6963 726f736f 66742001 000000††††.Microsoft ....

    从上述查询结果我们可以得出结论

    1. 新插入的记录都会写入到page中的slot0位置，原先的记录往后移动
    2. 在有聚集索引的情况下，非聚集索引叶子节点记录的是非聚集索引键值与聚集索引的键值

    2.3.dbcc page非聚集索引的叶子节点，表上没有聚集索引

    本来想通过

    drop index PK_Suppliers on Suppliers

    来删除索引的，但是报错“An explicit DROP INDEX is not allowed on index 'Suppliers.PK_Suppliers'. It is being used for PRIMARY KEY constraint enforcement.”。这是因为聚集索引是由主键自动产生的，而不是我们手动创建的。我们重新创建表来做实验，执行如下命令：

    --实验4:----无聚集索引情况下，非聚集索引叶子节点的数据内容------------------
use TESTDB3
--1.创建表,堆结构
CREATE TABLE Suppliers
(
  supplierid   INT          NOT NULL,
  companyname  CHAR(10) NOT NULL,
  address  CHAR(10) NOT NULL,
);

--2.创建非聚集索引
CREATE NONCLUSTERED INDEX idx_nc_companyname ON dbo.Suppliers(companyname);

--3.插入两条记录
insert into Suppliers values(1,'Microsoft','紫竹');
insert into Suppliers values(2,'Intel','紫竹');

--4.查看页信息，发现有4个page，其中两个PageType=10，一个PageType=1，还有一个PageType=2
dbcc ind ( TESTDB3, [dbo.Suppliers], -1)

--5.查看PageType=1,index=0的page,这是data page
DBCC PAGE (TESTDB3,1,2184, 1);

    其查询结果如下：

    Slot 0, Offset 0x60, Length 31, DumpStyle BYTE
Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 31
Memory Dump @0x00000000119EA060
0000000000000000:   10001c00 01000000 4d696372 6f736f66 †........Microsof 
0000000000000010:   7420d7cf d6f12020 20202020 030000††††t ×ÏÖñ      ...  
    
Slot     1, Offset 0x7f, Length 31, DumpStyle BYTE
Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 31
Memory Dump @0x00000000119EA07F
0000000000000000:   10001c00 02000000 496e7465 6c202020 †........Intel    
0000000000000010:   2020d7cf d6f12020 20202020 030000††††  ×ÏÖñ      ...

    我们发现：

    1. 数据节点的内容与之前的一模一样，没有发生改变。也就是说data page不管有没有聚集索引，是没有变化的。
    2. Microsoft在slot0上，Intel在slot1上，这个我们后面会用到。

    然后我们再来看看在没有聚集索引以后，非聚集索引的叶子节点的内容，执行如下命令：

    --6.查看非聚集索引的叶子节点,其pagetype=2,indexlevel=0.
DBCC PAGE (TESTDB3,1,2188, 1);

    查询结果如下所示：

    Slot 0, Offset 0x73, Length 19, DumpStyle BYTE
Record Type = INDEX_RECORD           Record Attributes =                  Record Size = 19
Memory Dump @0x000000000AD8C073
0000000000000000:   06496e74 656c2020 20202088 08000001 †.Intel     ..... 
0000000000000010:   000100†††††††††††††††††††††††††††††††...              

Slot 1, Offset 0x60, Length 19, DumpStyle BYTE
Record Type = INDEX_RECORD           Record Attributes =                  Record Size = 19
Memory Dump @0x000000000AD8C060
0000000000000000:   064d6963 726f736f 66742088 08000001 †.Microsoft ..... 
0000000000000010:   000000†††††††††††††††††††††††††††††††...

    对比2.2中非聚集索引叶子节点index page的内容与现在index page中的内容。我们发现：

    1. 两者前面的数据内容是一样的，比如"064d6963 726f736f 667420"，其中红字部分对应的是"Microsoft"，
    2. 如果有聚集索引，我们发现其后面跟多是聚集索引的键值，现在没有聚集索引，其后面跟所的不再是聚集索引的键值，而是row identifier (RID，行标识符), 格式为"File#:Page#:Slot#"。那么"88 08000001 000100"对应的就是RID。可以参考之前的博客：Sql Server中的表组织和索引组织（聚集索引结构，非聚集索引结构，堆结构）
    3. 我们发现Intel的最后六位是000100，而Microsoft的最后六位是000000，假如这个是Slot的话，那么刚好跟我们之前提到的"Microsoft在slot0上，Intel在slot1上，这个我们后面会用到。"相一致。但是前面的"88 08000001"不会解析。
    4. 在<inside in sql server2005>第七章的Clustered Index Node Rows小结提到了关于file:page:slot的东西。前面提到了RID="88 08000001 000100"是正确的，不过正确的分割应该是RID="88080000:0100:0100"，对应的应该是RID="PAGE:FILE:SLOT"。还有一个需要注意的是高位存储的问题。比如RID="88080000:0100:0100"应该翻译成RID="0x00000888:0x0001:0x0001"="2184:1:1"，这刚好对应我们查找data page的出来的内容。

    2.4.DBCC PAGE聚集索引非叶子节点

    前面我们dbcc page的都是叶子节点上面的数据，现在我们对聚集索引非叶子节点执行dbcc page，查看其中内容。

    执行如下实验：

    --实验4:查看聚集索引非叶子节点--------------------------------------
use TESTDB3
--1.创建表,堆结构
CREATE TABLE Suppliers
(
  supplierid   INT          NOT NULL,
  companyname  CHAR(10) NOT NULL,
  address  CHAR(10) NOT NULL,
);

--2.创建聚集索引
CREATE CLUSTERED INDEX idx_nc_supplierid ON Suppliers(supplierid);

--3.插入1000条记录
SET NOCOUNT ON;
GO
DECLARE @i int;
SET @i = 1;
WHILE @i <= 1000 BEGIN
  INSERT INTO Suppliers
   SELECT @i, 'Microsoft', '紫竹';
  SET @i = @i + 1;
 END;
GO

--4.查看是否插入成功
select * from Suppliers;

--5.查看页信息,找出PageType=2,indexleve=1的聚集索引非叶子节点.
dbcc ind ( TESTDB3, [dbo.Suppliers], -1)

--6.查看PagePID=2188的页
DBCC PAGE (TESTDB3,1,2188, 3);--产看child page
DBCC PAGE (TESTDB3,1,2188, 1);
-------------------------------------------------------------------

    dbcc page的查询结果如下

    DATA:
Slot 0, Offset 0x60, Length 11, DumpStyle BYTE
Record Type = INDEX_RECORD           Record Attributes =                  Record Size = 11
Memory Dump @0x000000000AD8C060
0000000000000000:   06000000 00880800 000100†††††††††††††...........      
Slot 1, Offset 0x6b, Length 11, DumpStyle BYTE
Record Type = INDEX_RECORD           Record Attributes =                  Record Size = 11
Memory Dump @0x000000000AD8C06B
0000000000000000:   06f60000 008d0800 000100†††††††††††††.ö.........      
Slot 2, Offset 0x76, Length 11, DumpStyle BYTE
Record Type = INDEX_RECORD           Record Attributes =                  Record Size = 11
Memory Dump @0x000000000AD8C076
0000000000000000:   06eb0100 008e0800 000100†††††††††††††.ë...Ž.....      
Slot 3, Offset 0x81, Length 11, DumpStyle BYTE
Record Type = INDEX_RECORD           Record Attributes =                  Record Size = 11
Memory Dump @0x000000000AD8C081
0000000000000000:   06e00200 008f0800 000100†††††††††††††.à.........      
Slot 4, Offset 0x8c, Length 11, DumpStyle BYTE
Record Type = INDEX_RECORD           Record Attributes =                  Record Size = 11
Memory Dump @0x000000000AD8C08C
0000000000000000:   06d50300 00900800 000100†††††††††††††.Õ.........

    同样是在<inside in sql server2005>第七章的Clustered Index Node Rows小结，我找到如下内容:

    "When you examine index pages, you need to be aware that the first index key entry on each page is frequently either meaningless or empty. The down-page pointer is valid, but the data for the index key might not be a valid value. When SQL Server traverses the index, it starts looking for a value by comparing the search key with the second key value on the page. If the value being sought is less than the second entry on the page, SQL Server follows the page pointer indicated in the first index entry. In this example, the down-page pointer is at byte offsets 6 through 9, with a hex value of 0x5264. (The next two bytes are 0x0001 for the file ID.) In decimal, the page number for the first page at the next level down is 21092, which is the same value we saw earlier when looking at the output of DBCC IND."

    通过上述结果以及引用文字，我们可以得出结论：

    1. 在我们的例子中，index page中的内容是"06000000 00880800 000100"，那么他的第6-9字节表示下一页的PagePID，第10-11位表示下一页的PageFID。按照前面的只是，PagePID="880800 00"="0x00000888"=2184，PageFID="0100"=0x0001=1。我们可以通过DBCC PAGE (TESTDB3,1,2188, 3)来验证这一事实。
    2. 前面分析了6-9跟10-11这两段内容，接下来我们看一下第2-5这一段内容。正如引文中说的，sql server 遍历索引的时候，总是将search key与slot1上的key值进行比较，如果search key值小于slot1上的key，那么会继续从slot0指向的page去查找。我们查看从slot1到slot4上第2-5为上的数据如下所示。我们可以发现246 491 736 981刚好是等差数列，等差为245，而246减去supplierid的第一个值1也刚好是245。所以我们这里第2-5位的内容求出来的是主键键值。
    slot1 "f60000 00"=0x00f6=246
slot2 "eb0100 00"=0x01eb=491
slot3 "e00200 00"=0xf02e0=736
slot4 "d50300 00"=0x03d5=981
  • 相关阅读:
    mhWaveEdit 1.4.8
    FFmpeg — 屏幕录制器材
    GNOME 主题: Troll
    cGmail — 主动反省邮件
    最小化布置 Ubuntu
    GNOME Do — 疾速翻开法式和文件
    PyTone 一个控制台音乐播放器
    高恪守编辑器 VIM－把持篇(2)
    Cankiri：玲珑实用的屏幕录像机
    LiVES 0.9.6pre4
  • 原文地址：https://www.cnblogs.com/xwdreamer/p/2596984.html
Copyright © 2020-2023  润新知