数据库与缓存：慢查询、事务与一致性

读完这一节，你能做到

1. 用 B+ 树解释索引为什么快，以及最左前缀与覆盖索引。
2. 说清四种隔离级别与 MVCC 的关系。
3. 判断一条 SQL 为什么用不上索引。
4. 处理缓存穿透、击穿、雪崩与双写一致性。

一、索引：B+ 树与最左前缀

InnoDB 用 B+ 树做索引：非叶子节点只存键，叶子节点存数据并用链表相连。相比 B 树，B+ 树的高度更低、范围查询更快。

两种索引：

• 聚簇索引：主键索引，叶子节点存整行数据。
• 二级索引：叶子节点存主键值，查整行要"回表"。

最左前缀：联合索引 (a, b, c) 能命中 a、a,b、a,b,c，不能命中 b、c。范围查询会中断后续列的索引使用。

覆盖索引：查询的列都在索引里，不需要回表。explain 的 Extra 出现 Using index 就是覆盖索引。

二、索引失效的常见场景

场景	原因
对索引列做函数/运算	无法用索引有序性
隐式类型转换	字符串列传数字，等价于函数
前导模糊 %abc	无法定位前缀
or 连接非索引列	退化为全表
联合索引跳过最左列	不满足最左前缀
数据量小或区分度低	优化器判断全表更便宜

explain 重点看四列：type（访问方式）、key（实际用的索引）、rows（预估扫描行数）、Extra（是否回表/排序/临时表）。

三、事务与隔离级别

ACID：原子性、一致性、隔离性、持久性。隔离性靠锁与 MVCC 实现。

隔离级别	脏读	不可重复读	幻读
读未提交	会	会	会
读已提交	不会	会	会
可重复读	不会	不会	InnoDB 基本不会
串行化	不会	不会	不会

MySQL 默认是可重复读。InnoDB 用 MVCC 实现一致性读：每行有隐藏的事务 ID 与回滚指针，读的时候按 Read View 判断哪个版本可见。

幻读在可重复读下用间隙锁（Next-Key Lock）解决；但快照读与当前读的行为不同，面试时要区分。

四、锁与死锁

• 行锁：锁索引上的记录；没有索引会退化为表锁。
• 间隙锁：锁索引区间，防止插入。
• Next-Key：记录锁 + 间隙锁。
• 意向锁：表级，表示"即将在行上加锁"。

死锁的四个条件与 Java 一样：互斥、占有且等待、不可抢占、循环等待。排查用 show engine innodb status 看最近一次死锁。

避免死锁：按固定顺序加锁、缩短事务、减少范围锁、必要时降低隔离级别。

五、慢查询与深分页

慢查询排查顺序：

1. 开启慢查询日志，找到具体 SQL。
2. explain 看访问方式与扫描行数。
3. 检查索引是否缺失、是否失效。
4. 优化 SQL：避免 select *、减少回表、拆分大事务。

深分页 limit 100000, 20 会扫描 10 万行。两种优化：

• 游标分页：where id > lastId order by id limit 20。
• 延迟关联：先用覆盖索引查出主键，再回表。

六、缓存：穿透、击穿、雪崩

问题	现象	方案
穿透	查不存在的数据，打到 DB	空值缓存、布隆过滤器
击穿	热点 key 过期，大量请求打 DB	互斥锁、逻辑过期
雪崩	大量 key 同时过期	随机过期时间、多级缓存、限流

双写一致性：先更新数据库再删缓存（Cache Aside），并接受短暂不一致；要求强一致时用延迟双删、订阅 binlog 或分布式锁。

缓存淘汰：LRU（最近最少使用）、LFU（最少频率）、TTL。Redis 的 allkeys-lru 与 volatile-lru 要区分。

七、常见坑

1. 给字符串列传数字，隐式转换导致索引失效。
2. 联合索引顺序与查询条件不匹配。
3. 在事务里做远程调用，锁持有时间过长。
4. 缓存与数据库双写但不删缓存，读到旧值。
5. 用 select * 导致无法覆盖索引。

八、动手任务：自写 → AI 写 → 对比 → 取舍

四步不跳：① 自己写（限时，关掉补全）→ ② 让 AI 写同一题 → ③ 逐条比对 → ④ 说出保留谁的写法。 完整动作与记录模板见《AI 协作与代码评审》。

① 自己写

1. 建一张百万行表，对比有索引与无索引的查询计划。
2. 用 explain 分析一条深分页 SQL，改成游标分页。
3. 用 Redis 实现空值缓存与互斥锁，模拟穿透与击穿。

