MySQL底层数据和索引的存储-从磁盘结构到内存管理的全解析

一、引言

在当今的数据时代，MySQL作为最流行的开源关系型数据库之一，支撑着无数互联网应用的核心数据存储。然而，很多开发者和DBA在日常工作中只停留在SQL编写和表面优化的层面，当遇到性能瓶颈或数据损坏等问题时，往往束手无策。真正的高手，必定深谙底层原理。

理解MySQL如何存储数据和索引，不仅能帮助我们写出更高效的SQL，还能在设计表结构、选择索引、配置数据库参数时做出更明智的决策。

二、存储基础——从磁盘到文件系统

2.1 数据持久化的物理基础：硬盘存储原理

在深入MySQL内部结构之前，我们必须先理解数据最终存放的物理介质——硬盘的工作原理。无论是机械硬盘（HDD）还是固态硬盘（SSD），其I/O性能都远低于内存访问速度，这也是数据库系统设计时需要克服的最大挑战。

2.1.1 机械硬盘的物理结构

机械硬盘由盘片（Platter）、磁头（Head）、磁道（Track）、扇区（Sector）和柱面（Cylinder）组成。

盘片：覆盖磁性材料的圆形薄片，数据就存储在这里

磁道：盘片上同心圆状的存储区域
扇区：磁道被划分为若干段圆弧，每个段称为一个扇区，是硬盘最小的物理存储单位，传统大小为512字节，现代硬盘多为4KB，其中，最内侧磁道上的扇区面积最小，因此数据密度最大。

磁道和扇区

柱面：所有盘片上相同半径的磁道构成一个柱面
磁头：位于磁臂末端，用于读写盘片上的数据

柱面和磁头

当需要读取数据时，磁臂移动磁头到指定磁道（寻道时间），然后盘片旋转使目标扇区旋转到磁头下方（旋转延迟），最后进行数据传输。这两个机械动作的耗时通常在毫秒级别，虽然看起来不长，但与纳秒级别的CPU处理和微秒级别的内存访问相比，简直是天壤之别。

1.1.2 固态硬盘

固态硬盘（SSD）的物理结构完全摒弃了传统机械硬盘的旋转盘片和移动磁头，由纯电子元件构成，我们不详细介绍其中的物理组件。

我们主要介绍下固态硬盘的特点：

读写不对称：读比写快
擦除前需先写入：闪存不能原地覆盖，需要先擦除再写入
写入放大：实际写入量可能大于应用层请求量
寿命有限：每个存储单元有有限的擦写次数

尽管SSD性能更好，但其随机访问延迟仍在微秒级别，依然远慢于内存。

2.2 从磁盘到文件系统的抽象

操作系统在磁盘之上建立了文件系统（如ext4、XFS、NTFS等）的抽象层，隐藏了磁盘的物理细节，以文件和目录的形式组织数据。文件系统也有自己的存储单位——块（Block），通常是磁盘扇区的整数倍，常见大小为4KB。

当MySQL向磁盘写入数据时，实际上是通过文件系统接口操作文件。但文件系统并不直接控制数据在磁盘上的物理位置，这可能导致额外的开销：

文件系统缓存：数据可能先写入操作系统缓存，再刷入磁盘
日志型文件系统：可能引入额外的写操作保证一致性
碎片化：文件在物理上可能不连续存储

2.3 数据库系统面临的挑战

基于上述硬件特性，数据库系统在设计时必须解决几个核心问题：

速度鸿沟：如何弥合内存与磁盘之间的巨大性能差距？
原子性保障：如何保证部分写入操作不会导致数据损坏？
并发控制：多个用户同时读写时如何保证数据一致？
崩溃恢复：系统突然崩溃后如何恢复数据？

MySQL的InnoDB存储引擎正是通过精心设计的架构来解决这些问题。接下来，我们将深入剖析InnoDB的存储架构。

三、InnoDB存储引擎架构概览

3.1 InnoDB的逻辑架构

InnoDB存储引擎采用插件式架构，既作为MySQL的一部分运行，又相对独立地管理数据的存储和访问。其整体架构可以分为内存结构和磁盘结构两大部分。

3.1.1 内存结构

缓冲池（Buffer Pool）：InnoDB访问数据和索引的缓存区域，通过内存加速数据访问
更改缓冲区（Change Buffer）：缓存对二级索引的更改操作，待时机合适时再合并
自适应哈希索引（Adaptive Hash Index）：根据查询模式自动建立的哈希索引，加速等值查询
日志缓冲区（Log Buffer）：暂存待写入磁盘的重做日志

3.1.2 磁盘结构

表空间（Tablespaces）：存储表数据和索引的物理文件
重做日志（Redo Log）：记录所有更改操作，用于崩溃恢复
撤销日志（Undo Log）：记录数据修改前的版本，用于事务回滚和MVCC

innoDB整体架构

3.2 存储单位层级体系

InnoDB的存储管理采用层次化的单位体系

单位	大小	描述
行（Row）	可变	单条记录，存储实际数据
页（Page）	默认16KB	InnoDB磁盘管理的最小单位
区（Extent）	1MB（64个页）	空间分配的单位，保证物理连续性
段（Segment）	可变	管理特定类型数据（如索引段、数据段）
表空间（Tablespace）	可变	逻辑存储容器，对应物理文件

这种层级结构既保证了灵活的空间管理，又能利用磁盘的物理连续性减少I/O开销。

innoDB存储单元

3.2.1 表空间（Tablespace）

表空间是 InnoDB 逻辑存储的最高层级，分为“共享表空间”和“独立表空间”（对应物理存储中的 ibdata1 和 .ibd 文件），本质是一个逻辑容器，用于容纳多个段（索引段、数据段等）。每张表都会有一个表空间（xxx.ibd），一个mysql实例可以对应多个表空间，用于存储记录、索引等数据。

