MySQL InnoDB表压缩

一、InnoDB表压缩概述

InnoDB表压缩可以在创建表时开启，也可以使用ALTER关键字更改。压缩表能够使表中的数据以压缩格式存储，压缩能够显著提高原生性能和可伸缩性。压缩意味着在硬盘和内存之间传输的数据更小且占用相对少的内存及硬盘，对于辅助索引，这种压缩带来更加明显的好处，因为索引数据也被压缩了。这主要得益于InnoDB表压缩是针对页面压缩（不是行压缩），而InnoDB是索引组织表，也就是说索引就是数据。压缩对于硬盘是SSD的存储设备尤为重要，因为它们相对普通的HDD硬盘比较贵且容量有限。

我们都知道，CPU和内存的速度远远大于磁盘，对于数据库服务器，磁盘IO可能会成为紧要资源或者瓶颈。数据压缩能够让数据库变得更小，从而减少磁盘的I/O，还能提高系统吞吐量，以很小的成本（耗费较多的CPU资源）。对于读比重比较多的应用，压缩是特别有用，压缩能够让系统拥有足够的内存来存储热数据。

在创建Innodb表时带上“ROW_FORMAT=COMPRESSED”参数能够使用比默认的16K更小的页，这样在读写时需要更少的I/O，对于SSD磁盘更有价值。

而压缩页的大小通过KEY_BLOCK_SIZE参数指定，不同大小的页意味着需要使用独立表空间，不能使用系统共享表空间，可以通过innodb_file_per_table参数指定开启独立表空间（MySQL 5.6开始默认开启）。KEY_BLOCK_SIZE的值越小，你获得I/O好处就越多，但是如果因为你指定的值太小，当数据被压缩到不足够满足每页多行数据记录时，会产生额外的开销来重组页。对于一个表，KEY_BLOCK_SIZE的值有多小是有严格的限制的，一般是基于每个索引键的长度。有时指定值过小，当create table或者alter table会失败。

在缓冲池中，被压缩的数据是存储在小页中的，这个小页的实际大小就是KEY_BLOCK_SIZE的值。为了提取和更新列值，MySQL也会在缓冲池中创建一个未压缩的16k页。任何更新到未压缩的页也需要重新写入到压缩的页，这时你需要估计缓冲池的大小以满足压缩和未压缩的页，尽管当缓冲空间不足时，未压缩的页会被挤出缓冲池。在下次访问时，不压缩的页还会被创建。

二、InnoDB创建压缩表

可以在每个表表空间（per tablespace）或一般表空间（general tablespace）中创建压缩表，表压缩不适用于InnoDB系统表空间（ibdata文件）。

要在每个表空间中创建压缩表，需要启用独立表空间参数innodb_file_per_table=1，并且也需要设置innodb_file_format=Barracuda，你可以写到my.cnf文件中然后动态设置参数值。然后使用CREATE TABLE或ALTER TABLE声明来创建压缩表。

• 如果你指定ROW_FORMAT=COMPRESSED，那么可以忽略KEY_BLOCK_SIZE的值，这时使用默认Innodb页的一半，即8kb。
• 如果你指定了KEY_BLOCK_SIZE的值，那么你可以忽略ROW_FORMAT=COMPRESSED，因为这时会自动启用压缩。

为了指定最合适KEY_BLOCK_SIZE的值，你可以创建表的多个副本，使用不同的值进行测试，比较他们的.ibd文件的大小。另外KEY_BLOCK_SIZE的值作为一种提示，如必要，Innodb也可以使用一个不同的值。0代表默认压缩页的值，Innodb页的一半。KEY_BLOCK_SIZE的值只能小于等于innodb page size。如果你指定了一个大于innodb page size的值，MySQL会忽略这个值然后产生一个警告，这时KEY_BLOCK_SIZE的值是Innodb页的一半。如果设置了innodb_strict_mode=ON，那么指定一个不合法的KEY_BLOCK_SIZE的值是返回报错。

