導(dǎo)語(yǔ)

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

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

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

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

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

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

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

1、適用場(chǎng)景

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

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

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

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

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

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

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

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

當(dāng)?shù)谌齻€(gè)字段的值等于 0x00 的時(shí)候，表示當(dāng)前包沒(méi)有壓縮，因此 n * byte 的內(nèi)容為 1 * byte,n * byte，即請(qǐng)求類(lèi)型和請(qǐng)求內(nèi)容。

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

3、方案實(shí)踐

在客戶(hù)端連接的時(shí)候加上-C 或者--compress=true 參數(shù)。如果是對(duì)同步添加壓縮協(xié)議支持的時(shí)候，則需要配置 slave_compressed_protocol=1。下面是采用壓縮協(xié)議連接 MySQL 服務(wù)端的范例：

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

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

如果需要在主從復(fù)制中啟用壓縮傳輸，則在從機(jī)開(kāi)啟 slave_compressed_protocol=1 參數(shù)就 OK。

4、壓縮效果

可以通過(guò)在 MySQLdump 中使用--compress 選項(xiàng)來(lái)觀(guān)察壓縮傳輸?shù)男Ч?，也可以通過(guò)主從復(fù)制中已用 slave_compressed_protocol 參數(shù)來(lái)觀(guān)察壓縮傳輸?shù)男Ч?，很容易看出效果，這里不再截圖說(shuō)明。

二、MySQL 列壓縮解決方案

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

1、適用場(chǎng)景

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

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

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

a、空字符串就以空字符串存儲(chǔ)

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

3、方案實(shí)踐

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

壓縮函數(shù)

COMPRESS()

解壓縮函數(shù)

UNCOMPRESS()

字符串長(zhǎng)度函數(shù)

LENGTH()

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

UNCOMPRESSED_LENGTH()

實(shí)踐步驟：

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

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='壓縮測(cè)試表';

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、查詢(xún)對(duì)應(yīng)的長(zhǎng)度和內(nèi)容

4、壓縮效果

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

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

1、適用場(chǎng)景

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

2、表壓縮簡(jiǎn)介 a、為什么需要壓縮

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

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

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

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

b、innodb 的壓縮介紹

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

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

你也可以通過(guò)調(diào)整 innodb_compression_level 來(lái)設(shè)置壓縮的級(jí)別，級(jí)別從 1~9，默認(rèn)是 6。級(jí)別越低，意味著壓縮比越高，同時(shí)也意味著需要更多的 CPU 資源。

c、壓縮算法

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

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

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

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

e、如何評(píng)估 KEY_BLOCK_SIZE 是否合適

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

我們可以通過(guò)觀(guān)察 INNODB_CMP 表的壓縮失敗情況，如果失敗比較多，則需要調(diào)大 KEY_BLOCK_SIZE。一般建議 KEY_BLOCK_SIZE 設(shè)置為 8。

3、方案實(shí)踐

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

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

b、創(chuàng)建對(duì)應(yīng)的壓縮表

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

ALTER TABLE compress_test ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

4、壓縮效果

壓縮效果通過(guò)線(xiàn)上的一個(gè)監(jiān)控的表修改為壓縮后的文件大小來(lái)說(shuō)明，壓縮前后對(duì)比如下：

最新評(píng)論