深入剖析MySQL存储架构，索引结构，日志机制，事务提交流程

发布于：2025-06-09 ⋅ 阅读:(21) ⋅ 点赞:(0)

一、MySQL存储架构（InnoDB）

1. MySQL 整体概览

+----------------------------+
|      客户端/连接层         |
+----------------------------+
|       SQL层（解析/优化）  |
+----------------------------+
|       存储引擎层（InnoDB） |
+----------------------------+
|       文件系统/物理存储    |
+----------------------------+

  • 连接层：管理连接、权限、线程池等。

  • SQL层：包含 SQL 解析器、查询优化器、执行器等。

  • 存储引擎层：真正负责数据的存储、读写、索引维护，如 InnoDB、MyISAM。

  • 物理存储层：最终以文件形式存储在磁盘中，涉及 Page、Block、Redo/Undo 日志文件等。

2. InnoDB 存储引擎架构

InnoDB 是 MySQL 默认的事务型存储引擎，支持 ACID、MVCC、多版本并发控制、行级锁。

1. InnoDB 架构图

+----------------------+
| InnoDB 存储引擎层    |
+----------------------+
| Buffer Pool 缓冲池   |
| Background Threads   |
| Adaptive Hash Index  |
| Doublewrite Buffer   |
| Redo Log Buffer      |
| Change Buffer        |
| Undo Tablespace      |
+----------------------+

3. InnoDB 核心组件详细剖析

1. Buffer Pool（缓冲池）

  • 用于缓存 磁盘上的数据页（Data Page）、索引页、Undo页、Insert Buffer 页 等。

  • 避免频繁磁盘 I/O。

  • 页大小默认为 16KB。

  • LRU 算法管理淘汰，结合 midpoint insertion strategy 优化读写热点。

2. 数据页结构（Page）

InnoDB 以页（Page）为最小磁盘读写单位，每页大小 16KB，页类型包括：

  • Index Page（索引页）

  • Undo Page（回滚页）

  • Insert Buffer Page

  • LOB Page（大字段）

  • System Page（系统页）

一个表的数据被划分为多个页，这些页组织为 B+ 树。

3. Redo Log（重做日志）

  • 作用：保证崩溃恢复（Crash Recovery），实现 WAL（Write Ahead Logging）。

  • 格式：

    • ib_logfile0, ib_logfile1, ...（循环使用）

  • 流程：

    • 修改先写入 redo log buffer → 持久化到磁盘 → 数据页后续刷新

即使数据未写入数据页，只要 redo 日志存在，重启后可以恢复数据。

4. Undo Log（回滚日志）

  • 作用：

    • 支持事务回滚

    • 实现 MVCC 版本控制（快照读）

  • 存储位置：

    • 系统表空间（ibdata）或独立 undo 表空间。

  • 每次更新/删除时生成一条 undo 记录。

5. Doublewrite Buffer（双写缓冲）

  • 作用：防止 “部分页写失败” 导致数据损坏。

  • 流程：

    • 页先写入 doublewrite buffer → 再批量刷入数据文件

  • 分布在共享表空间中，大小为 128 页。

6. Change Buffer（变更缓冲）

  • 用于缓存二级索引的插入、更新、删除操作（主键索引不会进入）。

  • 适用于冷数据场景，避免频繁 I/O。

  • 在查询该索引页或刷脏时才应用。

4. 文件结构剖析

1. 数据文件（.ibd）

  • 每个表对应一个 .ibd 文件（独立表空间启用时）。

  • 存储数据页、索引页。

2. 系统表空间（ibdata1）

  • 存放：

    • undo log（未分离时）

    • insert buffer

    • 系统表数据

  • 如果未开启 innodb_file_per_table，数据也存储于此。

3. 重做日志文件（ib_logfileX）

  • 记录 redo log。

  • 通过 innodb_log_file_size 和 innodb_log_files_in_group 控制大小和数量。

4. 表定义文件（.frm / .ibd）

  • .frm（旧版本）记录表结构

  • MySQL 8 后使用数据字典替代 .frm

5. 索引结构剖析

1. 主键索引（Clustered Index）

  • 数据与主键一体存储，叶子节点存放整行数据。

  • 优化主键选择可显著影响性能。

2. 二级索引（Secondary Index）

  • 叶子节点存放主键值而非整行数据，称为回表机制。

  • 支持覆盖索引优化（索引包含查询字段时无需回表）。

6. 事务与并发控制机制

1. MVCC 多版本并发控制

  • 利用 undo log 实现。

  • 每行记录有 trx_id, roll_ptr, row_id 等隐藏字段。

2. 隔离级别与实现

隔离级别 是否使用 MVCC 是否加锁 说明
Read Uncommitted 否 否 读取未提交数据
Read Committed 是 否 每次读取最新版本
Repeatable Read 是 否 读取一致快照（默认）
Serializable 否 是 加锁，强一致

