参考官方文档：
https://dev.mysql.com/doc/refman/5.7/en/innodb-compression.html

使用InnoDB的压缩功能，您可以创建以压缩格式存储数据的表。压缩有助于提高原始性能和可扩展性。压缩意味着更少的数据在磁盘和内存之间传输，并且占用磁盘和内存中的更少空间。对于具有二级索引的表，这些好处会被放大，因为索引数据也会被压缩。压缩对于SSD存储设备尤其重要，因为它们的容量往往低于HDD设备。

14.9.1 InnoDB 表压缩

使用CREATE TABLE或ALTER TABLE的ROW_FORMAT = COMPRESSED属性启用表压缩。

14.9.1.1 表压缩概述

由于处理器和高速缓存存储器的速度比磁盘存储设备的速度提高得多，因此许多工作负载都是磁盘式的。数据压缩能够实现更小的数据库大小，减少I / O并提高吞吐量，同时以较少的CPU利用率提高成本。对于读密集型应用程序而言，压缩尤其有价值，因为系统具有足够的RAM以将频繁使用的数据保存在内存中。

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

压缩的页面大小通过CREATE TABLE或ALTER TABLE KEY_BLOCK_SIZE参数指定。由于系统表空间不能存储压缩表，因此不同的页面大小要求将表放置 file-per-table表空间或常规表空间中，而不是放在系统表空间中。

无论KEY_BLOCK_SIZE的值如何，压缩级别都是相同的。在为KEY_BLOCK_SIZE指定较小的值时，您将在小页面增长上获得I / O优势。但是，如果您指定的值太小，则当数据值无法压缩到足以适应每个页面中的多行时，会有额外的开销来重新组织页面。根据每个索引的键列长度，对表KEY_BLOCK_SIZE的可用性有很大的限制。指定一个太小的值，并且CREATE TABLE或ALTER TABLE语句失败。

在缓冲池中，压缩数据保存在小页面中，页面大小基于KEY_BLOCK_SIZE值。为了提取或更新列值，MySQL还使用未压缩的数据在缓冲池中创建未压缩的页面。在缓冲池中，对未压缩页面的任何更新也会重新写回等效的压缩页面。您可能需要调整缓冲池的大小以适应压缩和未压缩页面的额外数据，尽管未压缩的页面在需要空间时从缓冲池中逐出，然后在下次访问时再次解压缩。

14.9.1.2 创建被压缩的

压缩表可以在每个表的文件表空间或一般的表空间中创建。表压缩不适用于InnoDB系统表空间。系统表空间（空间0，.ibdata文件）可以包含用户创建的表，但它也包含内部系统数据，这些数据从不压缩。因此，压缩仅适用于存储 file-per-table或一般表空间中的表（和索引）。

file-per-table 表空间中创建一个压缩表

要在每个表文件表空间中创建一个压缩表，必须启用innodb_file_per_table（MySQL 5.6.6中的缺省值），并且必须将innodb_file_format设置为 Barracuda。您可以使用SET语句在MySQL配置文件（my.cnf或my.ini）中或动态地设置这些参数。

在配置innodb_file_per_table和innodb_file_format选项后，在CREATE TABLE或ALTER TABLE语句中指定ROW_FORMAT = COMPRESSED子句或KEY_BLOCK_SIZE子句或两者，以在 file-per-table表空间中创建一个压缩表。

例如，您可以使用以下语句：

SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_file_format=Barracuda;
CREATE TABLE t1 (c1 INT PRIMARY KEY) ROW_FORMAT=COMPRESSEDKEY_BLOCK_SIZE=8;

在一般表空间中创建一个被压缩的表

要在常规表空间中创建压缩表，必须为创建表空间时指定的常规表空间定义FILE_BLOCK_SIZE。 FILE_BLOCK_SIZE值必须是与innodb_page_size值相关的有效压缩页面大小，并且由CREATE TABLE或ALTER TABLE KEY_BLOCK_SIZE子句定义的压缩表的页面大小必须等于FILE_BLOCK_SIZE / 1024。例如，如果innodb_page_size = 16384且FILE_BLOCK_SIZE = 8192，则表的KEY_BLOCK_SIZE必须为8.

