MYSQL 不同的表格式，导致不同的存储空间消耗和性能差异 横向评测

MYSQL 在建立之初，表的格式就有好几种，与其他的数据库不同，你从未听说 ORACLE ,SQL SERVER , PG 对于表的存储格式有不同，而MYSQL 在建表的时候有一个地方对于存储的表的格式有不一样的设定。

如果你知道我在说什么那么对于下面的几个单词一定是熟悉的， DYNAMIC , FIXED ,COMPRESSED , COMPACT ，REDUNDANT . 如果你不知道我在说什么，那么就可以往下看。

在MYSQL 建表的时候，会需要你在 ROW_FORMAT 指定你的行存储格式，如果你不曾在这个位置上动过心思，那么今天就动动心思。

ROW FORMAT 是指标中的行在磁盘中的物理存储的格式，一般我们在MYSQL8上建立的表如果你不指定row_format的格式的情况下，默认我们的表存储的格式是 DYNAMIC . 在更早期的MYSQL 5.6 时我们的表的格式默认是compact .

首先我们要确认以下的问题，dynamic 是compact格式进化而来的，而compressed的是dynamic进化而来的。而dynamic与compact最大的不一样在以下几个方面

1 针对可变字符类型的长度的字符类型的存储的支持

2 针对前缀索引的支持

而compressed 格式在dynamic的基础上，增加了压缩的数据处理的支持。

今天要谈这个问题，主要思路来自于，公司存储在MYSQL上的数据一直都有需要归档的需求，而数据归档临时的数据也是要存储在MYSQL上的，那么降低数据存储的空间，对于数据存储的空间的消耗是有利的。

所以今天就针对一个表在dynamic 和 compact 下到底能节省多少空间来做一个比较，同时也针对性能上面进行一个比对。

我们先产生一张表

CREATE TABLE `t_t` ( `id` int NOT NULL, `a` int DEFAULT NULL, `b` int DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

First blood， 我们使用REDUNDANT，插入数据 10000000 ，时间53.71

Double kill，我们使用compact , 插入数据10000000， 时间58.04

Triple kill, 我们使用 dynamic, 插入数据10000000，时间64.71秒

Quadra kill，我们使用compressed,插入数据10000000，时间为 2分37秒。

Penta kill, 我们使用表压缩方式为LZ4 插入数据10000000，时间为 63秒

unstoppable，我们使用表压缩方式为ZLIB 的方式，插入10000000，时间为68秒

下面我们来看看这些表在存储空间的存储比对是怎样的

select table_schema, table_name, table_rows, round(data_length / 1024 / 1024)+round(index_length / 1024 / 1024)+round(data_free / 1024 / 1024) TOTAL_MB, create_options from information_schema.tables where table_name like 't%' and table_schema = 'test';

通过比对，的确使用 compressed 格式处理数据与他的存储的速度相比较的确存储空间少了一半，而其他的格式，相比较差别并不大。（实际上这里面有隐藏的小秘密），尤其LZ4 和 ZLIB 两种格式压缩数据后，并没有让我们得到多大的空间压缩。

实际上小秘密就是，我们的字段类型是 INT 类型，这样其实我们就是为了看看实际上那种格式对数据的存储的时间和空间。

那么实际上我们还可以针对字符型的字段进行一个测试，看看那种的方式对比存储INT 有什么不同。

create table t_compress (id int not null,a int default null,b int default null, primary key (id)) engine=innodb default charset=utf8mb4 row_format=compressed; create table t_dynamic (id int not null,a int default null,b int default null, primary key (id)) engine=innodb default charset=utf8mb4 row_format=dynamic; create table t_compact (id int not null,a int default null,b int default null, primary key (id)) engine=innodb default charset=utf8mb4 row_format=compact; create table t_REDUN (id int not null,a int default null,b int default null, primary key (id)) engine=innodb default charset=utf8mb4 row_format=REDUNDANT; select table_schema, table_name, table_rows, round(data_length / 1024 / 1024)+round(index_length / 1024 / 1024)+round(data_free / 1024 / 1024) TOTAL_MB, create_options from information_schema.tables where table_name like 't%';

create table t_compress (id int not null,a int default null,b int default null, primary key (id)) engine=innodb default charset=utf8mb4 row_format=compressed; create table t_dynamic (id int not null,a int default null,b int default null, primary key (id)) engine=innodb default charset=utf8mb4 row_format=dynamic; create table t_compact (id int not null,a int default null,b int default null, primary key (id)) engine=innodb default charset=utf8mb4 row_format=compact; create table t_REDUN (id int not null,a int default null,b int default null, primary key (id)) engine=innodb default charset=utf8mb4 row_format=REDUNDANT; select table_schema, table_name, table_rows, round(data_length / 1024 / 1024)+round(index_length / 1024 / 1024)+round(data_free / 1024 / 1024) TOTAL_MB, create_options from information_schema.tables where table_name like 't%';

实际上，我们的表可不是单独的INT 的类型，而是更多的类型这里主要体现在压缩上，我们尝试建立一个字符类型的表看看，压缩后的状态如何

FIRST BLOOD ，我们在REDUNDANT 表中插入字符型数据1000000，需要时间29.63秒

Double kill， 我们在compact 模式里面插入字符类型数据1000000，需要时间10.95秒

Triple kill， 我们在dynamic 表中插入同样行的数据用时 11.96秒

Quadra kill，在使用 compress 模式下，我们插入同样的数据需要23.29秒

Penta kill, 我们使用表压缩方式为LZ4 插入数据，需要插入同样的数据需要12.43秒

unstoppable， 我们使用ZLIB 方式插入数据需要

在操作完毕后我们比对一下各个格式的数据大小，和明显，最小的还是compresed的数据格式，而 这里其他的格式对文件大小的收缩不是很明显,尤其REDUNDANT 格式。

通过mysqlslap 压测时，将表的 row_format 变更为 compressed 的性能消耗

测试主要分为以下几个部分

1 针对多线程的方式进行测试并且测试的角度为 MIXED ，也就是测试中包含了 UPDATE SELECT INSERT DELETE 等操作，来比较在表 compressed 和 dynamic 两种模式下的数据库操作的消耗对比不明显

2 写入操作测试，测试针对表进行写入操作时的，insert ,对于数据库CPU IO M MEMORY 的消耗问题，在小批量操作时，两种格式的性能差异不明显。

综上所述：MYSQL 不同的ROW_FORMAT 格式对于数据占用的空间除了 compressed 格式以外，在空间的相差并不大。

下面我们提供 2 组 通过NMON 分析后的 系统性能分析，实际上也可以看出性能的差异，但是对比上面的时间消耗想必，并不是那么明显而已

第一组为不压缩的情况下的CPU 消耗和磁盘性能消耗等

第二组为 压缩格式下，CPU 和磁盘系统的

最后说说为什么要做这件事，主要的原因

1 在不重要的项目当中，尤其项目的数据写入和读取并不频繁，但数据量积累的较大的情况下，可以采用compressed 来处理一些，日志表，或者非业务数据库

2 针对归档的数据，直接使用compressed 的格式处理，增加存储空间性价比。