核心特点：表空间有固定的头部信息，记录表空间的 ID、大小、关联的表等元数据；InnoDB 通过表空间 ID 唯一标识每个表空间，确保数据跨表空间访问的正确性。

3.2.2 段（Segment）

段是表空间下的逻辑划分，对应一个索引（InnoDB 中“数据段”本质是主键索引段），每个索引对应两个段：叶子节点段（Leaf Segment）和非叶子节点段（Non-Leaf Segment）。

主键索引段（聚簇索引段）：包含表的所有行数据，叶子节点存储完整数据行，非叶子节点存储索引键值和子节点指针；

二级索引段：叶子节点存储索引键值和对应的主键值，非叶子节点存储索引键值和子节点指针，不存储完整数据行。

特点：段的大小不固定，会随着数据量的增加动态扩展，扩展的最小单位是“区”。

段是表空间内的主要组织结构，InnoDB中主要有：

数据段：B+树的叶子节点，存储实际数据
索引段：B+树的非叶子节点，存储索引键
回滚段：存储Undo日志

每个索引对应两个段：叶子节点段和非叶子节点段。

3.2.3 区（Extent）

区是段的组成单位，是 InnoDB 中磁盘空间分配的最小单位，固定大小为 1MB（无论 MySQL 版本和操作系统，均为 1MB）。

换算关系：1 个区 = 64 个连续的页（因 1 个页默认大小为 16KB，1MB = 1024KB ÷ 16KB = 64 页）。

核心作用：避免频繁分配磁盘空间。若直接以“页”为单位分配空间，当数据量较大时，会产生大量零散的页，导致磁盘碎片化；以区为单位分配，可确保索引的 B+ 树节点在磁盘上连续存储，提升磁盘 I/O 效率（机械硬盘连续读比随机读快得多）。

特殊区：InnoDB 为每个段的初始阶段分配“碎片区”（Fragment Extent），碎片区以“页”为单位分配（而非 64 页），当碎片区大小达到 1MB 后，后续分配将以完整的区为单位，避免小表占用过多空闲空间。

3.2.4 页（Page）

页是 InnoDB 中数据读写的最小单位（区别于“区是空间分配的最小单位”），默认大小为 16KB（可通过参数 innodb_page_size 配置为 4KB、8KB、16KB、32KB、64KB，需在 MySQL 初始化时设置，后续无法修改）。在下节中我们会详细介绍页的结构。

3.2.5 行（Row）

行是 InnoDB 中存储数据的最小逻辑单元，对应表中的一条记录。InnoDB 的行数据有多种存储格式，核心差异在于行数据的压缩和字段长度的存储方式。后面我们也会详细介绍行的结构。

3.3 重要概念解释

在深入细节之前，我们先明确几个关键概念：

聚簇索引（Clustered Index）：InnoDB表的主键索引，叶子节点直接存储整行数据
二级索引（Secondary Index）：非主键索引，叶子节点存储主键值
回表：通过二级索引查询时，需要先用主键到聚簇索引中查找完整数据
覆盖索引：索引本身包含了查询所需的所有列，无需回表

理解了这些基础概念后，让我们从最基础的存储单元——页开始，逐步构建完整的存储体系。

四、页（Page）——InnoDB存储的基石

4.1 页的基本概念

页（Page）是InnoDB存储引擎管理存储空间的基本单位，也是内存和磁盘之间进行I/O操作的最小单位 。默认情况下，每个页的大小为16KB，这个值可以通过innodb_page_size参数在初始化MySQL实例时设置（通常不建议修改）。

为什么选择16KB作为默认页大小？这是综合考虑了以下因素后的折中选择：

磁盘I/O特性：一次I/O操作读取16KB数据，平衡了单次传输量和I/O次数

内存利用率：页太大浪费内存，太小则增加I/O次数

B+树特性：16KB的页可以容纳足够多的索引条目，保持树的高度较低

4.2 页的内部结构

一个16KB的InnoDB页被划分为多个区域，每个区域有特定的功能。下面我们以最常用的数据页为例，详细解剖其内部结构。

页结构

下面详细介绍页的内部结构：

4.2.1 File Header（文件头）

File Header占用38字节，存储页的通用元信息，主要包括：

表头信息	大小	描述
FIL_PAGE_SPACE_OR_CHKSUM	4	页的校验和，用于检测页是否损坏
FIL_PAGE_OFFSET	4	页号，在表空间中唯一标识一个页
FIL_PAGE_PREV	4	上一页的页号，用于构建双向链表
FIL_PAGE_NEXT	4	下一页的页号
FIL_PAGE_LSN	8	页最后一次修改对应的日志序列号
FIL_PAGE_TYPE	2	页类型（见下表）
FIL_PAGE_FILE_FLUSH_LSN	8	仅用于系统表空间的第一个页

FIL_PAGE_PREV和FIL_PAGE_NEXT就分别代表本页的上一个和下一个页的页号。这样通过建立一个双向链表把许许多多的页就都串联起来了，
保证这些页之间不需要是物理上的连续，而是逻辑上的连续

双向链表连接

常见的页类型包括：

类型值	名称	说明
17855	FIL_PAGE_INDEX	B+树索引页（最常用）
17857	FIL_PAGE_UNDO_LOG	撤销日志页
17858	FIL_PAGE_INODE	索引节点信息
17859	FIL_PAGE_IBUF_FREE_LIST	插入缓冲空闲列表
17860	FIL_PAGE_TYPE_ALLOCATED	最新分配的页
17861	FIL_PAGE_IBUF_BITMAP	插入缓冲位图
17862	FIL_PAGE_TYPE_SYS	系统页
17863	FIL_PAGE_TYPE_TRX_SYS	事务系统数据
17864	FIL_PAGE_TYPE_FSP_HDR	表空间头部信息
17865	FIL_PAGE_TYPE_XDES	区描述符页
17866	FIL_PAGE_TYPE_BLOB	BLOB页

