深入理解Sqlserver檔案儲存體之頁和應用

標籤：

我們每天都在使用資料庫，我們部門使用最多的關聯式資料庫有Sqlserver，Oracle，有沒有想過這些資料庫是怎麼存放到作業系統的檔案中的？有時候為了能夠設計出最優的表結構，寫出高效能的Sqlserver指令碼，處理海量資料並發，我們必須解底層原理。由於個人興趣最近研究了下Sqlserver的檔案儲存體，由於水平有限，下面只講解Sqlserver的最小儲存單元-頁。

什麼是頁，區？

什麼會有一個頁的概念，我們知道對於作業系統來說，檔案可以認為是一個很大 的線性空間，如果按地址空間順序分配容量（也就是按段式儲存），則有可能會造成很多的外部片段，造成很多的容量很難再次使用，只有移動合并空間才能騰出更 多的空間。例如：如下表所有，如果我現在要申請1024B位元組的空間，顯然下面的兩個空間空間單個計算不夠，合起來卻是夠用的的，只能移動合并空間。

8KB	512B	12KB	512B	8KB
已指派空間	空閑	已指派空間	空閑	已指派空間

表1

為了能夠更好的利用磁碟空間，Sqlserver借鑒了作業系統的虛擬記憶體的概念，人為的將檔案劃分N個8KB的儲存空間，這樣每次分配時，都是按照8KB空間申請，就解決了外部片段的問題，也就是說Sqlserver 中資料存放區的基本單位，頁的大小為8KB，每頁的開頭是96位元組的標題用於儲存有關頁的儲存資訊，其中有頁碼、頁類型、頁的可用空間以及擁有該頁對象的配置單位ID。上述的例子分配就成為下表所示:這樣就解決了外部片段問題。

業內分配	8kB	12KB		8KB	xxKB
頁單元	8KB	8KB	8KB	8KB	空閑

表2

為什麼會有區的概念，已經有了頁的單位難道不夠嗎？主要是為了更好的管理這些空間，Sqlserver將每8個頁劃分為一個區（如下表所示）就像百元大鈔代表著100個10元人民幣一樣，出去買很多東西時，用百元大鈔比用很多1元錢要方面。

一個分區
頁1	頁2	頁3	頁4	頁5	頁6	頁7	頁8

表3

為了有個頁有更具體的認識，下表為頁頭的結構：

 

圖-1

行是怎麼在頁中儲存的？

那麼資料庫中的資料到底是以什麼樣的形式儲存在資料中的呢？Sqlserver是以行為單位儲存的資料，也就是說表中的每條資料（每行資料為一個塊）順序存放在頁中的，那麼怎麼找到行？也就是一行的開始地址和結束位址? Sqlserver在每頁的末尾以2個位元組為單位存放了每行的開始地址，這樣我們就可以定位到行的開始，通過下一條的開始位置能夠知道本條記錄的結束位置，這樣我們就可以取出這行資料了。

圖-2

，如果我想取第二條資料，那麼現將一頁資料都讀到記憶體中，然後從最後讀取位移為第3開始開始讀取2個位元組，怎麼可以找到行2的開始位置，同理可以讀取出行2的結束位置。

列是怎麼在頁中的儲存？

現在我們已經讀取到行了並且已經在記憶體裡了，接下來怎麼解析出一行中的所有列？也就是這些列是怎麼存放的？資料庫表中的列無非就兩種情況：定長列、變長列。

首先假設只有定長列，那麼很容易想到一樣中的每列的之順序存放就行了，因為是定長的，完全可以將每列的位移放到另外一個地方單獨儲存，如果要取某個特定的列，每個列的位置很容易定位:如下表所示:

2位元組	3位元組	6位元組	10位元組	3位元組	2位元組
1	23	55	A	C	D

表-4

如果要取紅色的資料，那麼它的

 開始位置=（行開始位置）+ 2位元組+3位元組+6位元組+10位元組。

 結束位置= 開始位置 + 3位元組。

其中每個列的長度完全可以用另一張表存放

列	1	2	3	4	5	6
長度（位元組）	2	3	6	10	3	2

表-5

具體行結構的詳細資料如下：

 

圖-3

假如設計的表結構為 :

Col1	Col2	Col3	Col4
Char(5)(not null)	Int (null)	Char(3)(null)	Char(6)(not null)

表-6

在資料庫中存放資料為:

Col1	Col2	Col3	Col4
‘ABCDE’	‘123’	‘null’	‘ccc ‘

表-7

則資料在資料庫檔案中資料以如下形式存在:

 

圖-4

 

如果其中有變長列呢，這個結構又是怎麼儲存的？有變長列最大的不同就是每個列的長度是不定的（同一列，每行長度都不一樣），也就是不能用另外一張表存放。那麼我們只能把列的長度放在行內了。這樣就解決了實際長度定位的問題，上面已經說過，sqlserver有一個行位移矩陣。

如果我們定義的表結構如下：

Col1	Col2	Col3	Col4	Col5
Char(2)(not null)	Varchar(250)(not null)	Varchar(5)(null)	Varchar(20)(not null)	Small int (null)

表-8

假如這行資料為：

Col1	Col2	Col3	Col4	Col5
‘AAA’	RELICATE(‘X’,250)	null	‘ABC’	123

表-9

則資料在資料庫中實際的存放形式為:

 

圖-5

結論：

1.資料庫列中盡量不用可空類型，當值為空白時，實際不佔用位置，並且也不能作為索引的索引值。導致where語句中含有 is null 或者 is not null 時只能進行全表掃描，並且可空類型也容易導致Null 參考異常。

2．在設計列時，只有列長度確定的才用定長，比如身份證。其他情況基本上應該用varchar邊長類型，不但節省空間的同時，一個頁存放的資料會變多。導致同樣的資料量讀取頁的次數變少，減少I/O，提高效能。

3.-1所示，聚簇索引不是按物理順序存放的，是按邏輯物理順序存放的（大多數人在這裡會有誤解。）

4.正常情況下不要使用varchar(max),因為這個列的資料肯定放不在一個頁裡，為瞭解決這個問題，sqlserver在列裡只存放了一個指標。真正的資料放在了其他多個頁裡。每讀取一行中的列都會至少多一次I/O，影響效能。

附註,參考資料：

(1) Microsoft SQL Server 2005技術內幕：儲存引擎(中文)

(2)微軟MSDN: http://msdn.microsoft.com/zh-cn/library/ms190969(v=sql.105).aspx

深入理解Sqlserver檔案儲存體之頁和應用 （轉）