7. InnoDB 后台线程机制

  • Master Thread：协调 flush、merge、purge 等操作。

  • Purge Thread：清理无用 undo 记录。

  • Page Cleaner Thread：将脏页刷入磁盘。

  • I/O Threads：负责后台异步 I/O（读写请求、刷新日志等）。

8. 查询执行与存储交互流程

1. 接收SQL → 解析器解析
2. 优化器生成执行计划
3. 执行器根据计划访问存储引擎
4. InnoDB：
   - 若页在 buffer pool，直接访问；
   - 否则从磁盘读取页进入 buffer pool；
5. 若修改数据：
   - 写入 redo log（先）
   - 修改 buffer pool 的数据页
   - 后台刷脏（异步）

9. 小结

模块 作用 底层机制
Buffer Pool 加速读写，减少磁盘 I/O LRU、预读机制
Redo Log 崩溃恢复 WAL
Undo Log MVCC 与事务回滚支持 多版本数据
Doublewrite 防止页写入失败 二次写入机制
Change Buffer 二级索引写入优化 延迟合并
索引结构 数据组织 B+树
多线程机制 并发调度与后台任务 线程池

二、深入剖析 MySQL InnoDB 页结构（Page Structure） 

1. 页（Page）基本概念

  • InnoDB 是以页（Page）为单位来管理磁盘数据的。

  • 默认页大小为 16KB（16384 字节），可配置为 4KB、8KB、32KB、64KB。

  • 每个表、索引的数据都由多个页组成。

  • 页由段（Segment）管理，段由多个页组成。

2. InnoDB 页类型分类

页类型编号 页类型名 说明
0 FIL_PAGE_INDEX B+树的叶子或非叶子节点页（索引）
2 FIL_PAGE_UNDO_LOG Undo 日志页
3 FIL_PAGE_INODE 段空间页（管理 extent 分配）
4 FIL_PAGE_IBUF_FREE_LIST Change Buffer 页
5 FIL_PAGE_TYPE_ALLOCATED 空闲页
17855 FIL_PAGE_COMPRESSED 压缩页

我们重点分析最常见的：索引页（FIL_PAGE_INDEX）结构。

3. 索引页（FIL_PAGE_INDEX）结构

一页（16KB）大致结构如下图所示：

+-------------------------+ 0
| File Header             | 38 bytes
+-------------------------+ 38
| Page Header             | 56 bytes
+-------------------------+ 94
| Infimum & Supremum      | 26 bytes（13+13）
+-------------------------+ 120
| User Records            | 可变
+-------------------------+ -
| Free Space              | 可变
+-------------------------+ -
| Page Directory          | 2 * N bytes
+-------------------------+ -
| File Trailer            | 8 bytes
+-------------------------+ 16384

4. 各部分详细解析

1. File Header（文件头）- 38 Bytes

位于页起始位置，记录页的一些通用元数据：

字节范围 字段名 含义
0-1 FIL_PAGE_PREV 上一页页号（双向链表）
2-3 FIL_PAGE_NEXT 下一页页号
4-7 FIL_PAGE_LSN 日志序列号（用于恢复）
24-27 FIL_PAGE_TYPE 页类型编号（如 0 表示索引页）
38字节共

2. Page Header（页头）- 56 Bytes

描述页内数据的组织方式：

字节范围 字段名 含义
0-1 PAGE_N_DIR_SLOTS Page Directory 的 slot 数量
2-3 PAGE_HEAP_TOP 当前堆数据使用到的顶端偏移量
4-5 PAGE_N_HEAP 当前行记录数量（含Infimum/Sup）
6-7 PAGE_FREE 空闲链表首指针
8-9 PAGE_GARBAGE 可回收记录的数量（已删除）
26-27 PAGE_LEVEL 当前页在B+树的层级，0表示叶子页
28-31 PAGE_INDEX_ID 索引ID

3. Infimum & Supremum Records - 13+13 Bytes

这两个是虚拟记录：

名称 说明
Infimum 所有记录中最小的值
Supremum 所有记录中最大的值

它们并不代表真实数据，用于页面遍历和比较。

4. User Records（记录区域）

用户插入的真实记录，存放在 堆（heap）结构中。

每条记录的结构：
+----------------------+------------------------+
| Record Header        | Variable-length fields |
+----------------------+------------------------+
Record Header（变长记录头，5-6字节）：
字段名 含义
delete_flag 标记记录是否被删除（1bit）
min_rec_flag 是否最小记录（用于非叶子页）
n_owned 本组中有多少条记录（每组小于8）
heap_no 堆中记录编号（堆排序）
record_type 普通行/Infimum/Supremum/节点指针
next_record 下一个记录相对偏移

