AI 助理
备案控制台
开发者社区 数据库 文章 正文

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

2017-12-26 876
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS SQL Server Serverless，2-4RCU 50GB 3个月
推荐场景：
云数据库 RDS SQL Server，基础系列 2核4GB
推荐场景：
SQL Server性能诊断案例分析
云解析 DNS，旗舰版 1个月
简介：

前言

在阅读之前可以参考之前的一篇博客：通过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

 

 

 本文转自xwdreamer博客园博客，原文链接：http://www.cnblogs.com/xwdreamer/archive/2012/07/18/2596984.html，如需转载请自行联系原作者

文章标签：
云数据库 RDS SQL Server 版
云解析DNS
索引
存储
SQL
Go
数据库
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中，使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库，该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品：云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能，强力支撑各种企业应用，同时也包含了微软的License费用，减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
嗯哼9925
目录
相关文章
roysatm
|
存储 自然语言处理 前端开发
领域驱动设计(DDD)-基础思想
一、序言     领域驱动设计是一种解决业务复杂性的设计思想，不是一种标准规则的解决方法。在领域驱动设计理念上，各路大侠的观点也是各有不同，能力有限、欢迎留言讨论。 二、领域驱动设计 DDD是什么 wiki释义：     领域驱动设计（英语：Domain-driven design，缩写 DDD）是一种通过将实现连接到持续进化的模型[1]来满足复杂
roysatm
7538 0
绿水长流*z
|
1月前
|
存储 缓存 关系型数据库
MySQL事务日志-Redo Log工作原理分析
事务的隔离性和原子性分别通过锁和事务日志实现，而持久性则依赖于事务日志中的`Redo Log`。在MySQL中，`Redo Log`确保已提交事务的数据能持久保存，即使系统崩溃也能通过重做日志恢复数据。其工作原理是记录数据在内存中的更改，待事务提交时写入磁盘。此外，`Redo Log`采用简单的物理日志格式和高效的顺序IO，确保快速提交。通过不同的落盘策略，可在性能和安全性之间做出权衡。
绿水长流*z
1645 14
xaubllxwtvaqiu
|
存储 SQL 关系型数据库
InnoDB数据页什么时候合并(1)
InnoDB数据页什么时候合并
xaubllxwtvaqiu
111 0
程序媛地瓜
|
数据库
深入浅出理解数据库s锁和x锁
深入浅出理解数据库s锁和x锁
程序媛地瓜
706 1
pzf3zdekdh6es
|
数据库 C# 数据安全/隐私保护
分享 | C#编写的电影售票系统（附源码+数据库）
分享 | C#编写的电影售票系统（附源码+数据库）
pzf3zdekdh6es
1152 0
分享 | C#编写的电影售票系统（附源码+数据库）
xaubllxwtvaqiu
|
SQL 监控 关系型数据库
InnoDB数据页什么时候合并(2)
InnoDB数据页什么时候合并
xaubllxwtvaqiu
83 0
i5pzoh6jbghsc
|
Kubernetes Docker 索引
k8s+log-pilot日志收集
log-pilot可以从dockerlog-pilot主机收集日志并将它们发送到您的集中式日志系统。
i5pzoh6jbghsc
524 0
游客o6nnulqaeqex2
|
Python
解决jupyter notebook 画图不显示
解决jupyter notebook 画图不显示
游客o6nnulqaeqex2
529 0
王振威
|
测试技术 Linux 虚拟化
Docker与虚拟机的区别
概要 Docker是近年来新兴的虚拟化工具，它可以和虚拟机一样实现资源和系统环境的隔离。本文将主要根据IBM发表的研究报告，论述docker与传统虚拟化方式的不同之处，并比较物理机、docker容器、虚拟机三者的性能差异及差异产生的原理。
王振威
2378 1
游客2dnswgtrgjucq
|
Java
Error:(3, 29) java: 程序包org.junit.jupiter.api不存在
Error:(3, 29) java: 程序包org.junit.jupiter.api不存在
游客2dnswgtrgjucq
260 0
Error:(3, 29) java: 程序包org.junit.jupiter.api不存在