MySQL 壓縮解決方案

導語

描述 MySQL 壓縮的使用場景和解決方案，包括壓縮傳輸協議、壓縮列解決方案和壓縮表解決方案。

提到 MySQL 壓縮相關的內容，我們能想到的可能是如下幾種和壓縮相關的場景：

1、客戶端和伺服器之間傳輸的數據量太大，需要進行壓縮，節約帶寬

2、MySQL 某個列的數據量大，只針對某個列的數據壓縮

3、MySQL 某個或者某幾個表數據太多，需要將表數據壓縮存放，減少磁碟空間的佔用

這幾個問題在 MySQL 側都有很好的解決方案 ，針對第 1 個問題，可以使用 MySQL 的壓縮協議解決；針對第 2個問題，可以採用 MySQL 的壓縮和解壓函數完美解決；而針對最複雜的第 3 個問題，則可以在引擎層面進行解決，目前myisam、innodb、tokudb、MyRocks 等引擎都支持表的壓縮。本篇文章要詳細討論的就是此類關於 MySQL壓縮機制相關 的問題，下面是主要的內容：

一、MySQL 壓縮協議介紹

1、適用場景

MySQL 壓縮協議適合的場景是 MySQL的伺服器端和客戶端之間傳輸的數據量很大，或者可用帶寬不高的情況，典型的場景有如下兩個：

a、查詢大量的數據，帶寬不夠（比如導出數據的時候）；

b、複製的時候 binlog 量太大，啟用 slave_compressed_protocol 參數進行日誌壓縮複製。

2、壓縮協議簡介

壓縮協議是 MySQL 通信協議的一部分，要啟用壓縮協議進行數據傳輸，需要 MySQL 伺服器端和客戶端都支持 zlib演算法。啟動壓縮協議會導致 CPU 負載略微上升。使用啟用壓縮協議使用-C 參數或者 --compress=true參數啟動客戶端的壓縮功能。如果啟用了-C 或者 compress=true 選項，那麼在連接到伺服器段的時候，會發送0x0020（CLIENT_COMPRESS）的伺服器權能標誌位，和伺服器端協商通過後（3次握手以後），就支持壓縮協議了。由於採用壓縮，數據包的格式會發生變化，具體的變化如下：

未壓縮的數據包格式：

壓縮后的數據包格式：

大家可能留意到壓縮后的數據報格式有壓縮和未壓縮之分，這個是 MySQL 為了較少 CPU 開銷而做的一個優化。如果內容小於 50個位元組的時候，就不對內容進行壓縮，而大於 50 位元組的時候，才會啟用壓縮功能。具體的規則如下：

當第三個欄位的值等於 0x00 的時候，表示當前包沒有壓縮，因此 n*byte 的內容為1*byte,n*byte，即請求類型和請求內容。

當第三個欄位的值大於 0x00 的時候，表示當前包已採用 zlib 壓縮，因此使用的時候需要對n*byte 進行解壓，解壓后內容為1*byte,n*byte，即請求類型和請求內容。

3、方案實踐

在客戶端連接的時候加上-C 或者--compress=true 參數。如果是對同步添加壓縮協議支持的時候，則需要配置slave_compressed_protocol=1。下面是採用壓縮協議連接 MySQL 服務端的範例：

• MySQL -h hostip -uroot -p password --compress

• MySQLdump -h hostip -uroot -p password-default-character-set=utf8 --compress --single-transaction dbnametablename > tablename.sql

如果需要在主從複製中啟用壓縮傳輸，則在從機開啟 slave_compressed_protocol=1 參數就 OK。

4、壓縮效果

可以通過在 MySQLdump 中使用--compress 選項來觀察壓縮傳輸的效果，也可以通過主從複製中已用slave_compressed_protocol 參數來觀察壓縮傳輸的效果，很容易看出效果，這裡不再截圖說明。

二、MySQL 列壓縮解決方案

MySQL 針對列的壓縮目前直接的方案並不支持，映象中騰訊的 TMySQL可以直接針對列的壓縮。這裡主要介紹一個曲線救國的辦法，那就是在業務層面使用 MySQL提供的壓縮和解壓函數來針對列進行壓縮和解壓操作。也就是要對某一列做壓縮，就需要在寫入的時候調用 COMPRESS函數對那個列的內容進行壓縮，然後存放到對應的列。讀取的時候，使用 UNCOMPRESSED 函數對壓縮的內容進行解壓縮。