5. Free Space（空闲空间）

  • 当插入新记录时，使用此区域。

  • 删除记录后形成 gap，会合并/整理形成新的 free space。

6. Page Directory（页目录）

  • 存放用户记录的slot指针。

  • 每个 slot 记录一个 group 的最后一条记录的偏移位置。

  • 每组最多 8 条记录，方便快速查找（类二分）。

7. File Trailer（8 字节）

  • 校验页的完整性（校验和 + LSN）。

  • 用于崩溃恢复、页对比。

5. 页的插入 & 删除操作过程

插入过程：

  1. 在 Free Space 区域分配空间；

  2. 构造 record header + 数据；

  3. 插入到堆中，更新指针；

  4. 如果是组尾记录，更新 Page Directory；

  5. 更新 Page Header 的 N_HEAP、HEAP_TOP 等字段。

删除过程：

  1. 设置 delete_flag；

  2. 标记为垃圾记录，加入垃圾列表；

  3. 回收在空闲页中复用；

  4. 不立即物理删除，除非触发 purge。

6. 可视化示意图

[ File Header (38B) ]
[ Page Header (56B) ]
[ Infimum ] ---> 最小虚拟行
[ Supremum ] ---> 最大虚拟行
[ User Record 1 ]
[ User Record 2 ]
    ...
[ Free Space ]
[ Page Directory ]
[ File Trailer (8B) ]

7. 页结构优化建议

场景 建议
大字段 使用 LOB 页避免主页膨胀
热点更新页 合理选择主键避免页分裂
插入顺序紊乱 考虑逻辑主键、自增主键优化插入
MVCC堆积 定期 purge 或重建表（OPTIMIZE）

8. 工具推荐

可用于分析页结构的工具：

  • innodb_ruby：Ruby 编写，分析 .ibd 文件。

  • MySQL Page Inspector

  • Percona Toolkit (pt-ioprofile)

  • hexdump + 自己解析

三、深入剖析 InnoDB 的页分裂（Page Split）触发机制 与 Undo 合并策略

1. 页分裂（Page Split）机制深度剖析

页分裂发生在 InnoDB 的 B+ 树数据页（Leaf Page）或目录页（非叶子页）满了之后，需要插入新记录或维护索引结构时。常见于：

  • INSERT 时页空间不足

  • UPDATE 导致记录变长，占满页空间

  • DELETE 后未及时合并空间

✅ 1. 触发页分裂的典型场景

✅ 场景 A：Insert 插入新记录

  • 页面剩余空间不足（含记录、槽位、页目录等）

  • 插入点在中间位置 → 发生中间分裂（Mid-point split）

  • 插入点在页尾 → 右分裂（Right split）

✅ 场景 B：Update 变更导致行变长（变更变长字段）

  • 原记录为变长字段（如 VARCHAR、TEXT）

  • 更新后值变长，无法原地覆盖

  • InnoDB 会执行：

    • Insert 新记录（新版本）

    • 标记旧记录为删除

    • 如果页已满 → 页分裂

✅ 2. 页分裂流程图（逻辑）

Insert or Update →
   当前页空间不足？
     → 是 → 计算 split 位置
         → 分裂出新页（Page X → Page X, Y）
         → 调整 B+ 树父节点
         → 可能递归触发父节点分裂
     → 否 → 原页直接插入/修改

✅ 3. 页分裂代价与影响

  • 增加 I/O 和 CPU：

    • 需要新建页、调整 B+ 树索引结构、写 redo log；

  • 容易产生页碎片：

    • 若记录偏移或中间插入频繁；

  • 影响查询性能：

    • 查询路径变深，减少页缓存命中率；

  • 页合并不主动发生：

    • 删除大量数据后不会立即回收空间

✅ 4. 如何避免页分裂

  • 插入数据 尽量顺序写入（按主键自增）

  • 合理设置 innodb_fill_factor

  • 变长字段避免频繁扩展（varchar(200) → varchar(4000) 会有巨大开销）

  • 使用 OPTIMIZE TABLE 合并碎片

2. Undo Log 合并策略（Purge 策略）

✅ 1. Undo Log 简述

InnoDB 的 Undo Log 用于：

  • 事务回滚

  • MVCC 构造旧版本数据（快照读）

每次 UPDATE/DELETE 都会生成 Undo Log。

✅ 2. Undo Log 清理场景（Purge）

Undo Log 并不会在事务提交后立刻删除。需要满足两个条件：

条件 说明
事务已提交 不再需要用于回滚
没有其他事务引用旧版本 不再需要用于 MVCC

一旦满足上述条件，Purge 线程将执行 合并 & 回收 Undo Log 记录。

✅ 3. Undo 合并策略核心逻辑

for each undo record:
    if (trx 已提交 && 无快照事务访问该版本):
        清除 undo record
        回收 undo segment 空间

