一、起源:为什么需要存储引擎层
MySQL 的数据最终必须落在磁盘上,而磁盘的访问速度与内存相差数个数量级,页缓存与文件系统层面的速度差距见 linux: 页缓存与 IO 性能。数据库无法回避这层差距,只能在两者之间做出取舍:用内存缓存磁盘数据以换取读性能,用日志的顺序写以换取写性能与崩溃安全。
1.1 数据存放位置的取舍
- 若数据只存内存,容量受限于物理内存,且进程退出即丢失
- 若数据只存磁盘,每次读写都触发设备访问,吞吐不可接受
- 折中方案是将磁盘作为唯一持久化载体,内存作为磁盘数据的缓存副本,由数据库自身而非操作系统决定缓存的粒度与淘汰策略
页缓存同样是磁盘数据的缓存,但它是通用的、按文件偏移组织的,无法理解数据页内部的记录结构、索引顺序与事务可见性。数据库需要一层能感知数据语义的缓存与存储抽象,这正是存储引擎层存在的第一个理由。
1.2 随机写与顺序写的差异
磁盘设备的物理特性决定了顺序访问与随机访问的成本差异:
| 访问模式 | 机械磁盘 | 固态磁盘 | 影响 |
|---|---|---|---|
| 顺序写 | 磁头几乎不移动,可持续高吞吐 | 按块顺序写入,写放大低 | 单位时间写入量大 |
| 随机写 | 每次写需寻道与旋转等待 | 触发块擦除与垃圾回收,写放大高 | 单位时间写入量小,延迟高 |
数据页的修改是随机的:事务可能修改任意表、任意位置的记录,对应的数据页在磁盘上分散存放。若每次提交都把这些分散的页直接写回磁盘,写操作将以随机写为主,吞吐被设备能力限制。
由此产生一个核心设计取舍:将随机写转换为顺序写。把「对某个数据页某个偏移的修改」先按发生顺序追加到一份日志文件末尾,再在空闲时把日志中描述的修改合并回数据页。日志追加是顺序写,数据页回写可以批量、延迟、合并执行。这份日志就是 redo 日志,这种策略称为预写日志(Write-Ahead Logging,WAL)。
1.3 崩溃后如何保证已提交数据不丢
WAL 本身不解决崩溃一致性问题,还需要回答两个问题:
- 已提交但尚未写回数据页的修改如何保留:事务提交时日志已经落盘,重启后按日志重新施加修改即可
- 未提交的修改如何撤销:日志中可能已经记录了未提交事务的部分修改,重启后需要反向执行这些修改
因此需要两份信息:一份描述「做了什么」用于重做,一份描述「如何撤销」用于回滚。前者是 redo,后者是 undo。
此外还存在一个设备层面的问题:数据页的大小通常大于底层设备的原子写单位,页在写入过程中掉电可能导致只写入了一部分,形成部分页写入(partial page write)。这种损坏页无法用 redo 修复,因为 redo 描述的是基于完整原页的增量修改。该问题需要额外机制处理,即双写缓冲。
1.4 存储引擎层的职责
MySQL 采用分层架构,把「如何理解 SQL」与「如何存放数据」拆开:
- Server 层负责连接管理、SQL 解析、执行计划选择、事务语义、日志(binlog)、复制
- 存储引擎层负责数据的物理组织、索引结构、并发控制、事务的持久性与回滚、崩溃恢复
- 两层之间通过一组统一的接口(handler API)交互,存储引擎可替换
这种可替换性意味着同一套 SQL 在不同引擎上的行为与代价可能完全不同。InnoDB 自 MySQL 5.5 起成为默认存储引擎,原因在于它同时提供事务、行级锁、外键与崩溃恢复能力;MyISAM 不提供事务与崩溃恢复,Memory 引擎数据不落盘。引擎相关参数模板见 mysql: 关于 MySQL 服务配置,客户端与状态查看命令见 mysql: 使用命令。
二、原理
2.1 整体架构:InnoDB 在 MySQL 中的位置
一条 SQL 从客户端到磁盘需要经过三层,每层承担不同职责:
| 层次 | 组成 | 职责 | 与 InnoDB 的关系 |
|---|---|---|---|
| 连接层 | 连接管理、线程池、认证 | 管理客户端连接与会话状态 | 无关,连接在进入解析前已建立 |
| Server 层 | 解析器、优化器、执行器、binlog、查询缓存(已废弃) | 把 SQL 变成对表的读写请求 | 通过 handler 接口调用存储引擎 |
| 存储引擎层 | InnoDB 内存结构、后台线程、磁盘结构 | 数据的实际读写、事务、恢复 | 本文讨论对象 |
InnoDB 内部又可划分为三部分:
- 内存结构:Buffer Pool、Change Buffer、Log Buffer、自适应哈希索引
- 后台线程:Page Cleaner 刷脏线程、Purge 清理线程、IO 线程、Master 线程
- 磁盘结构:各类表空间文件、redo 日志文件、双写文件
一次读操作的路径为「Server 层请求页号 → Buffer Pool 查找 → 命中则返回内存页 → 未命中则发起磁盘读并装入 Buffer Pool」。一次写操作的路径为「修改 Buffer Pool 中的页并置为脏页 → 写 undo 与 redo → 事务提交时按刷盘策略把 redo 落盘 → 脏页由后台线程异步写回磁盘」。
2.2 内存结构
2.2.1 Buffer Pool
Buffer Pool 是 InnoDB 最核心的内存区域,以页为单位缓存表空间中的数据页与索引页。默认页大小为 16KB(innodb_page_size,建库后不可更改)。
- 页的定位:以「表空间 ID + 页号」为键维护哈希表,通过该键在缓存中直接定位页;控制块记录页的状态(干净/脏、是否被固定、访问时间)
- 缓存收益:读写均在内存中完成,避免磁盘访问;同一页被反复访问时收益最大
- 内存占用:Buffer Pool 通常是实例中占比最大的内存区域,其大小直接决定可缓存的页数量
LRU 分代
若采用简单的 LRU,一次全表扫描或预读会把大量只用一次的页放到链表头部,把真正的热点页挤出。InnoDB 因此把 LRU 链表分为两段:
- young 区(new 区):链表前段,占约 5/8,存放热点页
- old 区(后段):链表后段,占约 3/8,由
innodb_old_blocks_pct控制(默认 37) - 新页插入位置:新读入的页先插入 old 区头部,而不是链表头部
- 晋升条件:页在 old 区中停留时间超过
innodb_old_blocks_time(默认 1000 毫秒)后被再次访问,才晋升到 young 区;停留时间不足的重复访问不触发晋升
该机制使全表扫描这类「一次性大量访问」的页停留在 old 区并很快被挤掉,不污染热点数据。
预读
预读指在访问尚未请求的相邻页时提前把它们读入 Buffer Pool,利用磁盘顺序读的高吞吐:
| 类型 | 触发条件 | 相关参数 |
|---|---|---|
| 线性预读 | 顺序访问一个区(extent)内连续页数超过阈值,则预读下一个区 | innodb_read_ahead_threshold,默认 56 |
| 随机预读 | 一个区中已有相当数量的页在缓存中,则预读该区剩余页 | innodb_random_read_ahead,默认关闭 |
预读在顺序访问场景下收益明显,在随机访问场景下会产生无效 IO,因此随机预读默认关闭。
脏页与刷脏
被修改过但尚未写回磁盘的页称为脏页。刷脏的触发来自多个方向:
- 从 LRU 尾部腾出空闲页:需要空闲页时从 LRU 尾部扫描并刷脏,扫描深度由
innodb_lru_scan_depth控制 - 脏页比例超过阈值:由
innodb_max_dirty_pages_pct控制,自适应刷脏innodb_adaptive_flushing会根据 redo 产生速度动态提前开始刷脏 - redo 空间即将耗尽:redo 日志是循环写的,若脏页刷回速度跟不上 redo 产生速度,redo 无法被覆盖,写入会被阻塞,这是最需要避免的情形
- checkpoint 推进:刷脏进度与 checkpoint 绑定,checkpoint 之后的 redo 才可以被覆盖
刷脏速度由 innodb_io_capacity 与 innodb_io_capacity_max 决定的 IO 预算约束,实际刷脏量还受相邻页刷新的影响(innodb_flush_neighbors,开启时刷一个页会连带刷相邻的脏页,机械盘上可减少随机 IO,固态盘上通常无益)。执行刷脏的线程数量由 innodb_page_cleaners 控制。
2.2.2 Change Buffer
Change Buffer 缓存的是对非唯一二级索引页的修改操作,而不是数据本身。其存在理由是:修改二级索引时,索引页在磁盘上的位置与数据页无关,若每次修改都读入索引页,将产生大量随机读。
- 作用:把对非唯一二级索引的插入、更新、删除操作先记录在 Change Buffer 中,不立即读取索引页
- 合并时机:该索引页因其他原因被读入 Buffer Pool 时顺带合并;后台线程定期合并;缓冲池空间不足时合并;实例正常关闭或崩溃恢复时完成合并
- 适用条件:仅限非唯一二级索引
- 不适用条件:唯一索引与主键的写入必须立即读入对应页以校验唯一性,无法延迟,因此不进入 Change Buffer;表中几乎没有二级索引时该区域无用
- 空间上限:占用 Buffer Pool 的比例由
innodb_change_buffer_max_size控制(默认 25,即 25%)
合并集中在写多读少或批量导入场景收益最大:批量导入时索引页的随机读被推迟并合并为较少的顺序访问。写少读多、或者二级索引全部为唯一索引的场景,Change Buffer 的价值有限。
2.2.3 Log Buffer
redo 日志不是直接写文件的,先进 Log Buffer,再按条件刷到 redo 文件:
- 大小:由
innodb_log_buffer_size控制(默认 16MB) - 刷盘触发条件:
- 事务提交,且
innodb_flush_log_at_trx_commit取值要求刷盘 - 后台每秒刷一次
- Log Buffer 中已用空间超过约一半
- 发生 redo 日志文件切换(checkpoint 推进)
- 批量提交:多个并发事务的 redo 记录可合并为一次刷盘操作(组提交),把多次小 IO 合并为单次大 IO
Log Buffer 过小会导致频繁刷盘,过大则在事务密集写入时产生瞬时大 IO。
2.2.4 自适应哈希索引
InnoDB 会针对频繁以等值条件访问的索引页,在内存中建立哈希索引,把 B+ 树的多次页访问降为一次查找。该特性由 innodb_adaptive_hash_index 控制,默认开启。哈希索引只服务于等值查询,范围查询仍然走 B+ 树;其维护需要额外的内存与闩锁,高并发下可能成为争用点。
2.3 磁盘结构
2.3.1 表空间
InnoDB 的数据在磁盘上以表空间(tablespace)为单位组织,MySQL 8.0 中主要分为五类:
| 表空间 | 典型文件 | 存放内容 | 特点 |
|---|---|---|---|
| 系统表空间 | ibdata1 | 数据字典、Change Buffer、双写缓冲(旧版本)、回滚段(旧版本) | 全局唯一,通常只增不减 |
| 独立表空间 | 每表一个 .ibd | 该表的数据与索引 | 由 innodb_file_per_table 控制,可单独删除或迁移 |
| 通用表空间 | 自定义 .ibd | 多张表共享 | 用 CREATE TABLESPACE 创建,可指定存储位置 |
| undo 表空间 | undo_001、undo_002 | 回滚段与 undo 记录 | 8.0 起独立管理,数量固定为 2 |
| 临时表空间 | ibtmp1 | 用户临时表、回滚段 | 重启时重建,不承载持久化数据 |
独立表空间是生产环境的常规选择:单表损坏不牵连其他表,DROP TABLE 可直接释放空间,表可单独迁移。系统表空间在 8.0 中因数据字典改为独立管理,其体积增长已比 5.7 缓和,但仍然只增不减。
2.3.2 段、区与页的层级
- 页(page):最小 IO 单位,默认 16KB
- 区(extent):由页组成,页大小 16KB 时一个区为 1MB(64 个页),用于减少碎片、提高顺序性
- 段(segment):由若干区组成,对应逻辑对象(如一个索引的叶子节点段、非叶子节点段)
- 表空间(tablespace):由一个或多个段组成
新建小表时不会立即分配完整区,而是从小批量页开始,随数据增长逐步申请新区。
2.3.3 页结构与索引组织表
数据页的内部结构自页首到页尾依次为:
- File Header(38 字节):页号、前后页指针、页类型、校验和,负责页级别的链表连接与校验
- Page Header(56 字节):页内记录数、空闲空间位置、页目录槽数等
- Infimum 与 Supremum 记录:页内的虚拟边界记录,位于记录链两端
- User Records:实际的行记录,按索引键顺序组成单向链表
- Free Space:尚未使用的空间
- Page Directory:记录的稀疏索引槽,用于页内二分查找
- File Trailer(8 字节):页尾校验和,与 File Header 中的校验和配合判断页是否完整写入
InnoDB 采用索引组织表(Index Organized Table):表数据即索引数据。
- 聚簇索引:以主键组织的 B+ 树,叶子节点存放完整行记录。若未显式定义主键,InnoDB 会选择一个非空唯一索引替代,都没有则内部生成 6 字节的行 ID
- 二级索引:叶子节点存放的是索引列的值加主键值。通过二级索引查询非索引列时,需要用主键值回到聚簇索引取整行,称为回表
- 代价:主键过长会同时放大聚簇索引与所有二级索引的体积,因此通常选择短且单调递增的主键;回表代价、覆盖索引与联合索引的列顺序规则见 mysql: 索引与执行计划
2.3.4 行格式与溢出页
行格式决定行记录在页内的编码方式:
| 行格式 | 特点 | 适用 |
|---|---|---|
| REDUNDANT | 旧格式,头部开销大,已不推荐 | 兼容旧版本 |
| COMPACT | 变长字段长度列表与 NULL 标志位,头部开销较小;溢出列在页内保留 768 字节前缀加 20 字节指针 | 早期默认 |
| DYNAMIC | 在 COMPACT 基础上改为溢出列在页内只存 20 字节指针,不再保留前缀 | 5.7 起默认,长文本与 BLOB 场景更省空间 |
| COMPRESSED | 在 DYNAMIC 基础上引入页级压缩,可设置 KEY_BLOCK_SIZE | 需在存储空间与读放大之间权衡 |
当行记录过大导致单页容纳不下时,超长的变长列会存放到溢出页(off-page),页内只保留指向溢出页的指针。COMPACT 与 DYNAMIC 在溢出处理上的差别是二者的主要分野:DYNAMIC 不在页内保留列前缀,因此单页可容纳更多行,但读取溢出列需要额外一次页访问。
2.4 redo 与 undo
2.4.1 redo 的作用与循环写入
redo 日志记录的是数据页的物理修改:哪个表空间的哪个页、在什么偏移、做了什么修改。它的作用是在崩溃重启后把已经落盘的日志中描述的修改重新施加到数据页上,从而恢复「已提交但数据页尚未写回」的事务。
redo 日志是循环写的:
- 日志文件被组织成一个环,写入位置不断向前推进,LSN(Log Sequence Number)单调递增,记录当前写入进度
- 数据页写回磁盘后,checkpoint 相应推进,记录「此位置之前的日志对应的修改已经全部落盘」
- 只有 checkpoint 之前的日志空间可以被覆盖重用
- 如果脏页刷回速度跟不上日志产生速度,可覆盖空间耗尽,新的写入会被阻塞(表现为
Innodb_log_waits增长),这是 redo 设计中最需要规避的状态
因此 redo 用顺序追加写换取了数据页随机写的持久化:提交时只需保证一小段顺序日志落盘,而不必把分散的脏页立刻写回。redo 的容量配置与写入进度可通过 mysql: 使用命令 中的状态查询手段观察。redo 日志在 8.0.30 之前由 ib_logfile0、ib_logfile1 等固定数量文件组成,8.0.30 起改由 innodb_redo_log_capacity 统一管理 #innodb_redo 目录下的多个文件。
redo 与 binlog 的分工不同:redo 是存储引擎层的物理日志,只用于本实例的崩溃恢复;binlog 是 Server 层的逻辑日志,用于复制与时间点恢复。两者在提交时通过两阶段提交保持逻辑一致,机制见 mysql: 主从同步原理。
2.4.2 undo 的作用与 MVCC
undo 日志记录的是逻辑反向操作:插入对应删除,更新对应反向更新。它服务两个目标:
- 事务回滚:事务失败或显式回滚时,按 undo 记录反向执行
- 一致性读与多版本控制(MVCC):记录被修改前的旧版本,使不同事务能够读取到各自时刻可见的数据版本
undo 存放在回滚段(rollback segment)中,回滚段位于 undo 表空间。行记录中有一个隐藏列指向该行最近的 undo 记录,多条 undo 记录串成版本链,按需回溯即可构造出某个历史版本。一致性读、隔离级别与锁的具体规则见 mysql: 事务与锁。
undo 的写入同样是先写 undo 页再写 redo,因此 undo 本身也受 redo 保护。
2.4.3 purge 机制
undo 记录不能永久保留:一旦没有任何活跃事务需要看到某个旧版本,对应的 undo 就可以被清理。清理过程称为 purge:
- 后台 purge 线程按事务提交顺序扫描 undo 记录
- 判断该旧版本是否仍被任何活跃 Read View 需要
- 不再需要时,删除对应的 undo 记录并清理该版本的行记录
- 线程数量由
innodb_purge_threads控制
purge 无法推进的典型原因是长事务:一个长时间不提交的事务会使 Read View 长期存活,导致其之后产生的所有 undo 都无法清理,表现为 undo 表空间持续膨胀、版本链变长、查询需要回溯更多版本。此外,长事务还会推迟 checkpoint 推进,进而使 redo 空间紧张。
2.5 双写缓冲
2.5.1 部分页写入问题
数据页为 16KB,而底层设备的原子写单位远小于该值(通常为 512 字节或 4096 字节)。页写入过程中掉电时,可能出现只写了一部分的情况,形成部分页写入。
这类页无法用 redo 修复:redo 记录的是基于完整原页的增量修改,若原页本身已经损坏,施加增量只会得到错误结果。而且页尾的校验和与页首的校验和不一致,页会被判定为损坏。
2.5.2 双写的作用与代价
双写缓冲(doublewrite buffer)的思路是:在写入真正的数据页之前,先把整页顺序写到一块专门的区域,写入完成后再写回表空间中的实际位置。
- 写入流程:脏页刷盘时,先把一组脏页顺序写入双写区并刷盘,再把同样的页写入各自的实际位置
- 恢复流程:崩溃后若发现某个页校验失败,从双写区中找到该页的完整副本,用它覆盖损坏页,再用 redo 恢复
- 代价:每个页被写两次,写放大接近 2 倍;但双写区的写入是顺序的,实际开销远小于两倍随机写
- 位置:早期双写区位于系统表空间;MySQL 8.0.20 起改为 datadir 下的独立双写文件
双写缓冲解决的是「页不完整」而非「页内容错误」,与 redo 解决的层面不同,二者缺一不可。
2.5.3 可关闭的条件与风险
双写由 innodb_doublewrite 控制(默认开启)。关闭的前提是底层存储能够保证整页写入的原子性,例如:
- 文件系统或卷管理层提供原子写或写时复制语义
- 存储设备具备掉电保护(电容缓存),能够保证已确认写入的数据完整落盘
- 使用支持整块原子写的固态设备且已知其原子写单位不小于页大小
不具备上述条件时关闭双写,一旦发生掉电,可能直接产生无法恢复的损坏页。关闭属于降低持久化保护级别的操作,标记与处置见第 3.5 节。
2.6 刷盘与持久化参数
2.6.1 innodb_flush_log_at_trx_commit
该参数决定事务提交时 redo 日志的刷盘强度,是持久化强度与吞吐之间最直接的开关:
| 取值 | 提交时行为 | 数据丢失窗口 | 典型场景 |
|---|---|---|---|
| 0 | 不写入、不刷盘,仅由后台每秒刷新 | 实例崩溃或操作系统崩溃丢失最多约 1 秒已提交事务 | 允许少量丢失、可重建的数据 |
| 1 | 每次提交写入并调用 fsync,等待设备确认 | 不丢失已提交事务 | 交易、账务类数据,默认值 |
| 2 | 每次提交写入操作系统缓存,不调用 fsync | 实例崩溃不丢;操作系统或主机掉电丢失最近约 1 秒事务 | 可容忍主机级丢失的高写入量场景 |
取值 0 与 2 的差别在于数据是否交给了操作系统:取值 2 下进程崩溃不丢数据,但主机掉电仍会丢;取值 0 下进程崩溃即丢。
2.6.2 sync_binlog 与 redo 的关系
binlog 在提交过程中同样需要刷盘,由 sync_binlog 控制:取值为 1 表示每次提交都刷盘,取值为 N 表示每 N 次提交刷一次,取值为 0 时刷盘时机完全交由操作系统决定。
两个参数的刷盘动作落在同一事务的两阶段提交流程中:
- 写 redo 并置为 prepare 状态
- 写 binlog 并按其策略刷盘
- 写 redo 并置为 commit 状态
崩溃后依据该流程判断事务去向:若 binlog 完整则提交,若 redo 已 prepare 而 binlog 不完整则回滚。因此 innodb_flush_log_at_trx_commit 与 sync_binlog 的取值需要匹配,只保证其中一个落盘并不能保证主从数据与恢复链路的完整。复制链路中 binlog 的作用见 mysql: 主从同步原理。
并发提交时,多个事务的刷盘请求会被合并执行,称为组提交,因此将两个参数都设为 1 的实际代价通常低于逐事务串行刷盘的估算。
2.6.3 innodb_flush_method 与操作系统缓存的交互
innodb_flush_method 决定 InnoDB 打开数据文件的方式,直接影响数据是否经过操作系统页缓存:
| 取值 | 行为 | 结果 |
|---|---|---|
fsync | 使用带缓冲的读写,依赖 fsync 落盘 | 页同时存在于 Buffer Pool 与操作系统页缓存,内存被双重占用 |
O_DIRECT | 绕过操作系统页缓存,直接读写设备 | 避免双重缓存,但需处理对齐要求 |
O_DIRECT_NO_FSYNC | 在 O_DIRECT 基础上不再额外调用 fsync | 8.0 在 Unix 平台上的默认取值,减少一次同步开销 |
O_DSYNC | 以同步写方式打开日志文件 | 每次写等待落盘 |
页缓存的读写路径、脏页回写触发条件与 fsync、fdatasync 的语义差异见 linux: 页缓存与 IO 性能。选择 O_DIRECT 的目的正是避免通用页缓存与 Buffer Pool 争抢内存,同时让刷盘时机完全由数据库控制,不被内核的回写线程影响。需要注意对齐限制:缓冲区地址、偏移与长度需按块设备扇区大小对齐,否则调用失败;该限制同样适用于文件系统与磁盘设备的相关命令,见 linux: 磁盘存储与文件系统操作命令。
2.7 崩溃恢复
2.7.1 恢复流程
实例启动时,若检测到上次未干净关闭,会先执行崩溃恢复,再对外提供正常服务:
- 定位恢复起点:读取 checkpoint,确定从哪个 LSN 开始应用 redo
- redo 前滚:从恢复起点开始顺序扫描并应用 redo,把数据页恢复到崩溃前内存中已产生过的状态。此阶段会把未提交事务的修改也一并施加
- undo 回滚:扫描 undo,找出崩溃时处于未提交状态的事务,按 undo 反向执行,撤销它们的修改
- 完成清理:合并 Change Buffer、处理双写区中残留的页、重建临时表空间
2.7.2 恢复时间的影响因素
| 因素 | 影响 |
|---|---|
| 上次 checkpoint 之后产生的 redo 量 | 直接决定前滚阶段要扫描与应用的日志量 |
| 崩溃时未刷回的脏页数量 | 影响前滚阶段的页读取与写回量 |
| 崩溃时处于未提交状态的事务规模 | 大事务的回滚需要逐条反向执行,耗时与事务修改量成正比 |
| 双写区中待恢复的页数 | 影响损坏页的修复工作量 |
| 存储设备的随机读性能 | 前滚与回滚都涉及大量随机页访问 |
因此缩短恢复时间的常规手段是提高刷脏频率、避免长事务与超大事务,而非单纯调整日志大小。若实例在正常关闭时完成刷脏,则启动时无需恢复。崩溃后依赖备份与 binlog 完成数据修复的完整流程见 mysql: 备份与恢复。
2.7.3 崩溃后的常见现象
- 恢复期间拒绝新连接:恢复未完成时实例不提供正常服务,普通业务连接被拒绝,日志中会出现恢复进度的提示信息
- 临时表空间被重建:
ibtmp1不承载持久数据,启动时删除重建属正常行为 - 表空间损坏报错:日志中出现页校验失败、页号与预期不符、LSN 超前等信息,说明数据页或双写区存在损坏
- 恢复耗时明显长于重启耗时:属于预期现象,恢复过程不走正常启动路径
当页损坏导致实例无法启动时,可使用 innodb_force_recovery 逐级放宽限制以导出数据,其取值与风险见第 3.5 节。
2.8 关键限制与代价
2.8.1 Buffer Pool 命中率与磁盘 IO 的关系
Buffer Pool 的价值取决于命中率:
- 工作集(被频繁访问的数据)能够全部装入 Buffer Pool 时,读操作基本不产生磁盘 IO
- 工作集大于 Buffer Pool 时,每次访问都可能触发磁盘读,命中率下降,响应时间随之从内存级滑向磁盘级
- 命中率下降的影响是非线性的:一旦工作集超出容量,缓存淘汰会带来额外的脏页刷回与页重新读入,读写互相挤占 IO 预算
2.8.2 内存不足时的表现
- 热点页频繁被淘汰,命中率持续走低
- 脏页比例上升且刷脏更频繁,磁盘写入压力增大
- 空闲页不足时出现等待空闲页的情况(
Innodb_buffer_pool_wait_free增长),查询延迟出现抖动 - 操作系统层面发生换页,数据库内存被换出到磁盘,性能断崖式下降
2.8.3 表空间膨胀的成因
- 删除不释放空间:
DELETE只把记录标记为可复用,空间留在表空间内,供后续插入复用,文件不会缩小 - 页内碎片:随机删除与更新导致页内出现空洞,页利用率下降,同样的数据占用更多页
- 数据页分裂:乱序插入(如随机主键)导致页分裂,页的平均填充率降低
- undo 表空间膨胀:长事务阻塞 purge,undo 记录持续累积
- 系统表空间膨胀:旧版本中双写区与回滚段位于系统表空间,其占用随实例运行持续存在
以上成因决定了表空间治理的思路是控制碎片来源与管理长事务,而不是定期无条件重建表。
三、实现
3.1 内存相关配置的决策依据
3.1.1 Buffer Pool 大小
- 上限约束:需要为操作系统、连接相关工作内存、其他进程预留空间。参数模板中给出的经验区间与内存档位参考见 mysql: 关于 MySQL 服务配置
- 下限约束:需要能容纳生产环境的主要工作集,否则命中率无法达标
- 判断方法:观察命中率与磁盘读量,若磁盘读持续偏高而内存仍有余量,说明 Buffer Pool 偏小
- 调整方式:该参数支持在线调整,但扩缩容过程会阻塞一段时间,需在低峰期执行
-- 核对当前值与内存占用情况
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_data';
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_free';
3.1.2 缓冲池实例
innodb_buffer_pool_instances 把缓冲池切分为多个独立实例,各自维护 LRU 与闩锁,用于减少高并发下的争用:
- 单个实例过小会削弱缓存效果,通常每个实例不小于 1GB
- 实例数接近 CPU 核数后可获得较稳定的收益,继续增加会出现边际递减
- 实例数在实例启动后不便于频繁调整,应结合主机规格一次性确定
3.1.3 Change Buffer 与写模式
- 写多读少、批量导入、日志型表:二级索引页被延迟读取的收益大,可适当调大
innodb_change_buffer_max_size - 写少读多、实时查询为主:合并会被频繁的页读取提前触发,调大的意义有限
- 二级索引多为唯一索引:变更无法进入 Change Buffer,此时该区域的价值很低
- 注意:调大该比例会挤占 Buffer Pool 中用于数据页的空间,需与工作集大小一并权衡
3.2 持久化相关配置的决策依据
3.2.1 innodb_flush_log_at_trx_commit 的业务取舍
| 取值 | 丢失窗口 | 写入吞吐 | 适用业务 | 变更影响 |
|---|---|---|---|---|
| 1 | 不丢失已提交事务 | 受限于每次提交的同步开销 | 交易、账务、订单、余额 | 默认值,无需变更 |
| 2 | 主机掉电丢失最近约 1 秒事务 | 明显高于取值 1 | 可容忍主机级丢失、写入量大的业务 | 需明确业务可接受的丢失窗口 |
| 0 | 实例崩溃即丢失最近约 1 秒事务 | 高于取值 2 | 可重建的中间数据、批量导入的临时实例 | 需明确数据可重建 |
决策顺序建议为:先确认业务可接受的丢失窗口,再据此选择取值,而不是先按吞吐目标选择取值再反推容忍度。
同一类取舍在其他存储系统中同样存在:Redis 的 AOF 持久化在每次刷盘与每秒刷盘之间做出选择,默认的每秒刷盘对应的丢失窗口与本参数取值 0、2 的量级一致,见 Redis 核心与数据结构。
3.2.2 sync_binlog 的取舍
sync_binlog=1:提交链路上 binlog 与 redo 都刷盘,是主从架构与时间点恢复的常规配置sync_binlog=N:每 N 次提交刷一次,降低刷盘频率,代价是崩溃时最近 N 个事务的 binlog 可能缺失,从而影响从库一致性与恢复点sync_binlog=0:完全交由操作系统决定刷盘时机,丢失窗口不可控
在开启 binlog 的实例上,该参数与 innodb_flush_log_at_trx_commit 共同决定提交链路的持久化强度,两者不一致会导致崩溃恢复时的判断行为与预期不符。
3.2.3 刷脏相关参数与尖峰毛刺
周期性的写入抖动通常来自刷脏行为而非业务流量变化:
- IO 预算:
innodb_io_capacity与innodb_io_capacity_max描述设备能力,设置过低会使刷脏长期滞后,设置过高会在某一时刻集中释放大量 IO - 脏页阈值:
innodb_max_dirty_pages_pct越低,刷脏越早越均匀,抖动越少,但常态 IO 占用更高 - 自适应刷脏:
innodb_adaptive_flushing依据 redo 产生速度调整刷脏速率,建议保持开启 - 相邻页刷新:
innodb_flush_neighbors在固态盘上通常应关闭,避免为刷一个页而写入大量相邻页 - 刷脏线程数:
innodb_page_cleaners与innodb_buffer_pool_instances匹配时可提高并行刷脏能力 - redo 空间:redo 容量过小会让 checkpoint 频繁推进,刷脏被迫加速
# 刷脏相关配置示例(需结合设备能力与业务写入量确定取值)
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_max_dirty_pages_pct = 75
innodb_adaptive_flushing = ON
innodb_flush_neighbors = 0
innodb_page_cleaners = 4
3.3 表空间与文件管理
3.3.1 独立表空间的创建与核对
- 确认
innodb_file_per_table已开启 - 新建表会生成对应
.ibd文件;已有表需要重建才能转为独立表空间 - 通过数据字典核对表空间的落盘情况
-- 核对参数
SHOW VARIABLES LIKE 'innodb_file_per_table';
-- 核对表与文件对应关系(MySQL 8.0)
SELECT SPACE, NAME, FILE_FORMAT, ROW_FORMAT
FROM information_schema.INNODB_TABLESPACES
ORDER BY NAME;
-- 核对各库表占用(含数据与索引长度)
SELECT TABLE_SCHEMA, TABLE_NAME,
DATA_LENGTH, INDEX_LENGTH, DATA_FREE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
ORDER BY DATA_LENGTH DESC
LIMIT 20;
DATA_FREE 表示表空间内已分配但未使用的空间,该值持续偏大通常意味着存在可回收碎片。
3.3.2 表空间文件大小与膨胀的观察
# 查看数据目录下各类文件的占用
du -sh /var/lib/mysql
ls -lh /var/lib/mysql/*.ibd | sort -k5 -h | tail -20
ls -lh /var/lib/mysql/undo_00*
ls -lh /var/lib/mysql/ibtmp1
观察要点:单个 .ibd 文件与表中实际行数、information_schema.TABLES 的记录不对应时,说明存在碎片或未回收空间。
3.3.3 undo 表空间管理
- 8.0 起 undo 表空间数量固定为 2,由
innodb_undo_directory指定位置 innodb_undo_log_truncate控制是否允许在 undo 文件超过innodb_max_undo_log_size后截断回收- 截断能生效的前提是 purge 能够推进,因此长事务是 undo 膨胀的首要原因
- 定位长事务
SELECT trx_id, trx_state, trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS seconds_open,
trx_rows_modified, trx_mysql_thread_id
FROM information_schema.INNODB_TRX
ORDER BY trx_started;
3.3.4 碎片整理注意事项
- 可用手段:
OPTIMIZE TABLE(对 InnoDB 等价于重建表并回收空间)、ALTER TABLE ... ENGINE=InnoDB重建表 - 执行代价:重建期间需要额外占用与表等量的磁盘空间,磁盘余量不足会导致失败
- 锁与流量影响:重建过程会持有较长时间的表锁,并产生大量 redo 与 binlog,对复制延迟与磁盘 IO 有明显冲击
- 前置检查:确认磁盘余量、复制延迟水位、业务低峰窗口
- 替代做法:在允许业务侧改造时,用
pt-online-schema-change一类工具进行在线重建,工具的机制、关键参数与风险见 mysql: 表结构变更与运维治理
-- 查看碎片程度,评估是否需要整理
SELECT TABLE_NAME, DATA_LENGTH, DATA_FREE,
ROUND(DATA_FREE / (DATA_LENGTH + INDEX_LENGTH + 1) * 100, 2) AS free_pct
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'app'
ORDER BY free_pct DESC;
3.4 观察手段
3.4.1 状态变量的判读
-- 缓冲池命中率:读请求中未命中并触发磁盘读的比例
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';
-- 脏页与空闲页
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_free';
SHOW STATUS LIKE 'Innodb_buffer_pool_wait_free';
-- redo 写入与等待
SHOW STATUS LIKE 'Innodb_os_log_written';
SHOW STATUS LIKE 'Innodb_log_waits';
-- 双写页数与次数
SHOW STATUS LIKE 'Innodb_dblwr_pages_written';
SHOW STATUS LIKE 'Innodb_dblwr_writes';
-- 刷盘次数
SHOW STATUS LIKE 'Innodb_data_fsyncs';
判读要点:
| 指标 | 含义 | 异常信号 |
|---|---|---|
Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests | 未命中比例 | 比例持续偏高说明工作集超出缓冲池 |
Innodb_buffer_pool_pages_dirty 占比 | 脏页占缓冲池比例 | 长期接近上限说明刷脏跟不上写入 |
Innodb_buffer_pool_wait_free | 等待空闲页的次数 | 增长说明缓冲池不足或刷脏滞后 |
Innodb_log_waits | 因日志缓冲空间不足而等待的次数 | 增长说明 Log Buffer 偏小或刷盘受阻 |
Innodb_dblwr_pages_written 与 Innodb_dblwr_writes 之比 | 每次双写批次中的页数 | 接近 1 说明双写批次未合并,写入放大明显 |
3.4.2 SHOW ENGINE INNODB STATUS
该命令输出完整的引擎内部状态,其中与持久化相关的段落为:
- BUFFER POOL AND MEMORY:缓冲池总页数、空闲页、脏页、命中率、页读写与刷脏统计
- LOG:LSN、已刷盘 LSN、最近 checkpoint 位置、等待刷新的页数
- INSERT BUFFER AND ADAPTIVE HASH INDEX:Change Buffer 的合并次数与命中情况
- PENDING WRITES 或相关段落:待写入与待刷脏的页数
对比 LSN 与最近 checkpoint 的差值可以判断 redo 可覆盖空间的余量;等待刷新的页数持续偏大说明刷脏能力不足。
3.4.3 错误日志中的相关告警
日志中需要重点识别的信息:
- 页校验失败、页号异常、LSN 超前于当前页的提示
- 双写区恢复相关的提示
- 表空间文件缺失、无法打开、大小不符合预期的提示
- redo 日志空间不足或刷脏滞后的提示
- 恢复阶段输出的前滚与回滚进度
# 检索与存储引擎相关的告警
grep -Ei 'innodb|corrupt|doublewrite|page|tablespace' /var/log/mysql/error.log
3.4.4 与主机 IO 指标的对照
数据库内部指标只能说明引擎侧的行为,需要与主机侧的设备指标对照才能判断瓶颈位置:
# 查看设备的利用率、平均等待与每秒写入次数
iostat -x 1
# 关注 %util、await、w/s、avgrq-sz
# 查看数据目录所在文件系统的空间与挂载参数
df -h /var/lib/mysql
findmnt /var/lib/mysql
对照方式:若引擎侧脏页比例高、Innodb_data_fsyncs 增长快,同时主机侧设备利用率接近饱和,说明瓶颈在设备写入能力;若设备利用率不高而引擎侧仍有大量等待,则更可能是刷脏参数或 redo 容量设置不当。文件系统挂载选项与磁盘设备信息的相关命令见 linux: 磁盘存储与文件系统操作命令。
3.5 常见问题与处置
3.5.1 磁盘 IO 持续偏高
- 先区分读 IO 与写 IO 的构成:读偏高通常是工作集超出缓冲池,写偏高通常是刷脏与日志写入
- 读偏高的处置方向:扩大缓冲池、检查是否存在全表扫描或缺失索引的查询
- 写偏高的处置方向:确认是否存在批量导入、大批量删除等写入突发;核对刷脏参数的 IO 预算是否与设备能力匹配
- 若双写页数与双写次数之比接近 1,说明双写未有效合并批次,可结合版本特性调整相关参数
3.5.2 刷脏导致周期性抖动
- 现象为延迟指标呈周期性尖峰,尖峰间隔与刷脏周期相关
- 处置方向:提高刷脏线程数、降低脏页阈值使刷脏更均匀、确认相邻页刷新在固态盘上已关闭
- 若抖动与 checkpoint 推进同步,说明 redo 容量偏小导致刷脏被强制加速
3.5.3 表空间文件异常增大
- 确认是单表文件增大、undo 文件增大还是系统表空间增大
- 单表文件增大:检查
DATA_FREE与删除/更新模式,按第 3.3.4 节评估是否重建 - undo 文件增大:按第 3.3.3 节定位长事务
- 系统表空间增大:旧版本中双写区与回滚段位于系统表空间,属于结构性占用
高危:直接删除或移动 ibdata1、.ibd、undo_00*、ibtmp1 等表空间文件会导致实例无法启动或数据永久丢失。
- 禁止在实例运行期间删除上述文件
- 确需处理表空间时使用 SQL 接口:
DROP TABLE、DROP TABLESPACE,由引擎完成元数据与文件的同步清理 - 处理前必须完成全量备份并验证备份可恢复,备份与恢复流程见 mysql: 使用命令
- 误删后的兜底方式是使用备份重建实例,再通过 binlog 做时间点恢复,binlog 与复制链路见 mysql: 主从同步原理
3.5.4 崩溃后恢复缓慢
- 恢复耗时主要由 checkpoint 之后的 redo 量与未提交事务规模决定
- 处置方向:降低单事务修改量、缩短事务持有时间、避免大批量数据一次性导入
- 恢复期间不要强制中断进程,中断后重启会重新执行恢复
- 恢复期间应避免下发新连接与变更操作,等待错误日志中出现恢复完成提示
3.5.5 双写与文件系统特性冲突
- 部分文件系统或卷管理层已提供原子写或写时复制语义,此时双写的额外开销可以省去
- 部分存储设备具备掉电保护,同样满足关闭双写的前提
- 判断依据应来自存储层的能力说明与实测验证,而非默认假设
高危:关闭双写(innodb_doublewrite=OFF)会使部分页写入失去保护,掉电后可能产生无法通过 redo 恢复的损坏页。
- 关闭前必须确认底层存储具备整页原子写能力或掉电保护
- 变更前完成全量备份
- 变更仅通过配置文件修改并在重启后生效,属于需要重启的操作,重启流程见 mysql: 服务管理
- 兜底方式:出现页损坏时借助备份与 binlog 恢复;恢复失败前的数据导出手段属于最后手段,操作前应先完整备份数据目录
3.5.6 持久化参数调整与强制重启
高危:将 innodb_flush_log_at_trx_commit 由 1 调整为 0 或 2,以及将 sync_binlog 由 1 调整为更大的值,都会扩大崩溃时的数据丢失窗口。
- 变更前需由业务侧确认可接受的丢失窗口
- 变更后应记录变更原因与时间,便于事后核对数据完整性
- 回滚方式为将参数改回原值并重启实例;对于
innodb_flush_log_at_trx_commit,回滚前应先确认此前是否存在未刷盘的已提交事务 - 在开启 binlog 与复制的实例上,该参数与
sync_binlog应保持一致的持久化级别,避免恢复时出现 redo 与 binlog 判断结果不一致
高危:使用 kill -9 强制终止 mysqld、直接断电或强制中断恢复过程,等同于制造一次崩溃,可能触发长时间恢复,并可能造成未刷盘事务丢失。
- 正常停止使用
systemctl stop mysql或mysqladmin shutdown,使实例完成刷盘与清理 - 仅在实例无响应且已确认数据已备份的情况下才考虑强制终止
- 强制终止后的兜底方式是等待崩溃恢复完成,并核对错误日志中的恢复结果
高危:使用 innodb_force_recovery 启动实例属于只读排障手段,取值过大时可能跳过必要的恢复步骤,造成数据被进一步破坏或某些操作报错后数据被写入。
- 应从较小的取值开始逐级尝试,每级都先导出可读数据
- 排障完成后必须移除该参数并正常重启
- 恢复前的完整数据目录备份是必须的前置动作
# 排障用配置示例,仅在确认需要时临时启用
[mysqld]
innodb_force_recovery = 1
# 排障结束后必须删除该行并正常重启
四、总结
- InnoDB 存在的理由是在内存与磁盘的速度差距上做取舍:用内存缓存换取读性能,用日志的顺序写换取数据页随机写的持久化
- 内存层由 Buffer Pool、Change Buffer、Log Buffer 与自适应哈希索引构成,其中 Buffer Pool 决定读性能上限,LRU 分代与预读策略决定缓存是否被一次性访问污染
- 磁盘层以表空间组织数据,自下而上为页、区、段、表空间;表数据即索引数据,聚簇索引叶子存放整行,二级索引叶子存放主键值,行格式与溢出页决定单页能容纳的记录数
- redo 以循环写的方式记录页的物理修改,把随机写转换为顺序写;undo 记录逻辑反向操作,同时支撑回滚与多版本一致性读;purge 负责清理不再被需要的版本,长事务是 purge 与 checkpoint 推进受阻的共同原因
- 双写缓冲解决的是页写入不完整问题,redo 解决的是已提交修改未落盘问题,两者职责不同且不可互相替代
- 持久化强度由
innodb_flush_log_at_trx_commit与sync_binlog共同决定,二者的刷盘动作位于同一事务的两阶段提交流程中,取值应与业务可接受的丢失窗口匹配 innodb_flush_method决定数据是否经过操作系统页缓存,O_DIRECT的取舍在于避免双重缓存并让刷盘时机可控,其原理与 fsync 语义见页缓存专题- 崩溃恢复由 redo 前滚与 undo 回滚组成,恢复耗时取决于 checkpoint 之后的日志量与未提交事务规模,控制事务规模比调整日志参数更有效
- InnoDB 的代价集中在三处:工作集超出缓冲池导致命中率下滑、内存不足导致刷脏与换页并发恶化、表空间因删除与碎片只增不减
- 日常观察以缓冲池命中率、脏页比例、redo 写入与双写页数为核心指标,并与主机侧设备利用率对照,才能定位瓶颈在引擎侧还是设备侧