以下示例演示如何创建常规表空间并添加压缩表。该示例假定默认innodb_page_size为16K。 8192的FILE_BLOCK_SIZE要求压缩表的KEY_BLOCK_SIZE为8。

mysql> CREATE TABLESPACE `ts2` ADD DATAFILE 'ts2.ibd'FILE_BLOCK_SIZE = 8192 Engine=InnoDB;
mysql> CREATE TABLE t4 (c1 INT PRIMARY KEY) TABLESPACE ts2ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

注意：

如果指定ROW_FORMAT = COMPRESSED，则可以省略KEY_BLOCK_SIZE; KEY_BLOCK_SIZE设置默认为innodb_page_size值的一半。
如果您指定了有效的KEY_BLOCK_SIZE值，则可以省略ROW_FORMAT = COMPRESSED; 压缩自动启用。
要确定KEY_BLOCK_SIZE的最佳值，通常可以为该子句创建具有不同值的同一表的多个副本，然后测量所得到的.ibd文件的大小，并查看每个文件对于实际工作负载的执行情况。对于常规表空间，请记住，删除表不会减小常规表空间.ibd文件的大小，也不会将磁盘空间返回给操作系统。
KEY_BLOCK_SIZE值被视为提示; 如有必要，InnoDB可以使用不同的大小。对于file-per-table 表空间，KEY_BLOCK_SIZE只能小于或等于innodb_page_size值。如果指定的值大于innodb_page_size值，则会忽略指定的值，发出警告，并将KEY_BLOCK_SIZE设置为innodb_page_size值的一半。如果innodb_strict_mode = ON，则指定无效的KEY_BLOCK_SIZE值将返回错误。对于一般的表空间，有效的KEY_BLOCK_SIZE值取决于表空间的FILE_BLOCK_SIZE设置。
32k和64k页面大小不支持压缩。有关更多信息，请参阅innodb_page_size文档。
InnoDB数据页面的默认未压缩大小为16KB。根据选项值的组合，MySQL为表空间数据文件（.ibd文件）使用1KB，2KB，4KB，8KB或16KB的页面大小。实际的压缩算法不受KEY_BLOCK_SIZE值的影响; 该值确定每个压缩块有多大，这反过来又影响可以将每行压缩到每个压缩页中的行数。
在 file-per-table 表空间中创建压缩表时，将KEY_BLOCK_SIZE设置为InnoDB页面大小通常不会导致太多的压缩。例如，设置KEY_BLOCK_SIZE = 16通常不会产生太多压缩，因为正常的InnoDB页面大小为16KB。这个设置对于有很多长BLOB，VARCHAR或TEXT列的表格可能仍然有用，因为这些值通常压缩得很好，因此可能需要更少的溢出页面，如第14.9.1.5节“InnoDB表的压缩工作方式”所述。对于一般的表空间，不允许使用等于InnoDB页面大小的KEY_BLOCK_SIZE值。
表（包括聚簇索引）的所有索引都使用与CREATE TABLE或ALTER TABLE语句中指定的页大小相同的页大小进行压缩。诸如ROW_FORMAT和KEY_BLOCK_SIZE之类的表属性不是InnoDB表的CREATE INDEX语法的一部分，并且如果指定它们（尽管如果指定它们将出现在SHOW CREATE TABLE语句的输出中），它们将被忽略。

被压缩表上的限制

5.1之前的MySQL版本无法处理压缩表。
压缩表不能存储在InnoDB系统表空间中。
常规表空间可以包含多个表，但压缩表和未压缩表不能共存于同一个通用表空间中。
尽管子句名称为ROW_FORMAT，但压缩适用于整个表及其所有相关索引，而不适用于单个行。

14.9.1.3 调整InnoDB表的压缩

InnoDB数据存储和压缩中描述的内部优化通常会确保系统在压缩数据下运行良好。但是，由于压缩效率取决于数据的性质，因此可以制定影响压缩表性能的决策：

要压缩哪些表。
使用被压缩页面大小。
是否根据运行时性能特征调整缓冲池的大小，例如系统花费压缩和解压缩数据的时间量。工作负载是否更像数据仓库（主要是查询）或OLTP系统（混合查询和DML）。
如果系统在压缩表上执行DML操作，并且数据分布的方式会在运行时导致昂贵的压缩失败，则可以调整其他高级配置选项。

