mysql壓縮解決方案

 

描述 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 列壓縮解決方案

2、壓縮函式簡介

MySQL 的壓縮函式 COMPRESS 壓縮一個字串，然後返回一個二進位制串。使用該函式需要 MySQL 服務端支援壓縮，否則會返回 NULL，壓縮欄位最好採用 varbinary 或者 blob 欄位型別儲存。使用 UNCOMPRESSED 函式對壓縮過的資料進行解壓。注意，採用這種方式需要在業務側做少量改造。壓縮後的內容儲存方式如下：

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

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

3、方案實踐

欄位壓縮方案涉及到的幾個相關的函式如下：

壓縮函式

• COMPRESS()

解壓縮函式

• UNCOMPRESS()

字串長度函式

• LENGTH()

未解壓字串長度函式

• UNCOMPRESSED_LENGTH()

實踐步驟：

1. 建立資料庫表

CREATE TABLE  IF NOT EXISTS `test_compress` (

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

`content` blob NOT NULL COMMENT '內容列',
`name` varchar(200) NOT NULL COMMENT 'name',

PRIMARY KEY (`id`)

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

　　2.插入一條資料

insert into `test_compress`(content) values(COMPRESS('"<div class="w-e-content-container"> <p> <img src="https://img10.360buyimg.com/chic/jfs/t1/213416/13/1536/273782/617372daE5e6b3a33/825a0d03cfabf6f9.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/213416/13/1536/273782/617372daE5e6b3a33/825a0d03cfabf6f9.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/220138/15/1882/93119/61777d3dE61db5ddb/453c51a29aeff179.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/220138/15/1882/93119/61777d3dE61db5ddb/453c51a29aeff179.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/218219/26/1554/286582/617372daE80c0bdce/ab61320c58761f45.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/218219/26/1554/286582/617372daE80c0bdce/ab61320c58761f45.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/207798/25/6476/328695/617372daE4f770dd4/19cdc91ccdddabd1.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/207798/25/6476/328695/617372daE4f770dd4/19cdc91ccdddabd1.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/207293/28/6562/355923/617372daEad2418ed/b38b7c68b722d493.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/207293/28/6562/355923/617372daEad2418ed/b38b7c68b722d493.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/198472/7/14147/353512/617372daE475bfd78/fef8f2fba903b1a9.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/198472/7/14147/353512/617372daE475bfd78/fef8f2fba903b1a9.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/208709/11/6409/385534/617372daE546bf141/e76cef42871db280.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/208709/11/6409/385534/617372daE546bf141/e76cef42871db280.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/141557/22/23755/397718/617372daE0b82f73d/db33c96078f1d11a.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/141557/22/23755/397718/617372daE0b82f73d/db33c96078f1d11a.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/169270/29/21329/416165/617372daE94e8b257/a778fe8165757e9d.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/169270/29/21329/416165/617372daE94e8b257/a778fe8165757e9d.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/140728/1/25666/344248/617372dbE535662cb/8fe020754cf3722a.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/140728/1/25666/344248/617372dbE535662cb/8fe020754cf3722a.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/171559/6/21235/423277/617372dbE9eb46235/3a62fef4d1a15edf.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/171559/6/21235/423277/617372dbE9eb46235/3a62fef4d1a15edf.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/168858/37/22897/439146/617372dbEd82f4e2a/2af66e96a51b9486.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/168858/37/22897/439146/617372dbEd82f4e2a/2af66e96a51b9486.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/205152/24/12749/493690/617372dbEf73dbe18/4b2c801920a08390.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/205152/24/12749/493690/617372dbEf73dbe18/4b2c801920a08390.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/161214/15/21277/441293/617372dbE8e533234/95eade30a3655905.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/161214/15/21277/441293/617372dbE8e533234/95eade30a3655905.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/218750/6/1519/383308/617372dbEa6274296/3a916e4d95c97795.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/218750/6/1519/383308/617372dbEa6274296/3a916e4d95c97795.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/156858/19/21427/418698/617372dbEeca2b01f/df08509cfb4e29e8.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/156858/19/21427/418698/617372dbEeca2b01f/df08509cfb4e29e8.jpg" style=""/> </p> </div><div class="w-e-content-container"> <p> <img src="https://img10.360buyimg.com/chic/jfs/t1/213416/13/1536/273782/617372daE5e6b3a33/825a0d03cfabf6f9.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/213416/13/1536/273782/617372daE5e6b3a33/825a0d03cfabf6f9.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/220138/15/1882/93119/61777d3dE61db5ddb/453c51a29aeff179.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/220138/15/1882/93119/61777d3dE61db5ddb/453c51a29aeff179.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/218219/26/1554/286582/617372daE80c0bdce/ab61320c58761f45.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/218219/26/1554/286582/617372daE80c0bdce/ab61320c58761f45.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/207798/25/6476/328695/617372daE4f770dd4/19cdc91ccdddabd1.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/207798/25/6476/328695/617372daE4f770dd4/19cdc91ccdddabd1.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/207293/28/6562/355923/617372daEad2418ed/b38b7c68b722d493.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/207293/28/6562/355923/617372daEad2418ed/b38b7c68b722d493.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/198472/7/14147/353512/617372daE475bfd78/fef8f2fba903b1a9.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/198472/7/14147/353512/617372daE475bfd78/fef8f2fba903b1a9.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/208709/11/6409/385534/617372daE546bf141/e76cef42871db280.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/208709/11/6409/385534/617372daE546bf141/e76cef42871db280.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/141557/22/23755/397718/617372daE0b82f73d/db33c96078f1d11a.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/141557/22/23755/397718/617372daE0b82f73d/db33c96078f1d11a.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/169270/29/21329/416165/617372daE94e8b257/a778fe8165757e9d.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/169270/29/21329/416165/617372daE94e8b257/a778fe8165757e9d.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/140728/1/25666/344248/617372dbE535662cb/8fe020754cf3722a.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/140728/1/25666/344248/617372dbE535662cb/8fe020754cf3722a.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/171559/6/21235/423277/617372dbE9eb46235/3a62fef4d1a15edf.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/171559/6/21235/423277/617372dbE9eb46235/3a62fef4d1a15edf.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/168858/37/22897/439146/617372dbEd82f4e2a/2af66e96a51b9486.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/168858/37/22897/439146/617372dbEd82f4e2a/2af66e96a51b9486.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/205152/24/12749/493690/617372dbEf73dbe18/4b2c801920a08390.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/205152/24/12749/493690/617372dbEf73dbe18/4b2c801920a08390.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/161214/15/21277/441293/617372dbE8e533234/95eade30a3655905.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/161214/15/21277/441293/617372dbE8e533234/95eade30a3655905.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/218750/6/1519/383308/617372dbEa6274296/3a916e4d95c97795.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/218750/6/1519/383308/617372dbEa6274296/3a916e4d95c97795.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/156858/19/21427/418698/617372dbEeca2b01f/df08509cfb4e29e8.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/156858/19/21427/418698/617372dbEeca2b01f/df08509cfb4e29e8.jpg" style=""/> </p> </div><div class="w-e-content-container"> <p> <img src="https://img10.360buyimg.com/chic/jfs/t1/213416/13/1536/273782/617372daE5e6b3a33/825a0d03cfabf6f9.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/213416/13/1536/273782/617372daE5e6b3a33/825a0d03cfabf6f9.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/220138/15/1882/93119/61777d3dE61db5ddb/453c51a29aeff179.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/220138/15/1882/93119/61777d3dE61db5ddb/453c51a29aeff179.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/218219/26/1554/286582/617372daE80c0bdce/ab61320c58761f45.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/218219/26/1554/286582/617372daE80c0bdce/ab61320c58761f45.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/207798/25/6476/328695/617372daE4f770dd4/19cdc91ccdddabd1.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/207798/25/6476/328695/617372daE4f770dd4/19cdc91ccdddabd1.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/207293/28/6562/355923/617372daEad2418ed/b38b7c68b722d493.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/207293/28/6562/355923/617372daEad2418ed/b38b7c68b722d493.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/198472/7/14147/353512/617372daE475bfd78/fef8f2fba903b1a9.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/198472/7/14147/353512/617372daE475bfd78/fef8f2fba903b1a9.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/208709/11/6409/385534/617372daE546bf141/e76cef42871db280.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/208709/11/6409/385534/617372daE546bf141/e76cef42871db280.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/141557/22/23755/397718/617372daE0b82f73d/db33c96078f1d11a.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/141557/22/23755/397718/617372daE0b82f73d/db33c96078f1d11a.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/169270/29/21329/416165/617372daE94e8b257/a778fe8165757e9d.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/169270/29/21329/416165/617372daE94e8b257/a778fe8165757e9d.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/140728/1/25666/344248/617372dbE535662cb/8fe020754cf3722a.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/140728/1/25666/344248/617372dbE535662cb/8fe020754cf3722a.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/171559/6/21235/423277/617372dbE9eb46235/3a62fef4d1a15edf.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/171559/6/21235/423277/617372dbE9eb46235/3a62fef4d1a15edf.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/168858/37/22897/439146/617372dbEd82f4e2a/2af66e96a51b9486.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/168858/37/22897/439146/617372dbEd82f4e2a/2af66e96a51b9486.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/205152/24/12749/493690/617372dbEf73dbe18/4b2c801920a08390.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/205152/24/12749/493690/617372dbEf73dbe18/4b2c801920a08390.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/161214/15/21277/441293/617372dbE8e533234/95eade30a3655905.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/161214/15/21277/441293/617372dbE8e533234/95eade30a3655905.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/218750/6/1519/383308/617372dbEa6274296/3a916e4d95c97795.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/218750/6/1519/383308/617372dbEa6274296/3a916e4d95c97795.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/156858/19/21427/418698/617372dbEeca2b01f/df08509cfb4e29e8.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/156858/19/21427/418698/617372dbEeca2b01f/df08509cfb4e29e8.jpg" style=""/> </p> </div><div class="w-e-content-container"> <p> <img src="https://img10.360buyimg.com/chic/jfs/t1/213416/13/1536/273782/617372daE5e6b3a33/825a0d03cfabf6f9.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/213416/13/1536/273782/617372daE5e6b3a33/825a0d03cfabf6f9.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/220138/15/1882/93119/61777d3dE61db5ddb/453c51a29aeff179.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/220138/15/1882/93119/61777d3dE61db5ddb/453c51a29aeff179.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/218219/26/1554/286582/617372daE80c0bdce/ab61320c58761f45.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/218219/26/1554/286582/617372daE80c0bdce/ab61320c58761f45.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/207798/25/6476/328695/617372daE4f770dd4/19cdc91ccdddabd1.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/207798/25/6476/328695/617372daE4f770dd4/19cdc91ccdddabd1.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/207293/28/6562/355923/617372daEad2418ed/b38b7c68b722d493.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/207293/28/6562/355923/617372daEad2418ed/b38b7c68b722d493.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/198472/7/14147/353512/617372daE475bfd78/fef8f2fba903b1a9.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/198472/7/14147/353512/617372daE475bfd78/fef8f2fba903b1a9.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/208709/11/6409/385534/617372daE546bf141/e76cef42871db280.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/208709/11/6409/385534/617372daE546bf141/e76cef42871db280.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/141557/22/23755/397718/617372daE0b82f73d/db33c96078f1d11a.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/141557/22/23755/397718/617372daE0b82f73d/db33c96078f1d11a.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/169270/29/21329/416165/617372daE94e8b257/a778fe8165757e9d.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/169270/29/21329/416165/617372daE94e8b257/a778fe8165757e9d.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/140728/1/25666/344248/617372dbE535662cb/8fe020754cf3722a.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/140728/1/25666/344248/617372dbE535662cb/8fe020754cf3722a.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/171559/6/21235/423277/617372dbE9eb46235/3a62fef4d1a15edf.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/171559/6/21235/423277/617372dbE9eb46235/3a62fef4d1a15edf.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/168858/37/22897/439146/617372dbEd82f4e2a/2af66e96a51b9486.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/168858/37/22897/439146/617372dbEd82f4e2a/2af66e96a51b9486.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/205152/24/12749/493690/617372dbEf73dbe18/4b2c801920a08390.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/205152/24/12749/493690/617372dbEf73dbe18/4b2c801920a08390.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/161214/15/21277/441293/617372dbE8e533234/95eade30a3655905.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/161214/15/21277/441293/617372dbE8e533234/95eade30a3655905.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/218750/6/1519/383308/617372dbEa6274296/3a916e4d95c97795.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/218750/6/1519/383308/617372dbEa6274296/3a916e4d95c97795.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/156858/19/21427/418698/617372dbEeca2b01f/df08509cfb4e29e8.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/156858/19/21427/418698/617372dbEeca2b01f/df08509cfb4e29e8.jpg" style=""/> </p> </div><div class="w-e-content-container"> <p> <img src="https://img10.360buyimg.com/chic/jfs/t1/213416/13/1536/273782/617372daE5e6b3a33/825a0d03cfabf6f9.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/213416/13/1536/273782/617372daE5e6b3a33/825a0d03cfabf6f9.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/220138/15/1882/93119/61777d3dE61db5ddb/453c51a29aeff179.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/220138/15/1882/93119/61777d3dE61db5ddb/453c51a29aeff179.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/218219/26/1554/286582/617372daE80c0bdce/ab61320c58761f45.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/218219/26/1554/286582/617372daE80c0bdce/ab61320c58761f45.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/207798/25/6476/328695/617372daE4f770dd4/19cdc91ccdddabd1.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/207798/25/6476/328695/617372daE4f770dd4/19cdc91ccdddabd1.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/207293/28/6562/355923/617372daEad2418ed/b38b7c68b722d493.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/207293/28/6562/355923/617372daEad2418ed/b38b7c68b722d493.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/198472/7/14147/353512/617372daE475bfd78/fef8f2fba903b1a9.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/198472/7/14147/353512/617372daE475bfd78/fef8f2fba903b1a9.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/208709/11/6409/385534/617372daE546bf141/e76cef42871db280.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/208709/11/6409/385534/617372daE546bf141/e76cef42871db280.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/141557/22/23755/397718/617372daE0b82f73d/db33c96078f1d11a.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/141557/22/23755/397718/617372daE0b82f73d/db33c96078f1d11a.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/169270/29/21329/416165/617372daE94e8b257/a778fe8165757e9d.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/169270/29/21329/416165/617372daE94e8b257/a778fe8165757e9d.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/140728/1/25666/344248/617372dbE535662cb/8fe020754cf3722a.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/140728/1/25666/344248/617372dbE535662cb/8fe020754cf3722a.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/171559/6/21235/423277/617372dbE9eb46235/3a62fef4d1a15edf.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/171559/6/21235/423277/617372dbE9eb46235/3a62fef4d1a15edf.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/168858/37/22897/439146/617372dbEd82f4e2a/2af66e96a51b9486.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/168858/37/22897/439146/617372dbEd82f4e2a/2af66e96a51b9486.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/205152/24/12749/493690/617372dbEf73dbe18/4b2c801920a08390.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/205152/24/12749/493690/617372dbEf73dbe18/4b2c801920a08390.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/161214/15/21277/441293/617372dbE8e533234/95eade30a3655905.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/161214/15/21277/441293/617372dbE8e533234/95eade30a3655905.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/218750/6/1519/383308/617372dbEa6274296/3a916e4d95c97795.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/218750/6/1519/383308/617372dbEa6274296/3a916e4d95c97795.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/156858/19/21427/418698/617372dbEeca2b01f/df08509cfb4e29e8.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/156858/19/21427/418698/617372dbEeca2b01f/df08509cfb4e29e8.jpg" style=""/> </p> </div><div class="w-e-content-container"> <p> <img src="https://img10.360buyimg.com/chic/jfs/t1/213416/13/1536/273782/617372daE5e6b3a33/825a0d03cfabf6f9.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/213416/13/1536/273782/617372daE5e6b3a33/825a0d03cfabf6f9.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/220138/15/1882/93119/61777d3dE61db5ddb/453c51a29aeff179.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/220138/15/1882/93119/61777d3dE61db5ddb/453c51a29aeff179.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/218219/26/1554/286582/617372daE80c0bdce/ab61320c58761f45.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/218219/26/1554/286582/617372daE80c0bdce/ab61320c58761f45.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/207798/25/6476/328695/617372daE4f770dd4/19cdc91ccdddabd1.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/207798/25/6476/328695/617372daE4f770dd4/19cdc91ccdddabd1.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/207293/28/6562/355923/617372daEad2418ed/b38b7c68b722d493.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/207293/28/6562/355923/617372daEad2418ed/b38b7c68b722d493.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/198472/7/14147/353512/617372daE475bfd78/fef8f2fba903b1a9.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/198472/7/14147/353512/617372daE475bfd78/fef8f2fba903b1a9.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/208709/11/6409/385534/617372daE546bf141/e76cef42871db280.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/208709/11/6409/385534/617372daE546bf141/e76cef42871db280.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/141557/22/23755/397718/617372daE0b82f73d/db33c96078f1d11a.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/141557/22/23755/397718/617372daE0b82f73d/db33c96078f1d11a.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/169270/29/21329/416165/617372daE94e8b257/a778fe8165757e9d.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/169270/29/21329/416165/617372daE94e8b257/a778fe8165757e9d.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/140728/1/25666/344248/617372dbE535662cb/8fe020754cf3722a.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/140728/1/25666/344248/617372dbE535662cb/8fe020754cf3722a.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/171559/6/21235/423277/617372dbE9eb46235/3a62fef4d1a15edf.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/171559/6/21235/423277/617372dbE9eb46235/3a62fef4d1a15edf.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/168858/37/22897/439146/617372dbEd82f4e2a/2af66e96a51b9486.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/168858/37/22897/439146/617372dbEd82f4e2a/2af66e96a51b9486.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/205152/24/12749/493690/617372dbEf73dbe18/4b2c801920a08390.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/205152/24/12749/493690/617372dbEf73dbe18/4b2c801920a08390.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/161214/15/21277/441293/617372dbE8e533234/95eade30a3655905.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/161214/15/21277/441293/617372dbE8e533234/95eade30a3655905.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/218750/6/1519/383308/617372dbEa6274296/3a916e4d95c97795.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/218750/6/1519/383308/617372dbEa6274296/3a916e4d95c97795.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/156858/19/21427/418698/617372dbEeca2b01f/df08509cfb4e29e8.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/156858/19/21427/418698/617372dbEeca2b01f/df08509cfb4e29e8.jpg" style=""/> </p> </div><div class="w-e-content-container"> <p> <img src="https://img10.360buyimg.com/chic/jfs/t1/213416/13/1536/273782/617372daE5e6b3a33/825a0d03cfabf6f9.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/213416/13/1536/273782/617372daE5e6b3a33/825a0d03cfabf6f9.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/220138/15/1882/93119/61777d3dE61db5ddb/453c51a29aeff179.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/220138/15/1882/93119/61777d3dE61db5ddb/453c51a29aeff179.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/218219/26/1554/286582/617372daE80c0bdce/ab61320c58761f45.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/218219/26/1554/286582/617372daE80c0bdce/ab61320c58761f45.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/207798/25/6476/328695/617372daE4f770dd4/19cdc91ccdddabd1.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/207798/25/6476/328695/617372daE4f770dd4/19cdc91ccdddabd1.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/207293/28/6562/355923/617372daEad2418ed/b38b7c68b722d493.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/207293/28/6562/355923/617372daEad2418ed/b38b7c68b722d493.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/198472/7/14147/353512/617372daE475bfd78/fef8f2fba903b1a9.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/198472/7/14147/353512/617372daE475bfd78/fef8f2fba903b1a9.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/208709/11/6409/385534/617372daE546bf141/e76cef42871db280.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/208709/11/6409/385534/617372daE546bf141/e76cef42871db280.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/141557/22/23755/397718/617372daE0b82f73d/db33c96078f1d11a.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/141557/22/23755/397718/617372daE0b82f73d/db33c96078f1d11a.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/169270/29/21329/416165/617372daE94e8b257/a778fe8165757e9d.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/169270/29/21329/416165/617372daE94e8b257/a778fe8165757e9d.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/140728/1/25666/344248/617372dbE535662cb/8fe020754cf3722a.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/140728/1/25666/344248/617372dbE535662cb/8fe020754cf3722a.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/171559/6/21235/423277/617372dbE9eb46235/3a62fef4d1a15edf.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/171559/6/21235/423277/617372dbE9eb46235/3a62fef4d1a15edf.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/168858/37/22897/439146/617372dbEd82f4e2a/2af66e96a51b9486.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/168858/37/22897/439146/617372dbEd82f4e2a/2af66e96a51b9486.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/205152/24/12749/493690/617372dbEf73dbe18/4b2c801920a08390.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/205152/24/12749/493690/617372dbEf73dbe18/4b2c801920a08390.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/161214/15/21277/441293/617372dbE8e533234/95eade30a3655905.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/161214/15/21277/441293/617372dbE8e533234/95eade30a3655905.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/218750/6/1519/383308/617372dbEa6274296/3a916e4d95c97795.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/218750/6/1519/383308/617372dbEa6274296/3a916e4d95c97795.jpg" style=""/> <img src="https://img10.360buyimg.com/chic/jfs/t1/156858/19/21427/418698/617372dbEeca2b01f/df08509cfb4e29e8.jpg" alt="" data-href="https://img10.360buyimg.com/chic/jfs/t1/156858/19/21427/418698/617372dbEeca2b01f/df08509cfb4e29e8.jpg" style=""/> </p> </div>"'));

3.查詢結果

select UNCOMPRESS(content) from  `test_compress`;

 

 

 

 

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

　

 對比一下壓縮前後的資料長度：2萬多的字元對應varchar的長度達到911

 

 

 

從上面截圖可以看出壓縮效果比較好，針對 text、char、varchr、blob 等，如果裡面重複的資料越多壓縮效果就越好。

 

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

1、適用場景

採用壓縮表一般都用在由於資料量太大，磁碟空間不足，負載主要體現在 IO 上，而伺服器的 CPU 又有比較多的餘量的場景。

2、表壓縮簡介

a、為什麼需要壓縮

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

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、壓縮效果

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