Page Header占用56字节，专门针对存放数据的页，包含数据页的元数据信息，主要包括：

页头信息	大小	描述
PAGE_N_DIR_SLOTS	2	页目录中的槽数量
PAGE_HEAP_TOP	2	指向堆中第一个未使用记录的指针
PAGE_N_HEAP	2	堆中的记录数，包含已删除和未删除的
PAGE_FREE	2	指向已删除记录链表的首指针
PAGE_GARBAGE	2	已删除记录占用的总字节数
PAGE_LAST_INSERT	2	最后插入记录的位置偏移
PAGE_DIRECTION	2	插入方向（左、右、相同）
PAGE_N_DIRECTION	2	连续插入方向相同的次数
PAGE_N_RECS	2	用户记录的数量（不包括Infimum和Supremum）
PAGE_MAX_TRX_ID	8	修改当前页的最大事务ID
PAGE_LEVEL	2	当前页在B+树中的层级（叶子节点为0）
PAGE_INDEX_ID	8	当前页所属的索引ID
PAGE_BTR_SEG_LEAF	10	B+树叶子段头部信息
PAGE_BTR_SEG_TOP	10	B+树非叶子段头部信息

4.2.3 Infimum和Supremum记录

每个数据页中固定包含两条虚拟记录：

Infimum记录：表示比页内任何用户记录都小的值
Supremum记录：表示比页内任何用户记录都大的值

这两条记录不存储实际数据，只用于界定页内记录的边界，在页初始化时创建，始终存在且不会被删除。它们共同占用26字节空间。

4.2.4 User Records（用户记录）

用户记录区域存储实际的行数据或索引条目。每条记录都有特定的格式，我们将在下一节详细讨论。

4.2.5 Free Space（空闲空间）

空闲空间是页中尚未使用的区域，随着新记录的插入而逐渐减少。当空闲空间用完时，页需要进行分裂操作。

4.2.6 Page Directory（页目录）

页目录存储记录的相对位置（称为槽），用于加速页内的查找。页目录的设计非常巧妙：

页内的记录以单链表形式连接，但如果顺序查找链表，效率很低
InnoDB将记录分组，每组最后一条记录的偏移量存储在槽中
槽的数量通常远少于记录数，每组包含4-8条记录
查找时先在页目录中二分查找，找到所在组后再遍历组内记录

这种设计使得页内查找的时间复杂度从O(n)降低到O(log n) + 常数，极大提高了效率。

页目录

4.2.7 File Trailer（文件尾）

File Trailer仅8字节，包含两部分：

校验和：与File Header中的校验和对应，用于检测页是否完整写入
LSN低4字节：与File Header中的LSN后4字节一致

当页写入磁盘时，先写File Header，最后写File Trailer。如果在写入过程中发生崩溃，可以通过比较两者的校验和和LSN来检测页是否损坏。

五、行记录格式

5.1 行格式概述

InnoDB存储引擎支持多种行记录格式，用于定义数据行在页内的存储方式。MySQL 5.6以后支持四种行格式：

REDUNDANT：老版本格式，兼容性好但空间利用率低
COMPACT：MySQL 5.0引入的紧凑格式，目前默认格式
DYNAMIC：MySQL 5.7开始默认，对BLOB字段优化
COMPRESSED：压缩格式，节省空间但增加CPU开销

可以通过以下命令查看和设置行格式：

-- 查看当前默认行格式
SHOW VARIABLES LIKE 'innodb_default_row_format';

-- 创建表时指定行格式
CREATE TABLE t1 (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) ROW_FORMAT=DYNAMIC;

-- 修改已有表的行格式
ALTER TABLE t1 ROW_FORMAT=COMPACT;

不同格式的对比：

特性	REDUNDANT	COMPACT	DYNAMIC	COMPRESSED
引入版本	早期	MySQL 5.0	MySQL 5.6	MySQL 5.6
默认使用	否	否	MySQL 5.7+	否
空间利用率	低	高	高	极高
溢出处理	768字节前缀	768字节前缀	完全溢出	完全溢出
压缩支持	否	否	否	是
CPU开销	低	低	低	高

5.2 DYNAMIC行格式详解

在 MySQL 5.7 及之后版本中，InnoDB 存储引擎的默认行格式就是 DYNAMIC。这种格式是在 COMPACT 基础上发展而来的，主要针对大数据字段（如 BLOB、TEXT）做了优化，使得每个数据页能够容纳更多行记录，从而提升缓存效率和查询性能。

下面就是DYNAMIC 行格式的总体存储结构：

DYNAMIC详细结构

5.2.1 记录头信息

每条记录的开头是一段固定长度的记录头信息，占用5字节（40位），包含以下字段：

+---------------+----------------+--------------+--------+--------+
| 预留位1(1bit) | 预留位2(1bit)  | deleted_flag| min_rec_flag| n_owned(4bits)|
+---------------+----------------+--------------+--------+--------+
| heap_no(13bits)| record_type(3bits)| next_record(16bits)  |
+----------------+-------------------+-----------------------+

各字段含义：

deleted_flag：1位，标记记录是否被删除（1表示已删除）
min_rec_flag：1位，标记记录是否为当前层级的最小记录
n_owned：4位，当前记录拥有的记录数（用于页目录分组）
heap_no：13位，记录在页堆中的相对位置
record_type：3位，记录类型（0=普通，1=非叶子节点，2=Infimum，3=Supremum）
next_record：16位，指向下一条记录的相对偏移量

