詳解mysql表數據壓縮

記得一次面試中，面試官問我是否知道表的壓縮，這個時候我才知道mysql有個表壓縮這麼個功能，今天試用下看看表的壓縮率怎麼樣。

這裡分兩個部分說明，第一部分：官方文檔說明；第二部分：具體實例測試。

【第一部分】

一、表壓縮概述：

表壓縮可以在創建表時開啟，壓縮表能夠使表中的數據以壓縮格式存儲，壓縮能夠顯著提高原生性能和可伸縮性。壓縮意味著在硬盤和內存之間傳輸的數據更小且占用相對少的內存及硬盤，對於輔助索引，這種壓縮帶來更加明顯的好處，因為索引數據也被壓縮瞭。壓縮對於硬盤是SSD的存儲設備尤為重要，因為它們相對普通的HDD硬盤比較貴且容量有限。

我們都知道，CPU和內存的速度遠遠大於磁盤，因為對於數據庫服務器，磁盤IO可能會成為緊要資源或者瓶頸。數據壓縮能夠讓數據庫變得更小，從而減少磁盤的I/O,還能提高系統吞吐量，以很小的成本（耗費較多的CPU資源）。對於讀比重比較多的應用，壓縮是特別有用。壓縮能夠讓系統擁有足夠的內存來存儲熱數據。

在創建innodb表時帶上ROW_FORMAT=COMPRESSED參數能夠使用比默認的16K更小的頁。這樣在讀寫時需要更少的I/O，對於SSD磁盤更有價值。

頁的大小通過KEY_BLOCK_SIZE參數指定。不同大小的頁意味著需要使用獨立表空間，不能使用系統共享表空間，可以通過innodb_file_per_table指定。KEY_BLOCK_SIZE的值越小，你獲得I/O好處就越多，但是如果因為你指定的值太小，當數據被壓縮到不足夠滿足每頁多行數據記錄時，會產生額外的開銷來重組頁。對於一個表，KEY_BLOCK_SIZE的值有多小是有嚴格的限制的，一般是基於每個索引鍵的長度。有時指定值過小，當create table或者alter table會失敗。

在緩沖池中，被壓縮的數據是存儲在小頁中的，這個小頁的實際大小就是KEY_BLOCK_SIZE的值。為瞭提取和更新列值，mysql也會在緩沖池中創建一個未壓縮的16k頁。任何更新到未壓縮的頁也需要重新寫入到壓縮的頁，這時你需要估計緩沖池的大小以滿足壓縮和未壓縮的頁，盡管當緩沖空間不足時，未壓縮的頁會被擠出緩沖池。在下次訪問時，不壓縮的頁還會被創建。

二、使用表的壓縮

在創建一個壓縮表之前，需要啟用獨立表空間參數innodb_file_per_table=1；也需要設置innodb_file_format=Barracuda，你可以寫到my.cnf文件中不需要重啟mysql服務。

SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_file_format=Barracuda;
CREATE TABLE t1
 (c1 INT PRIMARY KEY) 
 ROW_FORMAT=COMPRESSED  
 KEY_BLOCK_SIZE=8;

• 如果你指定ROW_FORMAT=COMPRESSED，那麼可以忽略KEY_BLOCK_SIZE的值，這時使用默認innodb頁的一半，即8kb；
• 如果你指定瞭KEY_BLOCK_SIZE的值，那麼你可以忽略ROW_FORMAT=COMPRESSED，因為這時會自動啟用壓縮；
• 為瞭指定最合適KEY_BLOCK_SIZE的值，你可以創建表的多個副本，使用不同的值進行測試，比較他們的.ibd文件的大小；
• KEY_BLOCK_SIZE的值作為一種提示，如必要，Innodb也可以使用一個不同的值。0代表默認壓縮頁的值，Innodb頁的一半。KEY_BLOCK_SIZE的值隻能小於等於innodb page size。如果你指定瞭一個大於innodb page size的值，mysql會忽略這個值然後產生一個警告，這時KEY_BLOCK_SIZE的值是Innodb頁的一半。如果設置瞭innodb_strict_mode=ON，那麼指定一個不合法的KEY_BLOCK_SIZE的值是返回報錯。

InnoDB未壓縮的數據頁是16K，根據選項組合值，mysql為每個表的.ibd文件使用1kb,2kb,4kb,8kb,16kb頁大小，實際的壓縮算法並不會受KEY_BLOCK_SIZE值影響，這個值隻是決定每個壓縮塊有多大，從而影響多少行被壓縮到每個頁。設置KEY_BLOCK_SIZE值等於16k並不能有效的進行壓縮，因為默認的innodb頁就是16k，但是對於擁有很多BLOB,TEXT,VARCHAR類型字段的表可能會有效果的。

