MySQL壓縮解決方案

MySQL壓縮協(xié)議介紹

1.適用場景

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

（1）查詢大量的數(shù)據(jù)，帶寬不夠，比如導(dǎo)出數(shù)據(jù)的時候。

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

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

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

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

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

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

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

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

3.方案實踐

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

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

4.壓縮效果

可通過在mysqldump中使用--compress選項來觀察壓縮傳輸?shù)男Ч?，也可通過主從復(fù)制中已用slave_compressed_protocol參數(shù)來觀察壓縮傳輸?shù)男Ч苋菀卓闯鲂Ч?/p>

MySQL列壓縮解決方案

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

1.適用場景

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

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

圖3 壓縮比截圖

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

（1）空字符串就以空字符串存儲。

（2）非空字符串存儲方式為前4個bype保存未壓縮的字符串，緊接著保存壓縮的字符串。

3.方案實踐

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

壓縮函數(shù)

COMPRESS()

解壓縮函數(shù)

UNCOMPRESS()

字符串長度函數(shù)

LENGTH()

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

UNCOMPRESSED_LENGTH()

實踐步驟：

（1）創(chuàng)建一張測試表：

CREATE TABLE IF NOT

（2）網(wǎng)表中插入壓縮的數(shù)據(jù)：

（3）讀取壓縮的數(shù)據(jù)：

（4）查詢對應(yīng)的長度和內(nèi)容：

4.壓縮效果

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

InnoDB表壓縮解決方案

1.適用場景

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

2.表壓縮簡介

（1）為什么需要壓縮

目前很多表都支持壓縮，比 如 Myisam、InnoDB、TokuDB、MyRocks 。由于使用InnoDB主要是不需要做什么改動，對線上完全透明，壓縮方案也成熟，因此這里只對InnoDB做詳細(xì)說明。

在SSD沒有大量橫行的時候，數(shù)據(jù)庫幾乎都是IO負(fù)載型的，在CPU有大量余量的時候，磁盤IO的瓶頸就已經(jīng)凸顯出來。而數(shù)據(jù)的大量存儲，尤其是日志型數(shù)據(jù)和監(jiān)控類型的數(shù)據(jù)，會導(dǎo)致磁盤空間快速增長。硬盤不夠用也會在很多業(yè)務(wù)中凸顯出來。

一種比較好的方式就誕生了，那就是通過犧牲少量CPU資源，采用壓縮來減少磁盤空間占用，以及優(yōu)化IO和帶寬。尤其針對讀多寫少的業(yè)務(wù)。

SSD出來后，數(shù)據(jù)庫的IO負(fù)載有所降低，但是對于磁盤空間的問題還是沒有很好的解決。因此壓縮表使用還是非常的廣泛。這也就是為什么那么多的引擎都支持壓縮的原因。

而Innodb在MySQL 5.5的時候就支持了壓縮功能，只是壓縮比比較低，通常在50%左右。而tokuDB能達(dá)到80%左右，MyRocks的壓縮比能達(dá)到70%左右。

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

（2）Innodb的壓縮介紹

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

你可以使用ROW_FORMAT=COMPRESSED來create或者alter表來開啟Innodb的壓縮功能，如果沒有指定KEY_BLOCK_SIZE的大小，默認(rèn)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ù)也同樣會被壓縮。

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

（3）壓縮算法

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

（4）壓縮表在buffer_pool中如何處理？

在buffer_pool緩沖池中，壓縮的數(shù)據(jù)通過KEY_BLOCK_SIZE的大小的頁來保存，如果要提取壓縮的數(shù)據(jù)或者要更新壓縮數(shù)據(jù)對應(yīng)的列，則會創(chuàng)建一個未壓縮頁來解壓縮數(shù)據(jù)，然后在數(shù)據(jù)更新完成后，會將為壓縮頁的數(shù)據(jù)重新寫入到壓縮頁中。內(nèi)存不足的時候，MySQL會講對應(yīng)的未壓縮頁踢出去。

因此，如果你啟用了壓縮功能，你的buffer_pool緩沖池中可能會存在壓縮頁和未壓縮頁，也可能只存在壓縮頁。不過可能仍然需要將你的buffer_pool緩沖池調(diào)大，以便能同時能保存壓縮頁和未壓縮頁。

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

圖4 壓縮前后對比圖

（5）如何評估KEY_BLOCK_SIZE是否合適？

為了更深入地了解壓縮表對性能的影響，在Information Schema庫中有對應(yīng)的表可以用來評估內(nèi)存的使用和壓縮率等指標(biāo)。

INNODB_CMP是收集的是某一類的KEY_BLOCK_SIZE壓縮表的整體狀況的信息，匯總的是所有KEY_BLOCK_SIZE壓縮表的統(tǒng)計。

而INNODB_CMP_PER_INDEX表則是收集各個表和索引的壓縮情況信息，這些信息對于在某個時間評估某個表的壓縮效率或者診斷性能問題很有幫助。INNODB_CMP_PER_INDEX表的收集會導(dǎo)致系統(tǒng)性能受到影響，必須innodb_cmp_per_index_enabled選項才會記錄，生產(chǎn)環(huán)境最好不要開啟。

我們可通過觀察INNODB_CMP表的壓縮失

敗情況，如果失敗比較多，則需要調(diào)大KEY_BLOCK_SIZE。一般建議KEY_BLOCK_SIZE設(shè)置為8。

3.方案實踐

（1）設(shè)置好innodb_file_per_table和innodb_file_format參數(shù)

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

（2）創(chuàng)建對應(yīng)的壓縮表

CREATETABLE compress_test (c1 INT PRIMARY KEY,content varchar(255)) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

如果是已經(jīng)存在的表，則通過alter來修改，SQL如下：ALTER TABLEcompress_test ROW_FORMAT=COMPRESSEDKEY_BLOCK_SIZE=8;

4.壓縮效果

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