MySQL 表压缩

压缩技术

随着大数据时代的到来，海量数据的灌入到数据库服务器上，给已有的传统模式数据库带来的非常大的变化挑战。其实抛开分布式这些方式，在一定的范围内，数据压缩能缓解这个问题。压缩其实就是挤压数据使得它占用更少的磁盘存储空间和更短的传输时间。目前常使用的压缩技术方式 如：snappy，zlib，LZ4，zstd，gzip，winrar，在处理能力，响应时间，网络带宽传输 等方面基本都会有1ms~10ms的差距。

数据压缩本质是为了节约空间，压缩率越大，占用的磁盘空间越小，文件传输时间提升，降低数据的存储和网络传输成本。
另一个方面
对于CPU，io来说 是通过提高CPU利用率和节约成本，降低数据库容量及I/O负载，从而使数据吞吐率得到显著提高。

对于数据库来而言，数据压缩不仅大大减少了数据存储的所需的存储空间，而且还减少了I/O的工作量，提高了数据吞吐率，从而节约开销处理成本。

目前在数据库技术方面节省存储成本固然重要，但是减少I/O成本更为关键。

下面了解下MySQL方面压缩实现是怎样实现的。

InnoDB行格式

MySQL里表的行格式决定了其行的物理存储方式，这反过来又会影响查询和DML操作的性能。由于一个磁盘页可以容纳更多的行，查询和索引查找可以更快地工作，在缓冲池中需要更少的缓存内存，并且需要更少的I/O来写出更新的值。

1. 在InnoDB中，是以16K的页（Page）为基本的存储单位的。每个表中的数据被分成若干页。组成每个表的页面被安排在B Tree树索引的树数据结构中。
   InnoDB的数据是在Clustered index中存储的，Secondary index中仅存储对应数据的PK。Clustered index和Secondary index都是B Tree结构的，所以对InnoDB数据页和索引页的压缩很大程度上就是对B Tree节点页的压缩。

2. 在InnoDB中，除了B-Tree节点页，还有一类数据页（Page），称为“overflow page”。当需要存储Long column时，如果当前页能够完全存储全部字段时，则存储在当前页中；如果当前页不足以存储全部，则InnoDB选择最长的字段，将其存储到一个单独的页中，称这样的页为“overflow page”，而原数据页仅仅需存储一个20Bytes的指针

InnoDB存储引擎支持四种行格式:REDUNDANT、COMPACT、DYNAMIC、COMPRESSED。
这些数据都是属于紧凑的存储特性。

• REDUNDANT行格式的表将可变长度列值(VARCHAR、VARBINARY、BLOB和TEXT类型)的前768个字节存储在B-tree节点的索引记录中，其余的存储在溢出页上

• COMPACT行格式是REDUNDANT的增强版，COMPACT行格式减少了大约20%的行存储空间，但代价是增加了某些操作的CPU使用量。

• DYNAMIC行格式提供与COMPACT行格式相同的存储特性，但为可变长列添加了增强的存储功能，并支持大型索引键前缀。
  InnoDB可以存储长可变长度的列值(对于VARCHAR、VARBINARY、BLOB和TEXT类型)，这些列值完全在页外，聚集索引记录只包含一个指向溢出页的20字节指针。大于或等于768字节的固定长度字段被编码为可变长度字段。
  DYNAMIC格式，较短的列可能保留在b树节点中，从而最大限度地减少给定行所需的溢出页数。
  DYNAMIC行格式支持最多3072字节的索引键前缀
  DYNAMIC支持 innodb_file_per_table表空间 ，system tablespace general tablespaces

• COMPRESSED行格式提供与DYNAMIC行格式相同的存储特性和功能，但增加对表和索引数据压缩的支持。COMPRESSED行格式在页外存储中使用了与DYNAMIC行格式类似的内部细节，同时还考虑被压缩的表和索引数据的额外存储和性能，并使用更小的页。

MySQL默认采取DYNAMIC方式，要是压缩建议采用COMPRESSED方式；

压缩规则