InnoDB未压缩的数据页是16K，根据选项组合值，MySQL允许为每个表的.ibd文件使用1kb，2kb，4kb，8kb，16kb页大小。InnoDB会尝试把默认16kb的页压缩成KEY_BLOCK_SIZE页大小，如果无法压缩就按照KEY_BLOCK_SIZE大小来组织数据。实际的压缩算法并不会受KEY_BLOCK_SIZE值影响，这个值只是决定每个压缩页有多大，从而影响多少行被压缩到每个页。设置KEY_BLOCK_SIZE值等于16k并不能有效的进行压缩，因为默认的Innodb页就是16k，但是对于拥有很多BLOB，TEXT，VARCHAR类型字段的表可能会有效果的。

压缩表的限制

• MySQL 5.1之前的MySQL版本无法处理压缩表。
• 压缩表不能存储在InnoDB系统表空间中。
• 通用表空间可以包含多个表，但压缩和未压缩的表不能在同一个通用表空间中共存。
• 压缩适用于整个表及其所有相关索引，而不适用于单独的行，尽管关键字叫ROW_FORMAT（官方使用这个参数感觉是不太合适的）。

三、InnoDB表压缩优化

在进行表压缩时需要考虑影响压缩性能的因素，如：

– 哪些表需要压缩？

– 如何选择压缩表的页大小？

基于运行时性能特征是否需要调整buffer pool大小，如系统在压缩和解压缩数据所花费的时间量，系统负载更像一个数据仓库还是OLTP事务性系统。

如果在压缩表上执行DML操作，由于数据分布的方式，可能导致压缩失败，这时你可能需要配置额外的更高级的配置选项。

• 何时用压缩表

一般而言，对于读远远大于写的应用以及拥有合理数量的字符串列的表，使用压缩效果会更好。

• 数据特性及压缩率

影响数据文件压缩效率的一个关键因素是数据本身的结构，在页数据中，压缩是通过识别重复字符进行压缩的，对于完全随机的数据是一个糟糕的情况。一般而言，有重复数据的压缩更好。对于字符串的列压缩就不错，无论是string还是blob、text等类型的。另一方面，如果表中的数据是二进制类型，如整形、浮点型等或者之前压缩过的，如jpg、png类型的，压缩效果一般不好，但也不是绝对的。为了决定是否对某个表进行压缩，你需要进行试验，可以对比未压缩与压缩后的数据文件的大小，以及监控系统对于压缩表的工作负载进行决定。

对于简单的测试，如一个MySQL实例上没有其他的压缩表了，直接查询INFORMATION_SCHEMA.INNODB_CMP表数据即可，该表存一些压缩表的数据状态，结构如下：

参数解释：

除了INNODB_CMP表外，在INFORMATION_SCHEMA库下，你可能还会看见一个名为INNODB_CMP_RESET的表。这张表的作用就是当你访问时，系统会把INNODB_CMP表里面的信息转存到INNODB_CMP_RESET表中，同时意味着INNODB_CMP表会被重置。

对于上面这张表，我们只能看到一个总的信息。所以，MySQL也提供了INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX表，可以看到每个库每个表的每个索引压缩信息，但由于该表收集数据需要付出昂贵得代价，所以必须开启innodb_cmp_per_index_enabled参数才能查询（默认关闭）。一般不建议在生产环境下开启该选项。INNODB_CMP_PER_INDEX表信息结构如下：

当然，有INNODB_CMP_PER_INDEX表，也就同样存在一张INNODB_CMP_PER_INDEX_RESET表，作用与INNODB_CMP_RESET表相同。

还可以针对压缩运行一些测试SQL看看效率如何，如果发现很多压缩失败，那么你可以调整innodb_compression_level，innodb_compression_failure_threshold_pct和innodb_compression_pad_pct_max参数。

• 数据库压缩和应用程序压缩

不需要在应用端和数据库同时压缩相同的数据，那样效果并不明显而且还消耗很多CPU资源。对于数据库压缩，是在server端进行的。如果你在插入数据前通过代码进行数据压缩，然后插入数据库，这样耗费很多CPU资源，当然如果你的CPU有大量结余。你也可以结合两者，对于某些表进行应用程序压缩，而对其他数据采用数据库压缩。

• 工作负载特性和压缩率

为了选择哪些表可以使用压缩，工作负载是另一个决定因素，一般而言，如果你的系统是I/O瓶颈，那么可以使用CPU进行压缩与解压缩，以CPU换取I/O。

