SQL Server2008存储结构之堆表、行溢出

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介:

堆数据表是没有聚集索引的表。即数据行不按任何特殊的顺序存储,数据页也没有任何特殊的顺序。数据页不在链接列表内链接。 sys.system_internals_allocation_units 系统视图中的列 first_iam_page 指向管理特定分区中堆的分配空间的一系列 IAM 页的第一页。SQL Server 使用 IAM 页在堆中移动。堆内的数据页和行没有任何特定的顺序,也不链接在一起。数据页之间唯一的逻辑连接是记录在 IAM 页内的信息。

1
 

  那么堆表是如何存储数据的呢?出于简化的目的,我们先来构造不含任何索引的一张堆数据表,然后从简单到复杂逐步深入探讨。

--创建一张数据表,从系统表生成测试数据


DROP TABLE testheap


--创建一张2个固定长度字段,3个不定长字段的表,其中4个不为空,1个可为空


CREATE TABLE testheap


(


  ID         INT IDENTITY(1,1) NOT NULL,


  name       VARCHAR(20) NOT NULL,


  type       CHAR(100) NOT NULL,


  other      VARCHAR(50) NOT NULL,


  describle  VARCHAR(500)


)


--产生1000条随机数据,并插入表中


DECLARE @i INT 


SET @i=1


WHILE @i<=1000


BEGIN


  INSERT INTO testheap(name,type,other,describle)


  VALUES('name'+CAST(@i AS VARCHAR(3)),REPLICATE(@i%4,100),FLOOR(RAND()*10),NULL)


  SET @i=@i+1


END


SELECT * FROM testheap


--查询该表的IAM页面地址和首页地址


SELECT total_pages,used_pages,data_pages,


       --first_page,root_page,first_iam_page,


       testdb.dbo.f_get_page(first_page) first_page_address,


       testdb.dbo.f_get_page(root_page) root_address,


       testdb.dbo.f_get_page(first_iam_page) IAM_address


  FROM sys.system_internals_allocation_units


WHERE container_id IN (SELECT partition_id FROM sys.partitions


             WHERE object_id in (SELECT object_id  FROM sys.objects


                                              WHERE name IN ('testheap')))



  查询结果如下:

total_pages used_pages data_pages first_page_address root_address IAM_address
25 18 17 1:224 0:0 1:119

  即SQL Server为该表分配了总计25个页面,实际使用了18个页面,扣除1个IAM管理页面,实际数据页面为17个,IAM管理页面地址为第一个文件的第119页面,数据页面的第一个页面为第一个文件的第224页面。

  那么如何查看到该表的页面详细分配情况呢?

  首先通过dbcc page(testdb,1,119,3)可以粗略看到页面分配情况

1
 

  即SQL Server首先分配了8个混合区页面,其次因为该对象已经超过8页,SQL Server又分配了从第472页到第487页的页面,共计16个页面,然后包括本身的IAM页面,共计25个页面。

 

  其次SQL Server还提供了一个更为友好的命令以找到各个类型的页面分布和它们的所在的文件号和页号。

  DBCC IND({'dbname'|dbid},{'objectname'|objectID},

  {nonclustered indid|1|0|-1|-2}[,partition_number])

  {'dbname'|dbid}表示数据库名或者数据库ID

  {'objectname'|objectID}表示对象名或者对象ID

  {nonclustered indid|1|0|-1|-2}表示显示行内数据分页及指定对象的行内IAM分页信息

  1 :显示所有分页的信息,包括IAM分页,数据分页,所有存在的LOB分页和行溢出页,索引分页

  -1: 显示所有IAM、数据分页、及指定对象上全部索引的索引分页.

  -2: 显示指定对象的所有IAM分页

  nonclustered indid:显示所有的IAM、数据分页以及一个索引的索引分页信息。

  {partition_number}->可选,为了与中的DBCC IND命令向前兼容.它指定了一个特定分区号,如果不指定,显示所有分区的信息。

  以下是DBCC IND命令输出结果的字段描述:

