MySQL中對于壓縮的使用場景與處理方案

導語

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

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

1、客戶端和服務器之間傳輸?shù)臄?shù)據(jù)量太大，需要進行壓縮，節(jié)約帶寬

2、MySQL 某個列的數(shù)據(jù)量大，只針對某個列的數(shù)據(jù)壓縮

3、MySQL 某個或者某幾個表數(shù)據(jù)太多，需要將表數(shù)據(jù)壓縮存放，減少磁盤空間的占用

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

一、MySQL 壓縮協(xié)議介紹

1、適用場景

MySQL 壓縮協(xié)議適合的場景是 MySQL 的服務器端和客戶端之間傳輸?shù)臄?shù)據(jù)量很大，或者可用帶寬不高的情況，典型的場景有如下兩個：

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

b、復制的時候 binlog 量太大，啟用 slave_compressed_protocol 參數(shù)進行日志壓縮復制。

2、壓縮協(xié)議簡介

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

未壓縮的數(shù)據(jù)包格式：

壓縮后的數(shù)據(jù)包格式：

大家可能留意到壓縮后的數(shù)據(jù)報格式有壓縮和未壓縮之分，這個是 MySQL 為了較少 CPU 開銷而做的一個優(yōu)化。如果內(nèi)容小于 50 個字節(jié)的時候，就不對內(nèi)容進行壓縮，而大于 50 字節(jié)的時候，才會啟用壓縮功能。具體的規(guī)則如下：

當?shù)谌齻�字段的值等于 0x00 的時候，表示當前包沒有壓縮，因此 n * byte 的內(nèi)容為 1 * byte,n * byte，即請求類型和請求內(nèi)容。

當?shù)谌齻�字段的值大于 0x00 的時候，表示當前包已采用 zlib 壓縮，因此使用的時候需要對 n * byte 進行解壓，解壓后內(nèi)容為 1 * byte,n * byte，即請求類型和請求內(nèi)容。

3、方案實踐

在客戶端連接的時候加上-C 或者--compress=true 參數(shù)。如果是對同步添加壓縮協(xié)議支持的時候，則需要配置 slave_compressed_protocol=1。下面是采用壓縮協(xié)議連接 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 參數(shù)就 OK。

4、壓縮效果

可以通過在 MySQLdump 中使用--compress 選項來觀察壓縮傳輸?shù)男Ч�，也可以通過主從復制中已用 slave_compressed_protocol 參數(shù)來觀察壓縮傳輸?shù)男Ч�，很容易看出效果，這里不再截圖說明。

二、MySQL 列壓縮解決方案

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

1、適用場景

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

2、壓縮函數(shù)簡介

MySQL 的壓縮函數(shù) COMPRESS 壓縮一個字符串，然后返回一個二進制串。使用該函數(shù)需要 MySQL 服務端支持壓縮，否則會返回 NULL，壓縮字段最好采用 varbinary 或者 blob 字段類型保存。使用 UNCOMPRESSED 函數(shù)對壓縮過的數(shù)據(jù)進行解壓。注意，采用這種方式需要在業(yè)務側做少量改造。壓縮后的內(nèi)容存儲方式如下：

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

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

3、方案實踐

字段壓縮方案涉及到的幾個相關的函數(shù)如下：

壓縮函數(shù)

COMPRESS()

解壓縮函數(shù)

UNCOMPRESS()

字符串長度函數(shù)

LENGTH()

未解壓字符串長度函數(shù)

UNCOMPRESSED_LENGTH()

實踐步驟：

a、創(chuàng)建一張測試表

CREATE TABLE IF NOT EXISTS `test`.`test_compress` (

`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',

`content` blob NOT NULL COMMENT '內(nèi)容列',

PRIMARY KEY (`id`)

 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='壓縮測試表';

b、網(wǎng)表中插入壓縮的數(shù)據(jù)

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

c、讀取壓縮的數(shù)據(jù)

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

d、查詢對應的長度和內(nèi)容

復制代碼 代碼如下:

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

4、壓縮效果

從上面截圖可以看出壓縮效果比較好，針對 text、char、varchr、blob 等，如果里面重復的數(shù)據(jù)越多壓縮效果就越好。

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

1、適用場景

采用壓縮表一般都用在由于數(shù)據(jù)量太大，磁盤空間不足，負載主要體現(xiàn)在 IO 上，而服務器的 CPU 又有比較多的余量的場景。

2、表壓縮簡介 a、為什么需要壓縮

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

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

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

注意：壓縮比和你存儲的數(shù)據(jù)組成有很大的關系，并不是所有的數(shù)據(jù)都能達到上面所說的壓縮比。如果大部分都是字符串，并且重復的數(shù)據(jù)比較多，壓縮比會很好。

b、innodb 的壓縮介紹

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

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

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

c、壓縮算法

innodb 壓縮借助的是著名的 zlib 庫，采用 L777 壓縮算法，這種算法在減少數(shù)據(jù)大小和 CPU 利用方面很成熟高效。同時這種算法是無損的，因此原生的未壓縮的數(shù)據(jù)總是能夠從壓縮文件中重構，LZ777 實現(xiàn)原理是查找重復數(shù)據(jù)的序列號然后進行壓縮，所以數(shù)據(jù)模式?jīng)Q定了壓縮效率，一般而言，用戶的數(shù)據(jù)能夠被壓縮 50%以上。

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

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

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

e、如何評估 KEY_BLOCK_SIZE 是否合適

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

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

3、方案實踐

a、設置好 innodb_file_per_table 和 innodb_file_format 參數(shù)

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

b、創(chuàng)建對應的壓縮表

復制代碼 代碼如下:

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

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

ALTER TABLE compress_test ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

4、壓縮效果

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

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