5.2.2 变长字段长度列表

记录每个变长列（VARCHAR、VARBINARY、TEXT、BLOB）的实际数据长度（单位：字节）。按列顺序逆序存放（即最后面的变长列在前）。如果列允许 NULL 且值为 NULL，则不占用列表位置。

如果字段允许NULL且实际值为NULL，则不存储长度
长度 ≤ 255 字节时，用 1 字节存储；
长度 > 255 字节时，用 2 字节存储（注意：InnoDB 内部是使用 2 字节表示，但实际存储时以小端方式存放）。
长度列表按列顺序逆序存放（MySQL内部优化）

变长字段的长度最大不可以超过2字节，因为MySQL数据库中可变类型的最大长度限制为65535。

5.2.3 NULL值列表

NULL值列表使用位图表示哪些列的值是NULL：

每个允许NULL的列占用1位
按列顺序逆序存放
如果列值为NULL，对应位为1；否则为0
列表长度 = ceil(允许NULL的列数 / 8) 字节

这种设计节省了存储空间：如果某列为NULL，数据部分就不需要为其分配空间。

5.2.4 事务ID和回滚指针

对于聚簇索引记录，NULL值列表之后是两个系统列：

DB_TRX_ID（6字节）：最后修改该记录的事务ID
DB_ROLL_PTR（7字节）：指向Undo Log中记录修改前版本的指针

这两个字段是InnoDB实现MVCC的关键，每条记录都保存了修改它的事务ID，以及用于回滚的指针。

5.2.5 行ID

如果表没有定义主键，InnoDB会自动生成一个6字节的DB_ROW_ID作为隐式主键。这个字段在聚簇索引中作为排序键。

5.2.6 实际列数据

最后存储各列的实际数据：

对于定长数据类型，分配固定长度空间
- 比如：头信息，事务ID和回滚指针
对于变长数据类型，根据实际长度存储
- 变长字段长度列表，NULL值列表，行ID，实际列数据
对于NULL列，数据部分不存储

5.3 行溢出（Row Overflow）

5.3.1 什么是行溢出

InnoDB的页大小默认为16KB，而VARCHAR、TEXT、BLOB等类型的字段可能远大于16KB。当行记录过大，无法完整存储在单个页中时，就会发生行溢出

5.3.2 溢出处理机制

不同行格式处理溢出的方式不同：

COMPACT和REDUNDANT格式：

在记录中存储前768字节（称为前缀）
剩余部分存储在溢出页（BLOB页）中
记录中保存指向溢出页的指针（20字节）

DYNAMIC和COMPRESSED格式：

如果行长度超过阈值（约半个页），整列数据都存储在溢出页
记录中只保存20字节的指针
这种方式称为“完全行溢出”，避免了存储无用的前缀

5.3.3 溢出页的结构

溢出页（FIL_PAGE_TYPE_BLOB）的结构相对简单：

BLOB页头：存储所属表空间ID、页号等信息
数据区域：存储实际的BLOB数据
多个BLOB页通过链表连接，支持超大对象

5.5 DYNAMIC行格式占用空间计算

在 MySQL 5.7 及之后版本中，InnoDB 的默认行格式就是 DYNAMIC。这种格式继承了 COMPACT 的紧凑性，并针对 BLOB、TEXT 等大字段做了优化——采用完全行溢出策略，即当字段长度超过一定阈值时，整个字段内容存储在溢出页中，数据页只保留一个 20 字节的指针，从而让每个数据页能容纳更多行，提升缓存效率。

下面我们通过一个具体的例子，创建一张表，插入几条数据，然后一步步分析在 DYNAMIC 格式下，这些行在 InnoDB 页中是如何存储的。

5.5.1 创建测试表

假设我们有一张用户信息表，包含定长、变长及大字段：

CREATE TABLE `user` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `age` TINYINT,
    `intro` TEXT,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

这里我们显式指定了 ROW_FORMAT=DYNAMIC，实际上不写也会默认使用 DYNAMIC（如果 innodb_default_row_format 为 DYNAMIC）。

5.5.2 插入几条数据

INSERT INTO `user` (`name`, `age`, `intro`) VALUES
('Alice', 25, 'Short intro.'),
('Bob', NULL, NULL),
('Charlie', 30, REPEAT('A long intro...', 1000));  -- 产生一个很长的字符串

三条记录分别代表：

普通短数据，无 NULL；
有 NULL 值；
含超长 TEXT 字段。

我们逐条分析行记录的存储细节

5.5.3 分析行记录的存储细节

1. 第一条记录：(id=1, name='Alice', age=25, intro='Short intro.')

首先，我们先看下每列的存储数据：

id：INT，4 字节（主键，聚簇索引中已存在）
name：VARCHAR(50)，实际长度 5（’Alice’），变长
age：TINYINT，1 字节，可为 NULL，但此处为 25 非 NULL
intro：TEXT，实际长度 12（’Short intro.’），变长，且未超过溢出阈值，所以存储在行内

然后我们根据上节的行格式，分析存储细节：

记录头（5 字节）：包含基本信息，如记录类型为 0（普通记录）、heap_no、n_owned、next_record 偏移量等。我们暂且忽略具体二进制值。
变长字段长度列表：共有两个变长列：name 和 intro。按照逆序，先存储 intro 的长度，再存储 name 的长度。
- intro 长度 = 12（十六进制 0x0C），使用 1 字节存储（因为最大长度 255 以内）。
- name 长度 = 5（0x05），使用 1 字节存储。
  所以变长长度列表为：0x0C 0x05（实际存储顺序可能是 0x05 0x0C？需注意 InnoDB 是逆序存放，即最后面的变长列在前。这里 intro 在 name 后面，所以列表第一个字节是 intro 的长度 0x0C，第二个字节是 name 的长度 0x05）。我们按此理解即可。
