MySQL 压缩 详解

MySQL 压缩协议适合的场景是 MySQL 的服务器端和客户端之间传输的数据量很大，或者可用带宽不高的情况，典型的场景有如下两个 :

1> 查询大量的数据，带宽不够(比如导出数据的时候)

2> 复制的时候 binlog 量太大，启用 slave_compressed_protocol 参数进行日志压缩复制

压缩协议是 MySQL 通信协议的一部分，要启用压缩协议进行数据传输，需要 MySQL 服务器端和客户端都支持 zlib 算法。启动压缩协议会导致 CPU 负载略微上升。使用启用压缩协议使用 -C 参数或者 --compress=true 参数启动客户端的压缩功能。如果启用 -C 或者 compress=true 选项，那么在连接到服务器段的时候，会发送 0x0020(CLIENT_COMPRESS) 的服务器权能标志位，和服务器端协商通过后(3 次握手以后)，就支持压缩协议。由于采用压缩，数据包的格式会发生变化，具体的变化如下 :

未压缩的数据包格式 :

3*byte	1*byte	1*byte	n*byte
消息长度	包序号	请求类型	请求内容

压缩后的数据包格式 :

3*byte	1*byte	3*byte	n*byte
消息长度	包序号	压缩包大小	压缩或未压缩包内容

压缩后的数据报格式有压缩和未压缩之分，这个是 MySQL 为较少 CPU 开销而做的一个优化。如果内容小于 50 个字节的时候，就不对内容进行压缩，而大于 50 字节的时候，才会启用压缩功能，具体的规则如下 :

1> 当第三个字段的值等于 0x00 的时候，表示当前包没有压缩，因此 n*byte 的内容为 1*byte,n*byte，即请求类型和请求内容

2> 当第三个字段的值大于 0x00 的时候，表示当前包已采用 zlib 压缩，因此使用的时候需要对 n*byte 进行解压，解压后内容为 1*byte,n*byte，即请求类型和请求内容

使用方式 : 在客户端连接的时候加上 -C 或者 --compress=true 参数，如果是对同步添加压缩协议支持的时候，则需要配置 slave_compressed_protocol=1。下面是采用压缩协议连接 MySQL 服务端的范例 :

# 连接 MySQL

mysql -h hostip -uroot -p password --compress

# 备份数据

mysqldump -h hostip -uroot -p password -default-character-set=utf8 --compress --single-transaction dbname tablename > tablename.sql

如果需要在主从复制中启用压缩传输，则在从机开启 slave_compressed_protocol=1 参数，slave_compressed_protocol 用于设置是否对 binlog 进行压缩

列压缩

MySQL 针对列的压缩目前直接的方案并不支持，但是在业务层面使用 MySQL 提供的压缩和解压函数来针对列进行压缩和解压操作。也就是要对某一列做压缩，就需要在写入的时候调用 COMPRESS 函数对那个列的内容进行压缩，然后存放到对应的列。读取的时候，使用 UNCOMPRESSED 函数对压缩的内容进行解压缩

适用场景 : 针对 MySQL 中某个列或者某几个列数据量特别大，一般都是 varchar、text、char 等数据类型

MySQL 的压缩函数 COMPRESS 压缩一个字符串，然后返回一个二进制串。使用该函数需要 MySQL 服务端支持压缩，否则会返回 NULL，压缩字段最好采用 varbinary 或者 blob 字段类型保存。使用 UNCOMPRESSED 函数对压缩过的数据进行解压。需要注意的是采用这种方式需要在业务侧做少量改造，压缩后的内容存储方式如下 :

1> 空字符串就以空字符串存储

2> 非空字符串存储方式为前 4 个 bype 保存未压缩的字符串，紧接着保存压缩的字符串

COMPRESS() : 压缩函数

UNCOMPRESS() : 解压缩函数

LENGTH() : 字符串长度函数

UNCOMPRESSED_LENGTH() : 未解压字符串长度函数

使用实例

# 创建测试表

