MySQL表压缩和页压缩，难道只是空间压缩？学习心得分享

文章目录[隐藏]

表压缩
TPC 压缩（ Innodb Transaparent PageIO Compression）
表压缩在业务上的使用
小结

临近春节，相信每个公司都会进行全面巡检，无论是业务层还是数据库层，达到事前预防的目的；今天就来分享一下针对MySQL数据存储层面，在数据库存储来不及扩容的情况下，MySQL中的压缩方案；

日常工作中很多业务在表结构设计之初不会考虑存储的设计，只有当业务发展到一定规模才会意识到问题的严重性。而物理存储主要是考虑是否要启用表的压缩功能，默认情况下，所有表都是非压缩的。但说到压缩，总会下意识地认为压缩会导致 MySQL 数据库的性能下降。这个观点说对也不对，需要根据不同场景进行区分。

目前很多引擎表支持压缩，比如 Myisam、InnoDB、TokuDB、MyRocks 。本文主要是针对Innodb引擎表压缩进行说明，针对MyRocks引擎的详细可参考：《RocksDB引擎和Innodb性能对比》。

数据库存储磁盘如果是非SSD的，数据库几乎都是 IO 负载型的，在 CPU 有大量余量的时候，磁盘 IO 的瓶颈就已经凸显出来。而数据的大量存储，尤其是日志型数据，会导致磁盘空间快速增长。硬盘不够用也会在很多业务中凸显出来。一种比较好的方式就诞生了，那就是通过牺牲少量 CPU 资源，采用压缩来减少磁盘空间占用，以及优化 IO 和带宽。尤其针对读多些少的业务。

如果存储磁盘是SSD，数据库的 IO 负载有所降低，但是对于磁盘空间的问题还是没有很好的解决。因此压缩表使用还是非常的广泛。这也就是为什么那么多的引擎都支持压缩的原因。而 innodb 在 MySQL 5.5 的时候就支持了压缩功能，只是压缩比比较低，通常在 50%左右。而 TokuDB 能达到 80%左右，MyRocks 的压缩比能达到 70%左右。

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

表压缩

数据库中的表是由一行行记录（rows）所组成，每行记录被存储在一个页中，在 MySQL 中，一个页的大小默认为 16K，一个个页又组成了每张表的表空间。通常我们认为，如果一个页中存放的记录数越多，数据库的性能越高。这是因为数据库表空间中的页是存放在磁盘上，MySQL 数据库先要将磁盘中的页读取到内存缓冲池，然后以页为单位来读取和管理记录。

一个页中存放的记录越多，内存中能存放的记录数也就越多，那么存取效率也就越高。若想将一个页中存放的记录数变多，可以启用压缩功能。此外，启用压缩后，存储空间占用也变小了，同样单位的存储能存放的数据也变多了。

如果要使用 innodb 压缩前提条件是：innodb_file_per_table 这个参数要启用，innodb_file_format 这个参数设置成 Barracuda。

COMPRESS 页压缩是 MySQL 5.7 版本之前提供的页压缩功能。只要在创建表时指定ROW_FORMAT=COMPRESS，并设置通过选项 KEY_BLOCK_SIZE 设置压缩的比例；如果没有指定 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 资源。

启用表的页压缩功能后，性能有明显损失，因为压缩需要有额外的开销。主要原因是一个压缩页在内存缓冲池中，存在压缩和解压两个页。在 buffer_pool 缓冲池中，压缩的数据通过 KEY_BLOCK_SIZE 的大小的页来保存，如果要提取压缩的数据或者要更新压缩数据对应的列，则会创建一个未压缩页来解压缩数据，然后在数据更新完成后，会将为压缩页的数据重新写入到压缩页中。内存不足的时候，MySQL 会讲对应的未压缩页踢出去。因此如果你启用了压缩功能，你的 buffer_pool 缓冲池中可能会存在压缩页和未压缩页，也可能只存在压缩页。不过可能仍然需要将你的 buffer_pool 缓冲池调大，以便能同时能保存压缩页和未压缩页。

如下是官方文档描述：

In the buffer pool, the compressed data is held in small pages, with a page size based on the KEY_BLOCK_SIZE value. For extracting or updating the column values, MySQL also creates an uncompressed page in the buffer pool with the uncompressed data. Within the buffer pool, any updates to the uncompressed page are also re-written back to the equivalent compressed page. You might need to size your buffer pool to accommodate the additional data of both compressed and uncompressed pages, although the uncompressed pages are evicted from the buffer pool when space is needed, and then uncompressed again on the next access.

总之，COMPRESS 页压缩，适合用于一些对性能不敏感的业务表，例如日志表等

为了解决压缩性能下降的问题，从MySQL 5.7 版本开始推出了 TPC 压缩功能，其利用文件系统的空洞（Punch Hole）特性进行压缩。

TPC 压缩（ Innodb Transaparent PageIO Compression）

可以使用下面的命令创建 TPC 压缩表：

CREATE TABLE Transaction （
id BINARY(16) PRIMARY KEY,
.....
)
COMPRESSION=ZLIB | LZ4 | NONE;

要使用 TPC 压缩，首先要确认当前的操作系统是否支持空洞特性。通常来说，当前常见的操作系统都已支持空洞特性：