NULL 位图：表中有两个可为 NULL 的列：age、intro。id 是主键，非空；name 是 NOT NULL。所以需要 2 位来表示。
- 位图大小 = ceil(2/8) = 1 字节。
- 按照列顺序逆序存放位：第 0 位（最低位）对应第一个可为 NULL 的列？InnoDB 规则是：位图中每个位对应一个可为 NULL 的列，列顺序按照表定义顺序，但存储时逆序。
  表定义中可为 NULL 的列顺序：age（第 1 列）、intro（第 2 列）。
  逆序后，位图的 bit0（最低位）对应 intro，bit1 对应 age。
  此处 intro 非 NULL（位 0 = 0），age 非 NULL（位 1 = 0），所以 NULL 位图值为 00000000（二进制）即 0x00。
事务 ID 和回滚指针：各 6 字节和 7 字节，存储系统信息。
列数据（按表定义顺序）
- id：主键列，实际存储为 4 字节整数（例如 0x00000001）。
- name：存储实际字符串 ‘Alice’，占用 5 字节（无结束符，直接存字符）。
- age：存储 1 字节 25（0x19）。
- intro：存储 ‘Short intro.’ 的 12 字节（ASCII 字符串）

整体存储示意图（逻辑上）：

1	`[记录头5B] [0x0C] [0x05] [0x00] [6B事务ID] [7B回滚指针] [id=1(4B)] [name='Alice'(5B)] [age=25(1B)] [intro='Short intro.'(12B)]`

总大小 = 5 + 2 + 1 + 6 + 7 + 4 + 5 + 1 + 12 = 43 字节

2. 第二条记录：(id=2, name='Bob', age=NULL, intro=NULL)

数据大小分析：

name：’Bob’ 长度 3
age：NULL
intro：NULL

变长字段长度列表：只有 name 是变长且非 NULL，intro 虽然变长但值为 NULL，不存储长度。所以长度列表只有 name 的长度 3（0x03），1 字节。

NULL 位图：两个可为 NULL 的列：age 和 intro 均为 NULL。
逆序后：bit0（intro）=1，bit1（age）=1，位图值为 00000011（二进制）= 0x03。

列数据：

id：4 字节（2）
name：’Bob’（3 字节）
age：NULL，数据部分不占空间
intro：NULL，数据部分不占空间

存储示意：

1	`[记录头5B] [0x03] [0x03] [6B事务ID] [7B回滚指针] [id=2(4B)] [name='Bob'(3B)]`

注意：虽然 age 和 intro 是 NULL，但数据部分没有它们的任何字节，完全由 NULL 位图标识。

总大小 = 5 + 1 + 1 + 6 + 7 + 4 + 3 = 27 字节

3. 第三条记录：(id=3, name='Charlie', age=30, intro=超长字符串)

假设 intro 字段内容长度为 3000 字节（远超溢出阈值），因此 DYNAMIC 格式会将整个 intro 存入溢出页，数据页只保留一个 20 字节的溢出指针。

记录结构变化：

intro 列在数据页中不再存储实际数据，而是存储一个 BLOB 指针。
BLOB 指针结构：20 字节，包含：
- 空间 ID（4 字节）
- 页号（4 字节）
- 页内偏移（2 字节）——指向溢出页中 BLOB 数据的起始位置
- 一些标志位

变长字段长度列表：intro 是变长且非 NULL，但其实际数据不在本页，长度列表中是否存储长度？实际上，在 DYNAMIC 格式中，对于完全溢出的 BLOB 列，长度列表仍然存储该列的实际长度（用于快速知道总大小），因为长度可能用于排序或计算。所以长度列表会包含 intro 的长度（3000），可能使用 2 字节表示（因 > 255）。所以长度列表为：intro 长度（3000，0x0BB8，2 字节）和 name 长度（7，0x07，1 字节），逆序存储为 0x0BB8 0x07（实际存储低字节在前？按 InnoDB 存储整型是小端，长度存储也是小端，所以 3000 的十六进制 0x0BB8 存储为 0xB8 0x0B）。

NULL 位图：age 和 intro 均非 NULL，所以位图为 0x00。

列数据：

id：4 字节（3）
name：’Charlie’（7 字节）
age：30（1 字节 0x1E）
intro：20 字节的溢出指针（不是实际数据）

存储示意：

1	`[记录头5B] [intro_len_low][intro_len_high] [0x07] [0x00] [6B事务ID] [7B回滚指针] [id=3(4B)] [name='Charlie'(7B)] [age=30(1B)] [溢出指针(20B)]`

溢出指针指向的 BLOB 页中，存储着完整的 3000 字节字符串。

总大小 = 5 + 3 + 1 + 6 + 7 + 4 + 7 + 1 + 20 = 54 字节

5.5.4 影响行大小的关键因素

列的数据类型：定长类型（INT, BIGINT, DATE 等）占用固定空间；变长类型（VARCHAR, TEXT）占用实际数据长度。
NULL 值处理：NULL 不占数据空间，只占位图中的 1 位。
行格式：DYNAMIC/COMPACT 比 REDUNDANT 更紧凑；COMPRESSED 会压缩数据，但增加 CPU 开销。
溢出：大字段溢出后，行内只存指针（20 字节），可大幅减少行大小，但访问大字段需额外 I/O。

六、B+树深度解析

索引的核心目标是加速数据查找，而选择合适的数据结构至关重要。

B+树的演变过程我们后续讲解，我们先看下B+树的特点：