何时使用压缩

一般来说，压缩在包含合理数量的字符串列，并且数据读取的次数比写入的次数要多得多的表上工作得最好。由于没有可靠的方法来预测压缩是否有益于特定情况，因此请始终使用代表性配置上运行的特定工作负载和数据集进行测试。决定要压缩哪些表时请考虑以下因素。

数据特征和压缩

数据文件大小压缩效率的关键决定因素是数据本身的性质。回想一下，压缩是通过识别数据块中重复的字节串来工作的。完全随机数据是最糟糕的情况。典型的数据通常具有重复的值，因此有效地压缩。字符串通常压缩得很好，无论是在CHAR，VARCHAR，TEXT还是BLOB列中定义。另一方面，主要包含二进制数据（整数或浮点数）或先前压缩的数据（例如JPEG或PNG图像）的表通常可能无法很好地压缩，或根本不压缩。

您可以选择是否为每个InnoDB表打开压缩。表及其所有索引使用相同（压缩）的页面大小。主键（聚集）索引可能比二级索引更有效地压缩，其中包含表中所有列的数据。对于长行的情况，使用压缩可能导致长列值被存储为“off-page”，如第14.11.3节“动态和压缩行格式”中所述。这些溢出页面可能压缩得很好。鉴于这些考虑因素，对于许多应用程序，某些表比其他表压缩更有效，并且您可能会发现只有压缩表的子集才能使工作负载表现最佳。

要确定是否要压缩某个表，请进行实验。您可以粗略地估算一下，如何在未压缩表的.ibd文件副本上使用实现LZ77压缩的实用程序（例如gzip或WinZip）来压缩数据的效率。您可以期望从MySQL压缩表的压缩比基于文件的压缩工具会跟好，因为MySQL基于页面大小压缩数据块，默认情况下为16KB。除用户数据外，页面格式还包括一些未压缩的内部系统数据。基于文件的压缩实用程序可以检查更大的数据块，因此可能会在MySQL文件中找到的大文件中找到更多重复的字符串。

在特定表上测试压缩的另一种方法是，将一些数据从未压缩的表中复制到 file-per-table 表空间中类似的压缩表（具有所有相同的索引），并查看生成的.ibd文件的大小。例如：

USE test;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL autocommit=0;-- Create an uncompressed table with a million or two rows.CREATE TABLE big_table AS SELECT * FROMinformation_schema.columns;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
INSERT INTO big_table SELECT * FROM big_table;
COMMIT;
ALTER TABLE big_table ADD id int unsigned NOT NULL PRIMARY KEYauto_increment;
SHOW CREATE TABLE big_table\G select count(id) from big_table;-- Check how much space is needed for the uncompressed table.\! ls -l data/test/big_table.ibd
CREATE TABLE key_block_size_4 LIKE big_table;
ALTER TABLE key_block_size_4 key_block_size=4row_format=compressed; INSERT INTO key_block_size_4 SELECT * FROM big_table;
commit;-- Check how much space is needed for a compressed table-- with particular compression settings.
\! ls -l data/test/key_block_size_4.ibd

这个实验产生了下面的数字，这当然会根据你的表格结构和数据而有很大的不同：

-rw-rw----1 cirrus staff 310378496 Jan 9 13:44 data/test/big_table.ibd-rw-rw----1 cirrus staff 83886080 Jan 9 15:10 data/test/key_block_size_4.ibd

要查看压缩对于您的特定工作负载是否有效：

对于简单测试，使用没有其他压缩表的MySQL实例，并针对INFORMATION_SCHEMA.INNODB_CMP表运行查询。
有关包含多个压缩表的工作负载的更详细测试，请针对INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX表运行查询。由于INNODB_CMP_PER_INDEX表中的统计信息收集起来很昂贵，因此在查询该表之前必须启用配置选项innodb_cmp_per_index_enabled，并且可能会将这些测试限制为开发服务器或非关键的从服务器。
对正在测试的压缩表运行一些典型的SQL语句。
通过查询INFORMATION_SCHEMA.INNODB_CMP或INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX表，并比较COMPRESS_OPS和COMPRESS_OPS_OK，检查成功压缩操作与整体压缩操作的比率。
如果成功完成高比例的压缩操作，则表可能是压缩的理想选择
如果压缩失败的比例较高，您可以按照第14.9.1.6节“压缩OLTP工作负载”中所述调整innodb_compression_level，innodb_compression_failure_threshold_pct和innodb_compression_pad_pct_max选项，然后尝试进一步测试。