四、InnoDB表压缩原理

• 压缩算法

MySQL进行压缩是借助于zlib库，采用L777压缩算法，这种算法在减少数据大小、CPU利用方面是成熟的、健壮的、高效的。同时这种算法是“无损”的，因此原生的未压缩的数据总是能够从压缩文件中重构，LZ777实现原理是查找重复数据的序列号然后进行压缩，所以数据模式决定了压缩效率，一般而言，用户的数据能够被压缩50%以上。

不同于应用程序压缩或者其他数据库系统的压缩，InnoDB压缩是同时对数据和索引进行压缩，很多情况下，索引能够占数据库总大小的40%-50%。如果压缩效果很好，一般Innodb文件会减少25%-50%或者更多，而且减少I/O增加系统吞吐量，但是会增加CPU的占用，你可通过设置innodb_compression_level参数来平衡压缩级别和CPU占用。

• 数据存储及压缩

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

InnoDB表中的辅助索引（secondary indexes）也是B树，包含键值对：索引键和指向聚簇索引中的某个行的指针。 指针实际上是表的主键的值，在需要除索引键和主键之外的列时，则需要查找到该值用来访问聚簇索引，进而访问到需要的列。辅助索引记录必须始终可以放在单个B树页面。

B类树节点（聚簇索引和辅助索引）的压缩处理方式与用于存储长VARCHAR，BLOB或TEXT列的溢出页面的压缩有所不同，如以下部分所述。

B树中页的压缩

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

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

此外，MySQL尝试在更改索引页时避免不必要的解压缩和重新压缩。在每个B树页面中，系统保留未压缩的“modification log(mlog)”来记录对页面所做的更改。可以将对较小记录的update和insert直接写入这个mlog，而不去重构整个页面。当mlog的空间用尽时，InnoDB解压缩页面，将更改应用于将被重新压缩的页面。如果重新压缩失败（称为压缩失败的情况），则B树节点被拆分，该过程将重复直到update或insert成功。

为了避免在写入密集型工作负载（如OLTP应用程序）中频繁的压缩失败，MySQL有时会在页面中保留一些空白空间（填充(padding)），这样mlog会更快被填满，页面被重新压缩时就会有足够的空间避免分裂。随着系统跟踪页面分割的频率，每页中留下的填充空间量会有所不同。在进行频繁写入压缩表的服务器上，您可以通过innodb_compression_failure_threshold_pct和innodb_compression_pad_pct_max配置选项来调优这个机制。

通常，MySQL要求InnoDB表中的每个B-tree页面至少可容纳两个记录。对于压缩表，这个要求被放宽了。 B树节点的叶子页面（无论是主键还是辅助索引）只需要容纳一个记录，但该记录的未压缩的形式必须能够存储在每页修改日志。如果innodb_strict_mode为ON，则在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进行严格执行的。 有关更多信息，请参见“CREATE TABLESPACE语法”。

压缩BLOB, VARCHAR和TEXT列

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

对于用ROW_FORMAT = DYNAMIC或ROW_FORMAT = COMPRESSED参数创建的表，BLOB、TEXT或VARCHAR列的值可能会完全存储在页外，具体会取决于列长度和整行长度。对于非页外存储的列，聚簇索引记录仅包含20字节指向溢出页的指针。一列是否被存在页外取决于页大小和整行的大小。如果行太长而不能放入聚簇索引的页时，MySQL会选出最长的列吗，将其放在页外，知道页的大小合适放入聚簇索引中。如上面提到的，如果一行在压缩页上不合适，就会发生错误。

注意：对于在ROW_FORMAT = DYNAMIC或ROW_FORMAT = COMPRESSED中创建的表，小于或等于40字节的TEXT和BLOB列会始终保存在页内。

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

当表格采用COMPRESSED格式时，写入溢出页的所有数据也照样压缩；也就是说，MySQL会将zlib压缩算法应用于整个数据项。除了数据之外，压缩的溢出页还包含一个未压缩的header部分和trailer部分，其中包括页校验和到下一个溢出页的指针等信息。因此，如果数据是高度可压缩的，那么对于较长的BLOB，TEXT或VARCHAR列会和文本数据一样，也可以非常显著地节省存储空间。图像数据（例如JPEG）通常已被压缩，因此不会因为存储在压缩表中而获得得太多收益，双重的压缩可以只会浪费CPU周期或节省很小的空间。

