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

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

【第一部分】

一、表壓縮概述：

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

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

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

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

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

二、使用表的壓縮

在創(chuàng)建一個壓縮表之前，需要啟用獨立表空間參數(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的值，這時使用默認(rèn)innodb頁的一半，即8kb；
• 如果你指定了KEY_BLOCK_SIZE的值，那么你可以忽略ROW_FORMAT=COMPRESSED，因為這時會自動啟用壓縮；
• 為了指定最合適KEY_BLOCK_SIZE的值，你可以創(chuàng)建表的多個副本，使用不同的值進行測試，比較他們的.ibd文件的大小；
• KEY_BLOCK_SIZE的值作為一種提示，如必要，Innodb也可以使用一個不同的值。0代表默認(rèn)壓縮頁的值，Innodb頁的一半。KEY_BLOCK_SIZE的值只能小于等于innodb page size。如果你指定了一個大于innodb page size的值，mysql會忽略這個值然后產(chǎn)生一個警告，這時KEY_BLOCK_SIZE的值是Innodb頁的一半。如果設(shè)置了innodb_strict_mode=ON，那么指定一個不合法的KEY_BLOCK_SIZE的值是返回報錯。

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

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

在進行表壓縮時需要考慮影響壓縮性能的因素，如：

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

1、何時用壓縮表

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

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

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

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

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

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

• 對于精細(xì)的測試，如多個壓縮表，查詢INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX表數(shù)據(jù)，由于該表收集數(shù)據(jù)需要付出昂貴得代價，所以必須啟動innodb_cmp_per_index_enabled選項才能查詢。一般不要在生產(chǎn)環(huá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ù)據(jù)，那樣效果并不明顯而且還消耗很多CPU資源。對于數(shù)據(jù)庫壓縮，是在server端進行的。如果你在插入數(shù)據(jù)前通過代碼進行數(shù)據(jù)壓縮，然后插入數(shù)據(jù)庫，這樣耗費很多CPU資源，當(dāng)然如果你的CPU有大量結(jié)余。你也可以結(jié)合兩者，對于某些表進行應(yīng)用程序壓縮，而對其他數(shù)據(jù)采用數(shù)據(jù)庫壓縮。

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

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

四、INNODB表是如何壓縮的？

1、壓縮算法

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

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

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

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

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

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

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

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

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

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

【第二部分】實驗：

#沒有設(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è)置為16效果最差，因為頁的默認(rèn)值16K。通常我是設(shè)置為8。

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

最新評論