18a-MySQL 专项精讲

📖 本册定位

这是「数据库」这一科的前册，覆盖 MySQL 的全部内容：D1–D18。

• 写给谁：目标岗位 Python 后端 + AI 应用开发。MySQL 在这条路线上是必问项，但只问核心那几条（索引 → 事务 → 锁 → 日志），不考源码。

• 默认前提：MySQL 8.0 + InnoDB。凡涉及 5.7 与 8.0 的差别，正文都会点出来。

• 本册要达成什么：合上文档，30 秒能口述一条——讲得顺、有机制、有取舍。附录 B 就是验收清单（20 条）。

🔗 与续册的衔接

18b-Redis与缓存一致性精讲（D19–D26）是本册的续册，两册编号连续：本册 D1–D18，续册 D19–D26。

什么时候去读续册：本册读完 + 附录 B（尤其 B3 事务与并发那 4 条）自测过关之后。因为续册的 D24「缓存与数据库的一致性」直接建立在 D10–D13 之上——MVCC 和事务没讲顺，那一节就只会背结论。

只读一册行不行：只面 Python 后端，MySQL 册是主力、Redis 册是配角；面 AI 应用，Redis 册反而更常被问（缓存、限流、去重）。两册都读，顺序别反。

一、怎么用这份文档（学习路线）

资料在哪

主用网址（点开就看，最新版）：

离线备份（不想联网时用，页码见附录 A）：../查阅资料/小林coding图解网站合集PDF/——里面有图解MySQL 333 页、图解Redis 320 页、150道MySQL+Redis面试题 162 页，以及图解网络 / 图解系统 / 分布式等。

正文与附录的分工：正文各节的 📖 补原理 给可点击链接（读到哪点到哪）；附录 A 只做「离线页码对照」（断网时按页码翻 PDF），两边不重复。

三份材料怎么配合

顺序不能反。先看原理篇容易"看懂了但说不出"——本册先给你"该答什么"的骨架，再去看图，才是往骨架里填肉。

四阶段路线

阶段 ①–④ 全部在 MySQL（本册）完成，合计约 13 天。 Redis（续册 18b，D19–D26）排在阶段 ④ 之后：它是配套内容，而且续册的 D24 依赖本册 D10–D13——先有事务和 MVCC，才谈得上「缓存与数据库的一致性」。时间够就读全册；时间紧就只读续册的 D23、D24 两节。

📝 配套练习：读完 D1 之后先别往下读——去 练习题库/数据库/01-执行流程与索引基础-题目与答案（含批注）.md 做那 10 道题（不要翻本册），做完自己对照答案与批注。 「读完了」不等于「会了」——这一步是整条路线里最容易被跳过、也最值钱的一步。

验收标准（达不到就不算学会）

随便挑一条，合上材料，30 秒讲出来：
  · 讲得顺、有机制、有取舍  → 过
  · 卡壳 / 只会背结论        → 回阶段 ②，读对应那篇

四条硬标准（每条都要能脱稿）：

1. 索引：为什么是 B+ 树、聚簇 vs 二级、回表、最左前缀、什么情况会失效

2. 事务：四档隔离级别各解决什么、MVCC 靠什么实现、RC 与 RR 差在哪

3. 锁：行锁/间隙锁/临键锁各锁什么范围、没走索引会怎样、死锁怎么排查

4. 日志：三个日志各干什么、两阶段提交为什么必要、崩溃后怎么判定

这四条全部是 MySQL 的，都在本册。续册（Redis）的验收标准见 18b 的附录 B。

阅读顺序图

D1  一条 SQL 的执行流程          ← 全局地图，必先读
        │
        ├─ 索引（最高频）      D2 为什么 B+ 树 → D3 聚簇/二级/回表
        │                      → D4 索引失效 → D5 explain → D6 深分页
        ├─ 存储引擎与内存      D7 InnoDB/MyISAM → D8 页与行格式 → D9 Buffer Pool
        ├─ 事务与并发          D10 隔离级别 → D11 MVCC → D12 锁 → D13 日志
        ├─ 设计与运维          D14 主从/分片 → D15 数据类型 → D16 建表规范
        │                      → D17 DDL/备份 → D18 SQL 场景题
        └─ 缓存与一致性        D19–D26（Redis 侧）→ **见续册 `18b`**

为什么是这个顺序：先看清全貌（D1）→ 啃最高频的索引（D2–D6）→ 补上「数据怎么存」的地基（D7–D9）→ 才有资格谈并发（D10–D13）→ 最后才是设计与缓存（D14–D26）。 每一部分开头都有一句「为什么放这里」，遇到往后才会讲的概念，前面会先给个通俗解释。

二、前置概念：读之前先混个脸熟

这一节只是「认脸」。下面每个词后面都会详细讲，这里只给你个大概，让你读 D1 的时候不被绊住。 用法：先扫一遍这张表，不用记；读到后面某节卡住时，再回来对一眼。

一张表认全 16 个词

💡 一句话把上面串起来： 数据按页存在磁盘 → Buffer Pool 把页缓存进内存 → 页里的行靠 B+ 树索引快速定位 → 索引分聚簇（存整行）和二级（存主键值，要回表）→ 并发读写靠 MVCC + 锁 → 数据安全靠 redo / undo / binlog 三个日志。

三、全局地图：一条 SQL 的执行流程

D1　一条 SQL 的执行流程

一句话结论：一条 select 走五站——连接器 →（查询缓存已移除）→ 解析器 → 预处理器 → 优化器 → 执行器。语法错在解析器报，表不存在在预处理器报。 面试怎么用：五站按顺序说一遍，并主动补一句「MySQL 8.0 已经把 Server 层的查询缓存整个删掉了」——这句话本身就是考点。被追问「表不存在在哪报错」时，答「预处理器，不是解析器」。

先记住一条主线：MySQL 分成 Server 层（管连接、解析、优化、执行）和 存储引擎层（管数据怎么存怎么取）。所有引擎共用同一个 Server 层，而索引是引擎层实现的——这解释了为什么换引擎索引行为会变。

一条 select * from product where id = 1 走下来是六站：

1.1 六站主线：连接器 → 解析器 → 优化器 → 执行器

① 连接器——先和客户端做 TCP 三次握手（MySQL 基于 TCP），然后校验账号密码。密码对了就把这个用户的权限读出来缓存到连接里，后续这条连接上的所有操作都基于这份快照判断。

这里有个反直觉的点：管理员中途改了权限，已经建立的连接不受影响，只有新连接才用新权限。

连接管理有三个参数要认识：wait_timeout（空闲连接多久被断，默认 8 小时）、max_connections（最大连接数，默认 151，超了报 Too many connections）、以及长连接的内存问题——长连接累积太多会吃内存，解法是定期断开或调用 mysql_reset_connection() 重置（不用重连、不用重新验权限）。

② 查询缓存（MySQL 8.0 已删除，只作历史了解）——key 是 SQL 原文，value 是结果。它在 MySQL 8.0 被整个删掉了，原因有两个：① 只要表有更新，这张表的所有缓存全清空，命中率极低；② 它用全局一把锁，高并发下读写互相抢（见 1.2）。

⚠️ 以 8.0 为准：面试按五站答，不要把「查询缓存」说成现役组件；提到它只提「8.0 已移除、为什么移除」。

⚠️ 这里最容易搞混：删的是 Server 层的查询缓存，不是 InnoDB 的 Buffer Pool——后者还在，而且是 InnoDB 的心脏。

一句话区分：Query Cache 缓存「SQL 原文 + 结果」，写一次整表失效；Buffer Pool 缓存「数据页（16KB）」，页级失效、页级受益。

展开见 D9（Buffer Pool 的改良 LRU、脏页刷盘时机在本册附录 C）。

1.2 补充：Query Cache 的「全局锁」是什么锁？

删掉 Query Cache 的第二个原因是锁竞争——它用的是「全局一把锁」。这把锁既不是表级锁，也不是行级锁，三个层面别混：

关键点：Query Cache 内部是一张哈希表，多线程同时读写会把它搞坏，所以需要一把锁保护这个结构。 它锁的是「缓存表」，和"你查哪张表、哪一行"完全无关。

用图书馆打比方：

⚠️ 注意区分「全局」和「表级」——它们说的不是同一件事： · 「全局」描述的是锁的范围有多大（一把管所有） · 「表级 / 行级」描述的是锁的对象是什么（锁表还是锁行）

为什么它是瓶颈：整个 Query Cache 只有一把锁（不像 Buffer Pool 会拆成多个实例、每个实例一把锁），所以高并发下所有查询都在抢它，而且读也要抢。

📌 两个缓存对照：都是缓存，但 Query Cache 是「全局一把锁 + 整表失效」，Buffer Pool 是「多实例锁 + 页级失效」——所以一个被删，一个是 InnoDB 的心脏。

③ 解析器——做两件事：词法分析（把 SQL 切成 token，识别出 select、from 这些关键字）和语法分析（按语法规则构建语法树）。语法错在这一步报错。

一个常被讲错的细节：表不存在、字段不存在不是在解析器里查的。小林专门看了 5.7 和 8.0 源码确认——解析器只负责语法和语法树。

④ 预处理器——真正开始检查表和字段是否存在，并把 select * 展开成所有列。表不存在报的 Table doesn't exist 是在这一站。

⑤ 优化器——决定用哪个索引、按什么顺序连接。它基于成本估算做选择，不保证选最优，只保证选它算出来最省的。想知道它选了什么，就用 explain 看 key。

举个经典例子：select id from product where id > 1 and name like 'i%' 既能走主键索引也能走 name 索引。 要用的列（select 的 id、where 的 name）全都已经在 idx_name 里，所以这是覆盖索引——走二级索引不用回表，比走聚簇索引树更省，优化器因此选 name 索引，Extra 显示 Using index。 为什么"索引里会有 id"？ 因为二级索引的叶子节点会自动附上主键值——展开见 D3（聚簇索引与二级索引）。

⑥ 执行器——拿着执行计划，以记录为单位和存储引擎交互。三种典型路径：

• 主键等值查询：调 read_first_record 定位一条，再调一次就直接退出（const 类型）。

• 全表扫描：循环调 read_record 一行行读，Server 层逐行判断条件，符合就发给客户端。

• 索引下推：这个最关键——age > 20 and reward = 100000 建了 (age, reward) 联合索引，范围条件会让 reward 用不上索引定位。没有 ICP 时每条二级索引记录都要回表、再回 Server 层判断 reward；有 ICP 后存储引擎先在索引里判断 reward（因为 reward 就在联合索引里），不满足直接跳过、不回表。Extra 显示 Using index condition。

📖 补原理：执行一条 SQL 查询语句，期间发生了什么？ —— 这是整本书的地图，建议第一个读。读完你会知道后面所有"零件"（索引、MVCC、锁、日志）分别装在这条流水线的哪一环。

一句话记住：连接器管身份、解析器管语法、预处理器管存在性、优化器管选路、执行器管取数——索引决定执行器怎么取，这是性能的根源。

四、索引（面试最高频，先啃这块）

为什么把索引放最前面：它是 MySQL 面试出现频率最高的一块，也是后面 explain、慢查询、锁、分片全都绕不开的基础。

🎯 本部分的最终交付物｜「讲一下索引」口述稿（把 D2–D6 学完后定型）

① 完整版（约 90 秒）

索引通俗可以认为是书的目录。

【底层结构】 MySQL 用的是 B+ 树——非叶子节点只放"路标"（键值 + 指针），真实数据全在叶子节点。这样一页能放下更多路标、扇出极大，千万级数据层数也只有 3 层，大幅减少磁盘 IO。而且 InnoDB 的叶子节点之间是双向链表，范围查询顺着扫就行，不用回到上层。

【两种索引】 索引分两种：聚簇索引（就是主键索引）的叶子存完整的一行数据；二级索引的叶子存索引列 + 主键值。所以——要查的列在索引里 → 覆盖索引，不用回表；不在 → 拿主键值回聚簇索引取整行，这就是回表。

【索引下推】 另外还有索引下推：把能在索引里判断的条件从 Server 层下推到存储引擎层，引擎扫索引时顺手筛掉不满足的，减少回表次数。

【使用与排查】 使用上两个要点： · 联合索引遵循最左前缀——能用多少用多少，直到断掉为止，而且范围条件会截断它后面的列； · 这几类会让索引失效：列上套函数/运算、隐式类型转换、like '%x' 这种百分号开头的模糊匹配。另外单独说一条：or 连接的条件里只要有一个列没索引，整句就会退化成全表扫描，要改成 union all 拆开、或者给那列补索引。 排查用 explain，看优化器最终选了哪个索引（以及 Extra 里有没有 Using index）。

【代价】 最后，索引是有代价的：占磁盘空间 + 拖慢写入（每次增删改都要维护所有索引树），所以不是越多越好。

② 极简版（约 40 秒）

索引就是书的目录。MySQL 用 B+ 树——非叶子只放路标、数据全在叶子，扇出大、千万数据只要 3 层；叶子还是双向链表，范围查询顺着扫。 索引分聚簇索引（叶子存整行）和二级索引（叶子存索引列 + 主键值）——要查的列索引里都有就是覆盖索引免回表，没有就靠主键值回表。还有索引下推，把能在索引里判断的条件推到引擎层，减少回表。 使用上，联合索引遵循最左前缀；函数、隐式转换、like '%x'、or 混无索引列都会让索引失效，排查用 explain。 索引占磁盘、拖慢写入，所以不是越多越好。」

怎么用：先背极简版；被追问再展开完整版。完整版的每一段，正好对应 D2~D6 各节的「一句话结论」。

D2　为什么 MySQL 用 B+ 树做索引

一句话结论：B+ 树矮（多叉 + 非叶子不存数据）、范围快（叶子双向链表）、改动小（非叶子冗余）——所以 MySQL 选它。 面试怎么用：30 秒说 2.9 的总结；被追问再展开 2.4~2.6。 本节导航：2.1 主线 → 2.2 演进链 → 2.3 逐棵演进 → 2.4 层数 → 2.5 B vs B+ → 2.6 增删 → 2.7 不用哈希/跳表 → 2.8 其他库 → 2.9 总结

这是整个索引的地基。面试官问「讲一下索引」，B+ 树只需要 30 秒（结论在 2.9）；但要是他没听懂、或者你被追问，下面的推导就是弹药。

📖 配套链接：为什么 MySQL 采用 B+ 树作为索引？（离线 PDF 第 66 页）

2.1 先建立主线：一切为了减少磁盘 IO

内存和磁盘的差别，是所有设计的起点：

为什么数据必须放磁盘？ ① 放不下——业务数据可能几百 GB 到 TB 级，内存塞不下；② 会丢——内存断电就没了。

所以数据库的模式是：磁盘存全量 + 内存做缓存（Buffer Pool）。

那"读磁盘"是怎么发生的？

查 id = 100
    ↓
先看 Buffer Pool（内存）里有没有"包含这一行的那一页"
    ├─ 有   → 直接读内存 ✅ 快（命中）
    └─ 没有 → ❌ 从磁盘把整页读进来（未命中）

磁盘的读取单位是「分三层」的（这点容易被追问）：

⚠️ 注意「访问一个节点 = 一次磁盘 IO」这句话有个前提：节点的体积要小于操作系统的最小读写单位（块）。 B+ 树一个节点正好是一页（16KB），OS 一次能读 4KB 的整数倍，所以"读一个节点 ≈ 一次 IO"成立。讲这句话时把前提带上，会显得你读得细。

关键：不是每次都读磁盘。"索引要矮"的目的是：减少未命中时的读盘次数。

而读盘次数 ≈ 树的层数——所以最后一个问题变成：怎么让树尽量矮？

2.2 树的演进链（一眼看懂为什么有这么多树）

二叉搜索树 BST      快，但有序插入会【退化成链表】           ❌
      ↓ 解决退化
平衡二叉树 AVL      严格平衡（高度差≤1），但旋转太频繁         ❌
      ↓ 放松平衡条件                         而且【还是二叉的】
红黑树              旋转少了，工程上好用                      ❌
      ↓ 🔑 关键转折：从「二叉」变成「多叉」        【还是太高】
B 树                多叉！树一下子矮了 ✅
      ↓                但每个节点都塞了数据 → 扇出小、范围查询要回溯
B+ 树               数据只放叶子 + 叶子连成链表 ✅✅ ← MySQL 选它

🔑 记住那个转折点：AVL 和红黑树再怎么优化，本质还是二叉树——一个节点最多分 2 个岔，树高必然是 log₂(N)，降不下来。 要矮，就必须多叉。

2.3 树的演进：每棵都在解决上一棵的遗留问题

扫一遍小标题就能看懂整条链：每棵树都在补上一棵的短板，直到 B+ 树。

① BST：能二分，但会退化成链表

        8
      /   \
     3     10
    / \      \
   1   6      14

查找：比 8 小往左、大往右，每层排除一半 → log₂(N)。 遗留问题：按顺序插入（1,2,3,4…）会退化成一条链表，查找变成 O(N)。

→ 于是有了 AVL

② AVL：平衡了，但旋转太频繁，而且还是二叉

规则：任意节点左右子树高度差不超过 1，不平衡就靠旋转掰回来。 遗留问题：① 旋转太频繁（插入删除后要一路往上检查、反复旋转）；② 🔴 还是二叉的，还是太高。

→ 于是有了红黑树（把「严格平衡」放松成「近似平衡」）

③ 红黑树：旋转少了，但树还是太高

思路：既然 AVL 管得太严，那就别追求严格平衡——用颜色（红/黑）约束，保证「最长路径不超过最短路径的 2 倍」。 好处：旋转少（插入最多 2 次、删除最多 3 次）。 用在哪：Java 的 TreeMap、HashMap 链表转红黑树、Linux 进程调度（CFS）、C++ 的 std::map。 但对 MySQL 来说仍然不行：🔴 它还是二叉树。1000 万数据 ≈ 24 层 → 磁盘上就是 24 次 IO。

→ 所以必须从「二叉」变「多叉」

④ B 树：多叉了，但节点塞数据、范围要回溯

