索引-事务-锁与调优

阶段：数据库篇 ｜ 建议时长：7 天 ｜ 前置：01-MySQL基础与SQL实战.md

学习目标

• 能从磁盘 IO 角度解释 InnoDB 为什么选 B+ 树，并说清聚簇索引、回表、覆盖索引、最左前缀。
• 能画出 undo log 版本链 + ReadView，解释 MVCC 如何实现 RC/RR 两种隔离级别。
• 能说清 redo/undo/binlog 三大日志的分工与两阶段提交的必要性。
• 能用 explain 定位慢 SQL，独立完成本篇 3 条慢 SQL 的优化。
• 能讲出主从复制原理与分库分表的触发条件、代价、常见方案。

核心知识点

1. 索引：为什么用 B+ 树 ⭐

面试官想听：不是背”B+ 树好”，而是能拿哈希/红黑树/B 树逐个对比出原因。

索引本质：一张排好序的、以空间换时间的查找结构。InnoDB 以页（默认 16KB）为最小 IO 单位，衡量指标就是”找一行数据要几次页 IO”。

高度估算（InnoDB 默认 16KB 页）：
非叶子节点：bigint 主键 8B + 页指针 6B ≈ 一页放 1170 项
叶子节点：按一行 1KB 估算，一页放约 16 行
3 层容量 ≈ 1170 × 1170 × 16 ≈ 2000 万行 → 3 次 IO 定位任意一行
范围查询：定位起点后沿叶子链表顺序扫描即可

聚簇索引 vs 二级索引：聚簇索引即主键索引，叶子节点存整行数据（所以表本身就是一棵 B+ 树）；二级索引（辅助索引）叶子节点只存”索引列 + 主键值”。用二级索引查非覆盖列时，要先拿主键再回聚簇索引查整行，这一步叫回表。

覆盖索引：查询的列全部包含在某个索引里，无需回表，explain 的 Extra 显示 Using index。常用手段：把 SELECT 需要的列加进联合索引，或至少 SELECT id（二级索引自带主键）。

最左前缀原则：联合索引 (a, b, c) 相当于建了 a、(a,b)、(a,b,c) 三套索引能力；查询条件必须从最左列开始连续命中，遇到范围查询（>、<、between、like ‘xx%‘）后面的列停止走索引。设计口诀：等值列在前、范围列在后、尽量覆盖查询字段。

索引下推（ICP）：MySQL 5.6+ 默认开启，联合索引中即使某列不能直接走索引匹配，也可在引擎层先过滤，减少回表次数。

2. 索引失效的常见场景 ⭐

面试官想听：能结合 explain 说出至少 5 种失效原因，而不是死记硬背。

-- 以下写法都会让 idx_name 等索引失效或部分失效
WHERE LEFT(name, 1) = '张'          -- 1. 对索引列使用函数
WHERE name = '张' OR age = 18       -- 2. OR 两侧存在无索引的列
WHERE name LIKE '%三'               -- 3. 左模糊（'张%' 不失效）
WHERE phone = 13800000000           -- 4. 隐式类型转换：varchar 列传了数字
WHERE age + 1 = 20                  -- 5. 对索引列做运算
WHERE b = 2                         -- 6. 联合索引 (a,b) 跳过最左列 a
WHERE id <> 5 / NOT IN (...)        -- 7. 否定条件通常无法走索引（视优化器而定）

另两类”假失效”：优化器判断全表扫描更快（回表数据量大、查询超过表的 20%~30%）；统计信息过期。排查手段永远是 EXPLAIN + 实测。

3. explain 关键列 ⭐

面试官想听：拿到 explain 输出能立刻判断这条 SQL 健不健康。

EXPLAIN SELECT * FROM big_user WHERE name = 'user_999999';

重点看 5 列：

4. 事务：ACID、隔离级别与 MVCC ⭐

面试官想听：ACID 各靠什么机制实现，MVCC 能讲出”版本链 + ReadView”两件套。

• 原子性：undo log 回滚未提交的修改。
• 持久性：redo log 保证提交后宕机不丢（WAL：先写日志再刷数据页）。
• 隔离性：MVCC + 锁。
• 一致性：前三者是手段，一致性是最终目的。

三类并发问题：脏读（读到别人未提交的数据）、不可重复读（同一次事务内两次读同一行值不同——针对 UPDATE）、幻读（两次范围查询行数不同——针对 INSERT/DELETE）。

四个隔离级别（MySQL InnoDB 默认 REPEATABLE READ）：

MVCC 原理：

