SqlServer效能最佳化——Compression

http://blog.csdn.net/tuoxie5431/archive/2010/01/19/5214010.aspx

 

執行SQL查詢時，主要的幾個瓶頸在於：CPU運算速度、記憶體緩衝區大小、磁碟IO速度。而對於大資料量資料的查詢，其瓶頸則一般集中於磁碟IO，以及記憶體緩衝。那麼為了提高SQL查詢的效率，一方面我們需要考慮盡量減少查詢設計的資料條目數——建立索引，設立分區；另一方面，我們也可以考慮切實減少資料表物理大小，從而減少IO大小。

在SQL Server 2008中，最新提供了一項功能“壓縮（Compression）”，就是定位於減少資料表、索引物理大小。

設定壓縮

在企業管理器中，在需要壓縮的表或索引上右鍵選擇Storage-》Manage Compression：

這裡可以看到幾點：

1. 下方列表裡列出了該表所有的分區，也就是可以同一張表的不同分區應用不同的壓縮策略。
2. 壓縮方式（Compression Type）分為Row和Page兩種。
   
   行級壓縮（Row）：
   一方面減少了動態長度欄位中繼資料的大小（varchar、varbinary等），比如之前儲存欄位實際長度需要2bytes，壓縮後只需要3bits。
   另一方面也直接減少各欄位儲存內容的大小，比如儲存數值1在一個int類型欄位中，壓縮後只佔用了一個位元組。

   頁級壓縮（Page）： 能在各行間共用相同的資料，這裡麵包含兩項技術：列首碼（Column Prefix）、頁字典（Page Dictionary）。
   列首碼 可以讓擁有同樣首碼的欄位值擁有類似外鍵一樣的結構來儲存相同的首碼和各自的其餘部分。比如一張儲存了一個網站所有頁面URL的表，URL欄位儲存的值分別是‘ www.example.com/a.html’，‘www.example.com/b.html’，‘www.example.com/c.html’，‘www.example.com/d.html’。則壓縮後，它們同樣的首碼‘www.example.com/’會被提取出來，而其餘部分會被類似如下的形式儲存‘1a.html’，‘1b.html’，‘1c.html’，‘1d.html’。
   頁字典 則可以將在應用列首碼基礎上的其餘部分再次彙總儲存，比如同樣是一張儲存了一個網站所有頁面URL的表，假設有在表裡裡有多條URL欄位的值相同，比如‘1a.html’，‘1b.html’，‘1c.html’，‘1b.html’，‘1a.html’，‘1a.html’，則通過頁字典技術壓縮後，實際儲存在欄位中的值會進一步減少為‘2’，‘3’，‘1c.html’（沒有重複的欄位值不會被壓縮），‘3’，‘2’，‘2’。

3. 點擊“Calculate”後，會計算出表當前佔用的空間大小，以及壓縮需要的空間大小 。注意這裡與一般預想的不同，如果要對一張預存有資料但尚未壓縮的表進行壓縮，首先需要的是額外的空間大小。

執行壓縮

設定好之後，就可以選擇是產生指令碼還是立即執行，一般壓縮的執行時間受表原有資料多少以及選擇壓縮方式的影響。筆者對一張有上千萬條記錄的表做頁級壓縮，耗時在10分鐘左右。

壓縮完成之後查看資料庫大小，會探索資料庫的大小變大了！這也和在設定階段計算出來的額外空間相關。但實際上這裡大部分空間是預占的空間，並沒有實際資料。如果需要節省磁碟空間，需要進一步執行收縮（Shrink） 操作。

與Compression不同，Shrink用來釋放資料庫佔據的沒有利用的空間，一般用來對無用的記錄檔收縮（如果操作頻繁，記錄檔很有可能大於資料庫實際資料的大小）。這裡我們對資料庫檔案（mdf）做Shrink操作，完成之後再看資料庫的大小，果然減少了很多。筆者做壓縮、Shrink之後，一般都能將資料庫的大小減為原來的1/3~1/2左右。當然，具體壓縮比率取決於壓縮方式、壓縮表的欄位特點、壓縮表占整個資料庫資料的比重等。

注意事項

1. 既然對錶行了壓縮，那麼在執行查詢時必然會有解壓縮的過程。而這一過程會佔用CPU時間，也就是我們在通過壓縮減少了磁碟佔用空間以及IO時間的同時，增大了CPU的消耗。所以在壓縮前需要考慮清楚查詢的瓶頸到底是磁碟IO還是記憶體還是CPU。而且如果表應用了壓縮，類似建立索引，對於增刪改等操作也會有一定的影響。所以同樣要考慮應用在表上的操作到底以哪種為主。
2. 各頁面的壓縮是獨立進行的，頁字典和列首碼也分別儲存於各頁內。而且壓縮僅在資料頁快滿的時候進行，因為一個頁的大小是固定的，壓縮半頁不會有效能上的提升。
3. Database Backup中也有Compression的選項，但這利用的是系統的檔案壓縮技術，而且只能應用於整個資料庫上。
4. 容易被忽略的是，索引也能被壓縮，而且和表壓縮獨立，同樣也會提升所有應用到索引的查詢的效能。
5. 在Shrink階段，可能會造成大量的索引片段，所以可以在Shrink完成之後重建或者重組織索引，但同時，這些操作也會造成資料庫的體積變大……也就是，最小的資料庫體積和最小片段比率的索引是魚與熊掌，不可兼得。