字段名称 字段描述
PageFID 索引文件的ID
PageFID 索引文件的ID
IAMFID 管理该分页的IAM分页所在的文件ID
IAMFID 管理该分页的IAM分页的ID
ObjectID 对象ID
IndexID 索引ID,0 代表堆, 1 代表聚集索引, 2-250 代表非聚集索引
PartitionNumber 表或索引所在的分区号码
PartitionID 包含该分页的分区ID
iam_chain_type 该页所属分配单元类型;行内数据、行溢出数据或Lob数据
PageType 分页类型:1数据页面;2索引页面;3Lob_mixed_page;4Lob_tree_page;10IAM页面
IndexLevel 索引层级,0 代表叶级别分页 ;>0 代表非叶级别层次; NULL 代表IAM分页
NextPageFID 本层下一个分页所在的文件ID
NextPageFID 本层下一个分页ID
PrevPageFID 本层上一个分页所在的文件ID
PrevPageFID 本层上一个分页ID

   继续为了简化的目的,同时因为模拟的是小型数据表,所以可以忽略相关文件号和iam链类型、分区号(该表暂无行内迁移和lob字段),我们只需要看看各 个数据页之间是否有相互联系、各个页面的类型即可;所以我们构建了一张数据表用以存放dbcc ind命令输出的结果,并有选择性的选择我们想要的字段。

CREATE TABLE tablepage
(
  PageFID         TINYINT, 
  PagePID         
INT,   
  IAMFID          TINYINT, 
  IAMPID          
INT
  ObjectID        
INT,
  IndexID         TINYINT,
  PartitionNumber TINYINT,
  PartitionID     BIGINT,
  iam_chain_type  VARCHAR(
30),    
  PageType        TINYINT, 
  IndexLevel      TINYINT,
  NextPageFID     TINYINT,
  NextPagePID     
INT,
  PrevPageFID     TINYINT,
  PrevPagePID     
INT 
);
GO

TRUNCATE TABLE tablepage;
INSERT INTO tablepage EXEC (
'DBCC IND(testdb,testheap,1)');
SELECT 
  PagePID,IAMPID,ObjectID,IndexID,Pagetype,IndexLevel,
  NextPagePID,PrevPagePID
  FROM tablepage

最终结果如下:

2
 

  我们可以看到SQL Server为该表所使用的页面地址,索引ID、页面类型、索引级别、前后页的关系等等。

   Pagetype=10为IAM页面,Pagetype=1为数据页面,即17个数据页面,1个IAM页面,与 system_internals_allocation_units输出结果一致,每一个数据页面都对应该IAM页面地址,indexid=0表示为堆 表,indexlevel=null表示为IAM页面,indexlevel=0表示为叶子节点;而让我们感到有些失望的是每一个页面似乎除了有共同的 IAM管理页面之外,相互之间是缺乏联系的。 

  而且从dbcc ind运行的结果来看,每个页面好像也是不连续的,那么首先通过Internals Viewer插件让我们看一下IAM页的情况吧,前八页是断断续续的分散分布的,而后面的16页却是连续的,再回头看一下tablepage表也印证了这 个现象。既然页面与页面之间缺乏联系,那么对堆表数据的访问只能靠IAM页来管理和定位了。

3
 

  SQL Server数据页的结构大体包括三个部分,即标头、数据行和行偏移量。

3
 

  现在让我们正式进入数据页面去看一下数据页面的构造,让我们首先去访问一下该表的数据首页即第224个页面。

  Dbcc page(testdb,1,224,2)

  PAGE HEADER部分,即该页面的前96个字节。