隐藏列：每行有 trx_id(最近修改它的事务) 和 roll_pointer(指向 undo log 旧版本)
undo log 版本链：多次修改形成链表，新版本指向旧版本
ReadView：m_ids(生成时刻的活跃事务列表)、min_trx_id、max_trx_id、creator_trx_id
可见性判断：trx_id < min → 可见；trx_id >= max → 不可见；
           介于中间 → 在 m_ids 里不可见、不在则可见；不可见就顺 roll_pointer 找旧版本

RC 与 RR 的唯一实现差异：RC 每次快照读都生成新的 ReadView，RR 只在第一次快照读时生成并复用——这就是 RR 能”可重复读”的原因。

RR 下如何避免幻读：快照读（普通 SELECT）靠 MVCC 看到的是事务开始时的快照；当前读（SELECT ... FOR UPDATE / UPDATE / DELETE / INSERT）靠 Next-Key Lock = 记录锁 + 间隙锁锁住范围，阻止其他事务在范围内插入。两个会话混用快照读和当前读时仍可能出现”幻影行”，这是加分项。

5. 三大日志与两阶段提交 ⭐

面试官想听：redo/undo/binlog 分属什么层、解决什么问题，以及为什么提交要”两阶段”。

两阶段提交（2PC）：执行更新时 redo log 先写 prepare → 写 binlog → 提交时 redo log 置为 commit。为什么必须这样：redo 与 binlog 是两份独立日志，若不做两阶段，崩溃恢复时二者可能不一致，导致主从数据不一致（如 redo 写完 binlog 还没写，恢复后主库有这条数据、从库没有）。

崩溃恢复怎么判断该提交还是回滚（高频追问）：redo log 的 prepare 记录里带了 XID（事务 ID）。重启后扫描 redo log：

• 若该事务的 redo 已是 commit 状态 → 直接提交；
• 若处于 prepare 状态 → 拿 XID 去 binlog 里查：能找到（说明 binlog 已写成功）→ 提交；找不到 → 回滚。

判据的本质是「binlog 写成功才算事务真正提交成功」——因为主从复制依赖 binlog，只有保证「binlog 有则主库必提交、binlog 无则主库必回滚」，主从才可能一致。

组提交（group commit）：两阶段提交要求每个事务提交时刷盘，高频写入下每次一次 fsync 会成瓶颈。MySQL 把 commit 阶段拆成 flush → sync → commit 三段，每段维护一个队列、由队首事务当 leader 带队执行：多个事务的 binlog 合并成一次 fsync 落盘，等于把磁盘 IO 开销摊薄到整组。prepare 阶段不变。这也解释了为什么 sync_binlog=1 + innodb_flush_log_at_trx_commit=1（双 1 配置）在高并发下没有想象中慢——组提交把并发事务的刷盘合并了。

6. 锁：表锁、行锁、间隙锁 ⭐

面试官想听：知道”行锁锁的是索引项”，以及乐观锁悲观锁怎么选。

• 全局锁（FLUSH TABLES WITH READ LOCK，用于全库逻辑备份）、表级锁、元数据锁（MDL，防 DDL 与查询冲突）、意向锁（表级标记，快速判断表中是否有行锁）。
• 行锁三种：Record Lock 锁单条索引记录；Gap Lock 锁索引记录之间的间隙；Next-Key Lock = Record + Gap，RR 级别防止幻读的主力。
• 插入意向锁（Insert Intention Lock）：INSERT 之前设置的一种特殊的间隙锁，只表示”我打算在这个间隙里插一行，插入位置在这”。要点：① 它不是表锁，是行级/间隙级的意向标记；② 多个插入意向锁之间互不冲突（大家都可以”打算”往同一间隙插）；③ 但它与已有的、范围内其他事务持有的间隙锁冲突——若别人先用 SELECT ... FOR UPDATE 锁住了这个间隙，你的 INSERT 就会阻塞等它释放。这正是”并发插入被间隙锁阻塞”与”插入死锁”的常见来源：两个事务各持有某间隙的间隙锁、又都想插入对方锁住的间隙，就互相等待成环。RC 级别下间隙锁基本不生效，插入意向锁也就少见——不是 RC 才产生它，而是 RC 少用间隙锁、冲突面小。
• 关键推论：行锁加在索引上，WHERE 条件没走索引时会退化成扫描全表并把扫过的记录全锁住，等于锁全表。
• 另一个关键推论：非唯一索引等值查询也会锁相邻间隙。RR 下用非唯一索引等值查 WHERE a = 10 时，Next-Key Lock 锁的不只是 a=10 那条记录，而是 (5, 10] 与 (10, 15] 两个区间（左开右闭，含前一条记录到 10 的间隙、以及 10 到后一条记录的间隙）——因为不唯一索引里 a=10 可能有多条，必须锁住相邻间隙防止插入新的 a=10。所以并发插入 a=8（落在 (5,10] 内）会被阻塞；而普通 SELECT（快照读）走 MVCC 不加锁，不受影响。这是”明明只查了一行却把别人插不进来”的经典来源。等值查唯一索引时则退化为记录锁，不加间隙。
• 悲观锁：假定会冲突，先加锁再操作，如 SELECT ... FOR UPDATE，适合写多冲突激烈的场景。
• 乐观锁：不加锁，提交时用版本号校验，适合读多写少：

