停止使用innodb table compressionALTER TABLE ... ROW_FORMAT=COMPRESSED换成page compressionALTER TABLE ... COMPRESSION='zlib' #33
Description
某德国人克里斯蒂安·科恩托普曾于前年在其部落格中指出：
我从来没有太多理由使用表压缩，所以我基本上忽略了 MySQL 中的主题。我知道 MySQL 从 5.1 开始就有表压缩功能，但我也知道实现起来非常复杂，而且所有数据都会双重存储。还有页面压缩，这是 5.7 引入的功能，它取代了表压缩并且效果更好。
表压缩
表压缩 在 MySQL 5.1 及更新版本中可用。它通过设置 InnoDB 表ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8或类似的表来使用，以获得更小的密钥块大小。INFORMATION_SCHEMA.%CMP%中有很多状态表可以用来监控它。
表压缩创建更小的页面（以您指定的大小KEY_BLOCK_SIZE），并从缓冲池加载和存储这种更小大小的压缩页面。在加载时，缓冲池中的第二个未压缩页面被分配，数据被解压缩并放入这个辅助页面以供使用。
在写入时，修改后的未压缩页面被重新压缩并放回压缩缓冲池页面。由于这种情况发生在写入而不是检查点上，因此这对于写入很多的表来说并不理想。压缩和解压缩发生在查询线程中，即您的连接线程，因此是单线程的。
如果缓冲池空间紧张，未压缩的页面可以被逐出，并根据需要通过再次解压缩它们来重新创建。
为了能够在所有可能的关闭情况下崩溃恢复 MySQL，压缩页面也会在检查点时写入重做日志。这也会导致更大的重做日志，可能需要扩大规模以允许额外的流量。
页面压缩
MySQL 5.7 版本中已经添加了页面压缩。这是 MySQL 中处理数据压缩的一种不同方式：一个转换器阶段被插入到 MySQL 的 IO 处理程序中，它在写入时压缩页面，并在读取时将数据解压缩到缓冲池页面中。这是一种更简单的方法，它还与服务器的更少部分进行交互。
但是由于页面在磁盘上仍然是 16 KB，因为它们在内存中，因此需要一个额外的文件系统功能来真正节省空间：文件系统中的打孔支持。压缩页面将小于 16 KB，因此在 16 KB 末尾未使用的所有文件系统块将被操作系统标记为“未使用”。这适用于所有现代 Linux 内核、ext4 和 xfs 文件系统（可能还有许多其他文件系统）。由于 NTFS 内部处理事物的方式，它不能很好地与标准 NTFS 文件系统一起使用。
因为页面压缩非常简单，所以配置起来也非常简单：要为表启用页面压缩，请使用 设置 InnoDB 表COMPRESSION="zlib"。所有新写入该表的页面都将被压缩。旧数据保持未压缩状态。
为了压缩刚刚切换到页面压缩的表中的所有页面，在其上运行OPTIMIZE TABLE。这将使用新选项重新创建表。
页面级压缩的写入路径将自身插入到InnoDB 页面清理器 中。默认情况下，这个数字是 4，最多可以有 64 个。如果你的数据库向压缩表写入大量数据，增加这个数字可能会有用。由于压缩使用 CPU，因此将数字设置为大于可用内核数是没有用的。
读取由查询线程完成，除非它们不是（当发生预读时它们不是，在这种情况下它是由后台 IO 线程完成的 ）。
没有太多监控到位，也几乎不需要，因为页面压缩就简单多了。
小心文件副本
在我们的文件系统中，文件系统块大小的粒度是 4 KB。打孔在块级别进行，因此我们可以释放 16 KB 页面的 12 KB、8 KB 或 4 KB。即使页面中的数据压缩到例如 9 KB，它仍将使用 4 个文件系统块中的 3 个 4 KB 文件系统块构成一个页面的文件系统存储。所以 MySQL 只能将 4 KB 的空间还给操作系统。
当您天真地复制文件时，文件中的漏洞可能会被填充：keks2.ibd在没有漏洞识别工具的情况下进行复制将填补这些漏洞。这意味着：72 MB 源文件的副本将成为 252 MB 目标文件。复制文件的安全方法是使用适当的选项进行 rsync。
文件中的漏洞也会导致磁盘寻道。这对任何 SSD 或 NVME 闪存都不是问题，但会损害硬盘的性能。另一方面，现在是 2021 年，仍然在 HDD 上运行数据库的人可能配不上更好的选择。
根据mysql文档：
table: https://dev.mysql.com/doc/refman/8.0/en/innodb-compression-internals.html
page: https://dev.mysql.com/doc/refman/8.0/en/innodb-page-compression.html
名词表：
https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_compressed_row_format
为 InnoDB 表启用数据和索引压缩 的行格式。大字段存储在远离包含其余行数据的页面的地方，如 动态行格式。索引页和大字段都被压缩，从而节省了内存和磁盘。根据数据的结构，内存和磁盘使用量的减少可能会或可能不会超过在使用数据时解压缩数据的性能开销。有关使用详细信息，请参阅第 15.9 节，“InnoDB 表和页面压缩”。
https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_transparent_page_compression
MySQL 5.7.8 中添加的一项功能，允许对驻留在 file-per-table 表空间中的 InnoDB 表进行页级压缩。通过使用 CREATE TABLE 或 ALTER TABLEhttps://mariadb.com/kb/en/innodb-page-compression/ 指定 COMPRESSION 属性启用页面压缩。有关更多信息，请参阅第 15.9.2 节，“InnoDB 页面压缩”。
https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_sparse_file
一种通过将表示空块的元数据写入磁盘而不是写入实际空白空间来更有效地使用文件系统空间的文件类型。InnoDB 透明页面压缩功能依赖于稀疏文件支持。有关更多信息，请参阅 第 15.9.2 节，“InnoDB 页面压缩”。
知名的mysql fork mariadb文档进一步指出：
- InnoDB 页面压缩可用于任何存储设备和任何文件系统。
- InnoDB 页面压缩在支持稀疏文件的文件系统上最有效。有关详细信息，请参阅使用稀疏文件节省存储空间。
- InnoDB 页面压缩对固态驱动器 (SSD) 和其他闪存存储最有利。有关详细信息，请参阅针对闪存存储优化。
- 当您的存储设备和文件系统支持原子写入时，InnoDB 页面压缩性能最佳，因为这允许禁用InnoDB 双写缓冲区。有关详细信息，请参阅原子写入支持。
与COMPRESSED行格式的比较
InnoDB 页面压缩是一种压缩 InnoDB 表的现代方法。它类似于 InnoDB 的COMPRESSED行格式，但它有很多优点。一些差异是：
- 使用 InnoDB 页面压缩，压缩页面在从表空间文件中读取后立即解压缩，并且只有未压缩的页面存储在缓冲池中。相比之下，使用 InnoDB 的COMPRESSED行格式，压缩页面在从表空间文件中读取后立即解压缩，并且未压缩和压缩页面都存储在缓冲池中。这意味着COMPRESSED行格式在缓冲池中使用比 InnoDB 页面压缩更多的空间。
译注：mysql文档的某个角落也同样承认了这一点：由于处理压缩数据有时涉及同时在内存中保留页面的压缩版本和未压缩版本，因此在对 OLTP 样式的工作负载使用压缩时，请准备好增加innodb_buffer_pool_size配置选项的值。
以及在15.9.1.3 调整 InnoDB 表的压缩中：具有快速多核 CPU 的多用户环境中运行时，情况尤其如此。当压缩表的页面在内存中时，MySQL 通常在缓冲池中使用额外的内存，通常为 16KB，用于页面的未压缩副本。自适应 LRU 算法试图平衡压缩页面和未压缩页面之间的内存使用，以考虑工作负载是以 I/O 绑定还是 CPU 绑定方式运行。尽管如此，与内存高度受限的配置相比，使用压缩表时，具有更多内存专用于缓冲池的配置往往运行得更好。 - 使用 InnoDB 页面压缩，页面在写入表空间文件之前被压缩。相反，使用 InnoDB 的COMPRESSED行格式，页面在任何更改后立即重新压缩，并且压缩页面与未压缩页面一起存储在缓冲池中。然后这些更改偶尔会刷新到磁盘。这意味着COMPRESSED行格式比 InnoDB 页面压缩更频繁地重新压缩数据。
- 使用 InnoDB 页面压缩，支持多种压缩算法。相反，对于 InnoDB 的COMPRESSED行格式，zlib是唯一受支持的压缩算法。这意味着COMPRESSED行格式具有比 InnoDB 页面压缩更少的压缩选项。
通常，InnoDB 页面压缩优于COMPRESSED行格式。
使用稀疏文件节省存储空间
当使用 InnoDB 页面压缩时，InnoDB 可能仍将压缩页面以未压缩页面的原始大小写入表空间文件，这相当于innodb_page_size系统变量的值。这是设计使然，因为当 InnoDB 的 I/O 代码需要从磁盘读取页面时，它只能读取整个页面大小。然而，这显然不是最优的。
在支持稀疏文件的文件系统上，这个问题通过使用打孔技术将表空间文件写成稀疏文件来解决。使用打孔技术，InnoDB 只会将实际压缩页面大小写入表空间文件，与扇区大小对齐。页面的其余部分被裁剪。
这种打孔技术允许 InnoDB 从磁盘读取压缩页面作为完整页面大小，即使压缩页面在文件系统上占用的空间确实较少。
使用稀疏文件有一些潜在的缺点：
一些实用程序可能需要特殊选项才能以有效的方式处理稀疏文件。
大多数现有文件系统unlink()稀疏文件的速度很慢。因此，如果表空间文件是稀疏文件，则删除表可能会非常慢。
Linux 上的稀疏文件支持
在 Linux 上，以下文件系统支持稀疏文件：
- ext3
- ext4
- xfs
- btrfs
- nvmfs
在 Linux 上，文件系统需要支持带有和标志的fallocate()系统调用。例如：FALLOC_FL_PUNCH_HOLEFALLOC_FL_KEEP_SIZE
fallocate(file_handle, FALLOC_FL_PUNCH_HOLE | FALLOC_FL_KEEP_SIZE, file_offset, remainder_len);
某些 Linux 实用程序可能需要特殊选项才能有效地处理稀疏文件。例如：
- ls实用程序在以默认行为执行时将报告表空间文件的非稀疏大小，但ls -s将报告为表空间文件分配的实际存储量。
- cp实用程序非常擅长自动检测稀疏文件，但如果不需要自动检测，它还提供cp --sparse=always和cp --sparse=never选项。
- tar实用程序在以默认行为执行时将以非稀疏大小归档稀疏文件，但tar --sparse将自动检测稀疏文件，并以稀疏大小归档它们。
Windows 上的稀疏文件支持
在 Windows 上，以下文件系统支持稀疏文件：
- NTFS
在 Windows 上，文件系统需要使用FSCTL_SET_SPARSE和FSCTL_SET_ZERO_DATA控制代码支持DeviceIoControl()函数。例如：
DeviceIoControl(file_handle, FSCTL_SET_SPARSE, inbuf, inbuf_size,
outbuf, outbuf_size, NULL, &overlapped)
...
DeviceIoControl(file_handle, FSCTL_SET_ZERO_DATA, inbuf, inbuf_size,
outbuf, outbuf_size, NULL, &overlapped)译注：mysql文档中称在默认配置下的NTFS卷上使用page compression没有好处，可能mariadb改进了这一点：
仅当页面数据可以压缩到小于或等于 InnoDB页面大小减去压缩单元大小的大小时，Windows 系统上的页面压缩才有效。
默认 NTFS 簇大小为 4KB，压缩单元大小为 64KB。这意味着页面压缩对于开箱即用的 Windows NTFS 配置没有任何好处，因为最大值 innodb_page_size也是 64KB。
要在 Windows 上使用页面压缩，必须使用小于 4K 的簇大小创建文件系统，并且 innodb_page_size必须至少是压缩单元大小的两倍。例如，要在 Windows 上进行页面压缩，您可以构建簇大小为 512 字节（压缩单元为 8KB）的文件系统，并使用 16K 或更大InnoDB的 innodb_page_size值进行初始化。
配置 InnoDB 以使用稀疏文件
在MariaDB 10.3及更高版本中，InnoDB 使用打孔技术来创建当底层文件系统支持稀疏文件时自动使用的稀疏文件。
在MariaDB 10.2及之前的版本中，可以通过配置innodb_use_trim和innodb_use_fallocate系统变量，将 InnoDB 配置为使用打孔技术创建稀疏文件。在启动服务器之前，可以在选项文件的服务器选项组中设置这些系统变量。例如：
[mariadb]
...
innodb_use_trim=ON
innodb_use_fallocate=ON针对闪存存储进行了优化
InnoDB 页面压缩旨在优化固态驱动器 (SSD) 和其他闪存。
InnoDB 页面压缩最初是与Fusion-io合作开发的。因此，它最初被设计为在使用NVMFS的FusionIO 设备上工作得最好。Fusion-io此后被Western Digital收购，他们决定不再继续支持NVMFS。
然而，InnoDB 页面压缩仍然可能在固态驱动器 (SSD) 和其他闪存存储上得到最优化。
InnoDB 页面压缩在硬盘驱动器 (HDD) 上没有任何问题。但是，由于其压缩依赖于使用稀疏文件，因此数据在磁盘上可能有些碎片化。这种碎片可能会损害 HDD 的性能，因为它们处理随机读取和写入的速度比闪存存储慢得多。（译注：mysql文档也指出了这一点： 使用具有较大 InnoDB页面大小和相对较小文件系统块大小的页面压缩功能可能会导致写入放大。例如，InnoDB 64KB 的最大页面大小和 4KB 的文件系统块大小可能会提高压缩率，但也可能会增加对缓冲池的需求，从而导致 I/O 增加和潜在的写入放大。）
FYI
15年首次mysql5.7.8首次引入page compression时mysql员工的部落格： https://dev.mysql.com/blog-archive/innodb-transparent-page-compression/
对table compression给innodb buffer pool所产生的换页压力的性能测试：https://jfg-mysql.blogspot.com/2022/12/free-pages-consumption-by-innodb-table-compression.html
pinterest于16年利用14年某阿里员工Weixiang Zhai（其推特followed by @cw1997 ）对知名商业mysql fork Percona Server所增加的针对存储着json的TEXT列使用zlib压缩存储的功能代替page compression获得更高的解压性能： https://medium.com/pinterest-engineering/evolving-mysql-compression-part-1-7f8b09666589
亲测
测试用表结构：
CREATE TABLE `all_page_compress_lz4` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`discoveredAt` int unsigned NOT NULL,
`fid` int unsigned NOT NULL,
`portrait` varchar(255) NOT NULL,
`moderatorType` text,
PRIMARY KEY (`id`),
KEY `fid` (`fid`),
KEY `portrait` (`portrait`)
) ENGINE=InnoDB
字段moderatorType有着巨大多NULL值因此这也有利于压缩（因为每8个NULL=1byte）：
SELECT COUNT(*) FROM all WHERE moderatorType IS NOT NULL; -- 773142
SELECT COUNT(*) FROM all WHERE moderatorType IS NULL; -- 62416816使用mysql文档中提供的sql获得的tablespace实际大小：
SELECT SPACE, NAME, FS_BLOCK_SIZE, FILE_SIZE, ALLOCATED_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE "%all%";| NAME | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
|---|---|---|---|
| all_page_compress_zlib | 4096 | 9776922624 | 4889554944 |
| all_page_compress_lz4 | 4096 | 9776922624 | 6127190016 |
| all_table_compress | 4096 | 5515509760 | 5517258752 |
| all | 4096 | 9776922624 | 9780563968 |
测试执行5次经典full table scan之COUNT(*) without WHERE：
SELECT COUNT(*) FROM all; -- 40 s 345 ms
SELECT COUNT(*) FROM all; -- 38 s 393 ms
SELECT COUNT(*) FROM all; -- 36 s 128 ms
SELECT COUNT(*) FROM all; -- 36 s 926 ms
SELECT COUNT(*) FROM all; -- 36 s 671 ms
SELECT COUNT(*) FROM all_page_compress_lz4; -- 34 s 615 ms
SELECT COUNT(*) FROM all_page_compress_lz4; -- 31 s 73 ms
SELECT COUNT(*) FROM all_page_compress_lz4; -- 30 s 47 ms
SELECT COUNT(*) FROM all_page_compress_lz4; -- 31 s 494 ms
SELECT COUNT(*) FROM all_page_compress_lz4; -- 33 s 498 ms
SELECT COUNT(*) FROM all_page_compress_zlib; -- 1 m 15 s 331 ms
SELECT COUNT(*) FROM all_page_compress_zlib; -- 58 s 981 ms
SELECT COUNT(*) FROM all_page_compress_zlib; -- 52 s 590 ms
SELECT COUNT(*) FROM all_page_compress_zlib; -- 52 s 536 ms
SELECT COUNT(*) FROM all_page_compress_zlib; -- 47 s 535 ms
SELECT COUNT(*) FROM all_table_compress; -- 1 m 2 s 515 ms
SELECT COUNT(*) FROM all_table_compress; -- 1 m 0 s 358 ms
SELECT COUNT(*) FROM all_table_compress; -- 1 m 23 s 651 ms
SELECT COUNT(*) FROM all_table_compress; -- 1 m 11 s 868 ms
SELECT COUNT(*) FROM all_table_compress; -- 1 m 0 s 951 ms
TL;DR
- 如果不那么在乎空间但希望加快table scan耗时建议无脑使用
ALTER TABLE ... COMPRESSION='lz4' - 如果在乎空间则使用zlib（符合这篇
Zhe Zhang在CERN所作的PPT和stackoverflow人的预期） - 除此之外maraidb还通过插件的形式提供了更多可选压缩算法
- 无论如何都避免使用table compression