15.9 InnoDB Table and Page Compression

15.9 InnoDB Table and Page Compression

15.9.1 InnoDB Table Compression

15.9.2 InnoDB Page Compression

本节提供有关InnoDB表压缩和InnoDB页面压缩特性的信息。

使用InnoDB的压缩特性，可以创建数据以压缩形式存储的表。压缩可以帮助提高性能和可伸缩性。压缩意味着在磁盘和内存之间传输的数据更少，占用的磁盘和内存空间也更少。压缩对于SSD存储设备尤其重要，因为它们的容量往往低于HDD设备。

15.9.1 InnoDB Table Compression

15.9.1.1 Overview of Table Compression

15.9.1.2 Creating Compressed Tables

15.9.1.3 Tuning Compression for InnoDB Tables

15.9.1.4 Monitoring InnoDB Table Compression at Runtime

15.9.1.5 How Compression Works for InnoDB Tables

15.9.1.6 Compression for OLTP Workloads

15.9.1.7 SQL Compression Syntax Warnings and Errors

本节介绍InnoDB表压缩，InnoDB表位于file_per_table表空间或general表空间中。使用ROW_FORMAT= compress属性与CREATE Table或ALTER Table一起使用。

15.9.1.1 Overview of Table Compression

由于处理器和缓存内存的速度比磁盘存储设备快，许多工作负载都被限制在磁盘这里。数据压缩可以减少数据库大小、减少I/O和提高吞吐量。压缩对于读密集型应用程序特别有价值，因为系统具有足够的RAM将经常使用的数据保存在内存中。

使用ROW_FORMAT=compression创建的InnoDB表在磁盘上使用比配置的innodb_page_size值小的页面大小。更小的页面需要更少的I/O来读写磁盘，这对于SSD设备特别有用。

通过KEY_BLOCK_SIZE参数指定压缩的页面大小。由于系统表空间不能存储压缩的表，因此file-per-tabletablespace orgeneral tablespace与system tablespace有着不同的页面大小要求。

无论KEY_BLOCK_SIZE值是多少，压缩级别都是相同的。当为KEY_BLOCK_SIZE指定更小的值时，将获得越来越小的页面的I/O好处。但是，如果指定的值太小，那么当数据值不能被压缩到每个页面可以容纳多个行时，就会有额外的开销来重新组织页面。基于每个索引的键列的长度，对于表的KEY_BLOCK_SIZE可以有一个硬限制。指定一个值当他太小时，CREATE TABLE或ALTER TABLE语句就会失败。

在缓冲池中压缩数据保存在小页面中，页面大小基于KEY_BLOCK_SIZE的值。为了提取或更新列值，MySQL 还在缓冲池中创建未压缩页面存储未压缩数据。在缓冲池中，对未压缩页面的任何更新也被重新写入到等效的压缩页面。可能需要调整缓冲池的大小，以容纳压缩页面和未压缩页面的额外数据，当需要空间时，未压缩页面将从缓冲池中删除，然后在下次访问时再次解压。

15.9.1.2 Creating Compressed Tables

压缩表可以在file-per-tabletablespaces中创建，也可以在general tablespaces中创建。InnoDB 系统表空间没有表压缩功能。

Creating a Compressed Table in File-Per-Table Tablespace

要在file-per-table tablespace的文件中创建压缩表，必须启用innodb_file_per_table。

在配置了innodb_file_per_table选项之后，在CREATE TABLE或ALTER TABLE语句中指定ROW_FORMAT = compression子句或KEY_BLOCK_SIZE子句，或两者都指定，以便在每个表空间的文件中创建压缩表。

# CREATE TABLE t1 (c1 INT PRIMARY KEY) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8：

Restrictions on Compressed Tables

压缩表不能存储在InnoDB 系统表空间中。

一般的表空间可以包含多个表，但是压缩的表和未压缩的表不能在同一个通用表空间中共存。

压缩应用于整个表及其所有相关索引，而不是单个行，尽管子句名为ROW_FORMAT。

InnoDB 不支持压缩临时表。启用 innodb_strict_mode 时，如果创建临时表指定 ROW_FORMAT= compress 或 KEY_BLOCK_SIZE，则返回错误。如果禁用 innodb_strict_mode 则会发出警告，并使用非压缩行格式创建临时表。

15.9.1.3 Tuning Compression for InnoDB Tables

通常InnoDB Data Storage and Compression可以保证系统在压缩存储的数据上运行良好，但是压缩的效率取决于数据的性质。you can make decisions that affect the performance of compressed tables：

