Mysql 运行原理 (1) - InnoDB 数据存储结构-CSDN博客

本文链接：https://blog.csdn.net/qq_38074398/article/details/132185960

此系列博客为学习笔记，B 站原视频地址。某些更加具体细节可能会有遗漏，主要目的在于理解 mysql 整体的运行原理。后面博客如果没有特殊说明，都采用 Mysql 5.7 版本。

本文主要介绍，Mysql 中的 InnoDB 存储引擎是如何在磁盘中存储表数据，又通过什么样的存储结构进行存储。

文章目录

表空间

Mysql 5.7 版本，每当我们创建一个新的表，都会分别创建两个文件：

Mysql5.7表空间

不过 Mysql 5.7- 版本，所有表的数据以及索引数据都存储在一个文件中：系统表空间/共享表空间。

可以通过以下 SQL 设置存储方式：

# 开启独立表空间，即为每个表都创建 ibd 文件
SET GLOBAL innodb_file_per_table = 'ON';

# 查看是否开启独立表空间
SHOW VARIABLES LIKE 'innodb_file_per_table'

并且 Mysql 5.8 版本：已经将 frm 文件合并到了 ibd 文件中。

下面我们新建一张表，进行测试：

CREATE TABLE `user` (
	`id` 				 BIGINT ( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
	`user_id` 	 BIGINT ( 20 ) UNSIGNED NOT NULL COMMENT '业务主键',
	`name` 			 VARCHAR ( 100 ) NOT NULL COMMENT '名称',
	`created_by` BIGINT ( 20 ) DEFAULT NULL COMMENT '创建人',
	`created_at` datetime DEFAULT NULL COMMENT '创建时间',
	`deleted` 	 TINYINT ( 1 ) UNSIGNED DEFAULT '0' COMMENT '是否删除(1.Y 0.N)',
	PRIMARY KEY ( `id` ) USING BTREE,
    UNIQUE KEY `uk_user_id` ( `user_id` ) COMMENT '业务主键唯一索引' 
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = '用户表';

查看一下表在本地的存储位置：

# D:\development\mysql-5.7.38-winx64\data\
SHOW VARIABLES LIKE '%datadir%';

表空间data目录

表空间表文件

可见实际上，每个数据库在磁盘中是一个目录，而表则分为 table.ibd、table.frm 文件。表内的所有数据最终都存储在 ibd 文件中。

除了独立表空间还有其他类别的表空间，总计可分为五种：

表空间的分类

独立表空间：ibd 文件

ibd 文件是表数据存储的核心文件，下面就分析一下该文件中是如何组织存储数据的。

页 PAGE

页是最基础的存储单元，也是 InnoDB 在磁盘与内存间传输数据时的基本单位。
每个页大小为固定的 16 KB，即使没有数据，也会占用 16 KB 大小。如果要调整页的大小，仍需要保证为操作系统数据块 4KB 的整数倍。这样才能保证与磁盘进行数据传输时，数据不会被分割。

根据局部性原理：当一个数据被用到时，其附近的数据也通常会被马上用到，所以页之间的地址也是连续的。只需要在磁盘中读取一段连续的数据放入内存，后续的查询大概率就可以直接在内存中找到。这样就可以减少与硬盘的 IO 次数。

页的传输交互

页的分类有12种之多，但无论什么页，都会包含 File header 与 File tailer。并且，根据类别的不同，会在中间的主体部分存储不同的数据：

页结构

最为常见的是存储实际数据或索引数据的索引页，主体部分大多存储的为数据行：

索引页

下面以索引页为例，逐一拆分说明各行都存储了什么信息。

页头与页尾行

IndexPage-File header & File tailer

File header

页号、上页页号、下页页号：通过这三个字段，可以将多个页组成一个双向链表，保持页之间的连续性，即使在物理磁盘中不是连续的。
页类型：类型的标识字段，比如索引页的类型则为：FIL_PAGE_INDEX / 0x45BF
表空间 ID：表空间的 ID 值
校验和：用来做数据的完整性校验
最近一次修改的 LSN、已被刷到磁盘的 LSN：日志序列号

File tailer

校验和：与 File header 中的校验和相对应
最近一次修改的 LSN：日志序列号

校验和的作用：一个页的固定大小为 16KB，操作系统传输单元数据块为 4KB。也就是说，当我们传输时一个页的数据时，若发生断电情况就会导致部分页的数据块传输失败，进而导致页数据丢失。这时就需要用页头、尾中的校验和字段通过验证算法进行完整性校验，默认使用 crc32 算法。

校验算法可以通过 SQL 进行查看：

SHOW VARIABLES LIKE '%innodb_checksum_algorithm%';

数据行 ROW

对比页固定的 16KB 大小，行的大小不是固定的，最大为 8 KB 大小。表中的每条数据，本质上就是对应页中的数据行。在 mysql 5.7 默认行的格式为 dynamic。

Dynamic format row

头信息中的字段：

下一行地址：next_record
行类型：record_type
记录堆位置：heap_no
行组数：n_owned
为层最小值：min_rec_mask
删除标记：delete_mask

真实数据区字段：

主键值字段：Primary key 主键值
- 如果是复合主键，也会依序排在这里
- 如果没有主键，会优先使用一个不为 NULL 的 UNIQUE 唯一列作为主键
- 如果以上都没有，InnoDB 会构建一个 6 Byte 的叫做 DB_ROW_ID 行唯一表示字段替换主键字段。
事务运行中的核心字段：
- DB_TX_ID：事务 id
- DB_ROLL_PTR：事务回滚指针
列字段：存储的真实的表列数据，从左到右依次排放，但不包含主键列以及值为 NULL 的列（原因在讲解 NULL 值字段列表字段时会说明）。

额外信息区字段：

头信息字段：
1. 下一行地址：next_record，前16位，存储下一行真实数据的起始地址。这样做的好处是，向右移动即是下一行的真实数据，向左移动则是下一行的头信息，无需额外的长度计算。且通过该字段可将所有行组成一个单向链表。
2. 行类型：record_type，包含四种类型 0:普通数据行 1:索引目录行 2:页内最小行Infimun 3:页内最大行Supermun
3. 位置：heap_no，该行在页中的位置
4. 组行数：如果当前行是行分组中的最后一行，该 4bit 会记录分组的行数。这样可以快速查询行数，不用在行组遍历累加计算了。(行分组会在本文的后面说明)
5. 为层最小值：B+索引树每层的最小值标记。如果当前行为当前索引层的最小值，且为索引目录行，则置为1，方便索引查询。
6. 删除标记：delete_mask，当行数据被删除后，不会立即移除，而是置该位为1。并将 next_record 位改为 0。
  然后进行单向链表结点删除时的指针更新：上一行的 next_record 指向当前行的下一行地址，将当前行从链表中断开。
  当事务提交后，在讲该行的 next_record 指向一个称为垃圾链表的地方。这个链表被用于事物会滚中，事务后面的博客会进行讲解。
NULL值字段列表：将可为 NULL 的字段，从右至左的顺序，各用一个 bit 标识是否为 NULL，节省存储空间。所以实际上当字段值为 NULL 时是不会在真实数据区存储该列为 NULL 的。不足 8bit 则用 0 补齐，超过 8bit 则在申请 8 bit。
可变字段长度列表：记录可变长度字段的真实长度，存储顺序为字段从右至左。这样方便在真实数据区域对字段值进行分割。使用 2 Byte 存储真实数据大小，2 Byte 最多可表示：2^16 = 65535 Byte。按照最大长度字符集中每个字符的长度最多为 4 Byte 来算，最多可表示 65535 / 4 = 16383 个字符。mysql 5.7 版本中 varchar 长度上限 16383 就是根据这个计算得出的。常见可变长度字段类型：
1. varchar
2. varbinary
3. text
4. blob
5. utf-8、gkb 这样变长字符集的 char 类型

特殊情况：可变字段实际数据超过了一个页的存储容量，那么就会将数据存放到一个溢出页中，并在将溢出页的地址替换需要存储的实际数据。

可变字段的溢出特殊情况

栗子：

我们新建的 User 表中添加一条数据

CREATE TABLE `user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '业务主键',
  `name` varchar(100) NOT NULL COMMENT '名称',
  `created_by` bigint(20) DEFAULT NULL COMMENT '创建人',
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `deleted` tinyint(1) unsigned DEFAULT '0' COMMENT '是否删除(1.Y 0.N)',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_user_id` (`user_id`) COMMENT '业务主键唯一索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

INSERT INTO `user` ( id, user_id, NAME, created_by, created_at )
VALUES (1, 111, '张三', NULL, '2023-08-10 00:00:00')

这条数据的 Dynamic format row 是什么样的呢？

Dynamic format row 的栗子

其余格式的 row

除了 dynamic 格式的行外，还有其他格式的行。数据行一共可分为四种格式，每种格式的行具有不同的特点适用不同的场景：

redundant：已经被淘汰
dynamic
compressed：与 dynamic 的格式完全一致，但会对数据进行压缩减少空间占用
compact：与 dynamic 的格式完全一致，但在处理超长字段时有一些区别。compact 格式的行不会把所有超长数据都放入溢出页中，而是在溢出时只保留 768 字节的溢出数据

可以根据下面的 SQL 查询表中行的类型：

SELECT t.SPACE,t.NAME,t.ROW_FORMAT FROM information_schema.INNODB_SYS_TABLESPACES t
-- name : 库名/表名
WHERE name = 'local_db/user'

在建表时也可以通过 ROW_FORMAT 参数修改表的行类型。又或可以查看、修改默认的全局行格式配置：

SHOW VARIABLES LIKE 'innodb_default_row_format'

SET 'innodb_default_row_format' = format

最小行与最大行

每当创建一个新页，都会创建两个行：

固定 heap_no 为 0 且 record_type 为 2 的最小行 Infimun
固定 heap_no 为 1 且 record_type 为 3 的最大行 Supermun

由于 heap 0、1 被固定占用，所以新数据行的 heap_no 都是从 2 开始。
这两个行不存储任何信息，仅是作为数据行链表的头、尾节点。
当没有真实数据时，Infimun 的 next_record 指向 Supermun。Supermun 因为永远都是尾节点，所以 next_record 一直为 0。
虽然 Infimun 与 Supermun 不存储任何数据，但是他们与数据行的结构是完全一致的。且真实数据区存储的分别为固定字符串：Infimun、Supermun。

下面是新增数据行后的链表图（新增时根据主键字段值搜索插入节点，保证行是有序的）：

最小行最大行与数据行的链表

页目录行

当数据行的大小比较均匀且每个行大小较小时，一个页中可以存储的行是很多的。我们以创建过的 user 表为例，假设每个行数据都不存在列为 NULL 的情况：

name 使用 utf-8 字符集，且按照两个字符计算，8 Byte。datetime 8 Byte。tinyint 1 Byte。

一个 dynamic 数据行大小：54 Byte

额外信息区域：
可变字段列表：name 字段长度 -> 2 Byte
NULL 值字段列表：2bit 不足 8 bit 进行补齐 -> 8 bit
头信息：固定 -> 40 bit
总计：8 Byte

真实数据区域：主键 + 事务 id + 事务回滚指针 + 所有列
总计：8 Byte + 6 Byte + 7 Byte + / 8 Byte + 8 Byte + 8 Byte + 1 Byte = 46 Byte 

页头行 + 页尾行：38Byte + 8 Byte = 46 Byte
数据页头行 + 行目录：暂先不考虑

计算得可储数据行数：(16 KB - 46 Byte) / 54 Bye ≈ 595

所以我们想要在页中搜索一条数据时，时间复杂度为 O(n)。

为了提高页内搜索行的速度，引入了页目录行：将含有最小、最大行以及所有数据行的链表进行分组。约定最小行即首行单独为一组。其他组每组最多有 8 行。同时把每个组最后一行在页中的地址，按照根据主键字段升序记录到页中的页目录行。页目录中的单位为槽，每个槽对应了一个行组。

页目录示例1

当新增行后触发了组新增：

页目录示例2
为了快速判断是否组内行数是否到了 8 的阈值，会在每组最后一行的额外信息区域中的组行数字段（n_owned）。当发现超过 8 的阈值时，会在页目录中创建一个新的槽，记录组内最后一行的地址。

这样当我们搜索一个行时就可以先二分查询页目录，找到槽后在根据槽中的行地址，最多进行 8 次查找就可以找到行数据了，将 O(n) 的时间复杂度降到了 O(log2n)。

数据页头行

为了方便操作与管理数据页，会将数据页中的一些统计、通用信息记录在数据页头中，用 56 个固定 Byte 记录：

数据页头行

区 EXTENT

ibd 文件时存储在磁盘中的，当读取数据随机性过高，需要跨多页进行数据访问，而这些页大概率分布在不同的磁道上。

所以，在物理上，就会频繁发生磁头移动磁道读取数据。相比在同一磁道上每分钟几千上万次的旋转读取，要缓慢的多。

磁头移动

为了提高随机读取磁盘页数据的性能，避免频繁的磁头移动，引入了区的概念，将多个页再次组织成区在磁盘中：

当区中的某个页被频繁访问时，会将整个区读取到内存中的 Buffer Pool 中，进而提高查询速度。

碎片区

我们新建一张表时，我们是不确定表的数量级的，不知道该创建多少的页才合适。为了不创建过的的页导致磁盘空间的浪费，默认的会创建 6 个页（Mysql 8 时会创建 7 个页），共计 16 KB * 6 = 96 KB。6 个页不足构成一个完整的区，新建后产生的零散的页，会被存放到表空间中的碎片区（Base extent）。可以理解为碎片区是表中的第一个初始区。

初始的这 6 个页并不是完全空的，会存储一些数据信息的：

初始的6个页
后两个页为空闲页，即可用页。前四个页记录了：

表空间和区组
Change Buffer
段(段和区组后面会说到)
索引根

我们可以根据 SQL 查询一下新的空表的信息：

-- 查看某表的行类型
SELECT 
t.SPACE,
t.NAME,
t.ROW_FORMAT,
(t.PAGE_SIZE / 1024) AS PAGE_SIZE_KB,
(t.FILE_SIZE / 1024) AS FILE_SIZE_KB
FROM information_schema.INNODB_SYS_TABLESPACES t
-- name : 库名/表名
WHERE name = 'local_db/user'