記得一次面試中，面試官問我是否知道表的壓縮，這個(gè)時(shí)候我才知道m(xù)ysql有個(gè)表壓縮這么個(gè)功能，今天試用下看看表的壓縮率怎么樣。

這里分兩個(gè)部分說明，第一部分：官方文檔說明；第二部分：具體實(shí)例測試。

【第一部分】

一、表壓縮概述：

表壓縮可以在創(chuàng)建表時(shí)開啟，壓縮表能夠使表中的數(shù)據(jù)以壓縮格式存儲(chǔ)，壓縮能夠顯著提高原生性能和可伸縮性。壓縮意味著在硬盤和內(nèi)存之間傳輸?shù)臄?shù)據(jù)更小且占用相對(duì)少的內(nèi)存及硬盤，對(duì)于輔助索引，這種壓縮帶來更加明顯的好處，因?yàn)樗饕龜?shù)據(jù)也被壓縮了。壓縮對(duì)于硬盤是SSD的存儲(chǔ)設(shè)備尤為重要，因?yàn)樗鼈兿鄬?duì)普通的HDD硬盤比較貴且容量有限。

我們都知道，CPU和內(nèi)存的速度遠(yuǎn)遠(yuǎn)大于磁盤，因?yàn)閷?duì)于數(shù)據(jù)庫服務(wù)器，磁盤IO可能會(huì)成為緊要資源或者瓶頸。數(shù)據(jù)壓縮能夠讓數(shù)據(jù)庫變得更小，從而減少磁盤的I/O,還能提高系統(tǒng)吞吐量，以很小的成本（耗費(fèi)較多的CPU資源）。對(duì)于讀比重比較多的應(yīng)用，壓縮是特別有用。壓縮能夠讓系統(tǒng)擁有足夠的內(nèi)存來存儲(chǔ)熱數(shù)據(jù)。

在創(chuàng)建innodb表時(shí)帶上ROW_FORMAT=COMPRESSED參數(shù)能夠使用比默認(rèn)的16K更小的頁。這樣在讀寫時(shí)需要更少的I/O，對(duì)于SSD磁盤更有價(jià)值。

頁的大小通過KEY_BLOCK_SIZE參數(shù)指定。不同大小的頁意味著需要使用獨(dú)立表空間，不能使用系統(tǒng)共享表空間，可以通過innodb_file_per_table指定。KEY_BLOCK_SIZE的值越小，你獲得I/O好處就越多，但是如果因?yàn)槟阒付ǖ闹堤?，?dāng)數(shù)據(jù)被壓縮到不足夠滿足每頁多行數(shù)據(jù)記錄時(shí)，會(huì)產(chǎn)生額外的開銷來重組頁。對(duì)于一個(gè)表，KEY_BLOCK_SIZE的值有多小是有嚴(yán)格的限制的，一般是基于每個(gè)索引鍵的長度。有時(shí)指定值過小，當(dāng)create table或者alter table會(huì)失敗。

在緩沖池中，被壓縮的數(shù)據(jù)是存儲(chǔ)在小頁中的，這個(gè)小頁的實(shí)際大小就是KEY_BLOCK_SIZE的值。為了提取和更新列值，mysql也會(huì)在緩沖池中創(chuàng)建一個(gè)未壓縮的16k頁。任何更新到未壓縮的頁也需要重新寫入到壓縮的頁，這時(shí)你需要估計(jì)緩沖池的大小以滿足壓縮和未壓縮的頁，盡管當(dāng)緩沖空間不足時(shí)，未壓縮的頁會(huì)被擠出緩沖池。在下次訪問時(shí)，不壓縮的頁還會(huì)被創(chuàng)建。

二、使用表的壓縮

在創(chuàng)建一個(gè)壓縮表之前，需要啟用獨(dú)立表空間參數(shù)innodb_file_per_table=1；也需要設(shè)置innodb_file_format=Barracuda，你可以寫到my.cnf文件中不需要重啟mysql服務(wù)。

SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_file_format=Barracuda;
CREATE TABLE t1
 (c1 INT PRIMARY KEY) 
 ROW_FORMAT=COMPRESSED  
 KEY_BLOCK_SIZE=8;

