[SqlServer] 理解数据库中的数据页结构

2022-06-13 02:20:48

这篇文章，我将会带你深入分析数据库中数据页（Page）的结构。通过这篇文章的学习，你将掌握以下知识点：

1. 查看一个表/索引占用了多少了页。

2. 查看某一页中存储了什么的数据。

3. 验证在数据库中用 GUID类型时用 newid() 生成的数据作为聚集索引时的缺陷。

首先需要清楚页(Page) 和盘区(Extent) 的概念。页是SQL Server中数据存储的基本单元，每一页的大小都是8K。而盘区是一组页的集合，每一个盘区都是由8个相邻的页组合而成的。

上面的这张图片引用自微软官方文档，它展示了页的基本结构。

一个盘曲是8个页的集合，所以每一个盘曲的大小就是64K。1M的数据就包含16个盘曲。盘曲分为两种：

1. 统一区（Uniform）：由单个对象所有。区中的所有 8 页只能由所属对象使用。

2. 混合区（Mixed）：最多可由八个对象共享。区中八页的每页可由不同的对象所有。

除此之外，还需要了解一个概念，就是IAM页，它的全称是Index Allocation Map Page。IAM是对盘曲（Extent）的管理，每个IAM最大为4G。当数据超过4G时，或者IAM页中的 Extent 存储跨文件时，就会形成IAM链。

可以通过 sys.system_internals_allocation_units 来查看一个分配单元（allocation unit）的第一个IAM页地址。

IAM链的逻辑概念图：

上面只是简单地介绍了一下页，区，和分配单元的基本概念，更多信息，请查看 Pages and Extents Architecture Guide.

有了上面的基本概念后，接下来进行实际案例分析。

首先创建一个测试的数据库，并且插入一些测试数据。

CREATE DATABASE TEST
GO
USE TEST
CREATE TABLE DBO.EMPLOYEE
(
    EMPLOYEEID INT IDENTITY(1,1),
    FIRSTNAME VARCHAR(50) NOT NULL,
    LASTNAME VARCHAR(50) NOT NULL,
    DATE_HIRED DATETIME NOT NULL,
    IS_ACTIVE BIT NOT NULL DEFAULT 1,
    CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPLOYEEID),
    CONSTRAINT UQ_EMPLOYEE_LASTNAME UNIQUE (LASTNAME, FIRSTNAME)
)

GO
INSERT INTO DBO.EMPLOYEE (FIRSTNAME,LASTNAME,DATE_HIRED)
SELECT ‘George‘, ‘Washington‘, ‘1999-03-15‘
GO
INSERT INTO DBO.EMPLOYEE (FIRSTNAME,LASTNAME,DATE_HIRED)
SELECT ‘Benjamin‘, ‘Franklin‘, ‘2001-07-05‘
GO
INSERT INTO DBO.EMPLOYEE (FIRSTNAME,LASTNAME,DATE_HIRED)
SELECT ‘Thomas‘, ‘Jefferson‘, ‘2002-11-10‘
GO

现在，上面的表和索引已经成功创建了，并且SQL Server将这些数据以页的形式存起来了。我们可以通过DBCC IND命令来罗列出这些信息。

DBCC IND语法：

DBCC IND
(
[‘database name‘|database id], -- the database to use
table name, -- the table name to list results
index id, -- an index_id from sys.indexes; -1 shows all indexes and IAMs, -2 just show IAMs
)

接下来，让我们来看看 EMPLOYEE表的页信息：

-- List data and index pages allocated to the EMPLOYEE table
DBCC IND(‘Test‘,EMPLOYEE,-1)
GO

输出结果：

字段解释，PageFID：文件编号。PagePID：文件里页的编号。IAMFID：IAM页所在文件的编号。IAMPID：IAM页在文件里的编号。ObjectID：对象编号，可以由OBJECT_NAME获得其名称。IndexID：是sys.indexes中的的index_id值，1是聚集索引，2是非聚集索引。PartitionNumber：分区数。PartitionID：分区编号。iam_chain_type：IAM链类型，IN_ROW DATA 表示用于存储堆分区或索引分区，每个堆和索引的分区都有IN_ROW DATA的分配单元。Page Type：页类型，1是数据页，2是索引页，10是IAM页。IndexLevel：表示页所在树中的层级，0表示叶子节点。NextPageFID：下一个文件的编号。NextPagePID：下一个页编号。PrevPageFID：前一个文件的编号。PrevPagePID：前一个页编号。

有了这些信息后，我们进行进一步的分析。上面的EMPLOYEE表，有一个聚集索引为PK_EMPLOYEE，所以它的index_id就为1，并且PageType也应该为1（因为聚集索引就是实际存储数据的顺序）。因此我们可以锁定为上面的第2条数据，就可以得出PageFID和PagePID的值，有了这两个值后，我们就可以深入到页里面去观察了。使用DBCC PAGE命令，可以清楚地观察到页里面到底存了什么数据。

-- TRACEON(3604) 表示将结果输出到控制台
-- 1 是 PageFID
-- 368 是 PagePID
-- 3 表示输出Header和Data信息
DBCC TRACEON(3604)
DBCC PAGE(‘Test‘,1,368,3) WITH TABLERESULTS
GO