思路：既然「层数 = IO 次数」，那就让一个节点存很多 key、分很多岔：

一个 m 阶 B 树的节点：
┌──────────────────────────────────────────────┐
│  p0 │ key1 │ data1 │ p1 │ key2 │ data2 │ p2   │
└───┬──┴──────┴───────┴──┬─┴──────┴───────┴──┬───┘
   子指针   键1的数据     子指针   键2的数据   子指针

于是 1000 个数据一层就分完（二叉树要 10 层）。 遗留问题：① 节点里塞了数据 → 一个 16KB 页能放的 key 变少 → 扇出受限；② 范围查询要回溯（见 2.5）。

→ 于是有了 B+ 树（再改两刀）

⑤ B+ 树：非叶子只放路标 + 叶子连成链表 → MySQL 选它

                  [ 100 | 200 ]                ← 非叶子：只有路标，没有数据
                 /      |              [10|20|30]  [110|150]  [210|250]       ← 叶子：数据全在这
             ↕           ↕           ↕
        ←──────→   ←──────→   ←──────→        ← 叶子之间【双向链表】

2.4 层数到底怎么算出来的

二叉树为什么是 24 层？h 层的二叉树最多装 2^h − 1 个节点。要装 1000 万：

2^23 = 8,388,608    ← 838 万，不够
2^24 = 16,777,216   ← 1677 万，够了 ✅

→ 24 层。

B+ 树为什么是 3 层？（"3 层"= 根 + 一层路标 + 叶子）

关键是扇出（一个节点能分多少岔）：

非叶子节点一个条目：主键 bigint(8B) + 页号(6B) ≈ 14 字节
16KB ÷ 14 ≈ 1170 个           → 扇出 ≈ 1170

叶子节点：假设一行 1KB → 16KB ÷ 1KB = 16 行/页

⚠️ 这是理想估算：按「指针 6 字节 + 主键 8 字节 + 页开销」、叶子存满来推的。实际受页头页尾开销、行宽（大字段 / 长 VARCHAR）、主键类型影响，真机上差很多——只作经验参考，别当硬指标背。 | 4 层 | 根 + 2 层路标 + 叶子 | 1170³ × 16 ≈ 256 亿 |

对比一下（每加一层：B+ 树 ×1170，二叉树 ×2）：

同样是 2000 万数据：B+ 树 3 层、红黑树 25 层 → 查一次 = 3 次读盘 vs 25 次读盘（约 30ms vs 250ms）。 这就是 B+ 树赢的全部理由。

2.5 B 树 vs B+ 树：为什么"非叶子不放数据"这么关键

先看扇出的差距（假设一行数据 1KB）：

扇出 16 vs 1170——差 73 倍。 所以 B 树装 2000 万要 log₁₆(2000万) ≈ 6 层，B+ 树只要 3 层。

再看范围查询为什么要"回到上面"——查 20 到 80：

B 树（数据散在各层）：
              [ 50 ]                         ① 拿到 50
            /        \
     [20,30]          [70,80]                 ② 左子树拿 20、30
                                              ③ 要找 70、80 → ❓ 人在左子树
                                                必须【回到根】再往右走  ← 🔴 回溯

B+ 树（数据全在叶子 + 链表）：
          [ 50 ]                             ← 只放路标
        /        \
  [20,30]  ←→  [70,80]                        ← 叶子有链表
                                              找到 20 → 30 → 顺着【→】到 70、80
                                              一路往右，从不回头 ✅

类比：B 树像书散放在各楼层，你找完 3 楼要去 5 楼得先回大厅看指示牌；B+ 树像所有书都排在一楼一条长架上，往前走几步就到。

还有一处更本质的浪费：B 树会「白读」

查一条数据要经过 3 个节点——这 3 个节点里都塞着"别人的记录数据"，全部被读进内存，但你只需要它们的索引部分来判断路径，那些记录数据纯属白读：

2.6 B+ 树的第三个优势：插入删除效率高（靠「冗余节点」）

这是最容易被忽略、也最能加分的一条。 前面两条优势（矮、范围快）人人都知道，这一条只有真读懂了结构的人答得出来。

先搞清「冗余」到底指什么

B 树：每个 key 只有一份

        ┌─────────┐
        │   50    │        ← 50 在这里，就只有这一份
        └────┬────┘
        ┌────┴────┐
   ┌────┴───┐ ┌───┴────┐
   │  20 30 │ │  70 80 │    ← 20、30、70、80 也各只有一份
   └────────┘ └────────┘

B+ 树：非叶子的 key 是「复制品」

        ┌─────────┐
        │   50    │        ← 50 在这里
        └────┬────┘
        ┌────┴────┐
   ┌────┴─────┐ ┌─┴───────┐
   │ 20  30 50│ │ 70  80  │  ← 50 在这里【又出现一次】！
   └──────────┘ └─────────┘
              ↑ 数据（记录）在这里

🔑 关键认知：非叶子的 key 不是"数据"，它只是"路标"。 它唯一的职责是回答「你要找的值该往左还是往右」——它不需要和叶子里的数据保持一一对应。这就是"冗余"的含义。

B 树删根节点：为什么是「复杂变形」

假设要删掉 50，而 50 恰好在根节点上：

        ┌─────────┐
        │   50    │        ← 要删的就是它，而它在根上
        └────┬────┘
        ┌────┴────┐
   ┌────┴───┐ ┌───┴────┐
   │  20 30 │ │  70 80 │
   └────────┘ └────────┘

问题：B 树的规则要求非根节点 key 不能太少（M=3 时至少 1 个，根节点也至少 1 个）。直接拿掉 50，根节点就变成 0 个 key → 违法。

所以必须做一串操作：

① 去子树里找"替补" —— 左子树最大值 30，或右子树最小值 70
② 把替补【移】到根节点：
        ┌─────────┐
        │   30    │        ← 30 从下面"搬"上来了
        └────┬────┘
        ┌────┴────┐
   ┌────┴───┐ ┌───┴────┐
   │   20   │ │  70 80 │     ← 左子树只剩 20
   └────────┘ └────────┘
③ 如果子树【变空】或 key 不够 → 还要和兄弟节点合并
   合并 → 根节点少一个孩子 → 整棵树可能【降低高度】

🔴 这就是"复杂的树变形"：删一个 key 可能层层联动——找替补 → 上移 → 合并 → 整棵树变矮。 根本原因：B 树的 key 只有一份，删掉就是真没了，必须找东西补位。

B+ 树删同一个 key：为什么几乎不用动

              ┌─────────┐
              │   50    │         ← 根节点（只是路标）
              └────┬────┘
              ┌────┴────┐
        ┌─────┴─────┐ ┌─┴───────┐
        │ 20  30  50│ │ 70  80  │  ← 叶子（真数据）
        └───────────┘ └─────────┘

删掉 50 → 直接从叶子里拿掉，根节点原地不动：

              ┌─────────┐
              │   50    │         ← 【完全不用动】
              └────┬────┘
              ┌────┴────┐
        ┌─────┴─────┐ ┌─┴───────┐
        │  20   30  │ │ 70  80  │  ← 只在叶子里删掉 50
        └───────────┘ └─────────┘

根节点还写着 50，但 50 已经不存在了——为什么没问题？ 验证一下路由：

✅ 路由依然完全正确！ 因为 50 的职责只是「小于 50 走左，大于等于 50 走右」——左边的数都 < 50、右边的数都 ≥ 50，它的判断一点没出错。 所以非叶子节点根本不需要改。

💡 一句话理解：路标不需要和地图上的建筑同步——楼拆了，路口那块写着"前方 500 米"的牌子，照样能给你指对方向。

在 InnoDB 里的现实：为什么"不用动"是常态

那如果叶子被删空了呢？确实要动父节点。但是：

🔑 InnoDB 一页 16KB，能放很多行（一行 1KB 能放 16 行；一行 200B 能放 80 行）。 删掉一行，几乎不可能让整个叶子页变空。

这就是"删除效率高"的实际来源——绝大多数删除根本碰不到非叶子节点。

插入也一样：B 树「移动」，B+ 树「复制」

同一个原理的另一面。假设插入导致节点分裂：

B 树分裂：中间的 key 被【移走】
   原节点 [10|20|30] 满了
              [ 20 ]          ← 20 被"搬"到父节点
              /    \
          [10]    [30]        ← 20 从子节点【消失了】

B+ 树分裂：中间的 key 被【复制】一份上去
   原节点 [10|20|30] 满了
              [ 20 ]          ← 20 被"复制"到父节点
              /    \
        [10,20]    [30]       ← 20 在叶子里【还在】！

"移走"和"复制"的区别，就是"有没有冗余"的区别。 好处有两点： ① 叶子里数据不丢——分裂不会让记录凭空消失； ② 父节点索引可以随便保留——不用精确维护。

另外：B+ 树的插入最多只涉及"树的一条路径"——从根走到叶子，沿途该分裂就分裂，只影响这一条路径上的节点，不会像红黑树那样为了平衡到处旋转。

代价与取舍

注意那个"一点点"：

B+ 树冗余的只是 key（索引键），不含数据。主键 bigint 才 8 字节，冗余一份也就多 8 字节——相对整行数据（几百字节到几 KB），代价可以忽略。 用极小的空间代价，换大幅降低的维护成本——这是非常划算的工程取舍。

面试 30 秒版

「B+ 树的第三个优势是插入删除效率高，靠的是非叶子节点的索引是"冗余"的——同一个 key 在非叶子和叶子里各出现一次，非叶子那份只是"路标"。

为什么这让删除变快：B 树里 key 只有一份，如果你删的 key 恰好在内部节点上，就必须找替补、上移、可能合并、甚至整棵树降低高度。而 B+ 树数据全在叶子，删除直接从叶子删就行——非叶子那个路标即使已经不对应真实数据，路由判断依然正确，可以原样留着不动。加上 InnoDB 一页能放很多行，删一行几乎不可能让叶子页变空，所以实际删除基本碰不到非叶子。

插入同理：B 树分裂时中间的 key 是移走，B+ 树是复制一份上去，所以叶子里数据不丢。

代价是非叶子多存一份 key，但不含数据，空间代价极小。」

2.7 那为什么不用哈希 / 跳表

补一个 InnoDB 的彩蛋：自适应哈希索引（AHI）——InnoDB 会对高频访问的索引页自动建哈希来加速等值查询，但只支持点查，不支持范围。所以它只是辅助，主力还是 B+ 树。

跳表顺便说一下（因为它常被拿来对比）：跳表就是给有序链表加多层稀疏索引——从最上层开始"能跳就跳、跳不动就下一层"，像地铁的慢车/快车/特快。它靠抛硬币随机决定节点升不升层（期望上每层减半），所以不用旋转，实现比红黑树简单得多。Redis 选它，是因为内存里没有"读页"成本，而且 ZSet 大量是 ZRANGE 这类范围查询——跳表底层就是有序链表，顺着扫就行。

哈希索引的"适用场景"补全（上面只说了它为什么被淘汰，面试还可能问"那它用在哪"）： ① 真正唯一的优点是「等值查询 O(1) + 回表次数少」——因为哈希值相等 ⟺ 原值相等，一次定位就是唯一候选；它的结构里存的是「哈希值 + 行指针」，没有"顺着链表找下一条"这回事。 ② 在 MySQL 里你基本没有机会手动建它——InnoDB 不支持用户建哈希索引，只有自适应哈希索引（AHI，见上）自动加持；手动用哈希索引的是 Memory 引擎（默认索引类型）。 ③ 所以真实业务几乎不用：业务查询绝大多数带范围、排序或分组，全是哈希的死穴。 ④ 要"等值快 + 范围也要"的折中：B+ 树索引 + 冗余一个哈希列——存一列 crc32(url) 之类的哈希值并建索引，等值查走哈希列、范围查走原列（长字段存哈希值也见 15.6）。

2.8 其他数据库用什么索引（面试可能追问）

⚠️ 先纠正一个常见误解：不是"唯独 MySQL 用 B+ 树"。

为什么关系型数据库几乎都用 B+ 树？ 因为它们的核心操作是范围 + 排序 + 连接：

where id between 100 and 2000     -- 范围
order by create_time              -- 排序
join ... on a.id = b.a_id         -- 连接也要有序

这些全需要"从某点开始往后顺扫"——而 B+ 树的叶子链表天生就是干这个的。

MongoDB 为什么能用 B 树？ 因为它定位不同：文档数据库，没有 JOIN、不强调跨文档范围关联，更偏单文档点查。而 B 树在点查上有个优势——可能在半路就命中（数据在内部节点的话 1 次 IO 拿到），不像 B+ 树每次都必须走到叶子。

一句话：B+ 树为「范围扫描」优化，B 树为「点查」优化，选谁取决于你的数据库最常干什么。

2.9 三句话总结（面试直接说这段）

「MySQL 索引用 B+ 树，选它主要三个原因： ① 它矮——B+ 树是多叉的，而且非叶子节点只存路标不存数据，所以一层能放下上千个，千万级数据只要 3 层（红黑树要 25 层），读盘次数少且稳定。 ② 范围查询快——它的数据全在最底层，而且那一层排好序、用双向链表连起来，查 id between 100 and 200 顺着扫就行，不用回溯（B 树做不到）。 ③ 改动代价小——它的非叶子索引是冗余的（同一索引在非叶子和叶子都有），所以插入删除效率高：删除可以直接从叶子删、甚至不动非叶子，删根节点树形也几乎不变；插入最多只涉及一条路径的分裂，自动平衡、不需要旋转。 另外，因为每次查询都要走到最底层，IO 次数固定，性能也稳定，不会忽快忽慢。」

如果面试官继续追问：

• 「为什么不用红黑树？」→ 二叉树只能分 2 个岔，2000 万要 25 层，磁盘上就是 25 次 IO

• 「B 树和 B+ 树区别？」→ B 树每个节点都存数据（扇出小、范围要回溯）；B+ 树只有叶子存数据、上面只放路标

• 「为什么不用哈希？」→ 等值 O(1) 更快，但不支持范围/排序/最左前缀

• 「跳表为什么不行？」→ 内存结构，磁盘上没法按"页"读

📖 补原理：

• 从数据页的角度看 B+ 树（PDF 第 58 页）—— 想搞懂"页"到底是什么，看这篇

• MySQL 单表不要超过 2000W 行，靠谱吗？（PDF 第 78 页）—— "3 层 2190 万"这个数字的完整推演

D3　聚簇索引与二级索引：回表与覆盖索引

一句话结论：聚簇索引（主键索引）的叶子存整行；二级索引的叶子存索引列 + 主键值。要查的列索引里都有 = 覆盖索引免回表；没有就拿主键值回表。 面试怎么用：这两句先背死；被追问再讲「为什么聚簇索引只能有一个」「和索引下推的区别」。 本节导航：3.1 术语对齐 → 3.2 具体表看清 → 3.3 为什么附主键 → 3.4 覆盖索引 → 3.5 总结图 → 3.6 与 ICP 的区别 → 3.7 插入数据时索引的变化 → 3.8 索引的分类

这是 InnoDB 索引最核心的机制——面试里的"回表""覆盖索引"全是从这一句长出来的。

3.1 先统一术语（面试官会混着用，先对齐）

一句话定性：主键索引的叶子存「整行」；二级索引（辅助索引）的叶子存「索引列 + 主键值」。

⚠️ 一个常见误读：二级索引的叶子是「索引列的值 + 主键值」，不能理解为"只保存主键值"。 例：主键 id + 联合索引 idx_name_age(name, age) → 它的叶子记录里是 name、age、id 三个值（索引列全都在）。

回表：用二级索引查到主键值后，再回主键索引取完整的那一行——所以叫"回"表。

那为什么不干脆让二级索引直接存整行？（这是面试很自然的追问） ① 省空间——一张表有 N 个二级索引，就要存 N 份整行副本，太浪费； ② 保唯一——数据只保留一份权威副本（聚簇索引），修改时不用同步多份，避免不一致； ③ 代价就是多一次回表查询——而覆盖索引的意义正是"能省掉这次回表就省掉"。

3.2 用一张具体的表看清楚

CREATE TABLE product (
  id    INT PRIMARY KEY,
  name  VARCHAR(50),
  price INT,
  INDEX idx_name (name)      -- 这就是二级索引
);

主键索引（聚簇索引）——按 id 排序，叶子就是完整一行：

[ id=1 | name='apple'  | price=10  ]
[ id=2 | name='banana' | price=20  ]
[ id=3 | name='iphone' | price=800 ]
        ↑ 一次就能拿到整行，不用再查别的

二级索引 idx_name——按 name 排序，叶子只有 name + id：

[ name='apple'  | id=1 ]
[ name='banana' | id=2 ]
[ name='iphone' | id=3 ]
         ↑ 注意：没有 price

关键点：你建 idx_name 时只写了 name 一列，但 InnoDB 会自动在叶子节点后面附上主键 id。 这就是"二级索引的叶子节点本来就存着主键值"的意思——你没写，它自己加的。

3.3 为什么要附上主键值？

因为二级索引得能找回完整的那一行：

SELECT * FROM product WHERE name = 'iphone';

① 在 idx_name 里定位到 name='iphone'  →  拿到 id=3
② 但 idx_name 里没有 price
③ 拿 id=3 去【主键索引】查  →  拿到整行 (3, 'iphone', 800)
                                  ↑ 第 ③ 步就叫【回表】

主键值就是二级索引指向完整行的"指针"。 没有它，二级索引就找不回完整数据。

3.4 覆盖索引：什么时候不用回表

回到 D1 那个例子：

SELECT id FROM product WHERE id > 1 AND name LIKE 'i%';

要用的列（select 的 id、where 的 name）——全都已经在 idx_name 的叶子里了：

→ 不用回表，直接从索引把 id 读出来就完事
→ 这就是【覆盖索引】，explain 的 Extra 显示 Using index

对比一下，假如 SQL 改成：

SELECT id, price FROM product WHERE name LIKE 'i%';

要 price，但 idx_name 里没有 price：

① 从 idx_name 拿到 id
② 回表去主键索引取 price        ← 这就不是覆盖索引了