数据库压缩与应用程序压缩

决定是否压缩应用程序或表中的数据; 请勿对同一数据使用两种类型的压缩。当您在应用程序中压缩数据并将结果存储在压缩表中时，极其不可能节省额外的空间，而双重压缩只会浪费CPU周期。

在数据库中进行压缩

启用时，MySQL表压缩是自动的，并适用于所有列和索引值。这些列仍然可以使用LIKE等运算符的测试，即使索引值被压缩，排序操作仍然可以使用索引。由于索引通常占数据库总大小的很大一部分，因此压缩可能会显着节省存储空间，I / O或处理器时间。压缩和解压缩操作发生在数据库服务器上，这是一个能处理预期负载的功能强大的系统。

在应用程序中进行压缩

如果在应用程序中压缩数据（例如文本），然后将其插入数据库中，则可以通过压缩某些列来节省数据的开销。这种方法在客户端机器上使用CPU周期进行压缩和解压缩，而不是在数据库服务器上进行压缩和解压缩，这可能适用于具有多个客户端的分布式应用程序，或客户端计算机具有空闲CPU周期的情况。

混合方式

当然，可以将这些方法结合起来。对于某些应用程序，可能适合使用一些压缩表和一些未压缩的表。最好从外部压缩一些数据（并将其存储在未压缩的表中），并允许MySQL压缩（某些）应用程序中的其他表。与往常一样，前期设计和实际测试对于做出正确的决定是有价值的。

工作量特征和压缩

除了选择要压缩的表（和页面大小）之外，工作负载还是性能的另一个关键决定因素。如果应用程序由读取操作而非更新操作占据主导地位，在mysql维护压缩数据之后，需要很少的页面来重新组织和重新压缩页面。如果更新主要更改非索引列或那些包含BLOB或大型字符串的偶然存储在“离页”的列，则压缩的开销可能是可接受的。如果对表的唯一更改是使用单调增加的主键的INSERT，并且只有很少的二级索引，则很少需要重新组织和重新压缩索引页。由于MySQL可以通过修改未压缩的数据来“删除标记”并删除压缩页面上的行，因此表上的DELETE操作相对有效。

对于某些环境，加载数据所需的时间与运行时检索一样重要。特别是在数据仓库环境中，许多表可能是只读的或只读的。在这种情况下，在增加加载时间方面支付压缩的价格可能会也可能不会被接受，除非节省更少的磁盘读取或存储成本是显着的。

基本上，当CPU时间可用于压缩和解压缩数据时，压缩效果最佳。因此，如果您的工作负载受I / O限制，而不是CPU限制，则可能会发现压缩可以提高整体性能。当您使用不同的压缩配置测试应用程序性能时，请在类似于生产系统计划配置的平台上进行测试。

选择被压缩的页大小

压缩页面大小的最佳设置取决于表及其索引所包含数据的类型和分布。压缩的页面大小应该始终大于最大记录大小，否则操作可能会失败，如“B-Tree页面的压缩”中所述。

将压缩的页面尺寸设置得太大会浪费一些空间，但页面不必经常压缩。如果压缩的页面大小设置得太小，则插入或更新可能需要耗费时间的重新压缩，并且B树节点可能不得不更频繁地分割，导致更大的数据文件和更低效的索引。

通常，您将压缩的页面大小设置为8K或4K字节。假设InnoDB表的最大行大小约为8K，KEY_BLOCK_SIZE = 8通常是安全的选择

14.9.1.4 在运行时监控InnoDB表的压缩

要深入了解压缩表的性能注意事项，可以使用例14.1“使用压缩信息模式表”中介绍的信息模式表监视压缩表的运行时压缩性能。这些表反映了内存的内部使用和整体使用的压缩率。

