MySQL 压缩技术使用方法--云数据库 MySQL 版-火山引擎

文档中心

导航

MySQL 压缩技术使用方法

最近更新时间：2025.01.09 15:35:23首次发布时间：2025.01.08 17:01:40

MySQL 压缩技术简介

在现代数据库管理系统中，数据存储和处理的效率至关重要。随着数据量的不断增长，如何有效地压缩数据以节省存储空间和提高查询性能成为了一个重要的研究课题。MySQL 作为广泛使用的开源数据库系统，也在不断探索和优化数据压缩技术，以满足用户对高效存储和快速访问的需求。
本文将介绍 MySQL 中的数据压缩技术，重点讨论表压缩、页压缩及其在实际应用中的使用方法。

MySQL 表压缩 (Table Compression)

MySQL 压缩表简介

压缩表是 MySQL 中一种特殊的表类型，它通过对指定表的数据进行压缩来减少存储空间的使用。压缩表适用于存储大量重复数据或数据量较大但访问频率较低的情况。

如何使用 MySQL 压缩表

创建压缩表
1. 表压缩 - 在创建表时，可以使用 ROW_FORMAT=COMPRESSED 选项来创建压缩表。例如：
```
mysql> CREATE TABLE my_table (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
) ROW_FORMAT=COMPRESSED  KEY_BLOCK_SIZE=4;
```
2. 插入数据
  向压缩表中插入数据与向普通表中插入数据的方式相同。
查询数据
查询压缩表中的数据时，MySQL 会自动解压缩数据。

检查查看空间使用

可以通过以下命令获取表查看 TOTAL_MB 压缩前后表的空间使用情况

mysql> select table_schema, table_name, table_rows,  round(data_length / 1024 / 1024)+round(index_length / 1024 / 1024)+round(data_free / 1024 / 1024) TOTAL_MB, create_options from information_schema.tables where table_name='my_table';
+--------------+---------------+------------+----------+----------------+
| table_schema | table_name    | table_rows | TOTAL_MB | create_options |
+--------------+---------------+------------+----------+----------------+
| Test         | my_table      |     100000 |       35 |                |
+--------------+---------------+------------+----------+----------------+

mysql> analyze table test_compress; -- 更新表统计信息

mysql> select table_schema, table_name, table_rows,  round(data_length / 1024 / 1024)+round(index_length / 1024 / 1024)+round(data_free / 1024 / 1024) TOTAL_MB, create_options from information_schema.tables where table_name='my_table';
+--------------+---------------+------------+----------+----------------+
| table_schema | table_name    | table_rows | TOTAL_MB | create_options |
+--------------+---------------+------------+----------+----------------+
| Test         | my_table      |     100000 |       20 |                |
+--------------+---------------+------------+----------+----------------+

MySQL 压缩表特点

压缩：

表和索引的压缩：MySQL 支持对表和索引进行压缩，这可以显著减少存储空间的使用。对于读多写少的表提升较为明显
压缩的优缺点：压缩可以减少磁盘空间的使用和 I/O 操作，但也会增加 CPU 的负担，因为数据在读取和写入时需要进行压缩和解压缩操作。在使用压缩表的时候，可以适当调大 buffer_pool_size ，以增强性能。

最佳实践：

权衡和测试：在使用压缩和索引时，需要根据具体的应用场景进行权衡和测试。不同的应用场景可能需要不同的优化策略。
监控和优化：定期监控数据库性能，识别性能瓶颈，如CPU升高，并进行如扩大Buffer pool 相应的优化。确保压缩和索引的使用能够带来实际的性能提升。

动态调整：根据业务需求和数据量的变化，动态调整压缩和索引策略，以保持最佳的性能。

如何在压缩表和非压缩表之间转换

将非压缩表转换为压缩表。
可以使用 ALTER TABLE 语句将非压缩表转换为压缩表。例如：
```
ALTER TABLE my_table ROW_FORMAT=COMPRESSED;
```
将压缩表转换为非压缩表。
同样可以使用 ALTER TABLE 语句将压缩表转换为非压缩表。例如：
```
ALTER TABLE my_table ROW_FORMAT=DYNAMIC;
```

在转换表类型时，需要注意以下几点：

转换表类型可能需要一定的时间，具体时间取决于表的大小和数据量。
在转换表类型之前，应该备份数据，以防止数据丢失。
转换表类型后，可能需要重新创建索引，以确保查询性能。

注意事项

