Chapter 2: InnoDB 存储引擎内核深度解析

难度：高级 阅读时间 ~60 min 基于 MySQL 8.0 / 8.4

InnoDB 是 MySQL 默认也是最重要的存储引擎。本章从架构全景到 Buffer Pool 页结构、Redo Log WAL 机制、Undo Log 与 MVCC 版本链、Doublewrite Buffer 防止半写、Change Buffer 优化随机 I/O、自适应哈希索引、表空间布局、行格式对比，一直到刷盘策略和生产监控，进行源码级深度解析。每个知识点都配有 ASCII 架构图、MySQL 源码文件引用、关键参数调优建议和生产事故案例。

1. InnoDB 架构总览

1.1 架构全景图

InnoDB 的内部架构可以分为内存结构和磁盘结构两大部分。下面的 ASCII 图展示了所有核心组件及其数据流向：

┌─────────────────────────────────────────────────────────────────────────┐ │ InnoDB In-Memory Structures │ │ ┌──────────────────────────────────────────────────────────────────┐ │ │ │ Buffer Pool (default 128MB) │ │ │ │ ┌────────────┐ ┌────────────┐ ┌──────────┐ ┌────────────┐ │ │ │ │ │ Data Pages │ │Index Pages │ │Undo Pages│ │ Change Buf │ │ │ │ │ │ (16KB ea.) │ │ (16KB) │ │ (16KB) │ │ Pages │ │ │ │ │ └────────────┘ └────────────┘ └──────────┘ └────────────┘ │ │ │ │ ┌─────────────────────────────────────────────────────────┐ │ │ │ │ │ LRU List: [Young Sublist 5/8] ←→ [Old Sublist 3/8] │ │ │ │ │ └─────────────────────────────────────────────────────────┘ │ │ │ │ ┌──────────────────┐ ┌───────────────┐ │ │ │ │ │ Free List │ │ Flush List │ │ │ │ │ └──────────────────┘ └───────────────┘ │ │ │ └──────────────────────────────────────────────────────────────────┘ │ │ │ │ ┌────────────────┐ ┌──────────────────┐ ┌──────────────────────┐ │ │ │ Adaptive Hash │ │ Log Buffer │ │ Change Buffer │ │ │ │ Index (AHI) │ │ (64MB default) │ │ (25% of pool max) │ │ │ └────────────────┘ └──────────────────┘ └──────────────────────┘ │ └───────────┬──────────────────┬───────────────────┬───────────────────────┘ │ │ │ │ Checkpoint │ WAL Write │ Merge on read ▼ ▼ ▼ ┌─────────────────────────────────────────────────────────────────────────┐ │ InnoDB On-Disk Structures │ │ │ │ ┌──────────────┐ ┌────────────────┐ ┌──────────────────────────┐ │ │ │ System │ │ Redo Log │ │ Undo Tablespaces │ │ │ │ Tablespace │ │ ib_logfile0 │ │ undo_001, undo_002 │ │ │ │ (ibdata1) │ │ ib_logfile1 │ │ │ │ │ └──────────────┘ │ (or #innodb_ │ └──────────────────────────┘ │ │ │ redo/ 8.0.30+)│ │ │ ┌──────────────┐ └────────────────┘ ┌──────────────────────────┐ │ │ │ File-per- │ │ Doublewrite Buffer │ │ │ │ table .ibd │ ┌────────────────┐ │ (ibdata1 or separate │ │ │ │ files │ │ Temp Tablespace│ │ .dblwr files 8.0.20+) │ │ │ └──────────────┘ │ (ibtmp1) │ └──────────────────────────┘ │ │ └────────────────┘ │ │ ┌──────────────────────────────────────────────────────────────────┐ │ │ │ General Tablespaces (CREATE TABLESPACE ... ) │ │ │ └──────────────────────────────────────────────────────────────────┘ │ └─────────────────────────────────────────────────────────────────────────┘

源码参考: storage/innobase/include/buf0buf.h, storage/innobase/srv/srv0srv.cc

1.2 查询数据流

当一条 SELECT 到达 InnoDB 时，数据流如下：

Client SQL │ ▼ MySQL Server Layer (parser → optimizer → executor) │ ▼ handler::ha_read_row() InnoDB Handler Interface │ ├──▶ Check Adaptive Hash Index ──▶ HIT? → return row │ MISS ↓ ├──▶ Check Buffer Pool LRU ──────▶ HIT? → return page │ MISS ↓ ├──▶ Read page from .ibd file (OS read / AIO) │ └──▶ Place page into Buffer Pool (Old sublist head) │ └──▶ If accessed again after innodb_old_blocks_time → move to Young │ ▼ Return row to Server Layer → Client

1.3 写入数据流

当一条 INSERT / UPDATE / DELETE 执行时，InnoDB 的写入路径比读取复杂得多：

1. BEGIN (implicitly or explicitly) 2. Lock the target row(s) → record lock / gap lock / next-key lock 3. Write old row image to Undo Log (for rollback + MVCC) 4. Modify the page IN the Buffer Pool (in-memory only) 5. Write redo log record to Log Buffer 6. COMMIT: a. Flush Log Buffer → Redo Log files on disk (fsync per innodb_flush_log_at_trx_commit) b. Mark transaction committed in redo c. The dirty page stays in Buffer Pool → will be flushed later 7. Background threads (page cleaner) eventually: a. Write dirty page to Doublewrite Buffer (on disk) b. fsync Doublewrite Buffer c. Write page to actual .ibd file location d. fsync .ibd e. Advance checkpoint LSN
关键洞察: InnoDB 的性能秘诀在于将随机写转化为顺序写（Redo Log 是顺序追加的），并将实际的脏页刷盘推迟到后台完成。这就是 WAL（Write-Ahead Logging）的核心价值——事务提交只需等待 Redo Log 的顺序 fsync，而不是等待所有脏页的随机 I/O。

1.4 内存结构 vs 磁盘结构对照

组件 位置 作用 关键参数
Buffer Pool 内存 缓存数据页和索引页，减少磁盘 I/O innodb_buffer_pool_size
Log Buffer 内存 暂存 Redo Log 记录，批量刷盘 innodb_log_buffer_size
Adaptive Hash Index 内存 自动在热点 B+Tree 页上构建哈希索引 innodb_adaptive_hash_index
Change Buffer 内存 + 磁盘 缓存二级索引变更，延迟合并 innodb_change_buffer_max_size
Redo Log 磁盘 崩溃恢复，保证事务持久性 (D in ACID) innodb_redo_log_capacity
Undo Log 磁盘（+缓存在 Buffer Pool） 事务回滚 + MVCC 多版本读 innodb_undo_tablespaces
Doublewrite Buffer 磁盘 防止部分页写入（torn page） innodb_doublewrite
System Tablespace 磁盘 数据字典、Change Buffer 持久化 innodb_data_file_path

2. Buffer Pool 深入解析

Buffer Pool 是 InnoDB 最核心的内存结构。理解它的工作机制是理解 InnoDB 性能的基础。

2.1 页结构详解（16KB）

InnoDB 中所有数据都存储在 16KB 大小的页（page）中。每个页的内部结构如下：

┌─────────────────────────────────────────────────────────────┐ │ InnoDB Page (16384 bytes) │ ├─────────────────────────────────────────────────────────────┤ │ File Header (38 bytes) │ │ ├─ FIL_PAGE_SPACE_OR_CHKSUM (4) checksum │ │ ├─ FIL_PAGE_OFFSET (4) page number │ │ ├─ FIL_PAGE_PREV (4) previous page (B+Tree)│ │ ├─ FIL_PAGE_NEXT (4) next page (B+Tree) │ │ ├─ FIL_PAGE_LSN (8) last modified LSN │ │ ├─ FIL_PAGE_TYPE (2) page type │ │ ├─ FIL_PAGE_FILE_FLUSH_LSN (8) flush LSN (space 0) │ │ └─ FIL_PAGE_SPACE_ID (4) tablespace ID │ ├─────────────────────────────────────────────────────────────┤ │ Page Header (56 bytes) │ │ ├─ PAGE_N_DIR_SLOTS (2) slots in page dir │ │ ├─ PAGE_HEAP_TOP (2) first free byte │ │ ├─ PAGE_N_HEAP (2) number of records │ │ ├─ PAGE_FREE (2) free record list head │ │ ├─ PAGE_GARBAGE (2) deleted bytes │ │ ├─ PAGE_LAST_INSERT (2) last insert position │ │ ├─ PAGE_DIRECTION (2) insert direction │ │ ├─ PAGE_N_DIRECTION (2) consecutive inserts │ │ ├─ PAGE_N_RECS (2) user record count │ │ ├─ PAGE_MAX_TRX_ID (8) max txn ID (sec idx) │ │ ├─ PAGE_LEVEL (2) B+Tree level (0=leaf) │ │ ├─ PAGE_INDEX_ID (8) index this page for │ │ └─ ... (segment headers) │ ├─────────────────────────────────────────────────────────────┤ │ Infimum Record (13 bytes) │ │ Supremum Record (13 bytes) │ ├─────────────────────────────────────────────────────────────┤ │ │ │ User Records (grows downward ↓) │ │ │ │ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐ │ │ │ Record 1│→│ Record 2│→│ Record 3│→│ Record n│→Supremum │ │ └─────────┘ └─────────┘ └─────────┘ └─────────┘ │ │ │ │ Free Space (grows toward each other) │ │ │ │ Page Directory (grows upward ↑) │ │ ┌──────┬──────┬──────┬──────┬──────┐ │ │ │Slot 0│Slot 1│Slot 2│ ... │Slot n│ (2 bytes each) │ │ └──────┴──────┴──────┴──────┴──────┘ │ ├─────────────────────────────────────────────────────────────┤ │ File Trailer (8 bytes) │ │ ├─ Checksum (4) matches header │ │ └─ FIL_PAGE_LSN low 4 bytes (4) matches header LSN │ └─────────────────────────────────────────────────────────────┘

源码参考: storage/innobase/include/fil0types.h, storage/innobase/include/page0page.h

为什么是 16KB？ 16KB 是 InnoDB 默认的页大小，由 innodb_page_size 控制（可选 4K/8K/16K/32K/64K，但只能在初始化时设定）。16KB 是一个经过深思熟虑的折中：太小会导致 B+Tree 层数增加（更多磁盘寻道），太大会浪费内存和增加页内碎片。对于大多数 OLTP 工作负载，16KB 是最优选择。

2.2 LRU 算法：双链表设计

InnoDB 的 Buffer Pool 并不使用标准的 LRU（Least Recently Used）算法，而是采用了改良的两段式 LRU，以防止全表扫描等操作冲刷掉热数据。

Buffer Pool LRU List ┌─────────────────────────────────────────────────────────────────────┐ │ │ │ HEAD (MRU) TAIL (LRU) │ │ ◄────────── Young Sublist (5/8) ──────────►│◄── Old (3/8) ──►│ │ │ │ │ │ [HOT] [HOT] [HOT] ... [WARM] [WARM] │ [OLD] [OLD] [OLD] │ │ ▲ │ ▲ │ │ │ Page accessed again │ │ New page from │ │ │ (after old_blocks_time) │ │ disk lands HERE │ │ └──────────────────────────────────────────┘ │ ("midpoint │ │ Promotion │ insertion") │ │ │ │ │ Eviction from tail ──►OUT │ └─────────────────────────────────────────────────────────────────────┘

为什么要两段式？

考虑一个典型的生产场景：你的 Buffer Pool 里缓存了过去几小时的热点数据（用户表、订单表的索引页）。突然有人执行了一个 SELECT * FROM large_table（全表扫描），这会读取数十万个数据页。如果使用标准 LRU，这些只会被读一次就不再访问的 "冷" 页会把所有真正的 "热" 页从 LRU 中挤出，导致之后的正常查询全部命中磁盘——这就是所谓的 "Buffer Pool 被冲刷"。

InnoDB 的解决方案：

  1. 新页从磁盘读入时，插入到 Old 子链表的头部（midpoint insertion），而不是整个 LRU 的头部。
  2. 只有当该页在 Old 子链表中停留超过 innodb_old_blocks_time（默认 1000ms）后再次被访问，才会被提升到 Young 子链表。
  3. 全表扫描的页通常在 1000ms 内就被读完丢弃，永远不会进入 Young 子链表，从而保护了热数据。

关键参数

参数 默认值 说明
innodb_old_blocks_pct 37 Old 子链表占 Buffer Pool 的百分比。默认 37%（约 3/8）。取值范围 5-95。
innodb_old_blocks_time 1000 页在 Old 子链表中需要停留多少毫秒后才能被提升到 Young 子链表。设为 0 则禁用此保护。

2.3 Buffer Pool 实例

当 Buffer Pool 很大时（生产环境通常 32GB-128GB），单个 Buffer Pool 上的 mutex 会成为并发瓶颈。InnoDB 允许将 Buffer Pool 分成多个独立实例，每个实例有自己的 LRU list、Free list、Flush list 和 mutex。

参数 默认值 推荐值
innodb_buffer_pool_instances 8（当 pool >= 1GB 时） 每个实例至少 1GB。128GB pool → 16 个实例。
innodb_buffer_pool_chunk_size 128MB 在线调整 pool size 的单位。pool_size 应该是 chunk_size * instances 的整数倍。
注意 MySQL 8.0 变化: 在 MySQL 8.0.26+ 中，当 innodb_buffer_pool_size < 1GB 时，innodb_buffer_pool_instances 被强制设为 1，不管你设什么值。这是因为小 Buffer Pool 没有 mutex 争用的问题。

2.4 Buffer Pool 预热与 Dump/Load

MySQL 重启后 Buffer Pool 是空的（冷启动），查询性能会在 30-60 分钟内逐渐恢复。InnoDB 提供了自动 dump/load 机制来加速预热：

-- Enable automatic dump on shutdown and load on startup SET GLOBAL innodb_buffer_pool_dump_at_shutdown = ON; SET GLOBAL innodb_buffer_pool_load_at_startup = ON; -- Manual dump (writes to ib_buffer_pool file) SET GLOBAL innodb_buffer_pool_dump_now = ON; -- Check dump/load progress SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status'; SHOW STATUS LIKE 'Innodb_buffer_pool_load_status'; -- Dump file content example (tablespace_id:page_number) -- 0,7 -- 0,8 -- 12,0 -- 12,1

dump 文件只保存页的 (tablespace_id, page_number) 对，不保存实际数据。启动时按这个列表去磁盘预读即可。这也是为什么即使 128GB Buffer Pool 的 dump 文件通常也只有几 MB。

2.5 监控 Buffer Pool

-- Key metrics from SHOW ENGINE INNODB STATUS (BUFFER POOL AND MEMORY section) ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 137363456 -- Total memory allocated to Buffer Pool Dictionary memory allocated 622013 -- Memory for data dictionary Buffer pool size 8192 -- Total pages (8192 * 16KB = 128MB) Free buffers 1024 -- Free pages not yet used Database pages 7137 -- Pages containing data Old database pages 2614 -- Pages in Old sublist Modified db pages 123 -- Dirty pages awaiting flush Pending reads 0 -- Pages waiting to be read from disk Pending writes: LRU 0, flush list 0, single page 0 Pages made young 18623, not young 2045921 -- Promotions vs blocks 3.47 youngs/s, 412.05 non-youngs/s -- Rate of promotions Pages read 16842, created 5765, written 29412 0.00 reads/s, 0.24 creates/s, 1.86 writes/s Buffer pool hit rate 1000 / 1000 -- 1000/1000 = 100% hit rate (ideal) Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
生产关注指标:
1. Buffer pool hit rate：应保持在 999/1000 以上。低于此值说明 Buffer Pool 太小或有大查询冲刷。
2. Free buffers：如果长期为 0，说明 Buffer Pool 已满，所有新页都必须先淘汰旧页。
3. Modified db pages：如果持续增长，说明刷盘速度跟不上写入速度。
4. Pages made young / not young：not young 远大于 young 说明 innodb_old_blocks_time 保护在有效工作。

源码参考: storage/innobase/buf/buf0buf.cc — buf_page_get_gen(), buf_page_make_young()

2.6 Buffer Pool Size 计算器

2.7 Buffer Pool 内部链表详解

Buffer Pool 内部维护三个关键链表，理解它们对于排查性能问题至关重要：

Buffer Pool Internal Lists: ┌─────────────────────────────────────────────────────────────────┐ │ 1. FREE LIST │ │ Contains pages that are completely free (never used or │ │ evicted). When InnoDB needs a new page, it takes one from │ │ here first. When this is empty, LRU eviction kicks in. │ │ ┌────┐ ┌────┐ ┌────┐ ┌────┐ │ │ │Free│→ │Free│→ │Free│→ │Free│→ NULL │ │ └────┘ └────┘ └────┘ └────┘ │ │ │ │ 2. LRU LIST │ │ Contains all pages currently in use (clean + dirty). │ │ Organized as Young (5/8) + Old (3/8). │ │ Used for page eviction when FREE list is empty. │ │ ┌────┐ ┌────┐ ┌────┐ ┌────┐ ┌────┐ │ │ │Hot │→ │Hot │ →→ │Old │→ │Old │→ │Old │→ evict │ │ └────┘ └────┘ └────┘ └────┘ └────┘ │ │ Young (MRU) ──────│─────── Old (LRU) ──────► │ │ │ │ 3. FLUSH LIST │ │ Contains only DIRTY pages, ordered by oldest_modification │ │ LSN (oldest first). Page cleaner flushes from here to │ │ advance the checkpoint. │ │ ┌────┐ ┌────┐ ┌────┐ ┌────┐ │ │ │LSN │→ │LSN │→ │LSN │→ │LSN │→ NULL │ │ │100 │ │200 │ │300 │ │400 │ │ │ └────┘ └────┘ └────┘ └────┘ │ │ oldest ────────────────────────► newest │ │ │ │ A dirty page appears in BOTH the LRU list AND the Flush list. │ │ A clean page appears ONLY in the LRU list. │ └─────────────────────────────────────────────────────────────────┘

2.8 页类型 (Page Types)

Buffer Pool 中缓存的页不仅仅是数据页。InnoDB 定义了多种页类型：

FIL_PAGE_TYPE 值 说明
FIL_PAGE_INDEX0x45BFB+Tree 叶子节点（存储行数据或二级索引叶子）
FIL_PAGE_RTREE0x45BER-Tree 索引页（空间索引）
FIL_PAGE_SDI0x45BD序列化字典信息页（MySQL 8.0）
FIL_PAGE_UNDO_LOG0x0002Undo Log 页
FIL_PAGE_INODE0x0003段（Segment）信息节点页
FIL_PAGE_IBUF_FREE_LIST0x0004Change Buffer 空闲列表页
FIL_PAGE_TYPE_ALLOCATED0x0000已分配但未使用的页
FIL_PAGE_TYPE_SYS0x0006系统页（如事务系统页）
FIL_PAGE_TYPE_TRX_SYS0x0007事务系统数据页
FIL_PAGE_TYPE_FSP_HDR0x0008表空间头（File Space Header）页
FIL_PAGE_TYPE_XDES0x0009区描述（Extent Descriptor）页
FIL_PAGE_TYPE_BLOB0x000A溢出页（存储大字段）

源码参考: storage/innobase/include/fil0fil.h — enum page_type_t

2.9 Buffer Pool 预读 (Read-Ahead)

InnoDB 有两种预读机制来减少 I/O 等待时间：

线性预读 (Linear Read-Ahead)

当一个 extent（64 个连续页 = 1MB）中被顺序访问的页数超过 innodb_read_ahead_threshold（默认 56）时，InnoDB 异步预读下一个 extent 的所有页。适用于全表扫描等顺序访问模式。

随机预读 (Random Read-Ahead)

当一个 extent 中有 13 个以上的页在 Buffer Pool 的 Young 子链表中时，InnoDB 预读该 extent 剩余的页。默认关闭（innodb_random_read_ahead=OFF），因为在大多数工作负载中效果不明显。

-- Monitor read-ahead effectiveness: SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_ahead'; -- Pages pre-read SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_ahead_evicted'; -- Pre-read but never accessed -- If read_ahead_evicted / read_ahead is high (>50%), -- read-ahead is wasting I/O and should be tuned down. -- Increase innodb_read_ahead_threshold (up to 63) to be more conservative.

2.10 Buffer Pool mutex 与 latch 争用排查

在高并发场景下（>1000 QPS），Buffer Pool 的内部 mutex 可能成为瓶颈。主要的 mutex 包括：

Mutex / Latch 保护的资源 争用表现 解决方案
buf_pool->mutex Buffer Pool 实例的全局锁 高并发读写时的广泛争用 增加 innodb_buffer_pool_instances
buf_pool->LRU_list_mutex LRU list 页面淘汰/提升时的争用 增加 instances + 优化查询减少页面淘汰
buf_pool->flush_list_mutex Flush list 高写入负载时的争用 增加 instances + page_cleaners
hash_lock (page hash) 页哈希表（查找页是否在 pool 中） 极高并发点查时的争用 MySQL 8.0 已分区化，通常无需干预
-- Diagnose Buffer Pool mutex waits: SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1000000000 AS total_wait_ms, AVG_TIMER_WAIT/1000000 AS avg_wait_us FROM performance_schema.events_waits_summary_global_by_event_name WHERE EVENT_NAME LIKE 'wait/synch/%/innodb/buf_pool%' OR EVENT_NAME LIKE 'wait/synch/%/innodb/hash_table%' ORDER BY SUM_TIMER_WAIT DESC;

3. Redo Log (WAL) 机制

3.1 Write-Ahead Logging 原理

WAL（Write-Ahead Logging）是 InnoDB 实现事务持久性（ACID 中的 D）的核心机制。原理很简单：在修改数据页之前，先把修改操作记录到 Redo Log 中。如果系统崩溃，可以通过重放 Redo Log 恢复所有已提交事务的修改。

WAL Write Flow: Transaction: UPDATE users SET age=30 WHERE id=1 Step 1: Write redo record to Log Buffer (in memory) ┌──────────────────────────────────┐ │ Log Buffer (innodb_log_buffer_size = 64MB) │ │ ┌──────────────────────────────┐│ │ │ [type=MLOG_REC_UPDATE_IN_PLACE] ││ │ │ [space=12, page=45, offset=200] ││ │ │ [old: age=25 → new: age=30] ││ │ │ [LSN: 1234567890] ││ │ └──────────────────────────────┘│ └──────────────────────────────────┘ Step 2: Modify page in Buffer Pool (in memory, page becomes "dirty") Step 3: On COMMIT → flush Log Buffer to disk redo log files ┌──────────────┐ fsync ┌──────────────────┐ │ Log Buffer │ ──────────▶ │ ib_logfile0/1 │ │ (memory) │ │ (disk, sequential)│ └──────────────┘ └──────────────────┘ Step 4: Dirty page will be flushed to .ibd later (async, background)

3.2 Redo Log 文件结构

在 MySQL 8.0.30 之前，Redo Log 由固定数量的文件组成（默认 2 个），以循环方式写入：

Pre-8.0.30: Fixed redo log files ┌────────────────────────────────────────────────────────┐ │ ib_logfile0 ib_logfile1 │ │ ┌──────────────┐ ┌──────────────┐ │ │ │ ████████░░░░ │ ───▶ │ ░░░░░░░░░░░░ │ ───┐ │ │ └──────────────┘ └──────────────┘ │ │ │ ▲ │ │ │ └──────────────── circular ────────────────┘ │ │ │ │ ████ = written redo records │ │ ░░░░ = available space │ │ │ │ write_lsn ──▶ current write position │ │ checkpoint_lsn ──▶ all pages before this are on disk │ │ Space between checkpoint_lsn and write_lsn = "active" │ └────────────────────────────────────────────────────────┘ Post-8.0.30: Dynamic redo log (in #innodb_redo/ directory) ┌────────────────────────────────────────────────────────┐ │ #innodb_redo/ │ │ ├── #ib_redo0 (active, being written) │ │ ├── #ib_redo1 (active, being written) │ │ ├── #ib_redo2_tmp (spare, pre-allocated) │ │ └── #ib_redo3_tmp (spare, pre-allocated) │ │ │ │ Controlled by: innodb_redo_log_capacity (default 100MB)│ │ Files auto-created/removed as needed │ └────────────────────────────────────────────────────────┘

3.3 LSN（Log Sequence Number）

LSN 是 InnoDB 中最重要的内部概念之一。它是一个单调递增的 64 位整数，代表已写入 Redo Log 的总字节数（从 InnoDB 初始化开始计算）。

LSN 类型 含义 来源
Log sequence number 当前最新 LSN（写入 Log Buffer 的位置） SHOW ENGINE INNODB STATUS
Log flushed up to 已刷到磁盘 redo log 文件的 LSN SHOW ENGINE INNODB STATUS
Pages flushed up to 脏页已刷盘到此 LSN（flush list 中最老脏页的 LSN） SHOW ENGINE INNODB STATUS
Last checkpoint at 上次 checkpoint 的 LSN。崩溃恢复从此处开始重放。 SHOW ENGINE INNODB STATUS
LSN relationships (must always hold): Last checkpoint at ≤ Pages flushed up to ≤ Log flushed up to ≤ Log sequence number Example from SHOW ENGINE INNODB STATUS: --- LOG --- Log sequence number 3456789012 Log buffer assigned up to 3456789012 Log buffer completed up to 3456789012 Log written up to 3456789012 Log flushed up to 3456789012 Added dirty pages up to 3456789012 Pages flushed up to 3456780000 Last checkpoint at 3456780000 ▲ Gap = 9012 bytes of redo not yet checkpointed Crash recovery would need to replay these 9012 bytes

3.4 Checkpoint 机制

Checkpoint 是将 Buffer Pool 中的脏页刷到磁盘，并推进 checkpoint LSN 的过程。它的目的有两个：

  1. 释放 redo log 空间：checkpoint LSN 之前的 redo log 空间可以被复用
  2. 缩短崩溃恢复时间：恢复只需重放 checkpoint LSN 之后的 redo
Sharp Checkpoint

将所有脏页一次性刷盘。只在数据库关闭时使用（innodb_fast_shutdown=0）。因为刷盘期间会阻塞所有操作，生产环境绝不应在运行时使用。

Fuzzy Checkpoint

分批、异步地刷脏页。InnoDB 运行时使用的都是 Fuzzy Checkpoint，包括 LRU 刷盘、Flush List 刷盘和 Async/Sync 刷盘（当 redo log 快满时）。

3.5 innodb_flush_log_at_trx_commit：持久性 vs 性能

这可能是 InnoDB 最重要的参数之一。它控制事务提交时 redo log 的刷盘策略：

┌─────────┬──────────────────────────────────────────────────────────────┐ │ Value │ Behavior │ ├─────────┼──────────────────────────────────────────────────────────────┤ │ │ COMMIT → write to Log Buffer │ │ 0 │ Background thread flushes to OS cache + fsync every 1 sec │ │ │ ⚠ Up to 1 second of committed transactions lost on crash │ │ │ ★ Fastest — no sync on commit │ ├─────────┼──────────────────────────────────────────────────────────────┤ │ │ COMMIT → write to OS cache → fsync to disk │ │ 1 │ Every single commit triggers an fsync │ │ │ ✓ ZERO data loss on crash (ACID compliant) │ │ │ ★ Slowest — fsync on every commit │ ├─────────┼──────────────────────────────────────────────────────────────┤ │ │ COMMIT → write to OS cache (no fsync) │ │ 2 │ Background thread fsyncs every 1 second │ │ │ ⚠ Data safe on MySQL crash, lost on OS/power crash │ │ │ ★ Medium — good for replicas │ └─────────┴──────────────────────────────────────────────────────────────┘
值 性能 数据安全 适用场景
1 最慢（每次提交 fsync） 最安全（0 数据丢失） 主库、金融系统、任何不能丢数据的场景
2 较快（OS crash 可能丢 1 秒） MySQL 崩溃安全，OS/断电不安全 从库、批量导入、可容忍少量丢失的场景
0 最快（无 fsync） 任何崩溃都可能丢 1 秒 测试环境、非关键数据
生产事故案例: 某电商公司的从库设置了 innodb_flush_log_at_trx_commit=0，某天机房 UPS 故障导致从库断电重启。重启后发现从库的 GTID 位置比主库记录的要落后约 3 秒，导致复制中断。需要重新搭建从库。教训：即使是从库，在对复制一致性有要求的场景下也建议设为 2 而非 0。

3.6 Redo Log 大小调优

Redo log 太小会导致频繁 checkpoint（因为 redo 空间被写满后必须等待脏页刷盘来释放空间），而太大会延长崩溃恢复时间。

参数 版本 默认值 推荐值
innodb_log_file_size < 8.0.30 48MB 1GB-4GB（高写入负载）
innodb_log_files_in_group < 8.0.30 2 2
innodb_redo_log_capacity ≥ 8.0.30 100MB 2GB-8GB（取代上面两个参数）
-- How to estimate proper redo log size: -- 1. During peak load, check LSN growth over 1 hour: SHOW ENGINE INNODB STATUS; -- note "Log sequence number" = LSN_start -- wait 1 hour SHOW ENGINE INNODB STATUS; -- note "Log sequence number" = LSN_end -- redo_per_hour = LSN_end - LSN_start -- Recommended total redo size = redo_per_hour (covers 1 hour of writes) -- This means checkpoint must only happen once per hour -- Example: LSN grew by 2GB in 1 hour → set innodb_redo_log_capacity = 2G -- 2. Alternative: check checkpoint age SELECT (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_redo_log_current_lsn') - (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_redo_log_checkpoint_lsn') AS checkpoint_age; -- If checkpoint_age frequently exceeds 75% of redo log capacity, -- you need larger redo logs.

源码参考: storage/innobase/log/log0log.cc, storage/innobase/log/log0chkp.cc

3.7 Group Commit 与 binlog 协调

当多个事务同时提交时，InnoDB 使用 Group Commit 优化，将多个事务的 redo log 合并为一次 fsync 操作。在开启 binlog 的场景下，InnoDB 使用两阶段提交（2PC）确保 redo log 和 binlog 的一致性：

Two-Phase Commit with Binlog: Phase 1: PREPARE ├─ Write redo log with PREPARE state └─ fsync redo log Phase 2: COMMIT ├─ Write transaction to binlog ├─ fsync binlog (group commit: multiple txns in one fsync) └─ Write redo log with COMMIT state (no fsync needed—binlog is the authority) Crash Recovery Logic: ├─ If redo=PREPARE + binlog=YES → COMMIT (transaction was successful) ├─ If redo=PREPARE + binlog=NO → ROLLBACK (transaction incomplete) └─ If redo=COMMIT → already committed

3.8 Redo Log 记录类型

Redo Log 记录了多种类型的页修改操作。每条记录都包含类型标识、表空间 ID、页号和具体的修改内容。主要类型包括：

记录类型 说明 触发操作
MLOG_1BYTE / 2BYTE / 4BYTE / 8BYTE 写入 1/2/4/8 字节到页面特定偏移 页头字段更新等
MLOG_WRITE_STRING 写入变长字节串到页面 数据变更
MLOG_REC_INSERT 插入一条紧凑格式的记录 INSERT
MLOG_REC_UPDATE_IN_PLACE 原地更新记录（长度不变） UPDATE（字段长度不变时）
MLOG_REC_DELETE 删除记录（标记删除） DELETE
MLOG_PAGE_CREATE 创建新的索引页 页分裂等
MLOG_UNDO_INSERT 向 undo 页插入一条 undo 记录 DML 操作产生 undo
MLOG_COMP_PAGE_CREATE 创建紧凑格式页 新表空间分配页
MLOG_MULTI_REC_END Mini-transaction 结束标记 每个 mtr 结束时

源码参考: storage/innobase/include/mtr0types.h

3.9 Mini-Transaction (mtr) 概念

Mini-Transaction 是 InnoDB 内部的原子操作单元，不同于用户级事务。一个用户事务包含多个 mtr，每个 mtr 对 redo log 的写入是原子的。例如，一次 B+Tree 的页分裂可能涉及修改 3 个页面，这 3 个修改必须作为一个整体写入 redo log——要么全部有，要么全部没有。

User Transaction vs Mini-Transaction: BEGIN; -- User transaction starts INSERT INTO t VALUES (1, 'a'); -- mtr1: modify leaf page + undo page INSERT INTO t VALUES (2, 'b'); -- mtr2: modify leaf page + undo page INSERT INTO t VALUES (3, 'c'); -- mtr3: page split! modifies 3 pages -- 3a: allocate new page -- 3b: move half records to new page -- 3c: update parent page pointer -- These 3 writes are ONE mtr COMMIT; -- User transaction commits In the redo log: [mtr1 records][MLOG_MULTI_REC_END] [mtr2 records][MLOG_MULTI_REC_END] [mtr3 records][MLOG_MULTI_REC_END] ... (commit record)

3.10 崩溃恢复流程

当 MySQL 异常关闭后重启时，InnoDB 的崩溃恢复按以下步骤进行：

Crash Recovery Phases: Phase 1: REDO (Forward Recovery) ├─ Read redo log from last checkpoint LSN to end ├─ Parse redo records and apply them to data pages │ ├─ If page's LSN >= record's LSN → skip (already applied) │ └─ If page's LSN < record's LSN → apply the redo record ├─ This restores ALL modifications (both committed and uncommitted) └─ After this phase: database pages are in the state at crash time Phase 2: UNDO (Backward Recovery) ├─ Scan the undo log for active (uncommitted) transactions at crash time ├─ Roll back each uncommitted transaction using undo records ├─ This ensures atomicity: uncommitted changes are reversed └─ After this phase: only committed data remains Phase 3: Purge ├─ Clean up undo records from rolled-back transactions └─ Background purge threads handle this after recovery completes Recovery time depends on: - Amount of redo to replay (checkpoint_age at crash) - Number of uncommitted transactions to roll back - I/O speed of the storage device
实际恢复时间估算: 对于 SSD 存储，redo 重放速度约 100-200 MB/s。如果 checkpoint_age 为 4GB，恢复大约需要 20-40 秒（仅 redo 阶段）。Undo 阶段取决于要回滚的事务大小。大型未提交事务（如跑了 2 小时的 UPDATE ... WHERE）的回滚可能比正向恢复还慢，因为 undo 是逐行逆操作。

4. Undo Log 与 MVCC

4.1 Undo Log 的双重使命

Undo Log 在 InnoDB 中承担两个关键角色：

  1. 事务回滚: 当事务执行 ROLLBACK 时，用 Undo Log 中记录的旧值将数据恢复到事务开始前的状态。
  2. MVCC: 当其他事务需要读取某行的历史版本时，通过 Undo Log 中的版本链找到该事务可见的版本。

4.2 Undo Log 的存储结构

Undo Tablespace Structure: ┌────────────────────────────────────────────────────────┐ │ Undo Tablespace (undo_001, undo_002, ...) │ │ ┌──────────────────────────────────────────────────┐ │ │ │ Rollback Segment 1 (max 128 per tablespace) │ │ │ │ ┌─────────────────────────────────────────────┐ │ │ │ │ │ Undo Slot 1 ──▶ Undo Log 1 (one per txn) │ │ │ │ │ │ Undo Slot 2 ──▶ Undo Log 2 │ │ │ │ │ │ ... │ │ │ │ │ │ Undo Slot 1024 ──▶ Undo Log 1024 │ │ │ │ │ └─────────────────────────────────────────────┘ │ │ │ ├──────────────────────────────────────────────────┤ │ │ │ Rollback Segment 2 │ │ │ │ ... │ │ │ └──────────────────────────────────────────────────┘ │ └────────────────────────────────────────────────────────┘ Max concurrent transactions = undo_tablespaces × 128 rseg × 1024 slots = 2 × 128 × 1024 = 262,144 (default)

每个事务根据其操作类型需要 1 到 2 个 Undo Log：

  • INSERT Undo Log: 仅记录新插入行的主键，回滚时直接删除。事务提交后可立即释放。
  • UPDATE Undo Log: 记录被修改/删除行的旧值。事务提交后不能立即释放——需要等所有可能读到旧版本的事务结束后，由 purge 线程清理。

4.3 MVCC 版本链

InnoDB 中每行数据都有两个隐藏列：

  • DB_TRX_ID (6 bytes): 最后修改该行的事务 ID
  • DB_ROLL_PTR (7 bytes): 指向 Undo Log 中该行旧版本的指针
MVCC Version Chain Example: Current row in Buffer Pool / .ibd file: ┌─────────────────────────────────────────────┐ │ id=1, name="Charlie", age=30 │ │ DB_TRX_ID = 300 │ │ DB_ROLL_PTR ──────────────────┐ │ └────────────────────────────────┼──────────────┘ │ ▼ Undo Log ┌──────────────────────────────┐ │ name="Bob", age=28 │ │ DB_TRX_ID = 200 │ │ DB_ROLL_PTR ─────────┐ │ └───────────────────────┼───────┘ │ ▼ ┌──────────────────────────────┐ │ name="Alice", age=25 │ │ DB_TRX_ID = 100 │ │ DB_ROLL_PTR = NULL │ └──────────────────────────────┘ Transaction 150 (RR isolation) started before trx 200 and 300: → Sees row version: name="Alice", age=25 (trx 100, committed before 150) Transaction 250 (RR isolation) started after trx 200 committed: → Sees row version: name="Bob", age=28 (trx 200, committed before 250) Transaction 350 (any isolation) started after trx 300 committed: → Sees current row: name="Charlie", age=30

4.4 ReadView 详解

ReadView 是 InnoDB 实现 MVCC 一致性读的核心数据结构。当事务执行一致性读（非锁定读）时，InnoDB 创建一个 ReadView，它包含以下信息：

字段 含义
m_creator_trx_id 创建此 ReadView 的事务 ID
m_low_limit_id 创建 ReadView 时系统中下一个待分配的事务 ID（即当时的 max_trx_id）。ID >= 此值的事务一定不可见。
m_up_limit_id 创建 ReadView 时活跃事务列表中的最小事务 ID。ID < 此值的事务一定已提交，可见。
m_ids 创建 ReadView 时所有活跃（未提交）事务的 ID 列表。
ReadView Visibility Algorithm (simplified): function is_visible(row_trx_id, read_view): if row_trx_id == read_view.m_creator_trx_id: return true // I modified this row myself if row_trx_id < read_view.m_up_limit_id: return true // Transaction committed before ReadView was created if row_trx_id >= read_view.m_low_limit_id: return false // Transaction started after ReadView was created // m_up_limit_id <= row_trx_id < m_low_limit_id if row_trx_id in read_view.m_ids: return false // Transaction was active (uncommitted) when ReadView was created else: return true // Transaction committed before ReadView was created // (its ID is between up and low, but not in active list) If not visible → follow DB_ROLL_PTR to undo log, check previous version Repeat until a visible version is found or reach NULL (row didn't exist)

源码参考: storage/innobase/include/read0types.h — class ReadView, storage/innobase/row/row0sel.cc

4.5 RR vs RC：ReadView 创建时机的差异

REPEATABLE READ (RR)

ReadView 在事务的第一条 SELECT 时创建，整个事务生命周期内复用同一个 ReadView。因此事务内多次读取同一行，看到的值不变——即使其他事务已经提交了修改。

READ COMMITTED (RC)

ReadView 在每条 SELECT 执行时重新创建。因此事务内两次读取同一行可能看到不同的值——如果在两次读之间有其他事务提交了修改（不可重复读 / Non-Repeatable Read）。

Example: RR vs RC difference Initial: users table has (id=1, name="Alice") Timeline: T1 (RR): BEGIN T2: BEGIN T1: SELECT name FROM users WHERE id=1 → "Alice" T2: UPDATE users SET name="Bob" WHERE id=1 T2: COMMIT T1: SELECT name FROM users WHERE id=1 → "Alice" (same ReadView!) T3 (RC): BEGIN T4: BEGIN T3: SELECT name FROM users WHERE id=1 → "Alice" T4: UPDATE users SET name="Bob" WHERE id=1 T4: COMMIT T3: SELECT name FROM users WHERE id=1 → "Bob" (new ReadView!)

4.6 长事务的危害

生产事故：Undo Log 膨胀导致磁盘耗尽
某 SaaS 应用的一个分析查询事务开启后运行了 8 小时未提交。在此期间，其他事务产生的 UPDATE Undo Log 都不能被 purge（因为那个长事务的 ReadView 还需要读取旧版本），导致 undo tablespace 从 200MB 膨胀到 150GB，最终磁盘空间耗尽，数据库崩溃。

预防措施：
1. 设置 innodb_undo_log_truncate=ON（MySQL 8.0 默认 ON）
2. 监控 information_schema.INNODB_TRX 中长时间未提交的事务
3. 设置 kill_idle_transaction（Percona）或定期检查超时事务
4. 将分析查询路由到从库

4.7 Undo 参数配置

参数 默认值 说明
innodb_undo_tablespaces 2 独立 undo 表空间数量。MySQL 8.0 最低为 2。
innodb_undo_log_truncate ON (8.0) 自动回收超过 innodb_max_undo_log_size 的 undo 表空间。
innodb_max_undo_log_size 1GB 触发 undo 表空间 truncation 的大小阈值。
innodb_rollback_segments 128 每个 undo 表空间中的回滚段数量。
innodb_purge_threads 4 purge 操作的后台线程数。高写入场景可增加到 8。

源码参考: storage/innobase/trx/trx0trx.cc, storage/innobase/trx/trx0purge.cc

4.8 Purge 线程工作原理

Purge 是 InnoDB 后台清理操作，负责两个任务：

  1. 物理删除被标记为 "delete-marked" 的行（DELETE 操作只做逻辑删除标记，purge 才真正从索引中移除行）
  2. 清理不再被任何活跃事务需要的 UPDATE undo log 记录
Purge Process: 1. Purge coordinator thread (1 thread) reads the oldest ReadView across all active transactions 2. Any undo record with trx_id < oldest active ReadView's m_up_limit_id is safe to purge 3. For DELETE operations: ├─ Purge removes the delete-marked record from the clustered index └─ Purge removes corresponding entries from all secondary indexes 4. For UPDATE operations on indexed columns: └─ Purge removes old secondary index entries (the "ghost" entries) 5. After purge: undo log pages can be reused Monitoring purge lag: SHOW ENGINE INNODB STATUS → "History list length" This = number of undo records NOT yet purged If growing → purge can't keep up → increase innodb_purge_threads Or a long transaction is holding back the oldest ReadView

4.9 MVCC 对二级索引的处理

MVCC 在聚簇索引和二级索引上的实现有本质区别：

聚簇索引

每行都有 DB_TRX_ID 和 DB_ROLL_PTR，可以直接判断版本可见性并沿版本链追溯。

二级索引

二级索引条目没有 DB_TRX_ID 和 DB_ROLL_PTR。InnoDB 使用页头中的 PAGE_MAX_TRX_ID（该页中最大的 trx_id）做快速判断：如果 PAGE_MAX_TRX_ID < ReadView 的 m_up_limit_id，则整个页的所有记录都可见，无需回表。否则需要回表到聚簇索引检查每行的版本可见性。

性能影响: 这解释了为什么在高并发写入场景下，覆盖索引（covering index）的优势可能被削弱。如果二级索引页的 PAGE_MAX_TRX_ID 很新（因为有活跃的写事务），即使查询的列全部在二级索引中，InnoDB 仍然可能需要回表到聚簇索引来做版本可见性判断。长事务会加剧这个问题。

5. Doublewrite Buffer

5.1 部分页写入问题（Torn Pages）

InnoDB 的页大小是 16KB，但操作系统的原子写入通常只有 4KB（一个文件系统块）。这意味着一次 16KB 的页写入需要 4 次 4KB 的 I/O 操作。如果在这 4 次 I/O 之间发生断电或系统崩溃，可能只有部分数据写入成功——这就是 "torn page"（撕裂页）。

为什么 Redo Log 不能修复 Torn Page？ Redo Log 记录的是对页的逻辑修改（例如 "在偏移量 200 处将值从 25 改为 30"），而不是完整的页内容。如果页本身是损坏的（只有 8KB 被正确写入），在损坏的页上重放 redo 操作只会产生更多的错误。Redo Log 假设页的基础状态是正确的。

5.2 Doublewrite 如何工作

Doublewrite Buffer Write Flow: Step 1: Collect dirty pages to flush (batch, e.g., 64 pages) Step 2: Write pages sequentially to Doublewrite Buffer (on disk) ┌────────────────────────────────────────────────┐ │ Doublewrite Buffer (2 extents = 2MB total) │ │ ┌─────┬─────┬─────┬─────┬─────┬───────────┐ │ │ │ P1 │ P2 │ P3 │ ... │ P64 │ (unused) │ │ │ └─────┴─────┴─────┴─────┴─────┴───────────┘ │ └────────────────────────────────────────────────┘ Step 3: fsync the Doublewrite Buffer Step 4: Write each page to its actual location in .ibd files P1 → tablespace A, page 45 P2 → tablespace A, page 892 P3 → tablespace B, page 17 ... (these are random I/O) Step 5: fsync the .ibd files Crash Recovery: For each page in the .ibd file: 1. Verify page checksum 2. If checksum FAILS → page is torn → copy correct version from Doublewrite Buffer 3. Then apply redo log records on top of the good page

5.3 性能影响与何时禁用

方面 详情
写放大 每个脏页被写两次：一次到 Doublewrite Buffer，一次到实际位置。理论上写 I/O 翻倍，实测约 5-10% 额外开销（因为 Doublewrite 是顺序写入）。
可安全禁用的场景 1. 文件系统支持原子写入（如 ZFS、带 battery-backed cache 的 RAID 控制器）
2. 某些云数据库（如 Aurora）自己实现了存储层原子写
3. innodb_page_size 设为 4KB（等于文件系统块大小时无 torn page 风险）
-- MySQL 8.0.20+: separate doublewrite files -- Old: doublewrite stored in ibdata1 -- New: separate .dblwr files in datadir -- Disable doublewrite (only if your storage guarantees atomic writes!): SET GLOBAL innodb_doublewrite = OFF; -- MySQL 8.0.30+: fine-grained control -- innodb_doublewrite = ON | OFF | DETECT_AND_RECOVER | DETECT_ONLY -- DETECT_AND_RECOVER (default): full protection -- DETECT_ONLY: detect torn pages but don't fix (useful with ZFS)

6. Change Buffer

6.1 Change Buffer 解决什么问题？

当 INSERT/UPDATE/DELETE 修改的二级索引页不在 Buffer Pool 中时，InnoDB 面临一个选择：

不用 Change Buffer

立即从磁盘读取该二级索引页到 Buffer Pool，修改后标记为脏页。问题：这是一次随机读 I/O，对于每秒数千次 INSERT 的场景，会产生大量随机 I/O。

使用 Change Buffer

将修改操作缓存在 Change Buffer 中，不立即读取磁盘。等到该页因为其他查询被读入 Buffer Pool 时，再合并（merge）这些缓存的修改。这样避免了大量随机读 I/O。

重要限制: Change Buffer 只对二级索引（非唯一索引）有效。不适用于：
1. 聚簇索引（主键索引）——INSERT 时主键页必须被读取以确定插入位置
2. 唯一索引——INSERT 时必须读取索引页来检查唯一性约束

6.2 Merge 触发条件

  • 该二级索引页被 SELECT 查询读入 Buffer Pool 时
  • 后台 merge 线程定期执行
  • Buffer Pool 空间不足需要淘汰 Change Buffer 页时
  • 事务提交时（某些情况下）
  • 服务器正常关闭时（全量 merge）

6.3 配置参数

参数 默认值 说明
innodb_change_buffering all 缓冲哪些操作：none, inserts, deletes, changes(ins+del), purges, all
innodb_change_buffer_max_size 25 Change Buffer 最多占 Buffer Pool 的百分比。范围 0-50。

6.4 何时有效 vs 何时无效

有效场景
  • 大量随机 INSERT（如日志表、UUID 主键表的二级索引）
  • 二级索引页不太频繁被 SELECT（写多读少）
  • 磁盘 I/O 是瓶颈（HDD 比 SSD 受益更大）
无效/有害场景
  • 所有索引都是唯一索引（Change Buffer 完全不生效）
  • 写入后立即读取（merge 立即触发，与直接读无异）
  • Buffer Pool 足够大能缓存所有索引页（无需 Change Buffer 的优化）

7. 自适应哈希索引 (AHI)

7.1 工作原理

InnoDB 的所有用户数据和索引都存储在 B+Tree 中。B+Tree 查找需要从根节点逐层向下遍历，对于 3-4 层的 B+Tree，每次查找需要 3-4 次页访问（虽然上层页通常在 Buffer Pool 中，但仍有 CPU 开销）。

自适应哈希索引（AHI）是 InnoDB 自动在被频繁访问的 B+Tree 页上构建的内存哈希表。当 InnoDB 检测到某个索引前缀被反复用相同的模式查找时，它会自动建立一个哈希索引，将查找复杂度从 O(log n) 降低到 O(1)。

Without AHI: query → B+Tree root → level 1 → level 2 → leaf page → row 3-4 page accesses, CPU-intensive comparison at each level With AHI: query → hash lookup → directly to leaf page + record offset → row 1 hash probe, O(1)

7.2 监控 AHI

-- From SHOW ENGINE INNODB STATUS: ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Hash table size 34679, node heap has 1 buffer(s) Hash table size 34679, node heap has 0 buffer(s) ... 2.84 hash searches/s, 3.27 non-hash searches/s ← AHI hit ratio -- AHI hit rate = hash searches / (hash searches + non-hash searches) -- In this example: 2.84 / (2.84 + 3.27) = 46.5% -- If hit rate is consistently below 3-5%, consider disabling AHI

7.3 AHI 争用问题

AHI 使用 rw-lock（读写锁）保护。在高并发场景下，大量线程同时访问 AHI 可能导致 rw-lock 争用，表现为 CPU 使用率高但吞吐量不升反降。

参数 默认值 说明
innodb_adaptive_hash_index ON 是否启用 AHI。可动态关闭。
innodb_adaptive_hash_index_parts 8 AHI 分区数。增大可减少 rw-lock 争用。范围 1-512。

7.4 何时禁用 AHI

  • AHI hit rate 持续低于 5%（白白消耗内存和 CPU）
  • 在 SHOW ENGINE INNODB STATUS 的 SEMAPHORES 段看到大量 btr_search 相关的 rw-lock 等待
  • 工作负载以范围扫描为主（AHI 对点查有效，对范围扫描无效）
  • 使用大量不同索引前缀的随机查询（AHI 无法建立有效的哈希）

8. 表空间架构

8.1 表空间类型概览

InnoDB Tablespace Hierarchy: datadir/ ├── ibdata1 ← System Tablespace ├── undo_001 ← Undo Tablespace 1 ├── undo_002 ← Undo Tablespace 2 ├── ibtmp1 ← Temporary Tablespace ├── #innodb_temp/ ← Session Temporary Tablespaces (8.0.15+) │ ├── temp_1.ibt │ └── temp_2.ibt ├── #innodb_redo/ ← Redo Log files (8.0.30+) │ ├── #ib_redo0 │ └── #ib_redo1 ├── *.dblwr ← Doublewrite files (8.0.20+) ├── mydb/ ← Database directory │ ├── users.ibd ← File-per-table tablespace │ ├── orders.ibd │ └── ... └── general_ts.ibd ← General Tablespace (optional)

8.2 系统表空间 (ibdata1)

系统表空间是 InnoDB 最早的表空间，存储以下内容：

  • Change Buffer 的持久化数据
  • Doublewrite Buffer（MySQL 8.0.20 之前）
  • InnoDB 内部数据字典（MySQL 8.0 之前）
  • 如果 innodb_file_per_table=OFF，则用户表数据也存储在此
ibdata1 只增不减 系统表空间文件一旦增大就无法自动缩小。即使删除了大量数据，ibdata1 的大小也不会减少。这是因为 InnoDB 复用已释放的空间而不是归还给操作系统。历史上很多 DBA 遇到过 ibdata1 膨胀到数十 GB 的问题（通常是因为 innodb_file_per_table=OFF）。解决方案只有逻辑备份 + 重建实例。因此，永远保持 innodb_file_per_table=ON（MySQL 5.6.6+ 默认 ON）。

8.3 独立表空间 (File-per-table)

当 innodb_file_per_table=ON 时，每个 InnoDB 表的数据和索引存储在自己的 .ibd 文件中。

优点 缺点
DROP TABLE / TRUNCATE TABLE 可以真正回收磁盘空间 文件系统 inode 数量限制（数万张表时需注意）
可以单独对表进行 ALTER TABLE ... TABLESPACE 迁移 每个 .ibd 文件的元数据有少量存储开销
可以把不同表放到不同磁盘（通过符号链接或 DATA DIRECTORY） fsync 粒度更细（每个文件一次）可能略影响写性能
支持 COMPRESSED 行格式

8.4 通用表空间 (General Tablespaces)

-- Create a general tablespace CREATE TABLESPACE ts_hot ADD DATAFILE '/fast_ssd/ts_hot.ibd' ENGINE = InnoDB; -- Create table in specific tablespace CREATE TABLE hot_data (id INT PRIMARY KEY, val VARCHAR(100)) TABLESPACE ts_hot; -- Move existing table to the tablespace ALTER TABLE orders TABLESPACE ts_hot;

通用表空间的价值在于可以把相关的表组织在同一个文件中（减少文件描述符使用）或者指定到特定的存储设备上。

8.5 临时表空间

MySQL 8.0 有两种临时表空间：

  • 全局临时表空间 (ibtmp1): 用于用户创建的临时表（CREATE TEMPORARY TABLE）中的回滚段。重启后自动重建。
  • 会话临时表空间 (#innodb_temp/): MySQL 8.0.15+ 新增，用于存储用户创建的临时表数据和内部临时表。每个会话分配一个，会话结束后空间可回收。

8.6 表空间内部结构：Segment, Extent, Page

InnoDB 的表空间在内部按三级结构组织磁盘空间：

Tablespace Internal Hierarchy: ┌─────────────────────────────────────────────────────────┐ │ Tablespace (.ibd file) │ │ │ │ ┌──── Segment (logical grouping) ──────────────────┐ │ │ │ Each index has 2 segments: │ │ │ │ ├─ Leaf node segment (data) │ │ │ │ └─ Non-leaf node segment (internal nodes) │ │ │ │ │ │ │ │ ┌── Extent (physical allocation unit) ────────┐ │ │ │ │ │ 1 extent = 64 contiguous pages = 1 MB │ │ │ │ │ │ (when page_size=16KB) │ │ │ │ │ │ │ │ │ │ │ │ ┌──────┬──────┬──────┬───┬──────┐ │ │ │ │ │ │ │Page 0│Page 1│Page 2│...│Page63│ (16KB ea) │ │ │ │ │ │ └──────┴──────┴──────┴───┴──────┘ │ │ │ │ │ └─────────────────────────────────────────────┘ │ │ │ │ │ │ │ │ For small segments (< 32 pages): │ │ │ │ Pages are allocated individually from a │ │ │ │ "fragment" extent (shared with other segments) │ │ │ └────────────────────────────────────────────────────┘ │ │ │ │ Page 0: FSP_HDR (file space header) — tracks extents │ │ Page 1: IBUF_BITMAP — change buffer bitmap │ │ Page 2: INODE — segment metadata │ │ Page 3+: data pages (B+Tree root, etc.) │ │ │ │ Every 16384 pages (256MB): another XDES page │ │ for tracking the next group of extents │ └─────────────────────────────────────────────────────────┘
为什么按 Extent 分配？ InnoDB 使用 extent（1MB 连续空间）作为基本分配单位，而不是单独分配每个页。原因是磁盘的顺序读写性能远高于随机读写。当一个表增长时，分配连续的 1MB extent 确保数据在磁盘上是物理连续的，全表扫描和范围扫描都能受益于顺序 I/O。但对于很小的表（< 32 页 = 512KB），逐页分配避免了过多的空间浪费。

8.7 .ibd 文件空间回收与碎片整理

当大量行被 DELETE 后，.ibd 文件不会自动缩小。被删除的空间会被标记为可复用，但文件的物理大小不变。如果碎片严重，可以通过以下方式回收空间：

-- Method 1: OPTIMIZE TABLE (rebuilds the table, reclaims space) OPTIMIZE TABLE large_table; -- For InnoDB, this is equivalent to ALTER TABLE ... ENGINE=InnoDB -- ⚠ Requires full table copy — blocks writes during operation -- Use pt-online-schema-change or gh-ost for production -- Method 2: ALTER TABLE (null operation that triggers rebuild) ALTER TABLE large_table ENGINE=InnoDB; -- Check fragmentation level: SELECT TABLE_NAME, DATA_LENGTH, DATA_FREE, ROUND(DATA_FREE / DATA_LENGTH * 100, 1) AS frag_pct FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'mydb' AND DATA_FREE > 0 ORDER BY DATA_FREE DESC; -- frag_pct > 20% suggests rebuild may be worthwhile

9. 行格式 (Row Formats)

9.1 四种行格式对比

行格式 引入版本 变长字段存储 大字段溢出 推荐
REDUNDANT 最早 在行内存储完整长度信息 前 768 字节存在行内 否（仅兼容旧版）
COMPACT 5.0 紧凑存储 NULL 标志位和变长字段长度 前 768 字节存在行内 可用但非首选
DYNAMIC 5.7+ 与 COMPACT 相同 仅存 20 字节指针，全部数据存到溢出页 是（默认，推荐）
COMPRESSED 5.5+ 与 DYNAMIC 相同，但页级压缩 与 DYNAMIC 相同 特殊场景（冷数据）

9.2 COMPACT/DYNAMIC 行内部结构

COMPACT/DYNAMIC Row Format: ┌──────────────────────────────────────────────────────────────────┐ │ Variable-Length Field Lengths (1-2 bytes each, reverse order) │ ├──────────────────────────────────────────────────────────────────┤ │ NULL Flags (1 bit per nullable column, rounded to bytes) │ ├──────────────────────────────────────────────────────────────────┤ │ Record Header (5 bytes) │ │ ├─ info_flags (4 bits): delete-mark, min-rec, etc. │ │ ├─ n_owned (4 bits): records owned by this slot in page dir │ │ ├─ heap_no (13 bits): position in page heap │ │ ├─ record_type (3 bits): 0=leaf, 1=non-leaf, 2=infimum, 3=sup │ │ └─ next_record (16 bits): relative offset to next record │ ├──────────────────────────────────────────────────────────────────┤ │ Hidden Columns: │ │ ├─ DB_ROW_ID (6 bytes, only if no explicit PK) │ │ ├─ DB_TRX_ID (6 bytes, transaction ID) │ │ └─ DB_ROLL_PTR (7 bytes, undo log pointer) │ ├──────────────────────────────────────────────────────────────────┤ │ Column 1 data │ │ Column 2 data │ │ ... │ │ Column N data │ │ (If column > threshold → 20-byte pointer to overflow page) │ └──────────────────────────────────────────────────────────────────┘

9.3 溢出页存储（Off-page Storage）

当一行中的大字段（如 TEXT、BLOB、很长的 VARCHAR）超过页的存储能力时，InnoDB 会将部分或全部数据存储在溢出页中。COMPACT 和 DYNAMIC 的处理方式不同：

COMPACT / REDUNDANT

前 768 字节存储在行内，其余存储在溢出页中。这意味着即使只需要主键查找，也可能把大量 TEXT 数据加载到 Buffer Pool 的数据页中，浪费内存。

DYNAMIC

当字段需要溢出时，行内只存 20 字节的指针（空间 ID + 页号 + 偏移量），全部数据存在溢出页中。这使得数据页能容纳更多行，大幅提升 Buffer Pool 效率。

最佳实践: 始终使用 ROW_FORMAT=DYNAMIC（MySQL 8.0 默认值）。如果你的表有 TEXT/BLOB 列且需要节省磁盘空间（且可以接受约 10% 的 CPU 开销），可以考虑 COMPRESSED。但在 MySQL 8.0 中，推荐使用页级别的透明页压缩（COMPRESSION='zstd'）来替代 COMPRESSED 行格式。

9.4 InnoDB 行记录中的隐藏列详解

每行 InnoDB 数据都有最多三个隐藏列，对 MVCC 和内部管理至关重要：

隐藏列 大小 说明
DB_ROW_ID 6 bytes 行 ID，仅在表没有显式主键且没有非 NULL 唯一索引时才添加。InnoDB 会自动生成一个全局递增的 row_id 作为内部主键。这也是为什么建表时始终应该定义主键——否则 InnoDB 用一个全局 mutex 保护的计数器来生成 row_id，在高并发 INSERT 时成为瓶颈。
DB_TRX_ID 6 bytes 最后修改该行的事务 ID。INSERT/UPDATE/DELETE 操作都会更新此值。是 MVCC 判断行版本可见性的核心依据。
DB_ROLL_PTR 7 bytes 回滚指针，指向 undo log 中该行的前一个版本。通过 DB_ROLL_PTR 可以沿着版本链找到任意历史版本。7 字节 = 1 bit (INSERT/UPDATE flag) + 7 bits (rollback segment ID) + 32 bits (page number) + 16 bits (offset within page)。

9.5 COMPRESSED 行格式深入

COMPRESSED 行格式在 DYNAMIC 的基础上增加了页级 zlib 压缩。每个 16KB 页被压缩后存储在更小的空间中。但 InnoDB 在内存中需要同时维护压缩页和未压缩页的副本：

COMPRESSED page handling in Buffer Pool: Disk (compressed): Buffer Pool (memory): ┌──────────────┐ ┌─────────────────────────┐ │ 8KB on disk │ ──────▶ │ 8KB compressed page │ ← always present │ (compressed) │ │ + │ │ │ │ 16KB uncompressed page │ ← present when accessed └──────────────┘ └─────────────────────────┘ Problems with COMPRESSED: 1. Double memory usage (compressed + uncompressed copies) 2. CPU overhead for compress/decompress 3. Compression failures: if modified page can't fit in original compressed size → reorganize page → still can't fit → page split 4. Modification log: InnoDB maintains a tiny log of modifications on the compressed page to avoid recompressing on every change → when log is full → recompress → potential split
COMPRESSED vs 透明页压缩
MySQL 8.0 推荐使用透明页压缩（CREATE TABLE ... COMPRESSION='zstd'）替代 COMPRESSED 行格式：
1. 透明页压缩不需要在内存中维护两份副本
2. 支持更高效的压缩算法（zstd，而非 zlib）
3. 依赖文件系统的稀疏文件（punch hole）支持——Linux ext4/xfs/btrfs 均支持
4. 对 Buffer Pool 无额外内存开销
5. 缺点：需要文件系统支持 punch hole；实际节省取决于文件系统的块对齐

9.6 行记录大小限制

InnoDB 对行大小有几个重要限制：

限制 值 说明
MySQL 层行大小上限 65,535 bytes 所有列的声明长度之和（不含 BLOB/TEXT）不能超过此值
InnoDB 页内行大小上限 ~8000 bytes 约为页大小的一半（16KB/2），确保每页至少能存 2 行（B+Tree 要求）
溢出触发阈值 动态计算 当行内数据超过约 8000 字节时，最长的变长字段被移到溢出页。DYNAMIC 格式只保留 20 字节指针。
最大列数 1017 每张表最多 1017 列（含隐藏列和虚拟列后的限制）

10. 刷盘机制 (Flush)

10.1 为什么需要刷盘？

InnoDB 的写入先到 Buffer Pool（内存中的脏页），事务通过 Redo Log 保证持久性。但脏页最终必须写回磁盘，原因有二：

  1. 释放 Buffer Pool 空间给新页
  2. 推进 checkpoint LSN 以释放 redo log 空间并缩短崩溃恢复时间

10.2 两种刷盘路径

LRU Flushing

当 Buffer Pool 空闲页不足时，page cleaner 线程从 LRU 尾部开始扫描，将脏页刷盘后释放为空闲页。目标是确保有足够的空闲页供新的读请求使用。

Flush List Flushing

page cleaner 线程按 Flush List 中脏页的修改顺序（oldest_modification LSN）刷盘，推进 checkpoint。目标是确保 redo log 不会被写满。

10.3 关键刷盘参数

参数 默认值 说明 推荐设置
innodb_io_capacity 200 InnoDB 后台任务（刷盘、合并 change buffer）每秒的 I/O 操作数上限。 SSD: 2000-10000, HDD: 200-400
innodb_io_capacity_max 2000 紧急刷盘（redo log 快满）时的 I/O 操作数上限。 设为 innodb_io_capacity 的 2-4 倍
innodb_flush_neighbors 0 (8.0) 刷脏页时是否连带刷相邻的脏页。HDD 上有用（减少随机 I/O），SSD 上应禁用。 SSD: 0, HDD: 1
innodb_adaptive_flushing ON 自适应刷盘：InnoDB 根据 redo log 的消耗速度动态调整刷盘速率。 ON
innodb_page_cleaners 4 page cleaner 线程数。应与 innodb_buffer_pool_instances 相等或更多。 = buffer_pool_instances
innodb_lru_scan_depth 1024 每个 Buffer Pool 实例中 page cleaner 每次 LRU 扫描的深度。 保持默认或降低到 256-512（如果 I/O 压力大）

10.4 自适应刷盘算法

当 innodb_adaptive_flushing=ON 时，InnoDB 使用以下公式计算理想的刷盘速率：

Adaptive Flushing Rate Calculation (simplified): checkpoint_age = current_lsn - last_checkpoint_lsn max_checkpoint_age = redo_log_capacity * 0.8125 (async threshold) if checkpoint_age < max_checkpoint_age * 0.75: flush_rate = low (normal pace) elif checkpoint_age < max_checkpoint_age: flush_rate = scaled between io_capacity and io_capacity_max (proportional to how close checkpoint_age is to max) else: flush_rate = io_capacity_max (EMERGENCY: aggressive flush) ⚠ User queries may stall waiting for redo space! The "adaptive" part: InnoDB also considers the rate of redo log generation (LSN growth speed) to predict future flush needs.
生产事故：Flushing Storm 某公司使用默认的 innodb_io_capacity=200 和 innodb_redo_log_capacity=100MB（都太小），在业务高峰期 redo log 被快速填满。InnoDB 触发紧急同步刷盘，所有用户查询被阻塞 5-10 秒。表现为 QPS 突然降到接近 0，然后恢复。这种现象每隔几分钟重复一次。解决方案：增大 redo log 到 4GB，增大 innodb_io_capacity 到 4000（SSD），问题消失。

源码参考: storage/innobase/buf/buf0flu.cc — buf_flush_page_cleaner_coordinator()

11. InnoDB 配置速查表

以下是最关键的 InnoDB 参数及其推荐配置（假设 64GB RAM 服务器，SSD 存储）：

参数 默认值 推荐值 (64G/SSD) 说明
innodb_buffer_pool_size 128MB 44G ~70% 总内存
innodb_buffer_pool_instances 8 16 每个实例 ~2.75GB
innodb_redo_log_capacity 100MB 4G 8.0.30+；老版本用 log_file_size*files
innodb_log_buffer_size 16MB 64MB 大事务或高并发写入时增大
innodb_flush_log_at_trx_commit 1 1 主库必须为 1；从库可设 2
innodb_io_capacity 200 4000 SSD IOPS 的 50-75%
innodb_io_capacity_max 2000 8000 io_capacity 的 2 倍
innodb_flush_method fsync O_DIRECT 绕过 OS 缓存，避免双重缓存
innodb_flush_neighbors 0 0 SSD 无需相邻刷盘
innodb_page_cleaners 4 16 = buffer_pool_instances
innodb_purge_threads 4 4-8 高 UPDATE/DELETE 负载时增加
innodb_file_per_table ON ON 永远保持 ON
innodb_doublewrite ON ON 除非使用 ZFS 或 BBWC RAID
innodb_adaptive_hash_index ON ON 如有争用则关闭
innodb_change_buffer_max_size 25 25 如果无二级索引或全部唯一索引，设为 0
innodb_undo_tablespaces 2 2-4 更多表空间 = 更好的并发和 truncation
innodb_undo_log_truncate ON ON 自动回收 undo 空间
innodb_print_all_deadlocks OFF ON 将所有死锁记录到错误日志
innodb_deadlock_detect ON ON 高并发热点行场景可考虑 OFF + 超时
-- Example my.cnf InnoDB section for 64GB SSD server: [mysqld] # Buffer Pool innodb_buffer_pool_size = 44G innodb_buffer_pool_instances = 16 innodb_buffer_pool_dump_at_shutdown = ON innodb_buffer_pool_load_at_startup = ON # Redo Log innodb_redo_log_capacity = 4G innodb_log_buffer_size = 64M innodb_flush_log_at_trx_commit = 1 # I/O innodb_io_capacity = 4000 innodb_io_capacity_max = 8000 innodb_flush_method = O_DIRECT innodb_flush_neighbors = 0 innodb_page_cleaners = 16 # Tablespace innodb_file_per_table = ON innodb_doublewrite = ON innodb_undo_tablespaces = 3 innodb_undo_log_truncate = ON # Concurrency innodb_adaptive_hash_index = ON innodb_adaptive_hash_index_parts = 16 innodb_purge_threads = 4 # Monitoring innodb_print_all_deadlocks = ON innodb_status_output_locks = ON

12. 监控与排障

12.1 SHOW ENGINE INNODB STATUS 完全解读

这是 InnoDB 最重要的诊断工具。输出分为多个段落，每个段落提供不同维度的信息：

(1) SEMAPHORES 段

---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 12345 --Thread 140234567890 has waited at btr0sea.ic line 92 for 0.5 seconds the semaphore: S-lock on RW-latch at 0x7f... a]writer (thread id 140234567891) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Key indicators: - "has waited ... for X seconds": threads waiting for locks - btr0sea.ic: AHI-related contention → consider disabling AHI - buf0buf.cc: Buffer Pool mutex contention → increase instances - Frequent long waits (>1s) = performance problem

(2) TRANSACTIONS 段

------------ TRANSACTIONS ------------ Trx id counter 12345678 Purge done for trx's n:o < 12345670 undo n:o < 0 History list length 234 ← Undo records not yet purged If growing → long transactions or purge lag ---TRANSACTION 12345677, ACTIVE 3600 sec ← Active for 1 hour! Problem! 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 42, OS thread handle 140..., query id 999 SELECT * FROM large_table WHERE status = 'active' Key indicators: - History list length: should be low (<1000); growing = purge lag - ACTIVE NNN sec: long-running transactions → undo bloat risk - lock struct(s) / row lock(s): lock usage per transaction

(3) FILE I/O 段

-------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) ... Pending normal aio reads: [0, 0, 0, 0] ← Should be 0 or very low Pending normal aio writes: [0, 0, 0, 0] Pending ibuf aio reads: 0 6821 OS file reads, 29412 OS file writes, 8513 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 1.86 writes/s, 0.76 fsyncs/s Key indicators: - High pending reads/writes = I/O subsystem is saturated - fsyncs/s: correlates with commit rate and flush_log_at_trx_commit setting

(4) ROW OPERATIONS 段

-------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB ← Open read views = MVCC snapshots Process ID=12345, Main thread ID=140..., state: sleeping Number of rows inserted 567890, updated 123456, deleted 78901, read 45678901 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

12.2 information_schema 监控表

-- Buffer Pool statistics per instance SELECT POOL_ID, POOL_SIZE, FREE_BUFFERS, DATABASE_PAGES, OLD_DATABASE_PAGES, MODIFIED_DATABASE_PAGES, HIT_RATE, YOUNG_MAKE_PER_THOUSAND_GETS FROM information_schema.INNODB_BUFFER_POOL_STATS; -- Currently open transactions SELECT trx_id, trx_state, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec, trx_rows_locked, trx_rows_modified, trx_query FROM information_schema.INNODB_TRX ORDER BY trx_started ASC; -- Lock waits (who is blocking whom) SELECT r.trx_id AS waiting_trx, r.trx_query AS waiting_query, b.trx_id AS blocking_trx, b.trx_query AS blocking_query FROM performance_schema.data_lock_waits w JOIN information_schema.INNODB_TRX r ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID JOIN information_schema.INNODB_TRX b ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID; -- InnoDB metrics (hundreds of counters) SELECT NAME, COUNT, STATUS FROM information_schema.INNODB_METRICS WHERE STATUS = 'enabled' ORDER BY NAME;

12.3 performance_schema 中的 InnoDB 表

表名 用途
data_locks 当前持有和等待的所有 InnoDB 锁
data_lock_waits 锁等待关系：哪个事务在等哪个
events_waits_summary_global_by_event_name InnoDB mutex/rw-lock 等待统计（搜索 wait/synch/mutex/innodb）
file_summary_by_instance 每个 .ibd 文件的 I/O 统计（读/写字节、延迟）
table_io_waits_summary_by_table 每张表的 I/O 等待统计
-- Find InnoDB mutex contention hotspots SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1000000000 AS total_wait_ms FROM performance_schema.events_waits_summary_global_by_event_name WHERE EVENT_NAME LIKE 'wait/synch/mutex/innodb/%' ORDER BY SUM_TIMER_WAIT DESC LIMIT 10; -- Find the most I/O-heavy .ibd files SELECT FILE_NAME, COUNT_READ, SUM_NUMBER_OF_BYTES_READ, COUNT_WRITE, SUM_NUMBER_OF_BYTES_WRITE, SUM_TIMER_READ/1000000000 AS read_wait_ms, SUM_TIMER_WRITE/1000000000 AS write_wait_ms FROM performance_schema.file_summary_by_instance WHERE FILE_NAME LIKE '%.ibd' ORDER BY SUM_TIMER_READ + SUM_TIMER_WRITE DESC LIMIT 10;

12.4 常见问题排查清单

症状 可能原因 检查方法 解决方案
QPS 周期性骤降 Redo log 满导致同步刷盘 checkpoint_age 接近 redo log capacity 增大 redo log + io_capacity
Buffer Pool hit rate 低 Pool 太小或全表扫描冲刷 SHOW ENGINE INNODB STATUS → hit rate 增大 pool size 或优化查询
History list 持续增长 长事务阻止 purge INNODB_TRX → trx_started 找到并终止长事务
ibdata1 膨胀 innodb_file_per_table=OFF 检查 my.cnf 开启后逻辑重建
CPU 高但吞吐量低 AHI rw-lock 争用 SEMAPHORES → btr0sea.ic 关闭 AHI 或增加 parts
Undo tablespace 暴涨 长事务 + 高并发写入 INNODB_TRX + undo file size 终止长事务 + truncate undo

13. 常见问题 (FAQ)

Q1: innodb_buffer_pool_size 设置为总内存的多少合适？

通用规则是 60-80% 的总内存，但具体取决于场景：

  • 专用数据库服务器（只跑 MySQL）：70-80%
  • 共享服务器（还有应用、缓存等）：50-60%
  • 小内存（≤4GB）：50%

剩余内存留给：OS 文件缓存、每个连接的内存开销（sort_buffer、join_buffer、thread stack，每连接约 2-10MB）、其他进程。一个常见错误是把 buffer_pool_size 设到 90%+，导致 OS 开始 swap，性能反而暴跌。

Q2: 为什么 InnoDB 选择 B+Tree 而不是 B-Tree 或哈希索引？

B+Tree 相比 B-Tree 的优势：

  • 所有数据都在叶子节点：非叶节点只存键值和指针，扇出更高，树更矮，减少磁盘 I/O
  • 叶子节点形成双向链表：范围扫描（BETWEEN、ORDER BY）只需沿链表遍历，不用回溯到父节点
  • 查询性能稳定：所有查找都走到叶子节点，路径长度一致（O(log n)）

哈希索引虽然点查 O(1)，但不支持范围扫描、排序、前缀匹配——这些在 OLTP 中极其常见。InnoDB 将哈希作为补充（AHI）而非主结构。

Q3: innodb_flush_log_at_trx_commit=2 和 sync_binlog=1 能一起用吗？

技术上可以，但通常没有意义。sync_binlog=1 保证 binlog 每次提交都 fsync，但 flush_log_at_trx_commit=2 让 redo log 可能丢失。崩溃恢复依赖 redo log 和 binlog 的一致性：如果 redo log 丢了但 binlog 有，会导致主从不一致。

推荐组合：

  • 主库：flush_log_at_trx_commit=1 + sync_binlog=1（双1，最安全）
  • 从库：flush_log_at_trx_commit=2 + sync_binlog=0（双2/0，兼顾性能）
Q4: 如何判断 Buffer Pool 是否够大？

三个关键指标：

  1. Buffer Pool hit rate：SHOW ENGINE INNODB STATUS 中的 "Buffer pool hit rate"。应 >= 999/1000（99.9%）。
  2. Free buffers：如果长期为 0 但 hit rate 还高，说明 pool 大小刚好。如果 free=0 且 hit rate 低，需要增大。
  3. Pages read vs Innodb_buffer_pool_reads：SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%'。Innodb_buffer_pool_read_requests（逻辑读）vs Innodb_buffer_pool_reads（物理读），比值越高越好。

终极测试：如果你的整个数据集能装进 Buffer Pool（检查 SHOW TABLE STATUS 的 Data_length + Index_length 总和），那就设成能装下全部数据的大小。

Q5: MVCC 能完全避免锁吗？

不能。MVCC 只避免了读-写冲突（读不阻塞写，写不阻塞读），但写-写冲突仍然需要锁。具体来说：

  • 一致性读（普通 SELECT）：通过 MVCC 读取历史版本，不加锁
  • 当前读（SELECT ... FOR UPDATE / LOCK IN SHARE MODE）：加锁读，读最新版本
  • INSERT / UPDATE / DELETE：先读最新版本（当前读），再加排他锁修改

因此 MVCC + 锁 是 InnoDB 并发控制的完整机制，两者互补而非替代。

Q6: 为什么 innodb_flush_method 推荐 O_DIRECT？

默认的 fsync 方法下，InnoDB 的写入路径是：Buffer Pool → OS Page Cache → 磁盘。数据在内存中存了两份（InnoDB Buffer Pool + OS Page Cache），浪费内存。

O_DIRECT 让数据绕过 OS Page Cache 直接写入磁盘：Buffer Pool → 磁盘。优势：

  • 避免双重缓存，节省宝贵的 OS 内存给其他用途
  • 减少 OS Page Cache 的 CPU 开销（cache 查找、LRU 管理）
  • 在大 Buffer Pool 场景下效果特别明显

注意：O_DIRECT 对 redo log 仍使用 fsync。在 MySQL 8.0.26+ 中可以使用 O_DIRECT_NO_FSYNC，在某些文件系统上性能更好。

Q7: InnoDB 为什么需要 Doublewrite，而 PostgreSQL 不需要？

两者使用了不同的策略解决 torn page 问题：

  • InnoDB：Redo Log 记录的是物理逻辑日志（physiological logging），只记录页内偏移量的增量修改。如果页本身损坏，无法正确重放 redo。因此需要 Doublewrite 提供一个完好的页副本作为重放基础。
  • PostgreSQL：在 checkpoint 后第一次修改页时，会在 WAL 中写入完整页镜像（Full Page Write，FPW）。即使页损坏，也能从 WAL 中恢复完整页面。代价是 WAL 体积更大。

两种方案各有优劣：InnoDB 的 redo log 更紧凑但需要额外的 Doublewrite I/O；PostgreSQL 的 WAL 更大但架构更简单。

Q8: 如何在线调整 innodb_buffer_pool_size？

MySQL 5.7+ 支持在线调整 Buffer Pool 大小，无需重启：

SET GLOBAL innodb_buffer_pool_size = 48 * 1024 * 1024 * 1024;  -- 48GB

注意事项：

  • 调整以 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 为单位进行（默认 128MB * 8 = 1GB）
  • 如果设置的值不是该单位的整数倍，MySQL 会自动向上取整
  • 增大通常很快，缩小可能很慢（需要淘汰页面）
  • 调整期间不会阻塞查询，但可能导致短暂性能波动
  • 通过 SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status' 监控进度
Q9: RR 隔离级别下 InnoDB 如何解决幻读？

InnoDB 在 RR 下通过两种机制解决幻读：

  1. MVCC（快照读）：普通 SELECT 使用事务开始时的 ReadView，即使其他事务插入了新行，快照读也看不到——因此不会出现幻读。
  2. Next-Key Lock（当前读）：SELECT ... FOR UPDATE / INSERT / UPDATE / DELETE 使用当前读，InnoDB 通过 Next-Key Lock（Record Lock + Gap Lock）锁定查询范围内的间隙，阻止其他事务在该范围内插入新行。

注意：RR 级别并非在所有情况下都能完美防止幻读。一个经典的反例是：事务先做快照读（看不到新行），然后做 UPDATE 操作（当前读，能影响到新行），再做快照读（这次可能看到了——因为 UPDATE 把新行的 trx_id 改成了当前事务的 ID）。

Q10: MySQL 8.0 对 InnoDB 做了哪些重大架构改进？

MySQL 8.0 对 InnoDB 的改进是历史性的：

  • 数据字典原子化：从基于文件的 .frm 迁移到 InnoDB 内部的事务性数据字典，DDL 操作变为原子性（crash-safe）
  • Redo Log 重构 (8.0.30+)：从固定大小文件改为动态大小 #innodb_redo/ 目录，用 innodb_redo_log_capacity 统一控制
  • Doublewrite 独立文件 (8.0.20+)：从 ibdata1 分离到独立 .dblwr 文件，支持并行写入
  • Instant ADD COLUMN (8.0.12+)：某些 ALTER TABLE ADD COLUMN 操作可以瞬间完成，无需重建表
  • 不可见索引：ALTER TABLE ... ALTER INDEX idx INVISIBLE，测试删除索引的影响而不实际删除
  • 降序索引：真正的降序 B+Tree 索引，优化 ORDER BY ... DESC 查询
  • 会话级临时表空间 (8.0.15+)：每个会话单独的临时表空间文件，连接断开后可回收
  • Undo Tablespace 管理：支持在线创建/删除 undo tablespace，自动 truncation
  • 并行读取 (8.0.14+)：SELECT COUNT(*) 等全表扫描可以并行读取多个页

12.5 关键 InnoDB Status 变量

除了 SHOW ENGINE INNODB STATUS，以下 SHOW GLOBAL STATUS 变量对日常监控最有价值：

变量 含义 健康标准
Innodb_buffer_pool_read_requests Buffer Pool 逻辑读请求总数 与 reads 的比值 > 99.9%
Innodb_buffer_pool_reads Buffer Pool 未命中、需要从磁盘读取的次数 越低越好
Innodb_buffer_pool_pages_dirty Buffer Pool 中脏页数量 应保持稳定，不应持续增长
Innodb_buffer_pool_wait_free 等待空闲页的次数（Free list 为空） 应为 0 或接近 0
Innodb_log_waits Log Buffer 太小导致等待的次数 应为 0；如 > 0 增大 log_buffer_size
Innodb_os_log_written Redo Log 已写入字节数 用于计算写入速率和估算 redo 大小
Innodb_row_lock_time_avg 平均行锁等待时间（ms） < 10ms；> 100ms 需排查
Innodb_row_lock_waits 行锁等待次数 趋势比绝对值重要
Innodb_rows_read / inserted / updated / deleted InnoDB 层行操作统计 用于工作负载画像
Innodb_data_reads / writes 物理 I/O 操作数 与逻辑读比较衡量缓存效率
-- Buffer Pool hit rate calculation: SELECT (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') AS logical_reads, (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') AS disk_reads, ROUND( (1 - (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') / (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') ) * 100, 3 ) AS hit_rate_pct; -- Redo log write rate (bytes per second): -- Record Innodb_os_log_written at two points in time, divide the delta by seconds elapsed -- Quick health check script: SELECT 'Buffer Pool Hit Rate' AS metric, CONCAT(ROUND((1 - v2.val/v1.val)*100, 2), '%') AS value FROM (SELECT VARIABLE_VALUE val FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_read_requests') v1, (SELECT VARIABLE_VALUE val FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_reads') v2 UNION ALL SELECT 'Dirty Page Ratio', CONCAT(ROUND(v1.val/v2.val*100, 1), '%') FROM (SELECT VARIABLE_VALUE val FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_pages_dirty') v1, (SELECT VARIABLE_VALUE val FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_pages_total') v2 UNION ALL SELECT 'Log Waits (should be 0)', VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_log_waits' UNION ALL SELECT 'Wait Free (should be 0)', VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_wait_free' UNION ALL SELECT 'Avg Row Lock Time (ms)', VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_row_lock_time_avg';

12.6 Prometheus / Grafana 监控建议

在生产环境中，通常使用 mysqld_exporter 将 InnoDB 指标暴露给 Prometheus，再用 Grafana 可视化。建议监控的核心面板包括：

面板 指标 告警阈值
Buffer Pool Hit Rate rate(mysql_global_status_innodb_buffer_pool_read_requests[5m])
rate(mysql_global_status_innodb_buffer_pool_reads[5m])		 < 99.5% 告警
Dirty Pages % mysql_global_status_innodb_buffer_pool_pages_dirty / mysql_global_status_innodb_buffer_pool_pages_total > 75% 告警
Checkpoint Age mysql_global_status_innodb_checkpoint_age > 80% redo capacity 告警
History List Length mysql_global_status_innodb_history_list_length > 10000 告警
Row Lock Waits/s rate(mysql_global_status_innodb_row_lock_waits[5m]) > 100/s 告警
Long Transactions mysql_info_schema_innodb_trx (custom query) > 300 秒告警
Redo Log Write Rate rate(mysql_global_status_innodb_os_log_written[5m]) 趋势监控（突变告警）
Pending I/O mysql_global_status_innodb_data_pending_reads
mysql_global_status_innodb_data_pending_writes		 > 0 持续 5 分钟告警

12.7 sys schema 实用视图

-- Top tables by buffer pool usage SELECT object_schema, object_name, allocated, data, pages FROM sys.innodb_buffer_stats_by_table ORDER BY allocated DESC LIMIT 20; -- Top indexes by buffer pool usage SELECT object_schema, object_name, index_name, allocated, data, pages FROM sys.innodb_buffer_stats_by_schema ORDER BY allocated DESC; -- InnoDB lock waits (human-readable) SELECT * FROM sys.innodb_lock_waits\G -- Tables with full table scans (potential buffer pool polluters) SELECT object_schema, object_name, rows_full_scanned, latency FROM sys.schema_tables_with_full_table_scans ORDER BY rows_full_scanned DESC LIMIT 10;
← 上一章：架构全景 返回目录 下一章：源码阅读 →