INNODB_CMP表报告有关使用中的每个压缩页面大小（KEY_BLOCK_SIZE）的压缩活动的信息。这些表中的信息是系统范围的：它汇总了数据库中所有压缩表的压缩统计信息。您可以使用此数据来帮助您决定是否在没有其他压缩表正在访问时通过检查这些表来压缩表。它在服务器上的开销相对较低，因此您可能会定期在生产服务器上查询它以检查压缩功能的整体效率。

INNODB_CMP_PER_INDEX表报告有关各个表和索引的压缩活动的信息。此信息对于评估压缩效率和一次诊断一个表或索引的性能问题更有针对性和更有用。（因为每个InnoDB表被表示为一个聚集索引，MySQL不使表和索引之间有很大的区别在这种情况下）的INNODB_CMP_PER_INDEX表确实涉及相当大的开销，所以它更适合开发服务器，您可以比较不同的工作负载，数据和压缩设置的影响。为防止意外强制实施此监视开销，必须先启用innodb_cmp_per_index_enabled配置选项，然后才能查询INNODB_CMP_PER_INDEX表。

要考虑的关键统计数据是执行压缩和解压缩操作所需的时间和数量。由于当B-tree节点太满而无法包含修改后的压缩数据时，它会分割B-tree节点，因此比较“成功”压缩操作的数量和整体操作的数量。基于INNODB_CMP和INNODB_CMP_PER_INDEX表和信息整体应用性能和硬件资源的利用率，你可能会做出更改了硬件配置，调整缓冲池的大小，选择不同的页面大小，或选择一组不同的表来压缩。

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

总体上大量的压缩操作（与应用程序中的INSERT，UPDATE和DELETE操作数量以及数据库大小相比）可能表明，您的某些压缩表正在进行过度更新，无法进行有效压缩。如果是这样，请选择一个更大的页面大小，或者选择要压缩的表。

如果“成功”压缩操作（COMPRESS_OPS_OK）的数量占压缩操作总数（COMPRESS_OPS）的高百分比，那么系统可能表现良好。如果这个比例很低，那么MySQL在重组，重新压缩和分割B树节点的次数往往比预期的要多。在这种情况下，避免压缩某些表，或者增加一些压缩表的KEY_BLOCK_SIZE。您可以关闭压缩表，导致应用程序中“压缩失败”的数量超过总数的1％或2％。（在诸如数据加载的临时操作期间，这样的故障率可能是可接受的）。

14.9.1.5 InnoDB表的压缩工作原理

本节介绍一些关于压缩InnoDB表的内部实现细节。这里介绍的信息可能有助于调整性能，但不需要知道压缩的基本用法。

压缩算法

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

MySQL通过众所周知的实现LZ77压缩算法的zlib库实现压缩。这种压缩算法在CPU利用率和数据大小的减少方面都是成熟，稳健和高效的。该算法是“无损”的，因此原始未压缩数据总是可以从压缩格式重建。 LZ77压缩通过查找要压缩的数据中重复的数据序列来工作。数据中值的模式决定了它压缩的程度，但典型的用户数据通常会压缩50％或更多。

注意：
InnoDB仅支持1.2.3版本的zlib库。

与应用程序执行的压缩或某些其他数据库管理系统的压缩功能不同，InnoDB压缩既适用于用户数据，也适用于索引。在许多情况下，索引可以占数据库总大小的40-50％或更多，因此这种差异是显着的。当数据集压缩运行良好时，InnoDB数据文件（文件 - 每表表空间或通用表空间.idb文件）的大小为未压缩大小的25％至50％，或可能更小。根据工作负载的不同，较小的数据库可能会导致I / O减少，吞吐量增加，并且CPU利用率增加。您可以通过修改innodb_compression_level配置选项来调整压缩级别和CPU开销之间的平衡。

InnoDB 数据存储和压缩

InnoDB表中的所有用户数据都存储在包含B树索引（聚簇索引）的页面中。在其他一些数据库系统中，这种类型的索引称为“索引组织表”。索引节点中的每一行都包含（用户指定的或系统生成的）主键和表中所有其他列的值。

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