底层机制：

  • Purge 线程由后台 srv_purge_coordinator_thread 管理；

  • Undo 日志写入 Undo Tablespace；

  • 每个数据页中有 DB_ROLL_PTR 指针指向 undo 记录；

  • 清理时会：

    • 标记 undo 可回收；

    • 移除 undo 页引用；

    • 释放 undo 页或 segment。

✅ 4. Undo 合并触发机制

  • 主动触发：

    • 大量 DML（InnoDB 自动判断 Undo 膨胀）

  • 后台周期性任务：

    • innodb_purge_threads 个线程清理

  • 手动控制（高版本支持）：

    • 设置 innodb_max_purge_lag

    • 或用 PERSIST 参数控制阈值

✅ 5. Undo Log 不清理的风险

问题 影响
Undo Log 积压 Undo tablespace 不断增长
行版本过多 构造 MVCC 成本升高，查询变慢
空间膨胀 SSD 容量/磁盘 I/O 受压
查询历史版本变慢 二级索引与主键版本一致性差导致性能下降

3. 页分裂 + Undo 的组合风险场景

⚠️ 高频 UPDATE 的热点行：

  • 每次都触发生成新版本行 + Undo Log

  • 原页可能空间不足 → 频繁页分裂

  • Undo 堆积 → Purge 不及时，造成 “版本雪崩”

解决策略：

优化手段 效果
避免更新热点字段 减少页分裂和 undo 写入
合理设计字段长度 降低记录变长的风险
控制大事务、定期提交 避免长事务堆积 undo
调整 innodb_purge_threads 提升 purge 线程并发

4. 小结图表：页分裂 & Undo 合并对比

维度 页分裂 Undo 合并
触发时机 插入/更新导致页空间不足 事务提交后 + 无事务访问旧版本
是否自动 是 是（有后台 purge）
操作对象 数据页（索引页） undo log 页
是否影响性能 是，增加写放大 是，MVCC 增加查询成本
控制参数 无明显参数，靠设计优化结构 innodb_max_purge_lag 等参数

四、深入剖析 MySQL InnoDB 中更新数据的整个完整流程

我们以：

UPDATE user SET age = 30 WHERE id = 100;

为例，从 SQL 接收 → 存储引擎 → 日志写入 → 缓存修改 → 持久化落盘 的全过程进行逐层深度分析。

1. 整体流程总览图

客户端SQL
   ↓
连接层（连接管理、权限验证）
   ↓
SQL层（解析器 → 优化器 → 执行器）
   ↓
存储引擎（InnoDB）调用接口
   ↓
InnoDB 层执行更新流程：
   - 定位页（索引查找）
   - 页进入 Buffer Pool
   - 生成 Undo Log
   - 写入 Redo Log Buffer
   - 修改 Buffer Pool 中的页
   - 标记为脏页
   - 后台异步刷盘（Redo Log & 数据页）

2. 详细流程剖析（分阶段）

阶段一：SQL接收与语法解析

✅ SQL 解析与优化

  1. 语法解析器（Parser）

    • 将 SQL 文本解析为语法树。

  2. 查询优化器（Optimizer）

    • 基于 id 走主键索引（Clustered Index）。

    • 生成执行计划。

阶段二：执行器执行 → 调用存储引擎 API

  • 执行器调用 InnoDB 接口：

    handler->update_row()

阶段三：InnoDB 执行更新核心流程

✅ 第 1 步：定位数据页（B+树查找）

  • 根据 id=100，在 聚簇索引（Clustered Index）B+ 树中定位对应页（Page）。

  • 若页在 Buffer Pool 中：直接访问。

  • 否则触发 磁盘 I/O，将页从 .ibd 文件读入内存。

✅ 第 2 步：记录 Undo Log（生成回滚日志）

用于：

  • 支持事务回滚。

  • 支持 MVCC（快照读）。

内容包括：

字段 值
表名 user
行主键 id=100
修改前值 age=28
操作类型 UPDATE
trx_id 当前事务 ID

  • Undo Log 会写入 Undo Segment（在 Undo Tablespace 中）。

✅ 第 3 步：记录 Redo Log（WAL）

用于崩溃恢复（Crash Recovery）。

  • 记录“将 age 字段从 28 改为 30”的修改意图：

    UPDATE page_id=X offset=Y field=age old=28 new=30

  • 写入 Redo Log Buffer，标记 LSN（Log Sequence Number）。

  • 暂时不会立刻刷盘，由后台线程刷盘。

注：即使数据页未落盘，只要 Redo 存在就能恢复（WAL）。

✅ 第 4 步：修改 Buffer Pool 中的数据页

  • 更新 age 字段的值。

  • 修改的是 内存中的页结构。

  • 页头字段如 Page_LSN、N_HEAP 等也随之修改。

  • 页被打上脏页标记（Dirty Page）。