# 哪些表需要被压缩

# 压缩页的大小

# 是否根据运行时性能特征(如系统用于压缩和解压数据的时间)调整缓冲池的大小。工作负载更像是data warehouse(primarily queries) 还是 OLTP 系统(查询和 DML 的组合)。

如果系统在压缩表上执行DML 操作，并且数据的分布式方式导致运行时昂贵的压缩失败，可以调整其他配置选项。

When to Use Compression

通常，在包含合理数量的字符串列的表上压缩运行的良好，在这些表中，读取数据的频率要比写入数据的频率高得多。在决定压缩哪些表时，考虑以下因素：

Data Characteristics and Compression

压缩效率高低的一个关键决定因素是数据本身的性质。回想一下，压缩是通过识别数据块中重复的字节字符串来工作的。最坏的情况是完全随机的数据。典型数据通常具有重复的值，因此可以有效地压缩。字符串通常压缩得很好，无论是在CHAR、VARCHAR、TEXT 还是 BLOB 列中定义的数据。

可以选择是否为每个InnoDB 表打开压缩。使用相同的压缩页面大小。对于有长行的情况，使用压缩可能导致长列值被存储在“页外”。

要想知道对某个表的压缩效率可以先使用GZIP 进行压缩然后使用压缩算法进行压缩，比较两者的差别。或者是将包含同样数据的压缩表与未压缩表的 .ibd 文件对比。

简单的总结一下：压缩其实分为两类，一类是在数据库中压缩（分为压缩表跟未压缩表），另一类是应用程序压缩。也就是先进行压缩然后将压缩后的数据存储在数据库中。不建议同时使用两种压缩在同一个数据存储中，也就是说对同一个表的数据先进行应用程序压缩，然后在数据库中再使用压缩表存储，这样的压缩在使用上不但不会带来好的效果反而会导致CPU 的消耗。

15.9.1.4 Monitoring InnoDB Table Compression at Runtime

应用程序的整体性能、CPU 和 I/O 利用率以及磁盘文件的大小是衡量应用程序压缩效率的指标。为了更深入地了解压缩表的性能考虑因素可以参照Information Schema 中的一些压缩表。

INNODB_CMP 表报告了使用中的每个压缩页面大小(KEY_BLOCK_SIZE)的压缩活动信息。它汇总了数据库中所有压缩表的压缩统计信息。当没有访问其他压缩表时，可以通过检查这些表来帮助决定是否压缩一个表。它涉及服务器上相对较低的开销。

INNODB_CMP_PER_INDEX 表报告关于各个表和索引的压缩活动的信息。这些信息对于评估压缩效率和每次诊断一个表或索引的性能问题更有针对性，也更有用。INNODB_CMP_PER_INDEX 表确实涉及大量开销，因此它更适合于开发服务器。

如果压缩和解压所需的CPU 时间很高，那么使用更快或多核 CPU 可以帮助提高相同数据、应用程序工作负载和压缩表集的性能。增加缓冲池的大小也可能有助于提高性能，以便更多未压缩的页面可以留在内存中，从而减少只以压缩形式存在于内存中的页面解压的需要。

如果“成功”压缩操作的数量(COMPRESS_OPS_OK)在压缩操作总数(COMPRESS_OPS)中占很高的比例，那么系统可能表现良好。如果比率很低，那么 MySQL 重组、重新压缩和分割 B 树节点的频率就会比预期的要高。在这种情况下，避免压缩某些表，或增加某些压缩表的 KEY_BLOCK_SIZE。

15.9.1.5 How Compression Works for InnoDB Tables

本节介绍有关InnoDB 表压缩的一些内部实现细节。这里提供的信息可能有助于优化性能。

Compression Algorithms

一些操作系统在文件系统级别实现压缩。文件通常被分成固定大小的块，然后压缩成可变大小的块，这很容易导致碎片化。每次修改块中的内容时，整个块都会在写入磁盘之前被重新压缩。这些特性使得这种压缩技术不适合用于更新密集的数据库系统。

MySQL 借助著名的 zlib 库实现压缩，zlib 库实现 LZ77 压缩算法。这种压缩算法在 CPU 利用率和数据大小缩减方面都是成熟、健壮和高效的。该算法是“无损的”，因此原始的未压缩数据总是可以从压缩后的形式进行重构。LZ77 压缩的工作原理是找到要压缩的数据中重复出现的数据序列。数据中的值模式决定了它的压缩效果，但是典型的用户数据通常压缩 50% 或更多。

