MySQL 体系架构与存储引擎

概述

MySQL 采用分层架构，Server 层负责连接/分析/优化/执行，存储引擎层负责数据存取。理解这个分层是理解查询执行、锁机制、事务和性能调优的前提。

整体架构

┌─────────────────────────────────────────────┐
│                 Client / Connector            │
├─────────────────────────────────────────────┤
│  Server Layer                                │
│  ┌──────────┐  ┌──────────┐  ┌───────────┐  │
│  │ 连接器    │→ │ 分析器   │→ │ 优化器    │  │
│  │ 权限认证  │  │ 词法语法  │  │ 索引选择  │  │
│  │ 连接管理  │  │ 解析树    │  │ 执行计划  │  │
│  └──────────┘  └──────────┘  └───────────┘  │
│                      ↓                       │
│  ┌──────────────────────────────────────┐    │
│  │           执行器                      │    │
│  └──────────────────────────────────────┘    │
├─────────────────────────────────────────────┤
│  Storage Engine Layer                        │
│  ┌──────────┐  ┌──────────┐  ┌──────────┐  │
│  │ InnoDB   │  │ MyISAM   │  │ Memory   │  │
│  └──────────┘  └──────────┘  └──────────┘  │
└─────────────────────────────────────────────┘

各层职责

组件	职责	关键点
连接器	TCP 握手、身份认证、权限读取、连接管理	wait_timeout（默认 8h）控制空闲连接断开
查询缓存	8.0 已移除	高并发下锁竞争严重，不如外部缓存（Redis）
分析器	词法分析 → 语法分析 → 生成解析树	此阶段报语法错误
优化器	选择索引、决定 Join 顺序、生成执行计划	基于成本模型（CBO），不一定最优
执行器	调用存储引擎接口	SELECT * FROM t WHERE id=10 → 引擎取第一行 → 循环

实战要点：

• 连接器读取的权限在会话期间不会刷新，修改权限需重连
• SHOW PROCESSLIST 观察连接状态，Sleep 连接过多说明连接池配置不当

InnoDB 核心：内存 + 磁盘双架构

┌─────────────────────────────────────────┐
│              Buffer Pool                 │
│  ┌───────────────────────────────────┐  │
│  │ 数据页（默认 16KB）                │  │
│  │ 索引页                            │  │
│  │ Undo 页                           │  │
│  │ 插入缓冲（Insert Buffer）          │  │
│  │ 自适应哈希索引（AHI）              │  │
│  │ 锁信息                            │  │
│  └───────────────────────────────────┘  │
│  LRU 链表管理（young/old 分区）          │
├─────────────────────────────────────────┤
│  Change Buffer | Log Buffer | AHI       │
└─────────────────────────────────────────┘
                      ↕ 异步刷盘
┌─────────────────────────────────────────┐
│              磁盘文件                    │
│  ┌──────────┐ ┌──────────┐ ┌─────────┐ │
│  │ .ibd     │ │ ibdata1  │ │ ib_log  │ │
│  │ 独立表空间│ │ 系统表空间│ │ Redo Log│ │
│  └──────────┘ └──────────┘ └─────────┘ │
└─────────────────────────────────────────┘

Buffer Pool —— 最影响性能的参数

-- 查看 Buffer Pool 状态
SHOW ENGINE INNODB STATUS\G
SHOW STATUS LIKE 'Innodb_buffer_pool_%';

-- 关键参数
-- innodb_buffer_pool_size：建议物理内存的 50%-80%
-- innodb_buffer_pool_instances：多实例减少锁竞争，建议 ≤ 8

调优要点：

• Innodb_buffer_pool_read_requests（内存命中） vs Innodb_buffer_pool_reads（磁盘读取）
• 命中率公式：1 - reads / read_requests，建议 > 99%
• Innodb_buffer_pool_pages_flushed 增长过快 → Buffer Pool 太小或刷盘太激进

三大日志：Redo / Undo / Binlog