• 如果你指定ROW_FORMAT=COMPRESSED，那么可以忽略KEY_BLOCK_SIZE的值，這時(shí)使用默認(rèn)innodb頁的一半，即8kb；
• 如果你指定了KEY_BLOCK_SIZE的值，那么你可以忽略ROW_FORMAT=COMPRESSED，因?yàn)檫@時(shí)會(huì)自動(dòng)啟用壓縮；
• 為了指定最合適KEY_BLOCK_SIZE的值，你可以創(chuàng)建表的多個(gè)副本，使用不同的值進(jìn)行測試，比較他們的.ibd文件的大??；
• KEY_BLOCK_SIZE的值作為一種提示，如必要，Innodb也可以使用一個(gè)不同的值。0代表默認(rèn)壓縮頁的值，Innodb頁的一半。KEY_BLOCK_SIZE的值只能小于等于innodb page size。如果你指定了一個(gè)大于innodb page size的值，mysql會(huì)忽略這個(gè)值然后產(chǎn)生一個(gè)警告，這時(shí)KEY_BLOCK_SIZE的值是Innodb頁的一半。如果設(shè)置了innodb_strict_mode=ON，那么指定一個(gè)不合法的KEY_BLOCK_SIZE的值是返回報(bào)錯(cuò)。

InnoDB未壓縮的數(shù)據(jù)頁是16K，根據(jù)選項(xiàng)組合值，mysql為每個(gè)表的.ibd文件使用1kb,2kb,4kb,8kb,16kb頁大小，實(shí)際的壓縮算法并不會(huì)受KEY_BLOCK_SIZE值影響，這個(gè)值只是決定每個(gè)壓縮塊有多大，從而影響多少行被壓縮到每個(gè)頁。設(shè)置KEY_BLOCK_SIZE值等于16k并不能有效的進(jìn)行壓縮，因?yàn)槟J(rèn)的innodb頁就是16k，但是對(duì)于擁有很多BLOB,TEXT,VARCHAR類型字段的表可能會(huì)有效果的。

三、InnoDB表的壓縮優(yōu)化

在進(jìn)行表壓縮時(shí)需要考慮影響壓縮性能的因素，如：

• 哪些表需要壓縮
• 如何選擇壓縮表的頁大小
• 基于運(yùn)行時(shí)性能特征是否需要調(diào)整buffer pool大小，如系統(tǒng)在壓縮和解壓縮數(shù)據(jù)所花費(fèi)的時(shí)間量，系統(tǒng)負(fù)載更像一個(gè)數(shù)據(jù)倉庫還是OLTP事務(wù)性系統(tǒng)。
• 如果在壓縮表上執(zhí)行DML操作，由于數(shù)據(jù)分布的方式，可能導(dǎo)致壓縮失敗，這時(shí)你可能需要配置額外的更高級(jí)的配置選項(xiàng)

1、何時(shí)用壓縮表

一般而言，對(duì)于讀遠(yuǎn)遠(yuǎn)大于寫的應(yīng)用以及擁有合理數(shù)量的字符串列的表，使用壓縮效果會(huì)更好。

2、數(shù)據(jù)特性及壓縮率

影響數(shù)據(jù)文件壓縮效率的一個(gè)關(guān)鍵因素是數(shù)據(jù)本身的結(jié)構(gòu)，在塊數(shù)據(jù)中，壓縮是通過識(shí)別重復(fù)字符進(jìn)行壓縮的，對(duì)于完全隨機(jī)的數(shù)據(jù)是一個(gè)糟糕的情況，一般而言，有重復(fù)數(shù)據(jù)的壓縮更好。對(duì)于字符串的列壓縮就不錯(cuò)，無論是string還是blob、text等類型的。另一方面，如果表中的數(shù)據(jù)是二進(jìn)制類型，如整形、浮點(diǎn)型等或者之前別壓縮過的如jpg、png類型的，壓縮效果一般不好，但也不是絕對(duì)的。

為了決定是否對(duì)某個(gè)表進(jìn)行壓縮，你需要進(jìn)行試驗(yàn)，可以對(duì)比未壓縮與壓縮后的數(shù)據(jù)文件的大小，以及監(jiān)控系統(tǒng)對(duì)于壓縮表的工作負(fù)載進(jìn)行決定。具體試驗(yàn)請(qǐng)查看第二部分。