✅ 第 5 步：更新自带隐藏字段

每行记录都会有隐藏字段：

字段 说明
DB_TRX_ID 最后修改该行的事务 ID
DB_ROLL_PTR 指向 Undo Log 的指针
DB_ROW_ID 自动递增行 ID（若无主键）

此时 DB_TRX_ID 会更新为当前事务 ID，DB_ROLL_PTR 指向刚才写入的 Undo Log。

阶段四：事务提交阶段

✅ Step 6：COMMIT 执行

  • 将 Redo Log Buffer 持久化到磁盘（ib_logfile0 等文件）。

  • 顺序：

    1. Redo Log Buffer → Redo Log File
2. 写入 redo log 的事务被标记为提交完成
3. 刷新 binlog（若启用）
✅ Step 7：二阶段提交（若开启 binlog）
1. Prepare 阶段：写 redo log prepare 状态（未 commit）
2. 写 binlog
3. Commit 阶段：更新 redo log 为 commit 状态

这样 MySQL 就能实现崩溃恢复和 binlog 一致性（XA）。

阶段五：后台刷盘

更新事务提交后，数据实际仍可能只在内存中（Buffer Pool）。后台线程会异步处理：

✅ 刷 Redo Log：

  • Log Flusher Thread 周期性写 redo log 到磁盘。

  • 保证 WAL 机制成立。

✅ 刷脏页（Dirty Page）：

  • Page Cleaner Thread 会周期性将 Buffer Pool 中的脏页刷到 .ibd 文件。

  • 刷盘位置可不等于事务提交位置（延迟刷盘以提升吞吐）。

✅ 清理 Undo Log：

  • 已提交事务生成的 undo log 会在 Purge Thread 清理后物理删除。

3. 图示总结：更新数据的全流程

+----------------------+
|      SQL层           |
+----------------------+
        ↓
+----------------------+        +------------------+
| 查询执行器 → 调用引擎 | ----> | B+树定位主键页     |
+----------------------+        +------------------+
        ↓                               ↓
+--------------------------+     +-------------------------+
| 页进入 Buffer Pool       |<----|  磁盘 I/O（如未命中）    |
+--------------------------+     +-------------------------+
        ↓
+--------------------------+
| 写 Undo Log（支持回滚） |
+--------------------------+
        ↓
+--------------------------+
| 写 Redo Log（支持恢复） |
+--------------------------+
        ↓
+--------------------------+
| 修改内存页（Buffer Pool）|
+--------------------------+
        ↓
+--------------------------+
| 提交事务 COMMIT         |
|  - Redo 持久化           |
|  - Binlog 写入           |
+--------------------------+
        ↓
+--------------------------+
| 后台线程异步刷脏页       |
+--------------------------+

4. 扩展：若更新的数据页不存在于 Buffer Pool

  • 会发生 页读入（Page Load）。

  • 在多并发时，还涉及 页锁（X Lock）。

  • 在事务隔离级别为 RR 时，可能读取的是 旧版本行（Undo构造）。

5. 小结

模块 关键作用
Buffer Pool 提高性能，缓存热页
Redo Log 崩溃恢复，保证持久性
Undo Log 实现 MVCC 与事务回滚
Dirty Page 标识内存中已改未刷的新数据页
Page Cleaner 异步刷盘，降低写放大
Binlog + Redo XA 保证主从/恢复一致性

五、剖析MySQL底层对于update 和 insert、delete 的不同点

在 MySQL InnoDB 引擎中，UPDATE、INSERT 和 DELETE 都属于 DML 操作，但三者在底层执行逻辑上存在较大差异。我们从以下几个维度进行深入对比分析：

1. 核心差异总览

对比维度 INSERT UPDATE DELETE
操作行为 添加新行 修改已存在行 删除已存在行
B+ 树定位 不需要定位已有行（除唯一性校验） 需要精准定位待修改记录（通过索引） 需要精准定位待删除记录（通过索引）
是否涉及旧版本 否 是（需要记录旧值用于回滚、MVCC） 是（需要保留行数据构造历史版本）
Undo Log 类型 INSERT Undo（回滚时删除新行） UPDATE Undo（回滚时还原旧值） DELETE Undo（回滚时恢复整行）
Redo Log 内容 插入页位置和新值 页偏移位置、修改字段和前后值 标记删除的记录
Buffer Pool 写入 向页尾插入记录 修改页内记录字段 标记记录为已删除
脏页生成 是（插入数据页） 是（修改数据页） 是（修改页中的 delete_flag）
Purge 清理 无需清理（新行已写） 有（清理旧版本） 有（清理整行记录）

2. 详细底层流程对比

✅ 1. INSERT 流程

SQL：INSERT INTO user(id, name, age) VALUES (1, 'Tom', 18);