B树节点（聚集索引和二级索引）的压缩处理与压缩用于存储长VARCHAR，BLOB或TEXT列的溢出页的压缩方式不同，如以下各节所述。

B-树页面的压缩

由于它们经常被更新，因此B树页面需要特殊处理。重要的是最小化B树节点被分割的次数，以及最小化解压缩和重新压缩它们的文本的需求。

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

此外，MySQL会在更改索引页时尝试避免不必要的压缩和重新压缩。在每个B树页面中，系统保留未压缩的“修改日志”以记录对页面所做的更改。可以将小记录的更新和插入写入该修改日志，而不需要完整地重建整个页面。

当修改日志的空间用完时，InnoDB将解压缩页面，应用更改并重新压缩页面。如果重新压缩失败（称为压缩失败的情况），则会B树节点会分裂并重复该过程，直到更新或插入成功为止。

为了避免写入密集型工作负载中的频繁压缩失败，例如对于OLTP应用程序，MySQL有时会在页面中保留一些空白空间（填充），以便修改日志尽快填满并且页面被重新压缩，同时仍有足够的空间来避免分裂。每页中剩余的填充空间量随着系统跟踪页面拆分的频率而变化。在频繁写入压缩表的繁忙服务器上，您可以调整innodb_compression_failure_threshold_pct和innodb_compression_pad_pct_max配置选项以微调此机制。

通常，MySQL要求InnoDB表中的每个B树页面都可以容纳至少两个记录。对于压缩表，这个要求已经放宽。 B树节点的叶页（无论是主键还是辅助索引）只需要容纳一条记录，但该记录必须以未压缩的形式适合每个页修改日志。如果innodb_strict_mode为ON，则MySQL在CREATE TABLE或CREATE INDEX期间检查最大行大小。如果行不合适，则会出现下列消息：

ERROR HY000: Too big row.

如果在innodb_strict_mode为OFF时创建表，并且后续INSERT或UPDATE语句尝试创建不符合压缩页大小的索引条目，则操作将失败，并显示ERROR 42000：row size too large 。（此错误消息未指定记录过大的索引，或提及索引记录的长度或该特定索引页上的最大记录大小。）要解决此问题，请使用ALTER TABLE重建表并选择更大的压缩页面大小（KEY_BLOCK_SIZE），缩短任何列前缀索引，或使用ROW_FORMAT = DYNAMIC或ROW_FORMAT = COMPACT完全禁用压缩。

innodb_strict_mode不适用于通用表空间，它也支持压缩表。一般表空间的表空间管理规则严格依赖innodb_strict_mode强制执行。

压缩 BLOB,VARCHAR,和TEXT 字段

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

对于在ROW_FORMAT = DYNAMIC或ROW_FORMAT = COMPRESSED中创建的表，BLOB，TEXT或VARCHAR列的值可以完全存储在页外，具体取决于它们的长度和整行的长度。对于存储在页外的列，聚簇索引记录仅包含指向溢出页的20字节指针，每列一个。是否在页外存储任何列取决于页面大小和行的总大小。当行太长而无法完全适合聚簇索引的页面时，MySQL会选择最长的列进行页外存储，直到该行适合聚簇索引页。如上所述，如果某行在压缩页面上不适合，则会发生错误。

注意：
对于在ROW_FORMAT = DYNAMIC或ROW_FORMAT = COMPRESSED中创建的表，小于或等于40字节的TEXT和BLOB列始终以in-line方式存储。

在旧版MySQL中创建的表使用Antelope文件格式，该格式仅支持ROW_FORMAT = REDUNDANT和ROW_FORMAT = COMPACT。在这些格式中，MySQL将聚簇索引记录中的BLOB，VARCHAR和TEXT列前768个字节与主键一起存储。 768字节的前缀后跟一个20字节的指针，指向包含列值其余部分的溢出页面。

当表格采用COMPRESSED格式时，写入溢出页面的所有数据都“按原样”压缩; 也就是说，MySQL将zlib压缩算法应用于整个数据项。除了数据之外，压缩的溢出页面包含未压缩的头部和尾部，其包括页面校验和以及到下一个溢出页面的链接等。因此，如果数据具有高度可压缩性，则可以为较长的BLOB，TEXT或VARCHAR列获得非常显着的存储节省，这通常是文本数据的情况。图像数据（例如JPEG）通常已经被压缩，因此不会因存储在压缩表中而受益很多; 双重压缩会浪费CPU周期，节省很少或没有节省。

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

