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]