判断口诀：要用的列，在索引里能不能全找到？

• 能 → 覆盖索引，不用回表 ✅

• 不能 → 必须回表 ❌

💡 "索引里有的列" = 你建索引时写的列 + 主键。 所以 SELECT id, name 走 idx_name 也是覆盖索引（id 和 name 都在里面）。

3.5 一张图总结

        主键索引（聚簇索引）                二级索引 idx_name
     ┌──────────────────────┐          ┌──────────────────────┐
     │ 叶子：整行数据         │          │ 叶子：name + id       │
     │ id=1 name='apple'    │          │ name='apple'  id=1   │
     │      price=10        │          │ name='banana' id=2   │
     │ id=2 name='banana'   │          │ name='iphone' id=3   │
     │      price=20        │          └──────────────────────┘
     │ id=3 name='iphone'   │                     │
     │      price=800       │                     │ 想要 price，
     └──────────────────────┘ ◀───────────────────┘ 就得拿 id 回来
                ▲
                │ 回表：用 id 来这里取完整行

3.6 和索引下推的区别（两者最容易混）

📖 补原理｜索引常见面试题 —— 含「为什么二级索引不直接存整行」的完整推导。本册只留面试结论，想看推导点这里。

3.7 插入一条数据，索引会有哪些变化

一句话：插一行，这张表上的「聚簇索引 + 每一个二级索引」都要各插一次。

INSERT 一行  →  ① 聚簇索引：插到按【主键】排好的位置
             →  ② 每个二级索引：各插到按【自己的索引列】排好的位置

所以表上 5 个索引，插一行就要改 5 棵 B+ 树——这就是「索引拿写性能换读性能」的具体机制。

页分裂是怎么发生的（设一页放 4 行，主键现在是 1, 3, 5, 7）：

插入 4：
  ① 定位：位置在 3 和 5 之间
  ② 这一页已经满了
  ③ 申请新页，数据分一半过去 → 原页 1,3 ｜ 新页 4,5,7
  ④ 上层父节点要加一个路标指向新页

后果：页利用率从 100% 掉到约 50%（空间浪费 + 碎片）。 对比自增主键：1,2,3,4,5... 永远插在最右边，只会在"最后一页写满时开新页"，几乎不分裂——这就是 15.7 里"主键用自增"的直接原因。

完整清单（面试按这个顺序说）：

🔴 追问：DELETE 之后索引会变吗？会，但空间不一定还给你。 InnoDB 是标记删除（D6 讲 COUNT(*) 慢时提过），行不立刻物理删、只打标记，索引占的空间也不马上释放，页里留着空洞。要真正回收得靠 OPTIMIZE TABLE（本质是重建表 + 重建所有索引）——这就是"删了很多数据、表还是很大"的原因。 UPDATE 同理：改的是索引列 → 等价于「先删旧索引项 + 插新索引项」；改非索引列 → 索引不动。

3.8 索引的分类（一张体系图）

别背 7 项清单——抓"分类维度"就清楚了：同一张表上的索引，按四个不同标准被分成不同种类，而且一个索引可以同时属于不同维度（UNIQUE KEY (a,b) 既是唯一索引又是联合索引）。

维度 1：按「列数」分

KEY idx_a (a)        -- 单列索引
KEY idx_ab (a, b)    -- 联合索引（最左前缀，见 D4）

维度 2：按「唯一性」分

维度 3：按「物理存储」分（D3 正文讲的就是这个）

也叫聚簇 / 非聚簇。⚠️ 只有 InnoDB 才有聚簇索引（MyISAM 的索引和数据分开存，叶子存的是行物理地址）。

维度 4：按「用途」分

一页速查：

按列数：  单列索引  |  联合索引（最左前缀）
按唯一性：普通索引  |  唯一索引（允许多个 NULL）  |  主键索引（唯一 + 非空 + 只能一个）
按存储：  聚簇索引（叶子存整行）  |  二级索引（叶子存索引列 + 主键 → 回表）
按用途：  覆盖索引  |  全文索引（倒排）  |  空间索引

🎤 面试怎么答：按四个维度各说一句，重点落在最常考的两块——「联合索引的最左前缀」和「聚簇 / 二级索引 + 回表」。

D4　索引设计与失效

一句话结论：联合索引遵循最左前缀——能用多少用多少，直到断掉为止；范围条件只截断它后面的列。列上套函数/运算、隐式转换、like '%x'、or 混无索引列都会失效。 面试怎么用：最左前缀那句 + 失效四类是必背；「!= 不一定失效，看优化器成本估算」是加分点。

4.1 最左前缀原则

联合索引 (a, b, c) 在 B+ 树里的组织方式：先按 a 有序，a 相同再按 b 有序，b 相同再按 c 有序。

所以能被用来"快速定位"的，只有从最左列开始的连续前缀：

关键理解：最左前缀不是要求"把索引列都用上"，而是要求"用上的部分必须是连续的、从最左开始的前缀"。

有个细节：a = ? and c = ? 里 c 虽然不能定位，但如果恰好走覆盖索引（c 在索引里），存储引擎仍能在索引层判断 c，不一定非要回表。

4.2 索引失效的四类写法

一个必须澄清的误区：!=、not in、is not null 不能一概说失效——能不能走索引取决于优化器对回表成本的估算。面试时要答"看优化器的成本估算"，而不是背"这些都不走索引"。

4.3 范围条件会截断后面的列

索引 (a, b, c)
where a = ? and b > ? and c = ?     →  c 用不上索引定位

原因还是有序性：b 是范围，在 B+ 树里 b 的取值是连续一片，在 b 相同的前提下 c 才有序，而 b > ? 意味着 b 有多个值，c 的有序性就被破坏了。

但注意：c 用不上"定位"，不代表完全用不上——若走覆盖索引，c 仍可在索引层判断（ICP）。

📖 补原理：

• 索引常见面试题 🔥 —— 分类、最左前缀、回表讲得最全

• 索引失效有哪些？ 🔥 —— 逐条列举失效场景，含优化器成本估算的边界

D5　explain 与慢查询优化 ⭐

一句话结论：读执行计划按 type → key → rows → filtered → Extra 的顺序看。type 及格线是 range，掉到 index 或 ALL 就是索引没用上；Extra 里的 Using filesort / Using temporary 是要优化的坏消息。 面试怎么用：定位链四步（慢日志 → explain → 判病因 → 改完再对比）直接背；被追问用 5.7 的实战案例。 本节导航：5.1 阅读顺序 → 5.2 type → 5.3 rows/filtered → 5.4 Extra → 5.5 慢日志 → 5.6 定位链 → 5.7 实战案例 → 5.8 怎么查看表的索引

5.1 读执行计划的固定顺序

拿到 explain 的结果，别一行行扫，按这个顺序读：

① type     → 访问方式有没有退化？（最重要）
② key      → 用的是不是我期望的那个索引？
③ rows     → 预估要扫多少行（越小越好）
④ filtered → 过滤后剩下多少比例（多表连接时看）
⑤ Extra    → 有没有额外的排序/临时表动作

5.2 type：从好到坏

system > const > eq_ref > ref > range > index > ALL
  ↑                                              ↑
最好                                          最差

用「学生档案室」记（档案柜 = 页 ｜ 厚档案袋 = 完整行 ｜ 薄卡片 = 索引条目 ｜ 目录 = 非叶子节点）：

面试可以直接说："先看 type 有没有掉到 index 或 ALL，掉了就说明索引没用上。"

⚠️ range vs index（易错点）

常见误解：「index 是返回了索引列的全部数据、没有用 WHERE 过滤」——错。 type = index 完全可以带 WHERE（Extra 会显示 Using where）。它只说明一件事：

索引没有用来「缩小扫描范围」。

记忆：range 让「要装的更少」，index 让「每页装更多」→ 所以 range 优于 index，及格线定在 range。

怎么判断能不能走到 range：看能不能在索引上圈出连续的一段（是否 sargable）。LIKE '%张'、列上套函数、隐式转换都不 sargable。

比喻 → 术语（写笔记用左边，面试说右边）

5.3 rows 与 filtered

• rows 是估算值不是精确值——看数量级，别抠数字。

• filtered = 这一步扫描出来的行里，有多少百分比是「有用的」（没被 WHERE 干掉）。

  情况	含义
  rows = 100万, filtered = 100%	扫 100 万行，100 万行都有用 ✅
  rows = 100万, filtered = 1%	扫 100 万行，只有 1 万行有用 ⚠️ 白扫了 99 万行

  filtered 越高越好，原因是「扫描效率」——它低就意味着这个条件没被索引利用（是在 Server 层过滤的），白白扫了一大片。所以看到 filtered 低，行动应该是：考虑给那个条件加索引。

  在 join 里它还有第二层作用：MySQL 用 rows × filtered% 估算这张表会向下游传多少行，而下游被驱动表要按这个行数被查那么多次。所以理想状态是「rows 小 + filtered 高」——既没白扫，下游也轻松。

  ⚠️ 别搞反：filtered 低意味着传给下游的行少（不存在"放大下游扫描量"）。它的问题在上游白扫，不在下游。

5.4 Extra 里三个高频提示

5.5 慢查询日志的用法

开启 slow_query_log
  → long_query_time 设一个业务能接受的阈值（线上通常比默认值更小）
  → log_queries_not_using_indexes 顺手记下没走索引的语句
  → 用 mysqldumpslow 或 pt-query-digest 按「总耗时」和「执行次数」排序

注意排序维度：要看总耗时（单次慢但调用少 vs 单次快但调用百万次，后者往往更值得优化）。

5.6 定位链（固定套路，面试直接说）

慢日志找出 SQL
   → explain 看 type / key / rows
   → 判断是「缺索引」「索引失效」还是「SQL 写法有问题」
   → 改完再 explain 对比

5.7 ⭐ 你的实战案例（阶段 ④ 要产出的东西）

这是整个数据库学习中唯一背不出来的题，必须自己造一遍。模板：

【背景】订单表 500 万行，后台列表接口按「用户 + 时间范围 + 状态」筛选，翻到后面越来越慢，P99 到了 3 秒

【定位】
  · 慢日志找到 SQL
  · explain：type = ALL（全表扫描），key = NULL，rows = 498万，Extra = Using filesort
  · 结论：没有索引 + 额外排序

【第一次尝试】给 status 建单列索引
  → explain：type = ref，但 rows 还是 80 万，Extra 仍有 filesort
  → 原因：status 区分度太低（只有几个值），且不满足排序需求

【正确方案】建联合索引 (user_id, status, created_at)
  → 遵循最左前缀：user_id 等值 + status 等值 + created_at 用于范围与排序
  → explain：type = ref，rows 从 498万 降到 200，Extra 的 filesort 消失
  → P99 从 3s 降到 40ms

【取舍说明】
  · 为什么把 created_at 放最后：前面都是等值，它才轮到范围
  · 为什么不用覆盖索引：要查的列太多，全塞进索引会让索引膨胀、写入变慢
  · 代价：多了一个索引，写入稍慢、占空间

关键：把"第一次尝试失败"也讲出来——这比一次就成功更能体现你在思考。

📖 补原理：索引失效有哪些？ 🔥 —— 分析"为什么索引没用上"时，这篇是字典

5.8 怎么查看表的索引

最常用的是 SHOW INDEX FROM 表名（等价 SHOW KEYS FROM）：

SHOW INDEX FROM t;                 -- ① 列出这张表所有索引（最常用）
SHOW CREATE TABLE t;               -- ② 看建表语句里的索引定义（最直观）
SELECT INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, NON_UNIQUE, CARDINALITY
FROM information_schema.STATISTICS  -- ③ 要筛选/统计时走系统表
WHERE TABLE_SCHEMA = 'db' AND TABLE_NAME = 't';

SHOW INDEX 只看 4 个字段就够：

其余：Non_unique（0 = 唯一索引）、Collation（A 升序 / D 降序）、Index_type（BTREE / HASH / FULLTEXT）。

⚠️ 两个坑： ① Cardinality 是估算值，可能离谱；大批导入 / 删除后用 ANALYZE TABLE t 刷新统计信息。 ② SHOW INDEX 会把所有索引一次性列全——一张表 3 个联合索引就是十几行，别以为重复，按 Key_name 分组看。

怎么用它排查（这才是考点）：

① 慢查询 → SHOW INDEX FROM t   → 这个 SQL 的 where / order by 列上到底有没有索引
② 有联合索引 → 看 Seq_in_index  → 列顺序符不符合最左前缀
③ 有索引但没走 → 看 Cardinality → 选择性太低（如 status 只有 0/1）→ 优化器主动放弃

🔗 和 D5 正文配合：EXPLAIN 看"这次查询有没有走索引"，SHOW INDEX 看"这张表有没有索引可走"——一个看用没用上、一个看有没有，排查慢 SQL 时两个一起用。

📖 补原理：

• MySQL 分页有什么性能问题？怎么优化？

• count(*) 和 count(1) 有什么区别？哪个性能最好？

D6　深分页与 count 优化

一句话结论：深分页慢是因为「数据库没有行号」——LIMIT offset, n 只能从头数；解法是游标分页（把「按位置跳」改成「按值跳」）。count(*) 慢是因为 MVCC + 标记删除，InnoDB 存不了固定行数；而 COUNT(*) 和 COUNT(1) 一样快，带字段的 COUNT(字段) 既慢又语义不同（6.5）。 面试怎么用：两个「为什么慢」必背；游标分页的两个坑 +「多查一条」是加分点。 另外要知道怎么查一张表有哪些索引（见 5.8）。 本节导航：6.1 为什么慢 → 6.2 三种解法 → 6.3 count 为什么慢 → 6.4 总数怎么做 → 6.5 COUNT(*) / COUNT(1) / COUNT(字段)

6.1 深分页为什么慢

LIMIT offset, n 的语义要求先定位到第 offset+1 行，所以存储引擎只能按索引顺序一行行往下读，Server 层把前 offset 行丢掉，只返回最后 n 行。

offset 越大 → 白读的行越多（而且不在索引里还要回表）
→ 代价与 offset 成正比，和每页大小 n 关系不大

6.2 三种解法

游标分页的两个前提：

1. 排序键必须唯一且稳定——用时间排序时要带上主键做兜底，否则同一时刻的多条记录会漏掉或重复

2. 天生不支持跳到任意页

6.3 COUNT(*) 为什么慢

InnoDB 不保存精确行数，因为多版本并发控制下每个事务应该看到的行数并不相同，只能现场扫。

优化器会挑一棵最小的二级索引来扫（比扫聚簇索引小），但依然是整棵索引扫描，表越大越慢。

（MyISAM 自己维护精确行数，不带条件时能直接读——但它一旦加 WHERE，也和 InnoDB 一样要扫。）

6.4 总数怎么做：这是业务取舍

面试加分：主动说"更彻底的做法是产品侧改造——列表改无限滚动、用时间范围代替页码跳转，把分页天然变成游标形式"。这体现你会从系统设计层面解决问题，而不是只调 SQL。

6.5 COUNT(*)、COUNT(1) 和 COUNT(字段) 的区别

先给结论：COUNT(*) 和 COUNT(1) 完全等价（MySQL 8.0 已把 COUNT(*) 当特例优化，执行计划一样，没有性能差别）；COUNT(字段) 语义不同——它不数 NULL，而且用不上"数行数"的优化，通常最慢。

⚠️ 最常见的一条以讹传讹：说"COUNT(1) 比 COUNT(*) 快"。这是错的。COUNT(*) 里的 * 是"数行"的语义，不是"取出所有列"——所以它压根不会去读列值。

所以选哪个？看语义，不看性能：

• 要行数 → 写 COUNT(*)，意图最清楚

• 要某列的非空值个数 → 写 COUNT(字段)，这正是它存在的意义

• 别用 COUNT(字段) 去"数行数"（多算了 NULL 的坑，还更慢）

🔗 和 6.3 是同一道题的两半：COUNT(*) 为什么慢 → MVCC 要一致性视图 + 标记删除，InnoDB 存不了固定行数；哪个 COUNT 更快 → * 和 1 一样，带字段的慢。两问一起准备。

📖 补原理：count(*) 和 count(1) 有什么区别？哪个性能最好？

五、存储引擎与内存

索引讲的是「怎么找」；这一部分讲「数据存在哪、怎么被缓存」——是理解索引和日志的地基。

D7　存储引擎：InnoDB 与 MyISAM

一句话结论：InnoDB 给了业务最不能省的三样——事务、行级锁、崩溃恢复；MyISAM 只有表级锁、不支持事务。所以现代业务默认 InnoDB。 面试怎么用：三样能力必背（注意说「支持事务」而不是「有 MVCC」）；MyISAM 的两个优势是追问点。

现代业务默认选 InnoDB，因为它给了三样业务最不能省的东西：事务、行级锁、崩溃恢复。逐项对比：

记忆抓手：MyISAM 唯一的优势是"简单、计数快"，但缺的恰好是业务最需要的三样。所以只有在极少数纯只读的归档表上才会考虑它。

7.1 引擎全景：MySQL 是「可插拔存储引擎」

先建立架构认知：MySQL 分成 Server 层（管连接、解析、优化、执行）和存储引擎层（管数据怎么存怎么取）。Server 层是共用的，引擎层可以换——这就是"可插拔"。

💡 上一句解释了两件事：为什么换引擎索引行为会变（索引是引擎层实现的），以及为什么 InnoDB 和 MyISAM 能共存（只是同一套 Server 层下面换了个实现）。

面试怎么答：以 InnoDB vs MyISAM 为主线（就是上表那几行对比），再补两句： ① "可插拔"这个架构解释了 Server 层为什么能共用、而索引是引擎层实现的； ② 提一下 Memory 引擎为什么在临时表里见得到（GROUP BY / DISTINCT 的中间结果）。

📖 补原理：MySQL的架构是怎样的？ —— 讲清 Server 层与引擎层的分工，是理解"为什么换引擎行为会变"的前提。

D8　数据怎么存：页与行格式

