mysql原理–InnoDB记录结构

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

1.1.指定行格式的语法
我们可以在创建或修改表的语句中指定 行格式 ：

CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称;
ALTER TABLE 表名 ROW_FORMAT=行格式名称;

1.2.实例解析
我们后续基于下述实例进行说明。

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;
mysql> INSERT INTO record_format_demo(c1, c2, c3, c4) VALUES('aaaa', 'bbb', 'cc', 'd'), ('eeee', 'fff', NULL, NULL);

1.3.COMPACT行格式

1.3.1.记录的额外信息
(1). 变长字段长度列表
我们知道 MySQL 支持一些变长的数据类型，比如 VARCHAR(M) 、 VARBINARY(M) 、各种 TEXT 类型，各种 BLOB 类型，我们也可以把拥有这些数据类型的列称为 变长字段 ，变长字段中存储多少字节的数据是不固定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来，这样才不至于把 MySQL 服务器搞懵，所以这些变长字段占用的存储空间分为两部分：
a. 真正的数据内容
b. 占用的字节数

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

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

由于第一行记录中 c1 、 c2 、 c4 列中的字符串都比较短，也就是说内容占用的字节数比较小，用1个字节就可以表示，但是如果变长列的内容占用的字节数比较多，可能就需要用2个字节来表示。具体用1个还是2个字节来表示真实数据占用的字节数， InnoDB 有它的一套规则，我们首先声明一下 W 、 M 和 L 的意思：
(1). 字符集中表示一个字符最多需要使用的字节数为 W。
(2). 对于变长类型 VARCHAR(M) 来说，这种类型表示能存储最多 M 个字符（注意是字符不是字节），所以这个类型能表示的字符串最多占用的字节数就是 M×W 。
(3). 它实际存储的字符串占用的字节数是 L 。

确定使用1个字节还是2个字节表示真正字符串占用的字节数的规则就是这样：
(1). 如果 M×W <= 255 ，那么使用1个字节来表示真正字符串占用的字节数。
(2). 如果 M×W > 255 ，则分为两种情况：
a. 如果 L <= 127 ，则用1个字节来表示真正字符串占用的字节数。
b. 如果 L > 127 ，则用2个字节来表示真正字符串占用的字节数。
当用两字节存储占用字节数时，首先MySQL采用小端存储，然后两字节的数值的最高位固定为1。所以，可存储的最大值为：

2

15

−

1

2^{15} – 1

215−1。
假设要存储一个占据257字节的变长字段，数值为0x101，结合小端存储，最高位固定为1。所以，MySQL存储上先是0x01，再是0x81。
当可变字段实际尺寸很长，以至于超过

2

15

−

1

2^{15} – 1

215−1时，MySQL有溢出页机制来处理。

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

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

上述实例中插入两行后，存储信息如下：

(3).记录头信息
是由固定的 5 个字节组成。 5 个字节也就是 40 个二进制位，不同的位代表不同的意思，如图：

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

这里需要提一下 InnoDB 表对主键的生成策略：优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个 Unique 键作为主键，如果表中连 Unique 键都没有定义的话，则 InnoDB 会为表默认添加一个名为 DB_ROW_ID 的隐藏列作为主键。所以我们从上表中可以看出：InnoDB存储引擎会为每条记录都添加 DB_TRX_ID 和 DB_ROLL_PTR 这两个列，但是 DB_ROW_ID 是可选的（在没有自定义主键以及Unique键的情况下才会添加该列）。

上述实例插入两行的实际内容：

1.3.3.CHAR(M)列的存储格式
如果采用变长的字符集（也就是表示一个字符需要的字节数不确定，比如gbk 表示一个字符要1~2个字节、 utf8 表示一个字符要1~3个字节等）的话，此时即使列的类型是形如 CHAR(M) 类型时，也视为变长字段。相应的变长字段长度列表会包含此列尺寸信息。

变长字符集的 CHAR(M) 类型的列要求至少占用 M 个字节，而 VARCHAR(M) 却没有这个要求。比方说对于使用 utf8 字符集的 CHAR(10) 的列来说，该列存储的数据字节长度的范围是10～30个字节。即使我们向该列中存储一个空字符串也会占用 10 个字节。

2.行溢出数据
2.1.VARCHAR(M)最多能存储的数据
MySQL内部限制用户表一行存储中，剔除行中类型BLOB，TEXT类型字段，剔除隐藏列，记录头后剩余部分累计尺寸不可超过65535。因此，VARCHAR(M)中 M 受此限制影响可设置的最大值是有限制的。

2.2.记录中的数据太多产生的溢出
MySQL 中磁盘和内存交互的基本单位是 页 ，也就是说 MySQL 是以 页 为基本单位来管理存储空间的，我们的记录都会被分配到某个 页 中存储。而一个页的大小一般是 16KB ，也就是 16384 字节，而一个 VARCHAR(M) 类型的列就最多可以存储 65532 个字节，这样就可能造成一个页存放不了一条记录的尴尬情况。

在 Compact 和 Reduntant 行格式中，对于占用存储空间非常大的列，在 记录的真实数据 处只会存储该列的一部分数据，把剩余的数据分散存储在几个其他的页中，然后 记录的真实数据 处用20个字节存储指向这些页的地址（当然这20个字节中还包括这些分散在其他页面中的数据的占用的字节数），从而可以找到剩余数据所在的页，
如图所示：

从图中可以看出来，对于 Compact 和 Reduntant 行格式来说，如果某一列中的数据非常多的话，在本记录的真实数据处只会存储该列的前 768 个字节的数据和一个指向其他页的地址，然后把剩下的数据存放到其他页中，这个过程也叫做 行溢出 ，存储超出 768 字节的那些页面也被称为 溢出页 。

最后需要注意的是，不只是 VARCHAR(M) 类型的列，其他的 TEXT、BLOB 类型的列在存储数据非常多的时候也会发生 行溢出 。

2.3.行溢出的临界点
MySQL 中规定一个页中至少存放两行记录。当无法在一个页中完成两行记录的完整存储时，会通过页溢出来完成记录信息存储。

2.4.理解MySQL磁盘和内存交互以页面为单位
上述实例表对应一个磁盘上的文件record_format_demo.ibd，就是说上述文件内容按16KB切割。每个切割出的内容构成一个页。每个页按其性质有特定的页内数据组织方式。

代码007普通

打赏 收藏 海报 链接