一個字節(jié)對SQL SERVER的性能影響

Jason（徐子） 2010-09-17

展開全文

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

首先我們創(chuàng)建兩張表，一張表的列長度是4039字節(jié)，另一張表的長度是4040字節(jié)，他們就只有一個字節(jié)的差距，比如以下創(chuàng)建表的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個字節(jié)，所以我們創(chuàng)建的tb4039表有4+4035=4039個字節(jié)的長度，tb4040中的c2字段比tb4039中的c2字段多了一個字節(jié)，總長度是4040字節(jié)，其他沒有區(qū)別了。接下來是向這兩個表中插入數(shù)據(jù)，比如插入100條數(shù)據(jù)，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

好，現(xiàn)在我們使用SSMS來查看一下這兩個表的空間占用量，如果是SQL2005，那么可以使用SSMS自帶的報表查看，如果是SQL2008，那么直接使用對象資源管理器詳細信息界面進行查看（如果使用的是SQL2008而不知道怎么查看表空間使用量那么請查看我以前寫的一篇博客：SQL Server 2008新特性之SSMS增強）。我這里使用的是SQL2008，查看到的情況如圖：

當然，我們也可以使用T-SQL來查詢系統(tǒng)視圖，得出這兩個表的數(shù)據(jù)占用的空間，查詢代碼為：

select OBJECT_NAME(i.object_id) AS TableName,data_pages*8 AS DataSize --這里返回的是數(shù)據(jù)頁個數(shù)，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')

系統(tǒng)返回結果：

TableName    DataSize
tb4039           400
tb4040           800

和我們通過報表或者SSMS查看到的結果相同，兩個表只相差了一個字節(jié)，可是一個占用了400K的存儲空間，另一個卻占用了800K的存儲空間，是另一個表的雙倍?。。?/div>

一個字節(jié)的差距就造成了存儲空間成倍的增加，為什么會這樣呢？這就要從SQL Server存儲結構講起。

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

SQL Server最小的存儲單位是頁（Page），一個頁的大小是8K=8192字節(jié)。一個數(shù)據(jù)頁是由3部分組成：頁頭、數(shù)據(jù)行和行偏移矩陣，具體結構如圖：

頁頭保存了頁的編號、上一頁ID、下一頁ID、可以字節(jié)數(shù)等等關于該頁的基本信息。頁頭的大小是固定的96個字節(jié)，所以剩下8192-96=8096個字節(jié)用于存儲數(shù)據(jù)行和行偏移矩陣。

行偏移矩陣在頁的最后面，而且是倒序排列的，使用2個字節(jié)來表示數(shù)據(jù)行在頁面內部的偏移量，有1行數(shù)據(jù)則行偏移矩陣的大小是2字節(jié)，有2行數(shù)據(jù)則行偏移矩陣的大小是4字節(jié)，以此類推。

除了頁頭占用的空間和行偏移矩陣占用的空間，中間剩下的空間就是給數(shù)據(jù)行使用的。假設我們要在一個頁中保存2行數(shù)據(jù)，那么這2行數(shù)據(jù)可以使用8096-4=8092個字節(jié)的空間，也就是說1行數(shù)據(jù)可以使用8092/2=4046個字節(jié)的空間。這里的4046個字節(jié)并不是完全都用來保存數(shù)據(jù)行，一個數(shù)據(jù)行中還存在其他的信息用于表示該行數(shù)據(jù)，具體的結構是這樣的：

狀態(tài)位A 狀態(tài)位B 定長數(shù)據(jù)類型的長度定長數(shù)據(jù)的內容列數(shù)
NULL位圖
變長列的個數(shù) 變長列的偏移矩陣變長列的數(shù)據(jù)
1字節(jié)
1字節(jié) 2字節(jié) 具體定長數(shù)據(jù)字節(jié) 2字節(jié)
列數(shù)/8個字節(jié)
2字節(jié)
變長列個數(shù)*2個字節(jié) 具體變長數(shù)據(jù)字節(jié)

不管我們對表的定義是多么的簡單，一行數(shù)據(jù)除了數(shù)據(jù)自身占用的空間外，至少還要占用1+1+2+2+1=7個字節(jié)。如果定義的數(shù)據(jù)列很多，或者里面有變長數(shù)據(jù)列，那么占用的空間可能會更多。