m_pageId = (1:224) 当前页面号码
m_headerVersion = 1 版本号,始终为1
m_type = 1 当前页面类型,m_type=1表示数据页面
m_typeFlagBits = 0x4 数据页和索引页为4,其他页为0
m_level = 0 该页在索引页(B树)中的级数,0表示为叶子节点
m_flagBits = 0x8200 页面标志
m_objId (AllocUnitId.idObj) = 94  
m_indexId (AllocUnitId.idInd) = 256  
Metadata: AllocUnitId = 72057594044088320 存储单元的ID,sys.allocation_units.allocation_unit_id
Metadata: PartitionId = 72057594039107584 数据页所在的分区号,sys.partitions.partition_id
Metadata: IndexId = 0 对象的索引号,sys.objects.object_id&sys.indexes.index_id
Metadata: ObjectId = 133575514 该页面所属的对象的id,sys.objects.object_id
m_prevPage = (0:0) 该数据页的前一页面
m_nextPage = (0:0) 该数据页的后一页面
pminlen = 108 定长数据所占的字节数为108个字节
ID INT IDENTITY(1,1) NOT NULL,
type CHAR(100) NOT NULL,
共计104个字节,每个定长字段需要2个字节的管理字节
 
m_slotCnt = 62 页面中的数据的行数,每页62条记录
m_freeCnt = 293 页面中剩余的空间,还剩293字节的空间
m_freeData = 7775 从第一个字节到最后一个字节的空间字节数(包括96字节的文件头的长度)
m_reservedCnt = 0 活动事务释放的字节数
m_lsn = (67:272:3) 日志记录号
m_xactReserved = 0 最新加入到m_reservedCnt领域的字节数
m_xdesId = (0:0) 添加到m_reservedCnt的最近的事务id
m_ghostRecCnt = 0 幻影数据的行数
m_tornBits = 1213019927 页的校验位或者被由数据库页面保护形式决定分页保护位取代

  

  上在页的尾部还有个行偏移矩阵,记录了每条记录的起始位置,每条记录需要2个字节来记录该位置,所以62条记录共计124个维护字节,加上293个剩余空间和实际已使用的7775个字节,刚好8192个字节,即一页。

4
 

  从Offset table和page结构可以知道,第一条记录从第96个字节开始。

ID name Type other describle
1 name1 1111111111111111111111111111111111. 8 NULL

  如前文所说,关于数据的存储从第96个字节开始

4
 

  关于数据行的结构我们还可以采用稍微宏观一些的视角来查看。

4
 

  其中状态A为如下说明:

  bit0:版本信息,在SQL Server 2005/08总是为0

  bit1-3: 0=(primary record);1=(forwarded record);2=(forwarding stud);3=(index record);4=(溢出数据);5=(ghost索引记录);6=(ghost数据记录)

  bit4:表示存在NULL位图(在数据行里SQL2005/08总存在NULL位图)

  bit5:表示存在变长列

  bit6:未启用

  bit7:表示存在幽灵记录

  本例中30->00110000 它是一个行属性的位图 从高位存到地位(右边第一位是bit0),bit4为1即存在变长列的字段,因为在SQLServer2005/2008中总存在NULL位图,所以bit5也为1。

  状态位B在SQLServer2005//2008中未启用,所以为00

  记录定长部分的长度为2个字节,是所有定长字段的长度之和加4,该处为int类型4个字节,char(100)为100个字节,再加上4,所以为108,换算成16进制即6c。

  紧跟其后的为定长字段的内容,即ID字段的4个字节和TYPE字段的100个字节。

  固定长度的字段数据之后,是该表的总字段数,用两个字节表示,本表包括5个字段所以为05 00。

  NULL位图:f0->11110000 因为该表只有列 所以只需要看后面个,1表示该行的对应列为NULL或者该位图未使用。本表前4个字段不为空,第5个为空,第6-8未使用。

  接下来是行内存储数据的变长列的数目:0200->00000000 00000010=2 表示该行存储了列name和other字段的数据。

  第一变长列数据终止位置为:7a00->00000000 01111010=122=1+1+2+(4+100)+2+ceiling(5/8)+2+2+2+len(“name1”)

  第二变长列数据终止位置:7b00->00000000 01111011=123 实际上就是在前者的基础上加了第二个变长列的字段长度。

  1+1+2+(4+100)+2+ceiling(5/8)+2+2+2+len(“name1”)+len(“8”)

  第一列变长列的数据: 6e616d 6531换算成字符即'name1'

  第二列变长列的数据:38换算成字符即8  

  下面让我们将该记录的describle字段更新为非空值后,再看看该记录存储结构相应的变化。

