一個位元組造成的巨大效能差異——SQL Server儲存結構

今天同事問了我一個SQL的問題，關於SQL Server內部儲存結構的，我覺得挺有意思，所以寫下這篇部落格討論並歸納了一下。問題是這樣的：

首先我們建立兩張表，一張表的列長度是4039位元組，另一張表的長度是4040位元組，他們就只有一個位元組的差距，比如以下建立表的SQL：

CREATE TABLE tb4039(c1 INT IDENTITY,c2 char(4035) not null)
CREATE TABLE tb4040(c1 INT IDENTITY,c2 char(4036) not null)

由於INT類型是4個位元組，所以我們建立的tb4039表有4+4035=4039個位元組的長度，tb4040中的c2欄位比tb4039中的c2 欄位多了一個位元組，總長度是4040位元組，其他沒有區別了。接下來是向這兩個表中插入資料，比如插入100條資料，SQL語句是：

DECLARE @i INT
SET @i=1
WHILE @i<=100
BEGIN
    INSERT INTO tb4039 (c2) VALUES('test'+CONVERT(VARCHAR(5),@i));
    INSERT INTO tb4040 (c2) VALUES('test'+CONVERT(VARCHAR(5),@i));
    SET @i=@i+1
END

好，現在我們使用SSMS來查看一下這兩個表的空間佔用量，如果是SQL2005，那麼可以使用SSMS內建的報表查看，如果是SQL2008，那麼直接使用物件總管詳細資料介面進行查看（如果使用的是SQL2008而不知道怎麼查看錶空間使用量那麼請查看我以前寫的一篇部落格：SQL Server 2008新特性之SSMS增強）。我這裡使用的是SQL2008，查看到的情況

當然，我們也可以使用T-SQL來查詢系統檢視表，得出這兩個表的資料佔用的空間，查詢代碼為：

select OBJECT_NAME(i.object_id) AS TableName,data_pages*8 AS DataSize --這裡返回的是資料頁個數，1頁是8K，所以乘以8
FROM sys.indexes as i
JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.allocation_units as a ON a.container_id = p.partition_id
where i.object_id=OBJECT_ID('tb4039') OR i.object_id=OBJECT_ID('tb4040')

系統返回結果：

TableName    DataSize
tb4039           400
tb4040           800

和我們通過報表或者SSMS查看到的結果相同，兩個表只相差了一個位元組，可是一個佔用了400K的儲存空間，另一個卻佔用了800K的儲存空間，是另一個表的雙倍！！！

一個位元組的差距就造成了儲存空間成倍的增加，為什麼會這樣呢？這就要從SQL Server儲存結構講起。

------------------------------------------------華麗的分割線，進入主題-----------------------------------------------------------------

SQL Server最小的儲存單位是頁（Page），一個頁的大小是8K=8192位元組。一個資料頁是由3部分組成：頁頭、資料行和行位移矩陣，具體結構

頁頭儲存了頁的編號、上一頁ID、下一頁ID、可以位元組數等等關於該頁的基本資料。頁頭的大小是固定的96個位元組，所以剩下8192-96=8096個位元組用於儲存資料行和行位移矩陣。

行位移矩陣在頁的最後面，而且是倒序排列的，使用2個位元組來表示資料行在頁面內部的位移量，有1行資料則行位移矩陣的大小是2位元組，有2行資料則行位移矩陣的大小是4位元組，以此類推。

除了頁頭佔用的空間和行位移矩陣佔用的空間，中間剩下的空間就是給資料行使用的。假設我們要在一個頁中儲存2行資料，那麼這2行資料可以使用 8096-4=8092個位元組的空間，也就是說1行資料可以使用8092/2=4046個位元組的空間。這裡的4046個位元組並不是完全都用來儲存資料行，一個資料行中還存在其他的資訊用於表示該行資料，具體的結構是這樣的：

