Adrian Wang's blog

一个完全由AI生成，但是内容高质量的Blog

MYSQL 记录存储方式

Posted on Edited on In , ,
Symbols count in article: 4.5k Reading time ≈ 4 mins.
MYSQL 记录存储方式

MYSQL 数据文件存放位置

使用 show variables like 'datadir'; 命令查看数据文件存放位置：

1
2
3
4
5
6
mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+

比如我们的数据库是 test，那么数据文件存放位置就是 /var/lib/mysql/test/。

该目录下有以下文件：

  • db.opt：数据库选项文件，记录了数据库的字符集和校验规则
  • test.frm：表结构文件，记录了表的结构
  • test.ibd：表数据文件，记录了表的数据，包括数据页和索引页，它也被称作 InnoDB 存储引擎的 表空间文件

表空间文件结构

表空间主要由 段(segment)、区(extent)、页(page)、行(row) 组成。

  • 行(row)：行是表空间的最小存储单位，是 InnoDB 存储引擎中数据存储的基本单位。

  • 页(page)：页是 InnoDB 存储引擎中数据存储的基本单位，一个页默认大小为 16KB,页是为了方便管理数据，否则直接以行来存储数据，那么每次存取数据都要进行 I/O 操作，效率低下。每次数据库的读写操作都是以页为单位进行的。页的类型有很多，比如数据页、索引页、回滚页、溢出页、临时页、系统页、事务数据页、文件页等，具体参考MySQL 页类型。

  • 区(extent)：区是 InnoDB 存储引擎中数据存储的基本单位，一个区默认大小为 1MB，一个区由 64 个页组成。

    首先我们知道 InnoDB 存储引擎是 B+ 树结构，B+ 树结构的最后一层是使用双向链表连接的，那么如果链表中相邻的页的物理位置很远，那么磁盘查询会带来大量的随机 I/O（跟磁盘的机械运动有关），影响查询效率。

    因此 InnoDB 存储引擎引入了区的概念，一个区由 64 个页组成，这样就可以将物理位置相邻的页放到一个区中，从而减少磁盘的随机 I/O 操作，提高顺序 I/O的频率，提高查询效率。

  • 段(segment)：表空间是由多个段组成的，段是由多个区组成的，一般来说，段分为数据段、索引段、回滚段等。

    • 数据段：存放 InnoDB 存储引擎中叶子节点的区的集合。
    • 索引段：存放 InnoDB 存储引擎中非叶子节点的区的集合。
    • 回滚段：存放 InnoDB 存储引擎中表的回滚数据的集合。就是事务隔离部分，使用 MVCC 机制，就利用了回滚段来实现多版本查询数据。

1745323847902

InnoDB 行格式

InnoDB 提供了 Compact、Redundant、Dynamic、Compressed 四种行格式，5.1 版本后默认使用 Compact 行格式，5.7 版本后默认使用 Dynamic 行格式。

这里的 Redundant 行格式是 InnoDB 早期版本使用的行格式，现在已经不再使用。因为 Redundant 不是一种紧凑的行格式，所以现在使用 Compact 行格式。

Dynamic 和 Compressed 行格式都和 Compact 行格式类似，都是对 Compact 行格式的一种优化。

Compact 行格式

1745324130903

如上图所示，一条完整的记录被分为了“记录的额外信息”和“记录的真实数据”两部分。

  • “记录的额外信息”：

    • 变长字段长度列表：表示该条记录中变长字段的长度。
    • NULL 值列表：表示该条记录中哪些字段为 NULL。
    • 记录头信息：表示该条记录的一些信息，比如记录的类型、长度、是否被删除等。

  • “记录的真实数据”：

    • row_id：表示该条记录的唯一标识，如果表中没有定义主键，那么 InnoDB 会自动生成一个 row_id 作为主键。
    • 事务ID：表示该条记录的事务 ID。
    • 回滚指针：表示该条记录的回滚指针。
    • 列1...列N：表示该条记录的列数据。

变长字段长度列表

比如 varchar、varbinary、text、blob 等这种属于变长字段，就需要把它的长度存起来，因此需要一个列表来记录每个变长字段的长度。

假设我们有如下的表：

1
2
3
4
5
6
7
CREATE TABLE `t_user` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;

假设这里有三条记录:

id name phone age
1 a 123 18
2 bb 1234 NULL
3 ccc NULL NULL

我们先来看第一条记录：

id name phone age
1 a 123 18

name 列的值为 a，真实数据占用 1 字节，十六进制为 0x01，phone 列的值为 123，真实数据占用 3 字节，十六进制为 0x03，age 和 id 列都是定长字段，这里不用记录长度。

这些变长字段的真实数据占用的字节数会按照列的顺序逆序存放（等下会说为什么要这么设计），所以「变长字段长度列表」里的内容是「03 01」，而不是「01 03」。

1745459344704

同样的道理，我们也可以得出第二条记录的行格式中，「变长字段长度列表」里的内容是「04 02」，如下图：

1745459442389

