深入浅出——InnoDB记录结构详解，菜鸡看了直呼：能懂！

MySQL, 中间件, 后端开发 Karos

从这篇文章开始，将对InnoDB的行格式和页结构进行介绍，这里主要介绍一下InnoDB的行格式，但是在故事的开始，都来提一下吧
InnoDB将数据划分为若干页，以页作为磁盘与内存交互的基本单位，一般页的大小为16KB。这样的话，一次性至少读取1页数据到内存中或者将1页数据写入磁盘。通过减少内存与磁盘的交互次数，从而提升性能。

这一种典型的缓存设计思想，一般缓存的设计基本都是从时间维度或者空间维度进行考量的：

  • 时间维度：如果一条数据正在在被使用，那么在接下来一段时间内大概率还会再被使用。可以认为热点数据缓存都属于这种思路的实现。

  • 空间维度：如果一条数据正在在被使用，那么存储在它附近的数据大概率也会很快被使用。InnoDB的数据页和操作系统的页缓存则是这种思路的体现。

    image-20230613140903458

行格式

MySQL是以记录(一行数据)为单位向数据表中插入数据的，这些记录在磁盘上的存放方式称为行格式。

MySQL支持的4种行格式

  • Compact
  • Redundant（比较老，本文就不具体介绍了）
  • Dynamic
  • Compressed

指定行格式

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

下面来说一说各个行格式的区别

COMPACT行格式

image-20230613142254381

// (-_- ^!^) 这怎么这么像协议，hhh

咱们就不从大类上来说了，直接从小抓起

记录的额外信息

在将之前，:mouse::mouse:在这里做个预处理，咱们假设有一张表，长这个屌样

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)
-------------------------------------------------------------
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)

变长字段长度列表

对于VARCHAR(M)、TEXT等变长数据类型，存储数据占用的存储空间不是固定的，而是会随着存储内容的变化而变化。
所以至少得包含以下内容

  • 真正的数据内容
  • 占用的字节数

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

变长字段长度列表中只存储值为 非NULL 的列内容占用的长度，值为 NULL 的列的长度是不储存的

image-20230613142045172

NULL值列表

对于可为NULL的列，为了节约存储空间，MySQL不会将NULL值保存在记录的真实数据部分。而是会将其保存在记录的额外信息里面的NULL值列表中。

先统计表中允许存储NULL值的列，然后将每个允许存储NULL值的列对应一个二进制位（1：值为NULL，0：值不为NULL）用来表示是否存储NULL值，并按照逆序排列。

:tipping_hand_man:: MySQL规定NULL值列表必须用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，则在字节的高位补0

emmm... 这里来个例子看看比较好（图片都懒得做，网上有现成的我直接用了 qwq）

数据咱们上面说了，对应record_format_demo表中，c1、c3、c4都是允许存储NULL值的。前两条记录在填充了NULL值列表后的示意图就是这样：

image-20230613142011240

这里:mouse::mouse:来:dog:叫一下：

06？不是二进制位吗？

首先，能为空的是134

为空的只有3 4

所以这是在表中其实是这样

1 3 4

0 1 1

但是要倒排序 所以是 110

然后不就是6吗，高位补零06

注意，这里使用十六进制表示

记录头信息

记录头信息是由固定的5个字节(40位)组成, 不同的位代表不同的含义：

image-20230613141931147

下面对各个属性进行西索：

image-20230613141833415

具体的内容在后面我们西索

好奇的可以去嗦一口　揭秘：行的记录头信息
这里我放在页结构里面讲，具体为什么主要和页目录有一定关联，一起学比较好，hhh

记录的真实数据

除去原有数据外，还有隐藏数据

  • image-20230613141606391

只有当数据库没有定义主键或者唯一键时，隐藏列row_id才会存在，并且将其作为数据表主键。因为表record_format_demo并没有定义主键，所以MySQL服务器会为每条记录增加上述的3个列。

image-20230613141538359

实际上这几个列的真正名称其实是：DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR，为了美观才写成了row_id、transaction_id和roll_pointer。

CHAR(M)列的存储格式

对于 CHAR(M) 类型的列来说，当列采用的是定长字符集时，该列占用的字节数不会被加到变长字段长度列表，而如果采用变长字符集时，该列占用的字节数也会被加到变长字段长度列表。

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

CHAR定长是对字符位数定长，但是字符集却不一定是定长的，下面来说一下定长字符集和变长字符集

定长字符集 ascll

变长字符集 utf-8/gbk等

这就意味着：==对于 CHAR(M) 类型的列来说，当列采用的是定长字符集时，该列占用的字节数不会被加到变长字段长度列表，而如果采用变长字符集时，该列占用的字节数也会被加到变长字段长度列表。==

行溢出数据

这里我放到前面来说，趁热打铁

VARCHAR(M)最多能存储的数据