与应用程序执行的压缩特性不同，InnoDB 压缩同时适用于用户数据和索引。在许多情况下，索引可能占数据库总大小的 40% -50% 或更多，因此这种差异是显著的。当数据集的压缩工作良好时，InnoDB 数据文件(每个表的文件表空间或一般的表空间 .idb 文件)的大小是未压缩大小的 25% 到 50%，或者可能更小。根据工作负载的不同，这个更小的数据库反过来可以减少 I/O，并增加吞吐量，在增加 CPU 利用率方面的成本是适中的。可以通过修改 innodb_compression_level 配置选项来调整压缩级别和 CPU 开销之间的平衡。

InnoDB Data Storage and Compression

InnoDB 表中的所有用户数据都存储在包含 B 树索引(集群索引)的页面中。索引节点中的每一行都包含主键和表的所有其他列的值。

InnoDB 表中的二级索引也是 B -tree，包含对值：索引键和指向聚集索引中的一行的指针。指针实际上是表的主键值，如果需要索引键和主键以外的列，则用于访问聚集索引。二级索引记录必须始终适合于单个 B 树页面。

B 树节点(集群索引和辅助索引)的压缩处理与溢出页面的压缩不同，溢出页面用于存储长 VARCHAR、BLOB或 TEXT 。

Compression of B-Tree Pages

因为它们经常更新，所以B-tree 页面需要特殊处理。重要的是最小化 B-tree 节点分裂的次数，以及最小化解压缩和重新压缩它们的内容的需要。

MySQL 使用的一种技术是以未压缩的形式在 B 树节点中维护一些系统信息，从而促进某些就地更新。例如，这允许在不进行任何压缩操作的情况下删除行。

此外，MySQL 试图避免在索引页发生更改时进行不必要的解压缩和重新压缩。在每个 B-tree 页面中，系统保存一个未压缩的“修改日志”，以记录对页面所做的更改。可以将小记录的更新和插入写到这个修改日志中，而不需要重新构建整个页面。

当修改日志的空间耗尽时，InnoDB 将解压缩页面、应用更改并重新压缩页面。如果重新压缩失败(这种情况称为压缩失败)，B-tree 节点将被分割，并重复此过程，直到更新或插入成功。

为了避免在写密集型工作负载(如 OLTP 应用程序)中出现频繁的压缩失败，MySQL 有时会在页面中保留一些空白空间，这样修改日志就会很快被填满，页面也会被重新压缩，同时还有足够的空间避免被分割。当系统跟踪页面分裂的频率时，每个页面中剩余的填充空间的数量会发生变化。对压缩表进行频繁写入时，可以调整 innodb_compression_failure_old_pct 和 innodb_compression_pad_pct_max 配置选项来微调这种机制。

通常，MySQL 要求 InnoDB 表中的每个 B-tree 页面至少可以容纳两个记录。对于压缩表，这个要求已经放宽。B-tree 节点的叶子页(无论是主键还是辅助索引)只需要容纳一个记录，但该记录必须以未压缩的形式适合每页修改日志。如果 innodb_strict_mode 是打开的，MySQL 检查最大行大小在创建表或创建索引。如果行不匹配，将返回错误消息。

如果在innodb_strict_mode 关闭时创建一个表，并且随后的 INSERT 或 UPDATE 语句试图创建一个不适合压缩页面大小的索引条目，则操作失败。要解决这个问题，可以使用 ALTER table 重新构建表，并选择更大的压缩页面大小(KEY_BLOCK_SIZE)，缩短任何列前缀索引，或者使用 ROW_FORMAT=DYNAMIC 或ROW_FORMAT=COMPACT 完全禁用压缩。

innodb_strict_mode 不适用于支持压缩表的普通表空间。

Compressing BLOB, VARCHAR, and TEXT Columns

在InnoDB 表中，不属于主键的 BLOB、VARCHAR 和 TEXT 列可以存储在单独分配的溢出页面上。我们将这些列称为页外列。它们的值存储在溢出页面的单链接列表中。

对于用ROW_FORMAT=DYNAMIC 或 ROW_FORMAT= compression 创建的表，BLOB、TEXT 或 VARCHAR 列的值可能完全存储在页外，这取决于它们的长度和整个行的长度。对于页外存储的列，聚集索引记录仅包含指向溢出页的 20 字节指针，每列一个。是否有列存储在页面之外取决于页面大小和行的总大小。当行太长而不能完全适合于聚集索引的页面时，MySQL 会为非页面存储选择最长的列，直到行适合聚集索引页面为止。如上所述，如果一行本身不适合压缩页面，就会发生错误。

