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

HDWK發表於2021-07-30
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 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的部落格檢視更多內容。

 

相關文章