一句话结论：InnoDB 读写磁盘的最小单位是「页（16KB）」，不是行——查一行也要读整页。一行超过半页就是行溢出：大字段挪到溢出页，行内只留 20 字节指针。 面试怎么用：varchar(n) 的 n 是字符数、行溢出的后果与解法（拆表）是必答。

这一节只回答一个问题：你 insert 的一行，到底躺在磁盘的什么地方、长什么样？ 它看着「没用」，但它解释了三件后面反复出现的事：为什么按页读写、为什么大字段要拆表、为什么 B+ 树能拿页当节点。

8.1 三层结构：文件 → 页 → 行

表空间（.ibd 文件）
   └── 段（segment）
         └── 区（extent，1MB = 64 个页）
               └── 页（page，16KB）      ← InnoDB 读写磁盘的最小单位
                     └── 行（row）

最关键的一句：

InnoDB 读写磁盘的最小单位是「页（16KB）」，不是行。 你查 where id = 100 这一行，InnoDB 也是把包含它的整页读进来，再从页里找那一行。

这一条直接解释了 Buffer Pool 为什么按页缓存（D9），也解释了 B+ 树的节点为什么是页（D2）。

8.2 行格式：一行数据在页里怎么摆

InnoDB 有多种行格式，现在默认 DYNAMIC（5.7 起）。以经典的 COMPACT 为例：

这解释了一个常被问的题：varchar(n) 的 n 最大能写多少？ 受「一行最大 65535 字节」限制，还要扣掉变长字段长度列表和 NULL 位图的开销——所以并不是想写多大就写多大。

8.3 行溢出：一行太大了怎么办

一行数据超过半个页（约 8KB）就是行溢出，InnoDB 的处理是：

TEXT / BLOB 这类大字段 → 挪到【溢出页】去存
行内只留一个约 20 字节的指针 → 指向溢出页

工程含义（能直接用在建表上）：

大字段会把行撑大 → 一个页里能放的行数变少 → 查询、排序、临时表全跟着变慢。 所以大字段要拆到单独的扩展表（详见 D15 数据类型、D16 建表规范）。

📖 补原理：MySQL 一行记录是怎么存储的？（离线 PDF 第 19 页）—— COMPACT 行格式的逐字节图解、varchar 上限的完整推导都在这篇。

D9　Buffer Pool：内存里的页缓存

一句话结论：Buffer Pool 缓存的是**「页」（16KB），不是「结果」。写先改内存页（脏页），由 redo log 兜底（WAL），后台慢慢刷。淘汰用改良 LRU**——新页先放 old 区。 面试怎么用：和 Query Cache 的区别、改良 LRU 的三条规则必背（最容易说反）；脏页 + WAL 是深挖点。 本节导航：9.1 为什么需要 → 9.2 缓存什么 → 9.3 读写路径 → 9.4 参数与改良 LRU → 9.5 记住这三句

InnoDB 最核心的组件，也是「数据库为什么快」的答案。

9.1 为什么必须有它：磁盘太慢

数据最终躺在磁盘的 .ibd 文件里，但每次查都读磁盘会慢到没法用。所以必须有一块内存，把磁盘上的页搬进来反复用——这块内存就是 Buffer Pool。

9.2 它缓存的是「页」，不是「结果」⭐

为什么 Query Cache 被删、Buffer Pool 留下？ 就两点： ① 失效粒度——Query Cache 是「整表失效」，写一多全废；Buffer Pool 是「页级失效」，改一页不影响别的页。 ② 锁竞争——Query Cache 用全局一把大锁，读写互相抢；Buffer Pool 拆多实例、页级管理。

9.3 读和写分别发生什么

读：

要读 id=100 这一行
   → 先算它在哪一页（比如第 5 页）
   → Buffer Pool 里有第 5 页吗？
        有   → 直接读内存 ✅（快）
        没有 → 从磁盘把【整个第 5 页（16KB）】读进来 → 再读内存

写（这里最容易懵）：

UPDATE users SET name='Tom' WHERE id=100;

① 找到所在的页；不在池里就先从磁盘读进来（读的仍是整页）
② 直接改【内存里的页】
③ 这一页就和磁盘上的版本不一致了 → 它变成【脏页 dirty page】
④ 后台线程择机把脏页刷回磁盘 —— 不是立刻

为什么敢不立刻刷？ 因为 redo log 兜底：修改会先写进 redo log（顺序写、快），崩了也能靠它恢复。这就是 WAL（Write-Ahead Logging）——把随机的磁盘写，变成「内存写 + 后台批量刷盘」。详见 D13。

9.4 三个配套参数 / 机制

改良 LRU：防止全表扫描把热数据冲走

池子放不下所有页，必须淘汰。但普通 LRU 有个致命问题：

突然来一条 SELECT * FROM big_table（全表扫描，要读 10000 页）
   → 这些页只读一次、以后不再用
   → 却会把原来的热点页全从链表里挤出去
   → 这叫【Buffer Pool 污染】，命中率暴跌

InnoDB 的解法是把链表分成两段：

┌──────────────────────┬──────────────────────┐
│  young 区（约 5/8）   │   old 区（约 3/8）    │
│  频繁访问的【热页】    │  新页的【观察期】      │
└──────────────────────┴──────────────────────┘
  头部 ←              分界点            → 尾部（从这里淘汰）

规则：

1. 新页从磁盘读进来 → 先放 old 区头部（不是 young 区！）

2. 在 old 区待够一定时间、又被访问了一次 → 才升到 young 区

3. 淘汰永远从尾部走

这样全表扫描读进来的一次性页只会停在 old 区、很快被淘汰，冲不掉 young 区的热数据。

9.5 记住这三句

• Buffer Pool 缓存的是「页」，不是「SQL 结果」 → 所以改一页只影响一页

• 写先改内存页（脏页），redo log 兜底，后台慢慢刷 → 这就是 WAL

• 淘汰用改良 LRU（young / old 分区） → 防止全表扫描污染

延伸名词（看到能想起来就行）：Change Buffer（非唯一二级索引的写先缓存在这里，减少随机读）、innodb_io_capacity（脏页刷盘速度）、young/old 的 5/8 与 3/8 比例。

📖 补原理｜揭开 Buffer Pool 的面纱 —— 含改良 LRU 与脏页刷盘的逐步拆解。本册只留面试结论，想看推导点这里。

六、事务与并发控制

前面是「单个人怎么查得快」，这一部分讲「很多人同时读写时怎么不出乱子」。

D10　事务与四种隔离级别

一句话结论：A / I / D 靠 undo log + redo log 实现，C 靠约束和应用保证。四档隔离级别依次解决脏读、不可重复读、幻读，代价是并发度递减。 面试怎么用：四档的「问题 → 解法」对应关系必背；RR 下 InnoDB 靠临键锁避免幻读。

10.1 ACID 各自靠什么实现

这是把事务串起来的关键一问——四条特性不是并列的，各有各的落点：

面试加分点：把"一致性"说成"由前三者共同作用的结果，不是单独一个机制"，比说"靠 X 保证"更准确。

10.2 四档隔离级别：依次解决问题，代价是并发度

用 SELECT @@transaction_isolation 查当前值。

InnoDB 在 RR 下怎么处理幻读——分两条路：

• 快照读（普通 SELECT）：靠 MVCC 给出一致性视图，看到的是事务开始那一刻的版本

• 当前读（for update、update、delete）：靠临键锁阻止其他事务在范围内插入

所以 InnoDB 的 RR 基本挡住了幻读、但没有完全挡住（快照读靠 MVCC、当前读靠临键锁，混合场景仍可能漏），这也解释了为什么它敢把 RR 设为默认。

10.3 长事务的代价（工程视角）

面试官问"事务有什么坑"时，答这个：

• undo log 版本链膨胀：旧版本不能及时清理，purge 线程压力大

• 锁被长时间持有：别的请求排队

• 主从延迟变大：从库重放这个大事务要很久

结论：线上要监控长事务并尽量拆小。

D11　MVCC：多版本并发控制

一句话结论：MVCC 靠版本链（undo log） + ReadView 实现。RC 和 RR 的核心差别是 ReadView 的生成时机——RC 每次快照读都重新生成，RR 只在第一次生成。 面试怎么用：RC/RR 差别是最高频追问；快照读 vs 当前读是基础题；「RR 完全解决幻读了吗」必被追问，而且不能只答结论、要能举出漏网的场景。 本节导航：11.1 两个基础设施 → 11.2 快照读 vs 当前读 → 11.3 RC 与 RR 差别 → 11.4 RR 挡住了多少幻读 → 11.5 代价

MVCC 要回答一个问题：怎么让"读"不阻塞"写"，还能保证同一个事务多次读到一样的结果？

11.1 两个基础设施

① 版本链——InnoDB 每行有两个隐藏字段：

• trx_id：最后修改这行的事务 ID

• roll_pointer：回滚指针，指向 undo log 里的上一个版本

所有历史版本就这样被串成一条链。读到哪个版本，由可见性规则决定。

② ReadView（读视图）——快照读时生成的一张「黑名单」：上面写着生成那一刻，还有哪些事务尚未提交。沿版本链逐个比对 trx_id，找到第一个对本事务可见的版本。

记忆：它就是一张黑名单——上面的人改的版本，我不看。

它内部有四个字段：

面试主要问 m_ids；另外三个是"为了快速判断而预存的边界值"。

可见性判断（完整 5 条，按顺序问）：

① trx_id == creator_trx_id       → 可见（我自己的修改，对我可见）
② trx_id <  min_trx_id           → 可见（生成黑名单之前就提交了）
③ trx_id >= max_trx_id           → 不可见（黑名单生成之后才开的事务）
④ trx_id 在 m_ids 里             → 不可见（生成时它还活跃着）
⑤ 落在 [min, max) 且不在 m_ids    → 可见（在区间内、但已提交）

⚠️ 别只背前 4 条——第 ⑤ 条最容易漏（很多"简化版"直接把它省了）。

判断出不可见怎么办？ 不是放弃，而是顺着 roll_pointer 往回走一个版本、再判断一次；直到找到可见版本为止。链走完还没有可见的，说明这行对当前事务"不存在"。

一个场景走一遍（这段看完就通了）：

时刻    事务 100                    事务 101
──────────────────────────────────────────────────
 t1     BEGIN
 t2     UPDATE balance = 200
        （把 100 改成 200）
 t3                                BEGIN
 t4                                SELECT   ← 在这里生成 ReadView
 t5     COMMIT
 t6                                SELECT   ← 再读一次

t4 生成 ReadView 时，只有事务 100 还活跃（事务 101 只读、还没分配 trx_id，所以不在名单里）：

m_ids = [100]    min_trx_id = 100    max_trx_id = 102    creator_trx_id = 101

此时版本链（从新到旧）：balance=200 (trx_id=100) → balance=100 (trx_id=99)

判断过程：

看最新版本 trx_id = 100
    → 落在 m_ids 里               → ❌ 不可见
顺着 roll_pointer 往回走 →
看 trx_id = 99
    → 99 < min_trx_id(100)        → ✅ 可见
        ↓
读到 balance = 100

t5 事务 100 提交之后，t6 再读一次——这就是 RC 和 RR 分岔的地方：

🔑 上面这张表就是 §11.3「RC 与 RR 的核心差别」的全部由来——差别只有"ReadView 的生成时机"这一句，其余（比如 RC 下间隙锁基本关闭）都是衍生的果。

11.2 快照读 vs 当前读

这条区分极其重要：它解释了"为什么 update 会阻塞、select 不会"，也是理解锁的前提。

11.3 RC 与 RR 的核心差别（最常考）

另外：RC 下间隙锁基本被关闭，RR 则依赖临键锁防幻读。

11.4 RR 到底挡住了多少幻读

结论先说：很大程度上挡住了，但依然没有完全解决。

⚠️ 本册统一口径：幻读在 RR 下只是「基本挡住」，任何地方都不要说「完全解决」。挡的方式有两条路（快照读靠 MVCC、当前读靠临键锁），并且还有漏网场景，下面分开讲。挡的方式分两条路：

但有两条漏网的路，都会真的多读出"幻影行"——这两条要能自己举出来，只答结论拿不到分。

场景一：自己 UPDATE 了那条"看不见"的行

时刻    事务 A（RR）                        事务 B
──────────────────────────────────────────────────────────
 t1     SELECT ... WHERE id = 5   → 空
 t2                                        INSERT id = 5，COMMIT
 t3     UPDATE ... WHERE id = 5   → 成功
 t4     SELECT ... WHERE id = 5   → 🟥 读到了

为什么：t3 的 UPDATE 是当前读，改的正是别人刚插进来的那一条；一改，这行的 trx_id 就变成了事务 A 自己——按 §11.1 的第 ① 条规则，自己的修改对自己可见，所以 t4 就读出来了。

场景二：先快照读，再切当前读

 t1     SELECT ... WHERE id > 100            → 3 条（快照读）
 t2                                        INSERT id = 200，COMMIT
 t3     SELECT ... WHERE id > 100 FOR UPDATE → 4 条（当前读，读最新版本）

为什么：当前读本来就读最新版本，完全不看 ReadView，所以别人插的行立刻出现。

🔑 一句话：MVCC 只管快照读，当前读一定要读最新版——两条路一混，幻读就漏出来了。

✅ 实践建议：真要防幻读，事务一开始就上 SELECT ... FOR UPDATE（先把范围锁住，别人插不进来），不要先做一次快照读。

11.5 代价

旧版本不会立刻删，要靠 purge 线程清理；长事务会让版本链和 undo log 持续膨胀。这与 D10 的长事务代价是同一件事的两面。

📖 补原理：事务隔离级别是怎么实现的？ 🔥 —— ReadView 的四个字段、可见性判断的完整推导，这篇讲得最清楚

📖 延伸：MySQL 可重复读隔离级别，完全解决幻读了吗？ —— 想答出"基本挡住但没完全挡住"的细节，看这篇

D12　锁：行锁、间隙锁、临键锁

一句话结论：加锁范围由索引决定——走了索引就锁那几行；没走索引就退化成锁全表。间隙锁/临键锁是 RR 下防幻读的手段。 面试怎么用：「update 没加索引会锁全表」是经典题；死锁的四个条件与排查是追问点。 本节导航：12.1 两种分类维度 → 12.2 加锁范围由索引决定 → 12.3 行锁锁的是「索引项」 → 12.4 意向锁 → 12.5 死锁

12.1 两种分类维度

按模式分：共享锁（S 锁，读锁）、排他锁（X 锁，写锁）。

按范围分（这个更常考）：

临键锁是"记录 + 前面的间隙"，所以它同时防住了"改这一行"和"往前面插新行"——这就是 RR 能挡住幻读的原因。

12.2 加锁范围由索引决定（最重要的结论）

UPDATE ... WHERE 没走索引，就定位不到具体记录，会把扫描到的所有记录都锁上，效果接近锁表。

这是面试高频题"update 没加索引会锁全表吗"的答案。反过来：

• 走唯一索引且等值查询 → 临键锁会退化（更精确、并发更好），但退化成什么，要看这条记录在不在表里：

• 走普通索引或范围查询 → 会锁住一片区间（临键锁）

⚠️ 「命中」和「没命中」结果不同，这是最容易漏掉的一半。

凭什么这么退化——因为加锁的目的是防幻读：命中时主键唯一，别人插不进同一个值，锁住这一行就够了；没命中时这一行根本不存在，没东西可锁，只能锁住它「本该在的那段空隙」，防止别人把它插进来。

两条前提（说清才算真懂加锁规则）：

• 加锁是加在「索引」上的，不是加在数据行上；

• 加锁的基本单位是临键锁（(前, 后]，前开后闭），只在「用记录锁就够」的场景才退化。

📌 本册术语统一（认得出即可，答题统一用前一个叫法）：

本册统一叫法	你在别处会遇到的名字
聚簇索引	主键索引 / 聚集索引
二级索引	辅助索引 / 非聚簇索引
临键锁	Next-Key Lock
记录锁	Record Lock
间隙锁	Gap Lock

工程结论：更新语句一定要保证走索引，否则并发直接崩。

12.3 InnoDB 的行锁，锁的是「行」还是「索引」？

→ 锁的是「索引项」，不是数据行。 这是本节最容易被问住的一个问法。

• InnoDB 的行锁加在索引记录（索引项）上。数据行本身没有独立的"锁位"。

• 所以：更新语句没走索引时，InnoDB 只能扫全表去找目标行，把扫到的每一条索引记录都锁上 → 效果上等价于锁了全表。

• 表上完全没有索引时，InnoDB 会用隐藏的聚簇索引（row_id）组织数据，加锁落在它上面，结果依然是锁全表。

🔑 一句话把 12.2 和这节焊起来： "加锁范围由索引决定"（12.2）和 "行锁锁的是索引项"（这节）说的是同一件事—— 因为锁加在索引上，走索引 = 只锁命中的索引项，不走索引 = 锁住全表所有索引项。

两种行锁模式（顺带记一下）：

⚠️ 别把"行锁"理解成"InnoDB 只有行锁"：InnoDB 还有表级锁（意向锁 IS/IX、元数据锁 MDL）和 自增锁（AUTO-INC）——行锁只是它的常态，不是全部。

12.4 意向锁

表级锁，表示"表里有人加了行锁"。它的作用是：让表锁和行锁能快速判断冲突，而不需要逐行扫描。

12.5 死锁

成因：两个事务以相反的顺序持有并等待对方的锁。

InnoDB 的处理：有死锁检测，会主动回滚代价较小的事务并报错；也可以靠 innodb_lock_wait_timeout 超时退出。

排查手段：SHOW ENGINE INNODB STATUS 看最近一次死锁的双方 SQL 和等待的锁。

规避手段（这四条要背）：

1. 固定加锁顺序（比如统一按主键升序更新）

2. 把事务拆小，缩短持锁时间

3. 保证更新走索引

4. 必要时降低隔离级别（RC 下没有间隙锁，死锁概率下降）

📖 补原理：

• MySQL 有哪些锁？ 🔥 —— 全局锁、表锁、行锁的完整分类