使用ROW_FORMAT=REDUNDANT 和 ROW_FORMAT=COMPACT 的表在聚集索引记录中与主键一起存储 BLOB、VARCHAR 和 TEXT 列的前 768 字节。768 字节的前缀后面跟着一个 20 字节的指针，指针指向包含列值其余部分的溢出页面。

当表是压缩格式时，写入溢出页面的所有数据都按原样压缩：也就是说，MySQL 将 zlib 压缩算法应用于整个数据项。除了数据，压缩溢出页面包含一个未压缩的头和尾，其中包括一个页面校验和和一个到下一个溢出页面的链接。因此，如果数据是高度可压缩的，那么对于较长的 BLOB、TEXT 或 VARCHAR 列，可以获得非常显著的存储节省，这通常与文本数据一样。图像数据(如 JPEG)通常已经被压缩，因此存储在压缩表中并没有什么好处：双压缩可以浪费 CPU 周期，节省的空间很少或根本没有。

溢出页面的大小与其他页面相同。包含10 列的行存储在页外，即使列的总长度只有 8K 字节，也会占用 10 个溢出页面。在未压缩的表中，10 个未压缩的溢出页面占用 160K 字节。在一个页面大小为 8K 的压缩表中，它们只占用 80K 字节。因此，对于具有长列值的表，使用压缩表格式通常更有效。

对于每个表的文件表空间，使用16K 压缩页面大小可以减少 BLOB、VARCHAR 或 TEXT 列的存储和 I/O 成本，因为这些数据通常压缩得很好，因此可能需要更少的溢出页面，即使 B-tree 节点本身占用的页面与未压缩表单一样多。一般的表空间不支持 16K 压缩页面大小(KEY_BLOCK_SIZE)。

Compression and the InnoDB Buffer Pool

在一个被压缩的InnoDB 表中，每一个被压缩的页面都对应一个 16K 字节的未压缩页面。要访问页面中的数据，MySQL 将从磁盘读取压缩后的页面(如果不在缓冲池中)，然后将页面解压缩到原始形式。本节描述 InnoDB 如何管理与压缩表页面相关的缓冲池。

为了最小化I/O 并减少解压页面的需要，缓冲池有时包含数据库页面的压缩和未压缩形式。为了为其他需要的数据库页面腾出空间，MySQL 可以从缓冲池中取出未压缩的页面，同时将压缩的页面留在内存中。或者，如果有一段时间没有访问某个页面，那么可以将该页面的压缩形式写入磁盘，以便为其他数据腾出空间。因此，在任何给定时间，缓冲池可能同时包含页面的压缩形式和未压缩形式，或者只包含页面的压缩形式，或者两者都不包含。

MySQL 会跟踪哪些页面要保存在内存中，哪些页面要使用最近最少使用的(LRU)列表退出，这样热的(频繁访问的)数据就会留在内存中。当访问压缩表时，MySQL 使用自适应 LRU 算法来实现内存中压缩和未压缩页面的适当平衡。这种自适应算法对系统是以 I/O 绑定方式运行还是以 CPU 绑定方式运行非常敏感。目标是避免在 CPU 繁忙时花费太多的处理时间来解压页面，并避免在 CPU 有空闲周期时执行过多的 I/O，这些周期可以用于解压压缩页面(可能已经在内存中了)。当系统受 I/O 约束时，算法更倾向于收回未压缩的页面副本，而不是两个副本，以便为其他磁盘页腾出更多空间，使其成为内存驻留。当系统被 CPU 绑定时，MySQL 更喜欢将压缩页面和未压缩页面都清除掉，这样就可以将更多的内存用于“热”页面，从而减少只以压缩形式解压内存中的数据。

Compression and the InnoDB Redo Log Files

在将压缩页面写入数据文件之前，MySQL 会将页面的副本写入重做日志(如果自上次写入数据库以来已经重新压缩)。这样做是为了确保重做日志对于崩溃恢复是可用的，即使在不太可能的情况下，即 zlib 库被升级，并且该更改引入了与压缩数据的兼容性问题。因此，在使用压缩时，日志文件的大小可能会增加，或者需要更频繁的检查点。日志文件大小或检查点频率的增加取决于以需要重新组织和重新压缩的方式修改压缩页面的次数。

要在每个表空间的文件中创建压缩表，必须启用innodb_file_per_table。在常规表空间中创建压缩表时，不依赖于 innodb_file_per_table 设置。

