调整InnoDB表的压缩

大多数情况下，InnoDB数据存储和压缩中描述的内部优化可确保系统在压缩数据下正常运行。但是，由于压缩的效率取决于数据的性质，因此您可以做出影响压缩表性能的决策：

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

使用本节中的准则可帮助做出那些体系结构和配置选择。当您准备进行长期测试并将压缩表投入生产时，请参见“在运行时监视InnoDB表压缩”，以了解在现实条件下验证这些选择的有效性的方法。

何时使用压缩

通常，压缩在包含合理数量的字符串列且数据读取的频率比写入的频率高的表上最有效。因为没有保证可以预测压缩是否在特定情况下有用的方法，所以请始终对在代表性配置上运行的特定工作负载和数据集进行测试。在决定压缩哪些表时，请考虑以下因素。

数据特征与压缩

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

您选择是否为每个InnoDB表打开压缩。一个表及其所有索引使用相同（压缩）的页面大小。包含表的所有列的数据的主键（聚集）索引可能比辅助索引更有效地压缩。对于长行的情况，如 DYNAMIC行格式中所述，使用压缩可能会导致长列值“页外”存储。。这些溢出页面可能压缩得很好。考虑到这些考虑因素，对于许多应用程序而言，某些表的压缩比其他应用程序更有效，并且您可能会发现工作负载仅在压缩了一部分表的情况下才表现最佳。

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

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

USE test;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL autocommit=0;

-- Create an uncompressed table with a million or two rows.
CREATE TABLE big_table AS SELECT * FROM information_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 KEY auto_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=4 row_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。
• 如果很大一部分压缩操作成功完成，则该表可能是压缩的理想选择。
• 如果压缩失败的比例很高，则可以按照“ OLTP工作负载的压缩”中所述调整innodb_compression_level，innodb_compression_failure_threshold_pct和innodb_compression_pad_pct_max选项，然后尝试进一步测试。

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

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

在数据库中压缩

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

在应用程序中压缩

如果在将应用程序中的文本等数据压缩到数据库中之前将其压缩，则可以通过压缩某些列而不是其他一些列来节省那些压缩效果不佳的数据的开销。这种方法使用CPU周期在客户端计算机而不是数据库服务器上进行压缩和解压缩，这可能适用于具有许多客户端的分布式应用程序，或者在客户端计算机具有备用CPU周期的情况下使用。

混合方式

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

工作负荷特性和压缩

除了选择要压缩的表（和页面大小）以外，工作负载是性能的另一个关键因素。如果应用程序以读取而不是更新为主导，那么在索引页空间不足以容纳MySQL为压缩数据保留的每页“修改日志”之后，需要重组和重新压缩较少的页面。如果更新主要更改了未索引的列或包含BLOBs或较大字符串的更新，而这些恰巧是“页外”存储的，则压缩的开销可能是可以接受的。如果对表的唯一更改是INSERT使用单调递增的主键，并且二级索引很少，几乎不需要重组和重新压缩索引页。由于MySQL可以通过修改未压缩的数据来“删除标记”并“就地”删除压缩页上的行，因此DELETE对表的操作相对有效。

对于某些环境，加载数据所需的时间与运行时检索一样重要。特别是在数据仓库环境中，许多表可能是只读的或只读的。在那些情况下，就增加加载时间而言，为压缩付出代价也许是不可接受的，除非由此带来的减少磁盘读取次数或存储成本的节省是可观的。

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

配置特征和压缩

从磁盘读写数据库页面是系统性能最慢的方面。压缩尝试通过使用CPU时间压缩和解压缩数据来减少I / O，并且当I / O与处理器周期相比是相对稀缺的资源时，压缩是最有效的。

在具有快速，多核CPU的多用户环境中运行时，通常尤其如此。当压缩表的页面位于内存中时，MySQL通常会在缓冲池中使用额外的内存（通常为16KB）来存储页面的未压缩副本。自适应LRU算法尝试平衡压缩页面和未压缩页面之间的内存使用，以考虑工作负载是以I / O绑定还是CPU绑定的方式运行。尽管如此，使用压缩表时，具有更多专用于缓冲池的内存的配置往往会比内存受到高度限制的配置更好地运行。

选择压缩的页面大小

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

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

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