底层流程：

  1. 判断主键/唯一索引冲突（需要查索引页）；

  2. 找到合适的页，在页尾插入新记录；

  3. 写入 Redo Log（插入新值）；

  4. 写入 Undo Log（用于回滚：删除新插入的行）；

  5. 页入 Buffer Pool，标记为脏页；

  6. COMMIT 时刷 redo log。

⚠️：Undo Log 只在事务未提交前有效。提交后即 purge 可忽略。

✅ 2. UPDATE 流程

SQL：UPDATE user SET age = 30 WHERE id = 1;

底层流程：

  1. 使用索引定位到具体页、具体记录；

  2. 记录原值 → 写入 Undo Log（用于回滚 & MVCC）；

  3. 写入 Redo Log（记录页位置 + 修改字段 + 新旧值）；

  4. 修改 Buffer Pool 中页的数据；

  5. 更新 DB_TRX_ID、ROLL_PTR 等元信息；

  6. 标记为脏页，等待后台 flush；

  7. COMMIT 时 redo log 写磁盘。

🚨：由于涉及版本控制，Undo Log 会被其他事务用于构造旧版本（MVCC）。

✅ 3. DELETE 流程

SQL：DELETE FROM user WHERE id = 1;

底层流程：

  1. 使用索引定位到数据页；

  2. 写 Undo Log（存储整行数据用于回滚/MVCC）；

  3. 设置该记录的 delete_flag；

  4. 修改 Buffer Pool 页（标记删除）；

  5. 写 Redo Log（删除意图）；

  6. 后台 purge 线程最终物理删除记录（当无人再访问旧版本）。

❗：DELETE 是“逻辑删除”，物理空间并不会立即释放。

3. Undo Log 类型差异

操作类型 Undo 类型 Undo 内容
INSERT Insert Undo 插入记录主键（回滚时执行 DELETE）
UPDATE Update Undo 原始字段值（回滚时还原）
DELETE Update Undo 被删除行所有字段值（回滚时重新插入）

4. MVCC 行为差异（可见性）

操作类型 对其他事务影响（快照读）
INSERT 不可见（事务未提交前）
UPDATE 读取旧版本（通过 Undo 构造）
DELETE 读取旧版本（通过 Undo 构造）

5. 刷盘 & Redo Log 写入差异

操作 Redo Log 示例
INSERT insert page X offset Y: record(Z)
UPDATE update page X offset Y: age 28→30
DELETE delete page X offset Y

注意：

  • INSERT redo log 是新增数据；

  • UPDATE redo log 是 delta（变更前后对比）；

  • DELETE redo log 是标记记录删除。

6. 性能与空间对比

操作类型 性能影响 空间占用
INSERT 顺序插入性能最好（页合并少） 新增记录，占用更多页
UPDATE 高并发下性能受版本管理影响 多版本 + Undo 空间占用增加
DELETE 删除后并不立即回收空间 Undo 日志 + 残留记录空间等待清理

7. 小结

场景 推荐操作
大量新增操作 尽量顺序 INSERT
高并发写场景 避免频繁 UPDATE 热点字段
逻辑删除后定期清理历史数据 DELETE 后定期 OPTIMIZE TABLE
想保留历史版本 使用 UPDATE + MVCC

六、深入剖析 InnoDB 的 MVCC多版本并发控制的实现机制

1. MVCC 是什么？

MVCC 的核心目的是 在不加锁的前提下实现一致性读（Consistent Read），从而：

  • 保证数据读取的一致性（快照读）

  • 实现事务之间的并发控制

  • 提高系统吞吐量（避免读写冲突加锁）

InnoDB 实现的是乐观并发控制机制，即：允许多个事务同时读写，只在提交阶段做冲突检测。

2. InnoDB MVCC 核心机制：依赖三大组件

✅ 1. 行隐藏字段

每一行记录底层都含有两个隐藏字段（物理存储在记录中）：

字段名 含义
DB_TRX_ID 最近一次修改该记录的事务 ID
DB_ROLL_PTR 指向 Undo Log 的指针，用于构造旧版本（历史版本链） 
逻辑示意：
[最新版本行] ← DB_ROLL_PTR ← Undo 版本1 ← Undo 版本2 ...

✅ 2. Undo Log（撤销日志）

当你执行 UPDATE 或 DELETE 操作时：

  • 原记录不会直接删除，而是写入 Undo Log 中；

  • Undo 存放 变更前的记录内容 + 主键；

  • Undo 按事务顺序串联形成一条版本链（version chain）；

这条链可以被查询使用，从而构造出任意时间点的数据快照。

✅ 3. Read View（读视图）

  • 每个事务在执行第一个 一致性读（SELECT）时，创建自己的 Read View；

  • Read View 记录当前系统中活跃的事务 ID 列表（活跃读写事务）；

  • 查询时用来判断某个版本对当前事务是否可见。

