MySQL中的表壓縮功能

MySQL版本：8.0.22

os：linux ubuntu

語言：c++、c

在MySQL中支援3種類型的表壓縮，依次為：傳統壓縮、TPC壓縮、字典壓縮。

第一種：傳統壓縮

傳統的表壓縮方式是在MySQL5.0.7之前使用的，現在已經廢棄了，因為這種方式不但沒有提升資料庫的效率，反而降低了效率，導緻buffer pool的使用率降低了。

create table時指定壓縮後表的大小，即 KEY_BLOCK_SIZE 的大小，page預設大小為16KB。壓縮是按page為機關進行壓縮的。

SQL語句：

create table tt 
(
    c1 int primary key,
    c2 varchar(50)
) engine = innodb KEY_BLOCK_SIZE=8; 

KEY_BLOCK_SIZE 的取值為: 1，2，4，8，16
           

表示一個16KB的page壓縮之後是8KB。如果一個page壓縮之後的大小為9KB，那麼需要2個8KB的page進行存儲。如果一個page壓縮之後的大小為6KB，那麼隻需要1個8KB的page進行存儲即可。

KEY_BLOCK_SIZE的大小可以是 1、2、4、8、16，表示啟用頁壓縮，然後按照 1K、2K、4K、8K、16K 的頁大小存儲資料。如果設定KEY_BLOCK_SIZE的大小為0，那麼MySQL自動的将KEY_BLOCK_SIZE設定為innodb_page_size/2;

缺點：一個頁在緩沖池中存在兩個版本，壓縮前的原始版本和壓縮後的版本,這樣導緻緩沖池中能緩存page頁的數量大大減少，在buffer pool中多産生一個page。對于一個16KB的page，一個存放的是原來的16KB的頁資料，另一個是壓縮後的page，壓縮後以8KB為例，8KB中存放的是壓縮後的資料再加上redo.log日志以及file header和file tailer部分。

是以，這種壓縮方式會額外的多占用一個page用于存儲壓縮之後的page。

參考：MySQL :: MySQL 8.0 Reference Manual :: 15.9.1.2 Creating Compressed Tables

第二種：TPC壓縮

TPC是Transparent page compression的簡稱，也就是 透明頁壓縮。這種方式是主流的壓縮方式。

壓縮是按page為機關進行壓縮的，一個page的大小預設是16KB，也就是innodb page的預設大小，用于可以通過SQL : select @@innodb_page_size;查詢page的大小；下面都采用一個page為16KB為機關。

SQL語句：

create table tt
(
	c1 int primary key,
	c2 varchar(128)
) engine = innodb compression=zlib;

create table tt
(
	c1 int primary key,
	c2 varchar(128)
) engine = innodb compression=lz4;

create table tt
(
	c1 int primary key,
	c2 varchar(128)
) engine = innodb compression=none;
           

建表時，compression=後面指定壓縮的方式，支援下面3種寫法：

COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
           

其中zlib和lz4是支援的壓縮方式，NONE表示不壓縮。

一個page變成dirty page之後，背景線程就會将該dirty page flush到磁盤檔案中。确定要flush到磁盤檔案時，先将該page進行壓縮(lz4/zlib)，壓縮之後以9KB為例，那麼剩餘的7KB(16K-9K)就會被填充為0x00，然後flush到磁盤檔案後，調用檔案系統空洞（Hole Punch）特性(實際上是fallocate())對檔案進行“裁剪”，釋放 0x00 占用的稀疏空間，實際存放到磁盤上的檔案大小為7KB。

目前linux的核心以及大部分的檔案系統，例如：XFS、EXT4、ZFS、btrfs、NTFS 等，都支援檔案空洞特性。

檢視壓縮後的檔案占用的存儲空間大小的SQL如下：

SELECT SPACE, NAME, FS_BLOCK_SIZE, FILE_SIZE, ALLOCATED_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='mysql/bmsql_customer';
           

FILE_SIZE：表示原始檔案的大小

ALLOCATED_SIZE：表示壓縮之後的大小

壓縮率 = ALLOCATED_SIZE / FILE_SIZE

 參考：MySQL :: MySQL 8.0 Reference Manual :: 15.9.2 InnoDB Page Compression

第三種：字典壓縮

基于字典的列壓縮又叫壓縮字典，但隻适用于Percona分支。

優點是壓縮率高, 每個列的資料類型都相同；

限制條件：

該列壓縮方式僅用于InnoDB/XtraDB存儲引擎，資料類型支援:

(1)、BLOB (including TINYBLOB, MEDIUMBLOB, LONGBLOG)

(2)、TEXT (including TINYTEXT, MEDUUMTEXT, LONGTEXT)

(3)、VARCHAR (including NATIONAL VARCHAR)

(4)、VARBINARY

(5)、JSON

應用于不受支援的列類型或存儲引擎，則會報錯。

使用MySQL自帶的壓縮鍵功能時的SQL語句：

在建立表語句或更改表語句中增加壓縮辨別

(1)、CREATE語句

CREATE TABLE ... (..., foo BLOB COLUMN_FORMAT COMPRESSED, ...);
           

(2)、ALTER語句

ALTER TABLE ... CHANGE [COLUMN] ... COLUMN_FORMAT COMPRESSED;

ALTER TABLE ... MODIFY [COLUMN] ... COLUMN_FORMAT COMPRESSED;
           

還有一種是使用者自定義壓縮鍵，也就是在在建表時指定壓縮和壓縮鍵。

SQL語句如下：

SET @dictionary_data = 'wall' 'apple' 'peach' 'orange';

CREATE COMPRESSION_DICTIONARY numbers (@dictionary_data);

CREATE TABLE tt(
        c1 INT,
        c2 text COLUMN_FORMAT COMPRESSED,
        c3 BLOB COLUMN_FORMAT COMPRESSED WITH COMPRESSION_DICTIONARY numbers
      ) ENGINE=InnoDB;
           

c2采用MySQL預設的壓縮鍵；

c3采用使用者自定義的壓縮鍵；

不過這種方式使用起來還是有争議的，如果使用者用不好壓縮鍵導緻壓縮率低。

其他參考文獻：MySQL :: MySQL 8.0 Reference Manual :: 13.1.20 CREATE TABLE Statement

總之：

現在主流的壓縮方式為：TPC壓縮和基于字典鍵的壓縮。

如有疑問請發郵件：[email protected]