MySQL 完全指南 / 第 3 章：架构原理

第 3 章：架构原理

理解 MySQL 的内部架构，才能写出高效的 SQL、做出正确的调优决策。

3.1 MySQL 整体架构

MySQL 采用分层架构，从上到下分为三层：

┌──────────────────────────────────────────────────┐
│                   连接层 (Connection Layer)        │
│  ┌──────────┐  ┌──────────┐  ┌──────────┐        │
│  │ 客户端    │  │ 连接池   │  │ 认证授权  │        │
│  │ Connector │  │ Thread   │  │ Auth     │        │
│  │           │  │ Cache    │  │          │        │
│  └──────────┘  └──────────┘  └──────────┘        │
├──────────────────────────────────────────────────┤
│                   SQL 层 (SQL Layer)              │
│  ┌────────┐ ┌────────┐ ┌────────┐ ┌────────┐    │
│  │ 解析器  │ │优化器  │ │执行器  │ │缓存    │    │
│  │Parser  │ │Optimizer│ │Executor│ │Cache   │    │
│  └────────┘ └────────┘ └────────┘ └────────┘    │
│  ┌────────┐ ┌────────┐ ┌────────┐               │
│  │管理器  │ │日志    │ │复制   │               │
│  │Manager │ │Logging │ │Repli. │               │
│  └────────┘ └────────┘ └────────┘               │
├──────────────────────────────────────────────────┤
│                存储引擎层 (Storage Engine)         │
│  ┌──────────┐ ┌──────────┐ ┌──────────┐         │
│  │ InnoDB   │ │ MyISAM   │ │ Memory   │  ...    │
│  │(默认)    │ │          │ │          │         │
│  └──────────┘ └──────────┘ └──────────┘         │
│              ↕ 磁盘 / 文件系统                    │
└──────────────────────────────────────────────────┘

SQL 执行流程

一条 SELECT 语句的完整执行过程：

客户端发送 SQL
    ↓
连接器（验证身份、权限）
    ↓
查询缓存（8.0 已移除）
    ↓
解析器（词法分析 → 语法分析 → AST）
    ↓
预处理器（检查表/列是否存在，权限验证）
    ↓
优化器（选择执行计划：全表扫描 vs 索引、JOIN 顺序）
    ↓
执行器（调用存储引擎接口获取数据）
    ↓
返回结果集给客户端

⚠️ 重要变化：MySQL 8.0 移除了查询缓存（Query Cache），因为：

• 表级别失效，任何一个写操作都会清除整个表的缓存
• 在高并发场景下反而成为性能瓶颈
• 建议在应用层使用 Redis 等缓存替代

3.2 存储引擎

3.2.1 存储引擎概览

MySQL 支持可插拔存储引擎，不同引擎有不同的特性：

-- 查看支持的存储引擎
SHOW ENGINES;

-- 查看表使用的引擎
SELECT TABLE_NAME, ENGINE 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'myapp';

-- 修改表引擎
ALTER TABLE my_table ENGINE = InnoDB;

💡 结论：除非有特殊需求，始终使用 InnoDB。

3.2.2 MyISAM 的历史遗留问题

如果你在维护老项目，可能会遇到 MyISAM 表：

-- 查找所有 MyISAM 表
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE ENGINE = 'MyISAM'
ORDER BY TABLE_SCHEMA;

-- 批量转换为 InnoDB
-- 注意：生产环境需先备份
ALTER TABLE my_table ENGINE = InnoDB;

⚠️ MyISAM 的致命缺陷：

• 不支持事务：中途崩溃会导致数据不一致
• 只有表锁：一个写操作会锁住整个表
• 无崩溃恢复：损坏后可能需要 REPAIR TABLE

3.3 InnoDB 架构详解

3.3.1 InnoDB 内存结构