狀態位A	狀態位B	定長資料類型的長度	定長資料的內容	列數	NULL位元影像	變長列的個數	變長列的位移矩陣	變長列的資料
1位元組	1位元組	2位元組	具體定長資料位元組	2位元組	列數/8個位元組	2位元組	變長列個數*2個位元組	具體變長資料位元組

不管我們對錶的定義是多麼的簡單，一行資料除了資料自身佔用的空間外，至少還要佔用1+1+2+2+1=7個位元組。如果定義的資料列很多，或者裡面有變長資料列，那麼佔用的空間可能會更多。

現在回到我們前面講到的2個表tb4039和tb4040，要儲存tb4039中的一行資料需要1+1+2+4039+2+1=4046位元組，所以正好可以在一個頁中儲存2行資料。所以插入了100行資料，實際上是儲存在50個資料頁中，大小就是8K*50=400K。而對於tb4040表，要儲存一行資料需要4047個位元組，沒法在一個頁中儲存2行資料，所以一行資料就佔用一個資料頁，100行資料佔用了100個資料頁，大學就是 8K*100=800K。

--------------------------------------------做了一堆加減乘除，下面總結下--------------------------------------------

這裡只是舉了一個極端的例子，所以造成了一個位元組的偏差而使佔用的儲存空間翻倍，在實際應用中很少會出現這麼極端的情況，但是很有可能使一個頁儲存 5條資料的因為某個列多了1個2個位元組所以只能儲存4條資料。也許大家認為少存一條資料並沒有什麼，但是在資料量變的非常龐大以後一頁4條資料和一頁5條資料將會產生明顯的效能差異。使得一頁中存放更多的資料並不是為了節約儲存成本，現在的硬碟已經很便宜了很多伺服器都是幾百個G的硬碟，本來5G的資料現在變長了10G，相對幾百個G上T的硬碟來說又算得了什麼。

實際上我們要讓一個資料頁中存放更多行的資料主要是出於效能的考慮。SQL Server進行資料庫讀寫操作的基本單位是頁，如果一頁中存放了更多的資料，那麼對錶進行掃描和尋找時進行的IO操作將減少，畢竟IO操作是非常消耗時間影響效能的。假設tb4039中有100W條資料，那麼進行全表掃描就要讀取50W個資料頁，如果讀取10W個資料頁花費1秒鐘，那麼對錶tb4039 進行掃描需要花費5秒鐘時間，而如果是使用tb4040儲存這100W條資料，進行全表掃描則需要讀取100W個資料頁，總共花費10秒鐘時間。就一個位元組的差別，一個是5秒另一個是10秒，對效能的影響非常明顯。

為了提高資料庫查詢的效能，在表設計時可以遵循以下建議：

• 主鍵儘可能的短，能用tinyint的就不要用int，能用char(5)的就不要用成varchar(50)。
• 計算好表列的長度，能夠在一個頁中存放5條資料的，那就不要將欄位設定的太長使得一個頁中只能存放3條或者4條資料。
• 盡量將欄位設定為不允許為NULL，因為NULL值在儲存和資料處理時系統需要專門的處理，降低了效能。
• 能夠用固定長度的就不要用變長欄位，比如社會安全號碼就可以使用CHAR(18)，而不應該使用VARCHAR(18)。
• 不要在一個表中建立太多的列，如果一個實體的屬性太多時可以考慮進行垂直分割，將常用的欄位放在一個表，不常用的欄位放另外的表，這樣可以減小常用欄位表中資料列佔用的空間，使得一個資料頁中儲存更多的資料行。
• 不要將大對象、長字串和常用的欄位放在同一個表中。同樣還是出於效能上的考慮，比如有個產品表，裡面有產品ID、產品名字、產品售價、產品圖片、產品描述等欄位，那麼我們可以將產品ID、產品名字、產品售價這幾個常用的而且佔用空間小的列放在一個表，然後建立產品ID、產品圖片、產品描述這樣的表，通過外鍵約束的方式將大對象資料和長字串資料放在另一個表中。