溢出页与其他页大小相同。有10列需要存储在页外的行就要占用10个溢出页，所以即使要放入溢出页的列总大小只有8K字节。因此，在未压缩的表中，10个未压缩的溢出页占用160K字节；而在8K页面大小的压缩表中，它们只占用80K字节。因此，对于具有列值较长的表，使用压缩表格式往往更高效。

对于有file-per-table属性的表空间，使用16K的压缩页大小可以减少BLOB，VARCHAR或TEXT列的存储和I/O成本，这样虽然B树的节点本身的数量和不压缩相比并未减少，但由于这种情况下BLOB，VARCHAR或TEXT类型的数据经常被压缩得很好，所以这样可以减少溢出页的数量。通用表空间不支持16K压缩页大小（KEY_BLOCK_SIZE）。有关更多信息，请参见第15.7.9节“InnoDB常规表空间”。

• 压缩与InnoDB缓冲池(Buffer Pool)

InnoDB页压缩设计之初就是为了性能，对于压缩的InnoDB表，在磁盘存放压缩页，而每个压缩页（无论是1K，2K，4K还是8K）都对应于一个16K字节的未压缩页面（如果设置了innodb_page_size，可能是其他大小）在缓冲池中。要访问页面中的数据，如果相应的页没有在缓冲池中，那么MySQL会先从磁盘读取压缩页面，然后将页面解压缩到其原始格式。本节将介绍对于InnoDB缓冲池的管理方法中与压缩表页面相关的部分，压缩页在缓冲池中被存储在unzip_lru链表中。

当从磁盘把压缩页载入内存后，假设key_block_size=8K， Buffer Pool的页大小是16K，会向Free List中申请空闲的页，如果没有空闲页，则向LRU List申请页，如果LRU满了，则找LRU中最后的一个页，如果最后的页是脏页，则做flush操作，最后得到一个空白的页（16K），该16K的空白页，就给8K的压缩页使用。这样就多出一个8K的空间，该空间会移到8K的Free List中去，如果有一个4K的压缩页，就把8K的Free list中的空白页给它用，然后多余的4K的空间移到4K的Free List中去。通过上述方式，不同大小的页可以在同一个Buffer Pool中使用（可以简单的认为Free List是按照页大小来进行划分的）。但不能根据页大小来划分缓冲池，缓冲池中页的大小就是固定的大小（ 等于innodb_page_size ）；另外，LRU List和Flush List不需要按照页大小划分，都是统一的innodb_page_size大小。

然后被压缩的页需要在Buffer Pool中解压，为了最小化I/O并减少对页面进行解压缩的频次（提高性能），此时InnoDB并不会把压缩页丢弃，也就意味着内存中很有可能会同时存在压缩和未压缩页。这样的缺点是压缩页占用了Buffer Pool的空间，对于热点数据来说，相当于内存小了，可能造成性能下降（热点空间变小）。所以在开启了压缩后，Buffer Pool的空间要相应增大；如果启用压缩后节省的磁盘IO能够抵消掉Buffer Pool空间变小所带来的性能下降，那整体性能还是会上涨。为了其他必需的数据库页面腾出空间，MySQL可能从缓冲池中丢弃一些未压缩的页面，同时将这个页对应的压缩页留在内存中。或者，如果一段时间没有访问页面，页面的压缩形式可能也会写入磁盘，以为其他数据的释放内存空间。因此，在任何时间点，缓冲池可能包含一个页面的压缩和未压缩的形式，或者只包含这个页面的压缩形式，或者两者都不包含。

压缩页保留的原因是为了在更新数据的时候，先直接更改未压缩页，同时会记录修改操作的重做日志进压缩页的空闲部分。如果要刷回磁盘，可以直接将该压缩页刷回去。如果该页被写满，则做一次reorganize操作（在重新整理之前也要做解压），真的写满了才做分裂。这样的设计是为了不用每次有修改操作都需要进行解压操作，提高性能。

