InnoDB 支持驻留在 file-per-table 表空间中的表的页面级压缩。此功能称为 透明页面压缩。页面压缩通过在 CREATE TABLE 或 ALTER TABLE 中指定 COMPRESSION 属性来启用。支持的压缩算法包括 Zlib 和 LZ4。InnoDB supports page-level compression for tables that reside in file-per-table tablespaces. This feature is referred to as Transparent Page Compression. Page compression is enabled by specifying the COMPRESSION attribute with CREATE TABLE or ALTER TABLE. Supported compression algorithms include Zlib and LZ4.

支持的平台Supported Platforms

页面压缩需要稀疏文件和打孔支持。页面压缩在 Windows 上支持 NTFS，并且在以下 MySQL 支持的 Linux 平台子集上也支持，其中内核级提供打孔支持：Page compression requires sparse file and hole punching support. Page compression is supported on Windows with NTFS, and on the following subset of MySQL-supported Linux platforms where the kernel level provides hole punching support:

页面压缩的工作原理How Page Compression Works

当页面被写入时，它会使用指定的压缩算法进行压缩。压缩后的数据写入磁盘，其中的空洞打孔机制会释放页面末尾的空块。如果压缩失败，数据将按原样写出。When a page is written, it is compressed using the specified compression algorithm. The compressed data is written to disk, where the hole punching mechanism releases empty blocks from the end of the page. If compression fails, data is written out as-is.

Linux 上的空洞打孔大小Hole Punch Size on Linux

在 Linux 系统上，文件系统块大小是用于空洞打孔的单位大小。因此，页面压缩仅在页面数据能够被压缩到小于或等于InnoDB页面大小减去文件系统块大小的情况下才有效。例如，如果innodb_page_size=16K并且文件系统块大小为 4K，则页面数据必须压缩到小于或等于 12K，才能进行空洞打孔。On Linux systems, the file system block size is the unit size used for hole punching. Therefore, page compression only works if page data can be compressed to a size that is less than or equal to the InnoDB page size minus the file system block size. For example, if innodb_page_size=16K and the file system block size is 4K, page data must compress to less than or equal to 12K to make hole punching possible.

Windows 上的空洞打孔大小Hole Punch Size on Windows

在 Windows 系统中，稀疏文件的底层基础设施基于 NTFS 压缩。打孔大小是 NTFS 压缩单元，为 NTFS 群集大小的 16 倍。群集大小及其压缩单元如下表所示：On Windows systems, the underlying infrastructure for sparse files is based on NTFS compression. Hole punching size is the NTFS compression unit, which is 16 times the NTFS cluster size. Cluster sizes and their compression units are shown in the following table:

Windows 系统上的页面压缩仅在页面数据可以压缩到小于或等于 InnoDB 页面大小减去压缩单元大小时才有效。Page compression on Windows systems only works if page data can be compressed to a size that is less than or equal to the InnoDB page size minus the compression unit size.

NTFS 的默认簇大小为 4KB，其压缩单元大小为 64KB。这意味着，对于默认安装的 Windows NTFS 配置，页压缩没有任何好处，因为最大的 innodb_page_size 也是 64KB。The default NTFS cluster size is 4KB, for which the compression unit size is 64KB. This means that page compression has no benefit for an out-of-the box Windows NTFS configuration, as the maximum innodb_page_size is also 64KB.

要在 Windows 上使用页面压缩，文件系统必须使用小于 4K 的簇大小创建，并且 innodb_page_size 必须至少是压缩单元大小的两倍。例如，要在 Windows 上使用页面压缩，可以将文件系统构建为 512 字节的簇大小（其压缩单元为 8KB），并使用 innodb_page_size 值为 16K 或更大的值初始化 InnoDB。For page compression to work on Windows, the file system must be created with a cluster size smaller than 4K, and the innodb_page_size must be at least twice the size of the compression unit. For example, for page compression to work on Windows, you could build the file system with a cluster size of 512 Bytes (which has a compression unit of 8KB) and initialize InnoDB with an innodb_page_size value of 16K or greater.

