【SqlServer】 理解資料庫中的資料頁結構

HDWK發表於2021-07-30

這篇文章,我將會帶你深入分析資料庫中 資料頁(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 DA他的分配單元。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的部落格檢視更多內容。

 

相關文章