• update 没加索引会锁全表? —— 直接对应 7.2 那条结论

• MySQL 死锁了，怎么办？ —— 含真实死锁日志的读法

• 字节面试：加了什么锁，导致死锁的？ —— 想看加锁分析的实战演练，看这篇

D13　日志与两阶段提交

一句话结论：redo log 管崩溃恢复、undo log 管回滚 + MVCC、binlog 管归档与主从。两阶段提交是为了让 redo log 和 binlog 保持一致。 面试怎么用：三个日志的分工必背；两阶段提交要说清「为什么必须存在」。 本节导航：13.1 为什么需要 redo log → 13.2 binlog 三种格式 → 13.3 两阶段提交 → 13.4 双一配置

三个日志是 MySQL 最容易被问"串不起来"的地方。先把它们的分工钉死：

⚠️ 表里的「物理」和「逻辑」具体指什么？ 这是最容易含混的一点，说清它才能答好"redo 为什么不能直接给从库"。

🔑 物理 = 记「哪个页的哪个位置」被改了；逻辑 = 记「哪条数据的什么值」被改了。

同一条 UPDATE account SET balance = 200 WHERE id = 5（原值 100），假设这行落在 表空间 5 / 页 3：

	记录的内容
redo（物理）	「页 3 上这条记录，balance 改成 200」← 🔴 必须写明页号
binlog（逻辑）	「account 表、主键 id=5，balance：100 → 200」← ✅ 完全不提页

为什么这个区别是决定性的——因为主库和从库的"页"不保证一一对应（主库 id=5 在页 3，从库可能落在页 87）：

• 给从库 redo → 「改页 3」→ 从库的页 3 装的是别的数据 → 🔴 改错

• 给从库 binlog → 「id=5 的 balance 改成 200」→ 从库自己去找 id=5 在哪 → ✅ 一定对

那 redo 为什么非要用物理格式？ 因为崩溃现场可能"页只写了一半"：逻辑格式还得先找到记录（页都坏了就找不到）再重放，又慢又不可靠；物理格式可以照着页直接改，恢复快且确定。

	物理（redo）	逻辑（binlog）
定位方式	页号 + 页内位置	主键 / 行标识
和存储结构	🔴 强绑定	✅ 解耦
谁能读懂	只有 InnoDB 自己	任何引擎、任何实例
服务目标	本机崩溃恢复（要快）	跨机复制 / 归档（要别人读懂）

一句话记：redo 绑死页 → 只有自己懂；binlog 不依赖页 → 能给从库用。

13.1 为什么需要 redo log（WAL 思想）

Write-Ahead Logging：先顺序写日志并刷盘，脏页可以慢慢刷。

这里的"脏页"就是 D9 里 Buffer Pool 中"被改过、还没刷回磁盘"的内存页——写先落到 Buffer Pool（内存）→ 日志顺序写盘兜底 → 脏页由后台择机批量刷，这就是 WAL 把随机写变顺序写的全过程。

为什么这样更快？因为顺序写日志是连续的少量 IO，而随机刷脏页是离散的大量 IO。用"先写日志"把随机写转成顺序写，是数据库性能的关键设计。

关键限制：redo log 是一组固定大小的文件循环写，写到尾就回头覆盖已经 checkpoint 过的部分。所以它只保证"已提交的修改不丢"，不承担长期归档——归档的活交给 binlog。

❓ 顺着这句必被追问：整个库被误删了，能用 redo log 恢复吗？

🔴 不能，只能用 binlog。

为什么 redo 不行：它是循环写、边写边擦——数据一旦刷回磁盘，redo 里对应的记录就被覆盖掉了，所以 redo 里永远只留「还没落盘的脏页」，本身就是残缺的，凑不出全量。 为什么 binlog 行：它追加写、覆盖不了，全量记着每一次数据变更。

✅ 所以误删只能靠 「最近一次全量备份 + binlog 回放」，也就是 D17 §17.5 的 PITR（按时间点恢复）。

⚠️ 这句话和上面那张分工表说的是同一件事：redo 管「不丢」，binlog 管「能重建」。

13.2 binlog 的三种格式

线上主流选 ROW——用一点存储换主从一致。

13.3 两阶段提交：为什么必须存在

问题：一次提交要同时保证 redo 和 binlog 一致，可这两份日志不在同一处、没法原子写入。

为什么必须 2PC：redo（InnoDB 引擎层）和 binlog（Server 层）不是同一个组件、写盘时机也不同。如果各写各的，一次崩溃就可能出现两种坏情况——

• redo 提交了、binlog 没有 → 本机数据变了，从库和归档没变；

• binlog 写了、redo 回滚了 → 本机没变，从库却多了一条。

主从就此永久分叉，而且事后无法自愈。两阶段提交就是为了消灭这个窗口。

解法（两阶段提交）：

① prepare 阶段：写 redo log，标记为 prepare，刷盘
② commit 阶段第 1 步：写 binlog，刷盘（sync_binlog = 1）
③ commit 阶段第 2 步：把 redo log 的 prepare 标记改成 commit

两点必须说清： ① 第 ③ 步只是把内存里 redo 记录的标记位改掉，不是「又写了一份数据」，所以它极快； ② binlog 自己也是「先写文件、再刷盘」两步，判定用的是「binlog 是否完整落盘」（靠 XID event / event 完整性校验）。

崩溃恢复的判定规则（重启时扫 redo，只看「prepare 了但还没 commit」的事务）：

这样无论在哪一步断电，两份日志要么都算生效、要么都不生效，主库和从库不会分叉。

🎤 30 秒口述：「redo 和 binlog 是两个组件、写盘时机不同，各写各的会出现一边有一边没有，主从就此分叉。所以提交分两阶段：先写 redo 标 prepare，再写 binlog，最后把 redo 改成 commit。崩溃恢复时扫 redo，凡是处于 prepare 的，就看 binlog 完不完整——完整就提交、不完整就回滚。」

13.4 "双一"配置

两个都设 1 = 最安全（这就是"双一"）；放宽能提升吞吐，代价是崩溃时可能丢最近若干事务。

📖 补原理：undo log、redo log、binlog 有什么用？ 🔥 —— 从一条 update 语句出发，把三个日志的协作讲成一条线

10.4 哪些场景不适合脏读

先分清"脏"在哪：脏读 = 读到别人还没提交、可能马上回滚的数据。

关键区分：读到"旧值"和读到"可能从没存在过的值"是两回事。

• 读到旧值 → 别人曾真实提交过（不可重复读 / 幻读的范畴）

• 读到脏值 → 对方一 ROLLBACK，这个值根本不算数

判据一句话：读出来的结论，会不会驱动你写回数据库？

会出事的场景（绝对不能脏读）：

A 账户原本 1000 元
T1：UPDATE account SET balance = 900 WHERE id='A';   -- 未提交
T2：SELECT balance ... → 读到 900 → 判断"不够转 950"→ 拒绝这笔转账
T1：ROLLBACK;                                        -- 其实还是 1000 元

→ 一次本来能成功的交易被错误拦下。反过来（读到虚高的库存）就会超卖。

最难查的一种：一个事务里「快照不自洽」——同一个事务里读账户表、订单表、日志表，三张表读到的都是别人未提交的状态，数字来自三个不同时间点，对账时 A + B ≠ C，不报错、只是数字对不上。

🔑 分界线：读出来的结论会不会「驱动写回数据库」——会，就不能脏读；只是给人看，可以。

⚠️ 面试陷阱：如果被追问"那你怎么开脏读"，别急着答 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED。 先反问一句："MySQL 默认 RR，脏读在更上面一层就解决了，您是遇到什么具体问题吗？" 因为这道题考的不是技术开关，而是「业务对读取正确性的容忍度」——和续册 D24 是同一个思路： 没有绝对正确的隔离级别，只有业务能容忍的脏读时长。

七、设计与运维

前面是「怎么查、怎么并发」，这一部分是「怎么设计表、怎么扛增长、怎么不出事故」。

D14　主从复制、读写分离与分库分表

一句话结论：复制三步——主库写 binlog → 从库拉取写 relay log → 从库重放。延迟主要来自「从库重放」；读写分离的核心是「延迟 + 路由」；分库分表是最后手段，代价是分布式事务与跨库 JOIN。 面试怎么用：复制三步 + 延迟应对必背；读写分离要能说出三条路由规则；分库分表的「时机与代价」体现判断力。 本节导航：14.1 复制流程三步 → 14.2 三种复制模式 → 14.3 主从延迟 → 14.4 读写分离 → 14.5 分库分表

14.1 复制流程（三步）

主库把变更写进 binlog
   → 从库的 IO 线程拉取，写入中继日志 relay log
   → 从库的 SQL 线程重放，最终一致

14.2 三种复制模式

14.3 主从延迟：成因与应对

成因：

• 从库只有一个 SQL 线程串行重放（新版支持并行复制，但仍有约束）

• 大事务（重放要很久）

• 从库压力大、网络抖动

直接后果：写完立刻读，可能读到旧数据。

应对手段：

1. 写后读走主库（或关键业务强制走主库）

2. 用半同步降低丢数据风险

3. 监控 Seconds_Behind_Master 并告警

4. 把大事务拆小

14.4 读写分离

为什么做：业务普遍读多写少（典型 8:2 甚至更高）——把读分摊到从库，主库专心处理写。⚠️ 它提升的是读能力，写能力不变（写还是只有一个主库）。

在哪一层做：

核心矛盾只有一个：主从延迟 + 路由。 写完立刻读，可能读到旧数据。

三条路由规则（必背）：

① 写之后的读 → 走主库   （会话粘性 / 时间窗）
② 事务内的读 → 走主库   （事务里读写混合，读从库会拿到事务外的旧快照）
③ 强一致的读 → 走主库   （下单校验库存、支付后查订单：读到旧值会算错账）

🔑 判断标准一句话：读到旧数据的后果，是「体验差」还是「算错账」——算错账的一律走主库。

这类读占比很小，主体那 95% 的普通读还是走从库，否则读写分离就白做了。

两条配套措施：

• 从库设 super_read_only，从物理上防误写；

• 主从切换后先确认新主库已追平，再放读流量。

14.5 分库分表：时机与代价

没有统一阈值。常见判断依据：

• 单表与索引是否还放得进内存

• QPS 与写入量

• DDL 维护窗口 → 多数团队在千万到亿级之间开始评估。

拆分顺序：

优先垂直拆分（按业务拆库、按列拆表）
   → 最后才水平拆分（按分片键把同一张表拆到多个库）

分片的代价（这部分要能说全，体现你有架构意识）：

• 跨片查询与聚合变复杂

• 跨片事务要用分布式事务

• 全局唯一 ID 要额外方案（雪花算法等）

• 扩容需要数据迁移

常见工程结论："能不拆就不拆"——分片是最后手段，不是性能优化的第一选择。

📖 补覆盖：MySQL面试题 —— 主从复制与分库分表的问答版本

D15　数据类型与字符集

一句话结论：utf8 是个陷阱——它只支持 3 字节、存不了 emoji，要用 utf8mb4。金额必须用 DECIMAL（FLOAT 会精度丢失）。另外 SQL 与 NoSQL 不是对立的，「为什么选 MySQL 不选 NoSQL」是选型题（见 15.9）。 面试怎么用：utf8 vs utf8mb4、CHAR vs VARCHAR、DATETIME vs TIMESTAMP 是三个必问点；「金额为什么不能用 FLOAT」「utf8 和 utf8mb4 差什么」几乎每轮都会被问，答不出来很致命。 本节导航：15.1 字符串 → 15.2 大字段代价 → 15.3 数值类型（含 int(1) / int(10)） → 15.4 时间类型 → 15.5 字符集 → 15.6 防重复与判空 → 15.7 索引设计（选择性 / 主键 / 前缀索引 / 索引的优缺点 / 索引优化清单） → 15.8 表结构和索引一起设计 → 15.9 SQL vs NoSQL（选型） ⚠️ 15.4 / 15.5 为什么在附录：这两节（时间类型、字符集）的核心结论必须背，但原文里结论是和实现细节缠在一起写的，拆开反而难读——所以整段挪进 附录 C 的 C2 / C4，并在那里标了「必答」。读 D15 时请跳过 15.4 / 15.5 的编号，直接看附录 C 的 C2 / C4。

这一节看着像"查手册"，但类型选错的代价不是慢，是数据错了：金额用 FLOAT 会算错账，字符集用 utf8 会存丢 emoji。它是"便宜但必须做对"的事。

15.1 字符串：CHAR vs VARCHAR

先问一个更根本的问题：为什么要有两种字符串类型？ MySQL 在「省空间」和「读得快」之间取舍：

• CHAR = 定长：存多长都占满 n 个字符，不足补空格；不用存长度前缀。

• VARCHAR = 变长：按实际内容占，但要在值前面多存 1~2 字节记长度。

五个必须记准的细节：

1. VARCHAR(n) 里的 n 是字符数，不是字节数——实际占用取决于字符集。VARCHAR(255) 在 utf8mb4 下最多 255 × 4 = 1020 字节。面试问"n 代表字节还是字符"——答字符。

2. CHAR 不足会补空格，取出时 MySQL 默认裁掉。所以末尾可能带空格的值别用 CHAR——补的和原来的分不清，可能永远取不回来。

3. 长度前缀占 1 还是 2 字节，看的是「字节数」有没有超过 255。所以 utf8mb4 下 VARCHAR(63)（252 字节）是 1 字节前缀，VARCHAR(64)（256 字节）就变 2 字节。

4. 为什么到处都是 VARCHAR(255)？ 大半是历史习惯：MySQL 5.0.3 之前 VARCHAR 的上限就是 255 字节，框架默认值和老规范一直沿用。正确做法是按业务定长度，但也不用抠到极致——VARCHAR 本身按实际长度存。

5. CHAR 的定长优势，前提是字符集也定长。utf8mb4 是变长字符集，InnoDB 对 CHAR(N) 不一定按定长预留，所以它未必吃到传统意义上的定长红利；真想定长，写 CHAR(11) CHARACTER SET ascii。

💡 一句话怎么答：CHAR 是定长、不足补空格，适合长度固定的短字段；VARCHAR 是变长、带长度前缀，适合长短不一的字段。VARCHAR(n) 的 n 是字符数，实际占多少字节取决于字符集。 追问"手机号用哪个"：答 CHAR(11)（长度固定）；再补一句"utf8mb4 是变长字符集，InnoDB 对 CHAR 不一定按定长预留，所以 CHAR 的定长优势被削弱；想真定长可以用 CHAR(11) CHARACTER SET ascii；工程上我一般直接 VARCHAR(11)"。到这一句就停。

15.2 大字段的代价

TEXT（文本）、BLOB（二进制）——代价不在占磁盘，而在拖慢查询。

三条硬规则：

两个连带后果：

• SELECT * 会把大字段一起捞回来 → 白读一次溢出页。最常见的翻车现场：列表页接口把整篇正文查出来了

• ORDER BY / GROUP BY 带上大字段 → 中间结果一起搬 → 临时表变大、可能落盘

结论（三条落地）：

1. 拆到单独的扩展表：主表只留窄字段，扩展表 article_id + content，1:1

2. 干脆不进数据库：图片、附件放对象存储（OSS / S3），库里只存 URL

3. 应用层纪律：永远别写 SELECT *

扩展表关联的两条规范（面试加分）：

• 不要建物理外键（FOREIGN KEY）：外键在写入时要对父表加锁检查，高并发下容易锁等待甚至死锁；而且分库分表后跨库外键根本建不了。只做逻辑外键——字段上存 id、不加约束，一致性交给应用层（详见 D16）。

• 不要 join，用两次单表查询：1:1 扩展表通常是先查主表、再按 id 查扩展表。大表 join 优化器容易选错驱动表，而且分库分表后跨库 join 直接不支持。

⚠️ 去掉物理外键后，索引要自己补：InnoDB 建外键时若子表列没索引会自动建一个；外键去掉了，这个索引必须手动建（KEY idx_article_id (article_id)），否则按 article_id 查子表和"孤儿数据巡检"都会全表扫描。

📖 补原理：MySQL 一行记录是怎么存储的？ —— 行格式、数据页、大字段怎么存、行溢出的逐字节图解都在这篇

🎤 面试怎么答

TEXT/BLOB 这类大字段的代价主要不在磁盘空间，而在查询效率：一是不能设默认值，二是基本无法整列建索引（只能前缀索引），三是在 DYNAMIC 行格式下会被挪到溢出页（行内只留 20 字节指针），读它要多一次随机 IO，还会拖累排序和临时表。 所以规范做法是拆到单独的扩展表；图片附件直接放对象存储，数据库只存 URL。 关联上用逻辑外键、不用物理外键，查询用两次单表查询、不用 join。

15.3 数值类型

整数：按范围选，够用就行

自增主键建议直接上 BIGINT（高频）：

• INT UNSIGNED 上限只有 42.9 亿——订单表一天 1000 万，一年多就撞满

• 撞满后插入报主键冲突：Duplicate entry '4294967295' for key 'PRIMARY'

• 真正的代价是改不动：主键是聚簇索引，所有二级索引的叶子都存着主键值 → 改主键类型 = 整张表 + 所有索引全部重建（大表几小时 + 磁盘翻倍）

💡 多占 4 个字节，换一辈子不用改——这就是「设计时留余量」。

整数后面的括号 int(1) / int(10)：那是"显示宽度"，跟能存多大没关系

最容易踩的坑是类比错：varchar(10) 的 10 是容量（最多 10 个字符），但 int(10) 的 10 不是容量——int(1) 和 int(10) 都是 4 字节，取值范围完全一样。

CREATE TABLE t (a INT(1) ZEROFILL, b INT(10) ZEROFILL);
INSERT INTO t VALUES (5, 5);
-- a → 5            ← 宽度 1，刚好放下
-- b → 0000000005   ← 宽度 10，不足左边补 0

不写 ZEROFILL 呢：插入 123456 到两列 → 输出一模一样，括号等于白写。