压缩和 InnoDB buffer 池

在压缩的InnoDB表中，每个压缩页面（无论是1K，2K，4K还是8K）都对应于16K字节的未压缩页面（如果设置了innodb_page_size，则为较小的页面）。要访问页面中的数据，MySQL会从磁盘读取压缩页面（如果它尚未在缓冲池中），然后将页面解压缩为其原始格式。本节介绍InnoDB如何根据压缩表的页面管理缓冲池

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

MySQL会跟踪要保留在内存中的页面以及使用最近最少使用（LRU）列表逐出的页面，以便热（经常访问）的数据趋于保留在内存中。当访问压缩表时，MySQL使用自适应LRU算法来实现内存中压缩和未压缩页面的适当平衡。此自适应算法对系统是以I/O绑定还是以CPU绑定方式运行很敏感。目标是避免在CPU繁忙时花费太多处理时间解压缩页面，并避免在CPU具有可用于解压缩压缩页面（可能已经在内存中）的备用周期时进行过多的I/O.当系统受I/O约束时，算法更喜欢驱逐页面的未压缩副本而不是两者，以便为其他磁盘页面变为内存驻留更多空间。当系统受CPU限制时，MySQL更愿意驱逐压缩页面和未压缩页面，这样就可以将更多内存用于“热”页面，并减少仅以压缩形式解压缩内存中数据的需要。

压缩和InnoDB 日志文件

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

压缩表需要Barracuda文件格式。要在每个表的文件表空间中创建压缩表，必须启用innodb_file_per_table，并且必须将innodb_file_format设置为Barracuda。在通用表空间中创建压缩表时，不依赖于innodb_file_format设置。有关更多信息，请参见第14.7.9节“InnoDB通用表空间”。 MySQL Enterprise Backup产品支持Barracuda文件格式。

14.9.1.6 OLTP工作负载的压缩

传统上，InnoDB压缩功能主要建议用于只读或大多数读取工作负载，例如数据仓库配置。 SSD存储设备速度快但相对较小且昂贵，这使得压缩对OLTP工作负载也具有吸引力：高流量，交互式网站可以通过使用压缩表来降低经常执行INSERT，UPDATE和DELETE操作的应用程序的存储要求和每秒I / O操作数（IOPS）。

MySQL 5.6中引入的配置选项允许您对特定MySQL实例调整压缩的工作方式，并强调写入密集型操作的性能和可伸缩性：

innodb_compression_level允许您上下调整压缩程度。较高的值允许您将更多数据放入存储设备，但代价是压缩期间更多的CPU开销。较低的值可以在存储空间不重要时降低CPU开销，或者您希望数据不特别压缩。
innodb_compression_failure_threshold_pct指定更新压缩表期间压缩失败的截止点。当超过此阈值时，MySQL开始在每个新压缩页面中留出额外的可用空间，动态调整可用空间量，直至innodb_compression_pad_pct_max指定的页面大小百分比
innodb_compression_pad_pct_max允许您调整每个页面中保留的最大空间量，以记录对压缩行的更改，而无需再次压缩整个页面。值越高，可以记录的更改越多，无需重新压缩页面。 MySQL为每个压缩表中的页面使用可变数量的可用空间，仅当指定百分比的压缩操作在运行时“失败”时，才需要昂贵的操作来拆分压缩页面。
innodb_log_compressed_pages允许您禁用将重新压缩页面的images写入重做日志。当对压缩数据进行更改时，可能会发生重新压缩。默认情况下启用此选项可防止在恢复期间使用不同版本的zlib压缩算法时可能发生的损坏。如果您确定zlib版本不会更改，请禁用innodb_log_compressed_pages以减少修改压缩数据的工作负载的重做日志生成。

因为使用压缩数据有时需要同时在内存中保留页面的压缩和未压缩版本，所以在使用带有OLTP样式工作负载的压缩时，请准备增加innodb_buffer_pool_size配置选项的值。