系统表不支持对表级别的压缩。
在 innodb_file_per_table 的表空间或者 general 表空间里，可以使用表压缩。
KEY_BLOCK_SIZE 原则上不超过 innodb_page_size；但也不能过小，如果 KEY_BLOCK_SIZE 如果指定的值太小，则当数据值无法压缩到足以容纳每页中的多行时，重新组织页面会产生额外的开销；因此会有硬性的规定，KEY_BLOCK_SIZE 的值如果太小，会导致 CREATE 或者 ALTER SQL 执行失败。
FILE_BLOCK_SIZE 没有设置的时候，默认值是 innodb_page_size ，这时候不允许使用 COMPREESSION 功能。
压缩表适用于存储大量重复数据或数据量较大但访问频率较低的情况。如果表中的数据经常被更新或查询频率较高，可能不适合使用压缩表。
在使用压缩表时，应该根据实际情况评估其优缺点，并选择最适合的表类型。
在转换表类型时，应该谨慎操作，并确保备份数据以防止数据丢失。

MySQL 页压缩/透明压缩（Page Compression）

MySQL 页压缩简介

与表压缩以数据行为维度压缩方式不同，MySQL 页压缩（Page Compression）专门用于 InnoDB 存储引擎。它通过压缩 InnoDB 缓冲池中的单个页来减少存储空间的使用和 I/O 操作

如何使用 MySQL 页压缩

使用页压缩：

确认打开 innodb_file_per_table=on （火山默认为ON）

指定压缩算法 COMPRESSION='zlib'；

CREATE TABLE `my_table` (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
) ENGINE=InnoDB COMPRESSION='zlib'；

通过ALTER TABLE 将表结构变更为

ALTER TABLE my_table COMPRESSION="zlib";
OPTIMIZE TABLE my_table;

压缩算法：目前火山支持 zlib 和 lz4 算法。

MySQL 页压缩特点

页压缩只会压缩页面的一部分（页面本身与页面尾部），不会压缩页面头部。所以与表压缩相比有以下区别：

表大小会大于表压缩。
页压缩CPU占用会低于表压缩，压缩/解压速度也会高于表压缩。
I/O 占用相对于压缩前会减少。

注意事项

操作系统需要对 sparse file 以及 hole punching 的支持（当前火山环境均支持）。
在共享表空间中不支持页压缩。

关键参数

参数	Scope	参数说明
innodb_compression_failure_threshold_pct	GLOBAL	当压缩失败的次数 / 总压缩次数达到该值时，MySQL 会动态增加每个页面的额外可用空间，以此来提高压缩成功率。
innodb_compression_pad_pct_max	GLOBAL	每个页面中预留空间占总空间的比例。
innodb_compression_level	GLOBAL	压缩时使用的 zlib 算法的压缩级别（0-9，默认值为 6），更高的压缩级别意味着更高的压缩率与更高的 CPU 消耗。
innodb_log_compressed_pages	GLOBAL	是否在 redolog 中记载页面的 re-compression 信息，默认值为 ON。
innodb_file_per_table	GLOBAL	控制每个 InnoDB 表是否使用单独的表空间文件。启用时，每个表都有一个独立的 `.ibd` 文件。该参数为云数据库 MySQL 版的运维参数，默认值为 ON，用户无需修改。说明设置该参数为 ON 是打开页压缩的前置条件。

如何选择 MySQL 压缩方式

表压缩

适用情况

只读或读多写少的表：表压缩适用于只读或读多写少的表，因为它在写操作时的开销较大。
大表：表压缩对于非常大的表效果显著，可以显著减少磁盘空间的使用。
备份和归档：适用于备份和归档数据，因为这些数据通常不需要频繁修改。

不适用情况

频繁写操作：如果表的写操作非常频繁，表压缩可能会导致性能下降。
需要快速响应的应用：表压缩可能会增加响应时间，不适用于需要快速响应的应用。

页压缩

适用情况

磁盘空间有限：页压缩可以显著减少磁盘空间的使用。
读写操作频繁：页压缩在读写操作频繁的情况下表现较好，因为它可以减少 I/O 操作。
CPU 资源充足：页压缩需要更多的 CPU 资源来进行压缩和解压缩操作。

不适用情况

CPU 资源紧张：如果服务器的 CPU 资源有限，页压缩可能会导致性能下降。
压缩失败率高：如果压缩失败率较高，可能需要调整 innodb_compression_failure_threshold_pct 参数，或者考虑其他压缩方法。

总结

表压缩：适用于只读或读多写少的大表和备份归档数据。
页压缩：适用于磁盘空间有限、读写操作频繁且 CPU 资源充足的情况。
懒人选项：如果不确定，可以优先选择页压缩，并根据实际情况调整参数。

参考资料

https://www.percona.com/blog/mysql-compression-indexes-and-two-smoking-barrels/
https://dev.mysql.com/doc/refman/8.4/en/innodb-compression-usage.html