⚠️ 必须说的一句：MySQL 8.0.17 起，整数类型的"显示宽度"已被废弃（SHOW CREATE TABLE 里不再输出）。 所以标准答法是：int(1) 和 int(10) 存储和范围完全一样，括号里是显示宽度，只有配合 ZEROFILL 才有意义，而 8.0.17 之后这个语法已经废弃，新表直接写 INT。

🔑 最经典的踩坑：TINYINT(1)——很多人以为它"只能存 0<sub>1"，其实 \`TINYINT\` 永远是 \*\*1 字节、\`-128</sub>127**。TINYINT(1)` 的 1 同样只是显示宽度（某些驱动会据此把它映射成布尔类型，那是驱动行为，不是 MySQL 的约束）。

金额必须 DECIMAL（几乎是送分题）：

CREATE TABLE t (amount FLOAT);
INSERT INTO t VALUES (0.1), (0.2);
SELECT SUM(amount);        -- 不是干净的 0.3，而是带尾巴的值
SELECT SUM(amount) = 0.3;  -- 于是这个判断【不成立】

💡 更严格的实战做法：用「分」存整数（12.34 元存 1234，类型用 BIGINT）。彻底没有小数问题、运算更快，可读性靠字段名约定——支付系统就是这么做的（微信/支付宝接口本来就按「分」传）。

⚠️ 浮点不是"不能用"，是用错地方。判据是「这个数需不需要精确相等或精确累加对账」：需要对账 → DECIMAL / 整数；不需要 → 浮点更划算。 该用浮点的：GPS 经纬度、评分、监控指标（QPS / 响应时间）、科学计算与机器学习（只能用浮点，DECIMAL 慢几个数量级）。

以下三小节讲"设计"（15.6–15.8）——前面 15.1–15.5 是"选类型"（记结论），这里开始是"做决策"（面试更爱追问这半截，要能推）。

15.6 防重复与判空：两条建表规范的延伸

① 防重复插入：靠唯一约束，不靠「先 SELECT 再 INSERT」

SELECT id FROM users WHERE email = 'a@b.com';   -- 查不到
INSERT INTO users (email) VALUES ('a@b.com');   -- 于是插进去

两个请求并发时都查出"没有"，然后都插 → 重复。检查和插入之间的间隙，应用层补不上。

正确做法是先建唯一约束，让数据库来挡：

email VARCHAR(255) NOT NULL UNIQUE

在这条约束之上，三种写法：

⚠️ 两个坑：INSERT IGNORE 会把任何错误都降级成警告（不只是重复键）；REPLACE INTO 本质是 delete + insert。 💡 URL 这类长字段建不了唯一索引 → 存哈希值（CHAR(32) / BINARY(16)），在哈希列上建唯一索引。

② NULL 和空字符串不是一回事

NULL          = 值不知道 / 压根没填
''（空字符串）  = 值是知道的，就是空的

真正要考的是 NULL 的三值逻辑：普通比较（= != < >）遇到 NULL，结果既不是 true 也不是 false，而是「不知道」（UNKNOWN）；而 WHERE 只保留结果为 true 的行。

SELECT NULL = NULL;      -- 结果不是 true，是 NULL        ★ 最反直觉
WHERE phone  = NULL;     -- ❌ 不报错，但永远查不出任何行
WHERE phone IS NULL;     -- ✅

由这一点长出三个必须知道的结论：

两条规范的共同点：别在应用层做数据库该做的事——防重复交给唯一约束，判空交给 IS NULL / NOT NULL。

15.7 索引设计：只看划不划算

索引的收益是把「扫全表」变成「直接定位」；成本是占磁盘 + 每次写入都要维护它。所以判据就是一个数：

选择性 = 不重复的值 ÷ 总行数
越接近 1 → 越值得建；越接近 0 → 越不值得建

反例：性别字段。100 万行、只有男女两个值 → 选择性 ≈ 0.000002。WHERE sex = '男' 会命中 50 万行，走索引要回表 50 万次随机读，比全表顺序扫还慢——所以优化器干脆不用这个索引。

⚠️ 索引不是越多越好：每个索引都占空间，而且插入一行要更新所有索引、还可能页分裂。一句话：索引是拿写性能换读性能。

主键：为什么用自增，不用 UUID

关键在于 B+ 树的叶子是有序链表：

自增：1  2  3  ...  1000000     新行永远追加在【最右边】→ 不分裂
UUID：a3f...  7b2...  c91...    位置全随机 → 插进中间 → 页满就【页分裂】

页分裂的代价：一页拆成两页 → 产生碎片、页利用率从 100% 掉到 50%，而且插入位置随机就是随机 IO。

还有一个更狠的：UUID 是 36 字节字符串，BIGINT 只有 8 字节 → 索引键大 4 倍多 → 一页放不下那么多条目 → 树更高 → 查得更慢。

但自增在分库分表后会冲突 → 用雪花算法：高位是时间戳，整体趋势递增（不是严格递增，但一直往右走）→ 既全局唯一，又不怎么页分裂。

前缀索引：字段太长怎么办

ALTER TABLE t ADD INDEX idx_url (url);      -- ERROR 1071: Specified key was too long
ALTER TABLE t ADD INDEX idx_url (url(20));  -- 只拿前 20 个字符

n 怎么选——还是选择性：不断调大 n，直到 COUNT(DISTINCT LEFT(url, n)) 逼近 COUNT(DISTINCT url)。

代价（追问点）：

① 不能用于覆盖索引 —— 索引里只有前缀、没有完整值，必须回表
② 不能用于完整的 ORDER BY / GROUP BY
③ 前缀太短 → 区分度不够 → 回表次数反而变多

💡 长字段还有另一条路：存哈希值再建唯一索引（见 15.6）。 区别是 前缀索引支持 LIKE 'abc%' 前缀匹配，哈希只支持等值查询——按需求选。

索引的优缺点：本质是「用空间和写入速度换读取速度」

这句话是整道题的骨架。优点主要是三条：① 把"扫全表"变成"直接定位"（扫描行数从百万级降到几条，B+ 树叶子还有序、能把随机 IO 变有序）；② 覆盖索引免回表；③ 索引本身有序，能省掉 ORDER BY 的额外排序（免 Using filesort）。④ 唯一索引还能顺手当数据约束用——这是业务价值，不只是性能。

代价：

所以答案是：不是越多越好。每条索引都是「读性能的收益」和「写性能 + 空间的成本」的一次交易——读多写少的表（配置、字典、报表）可以多加；写多读少的表（日志、流水、消息）越少越好。常见经验值：单表索引控制在 5 个左右。

⭐ 一个能体现深度的追问：唯一索引和普通索引，谁写得更快？答案：普通索引更快。 原因在 change buffer（写缓冲）：普通索引插入时不需要立刻判断唯一性，如果目标页不在内存里，可以先把改动记进 change buffer，等后面有人读这个页时再合并，省掉一次随机磁盘读；而唯一索引必须立刻把页读进内存检查冲突，用不上这个优化。 🔑 结论：唯一索引只在业务真需要唯一性约束时才用，不要为了"读得更快"去加它——读性能它并不比普通索引好。

索引优化清单：查询侧 / 索引侧 / 表结构侧

这是那类"给你张表，说查询很慢，怎么办"的题。按三个方向回答，条理立刻清楚。

① 查询侧（不改索引也能救）

函数 / 运算不放在等号左边： DATE(create_time) = '2025-01-01'
                          → create_time >= '2025-01-01' AND < '2025-01-02'
把 % 放右边：   LIKE '%abc%'  →  LIKE 'abc%'
统一字符集：    避免隐式转换导致索引失效（最隐蔽的一种，见附录 C5.1）
OR 拆 UNION ALL：OR 连了无索引的列会整体失效
深分页改游标：  LIMIT 100000, 10  →  WHERE id > 上一页最后一个 id LIMIT 10
只查需要的列：  别 SELECT *（避免白读溢出页、也更容易命中覆盖索引）
别用 ORDER BY RAND()：它会全表排序

② 索引侧

最左前缀没断（等值在前、范围在后）
能走覆盖索引就让查询的列都在索引里
长字段用前缀索引
两张表连接的关联列两边都要有索引
不行就用 FORCE INDEX 干预优化器

③ 表结构侧

大字段拆到扩展表 → 主表变窄 → 一个页放更多行 → 扫描更快、更容易覆盖
冷热字段分开
超大表归档 / 分区，减少单表数据量

🔴 最后一定要补这句：优化前后都用 EXPLAIN 对比验证（看 type、key、rows、Extra 有没有改善）。说"我会用 explain 验证"比背十条优化技巧更值钱——因为优化不能凭感觉。

15.8 表结构和索引要一起设计

这是最容易被忽略、但最有价值的一条：

索引建在列上——列的区分度、字段长度、是否经常参与排序和范围查询，都会反过来影响表怎么拆。 很多慢查询的最优解不是再加一个索引，而是用表结构把问题消掉：把大字段拆走让一个数据页能放下更多行、把经常一起查询的字段收进同一张表省掉一次连接。

规范管的是"底子"，索引管的是"热点"，两者要放在一起看。

🎤 面试怎么答

表结构和索引不是分开设计的两件事。索引的键长、区分度、能不能覆盖，全都由表结构决定——列的分布决定索引有效不有效，行的宽度决定扫描成本，怎么拆表决定能不能用覆盖索引省掉回表。 所以遇到慢查询，我不会第一反应就加索引，而是先看能不能用表结构把问题消掉：把大字段拆到扩展表让主表变窄（一页装更多行）、把经常一起查的字段收到一起（省掉一次 join）。 一句话：规范管的是底子，索引管的是热点，两者要放在一起看。

📖 补覆盖：MySQL面试题 · SQL基础 —— 三大范式、连表查询、避免重复插入（UNIQUE / ON DUPLICATE KEY UPDATE / INSERT IGNORE）、SQL vs NoSQL 的区别等基础题

15.9 SQL vs NoSQL：这题考的是选型

这是小林 MySQL 面试题的第 1 题，也是很多面试的开场题。它排在「类型选型」后面讲，因为两者是同一件事——15.1–15.8 是「字段怎么选」，这一节是「数据库怎么选」。

先破一个最常见的误读：NoSQL 的 "No" 不是「不要 SQL」，而是「Not only SQL」（不只是关系型那一套）。它卖的不是「不用 SQL 语言」，而是「不用表 + 行 + 关系模型」。

SQL 的优势和代价：优势是 JOIN、事务、强约束、标准 SQL；代价是 schema 严、水平扩展难，海量数据下只能靠分库分表打补丁。

NoSQL 的优势和代价：优势是 schema 灵活、天生水平扩展；代价是 基本没有 JOIN、事务能力弱、多数只保证最终一致，一致性要应用层自己兜。

四类 NoSQL 各擅长什么（常被追问）：

⚠️ 两个高频误解，答错就露馅： ① 「关系型数据库」≠「结构化数据」：「关系型」说的是数据库的类型，「结构化」说的是数据的形态。关系型擅长存结构化数据，但这不是它的定义；严格说没有「结构化数据库」这个规范叫法。 ② 「关系」不是人际关系：它来自关系代数（集合论），指的是「表与表能关联、能用 SQL 运算」。

🔴 选型话术（这才是这题真正想听的）——别答成「SQL 好、NoSQL 不好」，要答成「看数据形态和一致性要求」：

• 数据结构化、关系强、要事务（订单、金额、库存）→ SQL

• 数据形态不固定 / 海量 / 高并发读写（日志、画像、缓存）→ NoSQL

• 现实里最多的是混用：MySQL 当权威数据源 + Redis 挡读流量（+ HBase / MongoDB 存海量明细）——不是二选一

🔗 和续册的接缝：这里答「为什么选 MySQL 不选 NoSQL」，续册 18b 的 D19 答的是下一层——「为什么用 MySQL + Redis 混用，而不是让 Redis 取代 MySQL」（四问：能不丢？放得下？查得动？要事务？）。两题一起准备，别只准备一个。

🎤 面试怎么答（30 秒版）

SQL 是关系型数据库，数据存在表里、用 SQL 操作、支持 JOIN 和完整 ACID 事务，强 schema、主要靠垂直扩展； NoSQL 是 Not only SQL，有键值、文档、列族、图四类，schema 灵活、天生水平扩展，但基本没有 JOIN、事务能力弱、多数只保证最终一致。 所以不是谁替代谁，而是看场景：强一致、关系复杂、要事务的用关系型；海量、高并发、格式不固定的用 NoSQL。真实系统一般混用——MySQL 做唯一数据源，Redis 挡读流量。 最后补一句：面试官问这个往往是在跟着你的项目问——「你们为什么用 MySQL 而不是 MongoDB」才是真正的正题。

D16　范式、反范式与建表规范

一句话结论：三范式各解决一个问题（原子性 / 消除部分依赖 / 消除传递依赖）；反范式是拿一致性换读性能。建表规范清单可以直接背。外键：规范上要，工程上大厂禁用物理外键（16.4）。 面试怎么用：说范式要讲「解决什么问题」，不要背定义；反范式的代价要主动说。 本节导航：16.1 三个范式 → 16.2 反范式 → 16.3 建表规范清单 → 16.4 外键约束本节分两半：16.1–16.3 是「机制」（范式、建表规范——记结论）；「设计」部分（防重复、判空、索引设计）已前移到 D15 的 15.6–15.8，因为那几节讲的是「表该怎么设计」，和 D15 的字段选型是同一件事。

16.1 三个范式各解决一个问题

范式化的收益是"改一处"，代价在查询侧：严格按范式拆表，一份事实只存一份，不会产生矛盾的多份数据（这正是范式要消灭的插入/更新/删除异常）；但查询要连多张表，连接越多越慢。

⚠️ 一个高频误解：不是「每个范式消灭一种异常」。 范式有 3 个、异常有 3 个，这两个"3"之间没有对应关系。

正确的模型是「一个病，三个症状」：冗余是病，三种异常是同一块冗余的三个表现。 比如选课表里冗余了 student_name，这一块冗余会同时长出：

• 更新异常：张三改名 → 要改他所有选课记录，漏一条就矛盾

• 插入异常：张三还没选课 → 表里没行 → 他的名字进不了数据库

• 删除异常：张三把课全退了 → 记录全删 → 他的名字也没了

范式管的是「冗余的形态」（1NF：多值塞一格；2NF：部分依赖；3NF：传递依赖），砍掉任何一种形态的冗余，三种异常会一起减轻。

一句话：范式不是目的，只是控制冗余的手段。

🎤 面试怎么答

三大范式各解决一个问题：1NF 要求字段原子，解决"一个字段塞多个值"；2NF 针对联合主键，解决"字段只依赖主键的一部分"；3NF 解决"非主键字段之间互相依赖"（传递依赖）。 违反它们会带来插入异常、更新异常、删除异常——注意不是每个范式对应一种，而是任何一处冗余都会同时引发这三种症状。 范式的收益是一份事实只存一份、改一处即可；代价是查询要连多张表。 所以我的理解是：范式不是目的，只是「控制冗余」的手段——实际设计时会在热点查询上主动反范式。

16.2 反范式：拿一致性换读性能

常见三种做法：

1. 把高频查询要用的字段冗余到主表

2. 把统计结果单独存成计数列

3. 把明细提前汇总成宽表/统计表

⚠️ 一旦冗余，就必须回答"谁来保证一致"：

• 要么在同一个事务里一起更新

• 要么走异步补偿 + 定时校准，并接受短时间最终一致

只冗余而不安排维护责任，就是给自己埋数据错乱的坑。

💡 顺带说清什么是 MQ：MQ = Message Queue（消息队列），是一个"消息中转站"——生产者把消息扔进去，消费者慢慢取，两边不用互相等。 好处是异步、削峰填谷、解耦、可靠；代价是不实时、可能重复、可能丢——所以消费端要幂等（同一条消息处理多次、结果一样），系统要有对账兜底。 常见产品：Kafka（吞吐最高）、RocketMQ（事务消息强）、RabbitMQ（中小规模常用）。 Python 后端最常接触的组合是 Celery + Redis / RabbitMQ——Celery 是异步任务框架，Redis / RabbitMQ 是它背后的"柜子"（broker）。

⚠️ 一个必须分清的边界：订单里的商品名、价格，严格说不是「冗余」。 商品表现在卖 80、订单里是 100——这两个都是真的：一个是"现在的价格"，一个是"当时的成交价"。而"当时的成交价"无法从商品表反推出来，所以它是独立的一份事实，不是同一份事实存了两遍。

判据是字段的业务语义：

• 语义是「当时的值」（成交价、下单时的商品名、收货人姓名、收货地址）→ 快照，永不同步，改了反而是 bug

• 语义是「现在的值」（为了省 join 而冗余的"客户当前手机号"、下单账号的昵称）→ 真冗余，不同步就是数据错乱

⚠️ 「客户名」有两种，别混：收货人姓名是交易凭证（谁收的货，可能是他爸妈），必须冻结；下单账号的昵称只是展示用，属于缓存——要么跟着同步，要么干脆不存、join 取。

所以判断一个字段要不要跟着源表更新，看的不是"它是不是重复了"，而是"它的语义是当时还是现在"。 快照必须单独存的原因用收据来想最清楚：商家不会因为今年降价，就回头改你去年那张收据。订单就是那张收据——对账、审计、纠纷举证、法律凭证都要能复现"当时发生了什么"，而这些信息一旦被同步覆盖，就再也算不回来了。

💡 心智模型：状态 vs 事件。用户表 / 地址簿 / 商品表是「状态」（只关心现在是什么，会被覆盖）；订单 / 流水 / 发票是「事件凭证」（一旦发生就不该变）。下单时要把地址簿里选中的地址复制一份到订单上并记下来源 id——是复制，不是引用。未发货时允许改地址，改的也是订单自己的快照，不是地址簿。

🎤 面试怎么答

