MySQL 是最广泛使用的开源关系型数据库，其默认存储引擎 InnoDB 凭借 B+ 树索引、MVCC 多版本并发控制和完善的崩溃恢复机制，支撑了互联网绝大多数 OLTP 业务。本文从核心概念出发，系统梳理 InnoDB 的存储结构与页模型、B+ 树索引原理（聚簇/二级/覆盖/联合索引、回表、最左前缀、索引下推）、事务隔离与 MVCC 实现细节、行锁/间隙锁/Next-Key Lock 的加锁规则、redo/undo/binlog 三日志与两阶段提交、主从复制与高可用方案，以及慢查询优化、分库分表等工程实践，是一份覆盖原理到实践的完整知识图谱。
一、基础概念 1. MySQL 是什么？核心架构 定义 ：开源的关系型数据库管理系统（RDBMS），采用客户端/服务端架构，支持 SQL，通过可插拔存储引擎机制实现不同的底层存储能力。
1.1 核心能力 1. 关系模型 — 表（行/列）、主外键、约束、范式 2. ACID 事务 — InnoDB 支持 commit/rollback、4 种隔离级别 3. MVCC — 多版本并发控制，读写不互相阻塞 4. 行级锁 — 高并发写入下吞吐好（MyISAM 只有表锁） 5. 主从复制 — binlog 异步/半同步复制，支撑读写分离与高可用 6. 崩溃恢复 — redo log + 两阶段提交，宕机不丢已提交数据
1.2 整体架构（Server 层 + 存储引擎层） ┌──────────────────────────────────────────────────────────┐ │ 客户端（JDBC / Driver） │ └──────────────────────────────────────────────────────────┘ │ SQL ▼ ┌──────────────────────────────────────────────────────────┐ │ MySQL Server 层（所有引擎共享） │ │ │ │ 连接器 → 查询缓存（8.0 已移除） │ │ ↓ │ │ 分析器（词法/语法分析 → 解析树 AST） │ │ ↓ │ │ 优化器（生成执行计划：选索引、决定 join 顺序、决定走索引还是全表）│ │ ↓ │ │ 执行器（调用存储引擎接口，逐行获取数据） │ │ │ │ ── 通用日志 ── │ │ binlog（归档日志，Server 层，所有引擎共用） │ └──────────────────────────────────────────────────────────┘ │ 引擎 API ▼ ┌──────────────────────────────────────────────────────────┐ │ 存储引擎层（Pluggable，可替换） │ │ │ │ ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐ │ │ │ InnoDB │ │ MyISAM │ │ Memory │ │ ... │ │ │ │ （默认） │ │ │ │ │ │ │ │ │ │ 行锁/MVCC│ │ 表锁 │ │ 内存表 │ │ │ │ │ │ redo/undo│ │ 无事务 │ │ 重启丢失 │ │ │ │ │ └──────────┘ └──────────┘ └──────────┘ └──────────┘ │ │ │ │ ── 引擎专属日志 ── │ │ redo log（InnoDB 引擎层，崩溃恢复用） │ │ undo log（InnoDB 引擎层，事务回滚 + MVCC 用） │ └──────────────────────────────────────────────────────────┘ │ ▼ 磁盘数据文件
关键边界 ：binlog 属于 Server 层（所有引擎共用），redo log 和 undo log 属于 InnoDB 引擎层（MyISAM 没有）。
1.3 一条 SQL 的执行流程 SELECT * FROM user WHERE id = 1; 1. 连接器：校验账号密码、权限，建立长连接（连接池复用） 2. 查询缓存（8.0 已删除）：key=SQL 命中直接返回，但失效频繁，弊大于利 3. 分析器：词法分析（识别 SELECT/user/id 等关键字）→ 语法分析 → AST 4. 优化器：选择执行方案 - 有多个索引时选哪个（基于成本估算） - 多表 JOIN 时决定驱动表与被驱动表顺序 - 决定走索引扫描还是全表扫描 → 生成执行计划（可用 EXPLAIN 查看） 5. 执行器：判断权限 → 调用存储引擎接口 - 第一次调用：handler.read_first() 拿到第一条 - 后续调用：handler.read_next() 逐条获取 - 引擎内部走 B+ 树定位数据页 → 在 Buffer Pool 找 → 命中则返回，否则从磁盘读 6. 返回结果集给客户端
1.4 MySQL vs 其他存储系统选型
场景
MySQL（InnoDB）
Redis
Elasticsearch
MongoDB
强一致事务 OLTP
✅ 首选
❌ 无多行事务
❌ 不完整
⚠️ 单文档
高并发缓存读
⚠️ 磁盘，慢
✅ 内存，毫秒级
❌
⚠️
全文检索
⚠️ 全文索引弱
❌
✅ 首选
⚠️
弱 schema 文档
❌ 需改表
⚠️
⚠️
✅ 首选
复杂 JOIN/聚合
✅ 强
❌
✅（聚合）
⚠️
大数据量分析（PB 级）
❌ 行存慢
❌
⚠️
❌
大数据 OLAP 场景通常用 ClickHouse / Hive / Doris，不是 MySQL 强项。
2. 存储引擎对比 mysql> SHOW ENGINES; +--------------------+---------+-----------------------------------------------------------+ | Engine | Support | Comment | +--------------------+---------+-----------------------------------------------------------+ | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys| | MyISAM | YES | MyISAM storage engine | | Memory | YES | Hash based, stored in memory, useful for temporary tables | | Archive | YES | Archive storage engine | | ... +--------------------+---------+-----------------------------------------------------------+
2.1 InnoDB vs MyISAM
维度
InnoDB（默认）
MyISAM（5.5 前默认）
事务 ✅ 支持 ACID
❌ 不支持
锁粒度 行锁 （高并发）表锁（并发差）
外键 ✅ 支持
❌
崩溃恢复 ✅ redo log 恢复
❌ 损坏需 repair
索引结构 聚簇索引 （数据和主键索引在一起）非聚簇（数据和索引分离）
全文索引 ✅ 5.6+ 支持
✅
COUNT(*) 慢（需扫描，因 MVCC）
快 （维护了计数器）
适用 OLTP 业务（绝大多数）
只读 / 历史归档表
5.5 起默认引擎改为 InnoDB，现代业务几乎不用 MyISAM。
2.2 为什么 InnoDB 的 COUNT(*) 慢？ MyISAM：表级元数据维护了总行数，COUNT(*) 直接读计数器 → O(1) InnoDB：因为 MVCC，同一时刻不同事务看到的行数可能不同 → 没法维护一个"全局准确的行数" → 必须遍历聚簇索引（或最小的二级索引）逐行累计 → 大表上慢 优化：用 explain 的 rows 估算值，或维护一张统计表
3. 数据类型选择速查
类型
字节数
适用
注意
TINYINT 1
状态值、布尔
TINYINT(1) 常作 bool
INT / BIGINT 4 / 8
主键、计数
自增主键用 BIGINT 防溢出
DECIMAL(M,D) M+2
金额
禁止用 FLOAT/DOUBLE 存金额 （精度丢失）
VARCHAR(N) 变长
字符串
N 是字符数，实际占 字符集字节数 × N + 长度位
CHAR(N) 定长
短定长（如 md5 32 位）
不足补空格
TEXT / BLOB 大对象
长文本
存在溢出页，慎做索引
DATETIME 8
范围广（1000-9999）
与时区无关
TIMESTAMP 4
范围窄（1970-2038）
自动转 UTC，2038 问题
两个高频踩坑 ：
1. 金额用 DECIMAL(18,2)，绝不用 FLOAT FLOAT 是浮点，0.1 + 0.2 ≠ 0.3（二进制无法精确表示） DECIMAL 是定点数，按字符串存，运算精确 2. VARCHAR(N) 的 N 是「字符数」不是「字节数」 utf8mb4 下 1 个汉字 = 4 字节 VARCHAR(255) 在 utf8mb4 下最多 255 × 4 = 1020 字节 索引键长限制 3072 字节 → VARCHAR(768) 是 utf8mb4 单列索引上限
二、InnoDB 存储结构 4. 存储结构层次：表空间 → 段 → 区 → 页 → 行 InnoDB 的存储是分层组织的，从大到小：
┌──────────────────────────────────────────────────────┐ │ Tablespace（表空间） │ │ 系统表空间 ibdata1 / 独立表空间 tableName.ibd │ ├──────────────────────────────────────────────────────┤ │ Segment（段） │ │ 叶子节点段 / 非叶子节点段 / 数据段 / 回滚段 │ ├──────────────────────────────────────────────────────┤ │ Extent（区）= 64 个连续页 = 1MB │ │ 分配的最小单位（防止页碎片化） │ ├──────────────────────────────────────────────────────┤ │ Page（页）= 16KB（默认） │ │ InnoDB 磁盘 IO 的最小单位 ★★★ │ │ 数据页 / 索引页 / undo 页 / 系统页 │ ├──────────────────────────────────────────────────────┤ │ Row（行） │ │ 变长字段长度 / NULL 标记 / 记录头信息 / 列数据 │ └──────────────────────────────────────────────────────┘
层级
大小
说明
Tablespace
—
物理文件，.ibd 为独立表空间
Segment
—
逻辑分组，叶子/非叶子节点分属不同段
Extent（区）
1MB 64 个连续页，B+ 树分配空间的单位
Page（页） 16KB （默认）磁盘 IO 最小单位，核心
Row
变长
行记录，含隐藏列（DB_ROW_ID/trx_id/roll_ptr）
4.1 为什么页大小默认 16KB？ 1. 文件系统块一般是 4KB，16KB 是 4 个块，对齐友好 2. B+ 树的扇出（fan-out）： 假设主键 BIGINT(8字节) + 指针(6字节) = 14 字节 非叶子节点一页可存 16384 / 14 ≈ 1170 个键 → 三层 B+ 树可存 1170 × 1170 × 16 ≈ 2000 万行 → 这就是"三层 B+ 树能存 2000 万行"的来历 3. 过大：Buffer Pool 命中率下降、单次 IO 时延长 4. 过小：树变高，磁盘 IO 次数增加
页大小可在初始化时通过 innodb_page_size 配置（4K/8K/16K/32K/64K），改后不可变。
4.2 行的隐藏列（InnoDB 自动加的） 每条记录除了用户定义的列，InnoDB 还会加三个隐藏列：
隐藏列
大小
作用
DB_TRX_ID 6 字节
最后一次修改该行的事务 ID（MVCC 核心）
DB_ROLL_PTR 7 字节
回滚指针，指向 undo log 中的上一版本（MVCC 版本链）
DB_ROW_ID
6 字节
行 ID（仅当表没主键也没唯一非空索引时 才生成）
主键必须显式定义！没有主键时 InnoDB 用 DB_ROW_ID 生成聚簇索引，但这个列对开发者不可见，无法用它定位数据。
5. Page 页结构详解 一个 16KB 数据页的内部布局：
┌─────────────────────────────────────────────┐ │ File Header（38 字节）— 页号、前后页指针、LSN │ ├─────────────────────────────────────────────┤ │ Page Header（56 字节）— 记录数、空闲位置 │ ├─────────────────────────────────────────────┤ │ Infimum + Supremum（系统记录） │ │ 最小记录（虚拟）/ 最大记录（虚拟） │ ├─────────────────────────────────────────────┤ │ │ │ 用户记录区（按主键升序的**单向链表**） │ │ │ │ [Rec1] → [Rec2] → [Rec3] → ... → [RecN] │ │ │ │ 每条记录的 record_type=CONVENTIONAL │ │ 记录头含 next_record 指针 │ ├─────────────────────────────────────────────┤ │ Free Space（空闲区，插入新记录用） │ ├─────────────────────────────────────────────┤ │ Page Directory（页目录，2 字节/槽） │ │ 把记录分组，每组最后一条记录地址入槽 │ │ → 页内二分查找用 │ ├─────────────────────────────────────────────┤ │ File Trailer（8 字节）— 校验和 + LSN │ │ 检测页是否完整写入（写了一半的检测） │ └─────────────────────────────────────────────┘
页内记录查找流程 ：
1. 通过 Page Directory 把页内记录分组（默认 4-8 条/组） 2. 每组最后一条记录的地址作为「槽」（slot） 3. 查找时：先在槽上二分 → 定位到组 → 在组内遍历（最多 4-8 次） → 页内查找复杂度 O(log N) + 常数
5.1 页与页之间：双向链表 Page_10 ⇄ Page_20 ⇄ Page_30 ⇄ Page_40 │ │ │ │ ▼ ▼ ▼ ▼ [1,2,3] [4,5,6] [7,8,9] [10,11,12] ↓ ↓ ↓ ↓ (页内记录用单向链表有序相连) → 数据页之间用双向链表连接（File Header 的 prev/next 指针） → 页内记录用单向链表连接 → B+ 树叶子层 = 这些有序页的集合
6. Buffer Pool 缓冲池 InnoDB 的核心内存区域，所有读写都先经过它。
┌──────────────────────────┐ │ Buffer Pool（内存） │ 读请求 │ │ 客户端 ─────────────────────►│ ┌────────────────────┐ │ │ │ 数据页缓存 │ │ 命中 → 直接返回 │ │ 索引页缓存 │ │ 未命中 → 从磁盘加载 │ │ undo 页 / change buf │ │ │ │ 自适应哈希索引 AHI │ │ │ └────────────────────┘ │ └──────────┬───────────────┘ │ 未命中 ▼ ┌──────────────────────────┐ │ 磁盘 .ibd 文件 │ └──────────────────────────┘
6.1 Buffer Pool 的核心机制
机制
作用
LRU 链表改进版 避免全表扫描 把热点数据全冲掉：链表分 young（热区，5/8）+ old（冷区，3/8），新页先进 old，第二次访问且停留 > innodb_old_blocks_time（默认 1s）才升 young
预读 顺序读 56 个页 → 异步预读下一个 extent；随机读不预读
change buffer 对非唯一二级索引 的写先在内存改，定期 merge 到磁盘（提升写入性能）
自适应哈希索引 AHI 监控热点查询，自动给 B+ 树建哈希索引，等值查询从 O(log N) 降到 O(1)
dirty page 刷盘 后台线程把脏页（修改过的页）刷回磁盘，触发：redo log 满、Buffer Pool 不足、空闲时
6.2 改进版 LRU 为什么重要？ 全表扫描场景（如 SELECT * FROM big_table）： 传统 LRU：扫一遍几百万页 → 全部挤进 LRU 头部 → 热点数据被冲走 → 缓存命中率暴跌 改进版 LRU： 新读入的页全部进 old 区（尾部 3/8） 只有「第二次访问 + 停留超过 1 秒」才升 young 区 → 全表扫描的页大部分停留 < 1s 就被淘汰，不污染 young 区 → 这就是为什么不要在生产环境随便全表扫大表——即使有 Buffer Pool 兜底也会拖垮性能
6.3 脏页刷盘时机 触发刷脏页的场景： 1. redo log 写满了 → 必须推进 checkpoint，强制刷脏页（⚠️ 业务写阻塞） 2. Buffer Pool 内存不足 → 淘汰 LRU 尾部页，是脏页就先刷盘 3. MySQL 空闲时 → 后台线程主动刷 4. MySQL 正常关闭 → flush all → redo log 满是写入性能抖动的元凶：此时所有写入必须等待刷盘推进 LSN
三、索引原理（B+ 树） 7. B+ 树详解 7.1 为什么不用其他数据结构做索引？
候选结构
为什么不行
哈希表 等值查询 O(1) 极快，但不支持范围查询 （>、BETWEEN、ORDER BY）
二叉搜索树 BST 树高 = 数据量，百万级数据树高 20+，磁盘 IO 次数 = 树高，太慢
红黑树 / AVL 同上，二叉树天然树高，磁盘场景不行
B 树 非叶子节点也存数据 → 单页能放的键更少 → 树更高；且范围查询要中序遍历多次回溯
跳表 内存数据库（如 Redis ZSet）用，但磁盘场景 B+ 树扇出更大、更优
核心矛盾 ：磁盘 IO 慢（毫秒级），CPU 和内存快（纳秒级）。索引结构必须降低树高 （=降低 IO 次数），所以用多叉树 。
7.2 B 树 vs B+ 树 B 树（每个节点都存数据）： [10 | 20 | 30] ← 非叶子节点也存数据 / | | \ [1,3,5] [11,13] [21,23] [31,33,35] ↑ ↑ ↑ ↑ 数据 数据 数据 数据 B+ 树（只有叶子节点存数据，非叶子节点只存索引）： [10 | 20 | 30] ← 只存索引，不存数据 / | | \ [1,3,5]→[11,13]→[21,23]→[31,33,35] ← 叶子节点存数据 ★ 叶子节点用双向链表相连 ★
对比
B 树
B+ 树
数据存放
所有节点都存
只有叶子节点存
非叶子节点
存键 + 数据 + 指针
只存键 + 指针 → 扇出更大
树高
较高
更低 （同样数据量）
范围查询
中序遍历，回溯多次
顺着叶子链表走即可 ★
等值查询
可能在中间节点命中
必然到叶子节点
查询稳定性
不稳定（早命中就快）
稳定 （都要到叶子）
B+ 树的两个核心优势 ：
1. 扇出大 → 树矮 → IO 少 非叶子节点不存数据，一页能放更多键 → 扇出可达 1000+ 三层 B+ 树：1000 × 1000 × 1000 = 10 亿行数据 → 查询任意一行只需 3 次磁盘 IO 2. 叶子链表 → 范围查询快 SELECT * FROM user WHERE id BETWEEN 100 AND 200; → 定位到 id=100 所在叶子页 → 顺着叶子链表向后遍历到 id=200 即可 → 不需要回到上层节点回溯
7.3 B+ 树能存多少行？（三层 B+ 树 ≈ 2000 万行的来历） 假设： - 页大小 16KB - 主键 BIGINT 占 8 字节，页内指针 6 字节 - 非叶子节点一项 = 8 + 6 = 14 字节 - 一页可存 16384 / 14 ≈ 1170 个键 - 叶子节点存完整行，假设每行 1KB - 一页可存 16384 / 1024 = 16 行 三层 B+ 树： 根节点（1 页）→ 1170 个二层层节点 二层（1170 页）→ 1170 × 1170 个三层叶子页 叶子层（1170 × 1170 页）→ 每页 16 行 总行数 = 1170 × 1170 × 16 ≈ 2190 万行 → 这就是"单表 2000 万以内性能好，超过就要分表"的经验法则来历 → 但实际容量取决于行大小：行越小，单表能存的越多
阿里 Java 开发手册建议：单表行数超过 500 万或容量超过 2GB 就考虑分表。这是保守的经验值，不是硬性上限。
7.4 B+ 树的查询与插入 查询 id = 8 ：
[10 | 20] / | \ [2,5] [10,12,15] [22,25,30] │ │ │ ... ... ... Step 1: 根节点 [10|20] → 8 < 10 → 走最左指针 Step 2: 二层节点 [2,5] → 5 < 8 但下一项 10 > 8 → 走最右指针... (实际是根据键区间路由) Step 3: 到叶子节点 → 二分/页目录定位到 id=8 的行
插入 id = 7 ：
若目标叶子页有空闲 → 直接插入到有序位置 若叶子页满了 → 页分裂（split）： 1. 创建新页 2. 把原页一半数据搬到新页 3. 在父节点插入新页的最小键 + 指向新页的指针 4. 若父节点也满 → 递归向上分裂 → 极端情况树高 +1 → 这就是为什么自增主键好：插入永远是追加到最右叶子页，不触发分裂 → UUID 主键坏：随机插入 → 频繁页分裂 → 性能差 + 空间浪费（页填充率低）
8. 聚簇索引 vs 二级索引 8.1 聚簇索引（Clustered Index） 定义 ：叶子节点直接存整行数据 的索引。一张表只能有一个聚簇索引（因为数据只能按一种物理顺序存放）。
聚簇索引（按主键 id 组织）： [10 | 20 | 30] ← 非叶子节点：主键值 + 页指针 / | | \ ┌─────────────────────────────┐ │ 叶子节点：[id=1, name, age] │ ← 叶子节点直接存完整行 │ [id=2, name, age] │ │ [id=3, name, age] │ └─────────────────────────────┘ → 表 = 聚簇索引本身（数据按主键有序存放） → SELECT * FROM user WHERE id = 5; 一次 B+ 树查找即可拿到整行
InnoDB 选择聚簇索引的优先级 ：
1. 显式定义的 PRIMARY KEY ← 首选 2. 第一个所有列都 NOT NULL 的 UNIQUE 索引 3. 自动生成隐藏的 DB_ROW_ID（6 字节）作为聚簇索引 → 你看不到也用不了，所以务必定义主键
8.2 二级索引（Secondary Index / 非聚簇索引） 定义 ：叶子节点只存索引列 + 主键值 的索引。可以有多个。
CREATE INDEX idx_name ON user(name); 二级索引 idx_name（按 name 组织）： [Li | Wang | Zhang] / | | \ ┌──────────────────────────────┐ │ 叶子节点：[name=Li, id=3] │ ← 叶子节点只存索引列 + 主键 │ [name=Li, id=7] │ │ [name=Wang, id=1] │ └──────────────────────────────┘ → 查 SELECT name FROM user WHERE name = 'Li'; 命中 idx_name → 叶子节点已有 name → 直接返回（覆盖索引） → 查 SELECT * FROM user WHERE name = 'Li'; 命中 idx_name → 拿到主键 id=3 → 还要回表到聚簇索引取整行
8.3 聚簇索引 vs 二级索引对比
维度
聚簇索引
二级索引
数量
1 个 多个
叶子节点存什么
整行数据 索引列 + 主键值
物理有序性
数据按聚簇索引键物理有序
逻辑有序（叶子链表），物理可乱
查找路径
1 次 B+ 树查找
等值/范围查询，取整行需回表 （2 次 B+ 树查找）
默认
主键
显式 CREATE INDEX 创建
MyISAM 没有”聚簇”概念：数据和所有索引分离存放，所有索引的叶子都存行的物理地址（数据行是无序的）。
9. 回表与覆盖索引 9.1 回表（Table Lookup） SELECT * FROM user WHERE name = 'Li'; 执行： Step 1: 查 idx_name 二级索引 → 在叶子层找到 name='Li' 对应的主键 id=3 Step 2: 用 id=3 回到聚簇索引做第二次查找 → 拿到完整行数据 (id=3, name=Li, age=20, ...) Step 3: 返回结果 → 一次查询需要查 2 棵 B+ 树，这叫「回表」 → 高频查询如果总是回表，性能损耗明显
9.2 覆盖索引（Covering Index） 定义 ：查询所需的列全部包含在某个索引的叶子节点中 ，不需要回表。
SELECT id, name FROM user WHERE name = 'Li'; idx_name(name) 的叶子节点已经存了 name + id（主键自动附加） → 查询要的列（id, name）二级索引都有 → 不用回表 EXPLAIN 结果：Extra 列显示 `Using index` → 表示命中覆盖索引 ★
实践：高频查询的字段组合做成联合索引，尽量覆盖查询列 。
SELECT amount, status FROM orders WHERE user_id = 123 ;CREATE INDEX idx_uid_amount_status ON orders(user_id, amount, status);
10. 最左前缀原则 定义 ：联合索引 (a, b, c) 只有从最左列开始连续 匹配才能用到索引。
CREATE INDEX idx_abc ON t(a, b, c); 索引排序：先按 a 排，a 相同按 b 排，b 相同按 c 排 → 实际是 (a,b,c) 的有序组合，类似字典排序 能用索引的情况： WHERE a = 1 ✅ 用到 a WHERE a = 1 AND b = 2 ✅ 用到 a, b WHERE a = 1 AND b = 2 AND c = 3 ✅ 用到 a, b, c WHERE a = 1 AND c = 3 ✅ 用到 a（c 用不上索引，因为跳过了 b） WHERE a = 1 AND b > 2 AND c = 3 ✅ 用到 a, b（b 是范围，c 用不上） 用不上索引的情况： WHERE b = 2 ❌ 缺少最左 a WHERE b = 2 AND c = 3 ❌ 缺少最左 a WHERE c = 3 ❌ 缺少最左 a
为什么？因为索引是按 (a, b, c) 全局有序排列的 ：
索引项（已排序）： (1, 1, 1), (1, 1, 5), (1, 2, 3), (1, 3, 1), (2, 1, 1), (2, 1, 2), ... WHERE a = 1 → 连续区间，能直接二分定位 WHERE b = 2 → a 不固定，(1,2,..), (2,2,..), (3,2,..) 散落各处 → 只能全扫
10.1 范围查询会”打断”索引使用 WHERE a = 1 AND b > 5 AND c = 3 a 用等值 → a 索引生效 b 用范围 → b 索引生效（定位范围起点） c → 用不上！b 是范围，c 在 b 范围内无序 → 索引用到 (a, b)，c 走不了索引 → 调整索引顺序为 (a, c, b) 可能更好（如果 c 是等值）
10.2 索引下推（Index Condition Pushdown, ICP，5.6+） 没有 ICP 的时代 ：
联合索引 (a, b) SELECT * FROM t WHERE a LIKE '张%' AND b > 10; 1. 用 idx(a,b) 找到 a LIKE '张%' 的所有主键（a 能用最左前缀） 2. 全部回表 → 取出整行 → 再过滤 b > 10 → 即使 b 不满足，也已经回表了，浪费
有 ICP 后 ：
1. 用 idx(a,b) 找到 a LIKE '张%' 的索引项 2. 在索引层就检查 b > 10（索引项里有 b）→ 不满足的不回表 3. 满足的才回表 → 大幅减少回表次数 EXPLAIN 显示 Extra: Using index condition → 启用了 ICP
11. 索引合并（Index Merge） 场景 ：单表查询，多个单列索引 的条件用 AND / OR 连接。
SELECT * FROM t WHERE a = 1 OR b = 2 ;
类型
说明
intersect 多个索引结果求交（AND）
union 多个索引结果求并（OR）
sort-union 排序后求并
但 Index Merge 性能一般不如联合索引，能用联合索引替代就替代。
12. 前缀索引与函数索引 12.1 前缀索引 长字符串字段（如 url、email）建全列索引空间浪费大，可以只对前 N 个字符建索引：
CREATE INDEX idx_email ON user (email(10 )); SELECT COUNT (DISTINCT LEFT (email, 5 )) / COUNT (* ) AS sel5, COUNT (DISTINCT LEFT (email, 10 )) / COUNT (* ) AS sel10, COUNT (DISTINCT LEFT (email, 15 )) / COUNT (* ) AS sel15 FROM user ;
前缀索引的代价 ：不能用于覆盖索引 （无法在索引层判断等值），也不能用于 ORDER BY。
12.2 函数索引（8.0+） CREATE INDEX idx_date ON orders((DATE (create_time)));SELECT * FROM orders WHERE DATE (create_time) = '2026-01-01' ;
13. 索引失效场景（高频面试题） CREATE TABLE t (id INT , name VARCHAR (50 ), age INT , phone CHAR (11 ));CREATE INDEX idx_name ON t(name);CREATE INDEX idx_phone_age ON t(phone, age);
失效场景
例子
原因
解决
函数/计算操作 WHERE LEFT(name,3)='abc'WHERE id + 1 = 10破坏索引有序性
改写为 WHERE id = 9
隐式类型转换 WHERE phone = 13800138000（phone 是 char）字符串 vs 数字 → 对 phone 套函数
传字符串 phone='13800138000'
LIKE 以 % 开头WHERE name LIKE '%abc'B+ 树前缀匹配失效
用 (a)bc% 或全文索引
OR 一侧无索引WHERE name='a' OR age=10（age 无索引）必须全表扫描
给 age 加索引或用 UNION
!= / <> / NOT INWHERE age != 20走索引反而可能更慢（命中量大）
视数据分布而定
IS NULL 在引擎不支持时—
5.6+ 已支持
—
字符集不匹配 WHERE name = 'abc' COLLATE utf8mb4_bin（列是 utf8mb4_general_ci）排序规则不一致触发转换
统一字符集
最左前缀缺失 WHERE age=10（联合索引 (phone,age)）跳过 phone
调整索引顺序
优化器主动放弃 命中行数 > 表 30%
全表扫比回表快
force index
最常见的坑 ：WHERE 字段 = 值 时类型不一致触发隐式转换：
phone 字段是 CHAR(11)，但 SQL 写 phone = 13800138000（数字） → 优化器等价改写为 WHERE CAST(phone AS SIGNED) = 13800138000 → 对 phone 套了函数 → 索引失效 → 全表扫描 排查：EXPLAIN 看 type=ALL、key=NULL、rows 接近全表
四、事务与 MVCC 14. ACID 与隔离级别 14.1 ACID
特性
全称
含义
实现机制
A 原子性Atomicity
事务要么全做要么全不做
undo log （回滚）
C 一致性Consistency
事务前后数据满足约束
A + I + 业务约束共同保证
I 隔离性Isolation
并发事务互不干扰
MVCC + 锁
D 持久性Durability
提交后数据永久不丢
redo log （崩溃恢复）
14.2 四种隔离级别 并发事务引发的问题：
问题
描述
脏读 （Dirty Read）读到其他事务未提交 的修改（对方回滚后这数据就是脏的）
不可重复读 （Non-repeatable Read）同一事务内两次读同一行，值不同 （被其他事务 UPDATE 了）
幻读 （Phantom Read）同一事务内两次范围查询，行数不同 （被其他事务 INSERT/DELETE 了）
四种隔离级别（从弱到强）：
隔离级别
脏读
不可重复读
幻读
性能
READ UNCOMMITTED 读未提交❌ 可能
❌ 可能
❌ 可能
最高
READ COMMITTED 读已提交（RC）✅ 避免
❌ 可能
❌ 可能
高
REPEATABLE READ 可重复读（RR，InnoDB 默认 ）✅ 避免
✅ 避免
✅ InnoDB 也能避免 ★
中
SERIALIZABLE 串行化✅ 避免
✅ 避免
✅ 避免
最低
-- 查看当前隔离级别 SELECT @@transaction_isolation; -- 设置（注意 RC 是互联网公司常用，Oracle/PG 默认 RC，MySQL 默认 RR） SET GLOBAL transaction_isolation = 'READ-COMMITTED';
为什么 InnoDB 的 RR 也能避免幻读？
SQL 标准说 RR 不能防幻读，但 InnoDB 在 RR 级别下： 1. 快照读（普通 SELECT）：用 MVCC 保证多次读结果一致 → 不会读到新插入的行 2. 当前读（SELECT ... FOR UPDATE / 加锁读）：用 Next-Key Lock 锁住范围 → 阻止插入 → InnoDB 的 RR 在大多数场景下「实际不会幻读」 → 但如果快照读和当前读混用，仍可能出现幻读
15. MVCC 原理（多版本并发控制） 核心思想 ：读不加锁、写不加锁（写写才互斥），通过保存数据的历史版本 让读操作看到合适时刻的快照。
15.1 MVCC 依赖的两件事 1. 行记录里的隐藏列（见第 4.2 节）： - DB_TRX_ID：最后一次修改该行的事务 ID - DB_ROLL_PTR：回滚指针，指向 undo log 中的上一版本 2. undo log 版本链： 每次更新都把旧版本写入 undo log，多个 undo log 通过 roll_ptr 串成链表 当前行: trx_id=200, roll_ptr ──► undo1(trx_id=150, name='B') │ ▼ undo2(trx_id=100, name='A')
15.2 ReadView（读视图） 事务执行快照读时，会生成一个 ReadView，用来判断哪个版本对当前事务可见。
ReadView 的四个核心字段 ：
字段
含义
m_ids生成 ReadView 时，**当前活跃（未提交）**事务 ID 列表
min_trx_idm_ids 中最小值
max_trx_id生成 ReadView 时系统应分配的下一个事务 ID（不是 m_ids 最大值）
creator_trx_id创建该 ReadView 的事务 ID
可见性判断规则 （对一个数据版本的 trx_id）：
设版本的 trx_id 为 T： 1. T == creator_trx_id → 自己改的，可见 ✅ 2. T < min_trx_id → 该版本在 ReadView 之前已提交，可见 ✅ 3. T >= max_trx_id → 该版本在 ReadView 之后才产生，不可见 ❌ 4. min_trx_id <= T < max_trx_id： - T 在 m_ids 中 → 该事务还活跃（未提交），不可见 ❌ - T 不在 m_ids 中 → 已提交，可见 ✅ 不可见 → 顺着 roll_ptr 找上一个 undo 版本，再判一次，直到找到可见版本
15.3 RC vs RR 的 MVCC 差异 关键差异 ：ReadView 的生成时机不同。
READ COMMITTED（RC）： 每次 SELECT 都生成【新的】ReadView → 每次都能看到最新已提交的数据 → 不可重复读 REPEATABLE READ（RR）： 事务内【第一次】SELECT 生成 ReadView，整个事务复用 → 整个事务看到的快照一致 → 可重复读
15.4 端到端示例（理解 MVCC 的关键） 初始：user 表有一行 (id=1, name='A')，假设这条记录 trx_id=100 时间线： T0: 事务 A（trx_id=200）开始 T1: 事务 A 执行 SELECT * FROM user WHERE id=1; → 生成 ReadView: m_ids=[200], creator=200 → 行的 trx_id=100 < 200 的 min_trx_id（其实就是 200），不对， 100 < m_ids 最小值 → 可见 → 读到 name='A' T2: 事务 B（trx_id=300）开始 T3: 事务 B 执行 UPDATE user SET name='B' WHERE id=1; 并 COMMIT; → 当前行变成 trx_id=300 → undo log 记录旧版本 trx_id=100, name='A' T4: 事务 A 再执行 SELECT * FROM user WHERE id=1; 【RC 场景】：重新生成 ReadView: m_ids=[]（B 已提交） → 行 trx_id=300，300 不在 m_ids，已提交 → 可见 → 读到 name='B' ★不可重复读 【RR 场景】：复用 T1 的 ReadView: m_ids=[200] → 行 trx_id=300，300 >= 200 但 300 不在 [200] 里，应该可见？ 错，重新看规则：300 >= max_trx_id（生成 ReadView 时 max_trx_id 比如 201）→ 不可见 ❌ → 顺 roll_ptr 找 undo 版本 trx_id=100 → 可见 → 读到 name='A' ★可重复读 → RR 下，事务 A 始终看到事务 A 开始时刻的快照，不受其他事务影响
15.5 MVCC 的空间代价：undo log 不能立即回收 问题：如果有个长事务（执行几小时），它的 ReadView 始终存在 → 这段时间产生的所有 undo log 都不能被 purge（清理） → undo 表空间暴涨，磁盘告警 → 历史版本链越来越长，其他事务的快照读要顺链查找 → 慢 → 这就是"生产环境禁止长事务"的根因之一 → 监控：information_schema.innodb_trx 查询事务持续时间
16. 快照读 vs 当前读
类型
定义
SQL
快照读 读 MVCC 历史版本，不加锁
普通 SELECT
当前读 读最新 数据并加锁 ，保证读到的是已提交的最新值
SELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODE、UPDATE、DELETE、INSERT
为什么 UPDATE/DELETE 要当前读？ UPDATE user SET name='B' WHERE id=1; → 必须基于最新数据修改，不能基于旧快照 → 否则会丢失其他事务已提交的更新 为什么 SELECT FOR UPDATE 要当前读？ 先读取再加 X 锁，确保读取到最新值且阻止其他事务修改 → 用于"先查后改"的强一致场景（如扣库存）
17. 幻读与 Next-Key Lock 幻读 ：同一事务内，两次相同的范围查询，第二次出现了之前没看到的行（其他事务 INSERT 了）。
事务 A： 事务 B： SELECT * FROM user WHERE id > 10; （空） INSERT INTO user(id) VALUES (15); COMMIT; SELECT * FROM user WHERE id > 10; → 出现 id=15 ★ 幻读
InnoDB 在 RR 级别下用 Next-Key Lock 防止幻读 ：
SELECT * FROM user WHERE id > 10 FOR UPDATE; → 不只锁住已存在的 id>10 的行 → 还锁住 (10, +∞) 这个「间隙」 → 事务 B 想 INSERT id=15 → 落在 (10, +∞) 间隙 → 阻塞 → 事务 A 再次查就不会看到幻影行
详见第 19 节「锁机制」。
五、锁机制 18. 锁的分类全景 按粒度： ├── 全局锁（FTWRL，整库加锁，备份用） ├── 表级锁 │ ├── 表锁（LOCK TABLES） │ ├── 元数据锁 MDL（自动加，防 DDL/DML 冲突） │ └── 意向锁（IS / IX，行锁前先在表上标记） └── 行级锁（InnoDB 独有，基于索引实现） ├── 记录锁 Record Lock（锁单行） ├── 间隙锁 Gap Lock（锁两行之间的间隙，防插入） └── Next-Key Lock = Record + Gap（锁左开右闭区间） 按兼容性： ├── 共享锁 S（读锁，IS 是表级意向共享） └── 排他锁 X（写锁，IX 是表级意向排他）
19. 行锁、间隙锁、Next-Key Lock 19.1 记录锁（Record Lock） 锁住索引上的单条记录 。
SELECT * FROM t WHERE id = 10 FOR UPDATE; → 锁住 id=10 这一条（前提：id 有唯一索引且等值匹配且记录存在） → 其他事务改 id=10 阻塞，但可以改 id=11
19.2 间隙锁（Gap Lock） 锁住索引记录之间的间隙 （左开右开区间），目的是阻止 INSERT 。
假设表中 id 有：1, 5, 10, 15, 20 SELECT * FROM t WHERE id BETWEEN 6 AND 9 FOR UPDATE; → 锁住间隙 (5, 10) → 其他事务 INSERT id=7 → 阻塞（落在间隙内） → 其他事务 INSERT id=12 → 不阻塞（不在间隙内） 注意：间隙之间是锁的，但 id=10 这条记录本身要看是 Record Lock 还是 Next-Key
19.3 Next-Key Lock（默认） = Record Lock + Gap Lock，锁住左开右闭区间 (prev, current]。
表中 id：1, 5, 10, 15, 20 SELECT * FROM t WHERE id BETWEEN 6 AND 12 FOR UPDATE; 锁住的 Next-Key 区间： (5, 10] ← 锁住 10 这条记录 + (5,10) 间隙 (10, 15] ← 锁住 15 这条记录 + (10,15) 间隙 → INSERT id=6,7,8,9,11,12,13,14 全部阻塞 → INSERT id=4 不阻塞（不在锁定范围）
19.4 不同查询的加锁规则（重点） 前提 ：RR 隔离级别、id 是主键或唯一索引。
查询
加的锁
WHERE id = 10（等值，命中 ）Record Lock （仅锁 id=10 一条）
WHERE id = 10（等值，未命中 ）Gap Lock （锁住相邻两条之间的间隙）
WHERE id > 10（范围）Next-Key Lock ，锁住 (10, +∞)
WHERE id BETWEEN 5 AND 15Next-Key Lock ，锁住 (5, 10]、(10, 15]、(15, 20]
无索引列 WHERE name='x'
锁全表 （每行都加 Next-Key）★
为什么无索引列会锁全表？
行锁是加在「索引」上的，不是数据行上。 WHERE name='x' 而 name 没索引 → 只能全表扫描 → 扫到每一条都加锁 → 等于锁全表 → 这就是"删除/更新一定要走索引"的根因，否则锁全表
20. 意向锁（IS / IX）与 MDL 20.1 意向锁 问题 ：事务 A 想给表加表锁，但事务 B 已经在某些行加了行锁，怎么快速判断表里有没有行锁？
解决 ：加行锁前，先在表级 加一个意向锁（IS 或 IX），快速判断。
意向锁
含义
兼容性
IS （Intention Shared）准备加行级共享锁 S
与表级 S/X 不兼容；与其他表级 IS/IX 兼容
IX （Intention Exclusive）准备加行级排他锁 X
与表级 S/X 不兼容；与 IS/IX 兼容
事务 A：UPDATE t SET name='x' WHERE id=1; → 表级加 IX，行级加 X 锁 事务 B：LOCK TABLES t READ; → 想加表级 S 锁 → 检测到表上有 IX（意向排他）→ 与 S 不兼容 → 阻塞 → 意向锁是自动加的，开发者无需关心，但要知道它的存在
问题 ：事务 A 在执行长 SELECT，事务 B 想给表加列（DDL），怎么办？如果允许 DDL，A 读到一半表结构变了 → 数据错乱。
解决 ：5.5+ 引入 MDL，自动加：
- 任何 DML（CRUD）开始：表上加 MDL 共享锁（MDL Read） - 任何 DDL（ALTER/ADD COLUMN）：表上加 MDL 排他锁（MDL Write） → MDL Read 之间兼容（多读并发） → MDL Read 与 MDL Write 互斥 → MDL Write 与 MDL Write 互斥
生产事故常见模式（MDL 锁陷阱） ：
1. 事务 A（长查询）持有 MDL Read 2. 事务 B 执行 ALTER TABLE → 申请 MDL Write → 等 A 释放 3. 事务 C、D、E... 普通查询 → 申请 MDL Read → 排在 B 后面（FIFO） 4. 后续所有查询全部阻塞 → 表级雪崩 ★ → DDL 在生产环境的坑：长查询 + DDL → MDL 队列堆积 → 表不可用 → 解法：DDL 前先检查长事务，或用 gh-ost / pt-online-schema-change 工具
21. 死锁与排查 21.1 死锁的产生 事务 A： 事务 B： UPDATE t SET ... WHERE id=1; UPDATE t SET ... WHERE id=2; → 锁住 id=1 → 锁住 id=2 UPDATE t SET ... WHERE id=2; UPDATE t SET ... WHERE id=1; → 等 id=2 的锁（B 持有） → 等 id=1 的锁（A 持有） → 互相等待 → 死锁 ★
21.2 InnoDB 的死锁检测与处理 InnoDB 自动死锁检测： 1. 检测到"等待图"（wait-for graph）中出现环 → 死锁 2. 回滚 undo 量较少的事务（牺牲代价小的那个）→ 报错 ERROR 1213 (40001) 3. 另一个事务得以继续 参数 innodb_deadlock_detect = ON（默认） - 优点：自动恢复 - 缺点：高并发下检测本身耗 CPU（O(N²) 检测复杂度） 参数 innodb_lock_wait_timeout = 50（秒） - 锁等待超时，超时后放弃并报错（不等死锁检测）
21.3 如何避免死锁 1. 固定加锁顺序：所有事务都按 id 升序加锁，不会形成环 2. 大事务拆小：缩短持锁时间 3. 必要时降低隔离级别（RC 比 RR 锁范围小） 4. 走索引：避免无索引列更新导致的锁升级（锁全表） 5. 业务层重试：捕获 1213 死锁错误，自动重试整个事务
21.4 锁排查命令 SELECT * FROM information_schema.innodb_trx;SELECT * FROM performance_schema.data_lock_waits;SELECT * FROM performance_schema.data_locks;SET GLOBAL innodb_status_output_locks = ON ;SHOW ENGINE INNODB STATUS\G
六、日志系统（redo / undo / binlog） 22. 三大日志概览
日志
所属层
作用
写入时机
内容
redo log InnoDB 引擎层
崩溃恢复 （持久性 D）事务执行中持续写
物理日志：在某页某偏移写了什么
undo log InnoDB 引擎层
事务回滚 （原子性 A）+ MVCC 修改前先写 undo
逻辑日志：旧值是什么
binlog Server 层
主从复制 + 归档 事务提交时写
逻辑日志：SQL 或行变更
一次 UPDATE 的完整日志流程： UPDATE user SET name='B' WHERE id=1; 1. 用 id=1 找到 Buffer Pool 中的页（没找到就从磁盘加载） 2. 记录旧版本到 undo log（MVCC + 回滚用） 3. 修改 Buffer Pool 中的数据页（变成脏页） 4. 写 redo log（记录"page X offset Y 改成 B"）— 顺序写、先写 5. 事务提交 → 写 binlog（记录"name 改成 B"） 6. 提交完成，返回客户端成功 后台异步： 7. 脏页择机刷盘（不是同步的） 8. undo log 在没有事务依赖时择机 purge
23. redo log 详解 23.1 为什么需要 redo log？ 问题：Buffer Pool 中的脏页是异步刷盘的，如果 MySQL 突然宕机， 未刷盘的修改就丢了 → 违反持久性 暴力解法：每次事务提交都把所有脏页刷盘 → IO 灾难（随机写、量大） 优雅解法：用 redo log 1. 修改时只写 redo log（顺序写、追加，极快） 2. 脏页后台慢慢刷 3. 宕机重启时：重放 redo log，恢复未刷盘的修改 → 这叫 Write-Ahead Logging（WAL），所有现代数据库的核心思想 → "顺序写 redolog" 比 "随机写数据页" 快 1-2 个数量级
23.2 redo log 的结构 redo log 是固定大小的循环文件： ib_logfile0、ib_logfile1（一般 2 个文件，每个 48MB-1GB） 逻辑结构（环形 buffer）： ┌────────────────────────────────────────────┐ │ ① 写位置 (write pos) │ │ ↓ │ │ [...已写...][未写空闲...][...已刷盘可覆盖...]│ │ ↑ │ │ ② 检查点 (checkpoint) │ └────────────────────────────────────────────┘ write pos：redo log 当前写到哪 checkpoint：redo log 中"对应脏页已刷盘"的位置，可以覆盖 write pos 追上 checkpoint → redo log 满了 → 必须停下来刷脏页推进 checkpoint → 此时所有写入阻塞 → 业务感知到抖动
参数： innodb_log_file_size：单个文件大小（生产建议 1-4GB） innodb_log_files_in_group：文件数（默认 2） 总空间 = file_size × files_in_group 建议：能容纳 10-60 分钟的写入
23.3 redo log 的刷盘策略（innodb_flush_log_at_trx_commit）
值
行为
持久性
性能
0 每秒刷盘（提交时不刷）
弱（宕机丢 1 秒）
最高
1 （默认）每次提交都 fsync 刷盘 强 最低
2 每次提交写 OS Cache，每秒 fsync
中（OS 崩溃才丢）
高
生产核心业务必须设 =1，牺牲性能换不丢数据。可接受少量丢失的非核心业务可设 =2 提升性能。
24. undo log 详解 24.1 两个作用 1. 事务回滚（原子性 A） UPDATE 把 name 从 'A' 改成 'B' → undo log 记录 "name=A" 的反向操作 → ROLLBACK 时执行反向操作恢复 2. MVCC（一致性非锁定读） 事务执行 SELECT 时，通过 undo log 中的历史版本读到合适快照 → 见第 15 节 MVCC 详解
24.2 undo log 的类型
类型
触发
内容
insert undo INSERT
反操作是 DELETE，事务结束即可丢弃
update undo UPDATE / DELETE
反操作是恢复旧值，MVCC 需要时要保留 ，不能立即 purge
长事务会让 update undo 持续堆积，这是第 15.5 节强调”禁止长事务”的原因。
25. binlog 详解 25.1 binlog vs redo log
维度
redo log
binlog
所属层
InnoDB 引擎层
Server 层（所有引擎共用）
内容
物理 日志（页偏移 + 值）逻辑 日志（SQL 或行变更）
写入方式
循环写（覆盖旧的）
追加写（永不满，按文件滚动）
用途
崩溃恢复 主从复制 + 数据归档
写入时机
事务执行中持续写
事务提交时写
必须开启
是（默认）
默认关闭，复制/备份场景必须开
格式
内容
优点
缺点
STATEMENT 原始 SQL
日志小、可读
函数（NOW()、UUID()）在从库重放结果不一致
ROW （推荐 ）每行的变更（前镜像 + 后镜像）
准确、无歧义
日志大（大批量 UPDATE 日志暴涨）
MIXED 自动选择
折中
仍有 STATEMENT 的边角问题
互联网公司普遍用 ROW 格式，配合 binlog_row_image=FULL，是 CDC（如 Canal、Debezium）的基础。
25.3 binlog 的写入流程 1. 事务执行过程中，binlog 写入 binlog cache（内存，每事务一个） 2. 事务提交时： a. 把 binlog cache 写入文件系统 page cache（write） b. 根据 sync_binlog 决定是否 fsync 到磁盘 3. 写入完成后，给从库复制用 参数 sync_binlog： = 0：由 OS 决定何时 fsync（性能高，宕机可能丢） = 1：每次提交都 fsync（强持久）★ 推荐 = N：累计 N 次事务后 fsync（折中）
26. 两阶段提交（2PC）与崩溃恢复 26.1 为什么需要两阶段提交？ 问题 ：redo log 和 binlog 是两个独立的日志，写入顺序不一致会导致主从数据不一致。
反例（不用两阶段提交）： 场景 1：先写 redo log，后写 binlog 1. 写 redo log 成功 2. 写 binlog 之前宕机 → 主库恢复后，redo log 重放 → 数据已改 → 但 binlog 没写 → 从库同步不到 → 主从不一致 ★ 场景 2：先写 binlog，后写 redo log 1. 写 binlog 成功 2. 写 redo log 之前宕机 → 主库恢复后，redo log 没写 → 数据没改 → 但 binlog 写了 → 从库同步后会改 → 主从不一致 ★
26.2 两阶段提交流程 UPDATE 事务提交时： 阶段 1（Prepare）： 1. 写 redo log，标记为 PREPARE 状态 2. redo log fsync 刷盘 阶段 2（Commit）： 3. 写 binlog 并 fsync 4. 把 redo log 的状态从 PREPARE 改成 COMMIT 5. 返回客户端"提交成功" ┌────────────────────────────────────────────────┐ │ redo log: [PREPARE] → [写 binlog] → [COMMIT] │ │ │ │ 关键点：binlog 写成功后才算提交 │ │ binlog 写失败 → 整个事务回滚 │ └────────────────────────────────────────────────┘
26.3 崩溃恢复规则 重启后扫描 redo log： 1. redo log 已是 COMMIT 状态 → 事务一定提交成功 → 重放 2. redo log 是 PREPARE 状态 → 检查 binlog： - binlog 完整（有该事务的记录）→ 提交（认为已经传给从库了，要保证一致） - binlog 不完整 → 回滚（事务其实没提交） → 保证 redo log 和 binlog 的一致性 → 进而保证主从数据一致
27. redo log、binlog 与两阶段提交的关系图 事务提交 │ ▼ ┌──────────────────────────────┐ │ 阶段 1：Prepare │ │ 写 redo log（PREPARE 状态） │ │ redo log fsync 刷盘 │ └──────────────┬───────────────┘ │ ▼ ┌──────────────────────────────┐ │ 阶段 2：Commit │ │ 写 binlog + fsync │ │ redo log 改为 COMMIT 状态 │ └──────────────┬───────────────┘ │ ▼ 返回客户端成功 崩溃恢复（重启时）： ┌─ redo COMMIT ──────► 重放（已提交） └─ redo PREPARE ─────► 查 binlog： ├─ binlog 完整 ─► 提交（保证主从一致） └─ binlog 缺失 ─► 回滚
七、查询执行与 EXPLAIN 28. 查询执行流程回顾 SELECT a.name, b.order_no FROM user a JOIN orders b ON a.id = b.user_id WHERE a.age > 18 AND b.status = 1; 1. 连接器：建立连接、鉴权 2. 分析器：解析 SQL → AST，校验表名/列名 3. 优化器： a. 选驱动表（小表驱动大表）—— 假设 user 过滤后行数少 → user 驱动 b. 选择访问类型（type）：a 走 idx_age，b 走 idx_uid c. 选择 JOIN 算法：Nested Loop / Block Nested Loop / Hash Join（8.0+） 4. 执行器： for each row in a (where age > 18): ← 驱动表 for each row in b (where user_id = a.id and status = 1): ← 被驱动表 输出 (a.name, b.order_no) 5. 返回结果
29. EXPLAIN 详解 EXPLAIN SELECT * FROM user WHERE age > 18 ;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | user | NULL | range | idx_age | idx_age | 5 | NULL | 200 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
列
含义
重点关注值
id 查询序号，越大越先执行；相同则从上往下
JOIN 多表看驱动顺序
select_type SIMPLE / PRIMARY / SUBQUERY / DERIVED
复杂查询时看子查询类型
table 表名
—
type ★访问类型（性能关键） 见下表
possible_keys 可能用到的索引
看是否选错
key ★实际用的索引 NULL 表示没走索引
key_len 索引使用的字节数
判断联合索引用了几列
ref 索引比较的来源
const / 列名
rows ★估算扫描行数
越小越好
filtered 过滤后剩余比例（%）
越接近 100 越好
Extra ★附加信息
见下表
29.1 type 列从好到坏 system > const > eq_ref > ref > range > index > ALL ★★★ ★★★ 最佳 全表扫描
type
含义
例子
system 表只有一行
系统表
const 主键 / 唯一索引等值匹配
WHERE id=1（id 主键）
eq_ref JOIN 时被驱动表用主键/唯一索引等值
JOIN ON a.id=b.id（b.id 主键）
ref 普通索引等值匹配
WHERE name='x'（name 二级索引）
range 索引范围扫描
WHERE id BETWEEN 1 AND 10
index 扫整个索引（不回表）
COUNT(*) 走最小索引
ALL 全表扫描 ★无索引或索引失效
生产 SQL 至少要达到 range 级别，严禁 ALL。
值
含义
是否好事
Using index 命中覆盖索引，不回表
✅ 最佳
Using index condition 启用索引下推 ICP
✅
Using where Server 层过滤
⚠️ 一般
Using temporary 用了临时表（DISTINCT、GROUP BY 常见）
❌ 优化空间
Using filesort 文件排序（ORDER BY 没用索引）
❌ 优化空间
Using join buffer Block Nested Loop（无索引 JOIN）
❌ 加索引
Impossible WHERE 条件恒假
—
29.3 key_len 怎么算？ 判断联合索引用到了几列： idx(a, b, c) 类型都是 INT(4字节) NOT NULL WHERE a=1 → key_len=4（只用 a） WHERE a=1 AND b=2 → key_len=8（用 a+b） WHERE a=1 AND b=2 AND c=3 → key_len=12（全用） 字符类型：utf8mb4 下 VARCHAR(N) key_len = 4N + 2（变长标记） 允许 NULL 多加 1 字节
八、复制与高可用 30. 主从复制原理 30.1 三线程模型 主库（Master） 从库（Slave） ┌────────────────┐ ┌────────────────────┐ │ │ │ IO Thread │ │ 客户端写入 │ │ ↓ │ │ ↓ │ │ 请求 binlog │ │ 写 binlog │◄──────────────│◄─┘ │ │ ↓ │ ① 网络传输 │ │ │ binlog dump │ binlog event │ 写 relay log │ │ thread │──────────────►│ │ │ │ │ SQL Thread │ │ │ │ ↓ │ │ │ │ 重放 SQL 到数据 │ └────────────────┘ └────────────────────┘
三个线程 ：
主库： Binlog Dump Thread — 读 binlog 发给从库 从库： IO Thread — 接收 binlog，写入 relay log（中继日志） SQL Thread — 读 relay log，重放到从库数据
30.2 复制流程 1. 从库 IO Thread 连接主库，请求从某个 binlog 位置开始的日志 2. 主库 Binlog Dump Thread 读取 binlog，发送给从库 3. 从库 IO Thread 把日志写入 relay log 4. 从库 SQL Thread 读 relay log，重放（按 ROW 格式就是应用每行变更） 5. 从库数据与主库一致 关键位置点： - 主库 binlog 位点（master_log_file, master_log_pos） - 从库已执行的位点（relay log 读取进度）
30.3 复制的三种类型
类型
机制
数据安全
性能
异步复制 （默认）主库写完 binlog 立即返回，不等从库
主库宕机可能丢未同步数据
最高
半同步复制 （semi-sync）主库等至少一个从库收到 binlog 才返回
较好
略低
全同步复制 （组复制 MGR）主库等所有从库提交才返回
强一致
低
互联网业务大多用异步复制 + 高可用切换工具 （MHA / Orchestrator）。金融场景用半同步 + MGR 保证不丢。
30.4 主从延迟的原因与解决 从库 SQL Thread 是单线程重放（5.7 前），主库并发写 → 从库跟不上 → 延迟 症状： 写主库后立刻读从库 → 读不到（数据还没同步过来） → 这就是"读写分离后立刻读"的经典问题 解决： 1. 5.7+ 并行复制（基于组提交 group commit，多线程重放） 2. 关键写后读强制走主库（同会话路由） 3. 半同步复制（写主库时确保至少一个从库已收到） 4. 业务接受最终一致，用缓存兜底
31. 半同步复制与组复制（MGR） 31.1 半同步复制（Semi-Sync） 主库提交事务 → 写 binlog → 等待至少 1 个从库 ACK（确认收到 binlog） ← 关键 → ACK 到达后才返回客户端成功 参数： rpl_semi_sync_master_wait_for_slave_count = 1（至少几个从库 ACK） rpl_semi_sync_master_timeout = 10000（ms，超时降级为异步） 代价：写入延迟 = 网络往返时间（RTT） 好处：主库宕机时，至少有一个从库有最新数据 → 不丢
31.2 MySQL Group Replication（MGR，8.0+） 基于 Paxos 变种（XCom）的强一致集群： - 多个节点组成集群（建议奇数：3/5/7） - 写入需多数派（quorum）确认 - 自动故障检测与选主 - 支持多主模式（多主同时写） 对比传统主从： 传统：异步/半同步，主从角色固定，需外部工具切换 MGR：内置共识协议，自动选主，强一致 适用：金融场景、对一致性要求极高 代价：写入需 quorum 确认，延迟略高
32. 高可用方案
方案
原理
优点
缺点
MHA 监控主库，宕机时从最新从库提升新主
成熟稳定
配置复杂，已较少更新
Orchestrator 拓扑感知的故障切换
灵活、可视化
学习成本
MGR 内置共识的集群
强一致、自动切换
写性能有损耗
云数据库 RDS 云厂商托管（Aurora/PolarDB/TDSQL）
免运维、高可用
锁定云厂商
Keepalived + VIP VIP 漂移 + 健康检查
简单
脑裂风险
故障切换的核心难点（脑裂与丢数据） ：
主库宕机 → 选新主 → 但原主库可能没真死（只是网络抖动） → 出现两个主库同时接受写入 → 脑裂 防护（同 ES）： 1. Quorum（MGR 内置） 2. Fencing：原主库被隔离（如云厂商强制 detach 磁盘） 3. 半同步保证至少一个从库有最新数据
九、性能优化 33. 慢查询定位与优化 33.1 开启慢查询日志 SET GLOBAL slow_query_log = ON ;SET GLOBAL long_query_time = 1 ; SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log' ;SET GLOBAL log_queries_not_using_indexes = ON ; SHOW VARIABLES LIKE 'slow_query%' ;
分析工具 ：
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log pt-query-digest /var/log/mysql/slow.log
33.2 慢查询优化流程 1. EXPLAIN：看 type / key / rows / Extra type=ALL → 加索引 Extra=Using filesort → ORDER BY 字段加索引 Extra=Using temporary → GROUP BY 字段加索引 rows 过大 → 缩小过滤范围或优化索引 2. 索引优化： - 检查索引是否失效（函数、隐式转换、最左前缀、LIKE %x） - 高频查询的列组合做联合索引（覆盖索引优先） - 区分度低的列（如性别）单独建索引意义不大 3. SQL 重写： - SELECT * → 只取需要的列 - 大 IN → 用 JOIN 或临时表 - 深度分页 → 游标分页（见 33.3） 4. 表结构优化： - 大字段拆出（TEXT/BLOB 单独表） - 适当冗余避免 JOIN - 合理反范式
33.3 深度分页优化 为什么 LIMIT 1000000, 10 慢？
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10; 执行流程： 1. 走聚簇索引（id 有序），扫描前 1000010 行 2. 丢弃前 1000000 行，返回最后 10 行 → 即使走索引，也要扫描 100 万行 ★ EXPLAIN：rows=1000010，Extra=NULL（覆盖索引也救不了，因为 SELECT *）
优化方案：延迟关联 / 游标分页
SELECT * FROM orders oJOIN ( SELECT id FROM orders ORDER BY id LIMIT 1000000 , 10 ) t ON o.id = t.id; SELECT * FROM ordersWHERE id > #{last_id}ORDER BY id LIMIT 10 ;
34. 分库分表 34.1 什么时候需要分？ 经验阈值（不是硬性）： - 单表行数 > 1000 万 - 单表数据 > 50GB - 单库 QPS > 5000 / 写 QPS > 1000 - 单库磁盘 / 内存 / 连接数接近瓶颈 → 先优化 SQL、索引、表结构，最后才考虑分 → "过早分库分表" 是反模式
34.2 垂直拆分 vs 水平拆分 垂直拆分（按字段/业务）： user 表 (id, name, age, intro, avatar, profile_json) → user_base (id, name, age) ← 高频小字段 → user_ext (id, intro, avatar, ...) ← 低频大字段 或按业务拆库： user_db / order_db / product_db 水平拆分（按行）： orders 表 → 拆成 orders_0, orders_1, ..., orders_N 按 user_id 取模路由：orders_{user_id % N}
34.3 分片键（Sharding Key）选择 核心原则：查询尽量能带上分片键，避免跨分片 订单表按 user_id 分片： SELECT * FROM orders WHERE user_id = 123; ✅ 单分片 SELECT * FROM orders WHERE order_no = 'X'; ❌ 广播到所有分片 折中：订单表同时按 user_id 分片，order_no 反向索引表（order_no → user_id） 查询路径： 1. 用 order_no 查反向表 → 得到 user_id 2. 用 (user_id, order_no) 查主表
34.4 分库分表带来的难题
问题
说明
解决
跨库 JOIN 不同分片上的表无法 JOIN
应用层组装、冗余字段、Elasticsearch 辅助
分布式事务 涉及多库的写需要事务
XA、TCC、Saga、最终一致（消息表）
全局唯一 ID 自增主键失效
雪花算法（Snowflake）、号段模式（如 Leaf）
跨库分页排序 LIMIT 10 要在每个分片取前 10 再合并改造成游标分页或用 ES
扩容迁移 分片数变化要重新分数据
一致性哈希、2 倍扩容法
34.5 常用中间件
中间件
模式
特点
ShardingSphere-JDBC Client 模式
嵌入应用，轻量，无需额外部署
ShardingSphere-Proxy Proxy 模式
独立部署，应用透明
MyCat Proxy 模式
老牌，社区活跃度下降
Vitess Proxy 模式
YouTube 开源，云原生
TiDB 原生分布式
兼容 MySQL 协议，无需改造
现代趋势：TiDB / OceanBase / CockroachDB 等原生分布式数据库，业务无感扩展。
35. SQL 编写最佳实践 SELECT * FROM user WHERE id = 1 ;SELECT name, age FROM user WHERE id = 1 ;SELECT * FROM user WHERE id IN (1 ,2 ,...,10000 );SELECT u.* FROM user u JOIN tmp_ids t ON u.id = t.id;SELECT * FROM user WHERE name= 'a' OR age= 10 ;SELECT * FROM user WHERE name= 'a' UNION ALL SELECT * FROM user WHERE age= 10 ;SELECT * FROM user WHERE id IN (SELECT user_id FROM orders);SELECT u.* FROM user u JOIN orders o ON u.id = o.user_id;SELECT * FROM user WHERE name LIKE '%abc' ;INSERT INTO user (name, age) VALUES ('a' ,1 ),('b' ,2 ),('c' ,3 );
十、运维与监控 36. 备份与恢复
方式
工具
特点
逻辑备份 mysqldump、mysqlpump导出 SQL 语句，慢但通用、可跨版本
物理备份 xtrabackup（Percona）复制数据文件，快、适合大库
binlog 增量 mysqlbinlog基于归档日志恢复到任意时间点（PITR）
mysqldump -uroot -p --single-transaction --master-data=2 \ --routines --triggers --events --all-databases > backup.sql xtrabackup --backup --target-dir=/backup/full xtrabackup --prepare --target-dir=/backup/full xtrabackup --copy-back --target-dir=/backup/full mysqlbinlog --start-datetime="2026-06-21 00:00:00" \ --stop-datetime="2026-06-22 10:00:00" \ mysql-bin.000123 | mysql -uroot -p
37. 常用监控指标
指标
含义
告警阈值
QPS / TPS 每秒查询/事务数
视容量
慢查询数 慢查询计数
持续增长告警
连接数 当前连接 / 最大连接
> 80% 报警
Buffer Pool 命中率 innodb_buffer_pool_read_requests< 95% 告警
锁等待 innodb_row_lock_waits持续增长告警
主从延迟 Seconds_Behind_Master> 10s 告警
redo log 使用率 —
接近满告警
长事务 持续时间 > 60s 的事务
告警
SHOW STATUS LIKE 'Threads_%' ;SHOW STATUS LIKE 'innodb_buffer_pool_read%' ;SELECT trx_id, trx_started, trx_state, trx_queryFROM information_schema.innodb_trxWHERE TIMESTAMPDIFF(SECOND , trx_started, NOW()) > 60 ;SHOW SLAVE STATUS\G
面试常问
MySQL 架构 — Server 层和引擎层各做什么？binlog 属于哪层？redo/undo 呢？B+ 树为什么做索引 — 为什么不用红黑树/哈希/B 树？B+ 树的两个核心优势（扇出大、叶子链表）？三层 B+ 树能存多少行 — 2000 万是怎么算的？聚簇索引 vs 二级索引 — 数据存在哪？为什么一张表只能有一个聚簇索引？回表是什么？覆盖索引与索引下推 — Using index 和 Using index condition 的区别？ICP 减少了什么开销？最左前缀 — 联合索引 (a,b,c) 哪些查询能用上？范围查询为什么”打断”索引？索引失效场景 — 函数、隐式类型转换、LIKE '%x'、OR、!=，怎么排查（EXPLAIN）？MVCC 原理 — 隐藏列 trx_id/roll_ptr？undo log 版本链？ReadView 的可见性规则？RC 和 RR 生成 ReadView 的时机差异？快照读 vs 当前读 — 哪些 SQL 是当前读？为什么 UPDATE 要当前读？隔离级别 — InnoDB 默认 RR，为什么能防幻读？(MVCC + Next-Key Lock)行锁/间隙锁/Next-Key Lock — 三者区别？RR 下不同查询加什么锁？无索引列更新为什么锁全表？死锁 — 怎么产生？InnoDB 怎么检测？怎么避免？redo log vs binlog — 物理日志 vs 逻辑日志？崩溃恢复 vs 主从复制？为什么需要两阶段提交？两阶段提交 — Prepare 和 Commit 阶段各做什么？崩溃后 PREPARE 状态怎么处理？主从复制 — 三个线程分别在哪？为什么有延迟？半同步复制怎么解决数据丢失？EXPLAIN — type 列从好到坏？Extra 的 Using index 和 Using filesort 是什么意思？深度分页 — LIMIT 1000000,10 为什么慢？延迟关联和游标分页怎么优化？分库分表 — 什么时候分？垂直 vs 水平？分片键怎么选？带来的难题怎么解决？
常用运维命令 SHOW VARIABLES LIKE 'innodb_buffer_pool_size' ;SHOW VARIABLES LIKE 'transaction_isolation' ;SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit' ;SHOW VARIABLES LIKE 'sync_binlog' ;SHOW ENGINE INNODB STATUS\GSHOW TABLE STATUS LIKE 'user' \GSHOW INDEX FROM user ;EXPLAIN SELECT * FROM user WHERE id = 1 ; EXPLAIN ANALYZE SELECT * FROM user JOIN orders ON ...; SHOW PROCESSLIST;SELECT * FROM information_schema.innodb_trx;SELECT * FROM performance_schema.data_locks;SELECT * FROM performance_schema.data_lock_waits;KILL < thread_id> ; SHOW MASTER STATUS; SHOW SLAVE STATUS\G SET GLOBAL slow_query_log = ON ;SET GLOBAL long_query_time = 1 ;SET GLOBAL log_queries_not_using_indexes = ON ;
mysqldump -uroot -p --single-transaction --master-data=2 dbname > backup.sql mysql -uroot -p dbname < backup.sql mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000123 mysqldumpslow -s t -t 10 slow.log pt-query-digest slow.log mysqladmin -uroot -p extended-status -r -i 1 | grep -E "Threads|Questions"