SQL Server 2008中的資料壓縮功能

drillchina發表於2008-03-11
SQLServer

SQL Server 2005 SP2為我們帶來了vardecimal功能，這項功能使得原來定長的decimal資料在資料檔案中以可變長的格式儲存，據稱這項功能可以為典型的資料倉儲節省30%的空間，而SQL Server 2008在這一基礎上又進一步增強了資料壓縮功能。SQL Server 2008現在支援行壓縮和頁面壓縮兩種選項，資料壓縮選項可以在以下物件上啟用：

  • 未建立聚簇索引的表
  • 建立聚簇索引的表
  • 非聚簇索引(對錶設定壓縮選項不會影響到該表上的非聚簇索引，因此聚簇索引的壓縮需要單獨設定)
  • 索引檢視
  • 分割槽表和分割槽索引中的單個分割槽

為什麼需要資料壓縮

首先可能需要討論的問題就是為什麼在儲存成本不斷降低的今天，微軟還要煞費苦心地在SQL Server中實現並且不斷改進資料壓縮技術呢？

儘管儲存成本已經不再是傳統意義上的首要考慮因素，但是這並不代表資料庫尺寸不是一個問題，因為資料庫尺寸除了會影響到儲存成本之外，還極大地關聯到管理成本和效能問題。

首先我們來討論為什麼會有管理成本的問題？因為資料庫需要備份，資料庫的尺寸越大，那麼備份時間就會越長，當然另外一點就是消耗的備份硬體成本也會隨之提高(包括需要的備份介質成本和為了滿足備份視窗而需要更高階的備份裝置帶來的採購成本)，還有一種管理成本就是資料庫的維護成本，例如我們經常需要完成的DBCC任務，資料庫尺寸越大，我們就需要更多的時間來完成這些任務。

接著我們再看看效能問題。SQL Server在掃描磁碟讀取資料的時候都是按照資料頁為單位進行讀取的，因此如果一張資料頁中包含的資料行數越多，SQL Server在一次資料頁IO中獲得的資料就會越多，這樣也就帶來了效能的提升。

最後考慮儲存的成本，按照原先SQL Server 2005 SP2中vardecimal的壓縮資料為例，30%的空間節省也就意味著30%的儲存成本，而按照SQL Server 2008當前放出的測試資料，採用新的資料壓縮技術可以達到2X-7X的儲存率，再加上如果企業要考慮容災而增加的儲存空間，這樣節省的儲存硬體成本也將是想當可觀的。

如何使用資料壓縮

SQL Server 2008中的壓縮選項可以在建立表或索引時通過Option進行設定，例如：
CREATE TABLE TestTable (col1 int, col2 varchar(200)) WITH (DATA_COMPRESSION = ROW);

如果需要改變一個分割槽的壓縮選項，則可以用以下語句：

ALTER TABLE TestTable REBUILD PARTITION = 1 WITH (DATA COMPRESSION = PAGE);

如果需要為分割槽表的各個分割槽設定不同的壓縮選項，可以使用以下的語句：(SQL Server 2008可以對不同的分割槽使用不同的壓縮選項，這一點對於資料倉儲應用是非常重要的，因為資料倉儲的事實表通常都會有一個或數個熱分割槽，這些分割槽中的資料經常需要更新，為了避免資料壓縮給這些分割槽上的資料更新帶來額外的處理載荷，可以對這些分割槽關閉壓縮選項)

CREATE TABLE PartitionedTable (col1 int, col2 varchar(200))
ON PS1 (col1)
WITH (
DATA_COMPRESSION = ROW ON PARTITIONS(1),
DATA_COMPRESSION = PAGE ON PARTITION(2 TO 4));

如果是為某個索引設定壓縮選項的話，可以使用：

CREATE INDEX IX_TestTable_Col1 ON TestTable (Col1) WITH (DATA_COMPRESSION = ROW);

如果是修改某個索引的壓縮選項，可以使用：

ALTER INDEX IX_TestTable_Col1 ON TestTable REBUILD WITH (DATA_COMPRESSION = ROW);

SQL Server 2008同時還提供了一個名為sp_estimate_data_compression_savings儲存過程幫助DBA估計啟用壓縮選項後物件尺寸。

資料壓縮是怎樣工作的

對於行壓縮，SQL Server 2008採用以下三種方法來節省儲存空間：

  • 減少了與記錄相關聯的後設資料開銷。此後設資料為有關列、列長度和偏移量的資訊。在某些情況下，後設資料開銷可能大於舊的儲存格式。
  • 它對於數值型別（例如，integer、decimal 和 float）和基於數值的型別（例如，datetime 和 money）使用可變長度儲存格式。
  • 它通過使用不儲存空字元的可變長度格式來儲存定長字串。

    對於頁面壓縮，SQL Server 2008則是在一張資料頁面上依次採用：

    1. 行壓縮
    2. 字首壓縮
    3. 字典壓縮

    配置資料壓縮功能需要注意的

    儘管SQL Server 2008的資料壓縮功能非常有價值，但是仍然需要注意一些問題：

    • 資料壓縮功能僅在企業版和開發版中可用
    • 資料壓縮可以讓一張資料頁儲存更多的資料行，但是並不能改變單行資料最長8060位元組這一限制
    • 在一張已經設定了資料壓縮的表上建立聚簇索引時，聚簇索引預設繼承原表上的壓縮選項
    • 在未設定聚簇索引的表上設定頁面壓縮時，只有以下情況才會獲得頁面壓縮的實際效果：
      • 資料使用BULK INSERT語法新增到表中
      • 資料使用INSERT INTO ... WITH (TABLOCK)語法新增到表中
      • 執行帶有頁面壓縮選項的ALTER TABLE ... REBUILD命令
    • 在未設定聚簇索引的表上更改壓縮選項，會導致該表上所有非聚簇索引都需要重建，因為這些非聚簇索引指向的資料行地址已經都發生了改變。
    • 在改變壓縮選項時所需要的臨時空間大小與建立索引是所需要的空間是一樣的，因此對於分割槽表，我們可以逐個分割槽設定壓縮選項來減少臨時空間的需求壓力。
    • 由於SQL Server 2008中資料壓縮技術其實是SQL Server 2005 SP2中vardecimal技術的一個超集，因此設定了資料壓縮後就沒有必要保留vardecimal了。當然SQL Server 2008為了保持向後相容性，在當前版本中仍然保留了vardecimal，但是SQL Server 2008的下一個版本及可能就會棄用vardecimal選項，因此做了這些設定的資料庫應該儘早改變到資料壓縮設定下。
    • SQL Server 2008的壓縮選項是工作在儲存引擎層的，對於SQL Server的其他部件來說這一特性是透明的，因此當我們用BULK LOAD的方式將外面的資料匯入SQL Server時，會顯著的增加CPU的工作載荷，同時將以壓縮的資料表匯出到外部檔案時，可能會消耗比原來多很多的空間。

    • 來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9079672/viewspace-203964/，如需轉載，請註明出處，否則將追究法律責任。

    相關文章