② 让 AI 写同一题

把第 2 题（缓存与一致性）交给 AI，补一句 写清失效顺序，以及并发写时的窗口期。

③ 对比要盯的点

• 它是“先删缓存再更新库”还是“先更新库再删缓存”？前者窗口期更大。
• 索引最左前缀它讲对了吗？函数、隐式转换会直接让索引失效。
• 它有没有考虑缓存击穿与雪崩？只写 get 与 put 说明没想过失效瞬间。

④ 一句话取舍

两个请求同时写同一条数据时，它那套顺序会留下什么脏数据？

九、自测

1. B+ 树相比 B 树的优势？
2. 最左前缀与覆盖索引分别是什么？
3. MVCC 如何实现可重复读？
4. 间隙锁解决什么问题？
5. 穿透、击穿、雪崩的区别与方案？

参考答案要点：更矮、范围快；联合索引顺序、不回表；Read View + 版本链；幻读；不存在/热点过期/同时过期。

附录：高频追问

问：为什么 InnoDB 用 B+ 树而不是哈希？ 哈希只支持等值查询，不支持范围与排序；B+ 树有序，适合磁盘预读。

问：聚簇索引与二级索引的区别？ 聚簇索引叶子存整行；二级索引叶子存主键，需要回表。

问：为什么建议用自增主键？ 顺序写入减少页分裂；随机主键会导致页分裂与碎片。

问：可重复读能完全避免幻读吗？ 快照读靠 MVCC，当前读靠间隙锁；特殊场景仍可能，需要具体分析。

问：什么是当前读与快照读？ 当前读读最新已提交版本并加锁（for update）；快照读读 Read View 对应的版本。

问：缓存为什么先删再更新不行？ 两个并发操作可能把旧值写回缓存；Cache Aside 推荐先更新库再删缓存。

问：布隆过滤器有误判吗？ 有，可能把不存在的判为存在；不会把存在的判为不存在，适合做前置过滤。

问：逻辑过期怎么实现？ 缓存不设 TTL，值里带过期时间；发现过期后异步重建，期间返回旧值。

问：大事务有什么问题？ 锁持有久、回滚慢、undo 日志大、主从延迟；要拆分。

问：分库分表后怎么查询？ 按分片键路由；非分片键查询用基因法、映射表或搜索引擎。

索引设计的三条经验

1. 区分度高的列放前面。
2. 联合索引覆盖高频查询，避免回表。
3. 控制索引数量，写多读少的表少建。

补遗：MySQL 的三个日志

redo log（InnoDB，物理日志，循环写，保证崩溃恢复）；undo log（逻辑日志，回滚与 MVCC）；binlog（Server 层，逻辑日志，主从复制与归档）。两阶段提交：redo prepare → binlog → redo commit，保证两者一致。

补遗：MVCC 细节

每行有 trx_id 与回滚指针；Read View 包含 m_ids、min_trx_id、max_trx_id、creator_trx_id。可见性判断：自己改的可见；小于 min 可见；大于等于 max 不可见；在 m_ids 中不可见。RC 每次快照读都新建 Read View，RR 只在第一次建。

补遗：分库分表与连接池

垂直拆分按业务，水平拆分按分片键；分片键要能覆盖高频查询，否则会全路由。跨库 JOIN、分页、事务都是代价。读写分离用主从复制，注意主从延迟导致的“写后读不一致”，可强制走主库。

连接池（HikariCP）看四个参数：最大连接数、最小空闲、连接超时、最大生命周期；连接数不是越大越好，超过数据库承载反而更慢。

补遗：Redis 的数据结构与持久化

五种基础结构：String（计数、缓存）、Hash（对象）、List（队列）、Set（去重）、ZSet（排行榜）。进阶：Bitmap（签到）、HyperLogLog（UV 估算）、GEO（附近）、Stream（消息）。

持久化：RDB 快照（快、可能丢数据）、AOF 日志（全、体积大）、混合持久化。淘汰策略：noeviction、allkeys-lru、volatile-lru 等。集群：主从 + 哨兵，或 Redis Cluster 分片；分布式锁的边界见下一节。

配套题目

★ 越多越该先背；点题目可看完整答案与深入版。

• ★★★★★ MySQL 索引在哪些情况下会失效？
• ★★★★★ 事务隔离级别有哪几种？MVCC 是怎么实现的？
• ★★★★☆ 一条慢 SQL 你会怎么排查和优化？
• ★★★★☆ Redis 有哪些数据结构？分别适合解决什么问题？
• ★★★★★ 缓存穿透、击穿、雪崩分别是什么？怎么解决？