压缩表可以在每个表的文件表空间(file-per-table)，也可以在general tablespaces.
但InnoDB的system tablespace（ibdata文件)）是不能压缩的。
。因此，压缩只适用于存储在每个表文件或一般表空间中的表(和索引)。

在配置了innodb_file_per_table选项后，在CREATE TABLE或ALTER TABLE语句中指定ROW_FORMAT=COMPRESSED子句或KEY_BLOCK_SIZE子句，或同时指定这两个子句，以在每个文件的表空间中创建一个压缩表。

在 innodb_page_size → FILE_BLOCK_SIZE → KEY_BLOCK_SIZE 之间形成了一种紧密的关系。

FILE_BLOCK_SIZE的值必须是一个相对于innodb_page_size值有效的压缩页面大小
例如，innodb_page_size=16384, FILE_BLOCK_SIZE=8192，则表的KEY_BLOCK_SIZE必须为8
对应管辖可以看以下对比：

1.innodb_page_size

MySQL的innodb引擎在系统层面处理数据的最小单位默认16kb。【偶尔咨询可否是8k，目前碰过8kb使用的案例，基本稳定。但需要考虑做好场景和测试验证，page小IO交互能力要提升】

2.FILE_BLOCK_SIZE

特定于InnoDB（ndb忽略)表空间数据文件的块大小。如果不指定该选项，FILE_BLOCK_SIZE默认值为innodb_page_size.当使用表空间来存储压缩的InnoDB表(ROW_FORMAT= compressed)时，FILE_BLOCK_SIZE是必需的.如果FILE_BLOCK_SIZE等于innodb_page_size，则表空间只能包含未压缩的行格式(COMPACT, REDUNDANT, DYNAMIC)。

具有压缩行格式的表与未压缩的表的物理页大小不同。因此，压缩表不能与未压缩表共存于同一个表空间中。压缩表的物理页大小(KEY_BLOCK_SIZE)必须等于FILE_BLOCK_SIZE/1024

如果在创建通用表空间时不指定FILE_BLOCK_SIZE，则FILE_BLOCK_SIZE默认为innodb_page_size。当FILE_BLOCK_SIZE = innodb_page_size时，表空间只能包含未压缩行格式(COMPACT、REDUNDANT和DYNAMIC行格式)的表。

3.KEY_BLOCK_SIZE:

对于MyISAM表，KEY_BLOCK_SIZE可选地指定用于索引键块的字节大小
对于InnoDB表，KEY_BLOCK_SIZE以千字节为单位指定用于压缩InnoDB表的页面大小.KEY_BLOCK_SIZE只能小于或等于innodb_page_size值。0表示默认的压缩页面大小，它是innodb_page_size值的一半。根据innodb_page_size的不同，可能的KEY_BLOCK_SIZE值包括0、1、2、4、8和16

• InnoDB只支持表级的KEY_BLOCK_SIZE。
• KEY_BLOCK_SIZE不支持32KB和64KB的innodb_page_size值。InnoDB表压缩不支持这些页面大小。
• InnoDB在创建临时表时不支持KEY_BLOCK_SIZE选项。

最终形成一个配置关系图：

操作方式

MySQL默认采取dynamic行压缩格式

mysql > SHOW VARIABLES LIKE '%innodb_default_row_format%';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_default_row_format | dynamic |
+---------------------------+---------+
1 row in set (0.01 sec)

mysql> SET GLOBAL innodb_file_per_table=1;
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) ROW_FORMAT=COMPRESSED  KEY_BLOCK_SIZE=8;

或 指定表空间方式：

#1. 先指定FIlE_BLOCK_SIZE
mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;
#2. 指定压缩
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

性能测试

压缩比：100W
环境：2G内存 1核cpu SSD盘100G

备注：容量方面压缩到50%，因为环境cpu能力有限 处理能力反之下降50%。

总结

可以说，压缩技术是损失一种资源,提升性能的一种方式。
压缩实现需要多核CPU支持，CPU支持力度不够的情况下，能力反而下降。
实际运行环境MySQL本身数据处理量有限，比如单表5000W 也就10G+的文件，所以没有必要进行压缩。