MySQL对一条记录占用的最大存储空间是有限制的，除了BLOB或者TEXT类型的列之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节。(简单来说就是普通的mysql一行记录占用的存储空间不能超过65535个字节)

当然，这65535里面还存储了其他数据（storage overhead）

拿varchar来说，主要有3部分：

  • 真实数据
  • 真实数据占用字节的长度 2字节
  • NULL值标识，如果该列有NOT NULL属性则可以没有这部分存储空间 1字节

假设varchar_size_demo只有一个VARCHAR类型的字段，如果该VARCHAR类型的列没有NOT NULL属性，那最多只能存储65532个字节的数据。如果该列是ascii字符集，对应的最大字符数最大为65532；如果是utf8字符集，则对应的最大字符数为21844。

记录中的数据太多产生溢出

再来个文章预处理，hhh

mysql> CREATE TABLE varchar_size_demo(
    ->       c VARCHAR(65532)
    -> ) CHARSET=ascii ROW_FORMAT=Compact;           #这里用的ascii编码
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO varchar_size_demo(c) VALUES(REPEAT('a', 65532));    #插入一条记录
Query OK, 1 row affected (0.00 sec)

REPEAT函数的作用是重复定义一个字符串n次

上面也提到过mysql中磁盘与内存交互的基本单位是页，一般为16KB，16384个字节，而一行记录最大可以占用65535个字节，这就造成了一页存不下一行数据的情况。

  • 分页存储:dog: # ( q _ q ^—^) 来了 神魔梦幻联动

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

img

这种在本记录的真实数据处只会存储该列的前768个字节的数据和一个指向其他页的地址，然后把剩下的数据存放到其他页中的情况就叫做行溢出，存储超出768字节的那些页面也被称为溢出页。

img

学到这里，再次感叹道四大基础的重要性啊，让我想到了os的分页存储，感兴趣的同学可以异步这里康康

操作系统-超20000字的“总结” – Karos (wzl1.top)

行溢出的临界点

MySQL中规定一个页中至少存放两行记录。以上边的varchar_size_demo表为例，它只有一个列c，我们往这个表中插入两条记录，每条记录最少插入多少字节的数据才会行溢出的现象呢？这得分析一下页中的空间都是如何利用的。

  1. 每个页除了存放我们的记录以外，也需要存储一些额外的信息，大概132个字节。

  2. 每个记录需要的额外信息是27字节

    这27个字节包括下边这些部分：

    • 2个字节用于存储真实数据的长度 （一行记录最大可以占用65535个字节）
    • 1个字节用于存储列是否是NULL值
    • 5个字节大小的头信息
    • 6个字节的row_id列
    • 6个字节的transaction_id列
    • 7个字节的roll_pointer列

假设一个列中存储的数据字节数为n，如要要保证该列不发生溢出，则需要满足：

132 + 2×(27 + n) < 16384 #页的大小 16kb 16384字节

结果是n < 8099。也就是说如果一个列中存储的数据小于8099个字节，那么该列就不会成为溢出列。如果表中有多个列，那么这个值更小。

compact行格式这一块儿的内容部分参考于:

Redundant行格式

有点太老了，上面引入的文章都没提过，嗯，这里还是提一下吧

image-20230613141459088

先把行格式改了

mysql> ALTER TABLE record_format_demo ROW_FORMAT=Redundant;
-------------------------------------------------------------------
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

image-20230613144522298

记录的额外信息

字段长度偏移表

注意的是,compact存储的是变长字段长度,而这里存储的是所有字段长度偏移

下面咱们从偏移这一块来西索:

简单的概括：用两个相邻数值的插值来计算各个列值的长度

image-20230613145634802

记录头信息

Redundant 行格式的记录头信息占用 6 字节， 48 个二进制位，这些二进制位代表的意思如下：

名称 大小（单位：bit） 描述
预留位1 1 没有使用
预留位2 1 没有使用
delete_mask 1 标记该记录是否被删除
min_rec_mask 1 B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned 4 表示当前记录拥有的记录数
heap_no 13 表示当前记录在页面堆的位置信息
n_field 10 表示记录中列的数量
1byte_offs_flag 1 标记字段长度偏移列表中每个列对应的偏移量是使用1字节还是2字节表示的
next_record 16 表示下一条记录的相对位置

记录的真实数据

这里就不西索了

Dynamic和Compressed行格式

Dynamic行格式 是MySQL 5.7使用的默认行格式

其实这他们和compact特别相似，但是在行数据溢出处理有些不同，就是在分页的时候，并不会存储前768个字节，会把前768个字节也存储到页面中

image-20230613150923452

其中Compressed会使用压缩算法对页面进行压缩，以节省空间

部分内容参考自：MySQL 是怎样运行的：从根儿上理解 MySQL - 小孩子4919 - 掘金小册 (juejin.cn)

  • 微信或QQ扫一扫

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

→ 目录