CREATE TABLE IF NOT EXISTS test_compress (

id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',

content BLOB NOT NULL COMMENT '内容列',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='压缩测试表';

# 网表中插入压缩的数据

mysql> INSERT INTO test_compress(content) VALUES(COMPRESS(REPEAT('a',1000)));

Query OK, 1 row affected (0.01 sec)

# 读取压缩的数据

mysql> SELECT UNCOMPRESSED_LENGTH(content) AS length,LENGTH(content) AS compress_length,UNCOMPRESS(content),content FROM test_compress\G

*************************** 1. row ***************************

             length: 1000

    compress_length: 21

UNCOMPRESS(content): aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

            content: �  x�KL�`

                              w  ��z�

1 row in set (0.00 sec)

针对 text、char、varchr、blob 等，如果里面重复的数据越多压缩效果就越好

InnoDB 表压缩

采用压缩表一般都用在由于数据量太大，磁盘空间不足，负载主要体现在 IO 上，而服务器的 CPU 又有比较多的余量的场景。目前很多表都支持压缩，比如 Myisam、InnoDB、TokuDB、MyRocks。使用 InnoDB 存储引擎实现表压缩不需要做什么改动，对线上完全透明，压缩方案也非常成熟。在 SSD 没有大量横行的时候，数据库几乎都是 IO 负载型的，在 CPU 有大量余量的时候，磁盘 IO 的瓶颈就已经凸显出来。而数据的大量存储，尤其是日志型数据和监控类型的数据，会导致磁盘空间快速增长。硬盘不够用也会在很多业务中凸显出来。一种比较好的方式就诞生了，那就是通过牺牲少量 CPU 资源，采用压缩来减少磁盘空间占用，以及优化 IO 和带宽。尤其针对读多写少的业务。SSD 出来后，数据库的 IO 负载有所降低，但是对于磁盘空间的问题还是没有很好的解决。因此压缩表使用还是非常的广泛。这也就是为什么那么多的引擎都支持压缩的原因。而 InnoDB 在 MySQL 5.5 的时候就支持压缩功能，只是压缩比比较低，通常在 50%左右。而 tokuDB 能达到 80%左右，MyRocks 的压缩比能达到 70%左右

注 : 压缩比和存储的数据组成有很大的关系，并不是所有的数据都能达到上面所说的压缩比。如果大部分都是字符串，并且重复的数据比较多，压缩比会很好

使用 InnoDB 压缩的前提条件是，innodb_file_per_table 这个参数要启用，innodb_file_format 这个参数设置成 Barracuda。可以使用 ROW_FORMAT=COMPRESSED 来 create 或者 alter 表来开启 InnoDB 的压缩功能，如果没有指定 KEY_BLOCK_SIZE 的大小，默认 KEY_BLOCK_SIZE 为 innodb_page_size 大小的一半，也可以通过指定 KEY_BLOCK_SIZE=n 参数来开启 InnoDB 的压缩功能，n 可以为 1、2、4、8、16，单位是 K。n 的值越小，压缩比越高，消耗的 CPU 资源也越多。需要注意的是 32K 或者 64K 的页不支持压缩。启用压缩后，索引数据也同样会被压缩。可以通过调整 innodb_compression_level 来设置压缩的级别，级别从 1~9，默认是 6。级别越低，意味着压缩比越高，同时也意味着需要更多的 CPU 资源

压缩算法 : InnoDB 压缩借助的是著名的 zlib 库，采用 L777 压缩算法，这种算法在减少数据大小和 CPU 利用方面很成熟高效。同时这种算法是无损的，因此原生的未压缩的数据总是能够从压缩文件中重构，LZ777 实现原理是查找重复数据的序列号然后进行压缩，所以数据模式决定了压缩效率，一般而言，用户的数据能够被压缩 50%以上

压缩表在 buffer_pool 中的处理 : 在 buffer_pool 缓冲池中，压缩的数据通过 KEY_BLOCK_SIZE 的大小的页来保存，如果要提取压缩的数据或者要更新压缩数据对应的列，则会创建一个未压缩页来解压缩数据，然后在数据更新完成后，会将为压缩页的数据重新写入到压缩页中。内存不足的时候，MySQL 会将对应的未压缩页踢出去。因此如果启用压缩功能，buffer_pool 缓冲池中可能会存在压缩页和未压缩页，也可能只存在压缩页。不过可能仍然需要将 buffer_pool 缓冲池调大，以便能同时能保存压缩页和未压缩页。MySQL 采用最少使用(LRU)算法来确定将哪些页保留在内存中，哪些页剔除出去，因此热数据会更多地保留在内存中。当压缩表被访问的时候，MySQL 使用自适应的 LRU 算法来维持内存中压缩页和非压缩页的平衡。当系统 IO 负载比较高的时候，这种算法倾向于讲未压缩的页剔除，一面腾出更多的空间来存放更多的压缩页。当系统 CPU 负载比较高的时候，MySQL 倾向于将压缩页和未压缩页都剔除出去，这个时候更多的内存用来保留热的数据，从而减少解压的操作

如何评估 KEY_BLOCK_SIZE 是否合适

为了更深入地了解压缩表对性能的影响，在 Information Schema 库中有对应的表可以用来评估内存的使用和压缩率等指标。INNODB_CMP 是收集的是某一类的 KEY_BLOCK_SIZE 压缩表的整体状况的信息，汇总的是所有 KEY_BLOCK_SIZE 压缩表的统计。而 INNODB_CMP_PER_INDEX 表则是收集各个表和索引的压缩情况信息，这些信息对于在某个时间评估某个表的压缩效率或者诊断性能问题很有帮助。INNODB_CMP_PER_INDEX 表的收集会导致系统性能受到影响，必须 innodb_cmp_per_index_enabled 选项才会记录，生产环境最好不要开启。可以通过观察 INNODB_CMP 表的压缩失败情况，如果失败比较多，则需要调大 KEY_BLOCK_SIZE。一般建议 KEY_BLOCK_SIZE 设置为 8

一般而言，对于读远远大于写的应用以及拥有合理数量的字符串列的表，使用压缩效果会更好

使用实例

mysql> show variables like '%innodb_file_per_table%';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| innodb_file_per_table | ON    |

+-----------------------+-------+

1 row in set (0.00 sec)

mysql> show variables like '%innodb_file_format%';

+--------------------------+-----------+

| Variable_name            | Value     |

+--------------------------+-----------+

| innodb_file_format       | Barracuda |

| innodb_file_format_check | ON        |

| innodb_file_format_max   | Barracuda |

+--------------------------+-----------+

3 rows in set (0.00 sec)

# 设置好 innodb_file_per_table 和 innodb_file_format 参数，MySQL 默认是已经设置好的

#  如果你指定 ROW_FORMAT=COMPRESSED，那么可以忽略 KEY_BLOCK_SIZE 的值，这时使用默认 InnoDB页的一半，即 8kb

# 如果你指定 KEY_BLOCK_SIZE 的值，那么你可以忽略 ROW_FORMAT=COMPRESSED，因为这时会自动启用压缩

mysql> SET GLOBAL innodb_file_per_table=1;

mysql> SET GLOBAL innodb_file_format=Barracuda;

# 创建对应的压缩表

mysql> CREATE TABLE compress_test(id INT PRIMARY KEY AUTO_INCREMENT,content VARCHAR(1000)) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

# 如果是已经存在的表，则通过 alter 来修改

mysql> ALTER TABLE compress_test ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

# 向数据库中插入数据

mysql> INSERT INTO compress_test(content) VALUES(REPEAT('a',1000));

# 创建对应的不压缩表

mysql> CREATE TABLE uncompress_test(id INT PRIMARY KEY AUTO_INCREMENT,content VARCHAR(1000));

# 向数据库中插入数据

mysql> INSERT INTO uncompress_test(content) VALUES(REPEAT('a',1000));

# 查看数据

# ll

total 372

-rw-r-----. 1 mysql mysql  8586 Mar 17 15:38 a.frm

-rw-r-----. 1 mysql mysql 98304 Mar 18 05:50 a.ibd

-rw-r-----. 1 mysql mysql  8592 Mar 18 09:34 compress_test.frm

-rw-r-----. 1 mysql mysql 65536 Mar 18 09:34 compress_test.ibd

-rw-r-----. 1 mysql mysql    61 Mar 17 15:38 db.opt

-rw-r-----. 1 mysql mysql  8594 Mar 18 08:54 test_compress.frm

-rw-r-----. 1 mysql mysql 98304 Mar 18 09:25 test_compress.ibd

-rw-r-----. 1 mysql mysql  8592 Mar 18 09:34 uncompress_test.frm

-rw-r-----. 1 mysql mysql 98304 Mar 18 09:35 uncompress_test.ibd

.frm文件 存储数据表的框架结构 compress_test 和 uncompress_test 大小基本相同，.ibd 单表表空间文件 compress_test 差不多只有 uncompress_test 的 2/3