Week 2 · Chapter 4 · MySQL

复习难度：⭐⭐⭐ | 预计时长：3小时 | 重点程度：高

4.1 索引结构与算法

原理

MySQL InnoDB 使用 B+ 树作为索引结构（注意不是 B 树）。

B+ 树 vs B 树： - B 树：所有节点都存数据，B+ 树只有叶子节点存数据 - B+ 树：叶子节点用双向链表连接，支持范围查询 O(1) - B+ 树高度：千万级数据约 3~4 层，IO 次数可控

InnoDB 聚集索引（Clustered Index）： - 主键索引的叶子节点直接存储完整行数据（行储存在索引的叶子节点上） - 非主键索引（辅助索引）叶子节点存主键 ID，查询时需要回表

-- 辅助索引查询过程（回表）
SELECT * FROM orders WHERE order_no = 'A12345';
-- ① 辅助索引树找到 order_no 对应的主键 ID
-- ② 再用主键 ID 去聚集索引树查找完整行（回表）

常见面试题

Q1：为什么 MySQL 用 B+ 树而不是 B 树？

答：① B+ 树所有数据在叶子节点，非叶子节点只存索引（单节点可容纳更多索引），树更扁平，IO 次数更少；② 叶子节点链表支持范围查询高效；③ 叶子节点在磁盘连续性好，顺序 IO 友好。

Q2：联合索引的最左前缀原则原理？

答：联合索引 (A, B, C) 底层按 A→B→C 顺序构建 B+ 树，必须从 A 开始连续使用才能命中索引。查询 WHERE A=1 AND C=2 可以用索引（只用到 A），WHERE B=1 AND C=2 无法使用（A 断了）。

Q3：覆盖索引是什么？如何减少回表？

答：查询的所有列都在索引中，无需回表： sql CREATE INDEX idx_order ON orders(order_no, user_id, amount); -- 查询只需 order_no, user_id, amount → 直接从索引返回，无需回表

4.2 事务与 MVCC

原理

事务四大特性（ACID）： - Atomicity：原子性 — Redo Log + Undo Log 保证 - Consistency：一致性 — 事务执行前后状态一致 - Isolation：隔离性 — 由锁和 MVCC 控制 - Duration：持久性 — Redo Log 刷盘保证

MVCC（Multi-Version Concurrency Control）：

InnoDB 每行数据隐藏两列： - DB_TRX_ID：最近修改的事务 ID - DB_ROLL_PTR：指向 Undo Log 的指针（可构建旧版本）

快照读 vs 当前读： - 快照读：SELECT ...（读取历史版本，无锁） - 当前读：SELECT ... LOCK IN SHARE MODE / FOR UPDATE、INSERT/UPDATE/DELETE（读取最新数据，加锁）

Read View（快照视图）生成时机： - READ COMMITTED：每次当前读都生成新 Read View - REPEATABLE READ（InnoDB 默认）：事务第一次读时生成，后续复用

Read View 结构：
- m_ids：活跃事务列表
- min_trx_id：最小活跃事务ID
- max_trx_id：创建 Read View 时最大事务ID+1
- creator_trx_id：当前事务ID

判断可见性：trx_id < min_trx_id → 可见（已提交）
           trx_id in m_ids → 不可见（活跃中）
           trx_id >= max_trx_id → 不可见（快照后开启）

常见面试题

Q1：RR 隔离级别下，MVCC 能否完全解决幻读？

答：不能完全解决。MVCC 只解决快照读的幻读（SELECT），但当前读（INSERT/UPDATE/DELETE）仍会产生幻读，InnoDB 通过 Next-Key Lock（记录锁+间隙锁）来抑制当前读场景的幻读。

Q2：Undo Log 何时被清理？

答：当没有事务需要用到 Undo Log 中的旧版本数据时（即没有更早的 Read View 引用它），由 purge 线程清理。

4.3 MySQL 锁

原理

锁类型： - 共享锁（S）：允许同时持有，多用于读 - 排他锁（X）：独占，写操作获取 - 意向锁：表级锁，事务获取行锁前先加意向锁（IX/IS），方便表锁判断

Next-Key Lock（RR 级别）： - 记录锁（Record Lock）：锁索引记录本身 - 间隙锁（Gap Lock）：锁索引之间的间隙，防止插入 - Next-Key Lock = 记录锁 + 间隙锁