第三条记录中 phone 列的值是 NULL，NULL 是不会存放在行格式中记录的真实数据部分里的，所以「变长字段长度列表」里不需要保存值为 NULL 的变长字段的长度。

1745459442389

为什么「变长字段长度列表」的信息要按照逆序存放？

这个设计是有想法的，主要是因为「记录头信息」中指向下一个记录的指针，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。

「变长字段长度列表」中的信息之所以要逆序存放，是因为这样可以使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，这样就可以提高 CPU Cache 的命中率。

同样的道理，NULL 值列表的信息也需要逆序存放。

如果你不知道什么是 CPU Cache，可以看这篇文章，这属于计算机组成的知识。

每个数据库表的行格式都有「变长字段字节数列表」吗？

其实变长字段字节数列表不是必须的。

当数据表没有变长字段的时候，比如全部都是 int 类型的字段，这时候表里的行格式就不会有「变长字段长度列表」了，因为没必要，不如去掉以节省空间。

所以「变长字段长度列表」只出现在数据表有变长字段的时候。

2. NULL 值列表

表中的某些列可能会存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中会比较浪费空间，所以 Compact 行格式把这些值为 NULL 的列存储到 NULL 值列表中。

如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列。

  • 二进制位的值为 1时，代表该列的值为 NULL。
  • 二进制位的值为 0时，代表该列的值不为 NULL。

另外，NULL 值列表必须用整数个字节的位表示（1 字节 8 位），如果使用的二进制位个数不足整数个字节，则在字节的高位补 0。

还是以 t_user 表的这三条记录作为例子：

id name phone age
1 a 123 18
2 bb 1234 NULL
3 ccc NULL NULL

接下来，我们看看看看这三条记录的行格式中的 NULL 值列表是怎样存储的。

先来看第一条记录，第一条记录所有列都有值，不存在 NULL 值，所以用二进制来表示是酱紫的：

1745459724424

但是 InnoDB 是用整数字节的二进制位来表示 NULL 值列表的，现在不足 8 位，所以要在高位补 0，最终用二进制来表示是酱紫的：

1745459735372

所以，对于第一条数据，NULL 值列表用十六进制表示是 0x00。

接下来看第二条记录，第二条记录 age 列是 NULL 值，所以，对于第二条数据，NULL 值列表用十六进制表示是 0x04。

1745459744703

最后第三条记录，第三条记录 phone 列 和 age 列是 NULL 值，所以，对于第三条数据，NULL 值列表用十六进制表示是 0x06。

1745459751962

我们把三条记录的 NULL 值列表都填充完毕后，它们的行格式是这样的：

1745459765680

每个数据库表的行格式都有「NULL 值列表」吗？

NULL 值列表也不是必须的。

当数据表的字段都定义成 NOT NULL 的时候，这时候表里的行格式就不会有 NULL 值列表了。

所以在设计数据库表的时候，通常都是建议将字段设置为 NOT NULL，这样可以至少节省 1 字节的空间（NULL 值列表至少占用 1 字节空间）。

「NULL 值列表」是固定 1 字节空间吗？如果这样的话，一条记录有 9 个字段值都是 NULL，这时候怎么表示？

「NULL 值列表」的空间不是固定 1 字节的。

当一条记录有 9 个字段值都是 NULL，那么就会创建 2 字节空间的「NULL 值列表」，以此类推。

3. 记录头信息

记录头信息中包含的内容很多，我就不一一列举了，这里说几个比较重要的：

  • delete_mask：标识此条数据是否被删除。从这里可以知道，我们执行 detele 删除记录的时候，并不会真正的删除记录，只是将这个记录的 delete_mask 标记为 1。
  • next_record：下一条记录的位置。从这里可以知道，记录与记录之间是通过链表组织的。在前面我也提到了，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。
  • record_type：表示当前记录的类型，0 表示普通记录，1 表示 B+树非叶子节点记录，2 表示最小记录，3 表示最大记录

记录的真实数据

记录真实数据部分除了我们定义的字段，还有三个隐藏字段，分别为：row_id、trx_id、roll_pointer，我们来看下这三个字段是什么。

1745459796350

  • row_id

如果我们建表的时候指定了主键或者唯一约束列，那么就没有 row_id 隐藏字段了。如果既没有指定主键，又没有唯一约束，那么 InnoDB 就会为记录添加 row_id 隐藏字段。row_id 不是必需的，占用 6 个字节。

  • trx_id

事务 id，表示这个数据是由哪个事务生成的。trx_id 是必需的，占用 6 个字节。

  • roll_pointer

这条记录上一个版本的指针。roll_pointer 是必需的，占用 7 个字节。

如果你熟悉 MVCC 机制，你应该就清楚 trx_id 和 roll_pointer 的作用了，如果你还不知道 MVCC 机制，可以看完这篇文章，一定要掌握，面试也很经常问 MVCC 是怎么实现的。

Mysql 中规定，除了 Text、Blob类型，其他类型其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节，这里注意一下，65535 是 <font color="red">一行 </font>的最大字节数，而不是一个列的最大字节数。

如果有多个字段的话，要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL 值列表所占用的字节数 <= 65535。

1745466102232