本文件是 01-MySQL深度指南.html 的精简面试版，用图书馆类比讲解核心面试题，适合快速突击。
01-MySQL深度指南.html 是完整版（架构→InnoDB→索引→事务→MVCC→锁→优化→主从→35道面试题），含8张CSS架构图，适合深度理解。
索引底层 · B+树 · MVCC · 锁机制 · SQL优化 · 日志系统
| 对比 | B 树 | B+ 树（MySQL 选择） |
|---|---|---|
| 数据位置 | 所有节点都存数据 | 数据只在叶子节点 |
| 树的高度 | 非叶节点也存数据，占空间大 | 非叶节点只存 key，一个页能存更多 key |
| 范围查询 | 需要中序遍历整棵树 | 叶子节点双向链表，顺序遍历即可 |
| 查询稳定性 | 不稳定（数据可能在非叶节点就找到） | 稳定（一定走到叶子节点） |
| 磁盘 IO | 可能更多 | 更少（3~4 层即可存千万数据） |
B 树 = 字典每页都混着词和解释，找到词可能早可能晚；B+ 树 = 目录页只有词，解释全在正文页，正文页用链条串好 → 查"图书馆→图书馆"范围，从"图书馆"顺着链一路翻就行。
InnoDB 一个页 16KB：
• 非叶节点：主键 BIGINT(8B) + 指针(6B) = 14B/条，一页约 1170 条
• 叶子节点：一行数据假设 1KB，一页约 16 条
• 3 层 B+ 树：1170 × 1170 × 16 = 约 2190 万行
所以千万级数据，3 次 IO 即可定位！
| 对比 | InnoDB | MyISAM |
|---|---|---|
| 事务 | ✅ 支持 | ❌ 不支持 |
| 行锁 | ✅ 行级锁 | 只有表级锁 |
| MVCC | ✅ 支持 | ❌ 不支持 |
| 外键 | ✅ 支持 | ❌ 不支持 |
| 崩溃恢复 | ✅ Redo Log | 可能丢数据 |
| 索引结构 | 聚簇索引（数据和主键索引在一起） | 非聚簇（索引和数据分离） |
| COUNT(*) | 需要遍历（因为 MVCC） | 直接读存储的行数 |
面试必问：为什么选 InnoDB？答案：事务 + 行锁 + MVCC + 崩溃恢复，互联网应用刚需。MyISAM 只适合读多写少且不需要事务的场景（如日志表）。
| 对比 | 聚簇索引 | 非聚簇索引（二级索引） |
|---|---|---|
| 数据存储 | 叶子节点存完整行数据 | 叶子节点存主键值 |
| 数量 | 只能有一个（主键索引） | 可以有多个 |
| 查询方式 | 直接拿到数据 | 先查到主键 → 回表查聚簇索引 |
聚簇索引 = 图书馆按编号排列的借书单本（编号和内容在一起）；二级索引 = 按口味做的目录（"芋泥味→借书单号3、7、12"），查到了号还要翻回借书单本看详情 = 回表。
联合索引 (a, b, c) 等效于建了 3 个索引：
• (a) ✅ • (a, b) ✅ • (a, b, c) ✅
• (b) ❌ • (c) ❌ • (b, c) ❌ （缺少最左列 a）
遇到范围查询（>, <, BETWEEN, LIKE 'xx%'）会停止匹配后续列：
WHERE a=1 AND b>2 AND c=3 → 只有 a 和 b 走索引，c 走不了
| 优化 | 含义 | 效果 |
|---|---|---|
| 覆盖索引 | 查询的列都在索引中，不需要回表 | 减少 IO，性能大幅提升 |
| 索引下推 ICP | 在索引遍历过程中，先在存储引擎层按索引列过滤 | 减少回表次数（MySQL 5.6+） |
① 对索引列做函数/计算：WHERE YEAR(create_time) = 2025 ❌
② 隐式类型转换：varchar 列用 int 查 ❌
③ LIKE 左模糊：WHERE name LIKE '%张' ❌（'张%' ✅）
④ OR 条件中有无索引列 ❌
⑤ 不满足最左前缀 ❌
⑥ NOT IN / !=（优化器可能放弃索引）
⑦ 优化器判断全表更快（数据量小时）
面试高频："索引什么时候会失效？"——重点记住 函数/计算、隐式转换、左模糊、最左前缀这四个就够了。
| 字段 | 大小 | 作用 |
|---|---|---|
| DB_TRX_ID | 6B | 最近修改该行的事务 ID |
| DB_ROLL_PTR | 7B | 回滚指针，指向 Undo Log 中上一个版本 |
| DB_ROW_ID | 6B | 隐藏自增 ID（无主键时自动创建） |
生成 Read View 时记录 4 个值：
• m_ids：当前所有活跃事务 ID 列表
• min_trx_id：m_ids 中最小的事务 ID
• max_trx_id：下一个将分配的事务 ID
• creator_trx_id：创建该 Read View 的事务 ID
判断逻辑：
① trx_id == creator_trx_id → 可见（自己的修改）
② trx_id < min_trx_id → 可见（事务已提交）
③ trx_id >= max_trx_id → 不可见（Read View 后才开启的事务）
④ min_trx_id <= trx_id < max_trx_id 且在 m_ids 中 → 不可见；不在 → 可见
| 隔离级别 | Read View 生成时机 | 效果 |
|---|---|---|
| RC（读已提交） | 每次 SELECT 都生成新 Read View | 两次 SELECT 可能结果不同（不可重复读） |
| RR（可重复读） | 只在第一次 SELECT 时生成 Read View | 同一事务中读到的数据一致（可重复读） |
RC = 每次看索引卡都重新拍照（看到最新修改）；RR = 只在第一次拍照，后面都用这张照片（看到的一直是开始时的状态）
面试中 MVCC 要能说出三点：① 隐藏字段（trx_id + roll_ptr） ② Undo Log 版本链 ③ Read View 可见性规则。RC 和 RR 的区别就是 Read View 生成时机不同。
| 锁类型 | 锁定范围 | 目的 |
|---|---|---|
| Record Lock（记录锁） | 锁定单条索引记录 | 防止修改/删除该行 |
| Gap Lock（间隙锁） | 锁定索引记录之间的间隙 | 防止幻读（阻止插入新记录） |
| Next-Key Lock（临键锁） | Record Lock + Gap Lock（左开右闭） | InnoDB 默认行锁类型 |
| 类型 | 含义 | SQL | 加锁？ |
|---|---|---|---|
| 快照读 | 读 MVCC 快照版本 | 普通 SELECT | 不加锁 |
| 当前读 | 读最新已提交数据 | SELECT FOR UPDATE / UPDATE / DELETE / INSERT | 加锁！ |
• 快照读：通过 MVCC 解决
• 当前读：通过 Next-Key Lock 解决（锁住记录 + 间隙）
注意：先快照读后当前读，可能看到新行。严格防幻读需要全程加锁读。
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | InnoDB 实现 |
|---|---|---|---|---|
| READ UNCOMMITTED | ❌ | ❌ | ❌ | — |
| READ COMMITTED (RC) | ✅ | ❌ | ❌ | MVCC（每次SELECT新建ReadView） |
| REPEATABLE READ (RR) | ✅ | ✅ | 基本✅ | MVCC + Next-Key Lock |
| SERIALIZABLE | ✅ | ✅ | ✅ | 所有 SELECT 加共享锁 |
InnoDB 默认 RR，且在 RR 下通过 MVCC + Next-Key Lock 基本解决了幻读。
面试陷阱：面试官问"RR 能完全解决幻读吗？"——快照读通过 MVCC 解决，当前读通过 Next-Key Lock 解决。但先普通 SELECT 再 FOR UPDATE 可能出现"幻读"现象，要答出这个细节。
| 字段 | 含义 | 重点关注 |
|---|---|---|
| type | 访问类型 | const > eq_ref > ref > range > index > ALL（ALL = 全表扫描❌） |
| key | 实际用到的索引 | NULL = 没用索引 |
| rows | 预估扫描行数 | 越小越好 |
| Extra | 额外信息 | Using index ✅ / Using filesort ❌ / Using temporary ❌ |
① 避免 SELECT *，只查需要的列（覆盖索引）
② 小表驱动大表：IN 适合子查询结果小，EXISTS 适合外表小
③ LIMIT 优化深分页：游标法代替 LIMIT offset, n
④ JOIN 用小结果集驱动，关联字段要有索引
⑤ UNION ALL 代替 UNION（不需要去重时）
⑥ 批量 INSERT：一条 INSERT 多个 VALUES
⑦ 避免在索引列上做运算/函数
⑧ 区分度高的列放联合索引前面
⑨ 避免子查询，改用 JOIN
⑩ 大表加索引用 pt-online-schema-change
| 日志 | 作用 | 写入时机 | 属于 |
|---|---|---|---|
| Redo Log | 崩溃恢复，保证持久性（D） | 事务提交时写（WAL） | InnoDB 引擎 |
| Undo Log | 回滚 + MVCC 版本链，保证原子性（A） | 修改数据前先写 | InnoDB 引擎 |
| Binlog | 主从复制 + 数据恢复 | 事务提交时写 | MySQL Server 层 |
| 对比 | Redo Log | Binlog |
|---|---|---|
| 归属 | InnoDB 引擎层 | MySQL Server 层 |
| 内容 | 物理日志（哪个页改了什么） | 逻辑日志（SQL 或行变更） |
| 写入方式 | 循环写（固定大小，会覆盖） | 追加写（不覆盖，归档） |
| 用途 | 崩溃恢复（crash-safe） | 主从复制、数据恢复 |
为什么需要两阶段提交？
保证 Redo Log 和 Binlog 的一致性。如果只写一个，主从数据会不一致：
• 只写 Redo Log 不写 Binlog → 主库已提交，从库没收到
• 只写 Binlog 不写 Redo Log → 崩溃恢复后主库丢了修改
面试必问：两阶段提交是 Redo Log 和 Binlog 一致性的保证。说出"prepare → 写 Binlog → commit"三步，以及"保证主从数据一致"就够了。
4 个原因：
① 非叶节点只存 key，一个页存更多 key → 树更矮 → IO 更少
② 叶子节点双向链表，范围查询只需顺序扫描
③ 查询性能稳定，一定走到叶子节点
④ 3 层 B+ 树可存千万级数据
回表：通过二级索引查到主键 → 再回聚簇索引查完整数据。两次索引查找。
避免方法：覆盖索引——查询的列都在索引中，不需要回表。
EXPLAIN 中 Extra 显示 Using index 就是覆盖索引。
MVCC = 多版本并发控制，让读不加锁，读写不冲突。
三大组件：
① 隐藏字段：DB_TRX_ID（事务ID）+ DB_ROLL_PTR（回滚指针）
② Undo Log 版本链：通过 roll_ptr 串成链表
③ Read View：判断当前事务能看到哪个版本
• 全局锁：FTWRL，全库只读（备份用）
• 表级锁：表锁、元数据锁（MDL）、意向锁（IS/IX）
• 行级锁：Record Lock（记录锁）、Gap Lock（间隙锁）、Next-Key Lock（临键锁=记录+间隙）
InnoDB 默认行锁类型是 Next-Key Lock，用于防止幻读。
• Redo Log 是InnoDB 引擎层的物理日志，循环写，用于崩溃恢复
• Binlog 是Server 层的逻辑日志，追加写，用于主从复制和数据恢复
• 通过两阶段提交保证两者一致
Redo Log 写入分两步：prepare → 写 Binlog → commit
保证 Redo Log 和 Binlog 一致：
• 如果 prepare 后崩溃 → 回滚（Binlog 没写，从库没收到）
• 如果写完 Binlog 后崩溃 → 提交（Binlog 已写，从库已收到）
不用两阶段 → 主从数据可能不一致！
• 聚簇索引：叶子节点存完整行数据，一个表只能有一个（主键索引）
• 非聚簇索引（二级索引）：叶子节点存主键值，查到后需回表查聚簇索引
InnoDB 中主键是聚簇索引，其他索引都是二级索引。
① EXPLAIN 看执行计划（type/key/rows/Extra）
② 确认走了索引，避免全表扫描
③ 避免 SELECT *，用覆盖索引
④ 深分页用游标法或延迟关联
⑤ 小表驱动大表
⑥ 避免索引列做运算/函数
⑦ 查看慢查询日志定位问题 SQL
ACID：
• A 原子性：事务不可分割 → Undo Log 保证
• C 一致性：数据从一个一致状态到另一个 → 由 A/I/D 共同保证
• I 隔离性：并发事务互不干扰 → 锁 + MVCC 保证
• D 持久性：提交后永久保存 → Redo Log 保证
意向锁是表级锁，表示事务打算对表中的行加行锁。
• IS 锁：打算加行级共享锁
• IX 锁：打算加行级排他锁
作用：快速判断表中是否有行锁。加表锁时不用逐行检查，直接看是否有意向锁。
• 自增主键写入是顺序写，追加到 B+ 树末尾，页分裂少
• 非自增主键写入是随机写，可能频繁页分裂和页合并 → 性能差
• 自增主键占空间小（INT 4B / BIGINT 8B），二级索引存的值小 → 更省空间
• Gap Lock 锁住两条记录之间的间隙，防止其他事务在此间隙插入新记录
• 只在 RR（可重复读）隔离级别下生效
• RC 下没有 Gap Lock，所以 RC 下会有幻读问题
• Gap Lock 之间不互斥（多个事务可以同时对同一间隙加 Gap Lock）
• count(*)：统计总行数，InnoDB 会选最小的索引遍历（推荐）
• count(1)：和 count(*) 效果一样，无本质区别
• count(字段)：统计该字段非 NULL 的行数，需要判断是否为 NULL，更慢
WAL = Write-Ahead Logging，先写日志，再写磁盘。
• 修改数据时先写 Redo Log 到磁盘（顺序写，快）
• 内存中的脏页择时刷回磁盘（随机写，慢）
• 崩溃时通过 Redo Log 恢复数据
好处：顺序写 >> 随机写，性能大幅提升。
InnoDB 的内存缓冲池，缓存磁盘上的数据页和索引页。
• 读取数据时先从 Buffer Pool 取，没有再从磁盘读
• 修改数据时先改 Buffer Pool（脏页），再择时刷回磁盘
• LRU 链表管理页面淘汰（改良版：冷热分区，防全表扫描污染）
• 一般设为物理内存的 60%~80%
B+树速记：
数据只在叶子 | 叶子双向链表 | 3层=千万行 | 非叶只存key
索引速记：
聚簇=数据在一起(1个) | 二级=存主键需回表 | 最左前缀 | 覆盖索引免回表
MVCC速记：
隐藏字段+Undo版本链+ReadView | RC每次新建 | RR只建一次
锁速记：
Record=行 | Gap=间隙 | Next-Key=行+间隙 | 快照读无锁 | 当前读加锁
日志速记：
Redo=崩溃恢复(物理/循环) | Undo=回滚+MVCC | Binlog=主从复制(逻辑/追加)
两阶段速记：
索引失效速记：
函数 | 隐式转换 | 左模糊 | OR有无索引列 | 最左前缀
ACID速记：
A=Undo | C=AID合力 | I=锁+MVCC | D=Redo
"索引有哪些类型？" → "为什么用 B+ 树？" → "什么是回表？覆盖索引？" → "索引什么时候失效？"
"事务 ACID？" → "MVCC 怎么实现？" → "RC 和 RR 区别？" → "RR 能完全防幻读吗？"
"MySQL 有哪些锁？" → "Next-Key Lock 是什么？" → "怎么避免死锁？" → "Gap Lock 什么时候用？"
"Redo Log 和 Binlog 区别？" → "两阶段提交？" → "为什么需要？" → "主从复制怎么实现的？"
MySQL三原则：① 索引设计优先于SQL优化 → ② 避免在WHERE条件中使用函数（索引失效）→ ③ 大查询用EXPLAIN验证执行计划