導語

描述 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 dbname tablename > 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 '內容列',

PRIMARY KEY (`id`)

 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 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、壓縮效果

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

以上就是MySQL中關于壓縮的使用場景和解決方案的詳細內容，更多請關注php中文網其它相關文章！