mysql innodb 压缩表_mysql8 参考手册-- InnoDB表的压缩方式

压缩算法

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

MySQL在著名的zlib库的帮助下实现了压缩，该 库实现了LZ77压缩算法。这种压缩算法在CPU利用率和数据大小减小方面都是成熟，可靠且有效的。该算法是 “ 无损的 ”，因此始终可以从压缩形式中重建原始的未压缩数据。LZ77压缩通过查找在要压缩的数据内重复的数据序列来工作。数据中的值模式决定了压缩的程度，但是典型的用户数据通常压缩50％或更多。

注意

InnoDB支持的zlib 库版本最高为1.2.11，这是与MySQL 8.0捆绑在一起的版本。

与应用程序执行的压缩或某些其他数据库管理系统的压缩功能不同，InnoDB压缩既适用于用户数据，也适用于索引。在许多情况下，索引可以占数据库总大小的40-50％或更多，因此这种差异非常明显。当压缩对于数据集工作得很好时，InnoDB数据文件(每个表的 文件表 空间或常规表空间 .ibd文件)的大小是未压缩大小的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_modeOFF 时创建表 ，并且随后的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。有关更多信息，请参见第13.1.21节“ CREATE TABLESPACE语句”。

压缩BLOB，VARCHAR和TEXT列

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

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

注意

在创建的表ROW_FORMAT=DYNAMIC或 ROW_FORMAT=COMPRESSED， TEXT并且 BLOB是小于或等于40个字节的列总是存储在行。

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

当表格采用COMPRESSED格式时，写入溢出页面的所有数据都按“ 原样 ”压缩；也就是说，MySQL将zlib压缩算法应用于整个数据项。除数据外，压缩的溢出页面还包含未压缩的头和尾，其中包括页面校验和以及到下一个溢出页面的链接等。因此，如果数据具有高度可压缩性(对于文本数据而言通常如此)，则对于更长的或 列BLOB，可以获得非常显着的存储节省 。图像数据，例如TEXTVARCHARJPEG，通常已经被压缩，因此从压缩表中存储不会带来太多好处；双重压缩会浪费CPU周期，几乎或根本不会节省空间。

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

对于文件的每个表 的表空间，采用16K压缩页面大小可以降低存储和I / O开销BLOB， VARCHAR或 TEXT列，因为这些数据往往压缩得很好，因此可能需要更少的溢出页，即使在B树节点本身占用的页面与未压缩形式的页面一样多。常规表空间不支持16K压缩页面大小(KEY_BLOCK_SIZE)。有关更多信息，请参见 第15.6.3.3节“常规表空间”。

压缩和InnoDB缓冲池

在压缩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库已升级且更改引起压缩数据的兼容性问题也是如此。因此，日志文件的大小有所增加 ，或者需要更频繁的 检查点，在使用压缩时可以预期。日志文件大小或检查点频率的增加量取决于以需要重组和重新压缩的方式修改压缩页面的次数。

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