UPDATE stock SET num = num - 1, version = version + 1
WHERE id = 1 AND version = 5;   -- 影响行数为 0 说明被并发修改过，业务侧重试

7. 调优与架构演进 ⭐

面试官想听：一条完整的”发现慢 SQL → 分析 → 优化 → 验证”流程，以及大表治理的全景。

慢 SQL 定位流程：开慢查询日志捕获 → 按耗时/扫描行数排序 → EXPLAIN 分析 → 加索引或改写 SQL → 复测验证。

SET GLOBAL slow_query_log = ON;      -- 开启慢日志
SET GLOBAL long_query_time = 1;      -- 超过 1 秒记录
SHOW VARIABLES LIKE 'slow_query_log%';

SQL 优化清单：只查需要的列（拒绝 SELECT *，给覆盖索引机会）；为 WHERE/ORDER BY 列建合适的联合索引；深分页改写；JOIN 用小表驱动大表并保证关联字段有索引；UNION ALL 代替 UNION；大事务拆小事务，减少锁持有时间；批量插入合并语句。

深分页优化：

-- 慢：LIMIT 900000, 10 要扫描 90 万零 10 行再丢弃
SELECT * FROM big_user ORDER BY id LIMIT 900000, 10;
-- 快① 延迟关联：先用覆盖索引拿到 10 个 id，再回表
SELECT u.* FROM big_user u
JOIN (SELECT id FROM big_user ORDER BY id LIMIT 900000, 10) t ON u.id = t.id;
-- 快② 游标分页：记住上一页最后的 id，要求连续翻页
SELECT * FROM big_user WHERE id > 900000 ORDER BY id LIMIT 10;

主从复制：主库写 binlog → 从库 IO 线程拉取写入本地 relay log → 从库 SQL 线程重放。用途：读写分离（写主读从）、容灾备份。主从延迟的原因：从库单线程重放慢、大事务、机器差；对策：关键读强制走主库、并行复制、监控 Seconds_Behind_Master。

分库分表：垂直分库按业务拆（订单库、用户库），垂直分表把大字段拆扩展表；水平分表按行拆（按用户 id 取模、按时间分表）。分片键要选查询最高频的维度（通常用户 id）。拆分的代价：跨库 JOIN 变难、需要分布式事务、需要全局唯一 ID、扩容数据迁移复杂。原则：先垂直后水平，索引和缓存优化到底扛不住了再分。

动手实践

任务 1：造 100 万数据，对比有无索引（1 小时）

CREATE TABLE big_user (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20), age INT, city VARCHAR(20), created_at DATETIME
);

