14.9.1.3 调整 InnoDBtable 的压缩

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

要压缩哪些 table。
使用什么压缩页面大小。
是否根据运行时性能 Feature(例如系统花费在压缩和解压缩数据上的时间)来调整缓冲池的大小。工作负载更像是data warehouse(主要是查询)还是OLTP系统(查询和DML的混合)。
如果系统对压缩 table 执行 DML 操作，并且数据的分发方式在运行时导致昂贵的compression failures，则可以调整其他高级配置选项。

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

何时使用压缩

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

数据 Feature 和压缩

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

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

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

测试特定 table 压缩的另一种方法是将未压缩 table 中的某些数据复制到file-per-tabletable 空间中的相似压缩 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 * 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

此实验产生了以下数字，当然，这些数字可能会根据您的 table 结构和数据而有很大差异：

-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

查看压缩对于您的特定workload是否有效：

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

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

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

在数据库中压缩

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

在应用程序中压缩

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

Hybrid Approach

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

工作负载 Feature 和压缩

除了选择要压缩的 table(和页面大小)以外，工作负载是性能的另一个关键因素。如果应用程序以读取而不是更新为主导，则索引页用完了 MySQL 为压缩数据维护的每页“修改日志”的空间后，需要重组和重新压缩较少的页面。如果更新主要更改了未索引的列或包含BLOB s 或恰好以“页外”方式存储的大字符串的列，则压缩的开销可能是可以接受的。如果对 table 的唯一更改是使用单调递增主键的INSERT，并且辅助索引很少，则几乎不需要重组和重新压缩索引页。由于 MySQL 可以通过修改未压缩的数据来“删除标记”并“就地”删除压缩页面上的行，因此对 table 的DELETE操作相对有效。

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

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

配置 Feature 和压缩

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

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

选择压缩的页面大小

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

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

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

Name	MySql 中文文档
Version
Language
Badge
Last Updated	2021-07-03T12:24:35