UPDATE testheap  SET  describle = ' abc' WHERE id=1

   再次使用dbcc page(testdb,1,224,1)命令

5
 

  我们不难发现状态A,状态B,定长长度、定长内容和字段总数是没有发生任何变化的。

  NULL位图部分变成了e0即11100000,表示describle字段即第五个字段不为空了

  第一个和第二个变长列数据终止位置分别加了2个长度,这是因为当第三个变长列变更为非空后,自动添加了2个字节的第三个字段的维护字段

  第一个变长列数据终止位置从7a00变更为7c00

  第二个变长列数据终止位置从7b00变更为7d00

  新增加的第三个变长列终止位置为8000

  同时在第一、二列变长列的数据后面新增加了616263,即字符串”abc”

  还有一个最显著的区别就是该记录的偏移位置显然转到了尾部,即5F1E的位置;但很奇怪的是该记录原来的位置上还保留着原值,并没有删除掉。也就是说对于该记录而言,应该是先删除,然后又添加了一条新纪录,只是把指针指向了新的偏移地址而已。

  最后观察一下记录是如何删除的

DELETE FROM testheap WHERE ID IN ( 2 , 3 )

  当我们对比一下删除前后两条记录的信息,发现基本上原来的位置上数据没有发生任何变化,只是原来的slot1和slot2已经不存在了。即SQL Server认为该数据已经不存在了。

5
 

      行溢出页面

USE TESTDB
CREATE TABLE testOverFlow
(
  ID    
INT  IDENTITY( 1 , 1 ),
  NAME1 VARCHAR(
5000 ),
  NAME2 VARCHAR(
5000 )
)
INSERT INTO testOverFlow (NAME1,NAME2)
SELECT  REPLICATE( ' A',5000),REPLICATE('AB',2500)
UNION
SELECT  REPLICATE( ' B',5000),REPLICATE('BA',2500)
SELECT   *  FROM testOverFlow
SELECT  type_desc
       total_pages,used_pages,data_pages,
       testdb.dbo.f_get_page(first_page) first_page_address,
       testdb.dbo.f_get_page(root_page) root_address,
       testdb.dbo.f_get_page(first_iam_page) IAM_address
  FROM sys.system_internals_allocation_units
WHERE container_id IN (
SELECT  partition_id FROM sys.partitions 
               WHERE object_id in (
SELECT  object_id  FROM sys.objects
                                              WHERE name IN (
' testoverflow')))

DBCC TRACEON(
3604 )
DBCC PAGE(testdb,
1 , 54242 , 2 )   -- 行内数据
DBCC PAGE(testdb,
1 , 52343 , 2 )   -- 行迁移数据

-- 同时我们也可以通过dbcc ind获取所有数据页面地址,然后进行页面信息显示
TRUNCATE TABLE tablepage;
INSERT INTO tablepage EXEC (
' DBCC IND(testdb,testOverFlow,1)');
SELECT  
  PagePID,IAMPID,ObjectID,IndexID,Pagetype,IndexLevel,
  NextPagePID,PrevPagePID
  FROM tablepage

        在NAME2字段之前和普通的行记录信息是一致的,我们只从NAME2字段开始就可以了。NAME2字段在NAME1字段之后,保存了以下内容,即改列的溢出列类型、节点类型、数据库更新次数、字段长度、指向OVERFLOW页的指针。