三、InnoDB表的壓縮優化

在進行表壓縮時需要考慮影響壓縮性能的因素，如：

• 哪些表需要壓縮
• 如何選擇壓縮表的頁大小
• 基於運行時性能特征是否需要調整buffer pool大小，如系統在壓縮和解壓縮數據所花費的時間量，系統負載更像一個數據倉庫還是OLTP事務性系統。
• 如果在壓縮表上執行DML操作，由於數據分佈的方式，可能導致壓縮失敗，這時你可能需要配置額外的更高級的配置選項

1、何時用壓縮表

一般而言，對於讀遠遠大於寫的應用以及擁有合理數量的字符串列的表，使用壓縮效果會更好。

2、數據特性及壓縮率

影響數據文件壓縮效率的一個關鍵因素是數據本身的結構，在塊數據中，壓縮是通過識別重復字符進行壓縮的，對於完全隨機的數據是一個糟糕的情況，一般而言，有重復數據的壓縮更好。對於字符串的列壓縮就不錯，無論是string還是blob、text等類型的。另一方面，如果表中的數據是二進制類型，如整形、浮點型等或者之前別壓縮過的如jpg、png類型的，壓縮效果一般不好，但也不是絕對的。

為瞭決定是否對某個表進行壓縮，你需要進行試驗，可以對比未壓縮與壓縮後的數據文件的大小，以及監控系統對於壓縮表的工作負載進行決定。具體試驗請查看第二部分。

查看監控壓縮表的負載，如下：

對於簡單的測試，如一個mysql實例上沒有其他的壓縮表瞭，直接查詢INFORMATION_SCHEMA.INNODB_CMP表數據即可，該表存一些壓縮表的數據狀態，結構如下：

• 對於精細的測試，如多個壓縮表，查詢INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX表數據，由於該表收集數據需要付出昂貴得代價，所以必須啟動innodb_cmp_per_index_enabled選項才能查詢。一般不要在生產環境下開啟該選項。
• 還可以針對壓縮運行一些測試SQL看看效率如何。
• 如果發現很多壓縮失敗，那麼你可以調整innodb_compression_level, innodb_compression_failure_threshold_pct, 和innodb_compression_pad_pct_max參數。

3、數據庫壓縮和應用程序壓縮

不需要在應用端和數據庫同時壓縮相同的數據，那樣效果並不明顯而且還消耗很多CPU資源。對於數據庫壓縮，是在server端進行的。如果你在插入數據前通過代碼進行數據壓縮，然後插入數據庫，這樣耗費很多CPU資源，當然如果你的CPU有大量結餘。你也可以結合兩者，對於某些表進行應用程序壓縮，而對其他數據采用數據庫壓縮。

4、工作負載特性和壓縮率

為瞭選擇哪些表可以使用壓縮，工作負載是另一個決定因素，一般而言，如果你的系統是I/O瓶頸，那麼可以使用CPU進行壓縮與解壓縮，以CPU換取I/O。

四、INNODB表是如何壓縮的？

1、壓縮算法

mysql進行壓縮是借助於zlib庫，采用L777壓縮算法，這種算法在減少數據大小、CPU利用方面是成熟的、健壯的、高效的。同時這種算法是無失真的，因此原生的未壓縮的數據總是能夠從壓縮文件中重構，LZ777實現原理是查找重復數據的序列號然後進行壓縮，所以數據模式決定瞭壓縮效率，一般而言，用戶的數據能夠被壓縮50%以上。

不同於應用程序壓縮或者其他數據庫系統的壓縮，InnoDB壓縮是同時對數據和索引進行壓縮，很多情況下，索引能夠占數據庫總大小的40%-50%。如果壓縮效果很好，一般innodb文件會減少25%-50%或者更多，而且減少I/O增加系統吞吐量，但是會增加CPU的占用，你可通過設置innodb_compression_level參數來平衡壓縮級別和CPU占用。

2、InnoDB數據存儲及壓縮

所有數據和b-tree索引都是按頁進行存儲的，每行包含主鍵和表的其他列。輔助索引也是b-tree結構的，包含對值：索引值及指向每行記錄的指針，這個指針實際上就是表的主鍵值。