3. MVCC 的读取流程（核心）

✅ 事务执行 SELECT 查询的流程如下：

  1. 当前事务生成一个 ReadView，包含以下信息：

    • 当前最大的事务 ID（TRX_ID_MAX）

    • 活跃事务 ID 列表（正在执行、未提交）

  2. 每读取一条记录时，InnoDB 判断该记录是否可见：

    • 查看记录的 DB_TRX_ID

    • 与 ReadView 中活跃事务比较

  3. 若该版本不可见，则追溯 DB_ROLL_PTR 指向的 Undo Log，尝试找到“可见版本”

  4. 若追溯后仍没有找到可见版本，则该记录“对当前事务不可见”

✅ 判断可见性的标准

判断条件 可见性
记录的 DB_TRX_ID < ReadView 中最小活跃事务 ID ✅ 可见
记录的 DB_TRX_ID 属于活跃事务 ID 列表 ❌ 不可见
记录的 DB_TRX_ID > 当前最大事务 ID（还未存在） ❌ 不可见

✅ 举个例子

假设当前有如下事务状态：

事务 事务 ID 状态
事务A 100 已提交
事务B（你） 105 正在运行
事务C 110 正在运行
事务D 115 正在运行

你（事务B）在 105 创建了 ReadView，活跃事务列表是 [105, 110, 115]，最大事务 ID 是 120：

  • 读到一条记录的 DB_TRX_ID=100（事务A提交时写入） → 可见（已提交、早于你）；

  • DB_TRX_ID=110（事务C还没提交） → 不可见；

  • DB_TRX_ID=107（新插入、但尚未提交） → 不可见；

  • DB_TRX_ID=122（以后事务插入的） → 不可见；

4. 不同 SQL 操作下的 MVCC 应用

操作类型 MVCC 是否参与 说明
SELECT ✅ 一致性读：通过版本链获取快照
SELECT ... LOCK IN SHARE MODE ❌（加锁读） 直接读取当前版本，加共享锁
SELECT ... FOR UPDATE ❌（加锁读） 加排他锁，不走 MVCC
INSERT ❌ 无需读取旧版本，不走 MVCC
UPDATE ✅ 生成 Undo 旧版本，保留可回滚和供其他事务可见
DELETE ✅ 生成 Undo 被删除的版本
COMMIT/ROLLBACK ✅ 控制版本的可见性，清理 Undo（由 Purge 完成）

5. MVCC 与事务隔离级别关系

隔离级别 是否使用 MVCC 可见性影响
Read Uncommitted ❌ 可以读到未提交事务的数据（脏读）
Read Committed ✅ 每次读都创建新的 ReadView，能看到新提交的数据
Repeatable Read ✅ 一个事务内只生成一次 ReadView，快照固定
Serializable ❌（使用锁） 全部加锁，完全串行执行

🔹 InnoDB 默认隔离级别是 Repeatable Read + MVCC

6. 性能优化与注意点

问题 原因 解决方案
Undo log 越积越多 长事务未提交，旧版本无法清除 控制事务时长，合理设置 innodb_max_purge_lag
二级索引版本不一致 二级索引不存 Undo 指针，仅主键行有版本链 回表查询主键获取版本一致性
大字段频繁变更导致 Undo 放大 TEXT/BLOB 修改生成大 Undo 记录 使用 ROW_FORMAT=dynamic，避免频繁变更大字段

7. 总结：MVCC 实现的核心逻辑图

           ┌──────────────┐
           │ 当前事务创建 ReadView │
           └────┬─────────┘
                ↓
     读取记录 → 比较 DB_TRX_ID
                ↓
      ┌─────────────┬──────────────┐
      │ 可见版本     │ 不可见 → 追溯 Undo  │
      └─────────────┴──────────────┘
                ↓
      返回快照数据 or 不可见

七、深入剖析 MySQL InnoDB 的索引机制和底层数据结构

1. 索引类型分类总览

InnoDB 索引从逻辑上可分为两大类：

类型 子类 说明
主索引 聚簇索引（Clustered） 按主键顺序存储数据本身。每张 InnoDB 表只能有一个
辅助索引 二级索引（Secondary） 存储主键指针（不含行数据）用于回表查询

✅ 主索引与行数据耦合，二级索引仅包含索引列 + 主键（即 RowID）指针。

2. 核心索引结构：B+ 树

InnoDB 使用的是 B+ 树结构（注意不是 B 树），原因在于其高扇出（高阶）特性，适合磁盘 IO 优化：

✅ B+树的结构特点

特性 描述
所有数据存储在叶子节点 非叶子节点仅用于查找导航，不存实际数据
节点间使用双向链表连接 支持范围查询、顺序扫描非常高效
每个页称为一个节点（默认 16KB） 每个节点可存储多个 key-ptr 对
高扇出（fan-out） 减少树高度，提高查找效率