0200 0000 0100 00009d75 0000 8813 0000 77cc0000 0100 0000000
溢出列类型 节点类型 Lob数据更新次数 ID 未知 字段长度 行溢出指针
RowOVerFlow 0 1 1973223424   5000 1:52343:0


         让我们再来看一下第52343页看一下行溢出页的数据情况,该页面首先是一个LOB类型的页面,然后主要包括该字段的长度、关联ID,和数据行;很显然行 内数据和溢出行数据的关联是通过一个行溢出指针和ID进行的;因此对某个数据查询而言,首先要找到该记录的信息,同时如果发生行溢出,还有根据该列的行溢 出指针和关联ID,才能找到整条记录。

1个字节 1个字节 2个字节 8个字节 4个字节 2个字节
08 00 9613 00009d75 00000000 0300
状态A 状态B 字段长度 ID unkown 类型
即包含行溢出   5014(同变长字段) 1973223424 未知 lob数据行

 

  LOB页面

  从SQL Server 2005版本以后中,新增加了大值数据类型varchar(max)、nvarchar(max)、varbinary(max)。大值数据类型最多可以存储2^30-1个字节的数据。

  从行为上来讲这几个数据类型和之前的数据类型 varchar、nvarchar 和 varbinary 相同。

  按照微软的说法是用这个数据类型来代替之前的text、ntext 和 image 数据类型,它们之间的对应关系为:

  varchar(max)-------text;

  nvarchar(max)-----ntext;

  varbinary(max)----image

  对大值数据类型的操作更类似于之前的varchar和varbinary之后,因此用法上也比之前的text和image比灵活和便宜。同时触发器也可以直接引用大值数据类型;而之前的text和image是不行的。

  因此varchar(max)与varchar(n)和text有着千丝万缕的联系。对于varbinary(max)也一样。

  因为之前我们已经观察过varchar(n)的行为,那么让我们看看这个新的varchar(max)与varchar(n)、text到底有什么不同。

CREATE TABLE testVARCHARMAX
(
  ID     
INT IDENTITY(1,1),
  name   VARCHAR(
20),
  remark VARCHAR(MAX)
)
CREATE TABLE testTEXT
(
  ID     
INT IDENTITY(1,1),
  name   VARCHAR(
20),
  remark TEXT
)

INSERT INTO testVARCHARMAX (name,remark)
SELECT REPLICATE('A',20),REPLICATE('AB',2500)
UNION
SELECT REPLICATE('B',20),REPLICATE('BA',2500)

INSERT INTO testTEXT (name,remark)
SELECT REPLICATE('A',20),REPLICATE('AB',2500)
UNION
SELECT REPLICATE('B',20),REPLICATE('BA',2500)

SELECT c.name,a.type_desc
       total_pages,used_pages,data_pages,
       testdb.dbo.f_get_page(first_page) first_page_address,
       testdb.dbo.f_get_page(root_page) root_address,
       testdb.dbo.f_get_page(first_iam_page) IAM_address
  FROM sys.system_internals_allocation_units a,sys.partitions b,sys.objects c
WHERE a.container_id
=b.partition_id and b.object_id=c.object_id
   
AND c.name in ('testVARCHARMAX','testTEXT')

 

  运行结果如下:

LOB页面
 

   我们很容易发现两者的共同之处,就是两个表都包括LOB_DATA数据类型的分配单元,但是testVARCHARMAX表的LOB_DATA并没有分 配页面,而testTEXT表却分配了3个页面;同时testVARCHARMAX表比testTEXT表多了一个数据页面,这是怎么回事呢?

  让我们首先看看testVARCHARMAX表的第217个数据页面

LOB页面
 

  让我们通过Internals Viewer插件看一下对该记录的解读

LOB页面
 

  与之前的堆表的介绍相比,基本上我们可以看到与varchar(n)的存储结构式完全一致的,在此就不多做叙述了。

  那么testTEXT表为什么会使用到LOB类型页面呢?我们使用dbcc page命令查看一下。

  运行dbcc page(testDB,1,222),我们从第96个字节开始阅读。