日志	层级	内容	作用	写入时机
Redo Log	InnoDB	物理日志：页的修改	crash recovery	事务过程中持续写
Undo Log	InnoDB	逻辑日志：修改前数据	回滚 + MVCC	事务开始前生成
Binlog	Server	逻辑日志：SQL 语句	主从复制 + 时间点恢复	事务提交时写

WAL（Write-Ahead Logging）机制

写操作流程：
1. 修改 Buffer Pool 中的数据页（脏页）
2. 写入 Redo Log Buffer
3. 事务提交时 Redo Log Buffer → 磁盘（innodb_flush_log_at_trx_commit 控制）
4. 写 Binlog（sync_binlog 控制）
5. 后台线程异步将脏页刷回数据文件（Checkpoint）

两阶段提交（2PC）

Redo Log (prepare) → Binlog → Redo Log (commit)

确保 Redo Log 和 Binlog 逻辑一致，崩溃恢复时以此判断事务是否完整。

存储引擎对比

特性	InnoDB	MyISAM	Memory
默认引擎	MySQL 5.5+ 默认	旧版默认	—
事务支持	✅ ACID	❌	❌
行级锁	✅	❌ 表锁	❌ 表锁
外键	✅	❌	❌
崩溃恢复	✅ via Redo Log	❌	❌
MVCC	✅	❌	❌
全文索引	5.6+ 支持	✅	❌
存储方式	表空间（.ibd）	三个文件（.frm/.MYD/.MYI）	内存
使用场景	通用 OLTP	日志/归档（极少使用）	临时表

关键参数速查

# InnoDB 核心
innodb_buffer_pool_size = 8G          # 物理内存 50-80%
innodb_buffer_pool_instances = 8       # 减少竞争
innodb_log_file_size = 1G             # 单个 Redo Log 文件大小
innodb_log_files_in_group = 2          # Redo Log 文件数
innodb_flush_log_at_trx_commit = 1     # 1=最安全, 2=高性能, 0=每秒刷
innodb_flush_method = O_DIRECT         # 绕过 OS 缓存，避免双重缓冲
innodb_io_capacity = 2000              # SSD 建议 2000-4000
innodb_page_size = 16384               # 默认 16KB
innodb_adaptive_hash_index = ON        # AHI 对等值查询有用

# 连接
max_connections = 500
thread_cache_size = 64

# Binlog
sync_binlog = 1                        # 每次事务提交刷盘
binlog_format = ROW                    # ROW 最安全，STATEMENT/MIXED
expire_logs_days = 7                   # Binlog 保留天数

Buffer Pool 深度调优

LRU 链表：Young/Old 分区

Buffer Pool 的 LRU 链表不是简单的单链表，而是分为 Young（热数据）5/8 和 Old（冷数据）3/8 两个区域：

插入新页      Midpoint（37% 位置）
    ↓              ↓
┌──────────────────┬──────────────────────┐
│    Young 区       │      Old 区          │
│   (5/8 热数据)    │    (3/8 冷数据)      │
└──────────────────┴──────────────────────┘
    ← LRU 淘汰方向           新页插入方向 →

这么做解决了全表扫描污染 Buffer Pool 的问题：

1. 新读取的页先放入 Old 区头部（37% 位置）
2. 如果该页在 Old 区被再次访问，且距首次放入的时间超过 innodb_old_blocks_time（默认 1000ms），才升入 Young 区
3. 全表扫描的大量页放入 Old 区 → 短时间内再次被访问（未超 1s）→ 不会升入 Young → 快速被淘汰

-- 查看 LRU 状态
SHOW ENGINE INNODB STATUS\G
-- 搜索 "BUFFER POOL AND MEMORY"
-- Old database pages：Old 区页数
-- Pages made young / not young：晋升/未晋升统计

-- 关键参数
-- innodb_old_blocks_pct = 37          # Old 区占比（OLTP 建议 37%，全表扫描多可调大）
-- innodb_old_blocks_time = 1000       # 晋升等待时间（ms）