查看監(jiān)控壓縮表的負(fù)載，如下：

對(duì)于簡單的測試，如一個(gè)mysql實(shí)例上沒有其他的壓縮表了，直接查詢INFORMATION_SCHEMA.INNODB_CMP表數(shù)據(jù)即可，該表存一些壓縮表的數(shù)據(jù)狀態(tài)，結(jié)構(gòu)如下：

• 對(duì)于精細(xì)的測試，如多個(gè)壓縮表，查詢INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX表數(shù)據(jù)，由于該表收集數(shù)據(jù)需要付出昂貴得代價(jià)，所以必須啟動(dòng)innodb_cmp_per_index_enabled選項(xiàng)才能查詢。一般不要在生產(chǎn)環(huán)境下開啟該選項(xiàng)。
• 還可以針對(duì)壓縮運(yùn)行一些測試SQL看看效率如何。
• 如果發(fā)現(xiàn)很多壓縮失敗，那么你可以調(diào)整innodb_compression_level, innodb_compression_failure_threshold_pct, 和innodb_compression_pad_pct_max參數(shù)。

3、數(shù)據(jù)庫壓縮和應(yīng)用程序壓縮

不需要在應(yīng)用端和數(shù)據(jù)庫同時(shí)壓縮相同的數(shù)據(jù)，那樣效果并不明顯而且還消耗很多CPU資源。對(duì)于數(shù)據(jù)庫壓縮，是在server端進(jìn)行的。如果你在插入數(shù)據(jù)前通過代碼進(jìn)行數(shù)據(jù)壓縮，然后插入數(shù)據(jù)庫，這樣耗費(fèi)很多CPU資源，當(dāng)然如果你的CPU有大量結(jié)余。你也可以結(jié)合兩者，對(duì)于某些表進(jìn)行應(yīng)用程序壓縮，而對(duì)其他數(shù)據(jù)采用數(shù)據(jù)庫壓縮。

4、工作負(fù)載特性和壓縮率

為了選擇哪些表可以使用壓縮，工作負(fù)載是另一個(gè)決定因素，一般而言，如果你的系統(tǒng)是I/O瓶頸，那么可以使用CPU進(jìn)行壓縮與解壓縮，以CPU換取I/O。

四、INNODB表是如何壓縮的？

1、壓縮算法

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

不同于應(yīng)用程序壓縮或者其他數(shù)據(jù)庫系統(tǒng)的壓縮，InnoDB壓縮是同時(shí)對(duì)數(shù)據(jù)和索引進(jìn)行壓縮，很多情況下，索引能夠占數(shù)據(jù)庫總大小的40%-50%。如果壓縮效果很好，一般innodb文件會(huì)減少25%-50%或者更多，而且減少I/O增加系統(tǒng)吞吐量，但是會(huì)增加CPU的占用，你可通過設(shè)置innodb_compression_level參數(shù)來平衡壓縮級(jí)別和CPU占用。

2、InnoDB數(shù)據(jù)存儲(chǔ)及壓縮

所有數(shù)據(jù)和b-tree索引都是按頁進(jìn)行存儲(chǔ)的，每行包含主鍵和表的其他列。輔助索引也是b-tree結(jié)構(gòu)的，包含對(duì)值：索引值及指向每行記錄的指針，這個(gè)指針實(shí)際上就是表的主鍵值。

在innodb壓縮表中，每個(gè)壓縮頁（1,2,4,8）都對(duì)應(yīng)一個(gè)未壓縮的頁16K，為了訪問壓縮頁中的數(shù)據(jù)，如果該頁在buffer pool中不存在，那么就從硬盤上讀到這個(gè)壓縮頁，然后進(jìn)行解壓到原來的數(shù)據(jù)結(jié)構(gòu)。為了最小化I/O和減少解壓頁的次數(shù)，有時(shí)，buffer pool中包括壓縮和未壓縮的頁，為給其他頁騰出地方，buffer pool會(huì)驅(qū)逐未壓縮頁，僅僅留下壓縮頁在內(nèi)存中?；蛘呷绻粋€(gè)頁一段時(shí)間沒有被訪問，那么會(huì)被寫到硬盤上。這樣一來，任何時(shí)候，buffer pool中都可以包含壓縮頁和未壓縮頁，或者只有壓縮頁或者兩者都沒有。