数据只存于叶子节点：内部节点只存储索引键和子节点指针，不存数据
叶子节点通过链表连接：所有叶子节点按顺序链接，形成有序链表
更高的扇出：内部节点可容纳更多键，树高更低
稳定的查询性能：所有查询都要走到叶子节点，路径长度固定

6.1 B+树的详细结构

6.1.1 节点结构

B+树节点分为两种类型：

非叶子节点：

存储k个键值和k+1个子节点指针
所有键值按升序排列
键值用于划分搜索路径

叶子节点：

存储键值和实际数据（或指向数据的指针）
在InnoDB聚簇索引中，叶子节点直接存储整行数据
叶子节点间通过双向链表连接

6.2 B+树的高度计算

B+树的高度决定了一次查询需要多少次磁盘I/O。我们可以计算不同层级能存储的数据量。

我们计算存储数据量：主要是计算叶子节点中存储的行数

因此得到计算公式：

1	`总行数 = 叶子节点个数 * 每个叶子节点行数`

6.2.1 怎么计算出叶子节点的个数

如果只有一层，那么一个叶子节点，就是根节点。

如果有两层呢？这就取决于根节点里的内部节点个数。

1
2

内部节点存储的是“键值 + 子节点指针”（当前主键的字节数以及指向叶子节点的指针）。
在 InnoDB 中，子节点指针通常为 6 字节（用于表示页号等），键值即索引列的大小。

1
2

假设主键为 `BIGINT`（8 字节），则一个内部节点条目大小为 8 + 6 = 14 字节
页大小为 16384 字节(16Kb * 1024)，减去页头（约 120~200 字节）和页尾（8 字节）开销后，实际可用于存储条目的空间约为 **16200 字节** 左右。

因此两层B+树：内部节点最多可容纳的条目数为：16384 / 14 = 1170

如果有三层呢？

就是1170 * 1170 = 136 8900个叶子节点。

6.2.2 怎么计算出每个叶子节点存储的行数

1
2

叶子节点存储的是实际数据或索引条目。每行大小包括数据本身以及 InnoDB 的行开销（记录头、变长字段列表、NULL 位图、事务 ID、回滚指针等）。
为简化，我们通常使用 平均行大小 来估算。下面我们已平均行大小 = 1KB = 1024 字节来计算。

因此一个叶子节点的行数有：16kb * 1024 / 1024 = 16行

综上所述，计算B+树容量为：

高度为1（根为叶子节点层）：1 × 16 ≈ 16条记录
高度为2（根为内部节点，叶子节点层）：1170 × 16 ≈ 18,720条记录
高度为3：1170 × 1170 × 16 ≈ 2,190万条记录
高度为4：1170³ × 16 ≈ 256亿条记录

惊人的结论：仅需3-4次I/O，就能从上亿条记录中精确定位到任意一条数据！

6.3 二级索引的容量计算

二级索引的 B+ 树结构与聚簇索引类似，但叶子节点存储的是“索引列值 + 主键值”（而不是整行数据）。因此，叶子节点条目大小会改变，从而影响每页行数。

假设二级索引列为 INT（4 字节），主键为 BIGINT（8 字节），则叶子节点条目大小约为：4 + 8 + 行开销（记录头等，约 10~20 字节）。若合计为 25 字节，则每页可存储 L2 ≈ 16200 / 25 ≈ 648 条索引条目。

内部节点存储的是索引列值（4 字节）+ 指针（6 字节），条目大小 10 字节，扇出系数 F2 ≈ 16200 / 10 = 1620。

此时，高度为 3 的二级索引可存储的索引条目数为：

text

1	`1620 * 1620 * 648 = 1.7×10⁹ 条（17 亿）`

远高于聚簇索引的行数限制，因为二级索引条目更小。

七、表空间（Tablespace）深入剖析

7.1 表空间的概念与层次

表空间是InnoDB存储引擎的最高逻辑存储层次，对应一个或多个物理文件

+-------------------+
|   表空间          |
|  (Tablespace)     |
+-------------------+
         |
         v
+-------------------+
|   段 (Segment)    |
+-------------------+
         |
         v
+-------------------+
|   区 (Extent)     |
+-------------------+
         |
         v
+-------------------+
|   页 (Page)       |
+-------------------+
         |
         v
+-------------------+
|   行 (Row)        |
+-------------------+

7.2 表空间的类型

InnoDB中有多种表空间类型：

表空间类型

7.2.1 系统表空间（System Tablespace）

系统表空间是InnoDB默认的表空间，对应ibdata1文件，存储以下内容：

数据字典（Data Dictionary）
双写缓冲区（Doublewrite Buffer）
修改缓冲区（Change Buffer）
Undo日志（Undo Logs，可配置独立）
如果未启用独立表空间，还存储所有表的数据和索引

系统表空间的文件可以通过innodb_data_file_path配置：

1 2	`[mysqld] innodb_data_file_path=ibdata1:12M:autoextend`

7.2.2 独立表空间（File-per-table Tablespace）

独立表空间是每个表单独的.ibd文件，通过innodb_file_per_table参数控制（MySQL 5.6以后默认开启）。

优点：

便于管理：删除表时直接删除文件
空间回收：TRUNCATE操作可立即释放空间给操作系统
便于移动：可以轻松迁移单个表

缺点：

大量文件可能超过操作系统限制
每个表有自己的空闲空间，整体利用率略低

7.2.3 通用表空间（General Tablespace）

通用表空间是MySQL 5.7引入的共享表空间，可以容纳多个表，类似于系统表空间但更灵活。

-- 创建通用表空间
CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' ENGINE=InnoDB;

-- 在通用表空间中创建表
CREATE TABLE t1 (id INT) TABLESPACE ts1;