输出结果：

通过上面的结果图可以看出，数据是按照聚集索引的顺序存储的(EMPLOYEEID)。每一条数据都对应一个slot，slot从0开始，每次增加1，slot 0, slot 1, slot 2 ...... slot n。Field和Value字段，清楚地展示了我们所存储数据。每次的偏移（Offset）都是上次的 Offset 加上上一个字段的长度。

EMPLOYEE表除了聚集索引，还有一个非聚集索引（UQ_EMPLOYEE_LASTNAME）。由于非聚集索引的index_id的值为2，并且PageType也应该为2，所以我们知道它的PagePID为1， PagePID为400，接下来看看页里的详细信息：

-- TRACEON(3604) 表示将结果输出到控制台
-- 1 是 PageFID
-- 400 是 PagePID
-- 3 表示输出Header和Data信息
DBCC TRACEON(3604)
DBCC PAGE(‘Test‘,1,400,3) WITH TABLERESULTS
GO

输出结果：

滑倒最下面，可以看到一张更清楚的索引逻辑表。

从这个表中可以清楚地看到非聚集索引是按照逻辑存储的。并且每条数据都又一个EMPLOYEEID，也就是主键。换句话说，在有聚集索引的表中，非聚集索引是通过主键和原始数据关联。这一点和堆表（heap table, 没有聚集索引的表）不一样。

上面观察了聚集索引和非聚集索引的页信息，除了这两个，还有一个是IAM页的信息，这里笔者不做过多描述。有兴趣的朋友，可以自己打印出来看看。打印方法和上面的一致。接下来我们再来看看堆表(heap table)中的索引页是如何存储的。

alter table EMPLOYEE drop constraint PK_EMPLOYEE
GO
ALTER TABLE DBO.EMPLOYEE ADD CONSTRAINT PK_EMPLOYEE PRIMARY KEY NONCLUSTERED (EMPLOYEEID)
GO
DBCC IND(‘Test‘,EMPLOYEE,-1)

DBCC PAGE(‘Test‘,1,440,3)

输出结果：

我们可以看出堆表中的非聚集索引都有一个HEAP RID，它指向了实际的数据源。RID值的格式为 FileID:PageID:SlotID 组成，移步Heaps(Tables Without Clustered Indexes)获取详细信息。

通过上面的学习你已经知道表的数据页的存储结构了，然后，笔者解决一下最开始提出的问题。

1. 查看一个表/索引占用了多少了页 ?

可以通过命令DBCC IND输出所有的页信息，然后再通过NextPagePID来得出某一个索引的全部页。

2. 查看某一页中存储了什么的数据 ?

可以通过命令DBCC PAGE某一个页里存储的数据详情。

3. 验证在数据库中用 GUID类型时用 newid() 生成的数据作为聚集索引时的缺陷?

通常情况，将newid()作为聚集索引是非常不好的设计，使用如下的测试案例来评测一下将newid()作为聚集索引时的存储缺点。

USE TEST
CREATE TABLE DBO.EMPLOYEE
(
    EMPLOYEEID [uniqueidentifier] not null default newid(),
    FIRSTNAME VARCHAR(50) NOT NULL,
    LASTNAME VARCHAR(50) NOT NULL,
    DATE_HIRED DATETIME NOT NULL,
    CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPLOYEEID)
)
INSERT INTO DBO.EMPLOYEE (FIRSTNAME,LASTNAME,DATE_HIRED)
SELECT ‘George‘, ‘Washington‘, ‘1999-03-15‘
GO
INSERT INTO DBO.EMPLOYEE (FIRSTNAME,LASTNAME,DATE_HIRED)
SELECT ‘Benjamin‘, ‘Franklin‘, ‘2001-07-05‘
GO
INSERT INTO DBO.EMPLOYEE (FIRSTNAME,LASTNAME,DATE_HIRED)
SELECT ‘Thomas‘, ‘Jefferson‘, ‘2002-11-10‘

然后查看一下内存页的数据存储情况

DBCC IND(‘Test‘,EMPLOYEE,-1)
DBCC TRACEON(3604)
DBCC PAGE(‘Test‘,1,456,3) WITH TABLERESULTS

输出结果：

你会发现实际数据的存储顺序和插入数据的顺序不一致，也就是说在SQL Server在插入新数据时，可能会移动其它的数据（因为newid()每次生成的数据都是随机的），插入新数据时候，移动其它的数据无疑是一种额外的消耗，在大数据量的表中，缺陷尤其明显。

怎么解决这个问题呢？ 有两个方法，第一是不用uniqueidentifier作为主键类型，第二种是使用这里 NEWSEQUENTIALID() 替换 NEWID() 。NEWSEQUENTIALID()每次生成的值都会比它以前生成的值大。

感谢读者耐心地阅读完本文，上面提到的 DBCC IND 和 DBCC PAGE命令，微软官方并没有提供相应的文档。未来，这些命令的功能可能会改变或是移除。目前笔者的数据库是2016的版本。本文参考了Armando Prato的Using DBCC PAGE to Examine SQL Server Table and Index Data文章，有兴趣的朋友，可以移步Armando Prato的博客查看更多内容。

[SqlServer] 理解数据库中的数据页结构

码农公寓

相关文章