MySQL表压缩和页压缩，难道只是空间压缩？

临近春节，相信每个公司都会进行全面巡检，无论是业务层还是数据库层，达到事前预防的目的；今天就来分享一下针对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 缓冲池调大，以便能同时能保存压缩页和未压缩页。

如下是官方文档描述：

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

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

小门道AI

小门道AI是一个提供AI服务的网站

117

查看详情

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

<code class="javascript">CREATE TABLE Transaction （id BINARY(16) PRIMARY KEY,.....)COMPRESSION=ZLIB | LZ4 | NONE;</code>

要使用 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 命令：

<code class="javascript">ALTER TABLE dic_history_202201 COMPRESSION=ZLIB；OPTIMIZE TABLE dic_history_202201;</code>

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

<code class="javascript">ALTER TABLE dic_history_202201 COMPRESSION="None";OPTIMIZE TABLE dic_history_202201;</code>

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

<code class="javascript">mysql>show create table employees\G*************************** 1. row ***************************Table: employeesCreate 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.ibd32768 /data/mysql_8306/data/wjqtest/employees.ibd</code>

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

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

以上就是MySQL表压缩和页压缩，难道只是空间压缩？的详细内容，更多请关注php中文网其它相关文章！