前篇 SQL Server 存储(1/8):理解数据页结构 我们提到,每条记录都有至少7 bytes的系统行开销,这7 bytes行开销到底是一个什么样的结构,本篇我们一起来看下。
数据记录结构示意图如下:
图中蓝色是数据记录部分,即系统行开销(>= 7 bytes),大小基于列数量。绿色部分取决于表结构定长/变长列的数据记录部分,是实际存放的数据,大小基于实际数据。
一、 详解系统行开销
1. 行头系统数据
1)第1字节(8位),用作状态位1,定义记录的属性:
- 第0位:版本信息,在SQL Server 2008里始终是0;
- 第1-3位:定义记录类型;
- 0 数据记录(data record)
- 1 转发记录(Forwarded record)
- 2 转发存根(a forwarding stub)
- 3 索引记录(Index record)
- 4 二进制堆碎片或行溢出数据(blob fragment or row overflow data)
- 5 鬼影索引记录(ghost index record)
- 6 鬼影数据记录(ghost data record)
- 7 鬼影版本记录(ghost version record)
- 第4位:是否存在空值位图(Null bitmap ),在SQL Server 2008里没有不为空的列也会有空值位图(Null bitmap );
- 第5位:表示是否存在变长列;
- 第6位:表示该列包含版本信息;
- 第7位:在SQL Server里未使用;
2)第2字节(8位),用作状态位2。只有1位用来表示这条记录是否为鬼影转发记录(ghost forwarded record)。
2. 由行头开始到定长列结尾长度
下2个字节用来表示从记录开始到定长字段结束的总长度,所有定长字段长度+4bytes系统数据(行头2Bytes和定长字段长度记录2Bytes)。例如如果表里没有定长列,这个列的值会是4。这和页头列pminlen显示的值是一样的。
3. 所有定长列字段值
下n个字节用来存储在表中的定长数据,n是在表中所有定长列的长度。如果表里的所有列都是变长列,这一部分就没有。
4. 表列数
下2个字节用来存储表里的列数。
5. 空值位图(Null_Bitmap)
下n个字节用作空值位图,每列对应一个bit,1表示对应列为空。n的值为:列数 / 8,取整。
6. 变长列个数
下2个字节用来存储表里变长列个数。
7. 每个变长字段在记录中的偏移量
下n个字节用来存储变长字段值在记录中的实际偏移量。每个变长列需要2字节,n的值为:变长列数 * 2 。
8. 变长字段值
最后n个字节用来存储所有变长列值,n的值为所有变长列的实际长度之和。
二、 案例分析
1. 定长列表分析
创建测试表,并插入记录
CREATE TABLE Customers
(
FirstName CHAR(50) NOT NULL,
LastName CHAR(50) NOT NULL,
Address CHAR(100) NOT NULL,
ZipCode CHAR(5) NOT NULL,
Rating INT NOT NULL,
ModifiedDate DATETIME NOT NULL,
);
INSERT INTO dbo.Customers
(
FirstName,
LastName,
Address,
ZipCode,
Rating,
ModifiedDate
)
VALUES
(
'Woody' , -- FirstName - char(50)
'Tu' , -- LastName - char(50)
'ZUOQIAO YOUXI TOWN LINHAI CITY' , -- Address - char(50)
'0000' , -- ZipCode - char(5)
1 , -- Rating - int
'2015-05-07 10:09:51' -- ModifiedDate - datetime
);
使用DBCC IND命令查看表对应页列表,可以看到数据页号为79
DBCC IND('InternalStorageFormat','Customers',-1)
使用DBCC PAGE命令查看页信息:
DBCC TRACEON(3604)
DBCC PAGE(InternalStorageFormat,1,79,3)
GO
- 在页头pminlen的值是221,包括定长列的总长217 bytes(50+50+100+5+4+8),2 bytes用作状态位(行头系统开销),2 byte 用作由行头开始到定长列结尾长度。
- 在记录槽提到的长度224,包括页头pminlen的值,1 byte用作空值位图(6/8 取整为1)和2 bytes 的字段个数。
2. 变长列表分析
再来看一个有变长列的表,创建表并插入数据后,查看表对应的页:
CREATE TABLE VariableLength(
Title CHAR(10) NOT NULL,
FirstName VARCHAR(100),
Lastname VARCHAR(100),
email VARCHAR(50),
dob date NOT NULL,
phone CHAR(10),
Countrycode CHAR(3),
Designation VARCHAR(100),
PersonalPreference VARCHAR(100)
);
INSERT INTO VariableLength VALUES ('Mr','Woody','Tu','smartgz@qq.com','2015-5-7','XXXXXXXXXX','Chn','DBA','Nothing Spl');
DBCC IND('InternalStorageFormat','VariableLength',-1)
我们看到数据页号为202,使用DBCC PAGE命令查看页信息:
DBCC TRACEON(3604)
DBCC PAGE('InternalStorageFormat',1,202,3)
pminlen值为30,包含:
- 1 byte 状态位1
- 1 byte 状态为2
- 2 bytes 存储行头开始到定长列结尾长度
- 26 bytes 所有定长列总长度(10+3+10+3:tittle,dob,phone,countrycode)
- Title CHAR(10) NOT NULL
- dob date NOT NULL
- phone CHAR(10)
- Countrycode CHAR(3)
可以用下列语句验证下定长列总长度:
SELECT DATALENGTH(Title) title,DATALENGTH(dob) dob,DATALENGTH(phone) phone,DATALENGTH(Countrycode) countrycode FROM VariableLength;
在槽0显示的81长度包含:
- 1 byte 状态位1
- 1 byte 状态为2
- 2 bytes 存储行头开始到定长列结尾长度
- 26 bytes 所有定长列总长度(10+3+10+3:tittle,dob,phone,countrycode)
- Title CHAR(10) NOT NULL
- dob date NOT NULL
- phone CHAR(10)
- Countrycode CHAR(3)
- 2 bytes 存储列个数
- 2 bytes 用作空值位图,字段个数/8后取整,即 9/8 得到2
- 2 bytes 存储变长列个数
- 10 bytes 用来存储每个变长列结束位置的偏移量 变长列个数 * 2,即 5 * 2 得到10,5个变长列包含:
- FirstName VARCHAR(100)
- Lastname VARCHAR(100)
- email VARCHAR(50)
- Designation VARCHAR(100)
- PersonalPreference VARCHAR(100)
- 35 bytes 用来存储所有变长列的实际长度,这个可以使用下列语句得到
SELECT DATALENGTH(FirstName)+DATALENGTH(Lastname)+DATALENGTH(email)+DATALENGTH(Designation)+DATALENGTH(PersonalPreference) FROM VariableLength
三、 总结
每条记录的系统行开销:行头系统数据(2 bytes)+由行头开始到定长列结尾长度(2 bytes)+列个数(2 bytes)+空值位图数据(取整(列个数/8) n bytes),即 2 bytes + 2 bytes + 2 bytes + 取整(列个数/8)。当列个数小于等于8时,系统行开销始终是7 bytes,往上没增加8列,增加1 bytes,即系统行开销始终大于等于7 bytes。
对于在SQL Server里数据记录的存储格式,希望你已经有了清晰的认识。