1、適用場景

針對 MySQL 中某個列或者某幾個列數據量特別大，一般都是 varchar、text、char 等數據類型。

2、壓縮函數簡介

MySQL 的壓縮函數 COMPRESS 壓縮一個字元串，然後返回一個二進位串。使用該函數需要 MySQL服務端支持壓縮，否則會返回 NULL，壓縮欄位最好採用 varbinary 或者 blob 欄位類型保存。使用UNCOMPRESSED 函數對壓縮過的數據進行解壓。注意，採用這種方式需要在業務側做少量改造。壓縮后的內容存儲方式如下：

a、空字元串就以空字元串存儲

b、非空字元串存儲方式為前 4 個 bype 保存未壓縮的字元串，緊接著保存壓縮的字元串

3、方案實踐

欄位壓縮方案涉及到的幾個相關的函數如下：

壓縮函數

• COMPRESS()

解壓縮函數

• UNCOMPRESS()

字元串長度函數

• LENGTH()

未解壓字元串長度函數

• UNCOMPRESSED_LENGTH()

實踐步驟：

a、創建一張測試表

CREATE TABLE IF NOT EXISTS `test`.`test_compress` (`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',`content` blob NOT NULL COMMENT '內容列',

b、網表中插入壓縮的數據

insert into `test`.`test_compress`(content) values(COMPRESS(REPEAT('a',1000)));

c、讀取壓縮的數據

select UNCOMPRESS(content) from `test`.`test_compress`;

d、查詢對應的長度和內容

SELECT UNCOMPRESSED_LENGTH(content) AS length, LENGTH(content) AS compress_length, UNCOMPRESS(content), content FROM `test`.`test_compress`

4、壓縮效果

從上面截圖可以看出壓縮效果比較好，針對 text、char、varchr、blob等，如果裡面重複的數據越多壓縮效果就越好。

三、InnoDB 表壓縮方案解決方案

1、適用場景

採用壓縮表一般都用在由於數據量太大，磁碟空間不足，負載主要體現在 IO 上，而伺服器的 CPU 又有比較多的余量的場景。

2、表壓縮簡介

a、為什麼需要壓縮

目前很多表都支持壓縮，比如 Myisam、InnoDB、TokuDB、MyRocks 。由於使用 InnoDB主要是不需要做什麼改動，對線上完全透明，壓縮方案也非常成熟，因此這裡只對 InnoDB 做詳細說明。對於 TokuDB 和MyRocks 的壓縮方案將在 MySQL 的壓縮方案（二）中撰文說明。

在 SSD 沒有大量橫行的時候，資料庫幾乎都是 IO 負載型的，在 CPU 有大量余量的時候，磁碟 IO的瓶頸就已經凸顯出來。而數據的大量存儲，尤其是日誌型數據和監控類型的數據，會導致磁碟空間快速增長。硬碟不夠用也會在很多業務中凸顯出來。一種比較好的方式就誕生了，那就是通過犧牲少量CPU 資源，採用壓縮來減少磁碟空間佔用，以及優化 IO 和帶寬。尤其針對讀多些少的業務。

SSD 出來后，資料庫的 IO負載有所降低，但是對於磁碟空間的問題還是沒有很好的解決。因此壓縮表使用還是非常的廣泛。這也就是為什麼那麼多的引擎都支持壓縮的原因。而innodb 在 MySQL 5.5 的時候就支持了壓縮功能，只是壓縮比比較低，通常在 50%左右。而 tokuDB 能達到80%左右，MyRocks 的壓縮比能達到 70%左右。

注意：壓縮比和你存儲的數據組成有很大的關係，並不是所有的數據都能達到上面所說的壓縮比。如果大部分都是字元串，並且重複的數據比較多，壓縮比會很好。

b、innodb 的壓縮介紹

使用 innodb 壓縮的前提條件是，innodb_file_per_table這個參數要啟用，innodb_file_format 這個參數設置成 Barracuda。

你可以使用 ROW_FORMAT=COMPRESSED 來 create 或者 alter 表來開啟 innodb的壓縮功能，如果沒有指定 KEY_BLOCK_SIZE 的大小，默認 KEY_BLOCK_SIZE 為innodb_page_size 大小的一半，也可以通過指定 KEY_BLOCK_SIZE=n 參數來開啟 innodb的壓縮功能，n 可以為 1、2、4、8、16，單位是 K。n 的值越小，壓縮比越高，消耗的 CPU 資源也越多。注意 32K 或者64K 的頁不支持壓縮。啟用壓縮后，索引數據也同樣會被壓縮。

你也可以通過調整 innodb_compression_level 來設置壓縮的級別，級別從 1~9，默認是6。級別越低，意味著壓縮比越高，同時也意味著需要更多的 CPU 資源。

c、壓縮演算法

innodb 壓縮藉助的是著名的 zlib 庫，採用 L777 壓縮演算法，這種演算法在減少數據大小和 CPU利用方面很成熟高效。同時這種演算法是無損的，因此原生的未壓縮的數據總是能夠從壓縮文件中重構，LZ777實現原理是查找重複數據的序列號然後進行壓縮，所以數據模式決定了壓縮效率，一般而言，用戶的數據能夠被壓縮 50%以上。

d、壓縮表在 buffer_pool 中如何處理

在 buffer_pool 緩衝池中，壓縮的數據通過 KEY_BLOCK_SIZE的大小的頁來保存，如果要提取壓縮的數據或者要更新壓縮數據對應的列，則會創建一個未壓縮頁來解壓縮數據，然後在數據更新完成後，會將為壓縮頁的數據重新寫入到壓縮頁中。內存不足的時候，MySQL會講對應的未壓縮頁踢出去。因此如果你啟用了壓縮功能，你的 buffer_pool緩衝池中可能會存在壓縮頁和未壓縮頁，也可能只存在壓縮頁。不過可能仍然需要將你的 buffer_pool緩衝池調大，以便能同時能保存壓縮頁和未壓縮頁。

MySQL採用最少使用（LRU）演算法來確定將哪些頁保留在內存中，哪些頁剔除出去，因此熱數據會更多地保留在內存中。當壓縮表被訪問的時候，MySQL使用自適應的 LRU 演算法來維持內存中壓縮頁和非壓縮頁的平衡。當系統 IO負載比較高的時候，這種演算法傾向於講未壓縮的頁剔除，一面騰出更多的空間來存放更多的壓縮頁。當系統 CPU 負載比較高的時候，MySQL傾向於將壓縮頁和未壓縮頁都剔除出去，這個時候更多的內存用來保留熱的數據，從而減少解壓的操作。

e、如何評估 KEY_BLOCK_SIZE 是否合適

為了更深入地了解壓縮表對性能的影響，在 Information Schema庫中有對應的表可以用來評估內存的使用和壓縮率等指標。INNODB_CMP 是收集的是某一類的 KEY_BLOCK_SIZE壓縮表的整體狀況的信息，匯總的是所有 KEY_BLOCK_SIZE 壓縮表的統計。而 INNODB_CMP_PER_INDEX表則是收集各個表和索引的壓縮情況信息，這些信息對於在某個時間評估某個表的壓縮效率或者診斷性能問題很有幫助。INNODB_CMP_PER_INDEX表的收集會導致系統性能受到影響，必須 innodb_cmp_per_index_enabled選項才會記錄，生產環境最好不要開啟。

我們可以通過觀察 INNODB_CMP 表的壓縮失敗情況，如果失敗比較多，則需要調大 KEY_BLOCK_SIZE。一般建議KEY_BLOCK_SIZE 設置為 8。

3、方案實踐

a、設置好 innodb_file_per_table 和 innodb_file_format 參數

SET GLOBAL innodb_file_per_table=1;SET GLOBAL innodb_file_format=Barracuda;

b、創建對應的壓縮表

CREATE TABLE compress_test (c1 INT PRIMARY KEY,content varchar(255)) ROW_FORMAT=COMPRESSEDKEY_BLOCK_SIZE=8;

如果是已經存在的表，則通過 alter 來修改，SQL 如下：

ALTER TABLE compress_test ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

4、壓縮效果

壓縮效果通過線上的一個監控的表修改為壓縮后的文件大小來說明，壓縮前後對比如下：