14.9.2 InnoDB 页压缩

InnoDB支持驻留在每个表文件表空间中的表的页级压缩。此功能称为透明页面压缩。通过使用CREATE TABLE或ALTER TABLE指定COMPRESSION属性来启用页面压缩。支持的压缩算法包括Zlib和LZ4。

页压缩如何工作

写入页面时，使用指定的压缩算法对其进行压缩。压缩数据被写入磁盘，其中hole punching机制从页面末尾释放空块。如果压缩失败，则按原样写出数据。

在linux上的 Hole Punch 大小

在Linux系统上，文件系统块大小是用于hole punching的单位大小。因此，页面压缩仅在页面数据可以压缩到小于或等于InnoDB页面大小减去文件系统块大小的大小时才有效。例如，如果innodb_page_size = 16K且文件系统块大小为4K，则页面数据必须压缩到小于或等于12K才能进行hole punching。

启用页压缩

要启用页面压缩，请在CREATE TABLE语句中指定COMPRESSION属性。例如：
CREATE TABLE t1 (c1 INT) COMPRESSION="zlib";

您还可以在ALTER TABLE语句中启用页面压缩。但是，ALTER TABLE ... COMPRESSION仅更新表空间压缩属性。写入设置新压缩算法后出现的表空间使用新设置，但要将新压缩算法应用于现有页面，必须使用OPTIMIZE TABLE重建表。

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

禁用页压缩

要禁用页面压缩，请使用ALTER TABLE设置COMPRESSION = None。写入设置COMPRESSION = None后发生的表空间不再使用页面压缩。要解压缩现有页面，必须在设置COMPRESSION = None后使用OPTIMIZE TABLE重建表。

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

页压缩元数据

页面压缩元数据位于INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES表的以下列中：

FS_BLOCK_SIZE：文件系统块大小，是用于hole punching的单位大小。
FILE_SIZE：文件的表观大小，表示文件的最大大小，未压缩。
ALLOCATED_SIZE：文件的实际大小，即磁盘上分配的空间量。

在以下示例中，将从INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES表中检索employees表的页面压缩元数据。

# Create the employees table with Zlib page compression
CREATE TABLE employees ( emp_no INT NOT NULL, birth_date DATE NOTNULL, first_name VARCHAR(14) NOT NULL, last_name VARCHAR(16) NOTNULL, 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_SYS_TABLESPACES

mysql> SELECT SPACE, NAME, FS_BLOCK_SIZE, FILE_SIZE,ALLOCATED_SIZE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACESWHERE 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字节，这是用于hole punching的块大小。

页压缩限制和使用事项

如果文件系统块大小（或Windows上的压缩单元大小）* 2> innodb_page_size，则页面压缩被禁用。
驻留在共享表空间中的表不支持页面压缩，这些表包括系统表空间，临时表空间和常规表空间。
undo日志表空间不支持页面压缩。
redo日志页面不支持页面压缩。
用于空间索引的R树页面不会被压缩。
属于压缩表（ROW_FORMAT = COMPRESSED）的页面保持原样。
在恢复期间，更新的页面以未压缩的形式写出。
在不支持所使用的压缩算法的服务器上加载页面压缩的表空间会导致I / O错误。
在降级到不支持页面压缩的早期版本的MySQL之前，请解压缩使用页面压缩功能的表。要解压缩表，请运行ALTER TABLE ... COMPRESSION = None和OPTIMIZE TABLE。
如果在两台服务器上都使用所使用的压缩算法，则可以在Linux和Windows服务器之间复制页面压缩的表空间。
将页面压缩的表空间文件从一个主机移动到另一个主机时保留页面压缩需要一个保留稀疏文件的实用程序。
使用NVMFS的Fusion-io硬件可以在其他平台上实现更好的页面压缩，因为NVMFS旨在利用punch hole功能。
使用具有大InnoDB页面大小和相对小的文件系统块大小的页面压缩功能可能导致写入放大。例如，具有4KB文件系统块大小的64KB的最大InnoDB页面大小可以改善压缩，但是也增加对缓冲池的需求，导致I/O增加和潜在的写入放大。

ITPub博客

14.9 InnoDB 表和页压缩

最新文章