對于表列數據類型選擇的一點思考

簡介

    SQL Server每個表中各列的數據類型的選擇通常顯得很簡單，但是對于具體數據類型的選擇的不同對性能的影響還是略有差別。本篇文章對SQL Server表列數據類型的選擇進行一些探索。

 

一些數據存儲的基礎知識

    在SQL Server中，數據的存儲以頁為單位。八個頁為一個區。一頁為8K，一個區為64K,這個意味著1M的空間可以容納16個區。如圖1所示：

   

    圖1.SQL Server中的頁和區

 

    如圖1（PS:發現用windows自帶的畫圖程序畫博客中的圖片也不錯）可以看出，SQL Server中的分配單元分為三種，分別為存儲行內數據的In_Row_Data，存儲Lob對象的LOB_Data,存儲溢出數據的Row_Overflow_data。下面我們通過一個更具體的例子來理解這三種分配單元。

    我建立如圖2所示的表。

   

    圖2.測試表

 

    圖2的測試表不難看出，通過插入數據使得每一行的長度會超過每頁所能容納的最大長度8060字節。使得不僅產生了行溢出(Row_Overflow_Data)，還需要存儲LOB的頁.測試的插入語句和通過DBCC IND看到的分配情況如圖3所示。

   

    圖3.超過8060字節的行所分配的頁

 

    除去IAM頁，這1行數據所需要三個頁來存儲。首先是LOB頁，這類是用于存儲存在數據庫的二進制文件所設計，當這個類型的列出現時，在原有的列會存儲一個24字節的指針，而將具體的二進制數據存在LOB頁中，除去Text之外，VarBinary(max)也是存在LOB頁中的。然后是溢出行，在SQL Server 2000中，一行超過8060字節是不被允許的，在SQL Server 2005之后的版本對這個特性進行了改進，使用Varchar,nvarchar等數據類型時，當行的大小不超過8060字節時，全部存在行內In-row data,當varchar中存儲的數據過多使得整行超過8060字節時，會將額外的部分存于Row-overflow data頁中，如果update這列使得行大小減少到小于8060字節，則這行又會全部回到in-row data頁。

 

數據類型的選擇

    在了解了一些基礎知識之后。我們知道SQL Server讀取數據是以頁為單位，更少的頁不僅僅意味著更少的IO，還有更少的內存和CPU資源消耗。所以對于數據選擇的主旨是:

    盡量使得每行的大小更小

    這個聽起來非常簡單，但實際上還需要對SQL Server的數據類型有更多的了解。

    比如存儲INT類型的數據，按照業務規則，能用INT就不用BIGINT,能用SMALLINT就不用INT,能用TINYINT就不用SMALLINT。

    所以為了使每行的數據更小，則使用占字節最小的數據類型。

 

   1.比如不要使用DateTime類型，而根據業務使用更精確的類型，如下表:

類型	所占字節
Date(僅日期)	3
Time(僅時間)	5
DateTime2(時間和日期)	8
DateTimeOffSet(外加時區）	10

 

    2.使用VarChar(Max),Nvarchar(Max),varbinary(Max)來代替text,ntext和image類型

    根據前面的基礎知識可以知道，對于text,ntext和image類型來說，每一列只要不為null,即使占用很小的數據，也需要額外分配一個LOB頁，這無疑占用了更多的頁。而對于Varchar(Max)等數據類型來說，當數據量很小的時候，存在In-row-data中就能滿足要求，而不用額外的LOB頁，只有當數據溢出時，才會額外分配LOB頁，除此之外，Varchar(Max)等類型支持字符串操作函數比如：

• COL_LENGTH
• CHARINDEX
• PATINDEX
• LEN
• DATALENGTH
• SUBSTRING

 

    3.對于僅僅存儲數字的列，使用數字類型而不是Varchar等。

     因為數字類型占用更小的存儲空間。比如存儲123456789使用INT類型只需要4個字節，而使用Varchar就需要9個字節(這還不包括Varchar還需要占用4個字節記錄長度)。

 

    4.如果沒有必要，不要使用Nvarchar,Nchar等以“字”為單位存儲的數據類型。這類數據類型相比varchar或是char需要更多的存儲空間。

 

    5.關于Char和VarChar的選擇

     這類比較其實有一些了。如果懶得記憶，大多數情況下使用Varchar都是正確的選擇。我們知道Varchar所占用的存儲空間由其存儲的內容決定，而Char所占用的存儲空間由定義其的長度決定。因此Char的長度無論存儲多少數據，都會占用其定義的空間。所以如果列存儲著像郵政編碼這樣的固定長度的數據，選擇Char吧，否則選擇Varchar會比較好。除此之外，Varchar相比Char要多占用幾個字節存儲其長度，下面我們來做個簡單的實驗。

    首先我們建立表，這個表中只有兩個列，一個INT類型的列，另一個類型定義為Char(5)，向其中插入兩條測試數據，然后通過DBCC PAGE來查看其頁內結構，如圖4所示。

   
    圖4.使用char(5)類型，每行所占的空間為16字節

 

    下面我們再來看改為Varchar(5),此時的頁信息，如圖5所示。

   

    圖5.Varchar(5)，每行所占用的空間為20字節

 

    因此可以看出，Varchar需要額外4個字節來記錄其內容長度。因此，當實際列存儲的內容長度小于5字節時，使用char而不是varchar會更節省空間。

 

關于Null的使用

    關于Null的使用也是略有爭議。有些人建議不要允許Null,全部設置成Not Null+Default。這樣做是由于SQL Server比較時就不會使用三值邏輯(TRUE,FALSE,UNKNOWN)，而使用二值邏輯(True,False),并且查詢的時候也不再需要IsNull函數來替換Null值。

    但這也引出了一些問題，比如聚合函數的時候,Null值是不參與運算的，而使用Not Null+Default這個值就需要做排除處理。

    因此Null的使用還需要按照具體的業務來看。

 

考慮使用稀疏列(Sparse)

    稀疏列是對 Null 值采用優化的存儲方式的普通列。 稀疏列減少了 Null 值的空間需求，但代價是檢索非 Null 值的開銷增加。 當至少能夠節省 20% 到 40% 的空間時，才應考慮使用稀疏列。

    稀疏列在SSMS中的設置如圖6所示。

   

    圖6.稀疏列

 

    更具體的稀疏列如何能節省空間，請參看MSDN。

 

對于主鍵的選擇

     對于主鍵的選擇是表設計的重中之重，因為主鍵不僅關系到業務模型，更關系到對表數據操作的的效率（因為主鍵會處于B樹的非葉子節點中，對樹的高度的影響最多）。關于主鍵的選擇，我之前已經有一篇文章關于這點：從性能的角度談SQL Server聚集索引鍵的選擇，這里就不再細說了。

 

總結

    本篇文章對于設計表時，數據列的選擇進行了一些探尋。好的表設計不僅僅是能滿足業務需求，還能夠滿足對性能的優化。