MySQL高阶讲座之：`InnoDB`的`Page`压缩：其压缩算法、`CPU`开销与存储收益。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

各位观众老爷，晚上好！我是你们的老朋友，今天咱们来聊聊MySQL InnoDB 的 Page 压缩，这个听起来高大上，实际上就是省钱小能手。

InnoDB 的 Page 压缩，说白了，就是把数据页(Page)里面的重复内容，像打包行李一样，挤掉水分，让它体积更小。这样一来，同样的空间就能存更多的数据，省钱！而且，IO 效率也能蹭蹭往上涨，速度更快！但是，压缩是要消耗 CPU 的，所以，如何权衡压缩带来的收益和 CPU 的开销，这就是咱们今天要讨论的重点。

一、InnoDB Page 压缩算法：zlib、lz4 和 zstd

InnoDB 支持多种压缩算法，最常见的有 zlib、lz4 和 zstd。它们各有千秋，适用于不同的场景。

zlib: 经典老牌压缩算法，压缩率高，但速度相对较慢，CPU 消耗也较高。适合对存储空间要求更高，对 CPU 消耗不敏感的场景，比如历史数据归档。
lz4: 速度快，压缩率相对较低，CPU 消耗也较低。适合对性能要求更高，对存储空间不那么敏感的场景，比如实时数据查询。
zstd: 后起之秀，压缩率和速度都比较均衡，CPU 消耗也适中。逐渐成为主流选择，适用于大多数场景。性能表现一般优于 zlib，同时压缩率优于 lz4。

咱们先用一个表格来简单对比一下：

算法	压缩率	速度	CPU 消耗	适用场景
`zlib`	高	慢	高	历史数据归档，对存储空间要求高，对 CPU 不敏感
`lz4`	低	快	低	实时数据查询，对性能要求高，对存储空间不敏感
`zstd`	中等	中等	中等	大多数场景，性能和压缩率的平衡

二、如何开启 InnoDB Page 压缩

开启 InnoDB Page 压缩非常简单，只需要修改 CREATE TABLE 语句或者 ALTER TABLE 语句即可。

1. CREATE TABLE 语句：

CREATE TABLE `my_table` (
  `id` INT PRIMARY KEY,
  `name` VARCHAR(255),
  `data` TEXT
) ENGINE=InnoDB
ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8;

ROW_FORMAT=COMPRESSED：启用压缩。
KEY_BLOCK_SIZE=8：指定压缩后的 Page 大小，单位是 KB。常见的取值有 4, 8, 16。 KEY_BLOCK_SIZE 必须小于等于 innodb_page_size，且必须是 innodb_page_size 的约数。比如，如果 innodb_page_size 是 16KB，那么 KEY_BLOCK_SIZE 可以是 4, 8, 16。

2. ALTER TABLE 语句：

ALTER TABLE `my_table` ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

这条语句可以将已有的表 my_table 开启压缩。

3. 指定压缩算法：

默认情况下，InnoDB 使用 zlib 算法进行压缩。如果你想使用 lz4 或者 zstd，需要通过 innodb_compression_algorithm 参数来指定。

SET GLOBAL innodb_compression_algorithm=lz4;  -- 设置全局参数，影响所有新创建的表
ALTER TABLE `my_table` ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 COMPRESSION_ALGORITHM=lz4; -- 只影响当前表

注意：

修改 innodb_compression_algorithm 全局参数后，只会影响新创建的表。对于已经存在的表，需要使用 ALTER TABLE 语句来修改。
开启压缩后，InnoDB 会在后台自动进行压缩。这个过程可能会比较耗时，取决于表的大小。
不是所有的数据都适合压缩。如果数据本身已经高度压缩，比如图片、视频等，再进行压缩可能效果不佳，甚至会适得其反。

三、CPU 开销与存储收益

Page 压缩虽然能节省存储空间，提高 IO 效率，但也会带来 CPU 开销。压缩和解压缩都需要消耗 CPU 资源。因此，在选择是否开启压缩时，需要权衡 CPU 开销和存储收益。

1. CPU 开销：

CPU 开销主要体现在以下几个方面：

压缩： 在写入数据时，InnoDB 需要对数据进行压缩，这会消耗 CPU 资源。
解压缩： 在读取数据时，InnoDB 需要对数据进行解压缩，这也会消耗 CPU 资源。

CPU 开销的大小取决于压缩算法、压缩率和数据量。压缩率越高，CPU 消耗越大。数据量越大，CPU 消耗也越大。

2. 存储收益：

存储收益主要体现在以下几个方面：

节省存储空间： 压缩后的数据体积更小，可以节省存储空间。
提高 IO 效率： 压缩后的数据体积更小，可以减少 IO 操作，提高 IO 效率。

存储收益的大小取决于压缩率和数据量。压缩率越高，存储收益越大。数据量越大，存储收益也越大。

3. 如何权衡：

权衡 CPU 开销和存储收益，需要根据具体的应用场景来分析。

CPU 资源充足，存储空间紧张： 可以选择压缩率较高的算法，比如 zlib 或者 zstd。
CPU 资源紧张，存储空间相对充足： 可以选择速度较快的算法，比如 lz4。
CPU 资源和存储空间都比较紧张： 需要进行综合评估，选择一个平衡点。可以考虑使用 zstd 算法，或者对部分数据进行压缩，对另一部分数据不进行压缩。