┌─────────────────────────────────────────────┐
│                InnoDB 内存结构                │
│                                             │
│  ┌─────────────────────────────────────┐    │
│  │         Buffer Pool (缓冲池)         │    │
│  │  ┌──────────┐  ┌──────────────────┐ │    │
│  │  │ 数据页   │  │ 索引页           │ │    │
│  │  │ Data     │  │ Index            │ │    │
│  │  │ Pages    │  │ Pages            │ │    │
│  │  └──────────┘  └──────────────────┘ │    │
│  │  ┌──────────┐  ┌──────────────────┐ │    │
│  │  │ 自适应   │  │ Change Buffer    │ │    │
│  │  │ 哈希索引 │  │ (变更缓冲)       │ │    │
│  │  │ AHI      │  │                  │ │    │
│  │  └──────────┘  └──────────────────┘ │    │
│  └─────────────────────────────────────┘    │
│                                             │
│  ┌─────────────────┐ ┌─────────────────┐   │
│  │ Redo Log Buffer  │ │ Lock Memory     │   │
│  │ (重做日志缓冲)   │ │ (锁内存)        │   │
│  └─────────────────┘ └─────────────────┘   │
└─────────────────────────────────────────────┘

3.3.2 Buffer Pool（缓冲池）—— 最重要的内存区域

Buffer Pool 是 InnoDB 最核心的组件，将磁盘上的数据页缓存到内存中，避免每次查询都访问磁盘。

工作原理：

查询请求 → Buffer Pool 中有？ → 命中 (Hit) → 直接从内存返回
                                ↓ 未命中 (Miss)
                            从磁盘读取 → 放入 Buffer Pool → 返回

LRU 算法改进：

InnoDB 使用改进的 LRU（Least Recently Used）算法，将链表分为两部分：

┌──────────────────────────────────────────────┐
│              Buffer Pool LRU List              │
│                                              │
│  ┌─ Young 区 (5/8) ──┐  ┌─ Old 区 (3/8) ──┐ │
│  │ 最近访问的热数据   │  │ 新加载的数据     │ │
│  │                    │  │                  │ │
│  │ 最常访问 ←──────── │ ──────→ 最新加载   │ │
│  └────────────────────┘  └──────────────────┘ │
└──────────────────────────────────────────────┘

• 新读入的页放在 Old 区头部
• 在 Old 区停留超过 innodb_old_blocks_time（默认 1 秒）后再次被访问，才移入 Young 区
• 这样可以避免全表扫描等操作污染整个 Buffer Pool

-- 查看 Buffer Pool 状态
SHOW ENGINE INNODB STATUS\G

