MySQL - InnoDB记录结构

前言

之前文章记录了MySQL逻辑架构和SQL语句执行流程文章链接，但是表中的数据到底存到了哪里？以什么格式存放的？我们全然不知，本文针对这些问题给予记录。

一、前置介绍

存储引擎负责对表中数据的读取和写入工作，而MySQL服务器支持多种类型的存储引擎，比如 InnoDB 、 MyISAM 、 Memory等，不同的存储引擎一般有不同的实现，真实数据在不同存储引擎中存放的格式一般是不同的，甚至有的存储引擎比如 Memory 内存数据库，都不用磁盘来存储数据，也就是说关闭服务器后表中的数据就消失了。 InnoDB 是 MySQL 5.5.5 版本后默认的存储引擎，本文将 InnoDB 作为存储引擎的数据存储结构，了解了一个存储引擎的数据存储结构之后，其他的存储引擎都是存储组织数据都是相通的。

二、InnoDB页简介

InnoDB 是一个将表中的数据存储到磁盘上的存储引擎，所以即使关机后重启我们的数据还是存在的。而真正处理数据的过程是发生在内存中的，所以需要把磁盘中的数据加载到内存中，如果是处理写入或修改请求的话，还需要把内存中的内容刷新到磁盘上。而我们知道读写磁盘的速度非常慢，和内存读写差了几个数量级，所以当我们想从表中获取某些记录时， InnoDB 存储引擎需要一条一条的把记录从磁盘上读出来么？不，那样会慢死。InnoDB 采取的方式是将数据划分为若干个页, 以页作为磁盘和内存之间交互的基本单位, InnoDB中页的大小一般为 16 KB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。使用如下命令查看 InnoDB 默认页的大小。

mysql>  show global status like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.02 sec)

mysql>
1
2
3
4
5
6
7
8
9

可以看出 innodb 默认的一页大小为 16384B = 16384/1024 = 16kb。

三、InnoDB行格式

我们平时是以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为行格式或者记录格式 。InnoDB 存储引擎有4种不同类型的行格式分别是 Compact、Redundant 、Dynamic 和 Compressed。

3.1 指定行格式的语法

#创建语句中指定行格式：
CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称
#修改表的语句中指定行格式：
ALTER TABLE 表名 ROW_FORMAT=行格式名称
1
2
3
4

mysql> CREATE TABLE record_format_demo (
 -> c1 VARCHAR(10),
 -> c2 VARCHAR(10) NOT NULL,
 -> c3 CHAR(10),
 -> c4 VARCHAR(10)
 -> ) CHARSET=ascii ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.03 sec)
1
2
3
4
5
6
7

该表的行格式就是 Compact ，字符集为ascii，ascii 字符集只包括空格、标点符号、数字、大小写字母和一些不可见字符，所以我们的汉字是不能存到这个表里的。往表中插入两条记录：

mysql> INSERT INTO record_format_demo(c1, c2, c3, c4) VALUES
('aaaa', 'bbb', 'cc', 'd'),
('eeee', 'fff', NULL, NULL);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
1
2
3
4
5

表记录：

mysql> SELECT * FROM record_format_demo;
+------+-----+------+------+
| c1 | c2 | c3 | c4 |
+------+-----+------+------+
| aaaa | bbb | cc | d |
| eeee | fff | NULL | NULL |
+------+-----+------+------+
2 rows in set (0.00 sec)
mysql>
1
2
3
4
5
6
7
8
9

3.2 查看默认的行格式

mysql> show variables like 'innodb_default_row_format';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_default_row_format | dynamic |
+---------------------------+---------+
1 row in set, 1 warning (0.00 sec)

mysql>
1
2
3
4
5
6
7
8
9

3.3 COMPACT行格式

一条完整的记录可以被分为记录的额外信息和记录的真实数据两大部分。

3.2.1 记录的额外信息
这部分信息是服务器为了描述这条记录而不得不额外添加的一些信息，这些额外信息分为3类，分别是变长字段长度列表 、 NULL值列表和记录头信息。

变长字段长度列表
VARCHAR(M) 、 VARBINARY(M) 、各种 TEXT 类型，各种 BLOB 类型，把这些数据类型的列称为变长字段，变长字段中存储多少字节的数据是不固定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来，这样才不至于把 MySQL 服务器搞懵，所以这些变长字段占用的存储空间分为两部分：
1.真正的数据内容
2.占用的字节数

在 Compact 行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表，各变长字段数据占用的字节数按照列的顺序逆序存放，我们再次强调一遍，是逆序存放！

