MySQL壓縮解決方案

MySQL壓縮協議介紹

1.適用場景

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

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

（2）復制的時候binlog量太大，啟用slave_compressed_protocol參數進行日志壓縮復制。

2.壓縮協議簡介

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

圖1 未壓縮的數據包格式

圖2 壓縮后的數據包格式

大家可能留意到壓縮后的數據報格式有壓縮和未壓縮之分，這個是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服務端的范例：

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

4.壓縮效果

可通過在mysqldump中使用--compress選項來觀察壓縮傳輸的效果，也可通過主從復制中已用slave_compressed_protocol參數來觀察壓縮傳輸的效果，很容易看出效果。

MySQL列壓縮解決方案

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

1.適用場景

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

2.壓縮函數簡介

圖3 壓縮比截圖

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

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

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

3.方案實踐

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

壓縮函數

COMPRESS()

解壓縮函數

UNCOMPRESS()

字符串長度函數

LENGTH()

未解壓字符串長度函數

UNCOMPRESSED_LENGTH()

實踐步驟：

（1）創建一張測試表：

CREATE TABLE IF NOT

（2）網表中插入壓縮的數據：

（3）讀取壓縮的數據：

（4）查詢對應的長度和內容：

4.壓縮效果

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

InnoDB表壓縮解決方案

1.適用場景

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

2.表壓縮簡介

（1）為什么需要壓縮

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

在SSD沒有大量橫行的時候，數據庫幾乎都是IO負載型的，在CPU有大量余量的時候，磁盤IO的瓶頸就已經凸顯出來。而數據的大量存儲，尤其是日志型數據和監控類型的數據，會導致磁盤空間快速增長。硬盤不夠用也會在很多業務中凸顯出來。

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

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

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

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

（2）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資源。

（3）壓縮算法

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

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

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

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

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

圖4 壓縮前后對比圖

（5）如何評估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.方案實踐

（1）設置好innodb_file_per_table和innodb_file_format參數

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

（2）創建對應的壓縮表

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

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

4.壓縮效果

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