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壓縮和基于字典鍵的壓縮。

如有疑問請發郵件：zgaoq@163.com

總結

以上是生活随笔為你收集整理的MySQL中的表压缩功能的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。