-- 移动表到通用表空间
ALTER TABLE t1 TABLESPACE ts1;

7.2.4 撤销表空间（Undo Tablespace）

从MySQL 5.7开始，Undo日志可以存储在独立的撤销表空间中，默认创建两个（undo_001、undo_002）。

7.2.5 临时表空间（Temporary Tablespace）

存储临时表的数据，对应ibtmp1文件，服务器重启时重建。

7.3 表空间文件结构解析

7.3.1 独立表空间文件（.ibd）结构

一个典型的.ibd文件结构如下：

+------------------------+
| 表空间头部 (FSP_HDR)   | ← 第0页，存储表空间元数据和区信息
+------------------------+
| 插入缓冲位图页         | ← 管理插入缓冲
+------------------------+
| 索引根节点页           | ← 聚簇索引根页
+------------------------+
| 索引根节点页           | ← 二级索引根页（如果有）
+------------------------+
| 数据页 ...             |
+------------------------+
| Undo页（如果启用）     |
+------------------------+

7.3.2 FSP_HDR页结构

FSP_HDR页（文件空间头部）是表空间的第一个页，存储表空间的全局信息：

表空间大小和版本
空闲区的位图
碎片页列表
段的INODE信息

FSP_HDR页之后，每256MB会有一个XDES（区描述符）页，继续管理后续区的状态。

7.4 表空间管理机制

7.4.1 空间分配策略

InnoDB采用以下策略分配空间：

优先使用碎片页：对于小对象，从碎片页池中分配
分配新区：如果碎片页不够，分配连续64页的完整区
预分配：为保持连续性，有时会一次性分配多个区

7.4.2 空间回收

DROP TABLE：直接删除文件（独立表空间）或标记空间可重用（系统表空间）
TRUNCATE TABLE：删除所有数据，保留表结构，重建.ibd文件
OPTIMIZE TABLE：重建表和索引，整理碎片

八、缓冲池（Buffer Pool）——内存缓存的核心

8.1 缓冲池的作用与地位

缓冲池是InnoDB中最重要的内存组件，用于缓存数据和索引页，极大减少磁盘I/O。其重要性体现在：

速度差异：内存访问是纳秒级，磁盘是毫秒级，相差几个数量级
局部性原理：刚访问过的数据很可能再次访问，邻近数据也可能被访问
写缓冲：修改先在内存进行，异步刷盘，提高写入性能

8.2 缓冲池的结构

8.2.1 物理结构

缓冲池是一片连续的内存区域，按页（16KB）划分，每个页称为一个缓冲帧（Buffer Frame），包含：

数据内容：与磁盘页一一对应
控制信息：页所属表空间、页号、脏页标记、访问频率等

8.2.2 逻辑组织

缓冲池内部通过三种链表组织页：

LRU链表：管理最近最少使用的页
Flush链表：管理脏页（已修改但未刷盘）
Free链表：管理空闲页

8.3 缓冲池管理算法

8.3.1 改进的LRU算法

InnoDB没有使用标准的LRU算法，而是采用中点插入策略，将LRU链表分为两部分：

+-------------------+--------------------+
|  新生代 (young)   |   老生代 (old)     |
|  占5/8            |   占3/8            |
+-------------------+--------------------+

工作原理：

新读取的页插入到老生代的头部（中点位置）
如果页在足够长的时间内被再次访问（超过innodb_old_blocks_time设置的时间），则晋升到新生代头部
淘汰时从老生代尾部开始

这种设计防止了什么？

全表扫描污染缓冲池：大量一次性访问的页不会挤占热点页位置

8.3.2 预读机制

InnoDB通过预读机制提前将可能需要的页加载到缓冲池：

线性预读：根据顺序访问模式，预读后续页
随机预读：根据同一区中页的访问模式，预读整个区（MySQL 5.5后默认禁用）

预读由innodb_read_ahead_threshold等参数控制。

8.4 脏页刷新机制

8.4.1 脏页产生

当缓冲池中的页被修改后，就变成脏页（与磁盘内容不一致）。脏页需要刷回磁盘以保证持久性。

8.4.2 刷新策略

InnoDB采用多种策略刷新脏页：

后台线程刷新：Page Cleaner线程定期刷新部分脏页
自适应刷新：根据脏页比例和I/O能力动态调整刷新速度
同步刷新：当脏页比例过高时，强制用户线程参与刷新

8.4.3 Checkpoint机制

检查点（Checkpoint）是脏页刷新的重要机制：

模糊检查点：只刷新部分脏页，不要求所有脏页都刷
尖锐检查点：将所有脏页刷盘（只在关闭数据库时发生）

每个检查点会记录一个LSN（Log Sequence Number），表示该LSN之前的所有修改都已持久化。

8.5 缓冲池配置优化

8.5.1 大小设置

缓冲池大小是最重要的性能参数：

[mysqld]
# 设置为可用内存的70%-80%（专用服务器）
innodb_buffer_pool_size=8G

# 如果是多实例MySQL，按比例分配

经验法则：

读密集型：尽量大，缓存更多数据
写密集型：适当减小，避免脏页刷新压力过大

8.5.2 多实例配置

对于大内存服务器，可以将缓冲池划分为多个实例，减少锁竞争：

1
2
3

[mysqld]
innodb_buffer_pool_instances=4
innodb_buffer_pool_size=32G  # 每个实例8G

8.5.3 预热机制

MySQL支持在重启后恢复缓冲池状态：

[mysqld]
# 启用缓冲池状态保存和恢复
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1

8.6 监控缓冲池

通过以下方式监控缓冲池状态：

-- 查看缓冲池统计信息
SHOW ENGINE INNODB STATUS\G