实战判断：

• Pages made young 远小于 Pages not young → Old 区很多页一次就被淘汰 → Old 区可能太小
• innodb_old_blocks_time 设太短 → 全表扫描可能污染 Young 区

预读机制（Read-Ahead）

-- InnoDB 两种预读模式：
-- 1. 线性预读（Linear Read-Ahead）：连续访问超过 innodb_read_ahead_threshold 个页（默认 56）
-- 2. 随机预读（Random Read-Ahead）：同一个 Extent（64 页）内有 13 个页被访问
SHOW VARIABLES LIKE 'innodb_read_ahead_threshold';
SHOW VARIABLES LIKE 'innodb_random_read_ahead';  -- 默认 OFF，SSD 环境不推荐

SSD 随机读很快，预读收益有限，有时反而加载无用数据占用 Buffer Pool。innodb_random_read_ahead 建议关闭。

Buffer Pool 预热（生产避坑）

-- 数据库重启后 Buffer Pool 是空的 → 大量磁盘 IO → 性能雪崩
-- 方案 1：MySQL 5.6+ 支持 Dump & Load
-- 关闭前 Dump（记录 Buffer Pool 中页的 space_id + page_no）
SET GLOBAL innodb_buffer_pool_dump_at_shutdown = ON;   -- 默认 ON
SET GLOBAL innodb_buffer_pool_load_at_startup = ON;     -- 默认 ON

-- 手动 dump/load
SET GLOBAL innodb_buffer_pool_dump_now = ON;
SET GLOBAL innodb_buffer_pool_load_now = ON;

-- 查看加载进度
SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

Change Buffer —— 二级索引的写加速器

INSERT/UPDATE 非唯一二级索引时：
1. 索引页在 Buffer Pool 中 → 直接修改
2. 索引页不在 Buffer Pool 中 → 写入 Change Buffer（合并写，延迟刷盘）
   → 后续读操作触发 Merge → 将 Change Buffer 内容应用到磁盘页

适用条件： 只对非唯一二级索引有效（唯一索引需要查重，必须读磁盘）

监控 Change Buffer 效率：

SHOW ENGINE INNODB STATUS\G
-- 搜索 "INSERT BUFFER AND ADAPTIVE HASH INDEX"
-- Ibuf: size 1, free list len 0, seg size 2, 100 merges
-- merges：合并次数
-- merged operations：insert/delete mark/delete 各种操作数
-- discard operations：未合并就被淘汰的操作（越大 → Change Buffer 效果越差）

SHOW VARIABLES LIKE 'innodb_change_buffering';    -- 建议 all（默认）
SHOW VARIABLES LIKE 'innodb_change_buffer_max_size';  -- Buffer Pool 的 25%（默认）

调优建议：

• SSD 环境 → innodb_change_buffer_max_size = 20（SSD 随机读快，Change Buffer 收益较小）
• HDD 环境 → 保持 25%
• discard operations / total operations > 30% → 增大 innodb_change_buffer_max_size

Checkpoint 机制详解

Checkpoint 的作用：将 Buffer Pool 中的脏页刷回磁盘，控制 Redo Log 可覆盖范围。

两种 Checkpoint 类型

类型	触发条件	行为	影响
Sharp Checkpoint	正常关闭数据库	全部脏页刷盘	关闭慢、恢复快
Fuzzy Checkpoint	持续运行中	部分脏页刷盘	对业务影响小

Fuzzy Checkpoint 的四种子类型

1. Master Thread Checkpoint
   每秒/每 10 秒定时刷一批脏页 → 平稳、量小

2. FLUSH_LRU_LIST Checkpoint
   LRU 淘汰时需要 Free Page → 不够 → 强制刷脏页
   innodb_lru_scan_depth = 1024 控制扫描深度

