MySQL InnoDB 表压缩(行格式压缩)

MySQL, 数据库 / 作者：

MySQL InnoDB支持数据压缩，有两种数据压缩方式，第一种为表压缩，通常也称之为行格式压缩，另外一种是页压缩，页压缩对操作系统及文件系统有一定的要求。本文主要介绍表压缩(行格式压缩)的原理及使用方法。

表压缩适用的场景：

  1. 有很多字符串类型的字段，比如char, varhcar, text之类。
  2. 读多写少的业务场景。

表压缩支持独立表空间和共享表空间，不支持系统表空间。

  • 独立表空间：file-per-table
  • 共享表空间：general tablespace

1. 独立表空间创建压缩表

前提条件：
innodb_file_per_table=ON
innodb_file_format=Barracuda

创建语法：
CREATE TABLE t1 (c1 INT PRIMARY KEY) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

2. 共享表空间创建压缩表

前提条件：
在general tablespace表空间中创建压缩表，必须要设置FILE_BLOCK_SIZE，它的有效值与innodb_page_size相关，KEY_BLOCK_SIZE 必须为 FILE_BLOCK_SIZE/1024。

例如：
innodb_page_size=16384
FILE_BLOCK_SIZE=8192
KEY_BLOCK_SIZE=FILE_BLOCK_SIZE/1024=8

创建语法：
CREATE TABLESPACE ts2 ADD DATAFILE ‘ts2.ibd’ FILE_BLOCK_SIZE = 8192 Engine=InnoDB;

CREATE TABLE t4 (c1 INT PRIMARY KEY) TABLESPACE ts2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

3. 表压缩语法及原理

3.1 表压缩语法

InnoDB使用如下语法进行压缩表的创建：
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

如果没有指定KEY_BLOCK_SIZE值，默认为innodb_page_size值的一半，也就是8KB。

32KB 和 64KB的页大小不支持压缩。默认的InnoDB未压缩的页大小为16KB，KEY_BLOCK_SIZE 可设置为1KB，2KB，4KB，8KB，16KB，KEY_BLOCK_SIZE应当设置为大于表中一条记录的最大长度，实际的压缩算法不受KEY_BLOCK_SIZE值影响，这个值决定每个压缩块有多大，间接影响一个压缩后的页能够存放多少个行记录。如果将KEY_BLOCK_SIZE设置的与页大小一致，比如16K，不会导致太多的压缩，因为正常的一个页面就是16K，但是这样的设置对于blob, text, varchar类型有很好的压缩效果。

3.2 表压缩原理

处理器和内存的计算和存储速度远远大于磁盘IO，因此大多数场景下，数据库性能瓶颈在磁盘IO上，数据压缩能够显著减少数据的大小，减少磁盘IO次数，提高吞吐量，唯一的成本是CPU消耗将会稍微增加。数据压缩对于读密集型的应用有非常好的效果。

创建压缩格式的表，能够使用比innodb_page_size更小的页，更小的页意味着更少的磁盘IO读写。

压缩之后的页大小由参数 KEY_BLOCK_SIZE 指定。如果该值指定的越小，可能会获得越好的IO性能提升，但是如果该值太小，可能会带来其他的额外开销，比如压缩后的数据无法完整容纳在一个非常小的页面里。当然对于KEY_BLOCK_SIZE的值，有一个硬性限制，这个限制基于索引字段的长度，如果KEY_BLOCK_SIZE太小，则会导致create table或者alter table语句执行报错。

对于一个打开压缩功能的表，表中的所有索引，包括聚簇索引、二级索引，都会使用同样的页大小进行压缩。

在buffer pool里面，压缩后的数据存放在更小的页里，页大小由KEY_BLOCK_SIZE指定。如果要更新一个压缩页面里的字段值，需要在buffer pool里面创建一个未压缩的页面来存放解压后的数据，在buffer pool里面，对未压缩页的更新都会重新写回与之相对应的压缩的页。因此打开压缩功能之后，需要增加buffer pool的大小，以容纳额外的数据（压缩和未压缩的页面)，当buffer pool里面空闲空间不足时，未压缩的页会被释放，而当下一次访问需要未压缩页面时，它会被再次创建。

4. 表压缩监控

主要通过information_schema里面的几个表来监控压缩状态。

  • information_schema.INNODB_CMP
  • information_schema.INNODB_CMPMEM
  • information_schema.INNODB_CMPMEM_RESET
  • information_schema.INNODB_CMP_PER_INDEX，默认这个表的监控是关闭的，需要设置参数innodb_cmp_per_index_enabled=ON打开，但对性能有一定影响。

5. 表压缩相关参数

  • innodb_compression_level，设置压缩级别，取值范围0~9，默认值为6。
  • innodb_compression_failure_threshold_pct，定义了表压缩失败率阈值，超过这个阈值时，通过增加压缩页中的保留空间，来降低压缩失败率。取值范围0~100，默认值为5。
  • innodb_compression_pad_pct_max，压缩页中保留的空闲空间的最大比例，取值范围0~75，默认值为50。
  • innodb_log_compressed_pages，指定重新压缩的页数据是否写入redo log里面，默认值为ON，如果zlib版本不变，建议设置为OFF。

当表压缩导致CPU负载过高，可以考虑降低压缩级别(innodb_compression_level)，来减少压缩对CPU的消耗。

6. 表压缩使用限制

  • MySQL 5.1 之前的版本不支持表压缩。
  • 压缩表不能存放在InnoDB系统表空间。
  • General tablespaces 表空间可以包含多个表，但是压缩的表和未压缩的表不同共存于一个General tablespaces里面。
  • 表压缩是对整个表的压缩，包括与表相关的所有索引，不能只对指定的行进行压缩。

发表评论