在innodb壓縮表中，每個壓縮頁（1,2,4,8）都對應一個未壓縮的頁16K，為瞭訪問壓縮頁中的數據，如果該頁在buffer pool中不存在，那麼就從硬盤上讀到這個壓縮頁，然後進行解壓到原來的數據結構。為瞭最小化I/O和減少解壓頁的次數，有時，buffer pool中包括壓縮和未壓縮的頁，為給其他頁騰出地方，buffer pool會驅逐未壓縮頁，僅僅留下壓縮頁在內存中。或者如果一個頁一段時間沒有被訪問，那麼會被寫到硬盤上。這樣一來，任何時候，buffer pool中都可以包含壓縮頁和未壓縮頁，或者隻有壓縮頁或者兩者都沒有。

Mysql采用LRU算法來保證哪些頁應該在內存中還是被驅逐。因此熱數據一般都會在內存中。

五、OLTP系統壓縮負載優化

一般而言，innodb壓縮對於隻讀或者讀比重比較多的應用效果更好，SSD的出現，使得壓縮更加吸引我們，尤其對於OLTP系統。對於經常update、delete、insert的應用，通過壓縮表能夠減少他們的存儲需求和每秒I/O操作。

下面是針對寫密集的應用，設置壓縮表的一些有用參數：

• innodb_compression_level：決定壓縮程度的參數，如果你設置比較大，那麼壓縮比較多，耗費的CPU資源也較多；相反，如果設置較小的值，那麼CPU占用少。默認值6，可以設置0-9
• innodb_compression_failure_threshold_pct：默認值5，范圍0到100.設置中斷點避免高昂的壓縮失敗率。
• innodb_compression_pad_pct_max：指定在每個壓縮頁面可以作為空閑空間的最大比例，該參數僅僅應用在設置瞭
• innodb_compression_failure_threshold_pct不為零情況下，並且壓縮失敗率通過瞭中斷點。默認值50，可以設置范圍是0到75.

【第二部分】實驗：

#沒有設置壓縮前的數據大小
-rw-rw----. 1 mysql mysql 368M 12月 29 11:05 test.ibd
#設置KEY_BLOCK_SIZE=1
(product)root@localhost [sakila]> alter table test KEY_BLOCK_SIZE=1;
Query OK, 0 rows affected (14 min 49.30 sec)
Records: 0  Duplicates: 0  Warnings: 0
-rw-rw----. 1 mysql mysql 204M 1月  11 21:43 test.ibd      #####壓縮率44.5%
#設置KEY_BLOCK_SIZE=2
(product)root@localhost [sakila]> alter table test KEY_BLOCK_SIZE=2;
Query OK, 0 rows affected (9 min 55.60 sec)
Records: 0  Duplicates: 0  Warnings: 0
-rw-rw----. 1 mysql mysql 180M 1月  12 13:40 test.ibd      #####壓縮率51%
     
#設置KEY_BLOCK_SIZE=4
(product)root@localhost [sakila]> alter table test KEY_BLOCK_SIZE=4;
Query OK, 0 rows affected (7 min 24.52 sec)
Records: 0  Duplicates: 0  Warnings: 0
-rw-rw----. 1 mysql mysql 172M 1月  11 21:09 test.ibd      #####壓縮率53.2%
#設置KEY_BLOCK_SIZE=8
(product)root@localhost [sakila]> alter table test KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected (5 min 16.34 sec)
Records: 0  Duplicates: 0  Warnings: 0
-rw-rw----. 1 mysql mysql 172M 1月  11 21:00 test.ibd      #####壓縮率53.2%
#設置KEY_BLOCK_SIZE=16
(product)root@localhost [sakila]> alter table test KEY_BLOCK_SIZE=16;
Query OK, 0 rows affected (2 min 47.48 sec)
Records: 0  Duplicates: 0  Warnings: 0
-rw-rw----. 1 mysql mysql 336M 1月  12 13:54 test.ibd      #####壓縮率8.6%

【總結】：通過以上測試可知，當KEY_BLOCK_SIZE的值設置為4或者8時，壓縮效果最好，設置為16效果最差，因為頁的默認值16K。通常我是設置為8。

到此這篇關於mysql表數據壓縮的文章就介紹到這瞭,更多相關mysql表數據壓縮內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：

• MySQL之Innodb_buffer_pool_size設置方式
• MySQL中limit對查詢語句性能的影響
• 詳解MySQL中事務的持久性實現原理
• MySQL中讀頁緩沖區buffer pool詳解
• MySQL 用 limit 為什麼會影響性能