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

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里面。
  • 表压缩是对整个表的压缩,包括与表相关的所有索引,不能只对指定的行进行压缩。

发表评论