八、IO最佳化（4）資料壓縮

一、概述

1. 為何需要資料壓縮

　　SQL Server的效能主要取決於磁碟I/O效率，提高I/O效率某種程式上就意味著提高效能。SQL Server 2008提供了資料壓縮功能來提高磁碟I/O。

　　資料壓縮意味著減小資料的有磁碟佔用量，所以資料壓縮可以用在表，叢集索引，非叢集索引，視圖索引或是分區表，分區索引上。

2. 啟用壓縮

　　啟用壓縮只會更改與資料類型相關聯的資料的實體儲存體格式，而不會更改其文法或語義。當對一個或多個表啟用壓縮時，不需要更改應用程式。

二、實現資料壓縮

　　資料壓縮可以在兩個層級上實現：行層級和頁層級。

頁層級壓縮包括行層級壓縮。

1. 行壓縮

　　新的記錄儲存格式主要有以下更改：

1）減少了與記錄相關聯的中繼資料開銷。此中繼資料為有關列、列長度和位移量的資訊。在某些情況下，中繼資料開銷可能大於舊的儲存格式。

2）它對於數實值型別例如，integer、decimal 和 float）和基於數值的類型例如，datetime 和 money）使用可變長度儲存格式。

3）它通過使用不儲存Null 字元的可變長度格式來儲存定長字串。

4）將對所有資料類型的 NULL 和 0 值進行最佳化，從而使它們不佔用任何位元組。

2. 頁壓縮

　　使用頁壓縮壓縮表和索引的分葉層級的過程由按以下順序進行的三個操作組成：

1）行壓縮

2）首碼壓縮

　　以表的頁壓縮為例，如所示，左側顯示了首碼壓縮之前表的一個樣本頁，右側則顯示的是同一頁在首碼壓縮之後的樣子。可以看到，首碼移至頁頭，列值更改為指向首碼的引用。在第一行的第一列，值 4b 指示為該行顯示首碼的前四個字元 (aaab) 和字元 b。這樣的話，結果值就是 aaabb，這是原始值。

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/210UW093-0.png" title="首碼壓縮.png" alt="113111954.png" />

3）字典壓縮

　　首碼壓縮完成後，將應用字典壓縮。字典壓縮搜尋網頁面上任意位置的重複值，然後將它們儲存在 CI 地區中。與首碼壓縮不同，字典壓縮不局限於一列。字典壓縮可以替換頁面上任意位置出現的重複值。顯示的是同一頁在字典壓縮之後的樣子。

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/210UQ140-1.png" title="字典壓縮.png" alt="113546432.png" />

請注意，值 4b 已由頁的其他列引用。

說明：

1）表、表分區、索引和索引分割區的頁壓縮都是類似的。

2）當使用頁壓縮時，將僅使用行壓縮來壓縮索引的非分葉層級頁。

三、壓縮表和索引

1.估算壓縮結果

　　若要確定更改壓縮狀態將對錶或索引有何影響，請使用 sp_estimate_data_compression_savings 預存程序。

2. 啟用壓縮

1） SSMS

　　可以通過SSMS更改表和壓縮選項。

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/210UW247-2.png" title="管理壓縮.png" alt="114633225.png" />

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/210UQ964-3.png" style="float:none;" title="01.png" alt="115219546.png" />

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/210UV341-4.png" style="float:none;" title="02.png" alt="115222108.png" />

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/210UQ158-5.png" style="float:none;" title="03.png" alt="115224507.png" />

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/210UUF4-6.png" style="float:none;" title="04.png" alt="115226277.png" />

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/210URQ1-7.png" style="float:none;" title="05.png" alt="115229693.png" />

2）T-SQL

　　表和索引在建立的同時就可以壓縮。

CREATE TABLE T1 (c1 int, c2 nvarchar(50) ) WITH ( DATA_COMPRESSION = ROW )

　　也可以使用Alter Table Rebuild With 或是 Alter Index Rebuild With文法壓縮已存在表或是索引。

USE [db01] ALTER TABLE [dbo].[EmpTable2] REBUILD PARTITION = ALL WITH ( DATA_COMPRESSION = PAGE )

3. 新頁的壓縮

　　當建立具有頁壓縮的新表時，不會進行壓縮。但是，表的中繼資料會指示應使用頁壓縮。當將資料添加到第一個資料頁時，會對資料進行行壓縮。因為此頁未滿，所以無法通過頁壓縮獲得任何益處。如果頁已滿，則添加下一行將引導頁壓縮操作。將查看整個頁；計算每一列以進行首碼壓縮，然後計算所有列以進行字典壓縮。如果頁壓縮已在頁上為要添加的行建立了足夠的空間，則添加該行，並對資料進行行壓縮和頁壓縮。如果通過頁壓縮獲得的空間減去 CI 結構所需空間之後剩餘的空間並不充足，則不會對此頁使用頁壓縮。以後，行將添加到新頁上，如果新頁中也無法再容納更多的行，則將再向表中添加一個新頁。與第一頁類似，新頁最初也不進行頁壓縮。

　　當包含資料的現有錶轉換為頁壓縮時，將重建和計算每一頁。重建所有頁會導致重建表、索引或分區。

四、注意事項

　　使用行壓縮和頁壓縮時，應注意以下事項：

1）只有在 SQL Server 2008以上的版本，Enterprise Edition 和 Developer Edition 中才能使用壓縮。

2）通過壓縮可在一頁上儲存更多的行，但不會更改表或索引的最大行大小。

3）當最大行大小加上壓縮開銷超過最大行大小 8060 個位元組時，不能對錶啟用壓縮功能。

4）非叢集索引不繼承表的壓縮屬性。若要壓縮索引，必須顯式設定索引的壓縮屬性。預設情況下，在建立索引時，索引的壓縮設定將設定為 NONE。

5）若要更改堆的壓縮設定，要求對錶重建所有非叢集索引，以便它們具有指向堆中的新行位置的指標。

本文結語：

　　通過資料壓縮，減少I/O壓力，提高I/O效率。

本文出自 “我們一起追過的MSSQL” 部落格，轉載請與作者聯絡！