3. Async/Sync Flush Checkpoint
   Redo Log 快满了 → 强制刷脏页（最重要！）
   75% 容量 → Async（异步刷）
   90% 容量 → Sync（同步刷，阻塞写入！）

4. Dirty Page too much Checkpoint
   脏页比例 > innodb_max_dirty_pages_pct（默认 90%）→ 强制刷

Redo Log 水位线监控

-- 查看 LSN 进度（最关键的性能监控点之一）
SHOW ENGINE INNODB STATUS\G
-- 搜索 "LOG"
-- Log sequence number：               当前 LSN（已产生 Redo Log）
-- Log flushed up to：                 已刷盘的 LSN
-- Pages flushed up to：               脏页已刷盘的 LSN
-- Last checkpoint at：                最后一次 Checkpoint LSN

-- 计算方式
-- 未刷盘 Redo Log = sequence - flushed
-- 未刷盘脏页 LSN = flushed - checkpoint
-- 未刷盘总量 = 未刷盘 Redo + 未刷盘脏页

-- 紧急信号：
-- Checkpoint age = sequence - checkpoint > 80% Redo Log 总大小 → 加快刷脏页速度

如果看到 Syncing（同步刷盘）：

SHOW STATUS LIKE 'Innodb_buffer_pool_pages_flushed';       -- 刷脏页总量
-- 快速增长 + Innodb_log_waits > 0 → Redo Log 太小或刷盘太慢
-- 调整：
-- innodb_log_file_size 增大（总大小建议 25%~50% Buffer Pool）
-- innodb_io_capacity 提高（SSD 设 2000-4000）

表空间文件体系

MySQL 数据目录（/var/lib/mysql）
├── ibdata1                     # 系统表空间（DDL 字典/Change Buffer/Undo 旧版/Doublewrite）
├── ib_logfile0、ib_logfile1    # Redo Log 文件
├── mysql.ibd                   # MySQL 系统库表空间
├── undo_001、undo_002          # Undo 表空间（MySQL 8.0 独立）
├── ibtmp1                      # 临时表空间
└── mydb/                       # 用户数据库
    └── orders.ibd              # 独立表空间（innodb_file_per_table=1）

各表空间详解

表空间	内容	关键参数	备注
系统表空间	Change Buffer、DDL 字典（5.7）	innodb_data_file_path = ibdata1:12M:autoextend	8.0 DDL 字典移到 mysql.ibd
独立表空间	每个表的数据+索引	innodb_file_per_table = ON	强烈建议 ON，否则所有表数据存在 ibdata1
Undo 表空间	Undo Log（回滚段）	innodb_undo_tablespaces >= 2	8.0 默认 2 个，支持 truncate
临时表空间	临时表、排序溢出	innodb_temp_data_file_path = ibtmp1:12M:autoextend	重启自动重建
Redo Log	WAL 日志	innodb_log_group_home_dir	循环写

独立表空间的优势

-- innodb_file_per_table = ON（默认启用）
-- 优势：
-- 1. TRUNCATE/DROP 表时立即回收磁盘空间
-- 2. 可针对单表做 OPTIMIZE TABLE
-- 3. 单表损坏不影响其他表
-- 4. 可把单表数据文件放到不同磁盘

-- 查看表空间使用情况
SELECT
    table_schema, table_name,
    ROUND(data_length/1024/1024, 2) AS data_mb,
    ROUND(index_length/1024/1024, 2) AS index_mb,
    ROUND(data_free/1024/1024, 2) AS free_mb
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'sys', 'performance_schema', 'information_schema')
ORDER BY (data_length + index_length) DESC
LIMIT 10;

-- data_free > 0 表示有碎片空间，可以 OPTIMIZE TABLE 回收

Doublewrite Buffer —— 防止页损坏

部分写（Partial Write）问题

数据页 16KB，OS/磁盘写入单位通常 4KB（4 个扇区）

发生 Crash 时：
  [████][████][    ][    ]   ← 只写了 8KB，8KB 是旧数据
  → Redo Log 重放时以为页是完整的 → 校验和不匹配 → 恢复失败