-- 通过INFORMATION_SCHEMA表
SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS;

-- 查看具体页内容（调试用）
SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE LIMIT 10;

重点关注指标：

命中率：缓冲池读取命中率，应>99%
脏页比例：不应过高，否则刷新压力大
空闲页：不应为0，否则说明缓冲池太小

九、总结

本篇文章主要介绍了磁盘到内存、从页到行、从索引到表空间。现在，我们提炼出几个最核心的认知：

磁盘是永恒的归宿，内存是速度的翅膀：所有数据最终都躺在磁盘上，但 InnoDB 通过缓冲池将热点数据缓存在内存中，利用局部性原理将磁盘 I/O 降到最低。理解页的缓存策略、LRU 算法和脏页刷新机制，是优化数据库性能的基础。
B+ 树是索引的灵魂：InnoDB 选择 B+ 树作为索引结构，是因为它在磁盘场景下实现了极低的树高（通常 3~4 层），从而用极少的 I/O 次数支撑起亿万级数据的快速定位。掌握 B+ 树的扇出计算，就能预估表容量和索引效率。
行格式决定空间利用率：DYNAMIC 行格式通过完全行溢出策略，让大字段不再占用数据页空间，使得每个页能容纳更多行，提升缓冲池命中率。理解行内各部分的存储开销，有助于设计更紧凑的表结构。
日志是数据一致性的守护神：重做日志（Redo Log）保证了事务的持久性，撤销日志（Undo Log）支撑了 MVCC 和回滚，二进制日志（Binlog）则负责复制和恢复。两阶段提交机制确保了 InnoDB 与 Server 层日志的一致性。
表空间是物理组织的蓝图：从系统表空间到独立表空间，从段到区再到页，InnoDB 通过层次化的空间管理，既保证了数据的物理连续性，又提供了灵活的空间回收能力。

MySQL 的底层存储原理，既是一部精巧的工程设计史，也是一本性能优化的实战指南。掌握它，你就能在遇到问题时透过现象看本质——当索引失效时，你知道 B+ 树如何工作；当 I/O 飙升时，你明白缓冲池如何影响磁盘访问；当数据损坏时，你懂得日志如何恢复。

#数据库

MySQL底层数据和索引的存储-从磁盘结构到内存管理的全解析

https://johnjoyjzw.github.io/2025/02/08/MySQL底层数据和索引的存储奥秘-从磁盘结构到内存管理的全解析/

Author

JiangZW

Posted on

February 8, 2025

Licensed under

从源码解析SpringBoot自动配置原理 Previous

深入理解Spring Bean初始化流程 Next

MySQL底层数据和索引的存储-从磁盘结构到内存管理的全解析

MySQL底层数据和索引的存储-从磁盘结构到内存管理的全解析

一、引言

二、存储基础——从磁盘到文件系统

2.1 数据持久化的物理基础：硬盘存储原理

2.1.1 机械硬盘的物理结构

1.1.2 固态硬盘

2.2 从磁盘到文件系统的抽象

2.3 数据库系统面临的挑战

三、InnoDB存储引擎架构概览

3.1 InnoDB的逻辑架构

3.1.1 内存结构

3.1.2 磁盘结构

3.2 存储单位层级体系

3.2.1 表空间（Tablespace）

3.2.2 段（Segment）

3.2.3 区（Extent）

3.2.4 页（Page）

3.2.5 行（Row）

3.3 重要概念解释

四、页（Page）——InnoDB存储的基石

4.1 页的基本概念

4.2 页的内部结构

4.2.1 File Header（文件头）

4.2.2 Page Header（页头）

4.2.3 Infimum和Supremum记录

4.2.4 User Records（用户记录）

4.2.5 Free Space（空闲空间）

4.2.6 Page Directory（页目录）

4.2.7 File Trailer（文件尾）

五、行记录格式

5.1 行格式概述

5.2 DYNAMIC行格式详解

5.2.1 记录头信息

5.2.2 变长字段长度列表

5.2.3 NULL值列表

5.2.4 事务ID和回滚指针

5.2.5 行ID

5.2.6 实际列数据

5.3 行溢出（Row Overflow）

5.3.1 什么是行溢出

5.3.2 溢出处理机制

5.3.3 溢出页的结构

5.5 DYNAMIC行格式占用空间计算

5.5.1 创建测试表

5.5.2 插入几条数据

5.5.3 分析行记录的存储细节

5.5.4 影响行大小的关键因素

六、B+树深度解析

6.1 B+树的详细结构

6.1.1 节点结构

6.2 B+树的高度计算

6.2.1 怎么计算出叶子节点的个数

6.2.2 怎么计算出每个叶子节点存储的行数

6.3 二级索引的容量计算

七、表空间（Tablespace）深入剖析

7.1 表空间的概念与层次

7.2 表空间的类型

7.2.1 系统表空间（System Tablespace）

7.2.2 独立表空间（File-per-table Tablespace）

7.2.3 通用表空间（General Tablespace）

7.2.4 撤销表空间（Undo Tablespace）

7.2.5 临时表空间（Temporary Tablespace）

7.3 表空间文件结构解析

7.3.1 独立表空间文件（.ibd）结构

7.3.2 FSP_HDR页结构

7.4 表空间管理机制

7.4.1 空间分配策略

7.4.2 空间回收

八、缓冲池（Buffer Pool）——内存缓存的核心

8.1 缓冲池的作用与地位

8.2 缓冲池的结构

8.2.1 物理结构

8.2.2 逻辑组织

8.3 缓冲池管理算法

8.3.1 改进的LRU算法

8.3.2 预读机制

8.4 脏页刷新机制

8.4.1 脏页产生

8.4.2 刷新策略