現(xiàn)在回到我們前面講到的2個表tb4039和tb4040，要存儲tb4039中的一行數(shù)據(jù)需要1+1+2+4039+2+1=4046字節(jié)，所以正好可以在一個頁中保存2行數(shù)據(jù)。所以插入了100行數(shù)據(jù)，實際上是保存在50個數(shù)據(jù)頁中，大小就是8K*50=400K。而對于tb4040表，要存儲一行數(shù)據(jù)需要4047個字節(jié)，沒法在一個頁中保存2行數(shù)據(jù)，所以一行數(shù)據(jù)就占用一個數(shù)據(jù)頁，100行數(shù)據(jù)占用了100個數(shù)據(jù)頁，大學就是8K*100=800K。

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

這里只是舉了一個極端的例子，所以造成了一個字節(jié)的偏差而使占用的存儲空間翻倍，在實際應用中很少會出現(xiàn)這么極端的情況，但是很有可能使一個頁存儲5條數(shù)據(jù)的因為某個列多了1個2個字節(jié)所以只能存儲4條數(shù)據(jù)。也許大家認為少存一條數(shù)據(jù)并沒有什么，但是在數(shù)據(jù)量變的非常龐大以后一頁4條數(shù)據(jù)和一頁5條數(shù)據(jù)將會產生明顯的性能差異。使得一頁中存放更多的數(shù)據(jù)并不是為了節(jié)約存儲成本，現(xiàn)在的硬盤已經很便宜了很多服務器都是幾百個G的硬盤，本來5G的數(shù)據(jù)現(xiàn)在變長了10G，相對幾百個G上T的硬盤來說又算得了什么。

實際上我們要讓一個數(shù)據(jù)頁中存放更多行的數(shù)據(jù)主要是出于性能的考慮。SQL Server進行數(shù)據(jù)庫讀寫操作的基本單位是頁，如果一頁中存放了更多的數(shù)據(jù)，那么對表進行掃描和查找時進行的IO操作將減少，畢竟IO操作是非常消耗時間影響性能的。假設tb4039中有100W條數(shù)據(jù)，那么進行全表掃描就要讀取50W個數(shù)據(jù)頁，如果讀取10W個數(shù)據(jù)頁花費1秒鐘，那么對表tb4039進行掃描需要花費5秒鐘時間，而如果是使用tb4040存儲這100W條數(shù)據(jù)，進行全表掃描則需要讀取100W個數(shù)據(jù)頁，總共花費10秒鐘時間。就一個字節(jié)的差別，一個是5秒另一個是10秒，對性能的影響非常明顯。

為了提高數(shù)據(jù)庫查詢的性能，在表設計時可以遵循以下建議：

主鍵盡可能的短，能用tinyint的就不要用int，能用char(5)的就不要用成varchar(50)。
計算好表列的長度，能夠在一個頁中存放5條數(shù)據(jù)的，那就不要將字段設置的太長使得一個頁中只能存放3條或者4條數(shù)據(jù)。
盡量將字段設置為不允許為NULL，因為NULL值在存儲和數(shù)據(jù)處理時系統(tǒng)需要專門的處理，降低了性能。
能夠用固定長度的就不要用變長字段，比如身份證號就可以使用CHAR(18)，而不應該使用VARCHAR(18)。
不要在一個表中建立太多的列，如果一個實體的屬性太多時可以考慮進行垂直分割，將常用的字段放在一個表，不常用的字段放另外的表，這樣可以減小常用字段表中數(shù)據(jù)列占用的空間，使得一個數(shù)據(jù)頁中存儲更多的數(shù)據(jù)行。
不要將大對象、長字符串和常用的字段放在同一個表中。同樣還是出于性能上的考慮，比如有個產品表，里面有產品ID、產品名字、產品售價、產品圖片、產品描述等字段，那么我們可以將產品ID、產品名字、產品售價這幾個常用的而且占用空間小的列放在一個表，然后建立產品ID、產品圖片、產品描述這樣的表，通過外鍵約束的方式將大對象數(shù)據(jù)和長字符串數(shù)據(jù)放在另一個表中。

本篇文章來源于：開發(fā)學院 http://edu. 原文鏈接：http://edu./2009/0803/12014.php