Mysql采用LRU算法來保證哪些頁應(yīng)該在內(nèi)存中還是被驅(qū)逐。因此熱數(shù)據(jù)一般都會(huì)在內(nèi)存中。

五、OLTP系統(tǒng)壓縮負(fù)載優(yōu)化

一般而言，innodb壓縮對(duì)于只讀或者讀比重比較多的應(yīng)用效果更好，SSD的出現(xiàn)，使得壓縮更加吸引我們，尤其對(duì)于OLTP系統(tǒng)。對(duì)于經(jīng)常update、delete、insert的應(yīng)用，通過壓縮表能夠減少他們的存儲(chǔ)需求和每秒I/O操作。

下面是針對(duì)寫密集的應(yīng)用，設(shè)置壓縮表的一些有用參數(shù)：

• innodb_compression_level：決定壓縮程度的參數(shù)，如果你設(shè)置比較大，那么壓縮比較多，耗費(fèi)的CPU資源也較多；相反，如果設(shè)置較小的值，那么CPU占用少。默認(rèn)值6，可以設(shè)置0-9
• innodb_compression_failure_threshold_pct：默認(rèn)值5，范圍0到100.設(shè)置中斷點(diǎn)避免高昂的壓縮失敗率。
• innodb_compression_pad_pct_max：指定在每個(gè)壓縮頁面可以作為空閑空間的最大比例，該參數(shù)僅僅應(yīng)用在設(shè)置了
• innodb_compression_failure_threshold_pct不為零情況下，并且壓縮失敗率通過了中斷點(diǎn)。默認(rèn)值50，可以設(shè)置范圍是0到75.

【第二部分】實(shí)驗(yàn)：

#沒有設(shè)置壓縮前的數(shù)據(jù)大小
-rw-rw----. 1 mysql mysql 368M 12月 29 11:05 test.ibd
#設(shè)置KEY_BLOCK_SIZE=1
(product)root@localhost [sakila]> alter table test KEY_BLOCK_SIZE=1;
Query OK, 0 rows affected (14 min 49.30 sec)
Records: 0  Duplicates: 0  Warnings: 0
-rw-rw----. 1 mysql mysql 204M 1月  11 21:43 test.ibd      #####壓縮率44.5%
#設(shè)置KEY_BLOCK_SIZE=2
(product)root@localhost [sakila]> alter table test KEY_BLOCK_SIZE=2;
Query OK, 0 rows affected (9 min 55.60 sec)
Records: 0  Duplicates: 0  Warnings: 0
-rw-rw----. 1 mysql mysql 180M 1月  12 13:40 test.ibd      #####壓縮率51%
     
#設(shè)置KEY_BLOCK_SIZE=4
(product)root@localhost [sakila]> alter table test KEY_BLOCK_SIZE=4;
Query OK, 0 rows affected (7 min 24.52 sec)
Records: 0  Duplicates: 0  Warnings: 0
-rw-rw----. 1 mysql mysql 172M 1月  11 21:09 test.ibd      #####壓縮率53.2%
#設(shè)置KEY_BLOCK_SIZE=8
(product)root@localhost [sakila]> alter table test KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected (5 min 16.34 sec)
Records: 0  Duplicates: 0  Warnings: 0
-rw-rw----. 1 mysql mysql 172M 1月  11 21:00 test.ibd      #####壓縮率53.2%
#設(shè)置KEY_BLOCK_SIZE=16
(product)root@localhost [sakila]> alter table test KEY_BLOCK_SIZE=16;
Query OK, 0 rows affected (2 min 47.48 sec)
Records: 0  Duplicates: 0  Warnings: 0
-rw-rw----. 1 mysql mysql 336M 1月  12 13:54 test.ibd      #####壓縮率8.6%

【總結(jié)】：通過以上測試可知，當(dāng)KEY_BLOCK_SIZE的值設(shè)置為4或者8時(shí)，壓縮效果最好，設(shè)置為16效果最差，因?yàn)轫摰哪J(rèn)值16K。通常我是設(shè)置為8。

到此這篇關(guān)于mysql表數(shù)據(jù)壓縮的文章就介紹到這了,更多相關(guān)mysql表數(shù)據(jù)壓縮內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論