【MySQL运维】使用表压缩功能降低磁盘消耗

一、数据库表压缩介绍

当业务数据发展到一定规模时占用的存储资源往往都比较高，为了在一定程序上降低物理存储消耗可以考虑启用MySQL的表压缩功能。MySQL默认不开启表压缩的，因为表压缩会带来一定的CPU性能损耗。但其实因为压缩带来的额外CPU指令并不意味着性能就一定会下降，甚至或许能提升性能。因为以现在的硬件性能和业务场景来看，CPU通常并不是数据库的瓶颈，而I/O负载才是。使用压缩技术后，MySQL可以把原本16K/个的数据页压缩为8K甚至4K，这样在从磁盘写入或读取时，就能将I/O请求降低，从而提升数据库性能。对于性能层面来说，是否开启压缩取决于性能开销和收益比例是否平衡；对存储空间来说，压缩可以有效整理数据容量，肯定是带来巨大收益的。

二、MySQL压缩表设计

1、压缩方式介绍

· COMPRESS页压缩

COMPRESS页压缩是MySQL 5.7版本之前提供的页压缩功能。该压缩方式对性能比较大，因为一个压缩页在内存缓冲池中存在压缩和解压两个页。所以COMPRESS适用于一些对性能不敏感的业务表，例如日志表、监控表、告警表等，压缩比例通常能达到50%左右。在创建表时指定ROW_FORMAT=COMPRESS就可以开启压缩，通过KEY_BLOCK_SIZE设置压缩比例。

TPC（Transparent Page Compression）是 5.7 版本推出的页压缩功能，其利用文件系统空洞特性（Punch Hole）进行压缩，并且对数据库性能几乎无影响（小于 20%），甚至带来性能提升。在一些较为核心的流水业务表上推荐使用TPC压缩。因为流水信息通常伴随了核心业务，整张表会比较大，但是对性能又有要求，如一笔电商交易，用户下单、付款、记流水。所以可以按月存储，对当前正在使用的流水表不启用TPC功能，对历史流水表启用 TPC压缩。

2、开启表压缩

· 开启COMPRESS压缩

#启用COMPRESS页压缩，并将一个16K的页压缩为8K
CREATE TABLE test1 (
  id int PRIMARY KEY,
  ......
)
ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8

· 开启TPC压缩

CREATE TABLE Transaction （
  transactionId BINARY(16) PRIMARY KEY,
  .....
)
COMPRESSION=ZLIB | LZ4 ;

3、对已存在的数据进行压缩

对于已经存在的表，可以通过命令ALTER TABLE xxx COMPRESSION = ZLIB启用压缩，但是只对新增的数据进行压缩，原有数据则不进行压缩（通过ALTER TABLE 操作只是修改元数据，瞬间就能完成）。若想要对整个表进行压缩，需要执行OPTIMIZE TABLE命令

ALTER TABLE Transaction202102 COMPRESSION=ZLIB；
OPTIMIZE TABLE Transaction202102;