Take a look at David Blog

【SqlServer】 理解数据库中的数据页结构

这篇文章,我将会带你深入分析数据库中 数据页(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的博客查看更多内容。

 

posted @ 2021-07-30 13:11  HDWK  阅读(317)  评论(2编辑  收藏  举报