MySQL会跟踪哪些页面保留在内存中，哪些页是使用最近最少使用的（LRU）列表去丢弃的，所以热（经常被访问的）数据更倾向于被保留在内存中。当访问压缩表时，MySQL使用自适应LRU算法来实现内存中压缩和未压缩页面的适当平衡。这种自适应算法对于I/O密集型或CPU密集型的系统都是敏感的。目标是避免在CPU繁忙时花费太多CPU时间来解压缩页面，或者在CPU具有空闲周期可以用于解压缩页(可能已经在内存中)时避免进行多余的I/O。当系统是I/O密集型时，该算法优先于逐出为压缩形式的副本而不是两个副本，从而为其他压缩页面驻留内存留下更多的空间。当系统受CPU限制时，MySQL更倾向于同时丢弃压缩和未压缩的页面，以便更多的内存用于存储“热”页面，避免浪费CPU解压内存中的仅存压缩形式的页面。

通过show engine innodb status可以查看LRU链表及UNZIP_LRU链表的长度。另外通过information_schema.INNODB_BUFFER_PAGE_LRU系统视图可以查看具体的压缩页信息，包括是否是压缩页及页大小。

五、OLTP系统压缩负载优化

传统上，InnoDB压缩功能主要用于只读或大部分读取的工作负载，例如在数据仓库配置中。快速但相对较小和昂贵的SSD存储设备的兴起使得压缩对于OLTP工作负载也越发具有吸引力的：高流量的交互式网站可以通过使用压缩表同时使用具有执行频繁INSERT，UPDATE和DELETE操作的应用程序来减少其存储需求和每秒I/O操作数(IOPS)。

MySQL 5.6中引入的配置选项可让您调节压缩对特定MySQL实例的工作参数，其对于写入密集型操作的性能和可扩展性是很重要的：

• innodb_compression_level

决定压缩程度的参数，如果你设置比较大，那么压缩比较多，耗费的CPU资源也较多；相反，如果设置较小的值，那么CPU占用少。默认值6，可以设置0-9。

• innodb_compression_failure_threshold_pct

指定了一个进行页压缩失败的百分比阈值，当它被超过时，MySQL开始在每个新的压缩页中增加一些额外的空闲的保留空间(padding)，动态的调整这个空余空间的大小，padding的上限是由innodb_compression_pad_pct_max指定的，它是页面大小的一个百分比。默认值5，范围0到100，设置中断点避免高昂的压缩失败率。

• innodb_compression_pad_pct_max

允许你调整每个页面中用于直接保存更改和插入数据的空闲保留空间（padding）的最大值（这样就无需每次更改和插入都再次压缩整个页面）。 值越高，就可以记录更多的更改，而无需重新压缩页面。MySQL运行时，只有当需要昂贵分割操作的”压缩失败”达到上一个参数所指定的百分比时，才会为每个压缩表中的页面增加一个大小可变的空余空间（padding）。

• innodb_log_compressed_pages

默认情况下，此选项被启用，以防止在恢复期间使用不同版本的zlib压缩算法时可能会发生损坏。 如果你确定zlib版本不会更改，请禁用innodb_log_compressed_pages以减少为修改压缩数据而生成的重做日志。

由于开启压缩时，内存中可能同时保留存在数据的压缩版本和未压缩版本，所以在OLTP工作负载下进行压缩时，innodb_buffer_pool_size配置选项的值可能会需要增加。

六、测试表压缩

没有设置压缩前的数据大小

开启测试

总结：通过以上测试可知，当KEY_BLOCK_SIZE的值设置为4或者8时，压缩效果最好，设置为16效果最差，因为页的默认值16K。通常我是设置为8。

下面是我一个100G日志表压缩的结果：

压缩比还是很可观的，执行了4个半小时（40核，128G，SSD）。然后我们看一下INNODB_CMP表记录的压缩信息。

可以看出，目前我们这个压缩成功率还是很高的。

<参考>

http://blog.jcix.top/2017-04-06/innodb_table_compression/

https://dev.mysql.com/doc/refman/5.7/en/innodb-table-compression.html

喜欢 (1)赏

[资助本站您就扫码 谢谢]

分享 (0)