四、实战案例：用 sysbench 模拟不同压缩算法下的性能表现

光说不练假把式，咱们来用 sysbench 模拟一下不同压缩算法下的性能表现。

1. 准备工作：

安装 sysbench： yum install sysbench (CentOS/RHEL) 或者 apt-get install sysbench (Debian/Ubuntu)
创建一个测试数据库： CREATE DATABASE sbtest;
连接到测试数据库： mysql -u root -p sbtest

2. 初始化 sysbench：

sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=your_password --mysql-db=sbtest --table-size=1000000 --tables=10 prepare

这条命令会创建 10 张表，每张表包含 100 万条数据。你需要把 your_password 替换成你自己的 MySQL 密码。

3. 测试 zlib 算法：

首先，创建一个使用 zlib 算法压缩的表：

CREATE TABLE `sbtest1_zlib` LIKE `sbtest1`;
ALTER TABLE `sbtest1_zlib` ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 COMPRESSION_ALGORITHM=zlib;
INSERT INTO `sbtest1_zlib` SELECT * FROM `sbtest1`;

然后，运行 sysbench 测试：

sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=your_password --mysql-db=sbtest --table-size=1000000 --tables=1 --report-interval=10 --time=60 --threads=8 run

这条命令会运行 60 秒的读写混合负载，使用 8 个线程。 --report-interval=10 表示每 10 秒输出一次报告。

4. 测试 lz4 算法：

类似地，创建一个使用 lz4 算法压缩的表：

CREATE TABLE `sbtest1_lz4` LIKE `sbtest1`;
ALTER TABLE `sbtest1_lz4` ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 COMPRESSION_ALGORITHM=lz4;
INSERT INTO `sbtest1_lz4` SELECT * FROM `sbtest1`;

然后，运行 sysbench 测试：

sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=your_password --mysql-db=sbtest --table-size=1000000 --tables=1 --report-interval=10 --time=60 --threads=8 run

5. 测试 zstd 算法：

类似地，创建一个使用 zstd 算法压缩的表：

CREATE TABLE `sbtest1_zstd` LIKE `sbtest1`;
ALTER TABLE `sbtest1_zstd` ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 COMPRESSION_ALGORITHM=zstd;
INSERT INTO `sbtest1_zstd` SELECT * FROM `sbtest1`;

然后，运行 sysbench 测试：

sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=your_password --mysql-db=sbtest --table-size=1000000 --tables=1 --report-interval=10 --time=60 --threads=8 run

6. 测试未压缩的表：

为了对比，咱们也测试一下未压缩的表：

sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=your_password --mysql-db=sbtest --table-size=1000000 --tables=1 --report-interval=10 --time=60 --threads=8 run

7. 分析结果：

运行完所有测试后，你需要分析 sysbench 的输出结果。关注以下几个指标：

transactions: 每秒处理的事务数 (TPS)。越高越好。
queries: 每秒执行的查询数 (QPS)。越高越好。
latency: 平均延迟。越低越好。

通过对比不同算法下的 TPS、QPS 和延迟，你可以了解不同算法的性能表现。同时，你也可以通过 SHOW TABLE STATUS 命令来查看表的存储空间占用情况，从而了解不同算法的压缩率。

SHOW TABLE STATUS LIKE 'sbtest1%';

关注 Data_length 和 Index_length 字段，它们分别表示数据和索引的存储空间占用情况。

五、Page 压缩的注意事项

KEY_BLOCK_SIZE 的选择： KEY_BLOCK_SIZE 的选择会影响压缩率和性能。一般来说，KEY_BLOCK_SIZE 越大，压缩率越高，但性能可能会下降。需要根据具体的应用场景进行测试，选择一个合适的 KEY_BLOCK_SIZE。
监控 CPU 负载： 开启压缩后，需要密切监控 CPU 负载，避免 CPU 成为瓶颈。可以使用 top、htop 等工具来监控 CPU 负载。
定期维护： 定期进行 OPTIMIZE TABLE 操作，可以清理碎片，提高性能。但是，OPTIMIZE TABLE 操作会重建表，比较耗时，需要谨慎使用。
备份与恢复： 在进行压缩相关操作之前，务必进行备份，以防万一。恢复时，需要确保 MySQL 版本一致，并且支持相应的压缩算法。
Doublewrite Buffer： 开启压缩后，Doublewrite Buffer 仍然会写入完整的 Page 大小，所以 Doublewrite Buffer 并不能节省空间。
二进制日志 (Binary Log)： 二进制日志会记录压缩后的数据。如果使用基于行的复制 (Row-Based Replication)，那么从库也需要支持相应的压缩算法。

六、总结

InnoDB Page 压缩是一个强大的工具，可以帮助你节省存储空间，提高 IO 效率。但是，它也会带来 CPU 开销。因此，在选择是否开启压缩时，需要权衡 CPU 开销和存储收益。

希望今天的讲座对你有所帮助。如果你有任何问题，欢迎随时提问。谢谢大家！

发表回复 取消回复

发表回复取消回复