Doublewrite 工作流程

1. 刷脏页之前 → 先写入 Doublewrite Buffer（ibdata1 中 2MB 连续空间，即 128 页）
2. Doublewrite Buffer 写完后 fsync
3. 再写入实际的表空间文件

Crash Recovery 时：
  如果实际表空间页的校验和不通过 → 从 Doublewrite Buffer 恢复完整的页

SHOW VARIABLES LIKE 'innodb_doublewrite';  -- 强烈建议 ON
-- Fusion-io/Intel Optane 等原子写 SSD → 可以关闭以提高性能

刷脏页策略与自适应刷盘

-- 自适应刷盘 —— MySQL 根据 Redo Log 产生速度和脏页比例，动态调整刷盘速率
SHOW VARIABLES LIKE 'innodb_adaptive_flushing';             -- 建议 ON
SHOW VARIABLES LIKE 'innodb_adaptive_flushing_lwm';         -- Redo Log 低水位（默认 10%）
SHOW VARIABLES LIKE 'innodb_flushing_avg_loops';            -- 采样窗口（默认 30）

-- IO 能力设置（决定刷盘速率的上限）
-- innodb_io_capacity       = 200  (HDD) / 2000+ (SSD)
-- innodb_io_capacity_max   = 2000 (HDD) / 4000+ (SSD)
-- innodb_max_dirty_pages_pct = 90   脏页比例上限
-- innodb_max_dirty_pages_pct_lwm = 10  低水位（预刷盘启动点）

-- 监控刷盘状况
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_dirty';        -- 当前脏页数
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_total';
-- 脏页比例 = dirty / total，持续 > 75% → 增大 innodb_io_capacity

自适应哈希索引（AHI）

-- AHI 自动在频繁访问的索引页上构建 Hash 索引，加速等值查询
-- 适用于：热数据 + 等值查询为主的场景
-- 不适用于：范围查询为主、数据随机访问、Join 操作多

SHOW ENGINE INNODB STATUS\G
-- 搜索 "INSERT BUFFER AND ADAPTIVE HASH INDEX"
-- Hash table size / node heap / hash searches/s  → 判断 AHI 效率
-- non-hash searches/s 占比高 → AHI 没起作用，可以考虑关闭

-- 关闭 AHI（高并发写场景减少锁竞争）
-- SET GLOBAL innodb_adaptive_hash_index = OFF;

常见问题 / 坑点

问题	原因	解决方案
innodb_buffer_pool_size 设太大导致 SWAP	超物理内存且未设置 swapiness=0	不超过物理内存 80%，OS 层面禁止 swap
O_DIRECT 下读性能反而下降	关闭了 OS 页缓存预读优势	写密集型建议开启，读密集型可关闭
Binlog 格式 MIXED 导致主从不一致	部分语句混用 ROW/STATEMENT	统一使用 ROW
连接数打满 Too many connections	连接池泄露/未及时释放	SET GLOBAL max_connections 应急 + 排查连接池
表空间文件过大	innodb_file_per_table=1 但长期未 optimize	OPTIMIZE TABLE 或 ALTER TABLE ... ENGINE=InnoDB

关联知识

• MySQL 索引原理与优化 — Buffer Pool 缓存数据页和索引页
• MySQL 事务与锁机制 — Undo Log 是 MVCC 的基础
• MySQL 主从复制与高可用 — Binlog 是主从复制的核心
• MySQL 备份恢复与慢查询优化 — Redo Log 保证 crash recovery

参考资源

• MySQL 8.0 官方文档：https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html
• 《MySQL 技术内幕：InnoDB 存储引擎》—— 姜承尧

学习时间

阶段	时间	备注
初次学习	2026-07-14	架构 + 日志 + Buffer Pool
深入理解	待定	源码级 Buffer Pool LRU、Change Buffer

---

状态: 📖 已掌握 下次复习日期: 2026-08-14