15.9.1.6 Compression for OLTP Workloads

传统上，InnoDB 压缩特性主要用于只读或以读为主的工作负载，比如在数据仓库配置中。SSD 存储设备快速但相对较小且昂贵，它的兴起也使压缩对 OLTP 工作负载具有吸引力:高流量、交互式网站可以通过使用压缩表和频繁插入、更新和删除操作的应用程序来降低其存储需求和每秒 I/O 操作 (IOPS)。

下面这些配置选项允许你调整特定MySQL 实例的压缩方式，并强调写密集型操作的性能和可伸缩性：

innodb_compression_level 允许提高或降低压缩程度。更高的值允许在存储设备上安装更多的数据，从而在压缩期间减少更多的 CPU 开销。如果存储空间不是很重要，或者希望数据不是特别可压缩，那么使用较低的值可以减少 CPU 开销。

innodb_compression_failure_old_pct 指定在更新压缩表期间压缩失败的截止点。当这个阈值通过后，MySQL 开始在每个新的压缩页面中留下额外的空闲空间，动态地调整空闲空间的数量，直到innodb_compression_pad_pct_max 指定的页面大小百分比。

innodb_compression_pad_pct_max 允许调整每个页面中保留的最大空间，以记录对压缩行的更改，而无需再次压缩整个页面。值越高，就可以记录更多的更改，而无需重新压缩页面。MySQL 为每个压缩表中的页面使用可变的空闲空间，只有当指定的压缩操作百分比在运行时“失败”时才会使用，这需要花费昂贵的操作来分割压缩的页面。

innodb_log_compressed_pages 允许禁用将重新压缩的页面的图像写入重做日志。当对压缩数据进行更改时，可能会发生重新压缩。默认情况下启用此选项是为了防止在恢复过程中使用不同版本的 zlib 压缩算法可能发生的损坏。如果你确定 zlib 版本不会更改，请禁用 innodb_log_compressed_pages，以减少修改压缩数据的工作负载重做日志生成。

因为处理压缩数据有时涉及同时将页面的压缩版本和未压缩版本保存在内存中，所以当使用OLTP 样式的工作负载进行压缩时，要准备好增加 innodb_buffer_pool_size 配置选项的值。

15.9.1.7 SQL Compression Syntax Warnings and Errors

本节描述在使用表压缩特性时可能遇到的语法警告和错误。

SQL Compression Syntax Warnings and Errors for File-Per-Table Tablespaces

当innodb_strict_mode 被启用，innodb_file_per_table 被禁用时，在 CREATE TABLE 或 ALTER TABLE 语句中指定 ROW_FORMAT=COMPRESSED 或 KEY_BLOCK_SIZE，会产生以下错误：

# ERROR 1031 (HY000): Table storage engine for 't1' doesn't have this option

当innodb_strict_mode 被禁用，innodb_file_per_table 被禁用时，在 CREATE TABLE 或 ALTER TABLE 语句中指定 ROW_FORMAT=COMPRESSED 或 KEY_BLOCK_SIZE，会产生警告：

# Warning | 1478 | InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.

# Warning | 1478 | InnoDB: ignoring KEY_BLOCK_SIZE=4.

# Warning | 1478 | InnoDB: ROW_FORMAT=COMPRESSED requires innodb_file_per_table.

# Warning | 1478 | InnoDB: assuming ROW_FORMAT=DYNAMIC.

“非严格”的行为允许将一个 mysqldump 文件导入不支持压缩表的数据库，即使源数据库包含压缩表。在这种情况下，MySQL 将创建 ROW_FORMAT=DYNAMIC 的表，而不是阻止操作。

15.9.2 InnoDB Page Compression

InnoDB 支持页级的表压缩，这些表位于每个表的文件表空间中。这个特性称为透明页面压缩。通过 CREATE TABLE 或 ALTER TABLE 指定 compression 属性来启用页面压缩。支持的压缩算法包括 Zlib 和 LZ4。

How Page Compression Works

写入页面时，使用指定的压缩算法对其进行压缩。压缩数据被写入到磁盘，在磁盘上，穿孔机制从页面末端释放空块。如果压缩失败，数据将按原样输出。

Hole Punch Size on Linux

在Linux 系统中，文件系统块大小是用于穿孔的单位大小。因此，只有当页面数据可以压缩到小于或等于InnoDB 页面大小减去文件系统块大小时，页面压缩才有效。

Enabling Page Compression

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

# ALTER TABLE t1 COMPRESSION="zlib";

# OPTIMIZE TABLE t1;