启用页面压缩Enabling Page Compression

要启用页面压缩，请在 CREATE TABLE 语句中指定 COMPRESSION 属性。例如：To enable page compression, specify the COMPRESSION attribute in the CREATE TABLE statement. For example:

CREATE TABLE t1 (c1 INT) COMPRESSION="zlib";

您还可以在ALTER TABLE语句中启用页面压缩。不过，ALTER TABLE ... COMPRESSION仅更新表空间的压缩属性。在设置新压缩算法之后对表空间的写入会使用新设置，但要将新压缩算法应用到现有页面，必须使用OPTIMIZE TABLE重建表。You can also enable page compression in an ALTER TABLE statement. However, ALTER TABLE ... COMPRESSION only updates the tablespace compression attribute. Writes to the tablespace that occur after setting the new compression algorithm use the new setting, but to apply the new compression algorithm to existing pages, you must rebuild the table using OPTIMIZE TABLE.

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

禁用页面压缩Disabling Page Compression

要禁用页面压缩，请使用 ALTER TABLE 设置 COMPRESSION=None。在设置 COMPRESSION=None 之后，对表空间的写入将不再使用页面压缩。要解压现有页面，必须在设置 COMPRESSION=None 后使用 OPTIMIZE TABLE 重建表。To disable page compression, set COMPRESSION=None using ALTER TABLE. Writes to the tablespace that occur after setting COMPRESSION=None no longer use page compression. To uncompress existing pages, you must rebuild the table using OPTIMIZE TABLE after setting COMPRESSION=None.

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

页面压缩元数据Page Compression Metadata

页面压缩元数据可以在信息模式 INNODB_TABLESPACES 表中找到，具体列如下：Page compression metadata is found in the Information Schema INNODB_TABLESPACES table, in the following columns:

在下面的示例中，从信息模式 INNODB_TABLESPACES 表中检索 employees 表的页面压缩元数据。In the following example, page compression metadata for the employees table is retrieved from the Information Schema INNODB_TABLESPACES table.

# Create the employees table with Zlib page compression

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)
) COMPRESSION="zlib";

# Insert data (not shown)

# Query page compression metadata in INFORMATION_SCHEMA.INNODB_TABLESPACES

mysql> SELECT SPACE, NAME, FS_BLOCK_SIZE, FILE_SIZE, ALLOCATED_SIZE FROM
       INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='employees/employees'\G
*************************** 1. row ***************************
SPACE: 45
NAME: employees/employees
FS_BLOCK_SIZE: 4096
FILE_SIZE: 23068672
ALLOCATED_SIZE: 19415040

employees 表的页面压缩元数据显示，表面文件大小为 23068672 字节，而实际文件大小（经过页面压缩）为 19415040 字节。文件系统块大小为 4096 字节，这是用于洞打孔的块大小。Page compression metadata for the employees table shows that the apparent file size is 23068672 bytes while the actual file size (with page compression) is 19415040 bytes. The file system block size is 4096 bytes, which is the block size used for hole punching.

使用页面压缩识别表Identifying Tables Using Page Compression

要识别启用了页面压缩的表，您可以检查信息模式TABLES表的CREATE_OPTIONS列，以查看是否在表定义中使用了COMPRESSION属性：To identify tables for which page compression is enabled, you can check the Information Schema TABLES table's CREATE_OPTIONS column for tables defined with the COMPRESSION attribute:

mysql> SELECT TABLE_NAME, TABLE_SCHEMA, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES 
       WHERE CREATE_OPTIONS LIKE '%COMPRESSION=%';
+------------+--------------+--------------------+
| TABLE_NAME | TABLE_SCHEMA | CREATE_OPTIONS     |
+------------+--------------+--------------------+
| employees  | test         | COMPRESSION="zlib" |
+------------+--------------+--------------------+

SHOW CREATE TABLE同样会显示COMPRESSION属性（如果使用了的话）。SHOW CREATE TABLE also shows the COMPRESSION attribute, if used.

页面压缩的限制和使用说明Page Compression Limitations and Usage Notes