LOB页面
 

  从这个角度,我们看到222页面类似于前面所讲到的行溢出页面,即在222页面保留了一个指向行溢出页面的指针

  运行dbcc page(testDB,1,220,2),我们从第96个字节开始阅读。

LOB页面
 

  实际上我们从name字段内容之后阅读就可以了,即0000d1 07000000 00dc0000 00010001 00

LOB页面
 

  是不是有点像缩略版的行溢出信息?

  既然有行溢出指针,必然有行溢出页面,那我们再看看行溢出页面的数据页,即220页面。实际上我们用dbcc page(testdb,1,220,3)阅读该页的信息更简明一些。

LOB页面
 

  很明显slot 0记录了第一条记录remark字段的长度、数据类型和内容。

  Slot 1,slot 2分别为两个指针,记录了remark字段的偏移地址和相应的文件号、页面和槽号

  这个与之前的行溢出页面是有所不同的。

 






本文转自baoqiangwang51CTO博客,原文链接:http://blog.51cto.com/baoqiangwang/414197,如需转载请自行联系原作者

相关实践学习
使用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
相关文章
|
6月前
|
存储 SQL Web App开发
SQL实践篇(一):使用WebSQL在H5中存储一个本地数据库
SQL实践篇(一):使用WebSQL在H5中存储一个本地数据库
143 2
|
6月前
|
SQL 存储 数据库
SQL实践篇(二):为什么微信用SQLite存储聊天记录
SQL实践篇(二):为什么微信用SQLite存储聊天记录
362 1
|
SQL Java BI
JSP超市销售管理统myeclipse开发sql数据库BS模式java编程网页结构
JSP 超市销售管理系统是一套完善的web设计系统,对理解JSP java编程开发语言有帮助,系统具有完整的源代码和数据库,系统主要采用B/S模式开发,开发环境为TOMCAT7.0,Myeclipse8.5开发,数据库为SQLSERVER2008,使用java语言开发
43 0
|
3月前
|
SQL 安全 Java
访问者模式问题之在上面的 SQL 结构定义中, sealed 接口的作用如何理解
访问者模式问题之在上面的 SQL 结构定义中, sealed 接口的作用如何理解
|
4月前
|
SQL 关系型数据库 数据处理
SQL结构
【7月更文挑战第28天】SQL结构
23 4
|
4月前
|
存储 SQL 数据库
MySQL设计规约问题之为何推荐用DECIMAL代替FLOAT和DOUBLE来存储精确浮点数
MySQL设计规约问题之为何推荐用DECIMAL代替FLOAT和DOUBLE来存储精确浮点数
|
5月前
|
SQL 关系型数据库 MySQL
mysqldiff - Golang 针对 MySQL 数据库表结构的差异 SQL 工具
Golang 针对 MySQL 数据库表结构的差异 SQL 工具。https://github.com/camry/mysqldiff
92 7
|
5月前
|
SQL 关系型数据库 分布式数据库
PolarDB产品使用问题之如何查看SQL语句使用的是行索引还是列索引
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
5月前
|
存储 分布式计算 大数据
MaxCompute产品使用合集之在sql里调用自定义的udf时,设置一次同时处理的数据行数,是并行执行还是串行执行的
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
5月前
|
SQL 存储 关系型数据库
PolarDB产品使用合集之有的sql里面有自定义存储函数 如果想走列存有什么优化建议吗
PolarDB是阿里云推出的一种云原生数据库服务,专为云设计,提供兼容MySQL、PostgreSQL的高性能、低成本、弹性可扩展的数据库解决方案,可以有效地管理和优化PolarDB实例,确保数据库服务的稳定、高效运行。以下是使用PolarDB产品的一些建议和最佳实践合集。
333 0