反范式就是故意冗余，用「写的时候多维护一份」换「读的时候少一次 join」。常见三种：冗余字段（订单表冗余"客户当前手机号"）、计数列（文章存评论数）、提前汇总（报表走统计表）。 关键不是"要不要冗余"，而是冗余之后谁来保证一致：不能错的数据（库存、余额）放同一个事务里更新；错了也无所谓的（点赞数）走异步 + 定时对账。 判断依据是先问「这个数错了会怎样」——看业务能不能容忍不一致。 另外要区分：订单里的商品名、价格严格说不是冗余，而是"当时的成交价"这份独立的事实（快照）——判断依据是字段语义是"当时"还是"现在"。

16.3 建表规范清单（可以直接背）

🔬 物理外键 vs 逻辑外键

互联网公司普遍用逻辑外键，五个原因：

代价：会产生「孤儿数据」（指向已不存在的 id），兜底三条：代码规范（删除统一走一个 service 方法）、软删除（父记录不真删，父子关系天然不断）、定时巡检（LEFT JOIN 扫孤儿数据）。

⚠️ 去掉物理外键后，子表那一列的索引要自己补（KEY idx_xxx (xxx)）——InnoDB 建外键时会自动为子表列建索引，不建外键就没人建了。

🔬 顺带澄清两个词

• DDL（Data Definition Language，数据定义语言）：改结构的 SQL——CREATE / ALTER / DROP / TRUNCATE。和它相对的是 DML（改数据的 INSERT / UPDATE / DELETE）。DDL 的麻烦是可能锁整张表、而且不能回滚，所以"大表 DDL"是个专门话题（详见 D17）。

• 雪花算法（Snowflake）：分库分表后自增主键会重复，需要一个全局唯一的方案。它把 64 位切成四段：

  TEXT

   0 │ 41 位毫秒时间戳 │ 10 位机器 ID │ 12 位序列号
  符号位   （约 69 年）      （1024 台）     （每毫秒 4096 个）
  

  高位是时间戳 → id 整体趋势递增 → 插入靠右、减少页分裂；机器 ID + 序列号保证全局唯一；单机约 400 万个 / 秒。 坑是时钟回拨（NTP 校时把时间往回调，可能生成重复 id）——面试主动提这个是加分点。

🎤 面试怎么答

我们建表有几条硬规矩：主键用自增 BIGINT（避免用满重建，而且递增插入减少页分裂）、每张表带 created_at/updated_at、金额用 DECIMAL、状态用 TINYINT 并把取值写进注释、尽量 NOT NULL 加默认值、用逻辑删除不物理删除、有唯一性要求的业务键一定建唯一索引、大字段拆到扩展表。 另外我们不用物理外键、只用逻辑外键——因为外键写入时要对父表加锁检查、分库分表后建不了、级联删除风险也不可控；代价是要靠代码规范、软删除和定时巡检去兜孤儿数据。 核心思路是：能让数据库约束保证的，就别交给代码。

16.4 外键约束

上面讲了"大厂为什么禁用物理外键"，这一节把外键本身补齐——面试考的是"它是什么 + 级联行为 + 什么时候还能用"。

① 它是什么：外键把子表的列绑定到父表的主键 / 唯一键上，由数据库保证引用完整性——往子表插一条，父表那条必须存在。

② 五种级联行为（被追问时要说得出）：

③ 什么时候还能用（说得平衡才像有判断力）：

• 单体应用、数据量不大、强一致要求高（财务、内部系统）→ 用外键换开发效率是划算的

• 高并发、要分库分表（互联网业务）→ 禁用，改用应用层保证 + 唯一索引兜底 + 定时巡检对账

④ 一个容易被忽略的"外键福利"：

InnoDB 建外键时会自动给子表的外键列建索引（你没建它也会建）。 原因是加锁要靠索引——子表那一列如果没索引，父表一改就得锁子表全表。 所以去掉物理外键之后，一定要自己把这列索引补上（KEY idx_xxx (xxx)），不然并发性能会突然变差。

🎤 一句话答"外键"：外键是让数据库来保证引用完整性的约束，级联有 RESTRICT（默认拒绝）/ CASCADE（跟着删改）/ SET NULL 三种常用行为。它的问题是写的时候要跨表加锁校验、分库分表后建不了、级联删除不可控，所以互联网公司普遍改成逻辑外键，靠应用层和巡检兜孤儿数据；但外键自动建的那个索引要自己补回来。

D17　大表 DDL 与备份恢复（加分项）

一句话结论：判断 DDL 会不会锁表看三个维度（版本 / 算法 / 是否重建表）；在线变更工具的通用思路是建影子表 + 增量同步 + 原子替换。顺带必答：DELETE / TRUNCATE / DROP 的区别（17.6）。 面试怎么用：这是加分项——能说出「影子表」思路就够，不必背工具名。 本节导航：17.1 三个维度 → 17.2 等待 vs 执行 → 17.3 在线变更工具 → 17.4 备份分类 → 17.5 恢复演练 → 17.6 DELETE / TRUNCATE / DROP

17.1 判断一次变更会不会"锁表"：看三个维度

⚠️ 各版本对具体操作的在线能力不同，同一操作在不同版本表现也不同——结论必须以目标版本的官方文档和实际环境测试为准，不能凭印象下断言。

17.2 真正拖垮业务的往往是"等待"而不是"执行"

变更语句需要拿排他的 MDL。如果表上有长事务或慢查询没结束，这条语句就会排队等锁；而它一排队，后面所有访问这张表的请求都会被它挡在后面，连接很快堆满。

所以做变更前：先确认没有长事务，并给变更设置合理的等待超时——宁可失败重试，也不要无声地排队把线上拖死。

17.3 在线变更工具的通用思路

🟡 面试权重：低，只需记两样——「影子表四步 + 最后一步是 RENAME TABLE」和「gh-ost 读 binlog 重放 / pt-osc 用触发器」。工具名不必背；本节其余内容属于 【了解即可】，不进口述。

gh-ost、pt-online-schema-change 都是同一个套路：

① 在旁边建一张结构正确的新表
② 把老表数据分批拷贝过去
③ 同时把拷贝过程中的增量变更同步到新表（触发器 / 解析 binlog 重放）
④ 最后在极短时间内做表名切换

本质：把「一次长时间的锁」换成「分批拷贝 + 一个极短的切换窗口」。

🟡 【了解即可，不用背】　代价也要说清楚：需要额外磁盘空间；拷贝期间要盯主从延迟和负载；切换瞬间要处理应用侧重连；有外键或触发器时限制更多；中断要能安全回滚。

17.4 备份：按三个维度分类

17.5 恢复演练与 PITR

没演练过的备份等于没有备份。

因为备份真正要回答的是："在可接受的停机时间内，能不能恢复到可接受的那个时间点？" 这只有实际恢复一遍才知道。演练要覆盖：全量还原、增量回放、恢复到指定时间点、数据完整性校验。

误删数据的处理（PITR，按时间点恢复）：

① 先止损：停掉相关写入 / 隔离问题实例（防止日志被覆盖、错误扩散）
② 用最近一次全量备份还原出基础副本
③ 用 binlog 从备份对应的位点，回放到误操作之前的位置

前提：binlog 一直开启；异地保存的时间要长于"最晚可能发现误删的时间"——否则等你发现，日志早被清理了。

17.6 DELETE、TRUNCATE、DROP 有什么区别

记忆口诀：删得越"整"，越不可回滚。

⚠️ 两个必须知道的细节： ① TRUNCATE 是 DDL，会隐式提交——所以不能把它当成"放在事务里可回滚的清空"。 ② 大表别直接 DELETE 全表——会长时间持锁、撑大 undo / binlog。正确做法是按主键分批删：

SQL

DELETE FROM t WHERE id > 1000 ORDER BY id LIMIT 1000;   -- 循环执行

或者先 RENAME TABLE 换成备份名，再慢慢清理。

💡 顺带澄清一个常被混淆的点：MySQL 8.0 起有「原子 DDL」（DDL 语句本身不会执行到一半就留下半成品），但这不等于 DDL 可以回滚——两件事别混。

D18　SQL 场景题速查

一句话结论：手写 SQL 的题型就那么几类，套路可以记，但答题习惯比写对更重要——先问清表结构与数据量级，最后主动补边界（NULL、重复、并列、时区）。 面试怎么用：先说思路、再写实现、最后补边界。题型一半靠窗口函数，一半靠几个固定小技巧。 本节导航：18.1 答题习惯 → 18.2 SQL 完整执行顺序 → 18.3 连表查询 → 18.4 窗口函数 → 18.5 TopN → 18.6 连续登录 → 18.7 排名三兄弟 → 18.8 行转列 · 存在性与差集 → 18.9 慢 SQL 与权重 → 18.10 常用函数

18.1 答题习惯：四步（比任何一道题的答案都重要）

① 先问清【表结构】和【数据量级】   ← 确认前提，而不是急着写
② 写出能跑通的主查询
③ 最后主动补【边界处理】：NULL、重复、并列名次、时区
④ 说明"我会用 explain 验证"

第 ③ 步是拉开差距的地方——大多数人写完就停了。

18.2 SQL 完整执行顺序（所有"怪现象"的总解释）

我们写 SQL 是从上往下写的，MySQL 跑它是按另一套顺序。记住这一条，一半的报错都能自己推出来：

① FROM / JOIN  →  ② ON  →  ③ WHERE  →  ④ GROUP BY  →  ⑤ HAVING
→  ⑥ SELECT  →  ⑦ DISTINCT  →  ⑧ ORDER BY  →  ⑨ LIMIT

心法只有一句：SELECT 看着排在开头，其实第 6 位才执行。所以：

⚠️ ORDER BY / HAVING 用别名是 MySQL 的扩展——标准 SQL 里 HAVING 排在第 ⑤ 步、SELECT 在第 ⑥ 步，严格说也不该能用。MySQL 是解析时发现名字对不上，就回头去 SELECT 列表里找同名的。面试别说"HAVING 能用别名"，要说："标准顺序里它不能用，MySQL 允许是扩展；为了可移植我会写 HAVING COUNT(*) > 5。"

注意 WHERE 是唯一连 MySQL 也不允许的——因为那时行都还没成型。

⚠️ 本节分两半：18.1–18.3 是「机制」（答题习惯、执行顺序、连接语义——必须真懂）；18.4–18.9 是「场景题」（套路固定，刷熟即可）。面试前时间紧，先刷 18.4–18.9 的模板，再回头补机制。

18.3 连表查询：四种连接

employees                    departments
name     dept_id             id     dept_name
张三      1                   1      技术
李四      2                   2      市场
王五      NULL                3      财务

LEFT JOIN + IS NULL 就是「求差集」——最常用的组合：

SQL

-- 找出"没有分配部门的员工"
SELECT e.name FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.id IS NULL;

和 NOT EXISTS 等价。求差集优先这两种写法，别用 NOT IN（原因见 15.6 的 NULL 陷阱）。

补充：INNER 可以省略，光写 JOIN 就等于 INNER JOIN。内连接是对称的（左右表换位置结果一样），所以 INNER JOIN 时过滤写 ON 还是 WHERE 结果相同——但别养成习惯，一换成 LEFT JOIN 就是 bug。

18.4 先搞懂窗口函数（一半的题靠它）

它解决一个很具体的麻烦：GROUP BY 算部门总额时会把行压扁，姓名全没了。窗口函数不动原来的行，只在右边多加一列：

SELECT name, dept, salary,
       SUM(salary) OVER (PARTITION BY dept) AS 部门总额   -- 每行都带上"我部门的总额"
FROM emp;

行数一点没变。 语法拆开就是两个大白话：

• OVER = "别把人压扁，算完贴回到每一行上"

• PARTITION BY dept = "按部门分堆，各自算各自的"（只看跟我同部门的人）

• 堆里再加 ORDER BY = 排个队；配上 ROW_NUMBER() 就是"你排第几"

⚠️ 窗口函数是 MySQL 8.0 才有的，5.7 只能用相关子查询等笨办法。 ⚠️ PARTITION BY 只划分计算范围，不压行、也不影响最终输出的行顺序。

18.5 分组取 TopN：先算，再筛

SELECT * FROM (
  SELECT name, dept, salary,
         ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
  FROM emp
) t
WHERE t.rn <= 3;

为什么不能把 rn <= 3 直接写进 WHERE？ 第二个原因才是关键：

1. 执行顺序：SQL 不是从上往下执行，实际是 FROM → WHERE → SELECT；WHERE 跑的时候"排第几"这一列还没算出来（WHERE 是第 ③ 步、SELECT 是第 ⑥ 步，见 18.2）。

2. 结果会错：排名必须看到同组的所有人，而 WHERE 是"先筛人再算"——第 3 个人早在排名之前就被筛掉了。

所以只能先算完、再套一层筛。括号里那段叫子查询（派生表）；8.0 也可以写成 CTE：WITH t AS (...) SELECT * FROM t WHERE rn <= 3。

18.6 连续登录：唯一一道"想通了就会"的题

难点：数据库不认识"连续"。所以要做的只有一件事——让连续的那几天落进同一组，剩下就是数行数。

技巧：日期 − 序号 = 定值。 先按用户、按日期编号（ROW_NUMBER()），断了也接着编、不重新从 1 开始：

小明  3/1  序号1  →  往前推 1 天  =  2/28  ┐
小明  3/2  序号2  →  往前推 2 天  =  2/28  ├ 连续三天 → 同一个值 ✅
小明  3/3  序号3  →  往前推 3 天  =  2/28  ┘
小明  3/5  序号4  →  往前推 4 天  =  3/1   ← 断了，值就变了

为什么成立：日期和序号同时 +1，差值就不变；中间断一天，日期跳 2 格而序号只跳 1 格，差值立刻变化。

GROUP BY user_id, DATE_SUB(login_date, INTERVAL rn DAY)  -- 这个差 = 分组键
HAVING COUNT(*) >= 3                                     -- 用 HAVING，不是 WHERE

18.7 排名三兄弟

都回答"我排第几"，只在遇到并列时分家。给分数 100, 100, 90, 80：

• ROW_NUMBER："排队报数 1、2、3、4，谁也不能重号。"（是序号，不是名次）

• RANK："体育比赛排名：两人并列第 1，'第 2 名'的位置被占了，下一个只能算第 3。" → 跳号

• DENSE_RANK："dense 就是密：名次挤在一起、中间不留空。" → 不跳

⚠️ 面试真正考的是「它决定你返回几行」：同一句"取前 2 名"，三个函数分别返回 2 行、2 行、3 行（DENSE_RANK 会把 90 也带上）。

⭐ 最实用的规则：

需求是"每组只取一条"  →  必须用 ROW_NUMBER（只有它保证每行号码都不同）
需求是"要带上并列的"  →  才用 RANK / DENSE_RANK

去重保留最新用同一模板：取 rn = 1，或 GROUP BY + MAX(id)；别用"先查重复 → DELETE → 再插入"（多步，中间失败就烂了）。

18.8 行转列 / 列转行 · 存在性与差集

• 行转列：SUM(CASE WHEN subject='语文' THEN score END) + GROUP BY name；列数是写死的（SQL 的列必须在写语句时就确定）——能主动说出这点是加分

• 列转行：UNION ALL；多行拼一列：GROUP_CONCAT

• 判断"有没有"：用 EXISTS（找到第一条就短路返回），别用 COUNT(*) > 0

• 求差集：NOT EXISTS 或 LEFT JOIN ... IS NULL；⚠️ 避开 NOT IN + NULL——会整体返回空结果（三值逻辑）

IN 和 EXISTS 的区别（上面只给了结论，这里展开）

一句话抓核心：IN 先算子查询、拿到一个"名单"再比对；EXISTS 拿外表的每行去问"有没有"、匹配到就停。

IN：      先跑子查询 → 得到名单 [1,1,2,1] → 再拿外表的行去名单里比对
          ⚠️ 不管外表几行，子查询那张表都要被完整扫一遍（名单必须先凑齐）

EXISTS：  拿外表的每一行，去子查询里问一句"有没有他的记录？"
          找到第一条立刻返回 true（短路），不关心查到几条 → 所以写 SELECT 1 就行

🔑 口诀：小表驱动大表。子查询结果集小 → 用 IN（先把小名单列出来，省事）；外表小 → 用 EXISTS（反正只跑几次，还能提前短路）。 ⚠️ "EXISTS 比 IN 快"是有前提的——内表关联列必须有索引，否则会慢得多。 💡 现代优化器会把很多 IN 子查询改写成半连接（semi-join），所以"哪个快"不能一概而论，最终看 EXPLAIN；但上面两条（小表驱动大表、NOT IN 的 NULL 坑）永远成立。

18.9 慢 SQL 改写 + 本节权重

子查询 → JOIN        OR → UNION ALL        深分页 → 游标（WHERE id > 上一页最后一个 id LIMIT 10）
函数不放在等号左边：DATE(create_time) = '2025-01-01'  →  create_time >= '2025-01-01' AND < '2025-01-02'
只查需要的列（别 SELECT *）

18.10 MySQL 常用函数（够用版）

别背函数表——只有日期函数和 CASE 是真会用到的（18.6 连续登录就靠日期函数），其余记住"函数别套在索引列上"就够了。

🔴 真正会被考的是这一条，比函数本身重要得多： 在索引列上套函数 → 索引失效（D4 那四类写法里的第一类）。所以

SQL

-- ❌ 用不上索引
WHERE DATE(created_at) = '2024-01-01'
-- ✅ 改写成范围比较，右边算成常量
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'

面试官问"你会哪些函数"，其实想看的是你知不知道这么用会毁索引。

📖 补覆盖：MySQL面试题 · SQL 题 —— 含"不存在 01 但存在 02"这类经典题的两种解法

附录 A｜小林 coding 文章索引（离线页码对照）

默认点链接（在线阅读，最新版）；右侧「页」是离线 PDF 的页码，供断网时翻阅。 离线资料在 ../查阅资料/小林coding图解网站合集PDF/，页码对应亮白版 v2.0。

A1 图解MySQL

本表只做「离线页码对照」——篇目的可点击链接见正文各节的 📖 补原理，这里不重复给。

A3 八股题库（阶段 ③ 用）

A4 最小必读集（时间不够时只读这些）

