一個位元組造成的巨大性能差異——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，檢視到的情況如圖：

<a href="https://images.cnblogs.com/cnblogs_com/studyzy/WindowsLiveWriter/SQLServer_14892/image_2.png"></a>

當然，我們也可以使用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部分組成：頁頭、資料行和行偏移矩陣，具體結構如圖：

<a href="https://images.cnblogs.com/cnblogs_com/studyzy/WindowsLiveWriter/SQLServer_14892/image_4.png"></a>

頁頭儲存了頁的編号、上一頁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位元組

2位元組

具體定長資料位元組

列數/8個位元組

變長列個數*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、産品圖檔、産品描述這樣的表，通過外鍵限制的方式将大對象資料和長字元串資料放在另一個表中。

本文轉自深藍居部落格園部落格，原文連結：http://www.cnblogs.com/studyzy/archive/2008/11/27/1342003.html，如需轉載請自行聯系原作者