DELIMITER $$
CREATE PROCEDURE gen_data(IN n INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  START TRANSACTION;
  WHILE i < n DO
    INSERT INTO big_user (name, age, city, created_at)
    VALUES (CONCAT('user_', i), FLOOR(RAND()*50)+18,
            ELT(FLOOR(RAND()*4)+1, '北京','上海','深圳','杭州'), NOW());
    SET i = i + 1;
  END WHILE;
  COMMIT;
END $$
DELIMITER ;

CALL gen_data(1000000);

验收：无索引 SELECT * FROM big_user WHERE name='user_999999' 耗时可观测（百万级约几百 ms），建 idx_name 后降到几 ms；两次 explain 的 type 分别为 ALL 和 ref，能复述原因。

任务 2：explain 优化 3 条慢 SQL（2 小时）

验收：3 条优化前后各跑一次 explain，type 提升、rows 数量级下降，并各写一句优化说明。

任务 3：开启慢日志（30 分钟）

按第 7 节配置开启慢日志，故意跑几条全表扫描 SQL，再到日志文件里找到它们。 验收：日志中出现慢 SQL 记录，能指出 Query_time 和 Rows_examined 两个字段。

任务 4：两个会话演示隔离级别与锁（1 小时）

-- 会话 A（开两个终端各连一个客户端）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT * FROM big_user WHERE id = 1;         -- 第一次读
-- 会话 B：UPDATE big_user SET age=99 WHERE id=1; COMMIT;
SELECT * FROM big_user WHERE id = 1;         -- RC 下值变了（不可重复读）
-- 再把会话 A 改成 REPEATABLE READ 重复实验：值不变，体会 ReadView 复用

再在 RR 下：会话 A BEGIN; SELECT * FROM big_user WHERE id BETWEEN 100 AND 200 FOR UPDATE;，会话 B 尝试 INSERT id=150，应被阻塞（临键锁生效）。 验收：三个实验现象截图或记录，各配一句话解释。

高频面试题

Q：为什么 InnoDB 用 B+ 树而不是哈希、红黑树、B 树？

• 哈希不支持范围查询和排序，只适合等值。
• 红黑树是二叉的，千万数据树高 20+ 层，每层一次磁盘 IO，太高。
• B 树非叶子也存数据，单页容纳键少树更高，范围查询需中序遍历回溯。
• B+ 树非叶子只存键，3 层可容纳约 2000 万行；叶子双向链表，范围查询顺序扫描即可。

Q：什么是回表？怎么避免？

• 二级索引叶子只存主键值，查到主键后还要回聚簇索引取整行，这就是回表。
• 用覆盖索引避免：查询列全部在索引里，explain 出现 Using index。
• 手段：把高频查询列加入联合索引；至少 SELECT id 而不是 SELECT *。

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

• 联合索引 (a,b,c) 按 a 排序、a 相同再按 b、再按 c，等价于 a、(a,b)、(a,b,c) 三套索引能力。
• 查询必须从最左列开始连续命中；遇到范围查询，后面的列停止走索引。
• 设计上把等值条件列放前面、范围列放后面，并尽量让常用查询覆盖。

Q：索引什么时候会失效？

• 对索引列用函数或运算；隐式类型转换（varchar 列传数字）。
• LIKE 以 % 开头；联合索引不满足最左前缀。
• OR 连接了无索引列；NOT IN / != 通常无法走索引。
• 优化器认为回表成本高于全表扫描（查询比例过大）也会放弃索引——一切以 explain 实测为准。

Q：事务的隔离级别有哪些？MySQL 默认哪个，怎么实现的？

• 读未提交、读已提交（RC）、可重复读（RR，InnoDB 默认）、串行化。
• 分别解决脏读、不可重复读、幻读的递进问题。
• 实现靠 MVCC（快照读）+ 锁（当前读）：RC 每次读生成新 ReadView，RR 复用第一次的 ReadView。

Q：讲一下 MVCC 的原理。

• 三件套：隐藏列（trx_id、roll_pointer）+ undo log 版本链 + ReadView。
• 读时拿 ReadView 和版本链上各版本的 trx_id 比对可见性，不可见就沿链找旧版本。
• 实现了读写不阻塞；RC 与 RR 的差别只在生成 ReadView 的时机。

Q：redo log、undo log、binlog 的区别？为什么要两阶段提交？

• redo：InnoDB 物理日志、循环写，崩溃恢复保证持久性；undo：InnoDB 逻辑日志，回滚 + MVCC；binlog：Server 层逻辑日志、追加写，用于主从复制和恢复。
• 两阶段提交：redo prepare → 写 binlog → redo commit。
• 目的是保证两份日志一致，否则崩溃恢复后主从数据不一致。

Q：线上出现慢 SQL，怎么排查？

• 开慢查询日志定位具体 SQL，看执行频率与耗时分布。
• EXPLAIN 分析：type 是否 ALL、key 是否为 NULL、rows 是否巨大、Extra 有无 filesort/temporary。
• 优化手段：建/改联合索引、覆盖索引、改写 SQL（深分页、函数下界）、必要时上缓存或读写分离。
• 改完必须复测 explain 与真实耗时，形成闭环。

Q：一张千万级大表，你有哪些优化思路？

• 先做索引与 SQL 优化、覆盖索引、深分页改写，成本最低收益最大。
• 上缓存挡读流量；读写分离分担读压力。
• 冷热数据归档（历史表），减小活跃数据量。
• 仍不够再分库分表：垂直拆业务、水平按分片键拆行，同时接受跨库 JOIN、分布式事务、全局 ID 的复杂度。

推荐资料