图解MySQL（8 篇）
  ① 执行一条 select 语句            (p6)    ← 全局地图，必先读
  ② 为什么用 B+ 树                  (p66)
  ③ 索引常见面试题                  (p36)
  ④ 索引失效                        (p89)
  ⑤ 事务隔离级别与 MVCC             (p121)
  ⑥ MySQL 有哪些锁                  (p143)
  ⑦ 三个日志                        (p234)
  ⑧ update 没加索引会锁全表         (p192)

A5 补充资料（稳定链接，优先小林 coding）

用途：小林讲原理，本册讲「该答什么」。先看本册结论，想深挖再点链接。 ⚠️ 原先挂在这里的两条对话分享链接已删除——分享类链接会失效，一律换成小林 coding 的稳定文章页。

附录 B｜口述验收清单（MySQL 册）

验收进度：B1 6 条 · B2 3 条 · B3 4 条 · B4 6 条 · B5 1 条，共 20 条。建议每天勾 5 条，四天过完 MySQL 册。

用法：合上本册，逐条用 30 秒讲一遍。讲不顺的打 ❌，回附录 A 找对应文章补。标准：讲得顺、有机制、有取舍 = 过；只会背结论 = 不算过。

📝 和练习题库的分工：这份清单是「快速自检」（30 秒一条，找薄弱点）；练习题库/ 是「动手作答 + 被批改」（写下来，能看出你到底缺哪块）。

B1 全局与索引（D1–D6，必过）　6 条

• D1 一条 select 走哪六站？为什么「表不存在」不是在解析器里查的？
• D2 为什么用 B+ 树而不是 B 树 / 哈希 / 红黑树？「树高 3~4 层」怎么来的？
• D3 插入一条数据，索引会发生哪些变化（页分裂 / 统计信息 / 删除的空间回收）？索引可以按哪几个维度分类？- [ ] D3 **索引可以按哪几个维度分类？**聚簇索引和二级索引的叶子各存什么？- [ ] D3 聚簇索引和二级索引的叶子各存什么？什么是回表、覆盖索引、索引下推？
• D4 最左前缀原则是什么？哈希索引适合什么场景？- [ ] D4 最左前缀原则是什么？索引失效有哪四类写法？范围条件为什么会截断后面的列？
• D5 **怎么查看一张表有哪些索引？Cardinality 有什么用？**explain 的阅读顺序？- [ ] D5 explain 的阅读顺序？type 有哪几档？Extra 里哪些是坏消息？
• D6 深分页为什么慢？游标分页和延迟关联各解决什么？COUNT(*) 为什么慢？COUNT(*)、COUNT(1)、COUNT(字段) 有什么区别、哪个快？

B2 存储引擎与内存（D7–D9）　3 条

• D7 InnoDB 和 MyISAM 差在哪四点？为什么现代业务默认 InnoDB？还知道哪些引擎（Memory / Archive）？为什么说 MySQL 是「可插拔」的？
• D8 InnoDB 读写的最小单位是什么？行溢出怎么处理？varchar(n) 的 n 受什么限制？
• D9 Buffer Pool 缓存的是什么？为什么它比 Query Cache 合理？改良 LRU 解决什么问题？

B3 事务与并发（D10–D13，必过）　4 条

• D10 **哪些业务场景不适合脏读，举例说明？**ACID 各靠什么实现？- [ ] D10 ACID 各靠什么实现？四档隔离级别各解决什么问题？InnoDB 在 RR 下怎么处理幻读？
• D11 MVCC 靠哪两个基础设施？RC 和 RR 最核心的差别是什么？
• D12 **InnoDB 的行锁锁的是「行」还是「索引」？**记录锁 / 间隙锁 / 临键锁各锁什么范围？为什么「update 没走索引会锁全表」？
• D13 三个日志各干什么？两阶段提交为什么必要？崩溃后怎么判定提交还是回滚？

B4 设计与运维（D14–D18）　6 条

• D14 主从复制的三步？主从延迟怎么应对？什么时候该分库分表、代价是什么？
• D15 **int(1) 和 int(10) 有什么不同？**SQL 和 NoSQL 的区别是什么？- [ ] D15 SQL 和 NoSQL 的区别是什么？该怎么选？（小林第 1 题，开场就考）VARCHAR(n) 的 n 是字符还是字节？金额为什么必须 DECIMAL？utf8 和 utf8mb4 差什么？生日该用哪个类型？utf8mb4 下索引最多能建多少个字符？防重复插入靠什么？NULL 为什么不能用 = 判断？索引该不该建看什么？主键为什么选自增？前缀索引的代价是什么？
• D16 三个范式各解决什么问题？反范式的代价是什么？建表规范能说几条？外键的三种级联行为？为什么不用物理外键？去掉外键后子表那一列的索引谁来建？（防重复与索引设计已并入 D15）
• D17 判断 DDL 会不会锁表看哪三个维度？在线变更工具的通用思路是什么？DELETE / TRUNCATE / DROP 有什么区别？为什么大表不能直接 DELETE 全表？
• D18 **IN 和 EXISTS 有什么区别、怎么选？**分组取 TopN、连续登录问题各用什么套路？- [ ] D18 分组取 TopN、连续登录问题各用什么套路？手写 SQL 的答题习惯是什么？四种连接各保留哪些行？常用函数知道哪些？为什么函数套在索引列上会失效、怎么改写？

B5 实战案例（1 条，最能加分）　1 条

🔧 这是本册唯一需要"动手产出"的一条——MySQL 册的价值一半在这。

• 能完整讲一个自己做的索引优化案例：背景 → explain 现状 → 第一次尝试失败 → 正确方案 → 复测数据 → 取舍说明

附录 C｜深挖细节（🔬 级，了解即可）

这一节是从 D15 正文搬出来的：C1 / C3 / C5 / C6 是实现细节（知道就行），C2 / C4 是必答结论（别跳过）。 为什么混在一起：C2 的「2038 问题」和 C4 的「utf8 陷阱」本身就嵌在那两段细节里，拆开会把一段话撕成两半，反而更难读。 什么时候读：正文学完了、想扛住追问时再翻。不要为了背细节而背——C1 / C3 / C5 / C6 被问到的概率远低于 C2 / C4。 对应正文：D15（数据类型与字符集）、D16（外键）。

C1　DECIMAL 到底怎么存的

先纠正一个常见误解：它不是"当字符串存"，而是用十进制的尺子去量。

【FLOAT：用 2 的幂做刻度】
0 ──1/2──1/4──1/8──1/16──1/32── ... 永远刻不完
        0.1 掉在两个刻度【中间】→ 只能取最接近的 → 近似值

【DECIMAL：用 10 的幂做刻度】
0 ──1/10──2/10──3/10── ...
       0.1 正好落在 1/10 上 → 精确

存储规则：每 9 个十进制数字压进 4 个字节。为什么是 9？因为 4 字节 = 2³² ≈ 4.29 × 10⁹ ≈ 10⁹。

整数部分和小数部分分开算，各按"剩余位数"查表：

例：DECIMAL(10,2) → 整数 8 位（落"7_{9"档 → 4 字节）+ 小数 2 位（落"1}2"档 → 1 字节）= 5 字节。

⚠️ 最容易搞错的一点：字节数由「声明的位数」决定，跟实际值无关。DECIMAL(10,2) 存 0.34 和存 12345678.34 都是 5 字节——整数段那 4 字节是预留的。 所以 VARCHAR 是变长、DECIMAL 是定长——这正是"定点数"里那个"定"。

顺手回答一个"为什么"：为什么 8 位数要 4 字节、2 位数只要 1 字节？因为那张表是「装下这么多十进制位，最少需要几个字节」算出来的——8 位数最大 99,999,999 需要 27 个二进制位（3 字节只有 24 位，不够），2 位数最大 99 只要 7 位（1 字节够）。

🎤 面试怎么答

金额必须用 DECIMAL：FLOAT/DOUBLE 是二进制浮点数，存的是近似值——十进制小数像 0.1 在二进制里是无限循环的，单笔看不出来，累加之后误差会显现，对账对不平。DECIMAL 是十进制定点数，按十进制位精确存储。更严格的做法是用「分」存整数，支付系统就是这么做的。 另外自增主键建议直接上 BIGINT：INT UNSIGNED 只到 42.9 亿，增长快的业务一年多就撞满，而改主键类型要重建整张表和所有索引。 浮点不是不能用，而是用在该用的地方：坐标、评分、监控指标、科学计算。

C2　时间类型：DATETIME vs TIMESTAMP（必答）

时区行为（一定要能说清）：

-- 会话时区 +08:00
INSERT INTO t (ts, dt) VALUES ('2024-01-01 12:00:00', '2024-01-01 12:00:00');
SET time_zone = '+00:00';
SELECT ts, dt FROM t;
-- ts（TIMESTAMP） → 2024-01-01 04:00:00   ← 换算成 UTC 了
-- dt（DATETIME）  → 2024-01-01 12:00:00   ← 纹丝不动

💡 TIMESTAMP 存的是「一个时间点」（北京的 12 点 = 伦敦的 4 点，同一个点）；DATETIME 存的是「一串数字」（12:00:00 就是 12:00:00，跟时区无关）。

2038 问题：TIMESTAMP 底层是 32 位有符号整数，上限 2³¹-1 = 2147483647 秒，对应 2038-01-19 03:14:07 UTC，过了就插入报错。这是全行业的问题（32 位 Unix 时间戳），不只是 MySQL。

C3　时间戳为什么是 32 位有符号

时间戳是什么：从 1970-01-01 00:00:00 UTC（纪元 / epoch）起过了多少秒的一个整数。1970 这个起点没有深奥理由，就是 Unix 诞生时的约定（Windows 从 1601 年算、NTP 从 1900 年算）。

为什么是 32 位：Unix 和 C 语言诞生在 32 位机器上，C 的 time_t 按机器字长定 → 一个整数就是 32 位。纯粹是"当时的机器这么宽"。

为什么是「有符号」（关键）：

无符号 32 位 → 0 ~ 4,294,967,295 → 能到 2106 年
有符号 32 位 → 上限砍半          → 只能到 2038 年   ← 为什么自找麻烦？

因为要用负数表示 1970 年以前的时刻（出生日期、历史事件）：-1 就是 1969-12-31 23:59:59。

为了能往回看，牺牲了往前的 68 年。

溢出为什么"变成负数"：因为有符号和无符号只是同一串 bit 的两种读法，靠最高位区分正负；而位宽固定，加法器只认 bit。

2147483647 = 0111 1111 ... 1111   ← 最高位 0，最后一个能表示的时刻
        +1 = 1000 0000 ... 0000   ← 最高位被顶成 1 → 按补码读 = -2³¹

于是时间从 2038-01-19 03:14:08 变成 1901-12-13 20:45:52

💡 为什么是 2³¹-1 而不是 2³²-1：32 位共 2³² 种组合，有符号要对半分：正数半边有 2³¹ 个座位，0 占掉一个，所以最大正数 = 2³¹-1。 这套读法叫补码（two's complement），选它的原因是加法和减法能共用同一个电路。

现代状态：操作系统层的 time_t 在 64 位系统上已经是 64 位；但 MySQL 的 TIMESTAMP 仍是 4 字节，所以 2038 问题依然存在。

⚠️ 用 BIGINT 存时间戳时，必须全系统统一秒 / 毫秒：Java 的 currentTimeMillis() 是毫秒、Python 的 time.time() 是秒——混用是经典线上事故。把单位写进字段名（created_at_ms）。

别漏了 DATE 和 YEAR（一个常见的选型失误）：

选型三问：

1. 这是「时间点」还是「日期」？→ 日期用 DATE

2. 可能落在 1970 年前 / 2038 年后吗？→ 会，就不能用 TIMESTAMP

3. 跨时区吗？数据库时区配置可信吗？→ 想省事用 TIMESTAMP（接受 2038）；想可控用 DATETIME + 应用层统一按 UTC 存

💡 业界实况：很多公司统一用 DATETIME，时区交给应用层（统一按 UTC 存、展示时转换），理由是可控、没有 2038、不受数据库时区配置影响——比省 1 个字节重要得多。另有一派用 BIGINT 存毫秒时间戳（Java 项目常见），代价是可读性差。

🎤 面试怎么答

DATETIME 存的是字面值，范围到 9999 年，不认时区，写什么读什么，占 5 字节； TIMESTAMP 存的是 UTC 时间戳，占 4 字节，读写时按会话时区自动换算，所以跨时区业务更省心——但它底层是 32 位整数，上限是 2038-01-19，这就是 2038 问题，而且是整个行业的问题。 选型上：跨时区、要绝对时间点用 TIMESTAMP；要存 2038 之后、或历史日期、或不想依赖数据库时区配置，用 DATETIME。现在很多公司干脆统一用 DATETIME，时区交给应用层处理。 顺带一个高频追问：生日要用 DATE——它是"日历上的一天"、不是时间点，而且 TIMESTAMP 的下限是 1970 年，老用户根本存不进去。

C4　字符集：utf8 是个陷阱（必答）

MySQL 里名字叫 utf8 的字符集，其实是最多只支持三个字节的 utf8mb3，存不下 emoji 和一部分生僻汉字（它们是 4 字节）。utf8mb4 才是完整的四字节实现——新建库表一律用它。

UTF-8 本身是变长编码（1~4 字节）：ASCII 1 字节、é 2 字节、中 3 字节、😀 和生僻字 4 字节。MySQL 当年只做到 3 字节就给它起名叫 utf8，于是留下了这个坑。

• MySQL 8.0 里 SHOW CREATE TABLE 会把 utf8 显示成 utf8mb3，该别名已废弃

• MySQL 8.0 起默认字符集从 latin1 改成了 utf8mb4（5.7 的默认还是 latin1，所以显式写出来是好习惯）

用 utf8 的三种后果（一种比一种糟）：

C5　排序规则（collation）：管"怎么比较"

💡 类比：字符集 = 这本字典能收录哪些字母；排序规则 = 查字典时的规矩（大小写算不算同一个词、é 和 e 算不算一样）。两者完全无关——这正是「能存 emoji」和「大小写是否敏感」被混为一谈的地方。

名字的读法：

utf8mb4_0900_ai_ci
   ↓       ↓   ↓  ↓
字符集  Unicode 9.0  不区分重音  不区分大小写

常用三个：utf8mb4_0900_ai_ci（8.0 默认，一般业务）、utf8mb4_unicode_ci（5.7 时代常见）、utf8mb4_bin（区分大小写——存 token、验证码、大小写敏感的编码时用）。

⚠️ _ci 带来的"意外"：WHERE nickname = 'abc' 会同时匹配 'ABC'、'Abc'。 要精确区分大小写又不想改表结构，可以在 SQL 里临时指定：

SQL

SELECT * FROM users WHERE nickname = 'abc' COLLATE utf8mb4_bin;
SELECT * FROM users WHERE nickname = BINARY 'abc';     -- 老写法

C5.1　最隐蔽的坑：字符集不统一 → 索引失效

决定比较方式的是「列自己的 collation」（SQL 里没显式指定时），不是某个数据库级开关——不同列可以有不同的 collation。

当两边的字符集或排序规则不一致时（比如 a.name 是 utf8mb4、b.name 是 utf8），连表查询必须先隐式转换：

一旦发生隐式字符集转换，被转换那一列的索引就用不上了，直接退化成全表扫描。它很隐蔽：SQL 没错、EXPLAIN 也不报错，只是慢——这是"索引失效"里最难查的一种。

所以四处必须统一：库 → 表 → 列 → 连接（character_set_client / connection / results）。

SHOW CREATE TABLE users;                  -- 看表/列的字符集与排序规则
SHOW VARIABLES LIKE 'character_set%';     -- 看连接层的各处设置

C6　字符集影响索引能建多长

字符集决定"每个字符最多占几字节"，而索引键上限是按字节算的——所以换个字符集，能建索引的字符数就变了：

💡 这一张表解释了三个"神秘数字"：

• VARCHAR(191) 满天飞 → 767 ÷ 4（老行格式 + utf8mb4）

• VARCHAR(255) 在 utf8 表里 → 767 ÷ 3

• 现在的上限 768 → 3072 ÷ 4 超过上限就得用前缀索引，否则报 ERROR 1071: Specified key was too long。

建库建表怎么写（可以直接抄）：

CREATE DATABASE app
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_0900_ai_ci;      -- 5.7 换成 utf8mb4_unicode_ci

CREATE TABLE users (
  id       BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nickname VARCHAR(32) NOT NULL   -- n 是字符数：utf8mb4 下最多 32 个 emoji
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci;

# my.cnf —— 服务端和客户端都要设
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_0900_ai_ci
[client]
default-character-set=utf8mb4

⚠️ 别漏了"连接"这一处：库对了、连接错了，照样出问号。JDBC / 驱动连接串也要指定 utf8mb4。

🎤 面试怎么答

MySQL 里名字叫 utf8 的字符集实际是 utf8mb3，最多只支持 3 个字节，存不下 emoji 和一部分生僻汉字（它们是 4 字节），插入会报 Incorrect string value，更糟的情况会被静默存成问号。utf8mb4 才是完整的 UTF-8 实现，MySQL 8.0 的默认字符集就是它，新建库表一律用它。 另外排序规则（collation） 决定字符串怎么比较和排序，后缀 _ci 不区分大小写、_bin 按二进制比较。 最关键的是：库、表、列、连接四处的字符集和排序规则必须统一——否则连表查询会发生隐式字符集转换，导致索引失效，这是"索引失效"里最隐蔽的一种。 顺便，换成 utf8mb4 后每个字符最多占 4 字节，能建索引的字符数会变少（老行格式下从 255 变 191）。

附录 D｜本册与其他册的关系

推荐流程：读本册（讲解）→ 用附录 B 自测 → 讲不顺的去附录 A 找文章补 → 接着读 18b（Redis） → 面试前翻 02/11 的话术版。

最后一句话：数据库是**唯一一门"必须动手才能学会"**的基础课。 D1–D13 靠读和讲能过，但 D5（explain）、D6（深分页）、D14–D18 必须自己在真实表上跑一遍——没有那个百万行表的优化案例，面试官一问"你实际优化过什么"就答不上。