3. 聚簇索引（Clustered Index）

InnoDB 表的主键索引就是聚簇索引。

✅ 特点：

  • 数据行存储在 B+ 树叶子节点中；

  • 叶子节点按主键顺序排序；

  • 整张表的数据即构建在主键 B+ 树上。

✅ 举例：

CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

结构示意：

非叶子节点：       [5]         [10]         [15]
                 /   \        /   \         ...
叶子节点：   [1,2,3,4]  [5,6,7,8,9]  [10,...]  ← 含整行记录（id,name,age）

4. 二级索引（Secondary Index）

任何非主键上的索引，InnoDB 都构建为二级索引。

✅ 特点：

  • B+ 树叶子节点中只保存：索引列 + 主键值（即 Row ID）；

  • 查询时需回表（用主键回到聚簇索引树中找完整记录）；

✅ 举例：

CREATE INDEX idx_name ON user(name);

索引项结构：

叶子节点：
[
 ("Alice", 3),
 ("Bob", 1),
 ("Charlie", 2)
]
→ 通过主键 ID 到聚簇索引中回表

5. 索引项的数据结构（页结构内）

InnoDB 每个节点本质上是一个页（Page，16KB），其物理结构如下：

✅ 页结构层级

┌────────────────────────────┐
│ File Header (38字节)       │
├────────────────────────────┤
│ Page Header (56字节)       │
├────────────────────────────┤
│ Infimum/Supremum (26字节)  │ ← 最小/最大记录，用于边界处理
├────────────────────────────┤
│ User Records (变长)        │ ← 索引记录区域，真正存储 key/value
├────────────────────────────┤
│ Page Directory (变长)      │ ← 快速定位记录位置的槽区
├────────────────────────────┤
│ Free Space                 │ ← 插入新记录使用
├────────────────────────────┤
│ File Trailer (8字节)       │ ← 校验和+页完整性验证
└────────────────────────────┘

✅ 每条记录的结构（Compact Format）

字段 含义
Header 变长字段指针、null标志、删除标记等
索引字段（主键/辅助键） 排序依据
RowID（辅助索引） 指向主索引的主键值
事务 ID/Undo PTR MVCC 用于构造版本链

6. InnoDB 索引维护机制（动态调整）

✅ 1. 插入时维护排序（按键值）

  • 插入数据时自动找到对应页；

  • 若页满，触发 页分裂（Page Split）；

  • 更新 B+ 树父节点指针，可能递归分裂。

✅ 2. 删除数据触发 页合并（Merge）

  • 删除页中数据后若剩余少量记录；

  • 可与相邻页合并，减少碎片；

  • 同样递归调整 B+ 树结构。

7. 自适应哈希索引（Adaptive Hash Index, AHI）

InnoDB 为了提升热点查询性能，会自动构建内存中的哈希索引：

特性 描述
由 InnoDB 自动维护 不需要用户干预
哈希范围：前缀或整行匹配 不适合范围查询
基于 B+ 树访问频率构建 高频查询路径自动加速
构建在 Buffer Pool 上 是一种缓存层优化而不是数据结构替代

8. 特殊索引类型说明

索引类型 支持 InnoDB 说明
BTREE ✅ 默认索引类型，所有主/辅索引
HASH ❌（仅 MEMORY 引擎） 不支持范围查询
FULLTEXT ✅（5.6+） 文本匹配
SPATIAL ✅（5.7+） 空间数据（GIS）
R-Tree ✅（空间索引） 专为多维空间数据设计，如经纬度

9. 索引结构与查询性能关系

✅ 覆盖索引（Covering Index）

若查询字段全部在二级索引中，无需回表：

SELECT name FROM user WHERE name = 'Alice';

→ 可直接用 idx_name 查询，无需访问聚簇索引页

✅ 最左前缀原则（Prefix Matching）

复合索引 (a,b,c)：

  • 支持 (a)、(a,b)、(a,b,c)；

  • 不支持 (b)、(b,c) 等跳列查询。

✅ 索引下推（Index Condition Pushdown, ICP）

从 MySQL 5.6 起，过滤条件可在索引页执行，减少回表次数：

SELECT * FROM user WHERE name LIKE 'A%' AND age > 30;

10. 总结：InnoDB 索引机制全景图

InnoDB B+ 树结构：
              [K5]
           /        \
     [K2]             [K8]
    /   \            /    \
[K1 K2] [K3 K4]   [K6 K7] [K8 K9]

聚簇索引：叶子节点保存整行数据
辅助索引：叶子节点保存索引列 + 主键值（回表用）
类型 存储内容 是否回表 是否排序
聚簇索引 行数据 否 是（主键）
辅助索引 索引列 + 主键值 是 是（索引列）
覆盖索引 查询字段全部在索引中 否 是

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布