-- 间隙锁示例
SELECT * FROM orders WHERE id BETWEEN 10 AND 20 FOR UPDATE;
-- 锁住 (10, 20) 这个区间，阻止其他事务插入 id=10~20 的新行

死锁： - 4 个必要条件：互斥、占有并等待、不可强占、循环等待 - InnoDB 用等待图（wait-for graph）检测死锁，回滚 undo 量最小的事务

常见面试题

Q1：行锁一定比表锁性能好吗？

答：不一定。行锁需维护更多的锁资源（锁升级成本），且可能产生死锁。大量扫描全表时表锁开销反而更小（一次锁整表）。InnoDB 在偏移量大的范围查询时会自动锁住全表（锁升级）。

Q2：如何排查死锁？

答：① 开启 innodb_print_all_deadlocks 打印到 error log；② SHOW ENGINE INNODB STATUS 查看最近死锁信息；③ 分析两个事务互相等待的 SQL，优化索引减少锁范围。

4.4 慢查询优化

原理

慢查询分析流程：

1. 开启慢查询日志：slow_query_log=1, long_query_time=1
2. 分析 EXPLAIN 输出
3. 定位问题：type / key / Extra / rows
4. 优化：加索引 / 改写 SQL / 减少回表 / 分库分表

EXPLAIN 关键字段： | 字段 | 好值 | 差值 | |------|------|------| | type | ref, range, const | ALL（全表扫描）| | key | 使用了索引名 | NULL | | Extra | Using index, Using filesort | Using temporary |

Filesort 优化： - 无索引时用 filesort（内存/磁盘文件排序），order by 字段有索引则直接用索引顺序 - 两次扫描：先读出行主键排序，再回表读数据（随机 IO）

常见面试题

Q1：索引使用了但还是慢，可能原因？

答：① 回表次数太多（辅助索引选择性差，如性别字段加索引）；② 数据量太小（全表扫描反而更快）；③ 索引不是最优（覆盖索引未命中）；④ 统计信息不准确（ANALYZE TABLE 重新统计）

Q2：深分页（OFFSET N, M）如何优化？

答： ```sql -- 方案1：延迟关联（利用覆盖索引） SELECT * FROM orders INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 20) AS t ON orders.id = t.id;

-- 方案2：游标分页（记录上一页最后ID） SELECT * FROM orders WHERE id > last_id ORDER BY id LIMIT 20; ```

4.5 分库分表

原理

分库分表策略： - 水平分库/分表：按行拆分，如按 user_id % n - 垂直分库/分表：按列拆分，如把大字段单独放一张表

分片键选择原则： 查询频率最高的字段，避免跨分片查询

跨分片查询解决方案： - 枚举分片：固定 ShardKey，可跨节点 JOIN（但有瓶颈） - 异构索引表：每张分片维护一份按查询字段的索引表 - 广播表：小表在所有分片冗余存储 - ES/Hive：复杂查询走 OLAP 引擎

分库分表后的问题： - 分布式 ID 生成（雪花算法 / 百度 UidGenerator） - 分布式事务（两阶段提交 / 本地消息表 / TCC） - 跨分片分页排序（聚合层合并结果）

常见面试题

Q1：分库分表后 ID 如何生成？

答：① 雪花算法（最常用）：时间戳 + 机器ID + 序列号，趋势递增；② 数据库自增：单独建一张发号器表，每台机器 step=n 步长隔离；③ UUID（不推荐，无序，存储空间大）

Q2：如何做到不停服迁移？

答：双写方案：① 部署新库（分片规则）；② 开启双写（新库+老库）；③ 数据同步（CDC 或历史数据批量迁移 + 增量补偿）；④ 灰度切读；⑤ 确认无误后下线老库。

📝 本章高频问题速记

问题 核心答案
MySQL 索引结构 B+ 树（不是 B 树）
聚集索引 叶子节点存完整行数据
回表 辅助索引查到主键后再查聚集索引
最左前缀原则 联合索引必须从最左列连续使用
MVCC 可见性判断 Read View + trx_id 比较
RR 能否完全防幻读 快照读可防，当前读需 Next-Key Lock
死锁处理 等待图检测，Rollback undo量最小的事务
深分页优化 延迟关联 / 游标分页
分片 ID 生成 雪花算法 / 步长自增