以 record_format_demo 表中的第一条记录来举个例子。因为 record_format_demo 表的 c1 、 c2 、 c4 列都是 VARCHAR(10) 类型的，也就是变长的数据类型，所以这三个列的值的长度都需要保存在记录开头处，因为record_format_demo 表中的各个列都使用的是 ascii 字符集，所以每个字符只需要1个字节来进行编码，来看一下第一条记录各变长字段内容的长度：

又因为这些长度值需要按照列的逆序存放，所以最后 变长字段长度列表 的字节串用十六进制表示的效果就是（各个字节之间实际上没有空格，用空格隔开只是方便理解）：01 03 04。把这个字节串组成的变长字段长度列表填入上边的示意图中的效果就是：

NULL值列表
表中的某些列可能存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中存储会很占地方，所以 Compact 行格式把这些值为 NULL 的列统一管理起来，存储到 NULL 值列表中，它的处理过程是这样的：
1.首先统计表中允许存储 NULL 的列有哪些。我们前边说过，主键列、被 NOT NULL 修饰的列都是不可以存储 NULL 值的，所以在统计的时候不会把这些列算进去。比方说表 record_format_demo 的3个列 c1 、 c3 、 c4 都是允许存储 NULL 值的，而 c2 列是被NOT NULL 修饰，不允许存储 NULL 值。
2. 如果表中没有允许存储 NULL 的列，则 NULL值列表 也不存在了，否则将每个允许存储 NULL 的列对应一个二进制位，二进制位按照列的顺序逆序排列，二进制位表示的意义如下：二进制位的值为 1 时，代表该列的值为 NULL 。二进制位的值为 0 时，代表该列的值不为 NULL。因为表 record_format_demo有3个值允许为 NULL 的列，所以这3个列和二进制位的对应关系如下图：

3. MySQL规定NULL值列表必须用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，则在字节的高位补0。表record_format_demo只有3个值允许为NULL的列，对应3个二进制位，不足一个字节，所以在字节的高位补0，效果就是这样：

以此类推，如果一个表中有9个允许为NULL，那这个记录的NULL值列表部分就需要2个字节来表示了。知道了规则之后，我们再返回头看表record_format_demo中的两条记录中的NULL值列表应该怎么储存。因为只有c1、c3、c4这3个列允许存储NULL值，所以所有记录的NULL值列表只需要一个字节。对于第一条记录来说，c1、c3、c4这3个列的值都不为NULL，所以它们对应的二进制位都是0，画个图就是这样：

所以第一条记录的NULL值列表用十六进制表示就是：0x00。对于第二条记录来说，c1、c3、c4这3个列中c3和c4的值都为NULL，所以这3个列对应的二进制位的情况就是：

所以第二条记录的NULL值列表用十六进制表示就是：0x06。所以这两条记录在填充了NULL值列表后的示意图就是这样：

记录头信息
用于描述记录的记录头信息，它固定的5个字节组成。5个字节也就是40个二进制位，不同的位代表不同的意思。如下图：

这些二进制位代表的详细信息如下表：

3.2.2 记录的真实数据
记录的真实数据除了c1 、 c2 、 c3 、 c4 这几个我们自己定义的列的数据以外， MySQL 会为每个记录默认的添加一些列（也称为隐藏列 ），具体的列如下：

InnoDB存储引擎会为每条记录都添加 transaction_id和 roll_pointer 这两个列，但是 row_id 是可选的（在没有自定义主键以及Unique键的情况下才会添加该列）。这些隐藏列的值不用我们操心， InnoDB 存储引擎会自己帮我们生成的。因为表 record_format_demo 并没有定义主键，所以 MySQL 服务器会为每条记录增加上述的3个列。现在看一下加上记录的真实数据的两个记录长什么样吧：

看这个图的时候我们需要注意几点：
1. 表 record_format_demo 使用的是 ascii 字符集，所以 0x61616161 就表示字符串 'aaaa' ， 0x626262 就表
示字符串 'bbb' ，以此类推。
2. 注意第1条记录中 c3 列的值，它是 CHAR(10) 类型的，它实际存储的字符串是： 'cc' ，而 ascii 字符集中
的字节表示是 '0x6363' ，虽然表示这个字符串只占用了2个字节，但整个 c3 列仍然占用了10个字节的空
间，除真实数据以外的8个字节的统统都用空格字符填充，空格字符在 ascii 字符集的表示就是 0x20 。 3. 注意第2条记录中 c3 和 c4 列的值都为 NULL ，它们被存储在了前边的 NULL值列表 处，在记录的真实数据处
就不再冗余存储，从而节省存储空间。

1
2
3
4
5
6
7
8

3.4 主键的生成策略

InnoDB 表对主键的生成策略：优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个 Unique 键作为主键，如果表中连 Unique 键都没有定义的话，则 InnoDB 会为表默认添加一个名为row_id 的隐藏列作为主键。