在Linux系统上，文件系统块大小是空洞特性的单位大小。因此，只有当页面数据可以压缩到小于或等于InnoDB页面大小减去文件系统块大小时，页面压缩才起作用。例如，如果innodb_page_size=16K，文件系统块大小为4K，则页面数据必须压缩到小于或等于12K，才能生效。

这是因为不同于 COMPRESS 页压缩，TPC 压缩在内存中只有一个 16K 的解压缩后的页，对于缓冲池没有额外的存储开销。

表压缩在业务上的使用

总的来说，对一些对性能不敏感的业务表，例如日志表、账单表等，它们只对存储空间有要求，因此可以使用 COMPRESS 页压缩功能。

在一些较为核心的流水业务表上，更推荐使用 TPC压缩。因为流水信息是一种非常核心的数据存储业务，通常伴随核心业务。如一笔交易，下单、记流水，这就是一个核心业务的模型。

所以，用户对流水表有性能需求。此外，流水又非常大，启用压缩功能可更为有效地存储数据。

若对压缩产生的性能抖动有所担心，我的建议：由于流水表通常是按月或天进行存储，对当前正在使用的流水表不要启用 TPC 功能，对已经成为历史的周期表启用 TPC 压缩功能，如下所示：

需要特别注意的是：通过命令 ALTER TABLE xxx COMPRESSION = ZLIB 可以启用 TPC 页压缩功能，但是这只对后续新增的数据会进行压缩，对于原有的数据则不进行压缩。所以上述ALTER TABLE 操作只是修改元数据，瞬间就能完成。

若想要对整个表进行压缩，需要执行 OPTIMIZE TABLE 命令：

ALTER TABLE dic_history_202201 COMPRESSION=ZLIB；
OPTIMIZE TABLE dic_history_202201;

禁用页面压缩

使用ALTER TABLE将compression设置为None。设置COMPRESSION=None后发生的表空间写入不再使用页压缩。要解压缩现有页面，必须在设置COMPRESSION=None后使用OPTIMIZE table重新生成表。

ALTER TABLE dic_history_202201 COMPRESSION="None";
OPTIMIZE TABLE dic_history_202201;

页压缩元数据

页压缩元数据存储在 INFORMATION_SCHEMA.INNODB_TABLESPACES表中，表中有三列：

FS_BLOCK_SIZE: The file system block size, which is the unit size used for hole punching. #文件系统块大小

FILE_SIZE: The apparent size of the file, which represents the maximum size of the file, uncompressed. #文件的实际大小，表示未压缩文件的最大大小

ALLOCATED_SIZE: The actual size of the file, which is the amount of space allocated on disk. # 文件的实际大小，即磁盘上分配的空间量

mysql>show create table employees\G
*************************** 1. row ***************************
Table: employees
Create Table: CREATE TABLE `employees` (
`emp_no` int NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMPRESSION='zlib'
1 row in set (0.00 sec)

mysql>SELECT SPACE, NAME, FS_BLOCK_SIZE, FILE_SIZE, ALLOCATED_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='wjqtest/employees';
+-------+-------------------+---------------+-----------+----------------+
| SPACE | NAME | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
+-------+-------------------+---------------+-----------+----------------+
| 265 | wjqtest/employees | 4096 | 114688 | 32768 |
+-------+-------------------+---------------+-----------+----------------+
1 row in set (0.00 sec)

# ls -l /data/mysql_8306/data/wjqtest/employees.ibd
-rw-r----- 1 mysql mysql 114688 Jan 11 11:22 /data/mysql_8306/data/wjqtest/employees.ibd

# du --block-size=1 /data/mysql_8306/data/wjqtest/employees.ibd
32768 /data/mysql_8306/data/wjqtest/employees.ibd

如上：在linux系统上，ls -l employees.ibd，以字节为单位显示明显的文件大小（相当于文件大小）。要查看磁盘上实际分配的空间量（相当于分配的大小），使用du –block-size=1 employees.ibd。–block size=1选项以字节而不是块的形式输入分配的空间，查询的结果和INFORMATION_SCHEMA.INNODB_TABLESPACES记录的结果一致。

页压缩限制和使用说明

* 如果文件系统块大小*2>innodb_页面大小，则禁用页面压缩。

* 驻留在共享表空间（包括系统表空间、临时表空间和常规表空间）中的表不支持页面压缩。

* undo/redo日志表空间不支持页面压缩。

* 使用具有较大InnoDB页面大小和相对较小文件系统块大小的页面压缩功能可能会导致写入放大。例如，最大InnoDB页面大小为64KB，文件系统块大小为4KB，这可能会提高压缩，但也可能会增加对缓冲池的需求，从而增加I/O和潜在的写放大。

小结

在进行表结构设计时，除了进行列的选择外，还需要考虑存储的设计，特别是对于表的压缩功能的设计，总结来说：

* COMPRESS 页压缩适合用于性能要求不高的业务表，如日志表等；

* COMPRESS 页压缩内存缓冲池存在压缩和解压的两个页，会影响性能；

* 对存储有压缩需求，又希望性能不要有明显退化，推荐使用 TPC 压缩；

* 通过 ALTER TABLE 启用 TPC 压缩后，还需要执行命令 OPTIMIZE TABLE 才能立即完成空间的压缩。