-- 查看 Buffer Pool 命中率（应 > 99%）
SELECT 
    (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 AS hit_rate_pct;

-- 查看 Buffer Pool 使用情况
SELECT 
    POOL_ID,
    POOL_SIZE,
    FREE_BUFFERS,
    DATABASE_PAGES,
    OLD_DATABASE_PAGES,
    PAGES_MADE_YOUNG,
    PAGES_NOT_MADE_YOUNG
FROM information_schema.INNODB_BUFFER_POOL_STATS;

Buffer Pool 大小设置：

-- 动态调整 Buffer Pool 大小（在线调整，无需重启）
SET GLOBAL innodb_buffer_pool_size = 8 * 1024 * 1024 * 1024;  -- 8GB

3.3.3 Change Buffer（变更缓冲）

当修改非唯一二级索引的页面不在 Buffer Pool 中时，InnoDB 不会立即从磁盘读取该页，而是将变更记录到 Change Buffer 中，等页面被读入 Buffer Pool 时再合并（merge）。

-- 查看 Change Buffer 配置
SHOW VARIABLES LIKE 'innodb_change_buffer%';
-- innodb_change_buffer_max_size = 25（默认占 Buffer Pool 的 25%）

3.3.4 Adaptive Hash Index（自适应哈希索引）

InnoDB 会监控索引页的访问模式，如果某些页面被频繁等值查询访问，会自动在内存中构建哈希索引，将 B+Tree 查找优化为 O(1) 的哈希查找。

-- 查看 AHI 状态
SHOW ENGINE INNODB STATUS\G
-- 在 "INSERT BUFFER AND ADAPTIVE HASH INDEX" 部分查看

-- 关闭 AHI（某些场景下可能有争用）
SET GLOBAL innodb_adaptive_hash_index = OFF;

3.4 InnoDB 磁盘结构

3.4.1 表空间（Tablespace）

InnoDB 的数据最终存储在表空间文件中：

-- 查看表空间文件
SELECT TABLESPACE_NAME, FILE_NAME, FILE_TYPE
FROM information_schema.FILES
WHERE TABLESPACE_NAME NOT LIKE 'innodb_%'
ORDER BY FILE_TYPE;

-- 查看表的大小
SELECT 
    TABLE_NAME,
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS total_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'myapp'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

3.4.2 行格式（Row Format）

InnoDB 支持四种行格式，影响数据存储方式：

-- 查看当前默认行格式
SHOW VARIABLES LIKE 'innodb_default_row_format';

-- 修改表的行格式
ALTER TABLE my_table ROW_FORMAT=DYNAMIC;

3.5 Redo Log（重做日志）

3.5.1 原理

Redo Log 是 WAL（Write-Ahead Logging） 机制的核心——先写日志，再写磁盘。

事务修改数据
    ↓
先写入 Redo Log Buffer（内存）
    ↓
事务提交时，刷入 Redo Log 文件（磁盘）  ← 保证持久性（Durability）
    ↓
后台线程异步将脏页刷入表空间文件（磁盘）

为什么需要 Redo Log？

如果每次事务提交都将脏页刷入磁盘，随机 I/O 代价极大。Redo Log 是顺序 I/O，性能远高于随机 I/O。即使中途崩溃，重启后也能通过 Redo Log 恢复已提交的事务。

3.5.2 Redo Log 配置

-- MySQL 8.0.30+ 使用动态配置
ALTER INSTANCE DISABLE INNODB REDO_LOG;  -- 慎用！禁用 Redo Log
ALTER INSTANCE ENABLE INNODB REDO_LOG;

-- 查看 Redo Log 文件
SELECT * FROM performance_schema.innodb_redo_log_files;

-- 查看 redo log 大小
SHOW VARIABLES LIKE 'innodb_redo_log_capacity';
-- MySQL 8.0.30+ 使用 innodb_redo_log_capacity（默认 100MB）

3.5.3 Redo Log 刷盘策略

innodb_flush_log_at_trx_commit 控制 Redo Log 的刷盘时机：

-- 查看当前设置
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

⚠️ 生产环境建议：保持 innodb_flush_log_at_trx_commit = 1 和 sync_binlog = 1，这是保证数据不丢失的双一配置。

3.6 Undo Log（回滚日志）

3.6.1 作用

Undo Log 有两个核心作用：

1. 事务回滚：事务失败时，将数据恢复到修改前的状态
2. MVCC 多版本并发控制：提供数据的历史版本，实现非锁定读

事务 A 修改 id=1 的行（从 'old' 改为 'new'）
    ↓
修改 Buffer Pool 中的数据页
    ↓
同时在 Undo Log 中记录旧值 ('old')
    ↓
如果事务 A 回滚 → 用 Undo Log 恢复为 'old'
如果事务 B 读取 id=1 → 通过 Undo Log 找到 'old' 版本

3.6.2 Undo Log 管理

-- MySQL 8.0+ 使用独立的 Undo 表空间
SHOW VARIABLES LIKE 'innodb_undo%';

-- 查看 Undo 表空间
SELECT SPACE, NAME, FILE_SIZE, STATE
FROM information_schema.INNODB_TABLESPACES
WHERE SPACE_TYPE = 'Undo';

-- 查看历史链表长度（越长说明 MVCC 保留的旧版本越多）
SHOW ENGINE INNODB STATUS\G
-- 在 "TRANSACTIONS" 部分查看 "History list length"

💡 长事务的危害：长时间不提交的事务会导致 Undo Log 无法清理，历史链表膨胀，影响查询性能。

-- 查找长时间未提交的事务
SELECT 
    trx_id,
    trx_state,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS running_seconds,
    trx_rows_modified,
    trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started ASC;

3.7 Binlog（二进制日志）

3.7.1 概述

Binlog 属于 MySQL Server 层，不属于 InnoDB。它的作用：

1. 主从复制：从库通过 Binlog 重放主库的变更
2. 数据恢复：配合备份实现时间点恢复（PITR）

-- 开启 Binlog
SHOW VARIABLES LIKE 'log_bin';  -- 应该为 ON

-- 查看 Binlog 文件列表
SHOW BINARY LOGS;

-- 查看 Binlog 事件
SHOW BINLOG EVENTS IN 'mysql-bin.000001' LIMIT 20;

-- 使用 mysqlbinlog 工具查看
-- mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000001

3.7.2 三种日志对比

3.7.3 两阶段提交

为了保证 Redo Log 和 Binlog 的一致性，InnoDB 使用两阶段提交（2PC）：

事务执行，写 Redo Log（prepare 状态）
    ↓
事务提交，写 Binlog
    ↓
Redo Log 标记为 commit 状态

如果崩溃发生在步骤 1 之后、步骤 2 之前：Redo Log 是 prepare 但没有对应 Binlog → 回滚。 如果崩溃发生在步骤 2 之后、步骤 3 之前：Redo Log 是 prepare 但有对应 Binlog → 提交。

3.8 页结构（Page）

InnoDB 的最小 I/O 单位是页（Page），默认 16KB。

┌─────────────────────────────────────┐
│          Page Header (38 字节)       │
│  页号、页类型、LSN、记录数等         │
├─────────────────────────────────────┤
│          Infimum + Supremum          │
│  最小记录和最大记录（哨兵）          │
├─────────────────────────────────────┤
│          User Records               │
│  实际的行数据记录（单链表）          │
├─────────────────────────────────────┤
│          Free Space                  │
│  可用空间                            │
├─────────────────────────────────────┤
│          Page Directory             │
│  页目录（稀疏目录，用于二分查找）    │
├─────────────────────────────────────┤
│          File Trailer (8 字节)       │
│  校验和，用于检测页损坏              │
└─────────────────────────────────────┘

3.9 内存与磁盘的交互总览

                    ┌──────────────┐
                    │   Client     │
                    └──────┬───────┘
                           │ SQL
                    ┌──────▼───────┐
                    │  SQL Layer   │
                    │  解析/优化    │
                    └──────┬───────┘
                           │ 调用引擎接口
               ┌───────────▼───────────┐
               │    Buffer Pool (内存)   │
               │  数据页、索引页         │
               │  Change Buffer         │
               │  Adaptive Hash Index   │
               ├────────────────────────┤
               │  Redo Log Buffer       │
               └──┬───────────────┬─────┘
                  │               │
        ┌────────▼──────┐  ┌─────▼────────┐
        │ Redo Log 文件  │  │ 表空间文件    │
        │ (顺序 I/O)     │  │ (随机 I/O)   │
        └───────────────┘  └──────────────┘

业务场景

场景 1：Buffer Pool 命中率低

某系统 Innodb_buffer_pool_reads 值很高，说明大量请求需要从磁盘读取数据。

排查思路：

-- 1. 确认命中率
SELECT 
    (1 - (
        SELECT VARIABLE_VALUE FROM performance_schema.global_status 
        WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
    ) / NULLIF((
        SELECT VARIABLE_VALUE FROM performance_schema.global_status 
        WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
    ), 0)) * 100 AS hit_rate_pct;
-- 命中率低于 99% 需要关注

-- 2. 检查 Buffer Pool 大小
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- 3. 检查数据量是否超过 Buffer Pool
SELECT 
    TABLE_SCHEMA,
    ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 0) AS total_mb
FROM information_schema.TABLES
GROUP BY TABLE_SCHEMA;

-- 解决方案：增大 Buffer Pool，或优化查询减少扫描范围

场景 2：Undo Log 膨胀

排查：

-- 查看历史链表长度
SHOW ENGINE INNODB STATUS\G
-- History list length 超过 100 万需要关注

-- 查找长事务
SELECT * FROM information_schema.INNODB_TRX
ORDER BY trx_started ASC;

-- 解决方案：杀掉长事务，设置超时时间
SET GLOBAL innodb_lock_wait_timeout = 10;  -- 锁等待超时

扩展阅读

• InnoDB Architecture - MySQL 官方文档
• InnoDB Buffer Pool - MySQL Internals
• InnoDB Redo Log
• MySQL 技术内幕：InnoDB 存储引擎 —— 姜承尧