MySQL中使用InnoDB引擎数据存储格式分析

概要

mysql数据库作为关系型数据库，是我们日常工作作为开源数据存储的主要工具之一，又因为InnoDB引擎对事物有着良好的支持，更凸显出来这个免费的开源数据库的优势。所以今天我们就基于使用了InnoDB引擎的mysql的数据存储进行分析。

InnoBD数据存储行格式分类

表的行格式决定了行的物理存储方式，进而影响查询和 DML 操作的性能。随着更多行可容纳在单个磁盘页中，查询和索引查找可以更快地运行，缓冲池中所需的缓存内存更少，并且写出更新值所需的 I/O 更少。

存储InnoDB引擎支持四种行格式：Redundant，Compact，Dynamic，Compress。下图四种行格式的对比图。

对于上述的四种行格式的对比，区别最大的就是是否支持紧凑型存储。 紧凑型存储就是数据存储使用的磁盘空间更小。所以可以划分为两类：

• 不支持紧凑型存储：Redundant，使用的磁盘空间更大，但使用的cpu资源更少。一般我们只会在低版本上使用。
• 支持紧凑型存储：Compact，Dynamic，Compress，使用的磁盘空间更小，官方文档上说节约了大概20%的存储空间，但是在某些操作上会占用更多的cup资源。当拥有了紧凑型的存储，意味着，我们能加载到数据库的buffer pool当中的数据量会更多。按照题主的理解，我们在内存当中读取的数据的速度比硬盘快的不是一个量级的，损耗的那点cpu资源不算啥。所以为啥现在大家都选择这三种模式作为数据存储的行格式。

鉴于上述的三种格式比较类似，题主选用Compact格式进行分析他们的行数据存储格式分析。

Compact行格式

假设我现在有如下的一张学生表，其中name和age及interest默认都为空

CREATE TABLE `t_student` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(64) DEFAULT NULL COMMENT '名称',
  `age` int(3) DEFAULT NULL COMMENT '年纪',
  `interest` varchar(255) DEFAULT NULL COMMENT '兴趣爱好',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 
COMMENT='学生表';

对于上述的表结构，我们存在两个变长字段 、三个默认为NULL字段。mysql定义的单行长度数据在不包含（text，blob字段格式时）不能超过65535个byte，即我们按照行格式定义的所有的数据，变长长度列表+NULL值字段列表+记录头信息+row_id+trx_id+roll_pre_id+字段信息。

COMPACT行格式具有以下存储特征：

• 变长长度列表也不是必须的，当我们都使用char定义字段时，就可以最少节省一个byte。
• 变长长度列表按照逆序存储字段所占用的字节数。当一个字段长度小于255（一个无符号byte最大表示255），使用一个byte存储。大于255使用两个byte表示
• 只有表结构定义当中存在NULL值定义，才会有NULL值列表。所以当数据库字段都为必填时，可以将字段都设置为NOT NULL，这样数据在存储时可以节省一个字节的空间。
• NULL值列表当中如果只有一个字段定义为NULL时，最少也占用一个byte，不足的部分bit位数据补0。 字节数为(N/8) 个Byte，N为为NULL的字段数。
• NULL值列表占位时，是逆序占位。比如我插入一条数据。INSERT INTO t_student (id, name,age, interest) VALUES (1, NULL, 18, ‘无’);对于这条数据的NULL值列表占用一个byte：00000001。倒数第一位对应name，倒数第二位对应age，倒数第三位对应interest。
• row_id在没有primary key时创建，存在不创建，非必须。创建主键id立省6个byte。

• 在MySQL当中，如果varchar列长度达到768byte后，会将该列的前768byte存放在行中，多出来的数据通过一个长度为20byte的数据存储溢出页地址，这就是mysql的行溢出。
  

小结

通过查阅了Mysql的官方文档，和查询了大量的资料，我们大概得了解了Mysql的行数据的存储格式，同时也大概了解了里边的一些比较重要的内容的机制