MySQL 数据到底存在哪里从数据目录到 InnoDB 表空间一层层揭开神秘面纱摘要每次执行 INSERT 都感觉数据消失进了数据库可它实际存在磁盘的哪个角落MySQL 的数据目录、.ibd 文件、表空间、区、段…这些概念之间到底是什么关系本文将用通俗的比喻和图解带你从文件系统一路走进 InnoDB 表空间的内部世界。一、写一条 INSERT数据去了哪里先来一个灵魂拷问当你执行INSERT INTO users VALUES (1, 张三, zhangsanexample.com)时这条记录最终去了磁盘的哪个角落答案藏在一条层层递进的路径里一条 INSERT 语句 │ ▼ MySQL Server 接收 SQL │ ▼ InnoDB 存储引擎处理 │ ▼ 找到对应的表空间.ibd 文件 │ ▼ 在 B 树索引中找到插入位置 → 定位到具体的数据页 │ ▼ 将记录写入页面标记为脏页 │ ▼ 后台刷盘线程将脏页写入磁盘 → 最终落到文件系统上的 .ibd 文件这一路上涉及的概念——数据目录、表空间、区、段、页——就是本文要逐个拆解的内容。二、数据目录数据库的家2.1 数据目录 vs 安装目录很多初学者容易把这两个概念搞混所以我们先来做一个清晰的区分┌─────────────────────────────────────────────────────────┐ │ MySQL 安装目录如 /usr/local/mysql │ │ ├── bin/ ← mysqld、mysql、mysqldump 等可执行文件 │ │ ├── lib/ ← 库文件 │ │ ├── include/ ← 头文件 │ │ └── share/ ← 错误消息、字符集配置 │ │ │ │ ⚠️ 这是程序的身体不存用户数据 │ └─────────────────────────────────────────────────────────┘ ┌─────────────────────────────────────────────────────────┐ │ MySQL 数据目录如 /usr/local/var/mysql │ │ ├── 数据库1/ ← 你创建的每个数据库是一个文件夹 │ │ ├── 数据库2/ │ │ ├── ibdata1 ← 系统表空间文件 │ │ ├── ib_logfile0 ← redo 日志 │ │ └── ... ← 各种运行时产生的数据 │ │ │ │ ⚠️ 这是数据的仓库所有表记录都存在这里 │ └─────────────────────────────────────────────────────────┘查看数据目录的准确位置mysqlSHOWVARIABLESLIKEdatadir;--------------------------------------|Variable_name|Value|--------------------------------------|datadir|/usr/local/var/mysql/|--------------------------------------2.2 数据库在文件系统上的样子每当你执行CREATE DATABASE mydb时MySQL 在磁盘上做了两件事在数据目录下创建一个名为mydb的子文件夹在mydb文件夹里创建一个db.opt文件记录该数据库的字符集和比较规则数据目录/ ├── mysql/ ← mysql 系统库 ├── information_schema/ ← 特殊处理没有实际文件夹 ├── performance_schema/ ├── sys/ ├── mydb/ ← 你创建的数据库 │ └── db.opt ← 数据库属性文件 ├── ibdata1 ← InnoDB 系统表空间 ├── ib_logfile0 ← redo 日志 └── ib_logfile12.3 表在文件系统上的三件套建表CREATE TABLE test (c1 INT)时MySQL 需要存储两类信息信息类型内容存储文件表结构列名、数据类型、约束、索引、字符集等表名.frm表数据实际插入的用户记录取决于存储引擎.frm是二进制文件直接打开会看到乱码——它是给 MySQL 程序读的不是给人读的。2.4 InnoDB vs MyISAM两种存储哲学对于同一个test表不同存储引擎产生的文件完全不同InnoDB 存储引擎默认 MyISAM 存储引擎 mydb/ mydb/ ├── test.frm ← 表结构 ├── test.frm ← 表结构 └── test.ibd ← 数据 索引 在一起 ├── test.MYD ← 数据文件MY Data └── test.MYI ← 索引文件MY Index 理念索引即数据 理念索引和数据分离关键差异InnoDB 的聚簇索引叶子节点存储完整记录所以数据和索引在同一个.ibd文件中。MyISAM 没有聚簇索引的概念所有索引都是二级索引叶子节点存的是行号而非主键以此实现回表。三、InnoDB 表空间页面的大池子3.1 为什么要有表空间前一篇文章讲过InnoDB 以16KB 的页为基本单位管理存储空间每个索引对应一棵 B 树树的每个节点就是一个数据页。但问题是成千上万个页分散在磁盘上谁来统一管理它们于是表空间登场了。你可以把表空间想象成一个巨大的池子池子里装满了页。插入记录时就从池子里捞出对应的页来写入。表空间是一个抽象概念最终对应文件系统上的一个或多个真实文件。表空间逻辑概念 │ │ 映射到 ▼ 文件系统上的物理文件 如 ibdata1、test.ibd │ │ 内部切分为 ▼ 若干个 16KB 的页3.2 系统表空间 vs 独立表空间InnoDB 提供两种主要的表空间类型特性系统表空间独立表空间文件ibdata1可配置多个表名.ibd作用范围整个 MySQL 实例共享一个每个表独享一个数据存放所有表的数据都可以放这里每个表的数据各自独立MySQL 版本5.5.7 ~ 5.6.6 默认5.6.6 默认配置参数innodb_data_file_pathinnodb_file_per_table1配置系统表空间为多个文件[server] # 创建 data1 和 data2 两个文件各 512Mdata2 不够时自动扩展 innodb_data_file_pathdata1:512M;data2:512M:autoextend切换表空间类型-- 查看当前使用的表空间模式SHOWVARIABLESLIKEinnodb_file_per_table;-- 将已有表从独立表空间迁移到系统表空间ALTERTABLEtestTABLESPACEinnodb_system;-- 将已有表从系统表空间迁移回独立表空间ALTERTABLEtestTABLESPACEinnodb_file_per_table;最佳实践现代 MySQL 8.0 默认使用独立表空间innodb_file_per_tableON这有几个好处——删除表时直接回收磁盘空间删.ibd文件即可不同表的 IO 互不干扰备份还原更灵活。四、表空间的内部结构区、段与碎片表空间不是把页胡乱堆在一起而是有一套精密的结构。4.1 区Extent连续存储的秘密回顾 B 树的范围查询找到范围起点后沿着叶子节点的双向链表顺序扫描即可。但如果链表上相邻的页在物理上离得很远每次跳到下一页都是随机 IO磁盘的磁头或 SSD 的寻址就要疲于奔命。为了解决这个问题InnoDB 引入了一个更大的分配单位——区Extent。一个区 连续 64 个页 64 × 16KB 1MB ┌──────────────────────────────────────┐ │ 一个区 (1MB) │ │ ┌────┐┌────┐┌────┐ ... ┌────┐ │ │ │页0 ││页1 ││页2 │ │页63│ │ │ └────┘└────┘└────┘ └────┘ │ │ ←── 64 个页在物理上连续存储 ──→ │ └──────────────────────────────────────┘当表中数据多了以后分配空间就以区为单位而非以页为单位这样同一个 B 树节点附近的页大概率物理相邻范围扫描就成了顺序 IO。扩展知识为什么刚好是 64 个页1MB 的大小是一个精心设计的平衡——既大到能显著减少随机 IO又小到不会因为填不满而浪费太多空间。4.2 段Segment叶子与非叶子的分流如果只按区分配叶子节点和非叶子节点的页会混在同一个区里范围扫描时仍会扫到大量无关的非叶子页。于是 InnoDB 进一步提出了段Segment一个索引 2 个段 │ ├── 叶子节点段Leaf Segment │ 存放 B 树叶子节点的所有页 │ └── 非叶子节点段Non-Leaf Segment 存放 B 树内节点的所有页所以对于一个有 N 个索引的表就有2N 个段。比如1 个聚簇索引 → 2 个段再加 1 个二级索引 → 再加 2 个段总计 4 个段每个段都以区为单位申请空间叶子段和非叶子段的页物理上相互隔离范围扫描时畅行无阻。4.3 碎片区小表的精打细算问题来了一个区默认 1MB那一个只插了几十条记录的小表也需要 2MB两个段各占一区这太浪费了。InnoDB 的解决方案是碎片区Fragment Extent小表初期段占用 32 个分散页 段 A 的零散页面 ──┐ 段 B 的零散页面 ──┼── 都从一个碎片区里按页租用 段 C 的零散页面 ──┘ 大表阶段段占用 ≥ 32 个分散页 段 A 直接申请完整的区1MB不再拼租每个区有四种状态状态含义归属FREE完全空闲啥都没用直属于表空间FREE_FRAG碎片区还有空闲页可用直属于表空间FULL_FRAG碎片区已无空闲页直属于表空间FSEG已分配给某个段附属于段如果把表空间比作一个集团军段就是师区就是团。FREE/FREE_FRAG/FULL_FRAG状态的区就像独立团直接听命于军部而FSEG状态的区则是各师的直属团。五、表空间的管理机制5.1 XDES Entry每个区的身份证表空间里有成千上万个区怎么记住每个区的状态InnoDB 为每个区设计了一个 40 字节的XDES EntryExtent Descriptor EntryXDES Entry 结构40 字节 ┌──────────────────┬──────────────────┬──────────┬────────────────────┐ │ Segment ID │ List Node │ State │ Page State Bitmap │ │ (8 字节) │ (12 字节) │ (4 字节) │ (16 字节) │ │ │ │ │ │ │ 该区属于哪个段 │ 前后指针用于 │ FREE │ 128 个比特位 │ │ 如果分配给段的话│ 串联成链表 │ FREE_FRAG│ 64 组 × 2 位 │ │ │ │ FULL_FRAG│ 标记区内每个页 │ │ │ │ FSEG │ 是否空闲 │ └──────────────────┴──────────────────┴──────────┴────────────────────┘每个组最多 256 个区每个区一个 XDES Entry所以需要40 × 256 10240字节。这些 XDES Entry 集中存储在每个组的第一个页面中。5.2 链表王国15 条链表的精密协作InnoDB 用链表来管理所有区而不是每次都遍历扫描。这些链表由 XDES Entry 通过List Node串联而成直属于表空间的 3 条链表所有区都参与 FREE 链表 → 串联所有 FREE 状态的区 FREE_FRAG 链表 → 串联所有 FREE_FRAG 状态的区 FULL_FRAG 链表 → 串联所有 FULL_FRAG 状态的区 每个段内部还有 3 条链表只串联该段拥有的区 FREE 链表 → 该段中全空闲的区 NOT_FULL 链表 → 该段中还有空页的区 FULL 链表 → 该段中已满的区以一个只有聚簇索引的表为例表 t仅有聚簇索引 │ ├── 叶子节点段 → FREE / NOT_FULL / FULL 链表 × 3 └── 非叶子节点段 → FREE / NOT_FULL / FULL 链表 × 3 加上直属于表空间的 3 条FREE / FREE_FRAG / FULL_FRAG 加上 INODE 页的管理链表SEG_INODES_FULL / SEG_INODES_FREE 总计3 3×2 3 2 14 条链表每有链表就有一个List Base Node结构16 字节记录了链表的头节点位置、尾节点位置和节点总数List Base Node: ├── List Length (4 字节) ├── First Node Page Number (4 字节) Offset (2 字节) └── Last Node Page Number (4 字节) Offset (2 字节)这些基节点存放在表空间头部页面的固定位置访问任何一个链表都非常高效。5.3 INODE Entry每个段的档案区有 XDES Entry 这个身份证段也有自己的户口本——INODE Entry192 字节INODE Entry 结构192 字节 ┌────────────┬───────────────┬───────────────┬───────────────┬──────────────────────┐ │ Segment ID │ NOT_FULL_N_USED│ FREE 链表 │ NOT_FULL 链表 │ FULL 链表 │ │ (8 字节) │ (4 字节) │ List Base Node│ List Base Node│ List Base Node │ │ │ NOT_FULL 链表 │ (16 字节) │ (16 字节) │ (16 字节) │ │ │ 已使用页数 │ │ │ │ ├────────────┴───────────────┴───────────────┴───────────────┴──────────────────────┤ │ Magic Number (4 字节) → 值为 97937874 表示已经初始化 │ ├──────────────────────────────────────────────────────────────────────────────────┤ │ Fragment Array Entry × 32 (每个 4 字节) → 记录该段零散页面的页号 │ └──────────────────────────────────────────────────────────────────────────────────┘一个INODE类型的页可以存放 85 个 INODE Entry。如果段太多、一个页放不下就通过SEG_INODES_FULL/SEG_INODES_FREE链表串联更多的 INODE 页面。5.4 Segment Header索引如何找到自己的段最后一个关键问题每个索引有两个段叶子段和非叶子段索引怎么找到自己的段对应的 INODE Entry答案藏在索引根页面的 Page Header 部分INDEX 类型页面的 Page Header部分字段 ┌─────────────────────────┬──────────┬──────────────────────────────────────┐ │ PAGE_BTR_SEG_LEAF │ 10 字节 │ B 树叶子节点段对应的 INODE Entry 地址 │ ├─────────────────────────┼──────────┼──────────────────────────────────────┤ │ PAGE_BTR_SEG_TOP │ 10 字节 │ B 树非叶子节点段对应的 INODE Entry 地址 │ └─────────────────────────┴──────────┴──────────────────────────────────────┘ 每个 Segment Header 记录一个精确地址 ├── Space ID (4 字节)INODE Entry 所在的表空间 ID ├── Page Number (4 字节)INODE Entry 所在的页号 └── Byte Offset (2 字节)INODE Entry 在页内的偏移量这样索引就能通过根页面中的 Segment Header 精确定位到叶子段和非叶子段的所有信息。六、系统表空间与数据字典6.1 系统表空间的特殊页面系统表空间的整体结构和独立表空间类似但多了几个记录整个系统信息的页面系统表空间布局Space ID 0 页号 类型 用途 ────────────────────────────────────────── 0 FSP_HDR 表空间头部信息 第1组 XDES Entry 1 IBUF_BITMAP Insert Buffer 位图 2 INODE INODE Entry 存储页 ── 以下为系统表空间特有 ── 3 SYS Insert Buffer 头部 4 INDEX Insert Buffer 根页面 5 TRX_SYS 事务系统信息 6 SYS 第一个回滚段 7 SYS 数据字典头部 ⭐ ── Doublewrite Buffer ── 64~127 双写缓冲区第1区 128~191 双写缓冲区第2区6.2 InnoDB 数据字典执行INSERT INTO t VALUES (1, hello)时MySQL 需要验证表t是否存在列数量是否匹配该表的索引根页面在哪个表空间的哪个页这些元数据都存在 InnoDB 的内部系统表中InnoDB 的 4 个基本系统表它们自己也是 B 树 SYS_TABLES → 整个 InnoDB 中所有表的信息 SYS_COLUMNS → 所有列的信息类型、长度、是否可空... SYS_INDEXES → 所有索引的信息根页面位置、索引类型... SYS_FIELDS → 每个索引包含哪些列这 4 张表的元数据它们有哪些列、索引在哪里硬编码在代码中而它们的索引根页面位置记录在页号为 7 的 Data Dictionary Header 页面里Data Dictionary Header 的关键字段 Max Row ID → 自增 row_id全局共享 Max Table ID → 下次建表时分配给新表的 ID Max Index ID → 下次建索引时分配给新索引的 ID Max Space ID → 下次建表空间时分配给新表空间的 ID Root of SYS_TABLES clust index → SYS_TABLES 聚簇索引根页面 Root of SYS_TABLE_IDS sec index → SYS_TABLES 的 ID 列二级索引根页面 Root of SYS_COLUMNS clust index → SYS_COLUMNS 聚簇索引根页面 Root of SYS_INDEXES clust index → SYS_INDEXES 聚簇索引根页面 Root of SYS_FIELDS clust index → SYS_FIELDS 聚簇索引根页面6.3 information_schema给用户开的后门普通用户不能直接访问SYS_*内部系统表但可以通过information_schema数据库中的INNODB_SYS_*表查看USEinformation_schema;SHOWTABLESLIKEINNODB_SYS%;-- 结果-- INNODB_SYS_TABLES ← 查看所有 InnoDB 表的信息-- INNODB_SYS_COLUMNS ← 查看所有列的定义-- INNODB_SYS_INDEXES ← 查看所有索引的信息-- INNODB_SYS_FIELDS ← 查看索引包含的列-- INNODB_SYS_TABLESPACES ← 查看所有表空间-- INNODB_SYS_DATAFILES ← 查看表空间对应的物理文件-- ...-- 实战查看某个表所在表空间的 IDSELECTname,spaceFROMINNODB_SYS_TABLESWHEREnameLIKE%test%;这些INNODB_SYS_*表不是真正的内部系统表而是 MySQL 启动时从SYS_*表读取数据后填充的只读快照。七、总结一张图看清全貌MySQL 数据存储层次结构 ┌─────────────────────────────────────────────────────────────────┐ │ 数据目录 (datadir) │ │ /usr/local/var/mysql/ │ │ │ │ ┌────────────┐ ┌────────────┐ ┌────────────┐ │ │ │ mydb/ │ │ testdb/ │ │ mysql/ │ ... 数据库 │ │ │ ├ db.opt │ │ ├ db.opt │ │ (系统库) │ │ │ │ ├ t1.frm │ │ ├ t2.frm │ │ │ │ │ │ └ t1.ibd ─┼──┼──┼──→ 表空间 ──────────────┼─────────────── │ │ └────────────┘ │ └ t2.ibd │ └────────────┘ │ │ └────────────┘ │ └─────────────────────────────────────────────────────────────────┘ │ ┌───────────────────┴───────────────────┐ ▼ ▼ 系统表空间 (ibdata1) 独立表空间 (t1.ibd) 每个实例只有一份 每个表一份 │ │ │ ┌─── 区 (Extent) ────────────────┐ │ └──│ 连续 64 个页 1MB │───┘ │ XDES Entry 管理每个区 │ │ FREE / FREE_FRAG / FULL_FRAG │ └────────────────────────────────┘ │ ┌───────────────────┴───────────────────┐ ▼ ▼ 叶子节点段 (Leaf Segment) 非叶子节点段 (Non-Leaf Segment) 存放完整用户记录 存放目录项记录 INODE Entry 管理 INODE Entry 管理 FREE / NOT_FULL / FULL 链表 FREE / NOT_FULL / FULL 链表 │ │ └───────────────────┬───────────────────┘ ▼ 数据页 (16KB) B 树的节点 真正的记录存储单元核心要点回顾概念一句话解释类比数据目录MySQL 存放所有数据的根路径一栋大楼数据库数据目录下的一个子文件夹大楼里的一层表空间管理页的逻辑容器对应.ibd或ibdata1一层里的一个房间区 (Extent)64 个连续页分配空间的基本单位房间里的一个书架段 (Segment)索引的叶子/非叶子节点各自独立的区集合书架按小说/工具书分区页 (Page)16KB 的读写基本单位书架上的一本书数据字典InnoDB 内部系统表记录表和索引的元数据房间门口的目录索引延伸阅读MySQL 官方文档 The InnoDB Storage EngineInnoDB 表空间管理源码分析