第三章数据库（MySQL）场景（第51-78题）｜场景题题库 · 282 题

第三章数据库（MySQL）场景（第51-78题）

51. 监控告警“某条 SQL 平均耗时 3s”，用户操作卡顿（慢 SQL 排查）

【考察内容】慢 SQL 排查是数据库类最高频场景题

【题目】监控平台告警：某条 SQL 平均耗时 3 秒，关联的页面操作明显卡顿。你如何从现象确认、执行计划分析、索引与表结构检查、优化验证一步步把它解决？

【参考答案】慢 SQL 平均 3s：按“确认 → 定位 → 处置 → 预防”：

容量估算（步进）：假设该接口 QPS=200，RT=3s，则占用并发连接/应用线程 ≈ 200×3=600，远超连接池常见 50–200，会拖垮同实例其他接口。优化目标：P99 压到 100ms 内，相同 QPS 下连接占用降 30 倍。
定位：① 慢日志看扫描行数、是否全表扫、锁等待；② EXPLAIN 看 type、key、rows、Extra（Using filesort/temporary）；③ SHOW PROCESSLIST 看是否长期 Sending data/locked；④ 看表量、索引、是否隐式转换、函数包字段、%like、回表过多。
常见根因与处置：无索引/索引失效 → 补索引或改写 SQL；大分页 → 延迟关联；锁等待 → 缩短事务、调隔离级别、拆事务；服务器资源 → 从库/扩容是次优。
失败与降级：紧急先限流该接口/降级返回缓存；上线新索引低峰进行、监控磁盘与主从延迟；改写 SQL 要灰度。
预防：SQL 审核、慢查询大盘、行数扫描告警（如扫描 >10 万行）。

【原理溯源】

为什么必须先抓 SQL 再谈优化？ 慢 SQL 的根因可能在「没走索引」「锁等待」「排序落盘」「回表过多」「深分页」等完全不同路径上。跳过定位直接「加索引」，相当于没诊断就开药——大概率无效，还可能因索引过多拖慢写入。慢日志（long_query_time）和 APM 是唯二可靠的「现象→SQL 文本」入口。
为什么 EXPLAIN 是核心工具？ 优化器在真正执行前会生成执行计划；EXPLAIN 把计划暴露出来，让你不用猜。type 反映访问路径（ALL 全表 → range 范围 → ref 等值 → const 主键），rows 是预估扫描行数，Extra 里 Using filesort 表示额外排序（索引有序性没用上）、Using temporary 表示中间结果落临时表——两者都是 RT 劣化信号。
为什么「扫得多」比「算得多」更致命？ InnoDB 的瓶颈在磁盘 IO 与缓冲池命中。扫描行数上升意味着更多页读取：命中 buffer pool 则耗 CPU+内存带宽，未命中则直接吃磁盘随机读。3s 量级的 SQL 通常对应百万级行扫描或严重锁等待，而不是「SQL 写得不够巧」。
为什么锁等待也会表现为「慢 SQL」？ 事务持有行锁期间，其他事务的同键 UPDATE/SELECT FOR UPDATE 会阻塞。SQL 本身执行计划没问题，却因等锁耗时数秒。所以必须用 SHOW PROCESSLIST / innodb_trx 区分「算得慢」和「等得慢」——前者改索引，后者改事务模型。
为什么验证必须对比 EXPLAIN？ 「上线后好像快了」不可靠。优化前后对比 type/rows/Extra，以及慢日志 RT 曲线，才能确认根因被消除而不是碰巧流量下降。

【选型判断树】

拿到一条 3s 的慢 SQL，先分型再动手：
├─ type=ALL 或 key=NULL
│   ├─ 写法触发失效（函数/隐式转换/%前导/LIKE 负向）→ 改 SQL 写法（第 52 题）
│   ├─ 缺合适索引 → 按 WHERE+ORDER BY 设计联合索引（第 54/77 题）
│   └─ 优化器主动放弃（小表/低选择性）→ FORCE INDEX 或 ANALYZE TABLE
├─ 走了索引但仍慢
│   ├─ rows 很大 → 范围太宽 / 回表太多 → 覆盖索引或收紧条件（第 76 题）
│   ├─ Extra 有 filesort/temporary → 把排序列纳入联合索引
│   └─ LIMIT offset 很大 → 深分页改游标（第 56 题）
├─ Waiting for lock / 状态为锁等待
│   → 看死锁与长事务，统一加锁顺序、缩短事务（第 60/62 题）
└─ SELECT * 拉大字段 → 只取必要列，大 text/blob 拆表

判断口诀： 先看走没走索引，再看扫了多少行，最后看有没有额外排序或锁等待。

【口述骨架】（5 分钟）

时间	任务	内容
0:00–0:30	定性	「这是慢 SQL 排查闭环题：定位 → EXPLAIN → 分根因 → 优化 → 验证」
0:30–1:20	定位入口	慢日志 `long_query_time` / APM 拿到 SQL 文本，先确认真实 RT
1:20–2:30	EXPLAIN 四看	type / key / rows / Extra，重点点名 filesort 与 temporary
2:30–3:40	根因五类	失效、扫太多、锁等待、深分页、大字段——每类一句对应药方
3:40–4:30	验证闭环	优化前后 EXPLAIN 对比 + 慢日志 RT 曲线，确认不是流量假象
4:30–5:00	收尾	「一句话：先用慢日志钉住 SQL，再用 EXPLAIN 定性质，最后用指标证明治好了」

【关键数字】

参数	经验值	说明
`long_query_time`	1s（可压到 0.1–0.5s 抓更多）	生产常用 1s，压测可更低
`type` 优劣序	system/const/eq_ref/ref/range/index/ALL	看到 ALL/range-over-large 要警惕
可接受 rows（点查）	数十～数百	超过万级要解释原因
可接受 rows（列表）	千级～万级（有分页）	与业务 QPS 一起看
filesort 成本	排序集超 `sort_buffer_size` 会落盘	大结果集排序是秒级元凶
锁等待超时	`innodb_lock_wait_timeout` 默认 50s	生产常调到 3–10s 快速失败
优化验证窗口	上线后观察 15–30min 慢日志	对比优化前后 P99
单表关注点	千万行级开始评估	非绝对魔数，看 RT/扫描/备份
连接池全局预算	实例数×池 max ≤ max_connections×0.8	预留运维连接
慢查询目标	扫描行数 < 1–5 万（在线）	返回少扫描多必优化

【追问链】（三层）

L1｜「EXPLAIN 里 Using filesort 说明什么？怎么消掉？」 → 说明排序没有利用索引的有序性，MySQL 要额外做一次排序（可能落盘）。消法：把 ORDER BY 列纳入联合索引，且满足「等值条件在前、排序列在后」；或 LIMIT 很小且排序集可控时接受它。

L2｜「索引也走了，rows 也不大，为什么还是 3s？」 → 大概率不是「扫得多」而是「等得久」：检查是否锁等待（innodb_trx、performance_schema）、是否回表次数极多（覆盖索引可消）、是否网络/序列化把大字段拉回应用。也可能是 buffer pool 命中率低导致冷页读盘。

L3｜「优化上线后 P50 变快了，P99 还是 3s，你怎么继续查？」 → 分位拆解：P99 慢通常来自尾部场景——偶发锁冲突、冷数据页、特定大商户、深分页、缓存未命中放大。用慢日志+链路追踪把 P99 样本捞出来单独 EXPLAIN，往往发现是另一类 SQL 或并发问题，而不是主路径没优化好。

【评分标准】

档位	答案特征
60 分	能说出「看慢日志 + EXPLAIN + 加索引」，知道 type/rows 大致含义
80 分	完整闭环：定位→EXPLAIN 四字段→按根因分类给药方→验证；能解释 filesort
95 分	区分「算得慢/等得慢」；主动提覆盖索引与深分页；强调优化前后指标对比；能答 P99 尾部与锁等待排查

【关联题】

同一知识簇： 第 52 题（索引失效）→ 第 54 题（给 SQL 做优化）→ 第 76 题（覆盖索引）→ 第 77 题（最左前缀）→ 第 56 题（深分页）
锁与事务： 第 60 题（死锁）、第 62 题（大事务）
容量与架构： 第 70 题（DB CPU）、第 74 题（单表容量）

【自测】

EXPLAIN 显示 type=ALL, rows=5000000, Extra=NULL，你下一步做什么？ 参考答案：确认 WHERE 是否可建索引/是否写法失效；设计覆盖等值与排序的联合索引；上线前后对比 type 与 rows。
判断对错：只要加上索引，慢 SQL 问题就一定解决。 参考答案：错。锁等待、大字段传输、深分页、优化器放弃索引等情况加索引无效，必须先分根因。
Extra 同时出现 Using filesort 和 Using temporary，通常意味着什么？ 参考答案：查询既需要中间结果落临时表又需要额外排序，常见于无索引支撑的 GROUP BY + ORDER BY 或大结果集聚合，优先补联合索引。

52. 明明建了索引，查询还是全表扫描（索引失效排查）

【考察内容】索引失效是数据库面试必考题，考察对 B+ 树索引机制的理解深度

【题目】开发反馈：表上明明建了索引，但 EXPLAIN 显示查询仍在全表扫描。可能是什么原因造成的索引失效？请列出你能想到的所有场景（写法、类型、函数、隐式转换等），并给出验证方法。

【参考答案】建了索引却全表扫描：查失效条件：

常见失效场景：① 对索引列用函数/运算（WHERE DATE(t)=...）；② 隐式类型转换（varchar 列传数字）；③ 前导模糊 LIKE '%x'；④ 联合索引不满足最左前缀；⑤ 优化器判断回表太多，不如全表（区分度低）；⑥ OR 连接非索引列；⑦ 数据量太小优化器选全表。
排查步进：EXPLAIN 的 possible_keys/key、type=ALL、rows；FORCE INDEX 试验是否收益；ANALYZE TABLE 更新统计信息；看列类型与字符集是否一致。
容量视角：表 5000 万行，全表扫可能秒～分钟级；索引等值查询目标 几十 ms。区分度：性别之类字段区分度低，单独索引往往没用。
修复：改写 SQL 保证 sargable（可搜索）、调整索引顺序、覆盖索引减少回表、必要时强制索引并评估数据分布变化。
失败与降级：紧急限流；新索引上线后观察命中；DDL 用 online 算法（INPLACE），期间允许并发 DML；但它起止仍要拿 MDL 排他锁——有长事务持表时 DDL 排队、会连带把后续所有查询堵死（「加索引把库锁死」的常见真因）。执行前先查 information_schema.innodb_trx 确认无长事务、把 lock_wait_timeout 调小，超大表用 gh-ost/pt-osc。

【原理溯源】

索引为什么能加速？ InnoDB 二级索引是 B+ 树：按索引列值有序排列，查询可用二分/顺序定位把「全表扫描」压成「少量页读取」。一切失效场景的共同本质都是：优化器无法把 WHERE 条件映射成 B+ 树上的连续区间，于是退回全表扫描。
为什么隐式类型转换会让索引失效？ MySQL 遇到 varchar 列 = 数字 时，会对列做转换（相当于对索引列套函数），破坏了「列值 ↔ 树上位置」的有序对应。等价于 CAST(phone AS signed) = 138...，索引列被包进函数后无法走树。
为什么 LIKE '%abc' 不能走索引，而 'abc%' 可以？ B+ 树按前缀字典序组织。尾随通配（abc%，% 在末尾）前缀确定，起点和终点可在树上定位成一个 range；前置通配（%abc，% 在开头）则是「结尾匹配」，在有序结构上没有单调性，无法收缩搜索区间。
为什么联合索引有最左前缀？ 联合索引 (a,b,c) 的有序性是「先按 a，a 相同再按 b，再按 c」。只给 WHERE b=2 时，b 在树上不是全局有序的（a 不同的段落里 b 重复交错），无法二分定位。跳过 a 只用 c 同理。
为什么 OR 连接非索引列可能导致全表？ 优化器可以对 OR 做 index merge，但若一侧无索引，合并收益消失，直接全表往往更便宜。解法是两侧都有索引，或改写成 UNION ALL 让每支各自走索引。
为什么负向条件常失效？ != / NOT IN 在 B+ 树上是「几乎全部区间」——排除一点等于扫其余所有，范围不收敛，优化器判断不如全表顺序扫（还能用顺序 IO）。这不是绝对禁止，而是选择性差时的代价判断。
为什么优化器会主动放弃索引？ 优化器按代价模型选计划。当预估「回表+随机 IO」成本高于「顺序全表扫」时会弃用——典型是小表、低区分度列、或要查的行占比过高（>20%–30%）。这是正确行为，不是 bug。

【选型判断树】

EXPLAIN key=NULL 或失效，按写法归类：
├─ 条件类型/值不匹配
│   └─ 字符串列用数字查 → 列侧被隐式转换、索引失效；数字列用字符串查 → 转换发生在常量侧，索引仍可用（类型仍应统一，但不是失效原因）
├─ 索引列被包住
│   ├─ 套函数 DATE(col)、YEAR(col) → 改成范围：col >= '2026-09-11' AND col < '2026-09-12'
│   └─ 套运算 col+1=30 → 改成 col=29
├─ 匹配模式破坏有序
│   ├─ LIKE '%xx' → 倒排/全文/ES，或改前缀匹配
│   └─ 负向 != NOT IN → 评估选择性；必要时改成正向枚举或标记位
├─ 联合索引结构不匹配
│   ├─ 跳最左列 → 补齐最左列或重建索引顺序（第 77 题）
│   └─ 排序列与索引序不一致 → 调整联合索引列序
├─ OR / 非索引列
│   └─ 补索引或改 UNION ALL
└─ 优化器主动放弃
    ├─ 统计信息旧 → ANALYZE TABLE
    ├─ 确要强制 → FORCE INDEX（慎用）
    └─ 真的是小表/低选择性 → 接受全表，不必硬走索引

判断口诀： 先看列有没有被包住，再看最左前缀有没有断，最后看优化器是不是算过了。

【口述骨架】（5 分钟）

时间	任务	内容
0:00–0:30	定性	「索引失效的本质是：条件无法映射成 B+ 树上的可定位区间」
0:30–2:00	分类列举	类型转换、函数运算、前导 %、最左前缀、OR、负向、优化器放弃——每类一句机理
2:00–3:00	深讲 1–2 个	隐式转换为什么等于套函数；`%abc` 为什么树上无法定位
3:00–4:00	验证与处置	EXPLAIN 看 key/type；改写 SQL / 调索引序 / ANALYZE / 谨慎 FORCE INDEX
4:00–5:00	收尾	「不是所有情况都该走索引——优化器放弃有时是对的」

【关键数字】

参数	经验值	说明
优化器倾向全表阈值	预估访问行占比约 20%–30%+	与统计信息、IO 模型相关，非硬编码
隐式类型转换何时失效	仅「字符串列 = 数字」触发列侧转换；「数字列 = 字符串」转换在常量侧，索引仍走	开发期最高频失效原因
LIKE 走索引条件	前缀确定（`abc%`，尾随通配）	前置通配（`%abc`）一般不走
联合索引列数	常 2–4 列	过宽索引写放大
选择性经验	区分度 >10%–20% 再建单列索引	sex 之类低区分度不建
FORCE INDEX	应急手段	升级/统计变化后可能变差
单表关注点	千万行级开始评估	非绝对魔数，看 RT/扫描/备份
连接池全局预算	实例数×池 max ≤ max_connections×0.8	预留运维连接
慢查询目标	扫描行数 < 1–5 万（在线）	返回少扫描多必优化

【追问链】（三层）

L1｜「为什么 WHERE b=2 用不上 (a,b,c) 索引？」 → 联合索引先按 a 排序；a 不同时 b 交错出现，b 在整棵树上不全局有序，无法二分定位。必须从最左列 a 开始连续匹配。

L2｜「WHERE a=1 AND c=3 能用 (a,b,c) 吗？用到什么程度？」 → 能用到 a 做 range/ref 定位；c 无法在树上继续收缩（中间缺 b）。若 c 选择性高且需免回表，可考虑 ICP（索引下推）在引擎层过滤，或重建 (a,c) 联合索引。

L3｜「统计信息不准导致优化器选错计划，你怎么处理？」 → 先 ANALYZE TABLE 刷新统计信息；仍偏差大时，检查直方图/持久化统计、是否 innodb_stats_method 干扰。应急可 FORCE INDEX，但要埋点观察，避免统计恢复后计划僵化。根治是让索引和 SQL 匹配真实访问模式。

【评分标准】

档位	答案特征
60 分	能背出函数、%前导、类型转换、最左前缀等 4–5 条场景
80 分	按「类型/函数/匹配/结构/优化器」分类，并能解释 1–2 条底层原因
95 分	用 B+ 树有序性统一解释各类失效；指出优化器主动放弃是合理行为；给出验证与 FORCE INDEX/ANALYZE 的正确使用边界

【关联题】

同一知识簇： 第 53 题（B+ 树原理）→ 第 77 题（最左前缀）→ 第 76 题（覆盖索引）→ 第 54 题（实战优化）
上手排查： 第 51 题（慢 SQL 闭环）
对比： 第 74 题（单表容量——「扫得多」的另一面）

【自测】

索引列 phone 是 varchar，SQL 写成 WHERE phone = 13800138000（无引号），会走索引吗？为什么？ 参考答案：通常不走。数字与字符串比较触发对 phone 列的隐式转换，等价于套函数，破坏 B+ 树有序定位。
WHERE name LIKE '%张' 与 WHERE name LIKE '张%' 在索引利用上有何差别？ 参考答案：前者 % 在开头（前置通配，等于「后缀匹配」），有序树上没有单调区间，一般全表；后者前缀确定（张%，尾随通配），可走 range。
小表（几千行）EXPLAIN 显示 ALL，需要强行建索引吗？ 参考答案：通常不必。全表顺序扫成本极低，优化器放弃索引是正确代价判断。

53. 订单表 5000 万行，主键范围查询越来越慢（B+ 树原理）

【考察内容】B+ 树是数据库原理第一考点

【题目】订单表已经 5000 万行，按主键做范围查询（id BETWEEN 100000 AND 200000）越来越慢。DBA 说换索引结构也解决不了，因为 MySQL 选的就是 B+ 树。请解释：为什么 MySQL 索引选 B+ 树而不是 B 树、哈希表或红黑树？各自差在哪？

【参考答案】5000 万行主键范围查询变慢：理解 B+ 树与数据局部性：

原理（步进）：InnoDB B+ 树主键索引，5000 万行、树高通常 3–4 层，等值点查稳定；但“范围扫描”代价取决于要顺序读多少叶子页与行宽——主键范围扫聚簇索引，叶子即整行，不存在回表；只有走二级索引做范围才要逐行回表，那时才是随机 IO。Buffer Pool 若装不下热数据，命中率下降，磁盘 IO 成为瓶颈。
为何越来越慢：题干区间固定（id BETWEEN 100000 AND 200000，恒约 10 万行），所以「越来越慢」不能用「扫描行数变大」解释；真因是：数据涨后热页超出 Buffer Pool 使命中率下降（同一批行要付更多磁盘 IO）、页分裂与碎片使同样行数为读更多页、统计信息失真致执行计划变差、以及树高从 3 层长到 4 层（每次下降多一跳 IO，但量级不足以单独解释秒级劣化）。
优化：① 范围分页用“游标/上次主键”避免 OFFSET；② 缩小扫描区间、强制使用更合适的索引；③ 冷热分离归档，主表保持在较瘦体量；④ 分库分表按主键/时间；⑤ 扩内存提升 Buffer Pool 命中率。
容量估算：单表经验值：行很小的表可到上亿，行较大的订单类建议 千万级 规划归档/分片；5000 万是“需要认真评估”的节点，不是魔法阈值。
失败与降级：历史查询走归档库/离线；在线接口限制扫描行数（LIMIT+应用层校验）。

【原理溯源】

为什么一切要从「磁盘 IO 次数」讲起？ 内存访问是纳秒级，磁盘随机 IO 是毫秒级（SSD 也差几个数量级）。磁盘数据库索引的第一目标不是「比较次数少」，而是「读盘次数少且可预测」。树高等价于点查的 IO 次数——这是选型的总纲。
为什么 B+ 树比红黑树矮得多？ 红黑树是二叉：每层 2 个分支，千万级数据树高约 log₂N ≈ 20+。B+ 树一个节点一页（16KB），能放上千个 key+指针，分支数上千，树高 ≈ log₁₀₀₀N，千万级只要 3 层。同样数据量，IO 从 20+ 次压到 3–4 次。
为什么 B+ 树比 B 树更合适范围查询？ B 树的数据分散在非叶和叶子，范围扫描要中序遍历整棵树，随机跳；B+ 树数据全在叶子，叶子间双向链表，范围查询定位起点后可顺序读。且非叶只存 key，一页能放更多分支，树更矮；点查路径都到叶子，延迟稳定。
为什么哈希索引扛不住订单范围查？ 哈希把 key 散列到桶，等值 O(1)，但桶内/桶间无序，BETWEEN 必须全表或全索引扫。InnoDB 的自适应哈希（AHI）只加速等值热点，不能替代二级索引做范围。
为什么「5000 万行主键范围查变慢」往往不是因为 B+ 树不够好？ 主键范围本身 3–4 层就能定位；变慢通常来自：①范围跨度大导致叶子顺序读的页数多；②冷数据不在 buffer pool，读盘；③行宽大、页内行数少；④服务器 IO 饱和。换「更好的索引结构」解决不了这些——该归档/分表/加缓存，而不是魔改树结构。

【选型判断树】

要为磁盘数据库选索引结构，先问访问模式：
├─ 只要等值点查（如 KV 缓存、会话）
│   └─ 哈希：O(1)，但无序、无范围
├─ 要范围查询 / 排序 / 前缀
│   └─ B+ 树：叶子有序链表 + 矮树，磁盘 IO 友好（MySQL/PG 默认）
├─ 纯内存、要范围且实现简单
│   └─ 跳表（Redis ZSet）：内存指针便宜，无需页对齐
└─ 写入吞吐极大、可接受后台合并与读放大
    └─ LSM-Tree：顺序写 + 合并（RocksDB/HBase），点查可能多层

判断口诀： 磁盘看 IO 次数，范围看叶子是否有序，写入吞吐看 LSM。

【口述骨架】（5 分钟）

时间	任务	内容
0:00–0:30	定性	「核心视角是磁盘 IO：树高=点查 IO 次数，叶子有序=范围查询能力」
0:30–1:40	算清 B+ 树	16KB 页、扇出上千、千万级 3 层；对比红黑树 20+ 层
1:40–3:00	对比其他结构	哈希无范围；B 树数据分散+范围乱跳；跳表适合内存
3:00–4:00	回到本题	5000 万变慢的真因是热页超出 Buffer Pool 命中率下降＋页分裂碎片使同样行数为读更多页＋统计信息失真（题干区间固定，不是扫描行数变大），不是树结构本身
4:00–5:00	收尾	「B+ 树是磁盘上「矮 + 有序叶子」的最优工程折中」

【关键数字】

参数	经验值	说明
InnoDB 页大小	16KB	可配置，生产默认
单页指针数（估算）	约 1000+（取决于 key 宽度）	扇出大 → 树矮
3 层 B+ 树容量	约千万级～2000 万+ 行	与行宽、页填充率相关
5000 万行树高	通常仍 3–4 层	不是「树高爆了」
红黑树千万级树高	20+	每层一次 IO 不可接受
等值哈希	O(1)	无范围能力
单表关注点	千万行级开始评估	非绝对魔数，看 RT/扫描/备份
连接池全局预算	实例数×池 max ≤ max_connections×0.8	预留运维连接
慢查询目标	扫描行数 < 1–5 万（在线）	返回少扫描多必优化

【追问链】（三层）

L1｜「为什么叶子节点要用链表串起来？」 → 范围查询和排序扫描：在叶子定位起点后，沿链表顺序读即可，不必反复回到根/父节点。这是 B+ 树相对 B 树在范围场景的关键优势。

L2｜「页大小改成 32KB 会怎样？」 → 单页能放更多 key，树更矮，点查 IO 次数可能更少；代价是单次 IO 读放大、内存利用率与缓存粒度变粗，写入时页分裂搬运更多数据。要压测，不是越大越好。

L3｜「订单表 5000 万主键范围查慢，你不改树结构，实际怎么治？」 → ①收窄范围或改游标分页，避免一次读太多叶子页；②保证热数据在 buffer pool，必要时加大；③冷数据归档（第 65 题）；④按业务分表/分库（第 55 题）；⑤分析查询是否真的需要这么多行（业务侧分页/异步导出）。DBA 说得对：换索引结构不是药方。

【评分标准】

档位	答案特征
60 分	能说出「B+ 树比 B 树矮、比哈希支持范围」
80 分	用磁盘 IO 统一解释：树高、扇出、叶子有序链表；能对比红黑树/哈希/B 树
95 分	算出 3 层约千万级；指出本题变慢真因常非树结构；能讨论页大小权衡与 LSM 适用边界

【关联题】

同一知识簇： 第 52 题（索引失效）→ 第 74 题（单表容量估算）→ 第 77 题（最左前缀）→ 第 76 题（覆盖/回表）
架构延伸： 第 55 题（分库分表）、第 65 题（数据归档）
对比： Redis 跳表（第 31/32 题数据结构）

【自测】

判断对错：订单 5000 万行变慢，说明 B+ 树已经撑不住了，必须换索引结构。参考答案：错。3–4 层 B+ 树可容纳千万级；变慢多因缓存命中率下降、页分裂与碎片、统计信息失真、行宽或并发（区间固定时「范围页数变大」这一项不成立），应归档/分表/优化查询。
为什么哈希索引不适合订单表主键范围查询？ 参考答案：哈希桶无序，BETWEEN 无法收缩区间，只能扫全部相关桶或全表；B+ 树叶子有序可顺序读。
B+ 树相对 B 树，对范围查询友好的两个结构原因是什么？ 参考答案：①数据全在叶子，非叶只存 key，树更矮且路径稳定；②叶子双向链表，定位后可顺序扫描。

54. 一条 3s 的订单查询，EXPLAIN 后怎么下手（慢查询实战）

【考察内容】索引设计与 SQL 优化实战能力

【题目】线上订单查询很慢（3s+）：SELECT * FROM orders WHERE user_id=123 AND status=1 ORDER BY create_time DESC LIMIT 20。请给出完整的优化思路：从索引设计、回表、排序、覆盖索引、分页等角度说明你每一步看什么、改什么。

【参考答案】3s 订单查询 EXPLAIN 后如何下手：

实操顺序：看 type（ALL/index/range/ref/eq_ref）→ key 是否预期 → rows 估算扫描量 → Extra 是否 filesort/temporary/using index → 结合慢日志看“发送行数 vs 返回行数”。
容量估算（步进）：假设订单表 2 亿行，EXPLAIN rows=5000 万，即使单行很快，全量扫也可能秒级；目标扫描行数 <1–5 万（按页大小与过滤率）。若返回 20 行却扫描 5000 万，100% 索引/条件问题。
常见病与药：无索引 → 建；索引失效 → 改写；深分页 → 延迟关联/游标；ORDER BY 无索引 → filesort，补索引；多表 join 驱动表选错 → 小表驱动；COUNT(*) 大范围 → 维护计数表/缓存。
失败与降级：立刻限流该查询、结果缓存；DDL 低峰 online；业务可先只查近 3 个月+归档。
验证：改完再 EXPLAIN + 真实数据压测，关注 rows 与 P99，不只看优化器说用了索引。

【原理溯源】

为什么联合索引要「等值在前、排序在后」？ B+ 树先按第一列有序，第一列相同时第二列有序……user_id、status 等值匹配后，create_time 在该前缀段内天然有序，ORDER BY 可直接顺序读叶子，无需 filesort。若排序列放前面而等值列在后，等值条件收敛不了区间（要扫完整个索引再逐行过滤）；但排序仍可借索引序免掉 filesort（见下面 filesort 一条里的倒序扫描），代价在扫描量而不在排序本身。
为什么要避免 SELECT *？ 二级索引叶子只存索引列+主键；要取其他列必须回表（按主键再查聚簇索引），每次回表是额外页读取。SELECT * 常把大 JSON/text 也拉出来，放大 IO 与网络。只取必要列，有机会让「需要的列 ⊆ 索引列」从而覆盖（Using index，零回表）。
什么是 filesort，为什么联合索引能消掉？ filesort 是优化器在执行层额外做的排序（可能落盘）。当 ORDER BY 列序与索引一致且方向可匹配时，读索引即有序，直接产出结果。DESC 可倒序扫描索引叶子，同样免排。
为什么这题优先建 (user_id, status, create_time) 而不是 (status, create_time)？ 订单查询最高频路径是「某个用户的历史订单」。user_id 选择性极高，能把扫描压到该用户的数据段；status 选择性低，单独前置几乎不收敛。跟着真实访问路径设计索引，而不是按字段「看起来重要」排序。
为什么深分页仍可能拖垮这条 SQL？ 本题 LIMIT 20 且有 user_id 前缀时通常还好；但若业务改成「翻很多页」或条件变宽，offset 扫描丢弃问题会出现。优化要预留游标分页改造，不能假设永远第一页。

【选型判断树】

对这条 SQL 逐步决策：
├─ 1) EXPLAIN：key 是否为联合索引？Extra 是否 filesort？
│   ├─ filesort 在 → 索引缺排序列或列序不对 → 建 (user_id, status, create_time)
│   └─ 无索引/ALL → 先补最左 user_id
├─ 2) 是否回表过多？
│   ├─ 只要 id/状态/时间等少数列 → 调整 SELECT + 索引列覆盖
│   └─ 确需多列大字段 → 接受回表，但避免 SELECT *
├─ 3) 是否深分页？
│   ├─ 否（首页/浅页）→ 现方案即可
│   └─ 是 → 游标：WHERE ... AND (create_time,id) < 上一页末尾
└─ 4) 单用户订单仍极多？
    └─ 冷数据归档或按 user_id 分表（第 55/65 题）

判断口诀： 先定索引列序，再抠回表，最后治分页。

【口述骨架】（5 分钟）

时间	任务	内容
0:00–0:30	定性	「典型列表查询优化：等值收敛 + 索引有序排序 + 控制回表」
0:30–1:30	读 SQL 拆条件	user_id/status 等值，create_time 排序，LIMIT 20
1:30–2:40	给索引	(user_id, status, create_time)，解释为何不是 (status, create_time)
2:40–3:40	抠回表	避免 SELECT *；需要时覆盖索引 Using index
3:40–4:30	分页与体量	深分页改游标；数据量大再分表/归档
4:30–5:00	收尾	「索引跟查询路径走：等值在前、排序在后、覆盖能消就消」

【关键数字】

参数	经验值	说明
LIMIT 本题	20	浅页，重点在索引与 filesort
联合索引列序	(user_id, status, create_time)	等值+排序
user_id 选择性	极高（单用户订单有限）	收敛主因
status 选择性	低（常个位数枚举）	不宜单独前置
回表成本	每行约 1 次额外主键查找	大行数时线性放大
游标分页键	(create_time, id) 复合	防 create_time 并列漏行
单表关注点	千万行级开始评估	非绝对魔数，看 RT/扫描/备份
连接池全局预算	实例数×池 max ≤ max_connections×0.8	预留运维连接
慢查询目标	扫描行数 < 1–5 万（在线）	返回少扫描多必优化

【追问链】（三层）

L1｜「为什么排序字段要放进索引里？」 → B+ 树叶子在等值前缀下按后续列有序，ORDER BY 可顺序读，消除 filesort（可能落盘的额外排序）。

L2｜「SELECT * 在这条 SQL 里有什么具体代价？」 → 无法覆盖，必须逐行回表取全部列；若含大字段，还放大 buffer pool 污染与网络传输。改成明确列清单后，有机会让 Extra 出现 Using index。

L3｜「create_time 有重复值，游标分页为什么会漏/重？怎么修？」 → 仅用 create_time < last_time 会跳过同秒并列行或重复包含。改为复合游标：(create_time, id) < (last_time, last_id)，利用 id 唯一性定全序（第 56 题）。

【评分标准】

档位	答案特征
60 分	知道建联合索引，但列序或排序关系说不清
80 分	正确给出 (user_id, status, create_time)，解释免 filesort；提到避免 SELECT *
95 分	讲清等值在前排序在后、覆盖索引与回表、深分页复合游标、以及为何不选 (status, create_time)

【关联题】

同一知识簇： 第 51 题（慢 SQL）、第 52 题（失效）、第 76 题（覆盖）、第 77 题（最左前缀）、第 56 题（深分页）
体量延伸： 第 55 题（分表）、第 65 题（归档）、第 74 题（容量）

【自测】

若 SQL 改为 WHERE user_id=123 ORDER BY create_time DESC LIMIT 20，索引应如何调整？ 参考答案：可用 (user_id, create_time)；status 等值条件没了就不必硬塞进索引，避免多余写放大。
覆盖索引的 EXPLAIN 标志是什么？ 参考答案：Extra 显示 Using index，表示所需列全在索引中，无需回表。
判断对错：只要建了 (user_id, status, create_time)，任何分页都不会慢。 参考答案：错。深分页 offset 仍会扫描丢弃前序行，需游标分页。

55. 订单表数据量爆炸，单库单表要撑不住了（分库分表）

【考察内容】分库分表是数据库架构第一高频题

【题目】订单表数据量涨得飞快，单表查询和写入都开始变慢。什么信号出现时真的需要分库分表？分片键怎么选？按什么维度分（用户/订单号/时间）各有什么取舍？

【参考答案】订单表爆炸、单库撑不住：分库分表按访问模式切：

容量估算（步进）：假设年订单 5 亿，5 年 25 亿行；单库磁盘与主键 B+ 树高度、备份窗口都会成为问题。写峰值 5000 TPS，单库写上限约 2000–5000 TPS，写上先不够用；读若按用户维度，分片后每库可回到可接受 QPS。
拆法：① 垂直拆：订单主表/订单明细/扩展信息分表分库；② 水平拆：按 user_id 分库（用户查询友好）或 order_id hash；范围查询/商家维度另建索引表或同步到分析库；③ 历史归档：热库只留 3–6 个月，其余归档。
中间件：ShardingSphere 等，或业务层路由；分片键选择决定 80% 查询能否直接命中。
失败与降级：跨分片查询 → 兜底走搜索引擎/数仓；扩分片迁移双写校验；分布式事务用本地消息表/TCC 按业务容忍度；路由表缓存更新失败要回退旧规则。
口径：先问查询模式与写热点，再选分片键；能归档先归档，能拆读写先拆读写，最后才水平分片。

【原理溯源】

为什么不能「行数一到就分」？ 分库分表引入分布式 ID、跨分片查询、分布式事务、扩容迁移等一整套成本。若瓶颈只是缺索引或冷数据未归档，拆分是高射炮打蚊子，还把简单问题复杂化。正确触发条件是：优化手段用尽后，容量/吞吐/延迟指标仍恶化。
为什么水平拆分的核心是「分片键跟着高频查询走」？ 水平分表后，只有带分片键的查询能路由到单分片；缺分片键就退化为广播（全分片扫）。订单最高频是「用户查自己的订单」，所以 user_id 作分片键能把主路径锁在单分片。若按 order_id 哈希，用户列表页会打穿所有分片。
为什么 Hash 与 Range 是经典取舍？ Hash（如 user_id % N）分布均匀，避免热点，但扩容（N 变化）要重哈希迁移；Range（按时间/ID 段）利于范围查询和「整段归档」，但新数据全落最新分片，写热点明显。没有银弹，按「均匀 vs 范围/归档」优先级选。
垂直拆分解决什么？ 垂直分库按业务域切开，减少单库连接与锁竞争；垂直分表把大 text/JSON 拆到扩展表，让主表行更小、一页放更多行、热字段扫描更快。两者都不改变「同一行仍在一起」的水平约束。
为什么水平拆分前必须有分布式 ID？ 自增主键在多分片下会重复。雪花算法/号段模式提供全局唯一且趋势递增的 ID，既保证主键唯一，又减少 B+ 树页分裂（相对 UUID 乱序）。

【选型判断树】

单库单表撑不住，按顺序决策：
├─ 0) 先排除伪瓶颈：索引失效？冷数据未归档？大事务/锁？
│   └─ 是 → 先优化/归档（第 52/65/74 题），不要急着拆
├─ 1) 瓶颈在业务域耦合/连接/资源争抢？
│   └─ 垂直分库/分表（主表+扩展表）
├─ 2) 瓶颈在单表行数与 IO/RT？
│   └─ 水平分表
│      ├─ 分片键：选最高频查询条件（订单→user_id）
│      ├─ 算法：要均匀→Hash；要范围/归档→Range（或时间分表）
│      └─ 配套：分布式 ID + 跨分片查询方案 + 迁移工具
└─ 3) 单库 QPS/连接到顶？
    └─ 水平分库（同一分片键，库级拆分）——**读写分离与归档排在分片之前**（正文口径：能归档先归档、能拆读写先拆读写，最后才水平分片）

判断口诀： 先优化再拆；拆前定分片键；算法选均匀还是范围。

【口述骨架】（5 分钟）

时间	任务	内容
0:00–0:30	定性	「分库分表是最后手段，先讲触发信号与前置优化」
0:30–1:20	触发信号	行数+RT/IO 恶化、QPS/连接到顶、容量压力
1:20–2:20	拆分方式	垂直分库/分表 vs 水平分表，各自解决什么
2:20–3:30	分片键	跟高频查询走：订单用 user_id；商家侧走 ES/映射
3:30–4:20	算法取舍	Hash 均匀 vs Range 利于范围与归档；写热点问题
4:20–5:00	配套收尾	分布式 ID、跨分片、迁移；强调不要过早拆

【关键数字】

参数	经验值	说明
评估分表行数	500 万–1000 万+（看指标）	非绝对阈值
3 层 B+ 树容量	约 2000 万级	行数到了不必然慢
单库 QPS 经验上限	约 1–2 万（视硬件/SQL）	到顶考虑分库
常见分片数	2ⁿ（16/32/64…）	Hash 取模迁移友好些
分布式 ID	雪花/号段	替代多分片自增
扩容	一致性哈希 / 成倍扩容	直接 %N 变更会重哈希
单表关注点	千万行级开始评估	非绝对魔数，看 RT/扫描/备份
连接池全局预算	实例数×池 max ≤ max_connections×0.8	预留运维连接
慢查询目标	扫描行数 < 1–5 万（在线）	返回少扫描多必优化

【追问链】（三层）

L1｜「分片键选错会怎样？」 → 主查询路径无法路由，退化为全分片广播，延迟与负载按分片数放大。例如按 order_id 分片却主要按 user_id 查列表——每次翻页打全部分片。

L2｜「Hash 取模扩容为什么痛苦？怎么缓解？」 → N 变化后 hash % N 映射改变，大量数据要重分布。缓解：①成倍扩容（N→2N，部分桶可映射迁移）；②一致性哈希（虚拟节点）；③提前按较大 N 逻辑分片、物理少实例。扩容要双写/追平+校验切流。

L3｜「分了 64 表后，运营要按商家统计，你怎么办？」 → 不能在 OLTP 分片上广播聚合。主路径仍走 user_id；商家维度用 ES/搜索索引或数仓（ClickHouse/Hive）旁路；精确单号查询走映射表定位分片。见第 57 题。

【评分标准】

档位	答案特征
60 分	知道水平/垂直拆分，能提按 user_id 分片
80 分	有触发阈值与前置优化意识；讲清 Hash vs Range；分片键跟查询走
95 分	纠正「行数到了必须分」；指出分片键选错后果；覆盖分布式 ID/扩容/跨分片配套；承认 ES/数仓旁路

【关联题】

同一知识簇： 第 57 题（跨分片查询）→ 第 74 题（何时必须分）→ 第 65 题（归档替代方案）→ 第 58 题（跨库一致性）
上游优化： 第 51/52/54 题（SQL 与索引）、第 59 题（读写分离）
配套： 分布式 ID、ShardingSphere 相关（第 57 题）

【自测】

单表 800 万行，RT 无恶化，是否应立即分表？ 参考答案：否。应以性能指标恶化为准；先索引/归档/读写分离，分表是成本更高的最后手段。
订单表为何常用 user_id 而非 order_id 作分片键？ 参考答案：最高频查询是用户维度列表，user_id 可路由到单分片；order_id 分片会让用户列表广播全分片。
Range 分表的主要风险是什么？ 参考答案：写入集中在最新分片形成热点；但利于按时间范围查询与整段归档。

56. 翻到第 5000 页，接口越来越慢（深分页优化）

【考察内容】深分页是数据库优化高频题

【题目】列表页翻到很后面（比如第 5000 页），接口响应越来越慢，SQL 形如 LIMIT 100000, 20。为什么 offset 越大越慢？有哪些优化手段（延迟关联、游标、覆盖索引等）？各适合什么场景？

【参考答案】深分页越来越慢：OFFSET 的代价：

原理与估算（步进）：题干的 LIMIT 100000, 20 仍需扫描并丢弃前 10 万行（每页 20 行时正是第 5000 页）。假设单行回表 100µs，仅丢弃就要约 10 秒 理论量级（1e5 × 100µs；实际有缓存与并行后仍远超接口可接受的百毫秒级）。页码越深，RT 越差，呈现单调上升；第 1 页可能 20ms，翻到这么深的页就可能秒级超时。若 offset 再大近两个量级（500 万行）就是约 500 秒，只能靠游标/翻页token 规避。
优化：① 游标分页：WHERE id < last_id ORDER BY id DESC LIMIT 20（每页性能稳定在 10–50ms）；② 延迟关联：先在覆盖索引上只取主键再回表；③ 业务限制最大页码/改用“加载更多”；④ 搜索/导出走 ES/离线任务。
对比：OFFSET 方式 RT 随页深线性甚至超线性恶化；游标方式与页深无关。C 端列表几乎都应按游标设计。
失败与降级：用户直接粘贴深页链接 → 降级为默认近页或提示用筛选条件；导出任务限流错峰，避免深扫影响在线库；ES 挂了回退 DB 时更要限流。
口径：页码深翻本质是分析型需求，不该逼 OLTP 单表扛；用游标换稳定 RT，用分析库换复杂查询。

【原理溯源】

为什么 offset 越大越慢？ LIMIT offset, n 的语义是「跳过 offset 行再取 n 行」。即使走索引，引擎仍要沿索引/叶子移动 offset 次才能到达起点；若还回表取整行判断，代价再乘一倍。offset=10 万意味着先白读 10 万行再取 20 行——浪费随页码线性上升。
为什么游标分页能消掉这个成本？ 游标把「绝对位置」换成「上次读到的键值」：WHERE id > last_id ORDER BY id LIMIT 20 直接在 B+ 树上定位到 last_id 之后，IO 与第几页无关，只与本页 20 行相关。这是「记住进度」替代「从头数」。
延迟关联为什么比直接深分页好一点？ 子查询只在覆盖索引上扫 id（窄），再对最终 20 个 id 回表取宽行；避免「先回表 10 万行再丢弃」。它降低的是每行宽度/回表次数，offset 扫描仍在，所以比游标差，但比裸 LIMIT 好。
为什么业务上常直接限制翻页深度？ 真实用户极少翻到第 5000 页；爬虫/异常参数才会。限制深度（如 100 页）+ 条件筛选，用产品手段消掉病态访问，比纯技术方案更便宜。
为什么排序列不唯一时游标会漏/重？ 仅用 create_time < last 时，同时间戳并列行无法区分先后，可能跳过或重复。需复合游标 (create_time, id) < (last_time, last_id)，用 id 打破并列。

【选型判断树】

列表分页怎么选：
├─ 产品允许无限滚动 / 只下一页
│   └─ 游标分页（推荐）：WHERE 键 > last ORDER BY 键 LIMIT n
│      └─ 排序列有并列 → 复合游标 (time, id)
├─ 必须支持跳页（点第 N 页）
│   ├─ 浅页（N 小）→ 常规 LIMIT，排序走索引
│   ├─ 深页但可接受稍慢 → 延迟关联（子查询先拿 id）
│   └─ 深页且高频 → 限制最大页码 / 条件筛选 / 搜索引擎
└─ 宽表大字段
    └─ 延迟关联或先 id 后 detail，避免深分页回表 10 万次

判断口诀： 能游标就游标；必须跳页先限深度；宽表先瘦身后取。

【口述骨架】（5 分钟）

时间	任务	内容
0:00–0:30	定性	「深分页本质是扫描丢弃：offset 越大，白读越多」
0:30–1:20	讲清原因	LIMIT offset,n 要先走过 offset 行；可叠加 filesort/回表
1:20–2:40	游标分页	last_id 定位，代价与页码无关；并列值用 (time,id)
2:40–3:40	延迟关联	子查询窄扫 id 再回表 20 行；适合必须跳页
3:40–4:30	业务手段	限制翻页深度、条件筛选、搜索引擎
4:30–5:00	收尾	「无限滚动用游标；跳页先限深；索引保排序」

【关键数字】

参数	经验值	说明
典型慢阈值	offset > 1 万–10 万开始明显	与行宽、回表相关
游标页成本	恒定（约 LIMIT n）	与 offset 无关
延迟关联收益	减少深分页回表次数	offset 扫描仍在
产品限深	常见 50–100 页	超出改筛选
LIMIT n	10–50 常见	过大伤首页
复合游标	(create_time, id)	防并列漏重
单表关注点	千万行级开始评估	非绝对魔数，看 RT/扫描/备份
连接池全局预算	实例数×池 max ≤ max_connections×0.8	预留运维连接
慢查询目标	扫描行数 < 1–5 万（在线）	返回少扫描多必优化

【追问链】（三层）

L1｜「游标分页有什么缺点？」 → 不能随机跳页，只能「上一页/下一页」或无限滚动；前端需保存 last_id；排序键变化或过滤条件动态时状态要对齐。适合信息流，不适合「跳到第 300 页」的管理后台（可结合导出/筛选）。

L2｜「延迟关联到底省了什么？没省什么？」 → 省了深分页时对大量行的回表与宽列传输；没省「沿索引移动 offset」的扫描本身。所以它优于裸 LIMIT，劣于游标。

L3｜「用户就是要跳到第 5000 页做导出，怎么办？」 → 不要走在线 OLTP 深分页。改为：①异步导出任务（流式/分批扫）；②走数仓或只读副本；③条件收敛（时间范围）后导出。在线列表限制深度，导出走离线通道。

【评分标准】

档位	答案特征
60 分	知道 offset 大会慢，会说游标分页
80 分	解释扫描丢弃；对比游标与延迟关联适用场景
95 分	指出并列值导致游标漏/重及复合游标；产品限深与异步导出；区分延迟关联「省回表不省扫描」

【关联题】

同一知识簇： 第 54 题（列表 SQL 优化）、第 76 题（覆盖索引）、第 77 题（索引列序）
体量： 第 55 题（分表）、第 65 题（归档）
对比： 第 51 题（慢 SQL 闭环）

【自测】

LIMIT 100000, 20 改成延迟关联后，offset 扫描还在吗？ 参考答案：在。延迟关联只减少回表/宽行代价，仍要沿索引跳过 10 万 id。
无限滚动场景首选什么分页方式？为什么？ 参考答案：游标分页。用 last_id 定位，IO 与页深无关。
排序字段 create_time 有重复，游标 SQL 应注意什么？ 参考答案：使用 (create_time, id) 复合比较，避免同秒并列行漏读或重复。

57. 订单分表后，按商家统计、按订单号查询全废了（跨分片查询）

【考察内容】分库分表的代价认知与兜底设计

【题目】订单表按 user_id 分了 64 个表。上线后发现两个需求做不了：运营要按“商家+时间”统计订单；客服只知道订单号查单。数据分散在各分片，怎么支撑这两种查询？有哪些方案（基因法、冗余表、ES、中间件）？

【参考答案】分表后按商家统计、按订单号查询全废：重建查询维度：

问题本质：按 user_id 分片后，merchant_id 查询变成跨分片 scatter-gather，延迟与稳定性都差；订单号若不含分片键，同样要路由或广播。
方案：① 订单号内嵌分片键（把分片基因编入 ID 低位比特位）→ 点查直达；② 冗余商家订单索引表/映射表（merchant_id + order_id + shard）或独立商家库；③ 异步同步到 搜索引擎/ClickHouse 做统计与多维查询；④ 离线数仓 T+1 报表。
容量估算（步进）：商家点查假设 1000 QPS，索引表单点即可；若强行跨 64 片广播（题干即为 64 个表），RT 可能从 10ms 升到 100–300ms 且任一分片慢就整体慢。
失败与降级：索引表延迟 → 统计容忍分钟级；点查映射缺失 → 回退有限广播+限流；ES 挂 → 走 DB 只读副本+更严限流。
口径：分片键优化主查询路径，其他维度必须通过冗余存储物化出来，不能靠现场扫全分片。

【原理溯源】

为什么非分片键查询必然痛苦？ 水平分片只保存「分片键 → 分片位置」这一维路由信息。给定商家号，引擎不知道订单落在哪片，逻辑上必须问遍全部 64 片。广播的成本随分片数线性放大：连接数、延迟、各片 CPU 一起上升，还容易把在线库拖垮。
为什么「单号查单」适合映射表？ order_no 全局唯一，本质是「点查定位」。维护 order_no → user_id（或直接 → 分片号）后，两跳完成：映射表点查 + 目标分片点查。映射可与订单同事务写入（强一致）或异步（最终一致+查不到补偿）。
为什么运营统计走 ES/数仓而不是 OLTP 分片？ 「商家+时间」是多维分析查询，扫描行数大、聚合重。OLTP 库的设计目标是点查/短事务；把分析负载压上去会与交易抢 IO 和锁。ES 适合近实时多维过滤+聚合，数仓适合离线全量统计——按实时性选。
基因法是什么、何时用？ 把分片键信息编入另一业务键的低位（如 order_no 低位嵌 user_id 哈希），使「有 order_no 即可推出分片」。适合能控制 ID 生成规则的新系统；对存量 ID 或无法改码的场景不适用。
为什么中间件自动聚合不能当默认方案？ ShardingSphere 广播对应用透明，但底层仍是 64 路扇出+内存归并。低频运营报表或许可忍；高频接口会打满各分片与中间件内存。透明不等于免费。

【选型判断树】

分片键之外的查询需求来了：
├─ 点查（order_no 查单、身份证查用户）
│   └─ 映射表：order_no → user_id/分片号（同事务写或异步+补偿）
│      └─ 新系统可控 ID → 可考虑基因法把分片信息编入单号
├─ 过滤+列表/近实时分析（商家+时间、多条件筛单）
│   └─ ES/搜索引擎冗余索引，明细回 MySQL
├─ 离线统计/报表/大聚合
│   └─ 数仓（ClickHouse/Hive），T+0/T+1
└─ 低频、小数据量、可接受慢
    └─ 中间件广播聚合（明确标注「仅低频」）

判断口诀： 点查映射，分析进 ES，离线进数仓，慎用广播。

【口述骨架】（5 分钟）

时间	任务	内容
0:00–0:40	定性	「这是分库分表的固有代价：非分片键无法路由」
0:40–1:30	讲清代价	广播 64 片的连接/延迟/负载放大，不能当默认
1:30–2:40	点查方案	映射表 order_no→user_id；可选基因法
2:40–3:40	分析方案	ES 多维索引；离线走数仓
3:40–4:30	设计原则	主路径必须分片键；旁路冗余；映射一致性
4:30–5:00	收尾	「拆分前就该规划旁路，而不是拆完再救火」

【关键数字】

参数	经验值	说明
分片数本题	64	广播成本 ×64
映射表点查	1–2ms 级（走索引）	可加 Redis 缓存热点
ES 同步延迟	秒级（binlog/消息）	精确单仍回 MySQL
广播聚合	仅低频/小结果	高频禁止
映射写入	与订单同事务或本地消息表	防映射丢失
单表关注点	千万行级开始评估	非绝对魔数，看 RT/扫描/备份
连接池全局预算	实例数×池 max ≤ max_connections×0.8	预留运维连接
慢查询目标	扫描行数 < 1–5 万（在线）	返回少扫描多必优化

【追问链】（三层）

L1｜「为什么不用中间件自动把商家查询路由好？」 → 中间件只有分片键路由能力；商家不是分片键时只能广播或依赖额外索引结构。「自动」解决的是工程接入，不是物理上「不用遍历」。

L2｜「映射表自己也几亿行，怎么办？」 → 映射表可按 order_no 再 Hash 分片（本身是点查）；热映射加 Redis；或把映射信息编进单号（基因法）省掉一张表。

L3｜「ES 与 MySQL 订单数据不一致，客服查到的状态和库不一样，怎么处理？」 → 分层：展示/筛选以 ES 为准可容忍秒级滞后；涉及金额、状态变更以 MySQL 为准。监控同步 lag，失败重试+死信+定时对账；严重不一致时触发重建索引。

【评分标准】

档位	答案特征
60 分	知道要映射表或 ES，承认非分片键查询难
80 分	点查映射、分析 ES、离线数仓分场景；说明广播代价
95 分	给出基因法适用边界；映射一致性与自身分片；ES/MySQL 一致性分层；拆分前规划旁路

【关联题】

同一知识簇： 第 55 题（分库分表）→ 第 58 题（跨库一致性）→ 第 65 题（归档）
搜索旁路： Canal/binlog 同步 ES（可关联缓存一致性章节）
对比： 第 73 题（冗余一致性）

【自测】

客服只有 order_no，库按 user_id 分片，第一步查什么？ 参考答案：查 order_no→user_id 映射（表或缓存），再按 user_id 路由到目标分片。
判断对错：用 ShardingSphere 后，按任意字段统计都可以放心上线。 参考答案：错。非分片键统计是广播聚合，只适合低频小数据；高频分析应进 ES/数仓。
基因法的核心思路是什么？ 参考答案：把分片键哈希编入另一业务 ID 的固定比特位，使持有该 ID 即可推算分片，省略映射表。

58. 订单库和库存库分开了，下单扣库存跨库怎么办（跨库一致性）

【考察内容】分布式事务选型能力

【题目】分库分表后，订单数据和库存数据落在不同的库里。用户下单要“插订单 + 扣库存”跨两个库操作，一个成功一个失败就出大问题。怎么保证跨库一致性？从强一致到最终一致有哪些方案？

【参考答案】订单库与库存库分开后，下单扣库存跨库一致性：

方案对比（步进）：假设下单峰值 2000 TPS，跨库分布式事务若用同步 2PC，RT 与可用性都受损；业务可接受秒级最终一致时，优先本地消息表/TCC/Saga。资金类步骤要求更强一致性，履约类可最终一致。错误窗口目标：秒级补偿闭环，对账兜底分钟级。
落地：① 库存服务接口幂等扣减（条件更新 + 唯一流水）；② 订单事务内写订单 + 写“扣库存任务”到本地消息表，同事务提交；③ 异步投递消息，库存消费扣减，失败重试+死信告警；④ 补偿：超时未支付关单 → 回补库存；⑤ 对账任务比对订单与库存流水。
TCC：Try 预占、Confirm 确认、Cancel 回滚；必须处理空回滚、悬挂、幂等三问题；超时未确认要有定时任务推进状态机。
失败与降级：库存服务挂 → 下单失败或排队（不能无限预占）；消息丢失 → 对账补偿；重复扣减 → 幂等键拦截；极端时只保核心商品的库存扣减。
监控：补偿次数、预占超时量、对账差异、消息堆积。核心：把“跨库事务”变成“可重试的本地事务+消息+对账”。

【原理溯源】

为什么跨库不能用本地事务？ 本地事务的 ACID 锚定在单个存储引擎的事务日志（InnoDB redo/undo）上。两个库在不同实例，MySQL 无法用一份日志原子提交两边——一边 commit 成功、另一边 rollback 就会撕裂。这是物理边界，不是配置问题。
为什么「先插订单再发扣库存」会丢？ 两步不在同一原子单元：插单成功后进程崩溃/网络失败，扣库存未发生；若「先扣库存再插单」，则插单失败时库存已被扣。任何「业务操作 + 远程调用」的朴素顺序都有窗口。
为什么本地消息表能可靠？ 把「订单数据」和「待发送消息」写在同一本地事务：要么都提交要么都回滚，消灭「业务成功但消息丢失」窗口。提交后由任务扫描投递，失败重试；MQ 只负责尽力送达，对账兜底。事务消息（半消息+回查）是同一思想的中间件化实现。
TCC 为什么适合扣库存强约束？ Try 阶段做资源预占/冻结而非最终扣减，不持有长事务行锁；Confirm 才落账，Cancel 释放预占。把锁从数据库长事务下沉到业务资源状态，并发更好。代价是三段逻辑与空回滚/悬挂/幂等问题。
为什么 Seata AT 高并发下是瓶颈？ AT 基于全局锁+undo_log 自动补偿，开发量小，但全局锁在热点行上竞争激烈，吞吐受限。适合中低并发、希望少写补偿代码的场景；秒杀级扣减通常不选。
为什么「能最终一致就最终一致」？ 强一致方案牺牲吞吐、可用性与实现复杂度。积分、通知、非热点库存等业务可接受秒级延迟，用最终一致+对账即可把工程成本降一个数量级。

【选型判断树】

跨库「下单+扣库存」怎么选：
├─ 库存是否强约束（禁止超卖/短时负库存）？
│   ├─ 是，且高并发
│   │   ├─ 要短事务、可冻结资源 → TCC（+Redis 预扣挡流量）
│   │   └─ 中低并发、少写代码 → Seata AT
│   └─ 是，但可接受秒级「先下单后扣减成功」
│       → 本地消息表/事务消息 + 失败自动关单 + 对账
└─ 非强约束（积分/通知/日志）
    → 事务消息/本地消息表最终一致即可

长流程多步补偿（下单-支付-出库）→ Saga

判断口诀： 先问资源能不能超卖，再问并发，最后才选协议复杂度。

【口述骨架】（5 分钟）

时间	任务	内容
0:00–0:30	定性	「跨库撕裂的根因：本地事务无法跨实例原子提交」
0:30–1:40	方案矩阵	本地消息表/事务消息、TCC、AT、Saga——机制一句+代价一句
1:40–2:40	本场景推荐	库存强约束：TCC 或消息最终一致+关单；电商主流消息表
2:40–3:40	为什么消息表可靠	业务与消息同库同事务，消灭丢消息窗口
3:40–4:30	风险兜底	对账、幂等、失败关单、监控
4:30–5:00	收尾	「强一致买正确性，最终一致买吞吐，按资源分级」

【关键数字】

参数	经验值	说明
消息表重试	多次+定时补偿	不能只靠 MQ ack
TCC Try 超时	30s–5min 自动 Cancel	防资源长期占用
库存预扣 Redis TTL	与订单超时一致 15–30min	未支付回滚
对账周期	T+0 准实时或 T+1	资金/库存建议更勤
消息最终一致窗口	通常秒级～分钟级	视重试与故障
单表关注点	千万行级开始评估	非绝对魔数，看 RT/扫描/备份
连接池全局预算	实例数×池 max ≤ max_connections×0.8	预留运维连接
慢查询目标	扫描行数 < 1–5 万（在线）	返回少扫描多必优化

【追问链】（三层）

L1｜「为什么不全部用最终一致？省事。」 → 库存/资金是强约束资源。异步窗口内可能超卖或透支，资损不可接受。非核心链路才降级为最终一致。

L2｜「本地消息表怎么保证消息不丢？」 → 消息与业务同库同事务提交；提交后扫描投递，失败重试；MQ 丢仍有表里记录。还需消费侧幂等与对账，因为「投递成功≠处理成功」。

L3｜「扣库存 TCC 的 Try 成功了，Confirm 一直失败怎么办？」 → 状态表标记待确认，定时补偿 Confirm；超阈值告警人工。资源预占带超时自动释放，避免永久冻结。Cancel 幂等，防悬挂。

【评分标准】

档位	答案特征
60 分	知道要分布式事务，能列 2–3 种方案名
80 分	按一致性×并发选型；说清本地消息表同事务原理；给出本场景推荐
95 分	指出强约束资源不能盲最终一致；TCC 预占与三问题；对账兜底；「同库优先本地事务，别为同源数据上分布式事务」

【关联题】

同一知识簇： 第 99 题（分布式事务总选型）→ 第 100 题（2PC）→ 第 101 题（TCC）→ 第 102 题（Seata AT）→ 第 103 题（Saga）→ 第 92/93 题（事务消息/本地消息表）
本章上游： 第 55 题（为何会跨库）、第 57 题（分片副作用）
落地保障： 第 67 题（幂等）、第 110 题（接口幂等）

【自测】

「注册成功送积分」跨两库，选强一致还是最终一致？ 参考答案：最终一致（事务消息/本地消息表）。积分非强约束，秒级延迟可接受。
本地消息表为什么必须与业务写在同一事务？ 参考答案：否则存在「业务成功消息丢失」或「消息在业务失败时已发出」的撕裂窗口。
TCC 的 Try 与直接 UPDATE 扣减有何本质区别？ 参考答案：Try 是资源预占/冻结，不最终落账；Confirm 才扣减。避免长事务行锁，并保留 Cancel 释放能力。

59. 用户刚下单成功，刷新就查不到订单（读写分离延迟）

【考察内容】与 Redis 主从延迟题类似但侧重 MySQL

【题目】数据库做了主从读写分离后，出现一个让用户抓狂的问题：刚下单成功，立刻刷新页面，订单没了——主库写入了，从库还没同步过来。怎么解决这种“写完读不到”？有哪些方案和取舍？

【参考答案】刚下单刷新查不到（读写分离延迟）——与主从延迟同构，落到读路由：

估算（步进）：延迟分布可能 P50=50ms、P99=2s；用户下单后 300ms 内刷新的比例高，体验敏感窗口约 0–3s。写 TPS 1000 时，从库重放若单线程落后，大事务时延迟尖刺到 10s+ 都可能，投诉会集中在这类尖刺时刻。
方案：① 写后读主：下单响应后短期内该用户查询走主库（session 亲和/标记 3–5 秒）；② 核心读（支付状态、订单详情）固定读主；③ 从库只承担列表/报表等可延迟读；④ 技术降延迟：并行复制、拆大事务、提升从库规格与网络。
缓存角度：下单成功写路径主动失效用户订单缓存，避免用户还读到旧列表；查询接口优先展示刚返回的订单号结果，减少“再去查库”依赖。
失败与降级：主库压力大 → 只对“同用户+写后 N 秒”读主；延迟告警自动提高读主比例；主库过载则进一步限流列表读。
口径：不要用“再刷新一次就好”糊弄，要在路由层给出确定性方案；这是复制延迟与路由策略问题，不是缓存 bug。

【原理溯源】

为什么主从默认是异步的？ 主库提交事务写完 binlog 即返回客户端，不保证从库已收到并重放。这样写吞吐最高、可用性最好；代价是主库宕机可能丢「已成功返回」的事务，以及从库读到旧数据。这是 CAP/性能取舍，不是疏忽。
复制延迟的根因链是什么？ 主库并发写 → 从库 SQL 线程按库/按组重放（早年单线程重放是瓶颈）→ 大事务、无主键表行锁冲突、从库硬件弱、网络抖动都会拉大 Seconds_Behind_Master。压力大时延迟从毫秒级涨到秒级甚至分钟级。
为什么「写后读主库」是工程首选？ 它不改复制协议，只改路由：同一请求链路内打标，读路由到主库，避开延迟窗口。代价是主库读压力上升，所以只对「写后马上读」的关键路径生效，而不是所有读。
半同步为什么不能根治？ Semi-sync 要求至少一个从库接收 binlog（或 ACK commit）后主库才返回，显著降低「主库宕机丢已提交事务」的概率；但不缩短从库回放延迟（ACK 只代表收到 binlog/写入 relay log），慢从库的 Seconds_Behind_Master 照涨；但网络分区/从库全挂时可能退化为异步，且写 RT 上升。它改善的是持久化与延迟分布，不是把复制变成强同步读。
为什么从库延迟要监控并摘流量？ 延迟大时从库读到的是历史。超过业务可容忍窗口（如 5s）就该把该从库从读池摘掉，避免「越慢越读、越读越慢」的正反馈，也避免用户看到严重过期数据。

【选型判断树】

写后读不到，按路径选择：
├─ 是否「写后立即读」关键路径（下单结果/支付状态/余额）？
│   └─ 是 → 路由主库（hint/注解/中间件），或写后短窗读主
├─ 能否接受秒级旧读？（列表/统计/详情展示）
│   └─ 可以 → 继续读从库 + 延迟监控
├─ 主挂丢数据风险是否不可接受？
│   └─ 是 → 半同步 / MGR 多数派
├─ 从库是否总是落后？
│   ├─ 大事务/无主键 → 治理（第 62 题）
│   └─ 重放慢 → 并行复制 / 升级从库硬件
└─ 极端：所有读都要像主库一样新
    └─ 要么不拆读写（只读主），要么接受性能代价

判断口诀： 关键读回主，延迟要监控，半同步降概率，根治靠路由策略。

【口述骨架】（5 分钟）

时间	任务	内容
0:00–0:30	定性	「读写分离是最终一致架构，写后读主是关键路径解法」
0:30–1:20	根因	异步复制 + 从库重放延迟
1:20–2:40	方案组合	写后读主 / 半同步 / 缓存补偿 / 摘延迟从库 / 并行复制
2:40–3:40	取舍	主库压力 vs 一致性；半同步 RT；哪些读可旧
3:40–4:30	监控	Seconds_Behind_Master 阈值与自动摘除
4:30–5:00	收尾	「不是消灭延迟，而是让延迟不出现在关键路径」

【关键数字】

参数	经验值	说明
正常复制延迟	毫秒级	空载/低负载
压力下延迟	秒级甚至更高	大事务/网络时明显
摘从库阈值	常见 >5s 告警摘除	按业务容忍度
写后读主窗口	同请求链路或数百 ms–数s	实现用 ThreadLocal/hint
semi-sync	至少 1 从库 ACK	写 RT 上升
并行复制	8–16 worker 常见	视版本与表冲突
单表关注点	千万行级开始评估	非绝对魔数，看 RT/扫描/备份
连接池全局预算	实例数×池 max ≤ max_connections×0.8	预留运维连接
慢查询目标	扫描行数 < 1–5 万（在线）	返回少扫描多必优化

【追问链】（三层）

L1｜「如何让某条查询强制走主库？」 → 应用层 hint：MyBatis 拦截器/注解、ShardingSphere HintManager、SQL 注释标记、按接口/表配置主库路由。下单详情接口默认主库。

L2｜「半同步会不会把主库拖死？」 → 会增加写 RT（等从库 ACK）。从库超时可降级异步保可用。需监控 ACK 超时与退化次数；不是把从库变成同步备机。

L3｜「用户就是投诉「刚支付完余额不对」，你线上怎么快速处置？」 → 先让该用户关键读临时切主库/缓存读写路径；查该从库延迟与是否异常；确认无数据丢失后再恢复读写分离。事后把支付结果页默认读主，并缩短缓存 TTL 或删除补偿。

【评分标准】

档位	答案特征
60 分	知道有主从延迟，会说读主库
80 分	组合方案：写后读主、半同步、延迟监控；说清异步复制根因
95 分	区分「丢数据 vs 读旧数据」；路由 hint 落地；半同步退化与代价；大事务加剧延迟

【关联题】

同一知识簇： 第 58 题（跨库一致性思想）、第 62 题（大事务拉大延迟）、第 78 题（binlog/备份）
缓存侧： 第 27/28 题（缓存一致性）、写后补偿缓存
对比： Redis 主从延迟（第 35 题附近）

【自测】

下单成功页应读主库还是从库？为什么？ 参考答案：读主库。写后立即读，从库可能未追上，用户会看到「单没了」。
判断对错：开了半同步复制就一定不会读到旧数据。 参考答案：错。半同步主要保证主库提交时至少一个从库有日志；读从库仍可能延迟，且从库故障时可退化。
Seconds_Behind_Master 持续变大，优先排查什么？ 参考答案：大事务、无主键/无索引更新导致的行锁冲突、从库硬件与网络、是否单线程重放（开并行复制）。

60. 线上报错“Deadlock found when trying to get lock”（死锁排查）

【考察内容】死锁排查是数据库高频场景题

【题目】线上日志频繁出现 Deadlock found when trying to get lock，事务回滚导致部分操作失败。你如何确认死锁现场（怎么查）、分析死锁原因（常见加锁顺序问题），并从代码与索引层面根治？

【参考答案】死锁：定位等待环，调整事务与索引顺序：

排查：SHOW ENGINE INNODB STATUS 看死锁日志中的等待锁与持有锁、SQL、索引；结合业务日志确认两个事务的加锁顺序；监控死锁频率与热点表。
常见原因：① 反向加锁（事务 A 先锁 1 再 2，B 先 2 再 1）；② 索引缺失导致锁升级为更大范围（间隙锁/扫描锁）；③ 事务过大持锁久；④ 隔离级别 RR 下范围 FOR UPDATE 间隙锁冲突；⑤ 唯一键冲突插入加锁。
修复：统一加锁顺序（按主键排序批量更新）；补索引缩小锁范围；缩短事务、拆批；必要时 SELECT ... FOR UPDATE 范围改条件更新；应用层捕获死锁错误做退避重试。
容量估算（步进）：并发更新同一热点行时死锁概率上升；假设热点更新 500 TPS，拆成有序小事务后冲突率可降一个数量级。死锁后 InnoDB 自动回滚一个事务，应用必须可重试，否则表现为业务失败率上升。
失败与降级：持续死锁则限流/拆分热点账户（如余额分桶）；锁等待超时配置与告警；紧急时只读降级。口径：先看死锁日志定环，再改顺序/范围/事务大小，最后才是重试兜底。

【原理溯源】

死锁的四个必要条件是什么？（四条同时成立才可能死锁，是必要而非充分；判死锁看等待图是否成环）互斥、持有并等待、不可剥夺、循环等待——InnoDB 行锁场景里，破坏「循环等待」最有效：全体事务按同一全局顺序访问行/索引，环就闭合不了。
为什么 InnoDB 会检测到死锁并回滚一个？ 检测等待图（wait-for graph）存在环时，选代价较小的事务作为牺牲者回滚，抛 Deadlock found...，让另一方继续。这与「锁等待超时」不同：后者是等太久被 innodb_lock_wait_timeout 杀掉。
为什么 RR 下死锁更多？ RR 除记录锁外还有间隙锁/临键锁，锁的是「区间」。两个事务对重叠间隙以不同顺序加 gap lock 或 gap+insert，极易成环。RC 无间隙锁，冲突面小很多。
为什么「走索引」能减少死锁？ 无索引时 UPDATE 可能锁住扫描到的许多行甚至近似表锁范围；有索引则锁精确命中的行与少量间隙。锁定集合越小、持有时间越短，成环概率越低。
为什么统一顺序比「加大锁超时」更根本？ 超时与重试是缓解症状；顺序统一是消灭环的形成条件。代码层约定（如按 id 排序更新）成本低、效果稳。

【选型判断树】

出现 Deadlock，按层处置：
├─ 1) 取证：SHOW ENGINE INNODB STATUS / performance_schema
│   └─ 看两个事务 SQL、锁类型、等待环
├─ 2) 归类
│   ├─ 加锁顺序不一致 → 统一资源访问顺序（代码规约）
│   ├─ 事务过长（RPC/循环）→ 拆事务，RPC 出事务（第 62 题）
│   ├─ 锁范围过大（无索引/全表更新）→ 补索引、改点查
│   └─ 间隙锁冲突（RR）→ 评估降 RC 或改等值/唯一索引访问
├─ 3) 工程兜底
│   └─ 捕获 deadlock 异常有限次重试 + 监控死锁 QPS
└─ 4) 预防
    └─ 压测复现、code review 加锁顺序、告警

判断口诀： 先看现场再归类，首选统一顺序，RC 与索引降冲突面。

【口述骨架】（5 分钟）

时间	任务	内容
0:00–0:30	定性	「死锁=循环等待；与锁等待超时区分」
0:30–1:20	取证	SHOW ENGINE INNODB STATUS 的 LATEST DETECTED DEADLOCK
1:20–2:30	机理	等待图成环、牺牲者回滚；RR 间隙锁放大
2:30–3:40	根治	统一加锁顺序、短事务、走索引、可选 RC
3:40–4:30	兜底	有限重试、监控、压测
4:30–5:00	收尾	「顺序统一是根治，超时重试是保险」

【关键数字】

参数	经验值	说明
`innodb_lock_wait_timeout`	默认 50s，生产常 3–10s	这是锁等待不是死锁
死锁检测	默认开启	热点极高时检测本身有 CPU 开销
重试次数	2–3 次+退避	防止活锁
RR vs RC 死锁	RR 因 gap lock 显著更高	降级需评估业务
取证保留	最近一次死锁	要持久化日志别只看一次
单表关注点	千万行级开始评估	非绝对魔数，看 RT/扫描/备份
连接池全局预算	实例数×池 max ≤ max_connections×0.8	预留运维连接
慢查询目标	扫描行数 < 1–5 万（在线）	返回少扫描多必优化

【追问链】（三层）

L1｜「死锁和锁等待超时怎么区分？」 → 死锁：检测到环，立即选牺牲者回滚，错误码/消息为 Deadlock found。锁等待：无环但等不到锁，超过 innodb_lock_wait_timeout 后报 Lock wait timeout。前者改顺序，后者查长事务/热点。

L2｜「RR 下 FOR UPDATE 范围查为何容易和 INSERT 打架？」 → 范围当前读加 next-key/gap，锁住区间；其他事务 INSERT 落入该区间要等 gap lock，两边不同访问序即成环。见第 69 题。

L3｜「死锁 QPS 很高但业务必须 RR，你还有什么招？」 → 缩小锁集合（索引点查）、统一顺序、拆短事务、热点拆行/分段库存、串行化热点更新到队列；评估关键路径单独 RC；死锁重试兜底并监控。

【评分标准】

档位	答案特征
60 分	知道看死锁日志，会说统一顺序
80 分	讲清循环等待；区分死锁与超时；给出短事务/索引/RC
95 分	用等待图解释；RR 间隙锁角色；重试与监控；热点行治理

【关联题】

同一知识簇： 第 61 题（隔离级别）→ 第 69 题（间隙锁）→ 第 62 题（大事务）→ 第 72 题（热点库存）
排查： 第 51 题（锁等待型慢 SQL）、第 63 题（连接被锁占满）

【自测】

两事务分别「先更新 A 再 B」与「先 B 再 A」，死锁机理是什么？ 参考答案：资源获取顺序相反形成循环等待；统一为同一顺序即可消除环。
死锁与 Lock wait timeout 是否同一回事？ 参考答案：否。死锁是环被检测并回滚牺牲者；超时是无环但等待超过阈值。
为什么降低隔离级别到 RC 能减少死锁？ 参考答案：RC 无间隙锁，锁范围从「行+间隙」缩到行，冲突面下降。需确认业务可接受不可重复读/幻读。

61. 转账并发出现重复扣款、读到未提交数据（事务隔离级别）

【考察内容】隔离级别是数据库原理必考题

【题目】转账场景出现三类事故：两个事务同时给同一账户扣款导致重复扣款；读到对方未提交的数据；同一事务内两次查询结果不一致。请说明 MySQL 四种隔离级别分别解决哪类问题？默认的 RR 下幻读是怎么出现的、怎么解决？

【参考答案】转账重复扣款/读到未提交：隔离级别与正确性：

现象映射：读到未提交 = 事务隔离不够（如 RU 下脏读）；重复扣款常见于丢失更新或幂等缺失，不完全是隔离级别一句话的事。
隔离级别：四种隔离级别与三类异常要一次答全：RU 允许脏读/不可重复读/幻读（只防丢失更新都勉强）；RC 禁脏读，允许不可重复读与幻读（无间隙锁，每语句新 ReadView）；RR（MySQL 默认） 禁脏读与不可重复读（同一 ReadView），幻读在快照读下由 MVCC 看不到、在当前读下由 next-key/间隙锁防住；Serializable 读加锁、三类都禁但并发最差；RC 下不可重复读更常见。金融账务通常 RR/RC + 明确加锁/条件更新，而不是指望隔离级别包打天下。
正确转账姿势（步进估算）：账户 A→B 转 100 元：① 事务内按固定顺序锁两行（避免死锁）；② UPDATE account SET balance=balance-100 WHERE id=A AND balance>=100 条件更新，影响行数=0 则失败；③ 写流水表，唯一键幂等；④ 增加 B；⑤ 提交。并发 100 个线程同时扣同一账户，条件更新保证余额不为负。
失败与降级：死锁/锁等待超时 → 重试；重复请求 → 幂等键拦截；对账发现差错 → 冲正/人工。
口径：隔离级别定义“能看到什么”，余额正确主要靠原子条件更新+幂等流水。

【原理溯源】

三类现象分别破坏了什么？ 脏读：读到未提交（可能被回滚）的数据，破坏隔离的「未提交不可见」；不可重复读：同行两次读值不同，破坏「事务内稳定视图」；幻读：两次范围查行数不同，破坏「范围结果集稳定」。级别升高是逐个关掉这些异常。
为什么 RC 能防脏读却防不了不可重复读？ RC 下每次读都取「最新已提交」快照：看不到未提交（防脏读），但别的事务在你两次读之间提交了，第二次就能看到新值。要可重复，必须让事务内多次读共用同一快照。
为什么 RR + MVCC 能让普通 SELECT 可重复？ MVCC 用 undo 版本链 + ReadView：RR 下 ReadView 在事务第一次快照读时生成并复用，之后读都走同一可见性判断，于是「事务视图」冻结，行值变更不影响本事务的普通 SELECT。
为什么当前读仍有幻读风险？ UPDATE/FOR UPDATE/INSERT 读的是最新已提交版本并加锁，不能只靠旧快照。若只锁已存在的行，其他事务仍可往间隙插入新行，范围结果变化。所以 RR 引入 gap/next-key 锁住「可能插入的位置」。
为什么极端情况仍可能「看起来幻读」？ 先快照读再当前读混用时：快照读看到旧集合，当前读加锁后读到新插入行，同一事务内两种读法结果不一致。严格防幻要避免这种混用模式，或接受 MySQL 的「当前读防插入」语义。
为什么 MySQL 默认 RR 而 Oracle 默认 RC？ 历史上 statement binlog 在 RC 下主从可能不一致，RR+间隙锁更安全；ROW binlog 后 RC 可用。互联网常改 RC 换更高并发（无 gap lock），前提是业务不依赖可重复读。

【选型判断树】

选隔离级别：
├─ 是否依赖「事务内两次读结果一致」（报表对账、余额校验）？
│   └─ 是 → 保持 RR，或改写 SQL 锁定（FOR UPDATE）
├─ 是否热点高并发、死锁频发？
│   └─ 是 → 评估 RC（ROW binlog），业务确认可接受不可重复读/幻读
├─ 是否读到未提交数据可接受？
│   └─ 一般不可 → 至少 RC
└─ 是否要求严格串行？
    └─ Serializable（性能差，少用；可用分布式锁/队列替代）

判断口诀： 先确认业务要「稳视图」还是要「高并发」，再定 RR/RC。

【口述骨架】（5 分钟）

时间	任务	内容
0:00–0:30	定性	「四级别对应三异常，MySQL 默认 RR」
0:30–1:40	对号入座	脏读/不可重复/幻读分别谁解决
1:40–3:00	RR 幻读	快照读 MVCC；当前读靠 gap/next-key
3:00–4:00	选型	RR 历史原因；RC 换并发；业务确认
4:00–5:00	收尾	「隔离级别是正确性与并发的旋钮，不是越高越好」

【关键数字】

参数	经验值	说明
MySQL 默认	RR	可改 RC
Oracle/PG 常见	RC	互联网 MySQL 也常改 RC
脏读	RC 起消除
不可重复读	RR 起消除
幻读	RR 靠 MVCC+间隙锁	极端混用仍有例外
死锁率	RC 通常明显低于 RR	因无 gap lock
单表关注点	千万行级开始评估	非绝对魔数，看 RT/扫描/备份
连接池全局预算	实例数×池 max ≤ max_connections×0.8	预留运维连接
慢查询目标	扫描行数 < 1–5 万（在线）	返回少扫描多必优化

【追问链】（三层）

L1｜「MVCC 怎么实现可重复读？」 → 行有 trx_id 与 roll_pointer 指向 undo 版本链；ReadView 记录活跃事务集合。RR 下事务首次快照读生成 ReadView 并固定，之后按同一可见性规则读旧版本，实现稳定快照。见第 68 题。

L2｜「为什么 RC 也能在很多公司用？」 → ROW 格式 binlog 下主从一致有保障；无间隙锁死锁少、并发高。业务若多为点查+最终一致展示，RC 更合适。转账余额校验可用 FOR UPDATE 显式加锁。

L3｜「先 SELECT 再 UPDATE 同一范围，在 RR 下还有什么坑？」 → SELECT 是快照读，UPDATE 是当前读且加锁；期间插入的新行会被 UPDATE 扫到，与快照不一致。对账/扣减应使用当前读（FOR UPDATE）或条件更新，避免依赖快照做写决策。

【评分标准】

档位	答案特征
60 分	能列出四级别与三问题的粗对应
80 分	讲清 RR 默认；快照读 vs 当前读两种防幻读机制
95 分	答到题干首事故（重复扣款）的正解：余额正确靠原子条件更新＋幂等流水，不靠隔离级别；再解释 MVCC 与间隙锁分工、极端混用例外、RC 选型与 binlog ROW 前提

【关联题】

同一知识簇： 第 68 题（MVCC）→ 第 69 题（间隙锁）→ 第 60 题（死锁）
应用： 第 72 题（条件更新防超卖）、第 62 题（事务边界）

【自测】

「读到对方未提交的数据」对应哪类问题？RC 能否消除？ 参考答案：脏读。RC 只读已提交，可消除脏读。
RR 下普通 SELECT 为何可重复？ 参考答案：MVCC，事务首次快照读固定 ReadView，后续读同一可见性快照。
为什么互联网有时故意改 RC？ 参考答案：去掉间隙锁，降低死锁、提高并发；前提 ROW binlog 且业务不依赖可重复读。

62. 一个事务里处理 10 万条数据还调远程接口（大事务治理）

【考察内容】大事务是“看起来简单但必踩坑”的高频题

【题目】有同事写代码喜欢“一个事务搞定一切”：事务里循环更新 10 万条数据、还调用远程接口。这类大事务会带来什么问题（锁、回滚、主从延迟、连接占用）？怎么避免？

【参考答案】大事务处理 10 万条还调远程接口：拆！

危害：长事务持有锁与 undo，复制延迟暴涨，连接占用久；调远程更致命——RT 不可控，锁时间不可控。假设批量操作本该 2s，远程每个 200ms×分批调用，可能变成分钟级事务，主从延迟从秒级冲到几十秒，从库读全部受影响。
治理（步进）：将 10 万条拆成 500–1000 条/批 的小事务，批间提交；远程接口移出事务：先提交本地，再异步调用/MQ；回滚策略改为补偿而非跨系统大事务。批大小按单批目标耗时 <1s 反推。
容量估算：单事务 10 万行更新可能触发 undo/redo 与主从延迟数十秒；拆批后单事务 <1s，延迟可控在秒级内。连接占用从“长时间独占”变为短脉冲，池压力大幅下降。
失败与降级：部分批失败 → 记录断点续跑，幂等（业务键/批次号）；重试上限与告警；高峰期暂停大任务或降速；远程调用失败不回滚已提交本地数据，走补偿。
预防：代码评审禁止事务内 RPC；事务模板限制时长；监控长事务（>5s 告警）、复制延迟联动。

【原理溯源】

为什么事务越大锁越久？ 行锁在事务提交/回滚时才释放。事务里每多做一步，锁的持有时间就向后拉长。10 万行更新 + 若干 RPC，其他事务在这些行上排队，吞吐塌陷。
为什么 undo 会膨胀、回滚更慢？ 更新前镜像写入 undo。10 万条修改产生大量 undo 版本；若中途失败要回滚，InnoDB 必须沿 undo 逆操作，耗时长且占 IO，还可能撑爆 undo 表空间。
为什么 RPC 放在事务里是反模式？ ①远程调用 RT 不可控（几十 ms 到超时），锁持有时间被网络放大；②远程成功但本地事务最终失败，产生「外部已通知、库内已回滚」的不一致；③下游超时拖死连接池。正确姿势是事务内只写库，提交后再发 MQ/HTTP，或用本地消息表。
为什么大事务加剧主从延迟？ 从库需完整重放该事务才算追上；期间从库被该大事务占住，后续小事务排队，Seconds_Behind_Master 飙升。读写分离架构下用户立刻感到「读旧」。
为什么拆成小批更优？ 每批 500–1000 条独立提交：锁窗口短、undo 小、失败重试粒度细、从库可流水重放。代价是「非原子」——要靠进度表/幂等保证断点续跑，业务上可接受（批处理本质允许中间态）。

【选型判断树】

发现长事务/事务内 RPC：
├─ 能否拆小？
│   ├─ 能 → 分批提交（500–1000/批）+ 进度记录 + 幂等
│   └─ 不能（强原子）→ 评估是否可改成状态机/补偿，而不是拉长事务
├─ 是否有 RPC/MQ/HTTP？
│   └─ 移出事务：先 commit，再本地消息表/事件发布
├─ 是否锁了不必要的行？
│   └─ 补索引、改点查、避免全表 UPDATE
└─ 监控
    └─ innodb_trx 长事务告警；连接池使用率；复制延迟

判断口诀： 事务内只碰数据库；批要小；远程一律出去。

【口述骨架】（5 分钟）

时间	任务	内容
0:00–0:30	定性	「大事务+事务内 RPC 是经典反模式」
0:30–1:40	危害五连	锁、undo、主从延迟、死锁、连接池
1:40–2:40	RPC 问题	RT 不可控、提交与外部副作用撕裂
2:40–3:40	治理	分批、RPC 出事务、本地消息表、索引缩锁
3:40–4:30	监控	innodb_trx、锁等待、复制延迟
4:30–5:00	收尾	「短事务是数据库并发的地基」

【关键数字】

参数	经验值	说明
批大小	500–1000 行/事务	按行宽调
长事务告警	>5–10s 需关注	金融更严
事务内 RPC	禁止	提交后再调
`innodb_lock_wait_timeout`	3–10s	快速失败
大事务复制延迟	可达分钟级	读写分离雪上加霜
单表关注点	千万行级开始评估	非绝对魔数，看 RT/扫描/备份
连接池全局预算	实例数×池 max ≤ max_connections×0.8	预留运维连接
慢查询目标	扫描行数 < 1–5 万（在线）	返回少扫描多必优化

【追问链】（三层）

L1｜「事务里调 RPC 为什么还可能丢数据一致性？」 → RPC 成功后本地事务回滚，外部已发货/已通知但库内无单；或本地提交成功但 RPC 失败且无重试。应本地事务成功后发消息，消费方幂等。

L2｜「拆事务后中途失败，怎么保证最终做完？」 → 记录批次进度/水位，任务断点续跑；每批幂等（唯一键/状态机）；失败重试+告警；必要时人工补跑。

L3｜「必须更新 100 万行且要尽量原子，怎么办？」 → 评估真原子必要性；多数场景可用「状态机推进+对账」替代单事务原子。若必须，低峰执行、控制锁、监控 undo 与延迟，并接受窗口内性能下降。

【评分标准】

档位	答案特征
60 分	知道大事务不好，会说拆批
80 分	列出锁/undo/主从/连接危害；RPC 移出事务
95 分	解释 undo 回滚与复制重放；进度+幂等续跑；监控指标

【关联题】

同一知识簇： 第 60 题（死锁）、第 59 题（主从延迟）、第 63 题（连接池）、第 58 题（本地消息表）
批处理： 第 75 题（批量写入）

【自测】

为什么禁止在事务里调 HTTP 接口？ 参考答案：RT 不可控拉长锁持有；外部副作用与本地事务可能撕裂。
分批更新每批多大较合适？ 参考答案：常见 500–1000 行，结合行宽、锁竞争与复制延迟压测调整。
怎么发现线上长事务？ 参考答案：扫描 information_schema.innodb_trx，对 trx_started 超阈值告警。

63. 报错“Too many connections”，服务大面积超时（连接池打满）

【考察内容】连接池与容量治理

【题目】线上突然大面积报 Too many connections / 连接池获取超时，应用不可用。怎么排查是连接没释放、还是连接数配置太小、还是数据库本身被拖垮？处理顺序和长期治理怎么做？

【参考答案】Too many connections：连接池/MySQL 连接数打满：

容量估算（步进）：MySQL 默认/常见 max_connections=151 或调到 500–2000。假设应用 50 实例×连接池 max 20 = 1000 连接，再叠加后台任务、其他服务，容易打满。RT 上升时池被占满，新请求拿不到连接 → 大面积超时。
应急：① 先看是哪类连接暴涨（SHOW PROCESSLIST）；② 杀掉空闲/慢查询连接；③ 应用限流降并发；④ 临时调高 max_connections 并扩连接预算（治标）；⑤ 服务降级非核心，保核心写。
根治：统一连接池中间层/代理（ProxySQL 等）；池大小公式化：实例数 × 池大小 + 保留 ≤ max_connections；慢 SQL 治理（连接被慢查询长期占用）；读走从库/缓存降低总连接需求。
失败与降级：获取连接超时要快速失败+重试引导；配置动态生效避免重启；监控 active/idle/wait、拒绝次数、MySQL Threads_connected。
口径：连接是稀缺资源，必须按全局预算配置，不能每个服务各配各的。

【原理溯源】

MySQL 连接为什么「贵」？ 每个连接对应一个服务线程，占内存、调度与上下文切换成本。连接数不是越高越好：超过 CPU 核数后大量线程争抢，吞吐反而下降。Too many connections 是连接数撞上 max_connections（或中间件池上限）的硬顶。
为什么慢 SQL 会打满连接池？ 连接被「借走」直到 SQL 结束才归还。慢 SQL 执行 3s，连接就占 3s；QPS 一高，池瞬间被占满，后续请求在池上排队超时——看起来是「连接不够」，根子是「单次占用太久」。
为什么连接泄漏比配置小更危险？ 泄漏是代码借出不还（异常路径没 close）。池大小无论多大最终都会漏光，且随时间恶化。必须靠泄漏检测（阈值告警/堆栈）而不是盲目调大 max。
为什么容量公式是「实例数 × 每实例池」？ 应用多副本部署时，每个副本都持有自己的池。总连接数 = 副本数 × maximumPoolSize，必须给 DB 的 max_connections 留管理与运维余量（备份、DBA、监控）。只按单实例算会在扩副本时突然打满。
应急为什么先杀慢/限流而不是先重启池？ 重启连接池只是清空应用侧状态，若慢 SQL/泄漏根因还在，立刻再次打满。先切断放大源（kill 慢查询、限流），再恢复，才能稳住。

【选型判断树】

Too many connections / 池获取超时：
├─ 看 SHOW PROCESSLIST / innodb_trx
│   ├─ 大量 Query 时间长 → 慢 SQL 占连接 → kill + 治 SQL
│   ├─ 大量 Waiting for lock → 长事务/热点锁 → 拆事务/统一顺序
│   ├─ 大量 Sleep 很久 → 泄漏或 max_idle 太大 → 泄漏检测/调 idle
│   └─ 连接数暴涨但都很短 → 真·流量 → 限流+扩容+读写分离
├─ 算容量：副本数 × 池大小 vs max_connections
│   └─ 超了 → 调池或分库或降副本单池
└─ 应急顺序：限流 → 杀慢/空闲 → 恢复 → 根治

判断口诀： 先看连接在干什么，再算总容量，最后才是调参数。

【口述骨架】（5 分钟）

时间	任务	内容
0:00–0:30	定性	「连接是稀缺资源：占用时长 × 并发 = 池压力」
0:30–1:30	排查四类	慢 SQL、锁等待、泄漏、真流量
1:30–2:30	应急	限流、kill 慢连接，慎用重启
2:30–3:40	容量公式	实例数×池 < max_connections，留余量
3:40–4:30	根治	治 SQL、泄漏检测、读写分离/分库
4:30–5:00	收尾	「池大小不是越大越好，占用时长才是」

【关键数字】

参数	经验值	说明
池使用率告警	>80%	预警
connectionTimeout	生产 3–5s	快速失败
maximumPoolSize	CPU 密集场景常 10–20/实例	视压测
DB max_connections	常 500–2000	留运维余量
泄漏检测阈值	Hikari leak-detection-threshold 如 30s	超时未归还告警
长事务	>5–10s	会占连接
单表关注点	千万行级开始评估	非绝对魔数，看 RT/扫描/备份
连接池全局预算	实例数×池 max ≤ max_connections×0.8	预留运维连接
慢查询目标	扫描行数 < 1–5 万（在线）	返回少扫描多必优化

【追问链】（三层）

L1｜「为什么连接池不是越大越好？」 → 连接过多导致 DB 线程调度与锁竞争加剧，吞吐下降、延迟上升；应用侧也有线程与内存开销。池应匹配「并发 SQL 的有效并行度」（常接近 CPU 核数）。

L2｜「怎么证明是连接泄漏？」 → 看 Sleep 连接随时间单调增长且不随流量下降；开启池泄漏检测打印未归还栈；对比 maxLifetime 内连接数曲线。代码审查异常分支与未关闭 ResultSet/Connection。

L3｜「扩容应用副本后突然 Too many connections，为什么？」 → 总连接=副本×池，扩容线性放大。治理：每实例池调小、DB 提高 max_connections、加代理（ProxySQL）连接复用、或分库分片摊薄。

【评分标准】

档位	答案特征
60 分	会看 processlist，知道可能慢 SQL 或配置小
80 分	四类根因+容量公式；泄漏检测
95 分	应急顺序与「先治占用时长」；扩容打满机理；代理/分库

【关联题】

同一知识簇： 第 64 题（池参数）、第 51/70 题（慢 SQL/CPU）、第 62 题（长事务）、第 60 题（锁）

【自测】

应用从 4 副本扩到 8 副本，池 50 不变，DB max_connections=500，有何风险？ 参考答案：8×50=400，叠加运维连接可能逼近上限；应下调每池或提高上限/加代理。
processlist 大量 Sleep 2 小时的连接，最可能是什么？ 参考答案：连接泄漏或应用持有不还；也可能是空闲未回收，需结合池 idle 配置与业务判断。
应急时为什么优先限流而不是重启连接池？ 参考答案：根因（慢 SQL/泄漏）还在，重启会再次被打满；限流先切断放大源。

64. 连接池参数怎么配才合理，谁也不服谁（连接池调参）

【考察内容】连接池参数化配置能力

【题目】团队对连接池参数争论不休：maximumPoolSize 设 10 还是 200？minimumIdle 设多少？connectionTimeout、maxLifetime 怎么定？请给出设置这些参数的依据（QPS、单连接耗时、DB 上限、连接复用），并说明哪些参数设置不当会引发故障。

【参考答案】连接池参数怎么配：用公式与压测定，不靠感觉：

经验公式（步进）：常用近似：池大小 ≈ **DB 主机** CPU 核数 × 2 + 有效磁盘数（算的是这台库能承载的总有效并行度，再按应用实例数分摊，不是单个应用实例的连接池大小）（偏计算/混合）；IO 密集可更大，但盲目加大没收益，反而排队在 DB。全局预算：N 实例 × 池 max + 运维连接 ≤ DB max_connections × 0.8。
例子：例：DB 主机 8 核 → 17＋有效磁盘数≈总有效并行度十几到几十；再按实例数分摊（50 实例 → 每实例个位数到十几），全局再受 max_connections×0.8 约束。别拿应用核数套这个公式——那会得出 50 实例×16~32＝800~1600 连接，正是本条要防的「打满 DB」，需与 DB 容量匹配；DB 若只能稳扛 800 活跃连接，则应用总池必须压到这个范围，多余实例要靠缓存/限流减需求，而不是加池。
关键参数：min/max、获取超时（connectionTimeout 要短，如 3–5s，别留默认 30s）、泄漏检测、连接最大存活、validation 查询；不要把池当缓冲无限排队。题干点名「minimumIdle 设多少、依据 QPS 与单连接耗时」，这两问要正面答：max 由第 1–2 条的全局预算反推（实例数×max ≤ max_connections×0.8 再按实例分摊）；池下限口径 ≈ 峰值 QPS × P99 单 SQL 耗时（这就是稳态同时在跑的查询数，例：3000 QPS × 20ms ≈ 60 个连接），minimumIdle 取该稳态值（波动大时取 P50～P90 之间），让突发不必现场建连（建连本身要 TCP＋认证＋可能的 SSL，几十 ms 级）；min 设太小＝尖峰集中建连打爆 DB，设太大＝空闲连接占 DB 侧内存与会话槽；读写分离时读从库池与写主库池分开算。读写分离时读从库池与写主库池分开。
压测校准：观察吞吐-池大小曲线，拐点后继续加大不再提升；看 DB CPU/活跃连接/RT；以 SLA 内最大 QPS 对应的池大小为推荐值。
失败与降级：拿不到连接快速失败+熔断，禁止线程长期阻塞在获取连接上；紧急动态调池与限流；监控 active/idle/wait、拒绝次数；参数变更要支持热更新。

【原理溯源】

为什么有「最优池大小」而不是无限大？ 并发 SQL 在 DB 端竞争 CPU 与锁。线程数超过核数后，更多连接只是排队和切换，有效吞吐不升反降。池应匹配 DB 的有效并行度，而不是峰值线程数。
为什么 maxLifetime 必须小于 DB 等待超时？ 若连接在 DB 侧已因 wait_timeout 被断开，而池仍复用，会出现「借到死连接」→ 执行报错 → 重试抖动。让池先主动淘汰，避免把「失效连接」交到业务手里。
为什么 connectionTimeout 生产要短？ 30s 默认会让故障时线程长时间阻塞，拖垮应用线程池，表现为全站超时。3–5s 快速失败，把压力暴露为可监控错误，而不是无声堆积。
minimumIdle 设大有什么问题？ 常驻连接多，闲置时仍占 DB 连接额度；流量低谷浪费，副本多时挤占 max_connections。设小则高峰要现建连，增加延迟。通常等于或略小于 maximumPoolSize，或让池按需伸缩。
为什么还要配合慢 SQL 治理？ 池参数只管「借还」；若每条 SQL 占用 3s，再大的池也会被占满。连接占用时长由 SQL 决定——先治 SQL，再谈池。

【选型判断树】

调连接池参数：
├─ maximumPoolSize
│   ├─ 先算：副本数 × 池 < max_connections（留 20%+ 余量）
│   ├─ 再压测：提升池大小直到吞吐不再上升
│   └─ CPU 密集短 SQL：小池（10–20）；IO 密集可稍大
├─ connectionTimeout
│   └─ 生产 3–5s（要快速失败）；不要 30s 默认
├─ maxLifetime
│   └─ 必须 < DB wait_timeout / 也可 10–30min 滚动换连
├─ minimumIdle
│   └─ 取**稳态并发数 ≈ 峰值 QPS × P99 单 SQL 耗时**（波动大时落在 P50～P90 之间），不要笼统等于 max
└─ 泄漏检测
    └─ 开启阈值告警，定位未 close

判断口诀： 先容量匹配，再压测定大小，超时要短命，生命周期要短于 DB。

【口述骨架】（5 分钟）

时间	任务	内容
0:00–0:30	定性	「池参数是容量与故障模式设计，不是拍脑袋」
0:30–1:40	maximumPoolSize	核数经验式 + 实例×池 < DB 上限 + 压测
1:40–2:40	时间类参数	connectionTimeout 短；maxLifetime < wait_timeout
2:40–3:40	minimumIdle 与验证	min 按「峰值 QPS × P99 单 SQL 耗时」取稳态值（太小＝尖峰集中建连，太大＝白占 DB 会话）；读写分离分池；失效连接检测
3:40–4:30	故障案例	池设 1000 打满 DB；maxLifetime 过长假死连接
4:30–5:00	收尾	「先治慢 SQL，再配池；超时快速失败」

【关键数字】

参数	经验值	说明
maximumPoolSize	10–50/实例常见	压测确定
经验上限	DB 主机核数×2 + 有效磁盘数（再按应用实例数分摊）	起点而非真理；别拿应用核数套
connectionTimeout	3–5s	默认 30s 过长
maxLifetime	10–30min	< DB wait_timeout
minimumIdle	≈ 峰值 QPS × P99 单 SQL 耗时（稳态并发数）	与 max 的关系由该值决定，不是固定「常等于 max」
使用率告警	80%
wait_timeout	默认 8h	应用侧应更短
单表关注点	千万行级开始评估	非绝对魔数，看 RT/扫描/备份
连接池全局预算	实例数×池 max ≤ max_connections×0.8	预留运维连接
慢查询目标	扫描行数 < 1–5 万（在线）	返回少扫描多必优化

【追问链】（三层）

L1｜「池设 200 为什么可能比 20 更慢？」 → DB 线程远多于核数，锁竞争与上下文切换吞掉收益；单 SQL 延迟上升，整体吞吐下降。有效并行度有限。

L2｜「maxLifetime 调大会怎样？」 → 连接更久不换，可能撞上 DB/中间件/防火墙静默断开，出现间歇性「connection reset」。太短则频繁建连。要小于链路上最小空闲超时。

L3｜「池满了但 DB CPU 不高，你怎么判断？」 → 说明瓶颈不在计算：连接可能都阻塞在锁等待、慢 IO、下游 RPC（若错误地在占连接时调用）。看 innodb_trx 状态与应用线程栈，而不是加池。

【评分标准】

档位	答案特征
60 分	知道几个参数名字和大致作用
80 分	给出容量匹配与超时/生命周期建议
95 分	解释「过大更慢」；maxLifetime 与 DB 超时关系；压测与监控闭环

【关联题】

同一知识簇： 第 63 题（池打满）、第 62 题（长事务占连接）、第 70 题（DB CPU 打满时的处置顺序）

【自测】

为什么生产常把 connectionTimeout 从 30s 调到 3–5s？ 参考答案：故障时快速失败，避免应用线程池被拖死，便于限流与告警。
maxLifetime 与 MySQL wait_timeout 谁应更小？ 参考答案：maxLifetime 应更小，保证池先回收，避免使用已被 DB 断开的死连接。
判断对错：池越大，应用能扛的 QPS 一定越高。 参考答案：错。超过 DB 有效并行度后吞吐不升反降。

65. 订单表 3 年 10 亿行，查询越来越慢（数据归档）

【考察内容】数据生命周期管理

【题目】订单表积累了 3 年共 10 亿行数据，热数据只有最近 3 个月。查询性能明显下降。怎么做数据归档（归档策略、冷热分离、删除与迁移方案、对线上无影响）？

【参考答案】3 年 10 亿行订单查询慢：归档+分层存储：

容量估算（步进）：订单查询绝大多数集中在近 3 个月（假设占 80%），历史查询 <5%。按题干「3 年 10 亿行」折算 ≈ 2780 万行/月（1e9 ÷ 36），热库只留近 3 个月 → 主表约 8300 万行——这已经超本库「在线单表目标千万级」约 8 倍，不能叫「回到千万级」：3 个月热窗要闭合必须再配按月分表/分片（1 个月≈2778 万行，仍偏大→按 user_id 哈希分 8~16 张），或把热窗压到 1 个月；只归档不分表，点查与列表 RT 仍不会可控；只有把热窗压到 1 个月、或热区足够集中使主表降到数千万行，才谈得上「回到千万级」与点查/列表 RT 可控。归档库可用更低成本规格，存储成本可降 50% 以上。
方案：① 按时间分区/分表；② 归档任务：冷数据迁 OSS/备份库/ClickHouse，保留可查；③ 应用查询路由：默认查热库，带时间范围且超出热库则查归档/提示去历史页；④ 统计走数仓，不打在线库。
实施注意：归档限速，避免主库 IO 打满；分批+可重入（记录进度表）；索引与校验（行数/抽样 checksum）；删除热库数据必须在归档确认之后，禁止先删后迁。
失败与降级：归档任务失败断点续跑；用户查历史超时 → 兜底异步导出任务；法律合规要求的留存年限必须满足；归档库故障时在线库只保近N月查询。
口径：归档不是删数据，是按访问温度分层，让 OLTP 库回到轻量状态；先归档再考虑分库分表。

【原理溯源】

为什么 10 亿行会慢而「最近 3 个月」不慢？ B+ 树高度本身可控，但：①非热点页很难常驻 buffer pool，点查也易读盘；②范围扫/统计扫描的页数随总量上升；③备份、DDL、purge 等运维成本随总量涨。归档是把「在线工作集」压回缓存友好的尺寸。
为什么按时间分表/归档比「只加索引」更彻底？ 索引改善访问路径，不减少总量与冷数据争抢 buffer pool。归档减少在线库行数与页数，从物理上缩小热集——这是数量级优化。
为什么大范围 DELETE 不能直接跑？ DELETE 千万行是超大事务：长锁、undo 爆炸、主从延迟、磁盘抖动。正确是分批删除或「插入归档表 + 分批删在线表」，或直接时间分表后整表切换。
为什么归档要幂等与对账？ 迁移任务可能中断重跑；无幂等会重复插入或状态错乱。迁移前后行数/checksum 对账，确认无丢无重，才能切换路由。
为什么要有统一查询入口？ 业务不应感知「这单在冷库还是热库」。路由层按 create_time 分发：近 N 月查在线，更早查归档/数仓；或异步导出。否则客服体验碎裂。

【选型判断树】

10 亿行订单，怎么归档：
├─ 是否已有时间维度分表？
│   ├─ 有 → 老分表整表迁归档实例/存储，改路由
│   └─ 无 → 在线库按月逻辑分片或影子表迁移
├─ 归档去哪？
│   ├─ 仍要在线查历史详情 → 归档 MySQL（低成本实例）
│   └─ 报表/审计 → 数仓/对象存储（Parquet）
├─ 删除策略
│   └─ 分批 delete 或切换表后 drop/truncate 老表（审计先归档）
└─ 迁移工程
    └─ 全量+增量 binlog、状态标记、对账、灰度切读

判断口诀： 热留在线，冷进归档；迁要可重入，切要可回滚。

【口述骨架】（5 分钟）

时间	任务	内容
0:00–0:30	定性	「数据生命周期：热温冷分层，在线只留热」
0:30–1:30	为什么慢	buffer pool 命中、扫描页数、运维成本
1:30–2:40	方案	时间分表、归档库/数仓、分批删
2:40–3:40	迁移安全	幂等、对账、增量、状态标记
3:40–4:30	业务路由	统一入口按时间分发；历史异步
4:30–5:00	收尾	「归档是容量治理，不是事后删数据」

【关键数字】

参数	经验值	说明
热数据窗口	90 天/3 个月	按业务调
在线单表	目标千万级	「归档后回到舒适区」只在热窗压到 1 个月或再按月分表时成立；只归档不分表，3 个月窗仍约 8300 万行、超目标约 8 倍
分批删除	500–1000 行/批	避免大事务
对账	迁移前后行数+checksum
审计保留	按法规数年	归档而非物理删
单表关注点	千万行级开始评估	非绝对魔数，看 RT/扫描/备份
连接池全局预算	实例数×池 max ≤ max_connections×0.8	预留运维连接
慢查询目标	扫描行数 < 1–5 万（在线）	返回少扫描多必优化

【追问链】（三层）

L1｜「用户要查两年前的订单怎么办？」 → 统一订单中心按时间路由：近端在线库，远端归档库/异步导出任务；详情页可提示「历史订单加载中」。

L2｜「为什么不直接 DELETE 一年前的数据？」 → 大事务锁与 undo、主从延迟、无法审计追溯。应先归档再分批删，或时间分表后下线整表。

L3｜「归档期间又有新订单写入，怎么保证不丢？」 → 全量迁移后用 binlog 增量追平；切换时短窗禁写或双写；对账通过再切读写。状态机标记迁移中/已迁移。

【评分标准】

档位	答案特征
60 分	知道按时间归档/分表
80 分	冷热分层、分批删、统一查询路由
95 分	幂等与对账、增量追平、审计与回滚；纠正「直接大 DELETE」

【关联题】

同一知识簇： 第 55 题（分表）、第 74 题（容量）、第 62 题（大事务）、第 78 题（备份）
查询侧： 第 57 题（非分片键）、第 56 题（深分页）

【自测】

归档任务失败重跑，如何避免重复插入？ 参考答案：按主键幂等（INSERT IGNORE/ON DUPLICATE）或状态标记，只迁「未迁移」。
在线库与归档库如何对外统一？ 参考答案：订单查询服务按 create_time 路由，业务无感；必要时异步任务导出。
为什么归档能提升性能而不仅是省磁盘？ 参考答案：缩小在线工作集，提高 buffer pool 命中，减少扫描页与备份/DDL 成本。

66. 新业务要建订单表，字段/索引/状态怎么设计（表设计）

【考察内容】业务表设计能力

【题目】公司要上线新电商业务，让你设计核心订单表。从字段设计（金额精度、状态机、冗余字段）、索引设计（高频查询路径）、扩展性（分表预留）几个角度，给出你的设计考量。

【参考答案】新订单表设计：字段、索引、状态机一次定好：

字段：主键（雪花/有序 ID）、user_id、merchant_id、金额分（整数，不用浮点）、状态、时间戳（创建/支付/更新）、幂等号/业务单号、版本号；大字段（报文、扩展 JSON）拆分到扩展表；字符集统一 utf8mb4。
索引（步进）：主键；user_id + create_time（用户订单列表，最高频）；需要商家查则单独索引表或 merchant_id 相关索引（注意未来分片键）；状态枚举一般区分度低，不单独建，除非与时间组合成高频条件。避免冗余低区分度索引拖慢写入。
状态机：明确合法迁移（待支付→已支付→已发货→完成/关闭）；更新用条件 WHERE status=目标前置，防并发乱跳；所有状态变更写流水或审计日志。
容量估算：峰值写 2000 TPS、行均 200B。峰值不能直接乘 365 天——按日常均值取峰值的 1/10~1/20（约 100~200 TPS）折算，年增约 31.5~63 亿行、630~1260GB（100~200 TPS ×86400×365，再 ×200B）；若业务实际只有一两 TPS，才落在"千万～亿行/数十 GB"这一档。先写清折算系数再给区间，提前规划分区/归档策略与分库分表预留（分片键建议 user_id 或订单号内嵌 shard）。
失败与降级：字段加列用 online DDL 低峰执行；索引变更可回滚；状态变更幂等；软删除与审计字段便于追查与合规。

【原理溯源】

为什么金额不能用 double？ 二进制浮点无法精确表示 0.1 等十进制小数，累加/比较会出现分位误差。金融场景用整数「分」或 DECIMAL；整数分还便于分布式计算与序列化。
为什么要商品快照冗余？ 订单是法律/财务凭证，必须反映下单那一刻的商品名、价格、规格。若实时联商品表，改名/改价/下架会篡改历史事实。快照是「时间点固化」，不是为了偷懒冗余。
为什么状态要状态机而不是随意 UPDATE？ 无约束时代码 bug 会把「已取消」改成「已发货」。状态机在应用层/DB 层限制合法边，非法流转报错；配合乐观锁版本号，防止并发下状态回退。
为什么主索引是 (user_id, create_time)？ 最高频路径是「用户翻自己的订单列表」。user_id 高选择性收敛，create_time 支撑有序分页。order_no 唯一索引同时服务幂等（防重复提交，第 67 题）。
为什么要预留分表？ 表设计阶段确定分片键（user_id）与全局 ID，避免上线后再改主键/迁移。字段与索引按「未来水平拆分仍成立」设计：所有查询尽量带 user_id。

【选型判断树】

订单表设计决策：
├─ 金额 → 整数分 / DECIMAL，禁止 float/double
├─ 历史事实字段（商品名/价/规格）→ 下单快照，不随后续变更
├─ 状态 → 枚举+状态机校验；并发用 version 乐观锁
├─ 索引
│   ├─ 列表：(user_id, create_time)
│   ├─ 幂等/查单：order_no UNIQUE
│   └─ 商家维度：低频统计可建 `merchant_id` 相关索引或单独索引表（见正文第 2 条）；高频/多维统计不在 OLTP 硬扛，走 ES/数仓——**按频次分档，别一刀切否定 OLTP 索引**
├─ 大字段 → 扩展表或 JSON 列（注意行宽）
└─ 分表 → 分片键 user_id + 分布式 ID

判断口诀： 钱用分，事实要快照，状态要机，索引跟主路径，分表早预留。

【口述骨架】（5 分钟）

时间	任务	内容
0:00–0:30	定性	「订单表是核心资产：正确性 > 灵活性」
0:30–1:30	字段原则	金额分、快照、状态机、审计时间戳
1:30–2:40	索引	(user_id, create_time)、order_no 唯一
2:40–3:40	扩展与分表	user_id 分片、分布式 ID、扩展表
3:40–4:30	对账	流水表与订单分离
4:30–5:00	收尾	「设计先定查询路径与一致性要求」

【关键数字】

参数	经验值	说明
金额存储	分（整数）或 DECIMAL(18,2)	禁 double
超时关单	15–30min	与库存预扣一致
状态数	通常 <10	状态机清晰
列表索引	(user_id, create_time)
快照格式	JSON/子表	注意更新成本
单表关注点	千万行级开始评估	非绝对魔数，看 RT/扫描/备份
连接池全局预算	实例数×池 max ≤ max_connections×0.8	预留运维连接
慢查询目标	扫描行数 < 1–5 万（在线）	返回少扫描多必优化

【追问链】（三层）

L1｜「为什么不联表查商品名？」 → 历史订单必须显示下单时信息；联表会随商品变更而变，且列表页 N+1/大表 join 性能差。快照是空间换正确性与性能。

L2｜「并发下怎么防止状态被改乱？」 → 状态机校验合法转移 + WHERE status=期望 AND version=? 乐观锁；失败重试或拒绝。关键路径可 FOR UPDATE。

L3｜「商家维度很常用，为什么不建 (merchant_id, create_time)？」 → 可建，但用户列表与商家列表是两条路径；MySQL 单查询通常用一个索引。商家高频统计/筛选更适合 ES，避免 OLTP 写放大。

【评分标准】

档位	答案特征
60 分	能列出常见字段与 user_id 索引
80 分	金额分、快照、状态机、唯一索引幂等
95 分	讲清快照的法律/正确性意义；乐观锁状态迁移；分表预留与对账流水

【关联题】

同一知识簇： 第 67 题（防重）、第 73 题（冗余一致性）、第 55 题（分表）、第 72 题（库存）
优化： 第 54/77 题（索引）

【自测】

为什么订单金额推荐整数分？ 参考答案：避免浮点精度误差，便于计算与存储。
商品改价后，旧订单应显示新价还是旧价？ 参考答案：旧价（下单快照），保证历史事实不被篡改。
order_no 上的唯一索引还有什么工程价值？ 参考答案：防重复提交/重复消费的数据库级幂等兜底。

67. 用户重复提交订单/重复领券，数据库层面怎么挡（防重复插入）

【考察内容】幂等的数据库层实现是最高频考点

【题目】用户连续点击“提交订单”两次，或并发领券，出现两条相同记录。业务层判断不可靠，如何在数据库层面防止重复插入？唯一索引怎么建？与“先查后插”相比为什么更可靠？

【参考答案】数据库层防重复提交/重复领券：

核心手段：唯一约束 做最终防线：订单业务号、或 user_id + activity_id + coupon_template 唯一索引；插入冲突捕获后返回既有结果或明确“已领取”，不把冲突当系统错误刷屏。
配合分层：① 前端防抖+一次性 Token（请求带 token，服务端消费一次）；② Redis setnx / 分布式锁前置，把重复请求挡在 DB 外；③ 应用幂等表（request_id 唯一）；④ DB 唯一键兜底一切上层失效场景。
容量估算（步进）：热点活动重复提交率可能 10%+，峰值 2 万 QPS 时有约 2000 次冲突；唯一键冲突代价是一次索引探测（微秒～毫秒级），完全可接受。缓存去重可先挡 90%，DB 保证最终正确。
失败与降级：唯一键冲突时按业务语义返回成功（“已存在”）或明确提示；误冲突要设计好唯一键含义，不能把“不同券类型”错杀；Redis 挡不住时仍靠 DB 唯一约束，系统降级为“更慢但正确”。
口径：缓存和锁降低冲突，唯一约束保证结果正确；缺一不可，且唯一约束是不可绕过的最后防线。

【原理溯源】

为什么「先查后插」有并发窗口？ 两个事务同时 SELECT 都看不到对方未提交的行，于是都 INSERT。隔离级别解决的是「读到什么」，不能把「查+插」变成一步原子操作。唯一索引在插入瞬间由存储引擎强制唯一，是真正的原子约束。
唯一索引为何可靠？ 唯一性检查发生在 B+ 树插入路径上：第二条相同键的插入直接失败。这是引擎层不变量，不依赖应用是否记得查重、不依赖分布式锁是否超时。
为什么 Redis SETNX 不能单独扛？ Redis 是缓存，可能丢（重启/主从切换）、可能 TTL 过期后重放。SETNX 适合挡流量与快速失败；正确性仍要 DB 唯一索引兜底。两级：前置快速幂等 + 存储层最终防线。
ON DUPLICATE KEY UPDATE 的适用点？ 「重复即业务上应合并」的场景：领券计数、登录次数、upsert 配置。注意它仍是「插入或更新」语义，要防「更新了不该更新的列」。
Token/幂等键解决什么？ 减少重复请求进入核心逻辑（防抖/防重复提交），提升体验与吞吐；但 Token 校验与业务插入之间仍可能有窗口，所以不能替代唯一键。

【选型判断树】

防重复插入：
├─ 业务唯一键是什么？
│   └─ order_no / user_id+coupon_id / request_id → 建 UNIQUE
├─ 重复时业务语义？
│   ├─ 应失败提示「已存在」→ 捕获 Duplicate entry
│   ├─ 应合并更新 → ON DUPLICATE KEY UPDATE
│   └─ 应静默忽略 → INSERT IGNORE
├─ 是否高并发热点？
│   └─ Redis SETNX / Token 前置 + DB 兜底
└─ 永远不要只靠「先 SELECT 再 INSERT」

判断口诀： 唯一键是最终防线；前置缓存只是加速与削峰。

【口述骨架】（5 分钟）

时间	任务	内容
0:00–0:30	定性	「幂等的 DB 层答案是唯一索引，不是先查后插」
0:30–1:20	为什么先查后插不行	查插非原子，并发双插
1:20–2:30	唯一索引	业务键建 UNIQUE，Duplicate entry 处理
2:30–3:30	增强	ON DUPLICATE / IGNORE / SETNX+Token
3:30–4:30	分层	前端防抖、Redis 前置、DB 兜底
4:30–5:00	收尾	「正确性靠存储约束，性能靠前置拦截」

【关键数字】

参数	经验值	说明
唯一索引	业务键 UNIQUE	最终防线
SETNX TTL	秒～分钟级	过期后需 DB 挡
Token	一次性	防重复提交
重试	Duplicate 按已存在返回	注意错误码
单表关注点	千万行级开始评估	非绝对魔数，看 RT/扫描/备份
连接池全局预算	实例数×池 max ≤ max_connections×0.8	预留运维连接
慢查询目标	扫描行数 < 1–5 万（在线）	返回少扫描多必优化

【追问链】（三层）

L1｜「唯一索引冲突会不会拖慢正常插入？」 → 正常插入走唯一索引查找，成本可控；恶意海量重复重试会抬高查重与错误日志量，需前置限流。

L2｜「Redis SETNX 成功但 DB 插入失败，怎么办？」 → SETNX 键要能回滚（删键）或带状态；最终以 DB 为准。更稳是 SETNX 只作短 TTL 限流，不作为唯一真相。

L3｜「跨库跨服务怎么幂等？」 → 业务幂等键贯穿全链路；每服务本地唯一约束；跨服务用幂等号+消费确认。见第 110 题接口幂等。

【评分标准】

档位	答案特征
60 分	知道用唯一索引
80 分	解释先查后插窗口；Duplicate 处理；ON DUPLICATE
95 分	分层幂等（Token/Redis/DB）；Redis 不能单独扛；热点与限流

【关联题】

同一知识簇： 第 66 题（表设计唯一键）、第 72 题（库存条件更新）、第 110 题（接口幂等）、第 58 题（消息表幂等）

【自测】

为什么「先查有没有再插入」不能防并发重复？ 参考答案：查与插之间存在窗口，两事务都能查到不存在并都插入。
领券重复点击，DB 层推荐怎么建模？ 参考答案：(user_id, activity_id, coupon_template) 唯一索引；重复插入捕获后返回已领取。
判断对错：有了 Redis SETNX 就可以不建唯一索引。 参考答案：错。Redis 可能丢数据/过期，唯一索引才是最终正确性防线。

68. 高并发读写不互斥，靠的是什么机制（MVCC 原理）

【考察内容】MVCC 是 MySQL 原理最高频题

【题目】面试官追问：InnoDB 靠什么做到“读不阻塞写、写不阻塞读”的高并发？请讲清 MVCC 的原理：版本链、ReadView、快照读与当前读，以及它如何实现可重复读。

【参考答案】高并发读写不互斥，靠 MVCC（多版本并发控制）：

机制：InnoDB 每行有隐藏事务版本（trx_id）与回滚指针；写就地修改当前页上的最新行，把旧值写入 undo 版本链，供快照读按版本链回溯（措辞要准：物理上是「就地改最新行＋旧值进 undo」，教科书说的「生成新版本」只是逻辑等价说法，版本差异体现在 trx_id 与 undo 链，并不是另起一行副本）；读事务按 ReadView（活跃事务列表）判断自己能看到哪个版本。因此读不阻塞写、写不阻塞读。
与隔离级别（步进）：RC 每次语句生成新 ReadView；RR 事务首个一致读生成后复用，所以同一事务内多次读同一行结果稳定（可重复读）。快照读（普通 SELECT）走 MVCC；当前读（FOR UPDATE/UPDATE/DELETE）仍要加锁，会等待。
容量视角：高并发浏览场景（读 10 万 QPS）主要走快照读，因此扩展性好；若热点行被大量当前读/更新，仍会锁竞争，MVCC 救不了写热点。
失败与注意：长事务持有 ReadView → undo 版本链变长 → 回滚段膨胀、查询变慢、purge 延迟；要控制事务时长。
口径：MVCC 用空间（版本链）换并发，不是“完全没有锁”。

【原理溯源】

为什么「多版本」能消掉读写锁冲突？ 无 MVCC 时，读通常要加共享锁与写互斥。MVCC 让读去读历史版本，写去改最新版本，两者操作不同版本对象，因此读不阻塞写、写不阻塞读。代价是维护 undo 与可见性判断。
版本链怎么形成？ 每次 UPDATE：旧行信息进入 undo，新行 roll_pointer 指向旧版本，形成链。事务按可见性沿链回溯，找到「对自己可见」的那个版本。
ReadView 解决什么？ 没有视图时，无法回答「这个版本是哪个事务写的、提交了没、对我可见吗」。ReadView 记录：当前活跃未提交事务集合、最小/最大事务 ID。判断规则本质是「只看在我生成视图时已提交的版本」。
RR 与 RC 的差别为什么在 ReadView 生成时机？ RR：事务首次快照读生成后固定 → 可重复读。RC：每次读都新生成 → 总能看到最新已提交 → 不可重复读成为特性。同一套机制，只改了视图生命周期。
为什么写操作仍要当前读？ UPDATE/DELETE/INSERT/FOR UPDATE 必须基于最新版本修改并加锁，否则会丢更新。MVCC 只优化「纯读」；写路径仍有锁，靠行锁保证写写互斥。
MVCC 是否完全消灭幻读？ 快照读视角内可以「看起来没有新行」；当前读靠 gap 锁防插入。混用或语义边界场景仍可能观察到异常，详见第 61/69 题。

【选型判断树】

面试讲 MVCC，按组件展开：
├─ 行结构 → trx_id + roll_pointer
├─ 历史 → undo 版本链
├─ 可见性 → ReadView（活跃事务集合）
├─ 读类型
│   ├─ 快照读（普通 SELECT）→ 不加锁，走 MVCC
│   └─ 当前读（FOR UPDATE/UPDATE/DELETE）→ 最新版本+锁
└─ 隔离级别差异 → RR 复用视图；RC 每次新视图

判断口诀： 读旧写新，视图定可见；RR 固定视图，RC 每读新视图。

【口述骨架】（5 分钟）

时间	任务	内容
0:00–0:30	定性	「读不阻塞写靠多版本：读快照，写最新」
0:30–1:40	行与链	trx_id、roll_pointer、undo 链
1:40–3:00	ReadView	活跃集合与可见性判断；RR 固定 vs RC 每次
3:00–4:00	快照读 vs 当前读	哪些语句走哪条路
4:00–5:00	收尾	「MVCC 优化读，写仍靠锁」

【关键数字】

参数	经验值	说明
隐藏列	trx_id、roll_pointer（+row_id）
RR ReadView	事务首次快照读生成后复用
RC ReadView	每次 SELECT 新建
undo	保留到无事务需要	purge 线程清理
快照读	普通 SELECT
当前读	FOR UPDATE/LOCK IN SHARE MODE/INSERT/UPDATE/DELETE
单表关注点	千万行级开始评估	非绝对魔数，看 RT/扫描/备份
连接池全局预算	实例数×池 max ≤ max_connections×0.8	预留运维连接
慢查询目标	扫描行数 < 1–5 万（在线）	返回少扫描多必优化

【追问链】（三层）

L1｜「MVCC 能完全防止幻读吗？」 → 快照读下范围结果可稳定；当前读靠间隙锁防插入。混用与特殊语义仍有例外。要完全串行需更强手段。

L2｜「UPDATE 为什么不能只靠 MVCC 不加锁？」 → 两个事务同时基于同一旧版本改，会丢更新。写必须读最新并加行锁，保证写写互斥。

L3｜「undo 链太长会怎样？」 → 可见性判断要回溯更多版本，长事务导致 undo 无法 purge，历史版本堆积占空间、拖慢读。要避免超长事务。

【评分标准】

档位	答案特征
60 分	知道 MVCC、读写不阻塞
80 分	版本链+ReadView；RR/RC 视图时机
95 分	快照读/当前读分工；写仍加锁；undo 膨胀与长事务

【关联题】

同一知识簇： 第 61 题（隔离级别）→ 第 69 题（间隙锁）→ 第 62 题（长事务/undo）

【自测】

RR 下事务内第二次普通 SELECT 为何结果不变？ 参考答案：复用首次快照读的 ReadView，读同一可见版本集。
SELECT ... FOR UPDATE 是快照读吗？ 参考答案：否，是当前读，读最新已提交并加锁。
MVCC 主要优化了读还是写？ 参考答案：读。写仍需加锁保证正确性。

69. RR 下 FOR UPDATE 一条范围查询，为什么插入全被阻塞（间隙锁）

【考察内容】InnoDB 锁机制

【题目】RR 隔离级别下执行 SELECT * FROM orders WHERE amount BETWEEN 100 AND 200 FOR UPDATE，之后其他事务向这个金额区间插入数据全部被阻塞。这条 SQL 到底锁住了什么？间隙锁、临键锁是什么关系？为什么需要它们？

【参考答案】RR 下 FOR UPDATE 范围查询把插入全阻塞：间隙锁：

机制：RR 要抑制幻读，范围当前读不只锁命中行，还锁记录之间的间隙（gap lock），有时 Next-Key Lock（行+左开右闭区间）。其他事务向该范围插入会被阻塞。
例子（步进）：SELECT * FROM t WHERE status=1 FOR UPDATE，status 索引上扫描到的范围都可能被锁；并发插入 status=1 的新单会卡住。热点范围下，锁冲突率随并发插入上升，连接堆积后像“库挂了”。
对比：RC 无间隙锁，插入阻塞少，但业务幻读风险要自己评估；RR 一致性更稳，热点插入代价大。
处置：缩小范围（用主键精确条件）；避免长事务持有范围锁；拆事务/分批；评估改 RC+应用层防幻读；热点状态字段慎用大范围 FOR UPDATE，改条件更新或队列串行。
失败与降级：锁等待超时（innodb_lock_wait_timeout）应用重试；监控锁等待与 innodb 锁信息表。
口径：不是“MySQL 坏了”，是 RR 为一致性付出的锁代价被业务触发了。

【原理溯源】

为什么只锁「已存在的行」不够防幻读？ 范围查询的幻读来自新行插入。若只锁命中行，其他事务可在 (100,200) 之间插入 amount=150，本事务再次范围查就会多出「幻影行」。必须锁住「还能插入的位置」，即间隙。
记录锁 / 间隙锁 / 临键锁关系？ 记录锁锁存在的索引记录；间隙锁锁开区间，不含端点记录；临键锁=左开右闭「间隙+下一条记录」，是 RR 非唯一索引范围扫描的常见形态。可记为：Next-Key = Gap + Record。
为什么 FOR UPDATE 是当前读？ 它要读「最新已提交」并加写锁，供后续写使用。当前读不能只靠 MVCC 旧快照，因此用 next-key 保证范围内不被插入。
为什么唯一索引等值查询可以不锁间隙？ 要分两种情况：命中已存在的唯一键时，等值最多一行，「范围内的其他插入能改变结果集」的问题不存在（冲突插入会违反唯一约束），故退化为记录锁，降低冲突；若该唯一键记录不存在，RR 下仍会加间隙锁（gap lock）防止并发插入造成幻读。只说"唯一索引不锁间隙"会漏掉后半句。
为什么 RC 没有间隙锁问题？ RC 接受不可重复读/幻读，语义上不要求「范围不变」，因此不使用 gap lock，并发插入不被区间挡住。这是 RC 高并发的重要原因。
副作用从哪来？ gap 锁住的是区间而非行，多个事务对重叠区间加 gap/next-key 极易互锁，死锁率上升；同时「无辜」的插入业务被挡住，表现为热点区间写入尖刺。

【选型判断树】

范围 FOR UPDATE 阻塞插入，怎么权衡：
├─ 业务是否必须防幻读（对账、余额区间校验）？
│   ├─ 是 → 保持 RR + 收窄范围 + 尽量唯一索引点查
│   └─ 否 → 降 RC，去掉 gap
├─ 是否可改为点查？
│   └─ 唯一键等值 FOR UPDATE → **命中已有行**才只锁记录；行不存在时 RR 下仍加间隙锁（gap）防并发插入造成幻读
└─ 已出现死锁/阻塞
    → 统一加锁顺序、拆事务、监控（第 60 题）

判断口诀： 要防幻读就付 gap 代价；能点查别范围锁。

【口述骨架】（5 分钟）

时间	任务	内容
0:00–0:30	定性	「当前读范围锁：锁行还要锁间隙」
0:30–1:30	三种锁	Record / Gap / Next-Key 关系
1:30–2:30	为什么	只锁行防不住插入幻读
2:30–3:30	副作用	并发插入阻塞、死锁升高
3:30–4:30	缓解	RC、唯一索引点查、收窄范围
4:30–5:00	收尾	「gap 是 RR 买幻读防护的成本」

【关键数字】

参数	经验值	说明
RR 默认锁	next-key（左开右闭）	非唯一索引
唯一等值（命中已存在记录）	记录锁	不锁间隙；记录不存在时仍加 gap lock
RC	无 gap lock	并发更好
死锁	gap 重叠时升高	见第 60 题
单表关注点	千万行级开始评估	非绝对魔数，看 RT/扫描/备份
连接池全局预算	实例数×池 max ≤ max_connections×0.8	预留运维连接
慢查询目标	扫描行数 < 1–5 万（在线）	返回少扫描多必优化

【追问链】（三层）

L1｜「FOR UPDATE 只锁命中行吗？」 → 不是。范围条件在 RR 下还会加间隙/临键锁，锁住可能插入的区间。

L2｜「为什么唯一索引等值可以不锁间隙？」 → 要分两种情况：命中已存在的唯一键时等值最多一行，区间插入不会改变结果集（冲突插入由唯一约束拦下），锁退化为记录锁；该唯一键对应的记录不存在时，RR 下仍会加间隙锁防止并发插入造成幻读。只说「唯一索引不锁间隙」是漏了后半句（详见本题【原理溯源】）。

L3｜「业务要防幻读又要高并发插入，怎么办？」 → 缩小锁定范围（更精确条件、点查）、拆热点区间、必要时业务层串行热点更新；或评估是否真需要 RR 语义。

【评分标准】

档位	答案特征
60 分	知道有间隙锁，范围会挡插入
80 分	三锁关系；防幻读动机；RC 缓解
95 分	唯一等值退化为记录锁；死锁副作用；与 MVCC 快照读对比

【关联题】

同一知识簇： 第 61 题（隔离级别）、第 68 题（MVCC）、第 60 题（死锁）、第 72 题（热点更新）

【自测】

Gap Lock 锁的是记录本身吗？ 参考答案：否，锁记录之间的间隙（不含端点记录），阻止插入。
为什么 RC 下插入不被这类范围锁挡住？ 参考答案：RC 不使用间隙锁，接受幻读语义。
唯一索引 WHERE id=10 FOR UPDATE 主要加什么锁？ 参考答案：命中已存在的行 → 记录锁（锁该行），不锁间隙；若 id=10 这行不存在，RR 下会锁它所在的间隙（gap lock）防插入。

70. MySQL CPU 持续 100%，业务几乎不可用（数据库 CPU 排查）

【考察内容】DB 层故障定位

【题目】MySQL 服务器 CPU 持续 100%，业务查询基本不可用。你如何快速定位是慢 SQL、还是并发过高、还是索引失效导致的，并给出应急与根治手段？

【参考答案】MySQL CPU 100%：先找 SQL/索引，再看连接与并发：

排查顺序：SHOW PROCESSLIST 看谁在跑；慢日志；performance_schema/sys schema 热 SQL；EXPLAIN；看 QPS 是否异常；看是否有全表扫描、函数运算、排序、正则；看连接风暴与锁等待（锁等待有时也表现为 CPU 高）。
容量估算（步进）：假设异常 SQL 全表扫 5000 万行，单条就可能打满一核数秒；若并发 20 条，CPU 直接 100%。目标：杀掉/限流异常 SQL，把 CPU 压回 50% 以下留缓冲；同时估算该 SQL 的 QPS×扫描行数，判断是优化还是必须先限流。
应急：KILL 突发慢会话；入口对该接口限流/降级；必要时重启实例（最后手段，注意持久与主从）；读流量切到从库；非核心分析查询立刻停掉。
根治：加索引/改写 SQL；杀循环应用线程（代码 bug 疯狂重试会表现为 CPU 高但无慢 SQL）；拆分分析型查询到数仓；防止定时任务齐峰（随机延迟）。
失败与降级：限流后用户侧错误页/排队；主库 CPU 高时禁止多余分析查询；演练“kill SQL”预案与权限；监控 CPU、慢 SQL 数、Threads_running 联动告警。

【原理溯源】

为什么先确认进程？ CPU 100% 可能来自应用、备份任务、病毒挖矿或 mysqld。不确认进程就「优化 SQL」会浪费应急窗口。DBA 第一动作是 top/pidstat 锁定 mysqld 及线程。
全表扫描为什么吃 CPU 也吃 IO？ 扫描要把大量页读入 buffer pool，解析行、过滤条件、可能排序/聚合。数据在内存时 CPU 算过滤；不在内存时叠加磁盘 IO。索引失效把 O(logN) 点查变成 O(N) 扫描，是 CPU 飙升头号根因。
为什么连接数过高也耗 CPU？ 每个活跃连接是一个线程，上下文切换与并发执行本身有成本；超过核数后切换开销主导。所以「CPU 高」不一定是「SQL 算得多」，也可能是「人太多」。
buffer pool 命中率低意味着什么？ 工作集大于内存，热页被挤出，查询变「准磁盘查询」。放大方式是 IO wait 高+CPU 处理页加载。加大 BP、归档冷数据、优化访问模式可治。
为什么应急要 kill 而不是重启 DB？ 重启掩盖问题且丢连接/可能触发 crash recovery。先 kill 确认的高耗 SQL（如无索引全表扫）或限流，保留现场便于根因分析。

【选型判断树】

DB CPU 100%：
├─ 1) top：是不是 mysqld？
├─ 2) PROCESSLIST / 慢日志
│   ├─ 少数几条 SQL 很重 → EXPLAIN：失效/大排序/深分页 → kill+改 SQL
│   ├─ 大量短查询堆积 → 并发/QPS 过高 → 限流+读写分离
│   └─ 大量锁等待 → 长事务/热点 → 第 60/62 题
├─ 3) 看 Innodb_buffer_pool 命中率
│   └─ <99% → 加大内存/归档/优化工作集
└─ 应急：kill 高耗 + 限流；根治：索引/拆分/扩容

判断口诀： 先进程，再 SQL，再并发与缓存命中。

【口述骨架】（5 分钟）

时间	任务	内容
0:00–0:30	定性	「CPU 高要先定进程，再定 SQL 还是并发」
0:30–1:30	取证链	top → processlist → 慢日志 → EXPLAIN
1:30–2:40	根因	失效扫描、排序、连接过多、命中率低
2:40–3:40	应急	kill、限流，慎用重启
3:40–4:30	根治	索引、拆查询、读写分离、分库、加内存
4:30–5:00	收尾	「与慢 SQL 题互为表里，多一层进程与并发视角」

【关键数字】

参数	经验值	说明
buffer pool 命中率	>99% 健康	低则冷页多
long_query_time	0.1–1s	抓更多
CPU 危险区	持续 >70%–80%	预警
连接 vs 核数	连接远多于核则切换贵
单表关注点	千万行级开始评估	非绝对魔数，看 RT/扫描/备份
连接池全局预算	实例数×池 max ≤ max_connections×0.8	预留运维连接
慢查询目标	扫描行数 < 1–5 万（在线）	返回少扫描多必优化

【追问链】（三层）

L1｜「processlist 里看到很多 Sleep，会是 CPU 高的原因吗？」 → Sleep 本身几乎不耗 CPU；真正耗 CPU 的是 Query 状态中的活跃 SQL。但 Sleep 多会占连接，间接导致异常。

L2｜「怎么计算 buffer pool 命中率？」 → 命中率 = 1 − Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests，接近 1 为佳。read_requests 是逻辑读请求总数（已含未命中），reads 是其中落到物理读的次数，所以分母不能再加一次 reads（那样命中率恒 ≥50%，量也偏错）；显著偏低说明未命中增多。

L3｜「杀掉大 SQL 后 CPU 降了，但业务报错变多，你怎么权衡？」 → 应急杀 SQL 是止血；应同步限流与降级，保证核心接口。事后必须完成索引/改写，并把该 SQL 加入审核拦截。

【评分标准】

档位	答案特征
60 分	会 processlist，说加索引
80 分	完整取证链+根因分类+应急根治
95 分	buffer pool 命中率；连接过多模型；与慢 SQL 题联动

【关联题】

同一知识簇： 第 51 题（慢 SQL）、第 52 题（失效）、第 63 题（连接）、第 74 题（容量）

【自测】

第一步应确认什么？ 参考答案：top 确认是否 mysqld 占 CPU。
命中率公式是什么？ 参考答案：1 − reads / read_requests（reads 已含在 read_requests 内）。
为什么不能直接重启 MySQL？ 参考答案：掩盖根因、可能触发恢复、业务更大面积中断；应先 kill/限流保留现场。

71. 自建 MySQL 要迁到云 RDS，业务不能停（不停机迁移）

【考察内容】数据迁移工程能力

【题目】公司要把自建 MySQL 迁移到云 RDS（或升级大版本），要求业务零停机、数据零丢失。迁移方案怎么设计？全量+增量怎么衔接？切换时怎么保证一致？回滚预案怎么做？

【参考答案】自建 MySQL 迁云 RDS 不停机：

目标：切换窗口尽可能 秒～分钟级，业务不中断或只中断可接受秒级；数据零/少丢失（RPO≈0，RTO 分钟内）。
步进方案：① 全量初始化：用备份/DTS 把存量同步到 RDS；② 增量：DTS/基于 binlog 的复制，追到延迟秒级内；③ 灰度：先迁只读流量/备库，验证数据与性能；④ 切换：短时禁写或降级写（可接受的几秒）→ 检查延迟=0 → 改连接串/DNS/配置中心 → 开写；⑤ 观察与回滚预案（保留自建实例一段时间再下线旧库，观察期数天～14 天，与本题【关键数字】同口径）。
容量与风险估算：数据 1TB 级，全量同步可能数小时；增量追平后切换检查延迟 <1s；连接串切换采用双写/兼容期时注意唯一约束冲突与序列问题；RDS 规格要按峰值 QPS×RT 预留 1.5 倍。
失败与降级：同步中断告警重试；切换失败立即回切自建；应用连接池配置动态刷新，避免长时间等待旧连接；DNS TTL 要提前调短。
口径：不停机迁移的核心是 “全量+增量追平+快速切流+可回滚”，不是“复制粘贴配置”；演练一次再上生产。

【原理溯源】

为什么必须「全量+增量」而不是一次导？ 全量导出需要时间，期间业务仍在写。只导全量会丢这段时间的变更；全量结束后立刻开始消费 binlog，才能把「导出窗口」的写追上。
为什么 binlog 是增量同步的可靠源？ binlog 记录已提交变更，格式完整（ROW 更佳）。订阅位点明确，可断点续传。工具（Canal/DTS）本质是「伪装从库」或解析 binlog，与业务解耦。
为什么要校验再切流？ 同步可能因字符集、sql_mode、触发器、外键或工具 bug 出现不一致。行数+checksum 抽样/全量对账是切流前的「门禁」，避免切过去才发现丢数。
为什么先切读再切写？ 读切换风险低，可观察延迟与正确性；写切换影响大，需确认新库可写且旧库停止写入（或双写过渡）。灰度降低爆炸半径。
回滚为什么必须保留？ 迁移失败是常态风险。旧库在观察期内保持可用，出现数据错乱/延迟失控时秒级切回。观察期结束再下线旧库。

【选型判断树】

不停机迁移方案：
├─ 数据同步手段
│   ├─ 官方/云 DTS、Canal 订阅 binlog（推荐）
│   └─ 仅停写窗口可接受 → 短暂停写迁移（简化）
├─ 是否双写？
│   ├─ 是 → 应用双写过渡，更稳但代码复杂
│   └─ 否 → 依赖 binlog 追平，需严格校验
├─ 校验
│   └─ 行数 + checksum + 业务抽查
└─ 切流
    └─ 灰度：读→观察→写；保留回滚

判断口诀： 全量打底，增量追平，校验过关，灰度切流，留好回滚。

【口述骨架】（5 分钟）

时间	任务	内容
0:00–0:30	定性	「零停机=全量+增量+校验+灰度」
0:30–1:30	全量与增量衔接	dump 后立刻 binlog 同步
1:30–2:30	校验	行数/checksum/抽查
2:30–3:30	切流	先读后写，观察 lag
3:30–4:30	回滚与兼容	旧库保留、sql_mode/字符集
4:30–5:00	收尾	「可回滚比一次性成功更重要」

【关键数字】

参数	经验值	说明
同步 lag	切流前应 <1s	视业务
观察期	数天～14 天（核心库建议留满 14 天再下线旧库）	与正文第 2 条同口径
校验	全量关键表+抽样
停写窗口	可做到秒～分钟	双写可更短
单表关注点	千万行级开始评估	非绝对魔数，看 RT/扫描/备份
连接池全局预算	实例数×池 max ≤ max_connections×0.8	预留运维连接
慢查询目标	扫描行数 < 1–5 万（在线）	返回少扫描多必优化

【追问链】（三层）

L1｜「切写瞬间丢一笔订单怎么办？」 → 切流采用短窗停写+确认同步位点，或双写+对账。事后以旧库/新库对账修复。监控写入成功与同步状态。

L2｜「为什么要关注 sql_mode 与字符集？」 → 严格模式差异会导致同一 SQL 在新库报错；字符集不一致造成乱码或排序差异。迁移前兼容性测试是必须项。

L3｜「回滚的触发条件你怎么定？」 → 数据校验失败、同步延迟失控、错误率升高、核心接口失败率超阈值。满足即回切旧库，继续排查。

【评分标准】

档位	答案特征
60 分	知道全量+增量同步
80 分	完整步骤与校验、灰度切读写
95 分	回滚预案、兼容性、双写权衡、监控 lag

【关联题】

同一知识簇： 第 59 题（主从同步）、第 78 题（备份恢复）、第 58 题（一致性思想）
工具： Canal/binlog（与缓存一致性、ES 同源）

【自测】

为什么不能只做全量导出导入？ 参考答案：导出期间业务仍在写，会丢增量；需 binlog 追平。
切流顺序为何先读后写？ 参考答案：读风险低便于观察；写切换需更谨慎并可回滚。
迁移前必做的兼容检查？ 参考答案：sql_mode、字符集排序规则、存储引擎与版本特性差异。

72. 秒杀库存只有数据库，怎么保证不超卖（库存表设计）

【考察内容】防超卖的数据库实现细节

【题目】如果不借助 Redis，只靠数据库，秒杀库存表怎么设计才能保证不超卖？扣减 SQL 怎么写（条件更新）、表结构怎么设计（版本号/库存字段）、并发性能怎么权衡？

【参考答案】库存只有数据库也想秒杀不超卖：数据库侧原子扣减设计：

表设计（步进估算）：stock(item_id, num, version) 或直接条件更新不需 version；唯一主键商品 ID。库存 1 万、峰值尝试 5 万 QPS。顺序不能反：先分段（第 3 条）或先压测测出单行热点 TPS，再定入口阈值——本题只有 1 个 item_id 一行，「DB 几千 TPS」是整库能力，单行受行锁串行化只有数百～千级；照原顺序先限 3000，1 万库存÷3000≈3.3 秒售罄、其余 4.7 万 QPS 全在被拒路径上排队，锁等待仍会把 RT 拖垮。正确答：入口阈值＝单行实测 TPS×分段数，并预留重试与查询流量，其余快速失败，这是“只有 DB”时的生存前提。
扣减：UPDATE stock SET num=num-1 WHERE item_id=? AND num>0，影响行数判断成败；批量可 WHERE num>=?。高并发热点行会有锁排队，RT 上升但正确性可保证；一人一单用唯一订单键防重复扣。
减少热点：库存分段（1000 拆 10 个子库存 100）应用层选段扣减；或对热点商品排队串行化；列表页库存可短 TTL 缓存，下单路径仍走 DB 条件更新。
失败与降级：扣减超时重试幂等；支付取消/超时关单回补库存；对账订单数与库存；限流保护主库；DB 慢时排队页而不是放开写。
口径：没有 Redis 也能不超卖，代价是吞吐受限；先保证正确，再用限流和拆热点提吞吐；后续加缓存预扣是性能优化不是正确性前提。

【原理溯源】

为什么「先查再改」会超卖？ 两事务同时读到 stock=1，各自判断「够」并 UPDATE 为 0，实际卖了两件。读与写不是原子，隔离级别不能替你把「检查+扣减」合并成一步。
为什么条件更新是原子的？ UPDATE 在存储引擎内对行加锁并求值 stock>0，不满足则不影响该行。整个「判断+修改」在行锁保护下一次完成，其他事务要么等锁要么看到新值。影响行数是权威结果。
为什么乐观锁 version 需要重试？ 并发下只有一个事务的 version=? 匹配成功，其余失败。业务要重试（可限次数）或改为条件更新避免无谓失败。
单行热点瓶颈在哪？ 所有扣减打同一行，行锁串行化，TPS 上限受单行锁与 redo 限制。库存分段把热点拆成 N 行，扣减随机/哈希选段，并行度上升；总额是各段之和，仍不会超卖。
Redis 预扣与 DB 的关系？ Redis 扛流量快速拒掉明显无效请求，DB 做最终正确性与持久化。即使 Redis 与 DB 短暂不一致，以 DB 条件更新为准并可对账。

【选型判断树】

只靠 DB 防超卖：
├─ 核心 SQL
│   └─ UPDATE ... SET num=num-1 WHERE item_id=? AND num>0
├─ 是否一人一单？
│   └─ (user_id, activity_id) 唯一索引
├─ 热点是否极高？
│   ├─ 否 → 单行即可
│   └─ 是 → 库存分段 N 片，降低行锁竞争
└─ 有 Redis？
    └─ 预扣挡流量 + DB 兜底 + 对账

判断口诀： 条件更新是底线，分段抗热点，唯一键防重复。

【口述骨架】（5 分钟）

时间	任务	内容
0:00–0:30	定性	「防超卖靠原子条件更新，不是先查后改」
0:30–1:30	SQL 论证	stock>0 条件在行锁内完成
1:30–2:30	表结构	stock、version、主键、一人一单唯一键
2:30–3:30	热点	单行锁竞争→库存分段
3:30–4:30	题干限定「只靠 DB」	先分段或压测出单行 TPS 再定入口阈值（阈值＝单行实测×分段数，并预留查询与重试）；Redis 预扣只作对比与后续演进
4:30–5:00	收尾	「把检查和扣减放进同一条原子语句」

【关键数字】

参数	经验值	说明
库存分段	10–100 段	视热点
单行 TPS	受行锁串行化，通常只有数百～千级（「数千 TPS」是整库能力，别当成单行）	入口阈值＝单行实测 TPS×分段数
影响行数	0=库存不足	业务判据
Redis TTL/预扣	与活动一致	以 DB 为准
单表关注点	千万行级开始评估	非绝对魔数，看 RT/扫描/备份
连接池全局预算	实例数×池 max ≤ max_connections×0.8	预留运维连接
慢查询目标	扫描行数 < 1–5 万（在线）	返回少扫描多必优化

【追问链】（三层）

L1｜「为什么不能 SELECT 判断后再 UPDATE？」 → 判断与更新之间有并发窗口，两事务都可通过判断，导致负库存。

L2｜「库存分段怎么保证总额不超卖？」 → 每段独立条件更新，任何一段都不能为负；总成功次数=各段成功之和，物理上不可能超过初始总额。可能出现「还有货但某段空了」的略微少卖，可通过段间转移缓解。

L3｜「扣减成功但下单后续失败，库存怎么办？」 → 预扣与订单状态绑定；订单超时未支付自动回补库存（定时任务/延迟队列），并保证回补幂等。

【评分标准】

档位	答案特征
60 分	知道用 UPDATE 条件扣减
80 分	解释原子性；version 乐观锁；一人一单
95 分	热点分段；Redis 分层；失败回补与对账

【关联题】

同一知识簇： 第 67 题（防重）、第 61 题（隔离级别）、第 58 题（扣库存一致性）、第 60 题（热点死锁）

【自测】

写出防超卖的扣减 SQL 核心条件。 参考答案：UPDATE ... SET num=num-1 WHERE item_id=? AND num>0，看影响行数。
一人一单如何约束？ 参考答案：(user_id, activity_id) 唯一索引。
为什么高并发秒杀要分段库存？ 参考答案：降低单行行锁竞争，提高并行扣减 TPS。

73. 订单表冗余了商品名，商品改名后订单还是旧名（冗余一致性）

【考察内容】反范式设计的权衡

【题目】订单表为了查询方便冗余了商品名称和用户昵称。现在商品改名、用户改名了，历史订单里冗余的旧名字要不要改？怎么改？改的代价与一致性怎么权衡？

【参考答案】订单冗余商品名，改名后旧订单仍旧名：

设计问题界定：订单是交易快照，下单时点商品名“留在旧订单”往往才是正确业务语义（发票/售后以成交时信息为准）。若产品要展示最新名，则是另一种需求，必须先定义清楚再选技术。
方案选择：① 快照语义：订单保留成交时名称，不跟随改名——默认推荐，零成本、零风险；② 跟随更新：商品改名发 MQ，异步更新历史订单冗余字段——影响行数大时要限速；③ 展示时 join 商品中心当前名（订单只存商品 ID）——实时但商品中心依赖变重、RT 上升；④ 混合：交易凭证用快照，列表展示当前名（两个字段都保留）。
容量估算（步进）：爆款改名若历史订单 2000 万行，批量 UPDATE 可能小时级并拖累主库，必须分批+限速（如每批 1000–5000 行）；MQ 扇出巨大，要分区消费与幂等；join 方案在列表 QPS 1 万时会把商品中心读放大。
失败与降级：更新失败补偿重试；join 商品超时回退快照名；审计字段保留原名与现名，便于对账与客服；批量更新可暂停。
口径：先问清业务语义是“快照”还是“同步”，不要技术选择先于业务定义；金融与合规场景优先快照。

【原理溯源】

为什么历史订单不该随商品改名而变？ 订单是交易凭证，必须反映成交时的事实。若商品名被改成最新名，历史凭证失真，客服、对账、法律场景都会出错。所以快照型冗余「不更新」是正确性要求，不是懒。
为什么还存在需要同步的冗余？ 有些冗余是「展示当前身份/状态」，例如用户中心展示最新昵称、商品卡显示当前标题。这类不更新会导致展示过时，属于同步型。关键是设计时打标，而不是事后猜。
事件驱动与 binlog 订阅有何差别？ 事件驱动显式、可携带业务语义（改名事件），依赖上游发消息；Canal 订阅 binlog 解耦业务代码，对存量系统友好，但拿到的是「行变了」需要自己翻译语义。两者都最终一致。
为什么要对账？ 消息丢失、消费失败、顺序错乱都会造成漏更新。定时对比源与冗余差异并修复，是最终一致系统的标配兜底。
为什么高频变化字段不宜冗余？ 同步成本随变更频率上升，写放大与不一致窗口变大。这类字段应联表、缓存或搜索索引，而不是宽表全冗余。

【选型判断树】

冗余字段要不要跟源一起变：
├─ 是否「交易/历史事实」？
│   └─ 是 → 快照，永不更新
├─ 是否「当前展示/统计」且查询高频？
│   ├─ 变更低频 → 冗余+事件/binlog 同步
│   └─ 变更高频 → 不冗余，联表/缓存/ES
└─ 同步失败
    └─ 重试+死信+对账

判断口诀： 事实要快照，展示才同步，高频变更别硬冗。

【口述骨架】（5 分钟）

时间	任务	内容
0:00–0:30	定性	「先分快照型 vs 同步型」
0:30–1:30	快照型	历史订单不改名的原因
1:30–2:40	同步型	MQ 事件 / Canal / 对账
2:40–3:40	设计原则	高频变化不冗余
3:40–4:30	失败处理	重试与兜底
4:30–5:00	收尾	「冗余是空间换时间，一致性要提前设计」

【关键数字】

参数	经验值	说明
同步延迟	秒级	最终一致
对账	小时/天	视业务
快照字段	商品名/价/规格	订单核心
单表关注点	千万行级开始评估	非绝对魔数，看 RT/扫描/备份
连接池全局预算	实例数×池 max ≤ max_connections×0.8	预留运维连接
慢查询目标	扫描行数 < 1–5 万（在线）	返回少扫描多必优化

【追问链】（三层）

L1｜「所有冗余都实时同步行不行？」 → 技术可做但成本高、写放大、还破坏快照语义。应按字段语义分类，不是一刀切。

L2｜「同步失败怎么发现？」 → 消费失败告警、死信队列、对账差异报表。不能只依赖「应该成功了」。

L3｜「为什么不干脆全部联表？」 → 高频列表页 join 成本高，且历史正确性仍要快照。冗余是用存储与同步复杂度换读性能，需权衡。

【评分标准】

档位	答案特征
60 分	知道冗余有一致性问题
80 分	快照 vs 同步分类；MQ/Canal
95 分	历史正确性论证；对账兜底；高频变更策略

【关联题】

同一知识簇： 第 66 题（表设计快照）、第 57 题（ES 冗余）、第 27/28 题（缓存一致性）

【自测】

历史订单商品名要不要随改名更新？ 参考答案：不要。属快照型，保持下单时事实。
同步型冗余的两种主流通道？ 参考答案：业务 MQ 事件；Canal 订阅 binlog。
为何要定时对账？ 参考答案：消息可能丢/失败，对账是最终一致兜底。

74. 单表存多少行会变慢？1000 万行必须分表吗（容量估算）

【考察内容】容量估算与分表时机判断

【题目】同事说“单表超过 1000 万行就必须分表”，你认同吗？单表性能变慢到底由什么决定（数据量、行宽、索引、查询模式）？怎么估算一张表的容量上限？什么情况下 1 亿行也没问题？

【参考答案】单表多少行变慢？1000 万不是绝对线：

原理与估算（步进）：B+ 树高度随行数增长缓慢（千万级通常 3–4 层）；行大、索引多时更早感受到维护与备份压力。经验上：小行订单类单表千万级开始关注，几千万认真评估归档/分表；大字段表可能几百万就吃力。查询变慢往往来自：索引不合适、范围扫描过大、页分裂与碎片、Buffer Pool 装不下热数据、统计信息不准。
何时必须分表：写瓶颈（单库 TPS 打满）、单表磁盘/备份窗口不可接受、DDL 时间过长、热点与查询模式需要不同物理布局、单表影响主从延迟。
替代手段优先：① 优化索引与 SQL；② 归档冷数据；③ 读写分离；④ 分区表；⑤ 水平分库分表。很多团队过早分表，运维复杂度反而吃掉收益。
失败与降级：不分表就先限流慢查询、加从库；分表要可回滚与双写校验；迁移期双读对比，出问题可切回。
口径：1000 万是运维关注点，不是性能魔数；用监控（RT、扫描行数、磁盘、DDL 时长、备份时长）驱动决策，而不是拍一个行数就动手分表。

【原理溯源】

为什么「1000 万必须分」是误导？ 它把某一经验值当铁律，忽略了 3 层 B+ 树本身就能容纳约两千万级；也忽略了点查与范围扫的天壤之别。分表有分布式 ID、跨分片查询、事务与运维成本，不能按行数触发器式执行。
点查为什么对总量不敏感？ 主键点查路径是根→内节点→叶，3 次左右 IO，与表有 100 万还是 1 亿行几乎无关（只要树高不变）。变慢通常来自冷数据不在 buffer pool 或行太宽。
范围扫与全表扫为什么对总量敏感？ 扫描页数与数据量线性相关。1 亿行全表扫当然慢；但若条件能走索引收敛到几千行，依然快。所以「怎么查」优先于「存多少」。
行宽如何影响容量？ 行越宽，一页放的行越少，同样行数占用更多页，树更高、缓存能装的行更少。大 JSON/text 应拆表或压缩。
为什么要先归档/优化再分表？ 归档能快速把工作集压回缓存友好区间；索引优化消除全表扫。这些都是低风险高收益操作，应先于高成本的架构拆分。

【选型判断树】

单表变大要不要分：
├─ 1) 先看查询模式
│   ├─ 主键/唯一键点查为主 → 大表常可继续扛
│   └─ 大量范围/分析扫 → 考虑归档或分表/数仓
├─ 2) 指标是否恶化？
│   ├─ RT/IO/锁等待随行数明显上升 → 治理
│   └─ 仍健康 → 不要为拆而拆
├─ 3) 治理顺序
│   └─ 索引 → 归档冷数据 → 读写分离 → 分库分表
└─ 4) 估算
    └─ 3 层约 2000 万级；行宽与命中率会改写阈值

判断口诀： 看指标不看玄学行数；点查耐大表，扫描怕大表。

【口述骨架】（5 分钟）

时间	任务	内容
0:00–0:30	定性	「不认同机械 1000 万；要看查询与指标」
0:30–1:30	B+ 估算	3 层约两千万级
1:30–2:40	真正变量	查询模式、行宽、命中率、写入
2:40–3:40	治理顺序	索引→归档→读写分离→分表
3:40–4:30	反例	1 亿点查可扛；1000 万乱扫会慢
4:30–5:00	收尾	「分表是架构决策，不是行数阈值」

【关键数字】

参数	经验值	说明
页大小	16KB
3 层容量	约 2000 万级	估算
舒适区	500 万–2000 万	视模式
评估分表	指标恶化且优化穷尽
单表关注点	千万行级开始评估	非绝对魔数，看 RT/扫描/备份
连接池全局预算	实例数×池 max ≤ max_connections×0.8	预留运维连接
慢查询目标	扫描行数 < 1–5 万（在线）	返回少扫描多必优化

【追问链】（三层）

L1｜「为什么点查 1 亿行也能快？」 → 树高仍是 3–4 层，IO 次数恒定；只要热数据在内存或 SSD 随机读可接受，延迟稳定。

L2｜「怎么测该不该分表？」 → 压测/灰度观察 RT、IO、锁等待随数据量增长曲线；对比归档前后。指标恶化且索引/归档无效再拆。

L3｜「老板就要按 1000 万分表，你怎么办？」 → 先给出风险与成本（跨分片、ID、运维），建议用指标驱动；若必须执行，选好分片键与迁移方案，避免按错误维度拆。

【评分标准】

档位	答案特征
60 分	知道 1000 万是经验值
80 分	解释 B+ 3 层容量；查询模式决定快慢
95 分	治理顺序；点查 vs 扫描；纠正教条阈值

【关联题】

同一知识簇： 第 53 题（B+ 树）、第 55 题（分表）、第 65 题（归档）、第 51 题（慢 SQL）

【自测】

主键点查对表总行数敏感吗？ 参考答案：不敏感，树高决定 IO 次数。
分表前应先做什么？ 参考答案：索引优化、归档、读写分离等低风险手段。
什么查询模式下大表更容易慢？ 参考答案：无索引范围扫、全表扫、大排序分组。

75. 一次性导入 100 万条数据，怎么最快（批量写入优化）

【考察内容】写入性能优化实操

【题目】运营要导入 100 万条商品数据，单条 INSERT 循环插入要跑几个小时。怎么优化（批量 insert、事务分批、去掉不必要索引、load data、并行）？各自的收益和风险是什么？

【参考答案】一次性导入 100 万条：批量、减索引、控事务：

容量估算（步进）：口径要先对齐——批量/本机 socket 下单条 INSERT 约 50–100µs；但逐条 + autocommit 时每条都要一次 fsync + RTT，实测约 1–3ms/条，100 万条就是 17–50 分钟量级（1e6 × 1~3ms = 1000~3000s），这才是"逐条很慢"的真实来源。题干说的"要跑几个小时"对应更差的一档：DB 在跨机房/云外、单条 RTT 10–30ms 时，1e6 × 10~30ms = 2.8~8.3 小时——两档都指向同一结论：逐条插入不可接受。批量 INSERT ... VALUES (...),(...) 每批 500–1000 条（受 max_allowed_packet 与锁范围约束），配合本地事务，吞吐可到 数万行/秒～十万级（视索引与硬件）。目标：从这 17–50 分钟压到 几分钟内。
手段：① 批量插入/LOAD DATA INFILE；② 导入期关闭非唯一辅助索引，导完重建（或延迟索引）；③ 单事务分批提交，避免超大事务；④ 并行导入注意主键无冲突与分库路由；⑤ 关闭唯一检查仅在安全场景，慎用。
优先 LOAD DATA：纯文件导入通常比 JDBC 快一个数量级。
失败与降级：失败从断点续导（记录进度表）；磁盘与主从延迟监控；高峰禁导；校验总行数与抽样 checksum；大表 DDL/建索引低峰。
口径：导入优化三板斧——批量、减索引、拆事务；同时保证可重入可校验。

【原理溯源】

为什么单条 INSERT 慢？ 每条 SQL 都要：网络往返、解析、优化、执行、写 redo、（默认）fsync。固定开销 × 100 万次被放大。批量把 N 次固定开销摊到 1 次。
为什么事务要分批而不是一个巨大事务？ 单事务 100 万行 undo 巨大、锁时间长、主从延迟大、失败回滚代价高；逐条提交则 fsync 过多。每批 500–1000 是工程折中。
LOAD DATA 为什么快？ 它走轻量解析路径，接近顺序写存储，比逐行 INSERT 省去大量 SQL 层开销，官方场景常快一个数量级。
为什么建议按主键顺序插入？ 顺序主键让新行追加到页尾，减少页分裂与随机 IO；乱序 UUID 会导致频繁分裂与碎片。
为什么可以临时去掉二级索引？ 每插入一行要维护所有二级索引 B+ 树。导入完成再 CREATE INDEX，往往比边插边维护更快（但要评估窗口与锁）。
风险提醒： 调 innodb_flush_log_at_trx_commit=0/2 在宕机时可能丢事务；生产导入要评估是否可接受，结束后立即恢复默认。

【选型判断树】

大批量导入：
├─ 首选工具
│   └─ LOAD DATA / 并行导数工具 > 应用循环 INSERT
├─ 若必须应用导入
│   ├─ 批量 VALUES 500–1000
│   ├─ 每批一事务
│   └─ JDBC rewriteBatchedStatements=true
├─ 索引
│   ├─ 可停窗口 → 后建二级索引
│   └─ 在线索入 → 保持索引，控制并发
├─ 顺序
│   └─ 尽量主键顺序，减少页分裂
└─ 参数
    └─ 可临时调整 flush 策略，评估丢数风险后恢复

判断口诀： 能 LOAD 就 LOAD；必须 INSERT 就批量分事务；索引能后建就后建。

【口述骨架】（5 分钟）

时间	任务	内容
0:00–0:30	定性	「批量写入要摊薄固定开销，避免大事务与页分裂」
0:30–1:30	为什么单条慢	往返/解析/fsync × N
1:30–2:40	组合拳	批量+分事务+顺序主键+后建索引
2:40–3:30	工具	LOAD DATA
3:30–4:30	风险	大事务、参数激进丢数
4:30–5:00	收尾	「正确工具 + 正确批大小」

【关键数字】

参数	经验值	说明
批大小	500–1000 行	按行宽调
LOAD DATA	可比逐条快 10 倍+
flush 策略	默认 1 最安全	0/2 有丢数风险
并行	4–8 线程常见	防锁竞争
单表关注点	千万行级开始评估	非绝对魔数，看 RT/扫描/备份
连接池全局预算	实例数×池 max ≤ max_connections×0.8	预留运维连接
慢查询目标	扫描行数 < 1–5 万（在线）	返回少扫描多必优化

【追问链】（三层）

L1｜「为什么不能一个事务插 100 万？」 → undo 膨胀、长锁、主从延迟、失败回滚慢，故障爆炸半径大。

L2｜「在线业务期间能删索引导入吗？」 → 通常不行，影响线上查询。可在低峰或影子表导入再切换。

L3｜「JDBC 批量为什么还要 rewriteBatchedStatements？」 → 否则驱动可能仍发单条 SQL；开启后才会改写为多值 INSERT，真正吃到批量收益。

【评分标准】

档位	答案特征
60 分	知道用批量插入
80 分	批量+分事务+顺序；提到 LOAD DATA
95 分	后建索引、flush 参数风险、并行与页分裂

【关联题】

同一知识簇： 第 62 题（大事务）、第 53 题（页分裂）、第 65 题（迁移归档）

【自测】

每批多少行较合适？ 参考答案：常见 500–1000，结合行宽与锁压测。
LOAD DATA 相对循环 INSERT 的优势？ 参考答案：轻量解析与顺序写路径，固定开销大幅下降。
乱序 UUID 主键导入有何问题？ 参考答案：频繁页分裂与碎片，写性能下降。

76. EXPLAIN 显示回表太多，查询慢（覆盖索引优化）

【考察内容】索引执行计划深度

【题目】一条查询很慢，EXPLAIN 显示需要大量回表。什么是回表？什么是覆盖索引？怎么通过调整索引让查询免回表？哪些查询天然适合覆盖索引？

【参考答案】EXPLAIN 回表太多导致慢：上覆盖索引/改写查询：

机制（步进估算）：二级索引找到主键后还要回表查聚簇索引，若命中 100 万行，100 万次随机回表，RT 可到秒级；覆盖索引后只扫描二级索引，顺序 IO，可能降到 几十～几百 ms，性能差 1–2 个数量级。
判断：EXPLAIN Extra 出现 Using index 说明覆盖；否则看 rows 很大且筛选列多来自少数列，且 type 为 ref/range 但仍然慢，多半是回表放大。
手段：① 建覆盖索引 (user_id, create_time, status, amount) 等把查询列包进去；② 只查需要的列，禁止 SELECT *；③ 延迟关联：先二级索引取主键再回表少量行；④ 必要时汇总进 ES/汇总表，OLTP 只服务点查。
代价与容量：覆盖索引写放大、占磁盘；按查询频率建最关键的几个；亿级表上大回表查询禁止直接暴露给 C 端，必须限流或走分析库。
失败与降级：紧急限流该接口；上线索引监控命中与回滚；业务强制走覆盖路径，查询列白名单；新索引未生效时降级为近N天查询。

【原理溯源】

回表为什么慢？ 二级索引给你主键后，还要按主键再查一次聚簇索引才能拿整行。若命中行分散，就是多次随机 IO。行数一大，回表成本线性放大，甚至超过扫描本身。
覆盖索引为什么能免回表？ 查询所需列都已在二级索引叶子中，引擎直接返回，不再访问聚簇索引。Extra 的 Using index 表示「Using index only」——只用索引。
为什么 SELECT * 常破坏覆盖？ 星号把所有列都纳入「需要的列集合」，而索引通常只包含少数列，必然回表。改成明确列清单，覆盖才可能成立。
ICP 与覆盖的区别？ ICP（Using index condition）在引擎层用索引过滤掉不满足的行，减少回表次数，但最终仍要回表取其他列；覆盖是零回表。一个减次数，一个消路径。
为什么索引不能无限加宽？ 每列都进索引会增大二级索引体积、拖慢写入与维护，还可能让优化器选择变差。只覆盖高频窄查询。

【选型判断树】

回表太多：
├─ 是否可只取索引已有列？
│   └─ 是 → 改 SELECT 列清单，争取 Using index
├─ 高频查询列是否固定少数？
│   └─ 是 → 建合适联合索引覆盖
├─ 是否只是过滤后仍要宽行？
│   └─ ICP 减少回表次数，或延迟关联
└─ 索引过宽？
    └─ 评估写放大，只保留高频覆盖

判断口诀： 先瘦 SELECT，再谈覆盖；能覆盖就覆盖，不能就减回表次数。

【口述骨架】（5 分钟）

时间	任务	内容
0:00–0:30	定性	「回表=二级索引再查聚簇；覆盖=不回表」
0:30–1:30	机制	二级索引存主键；聚簇存整行
1:30–2:40	优化	改列清单+联合索引覆盖
2:40–3:30	ICP 对比	减次数 vs 零回表
3:30–4:30	代价	索引变宽写变慢
4:30–5:00	收尾	「Using index 是目标信号」

【关键数字】

参数	经验值	说明
覆盖标志	Extra: Using index
ICP 标志	Using index condition	仍回表
回表	每行约一次聚簇点查	量大即慢
单表关注点	千万行级开始评估	非绝对魔数，看 RT/扫描/备份
连接池全局预算	实例数×池 max ≤ max_connections×0.8	预留运维连接
慢查询目标	扫描行数 < 1–5 万（在线）	返回少扫描多必优化

【追问链】（三层）

L1｜「主键查询为什么天然覆盖？」 → 聚簇索引叶子就是整行数据，无需再去别处取列。

L2｜「把所有查询列都塞进索引可以吗？」 → 不建议。索引膨胀、写放大、维护成本高。只覆盖真正高频的窄查询。

L3｜「Using index condition 和 Using index 有何不同？」 → 前者是索引下推过滤后仍可能回表；后者是覆盖索引，完全不回表。

【评分标准】

档位	答案特征
60 分	知道回表概念与建联合索引
80 分	覆盖定义、Using index、避免 SELECT *
95 分	与 ICP 区分；索引宽度权衡；主键天然覆盖

【关联题】

同一知识簇： 第 54 题（SQL 优化）、第 77 题（最左前缀）、第 52 题（失效）、第 51 题（慢 SQL）

【自测】

覆盖索引的 EXPLAIN 标志？ 参考答案：Using index。
ICP 是否等于不回表？ 参考答案：否，ICP 减少回表次数，覆盖才是零回表。
为何 SELECT * 不利于覆盖？ 参考答案：需要全部列，超出索引范围，必须回表。

77. 联合索引 (a,b,c) 有的查询用不上，字段顺序怎么排（最左前缀）

【考察内容】联合索引是最左前缀与索引设计的核心考题

【题目】表上有联合索引 (a, b, c)，但慢查询日志里仍有查询没走索引。哪些查询条件下索引会生效、哪些会失效？设计联合索引时字段顺序按什么原则排（区分度、查询频率、范围字段放哪）？

【参考答案】联合索引 (a,b,c) 有的查询用不上：最左前缀：

规则：查询条件必须从最左列连续使用，才能用上索引前缀；跳过 a 只有 b、或 a 用了范围后再想高效用 c 可能受限（范围后列多用于过滤/覆盖，排序能力受限）。
例子：WHERE a=1 AND b=2 AND c=3 全用上；WHERE b=2 AND c=3 用不上；WHERE a=1 AND c=3 可能只用到 a；WHERE a IN (...) AND b=? 视优化器；ORDER BY a,b 在左侧等值匹配时可避免 filesort。
设计建议（步进）：等值列在前、范围列在后；高区分度列靠前并非绝对，以真实 SQL 频次与业务访问模式为准；不同访问模式可能需要多个索引或冗余索引表；上线前用慢 SQL 预演验证。
容量：区分度低的列放最左意义不大；索引不是越多越好，写入成本随索引数上升；联合索引列数控制在 2–4 个常用列内（再多写放大明显）。
失败与降级：历史 SQL 与新索引不匹配时改写 SQL 或补索引；生产加索引用 online DDL 低峰执行；监控慢 SQL 变化与索引命中率。
口径：联合索引像电话簿排序键，不按前缀查就无法二分定位；先列访问模式再设计索引顺序。

【原理溯源】

为什么有最左前缀？ 联合索引先按 a 全局有序，a 相同再按 b，再按 c。跳过 a 时，b/c 在树上不全局有序，无法二分定位。这是 B+ 树多列有序的直接推论。
为什么范围条件之后的列用不上定位？ a=1 AND b>5 AND c=3 中，b 是范围，c 在「b 的每个取值段内」有序，但无法在整棵树上先按 c 收缩。优化器通常只把范围前的等值列用于定位，范围列用于过滤/扫描。
为什么「优化器会调整条件顺序」？ WHERE b=2 AND a=1 与 WHERE a=1 AND b=2 在逻辑上等价，优化器能重排，所以仍可走 (a,b,c)。失效的是「缺列」而不是「书写顺序」。
区分度为何重要？ 第一列选择性越高，定位区间越小。把 sex（2 值）放最前，几乎退化为大范围扫描；user_id 放最前才能快速收敛。
排序为何吃最左前缀？ WHERE a=1 ORDER BY b 时，在 a 定值段内 b 有序，可免 filesort。若 ORDER BY c 且缺 b，则无法利用索引序。

【选型判断树】

联合索引设计：
├─ 从高频 SQL 反推列
│   └─ 等值列在前，高区分度优先
├─ 范围列
│   └─ 放等值之后；范围后列难定位
├─ 排序列
│   └─ 尽量纳入索引尾部，吃有序性
├─ 查询只用到中间列？
│   └─ 失效或只用前缀；补最左列或另建索引
└─ 控制宽度
    └─ 2–4 列常见，防写放大

判断口诀： 等值在前，范围靠后；「高区分度打头」只是常见情形，最终以真实 SQL 频次与访问模式为准（正文已条件化），排序跟尾。

【口述骨架】（5 分钟）

时间	任务	内容
0:00–0:30	定性	「最左前缀是 B+ 多列有序的必然」
0:30–1:40	生效/失效清单	用 (a,b,c) 举例
1:40–2:40	排序与分组	ORDER BY/GROUP BY 吃前缀
2:40–3:40	设计原则	等值、范围、区分度
3:40–4:30	误区	书写顺序 vs 缺列；优化器重排
4:30–5:00	收尾	「按真实 SQL 设计，不按表结构堆列」

【关键数字】

参数	经验值	说明
联合索引列数	常 2–4
选择性	>10%–20% 更适合前置	业务相关
一个查询可用索引	通常一个（index merge 例外）
单表关注点	千万行级开始评估	非绝对魔数，看 RT/扫描/备份
连接池全局预算	实例数×池 max ≤ max_connections×0.8	预留运维连接
慢查询目标	扫描行数 < 1–5 万（在线）	返回少扫描多必优化

【追问链】（三层）

L1｜WHERE b=2 AND a=1 能走 (a,b,c) 吗？ → 能。优化器重排条件，只要等价包含最左前缀即可。

L2｜a=1 AND b>2 AND c=3 用到 c 做定位吗？ → 通常不能把 c 用于继续收缩定位（范围之后）；可能作为过滤（ICP）减少回表。

L3｜「为什么范围字段建议放后面？」 → 等值列可把区间收得很小，范围列在其上扫描；若范围在前，后续列难以再二分定位，易变成较大范围扫。

【评分标准】

档位	答案特征
60 分	知道最左前缀
80 分	完整生效/失效判断；等值+范围+排序设计
95 分	解释树上有序；优化器重排；范围后列定位限制

【关联题】

同一知识簇： 第 52 题（失效）、第 54 题（实战）、第 76 题（覆盖）、第 53 题（B+）

【自测】

WHERE c=3 能否用 (a,b,c)？ 参考答案：不能作为定位（缺最左前缀）。
a=1 ORDER BY b 为何可能免 filesort？ 参考答案：a 定值段内 b 有序，索引直接提供顺序。
范围查询列应放在联合索引什么位置？ 参考答案：等值列之后；范围之后的列难以继续定位。

78. 误删数据/服务器损坏，怎么把数据救回来（备份与恢复）

【考察内容】数据安全与容灾

【题目】半夜有人误执行 DELETE 没带 WHERE，几千万行数据没了。你负责数据安全：平时备份策略怎么定（全量/增量/binlog）？发生误删后按什么步骤恢复到误删前的状态？恢复时长怎么控制？

【参考答案】误删数据/服务器损坏的恢复：备份 + 延迟/闪回 + 演练：

容量与指标（步进）：先定义 RPO/RTO：如 RPO≤5 分钟（最多丢 5 分钟数据）、RTO≤30 分钟（多久恢复服务）。假设数据 500GB，全量恢复+增量应用可能 30–60 分钟，要靠从库热备把 RTO 压短。备份保留：日备 7 天+周备 4 周+归档更久，按合规要求定；备份存储按数据量×保留份数估算：按本句口径（日备 7 份 + 周备 4 份）≈ 11 份全量 = 500GB×11 ≈ 5.5TB；若合规要求日备保留 30 天，才是 500GB×30 ≈ 15TB——两个数对应两种保留策略，别混用。
体系：① 全量物理备份（xtrabackup）+ binlog 增量；② 实时从库/跨机房副本，误删可从延迟从库抢救，但延迟窗口必须大于「最坏发现时延」：题干是半夜误删、次日才发现（6–8 小时），故意设 1 小时的延迟从库早已重放完 DELETE，抢救为空。工程做法：延迟窗口按发现链路实测取 12–24 小时或多档延迟从库；主路径仍是全量备份 + binlog 重放到误删时刻前（或跳过该事务），延迟从库只作最快的第二兜底；③ 云数据库快照与闪回；④ 逻辑导出用于点查恢复。
误删应急：停止写相关表 → 评估影响范围与时间点 → 从备份/延迟从库定位删除前数据 → 灰度回灌（注意唯一键与业务状态机）→ 对账验证行数与金额。
失败与降级：备份损坏 → 异地多副本；恢复演练失败暴露问题比真正事故时才发现好；恢复期间业务只读/限流；关键表恢复要有书面步骤与审批。
口径：没演练过的备份等于没有备份；恢复能力要定期在非核心库真实演练，记录实际 RPO/RTO，而不是纸面指标。

【原理溯源】

为什么「全量+binlog」是标准答案？ 全量提供基线，binlog 提供「任意时间点」前滚能力。只靠全量会丢两次备份之间的变更；只靠 binlog 没有完整基线。两者组合才能恢复到误删前一刻。
为什么可以跳过误删语句？ binlog 按事务顺序记录。恢复时重放到 T 之前，对误删事务做 skip（或用工具生成反向 SQL），即可回到「删了但还没执行」的状态。前提是 binlog 完整且保留期足够。
为什么延迟从库是「后悔药」？ 从库按设定延迟重放主库日志，误删发生在「延迟窗口之内」时从库尚未执行该 DELETE，可立即从从库抢救或切换。但窗口必须大于「最坏发现时延」：题干是半夜误删、次日才发现（6–8 小时），故意设 1 小时的延迟从库早已把 DELETE 重放完，抢救为空。所以它是第二兜底而非主路径——主路径仍是全量备份＋binlog 重放到误删时刻前（或跳过该事务）；延迟窗口按发现链路实测取 12–24 小时或多档从库。代价：该从库不能提供「最新读」。
为什么必须演练恢复？ 备份文件损坏、权限不足、版本不兼容、binlog 被 purge，都会导致「有备份但救不回」。演练是唯一验证手段。
RPO/RTO 为什么是设计起点？ RPO=最多能丢多少数据，决定备份频率与是否半同步；RTO=多久恢复，决定物理备份、从库热备、自动化程度。先定目标再选工具，避免过度建设或不足。

【选型判断树】

备份与恢复体系：
├─ 定目标
│   ├─ RPO 分钟级/0 → 半同步 + 频繁 binlog
│   └─ RTO 分钟级 → 物理备份/热备从库
├─ 日常
│   ├─ 每日全量 + 连续 binlog + 异地
│   └─ 恢复演练周期化
├─ 误删发生
│   ├─ 停写/止血
│   ├─ 定位 T
│   ├─ 全量恢复 + binlog 重放到 T 前
│   ├─ 跳过误删 / 闪回
│   └─ 校验切回
└─ 预防
    ├─ 权限与高危审核
    └─ 延迟从库

判断口诀： 先 RPO/RTO，再全量+binlog，演练才算数。

【口述骨架】（5 分钟）

时间	任务	内容
0:00–0:30	定性	「备份体系=RPO/RTO 驱动」
0:30–1:30	日常策略	全量+binlog+异地+演练
1:30–2:40	误删步骤	定 T、全量恢复、binlog 重放跳过
2:40–3:30	预防	权限、审核、延迟从库、闪回
3:30–4:30	指标	RPO vs RTO
4:30–5:00	收尾	「没演练过的备份等于没有」

【关键数字】

参数	经验值	说明
全量频率	每天	低峰
binlog	持续/至少覆盖全量间隔
延迟从库	窗口必须 > 最坏发现时延（半夜误删、次日发现＝6–8 小时，故取 12–24 小时或多档延迟从库）	30min–1h 这类短窗口对「隔天才发现」根本救不到
演练	每季度/每月	视等级
RPO 金融	≈0	半同步等
单表关注点	千万行级开始评估	非绝对魔数，看 RT/扫描/备份
连接池全局预算	实例数×池 max ≤ max_connections×0.8	预留运维连接
慢查询目标	扫描行数 < 1–5 万（在线）	返回少扫描多必优化

【追问链】（三层）

L1｜「只有全量备份没有 binlog，最多能恢复到哪？」 → 只能恢复到最近一次全量完成时刻，之后的变更会丢。

L2｜「如何恢复到误删前一秒？」 → 全量恢复到 T0，再重放 binlog 至误删事务之前（skip 误删或闪回生成反向 SQL），校验后切换。

L3｜「RPO 和 RTO 怎么影响架构？」 → RPO 小要求更频繁备份/半同步；RTO 小要求物理备份、热备、自动化。二者不可兼得时按业务优先级取舍。

【评分标准】

档位	答案特征
60 分	知道全量+binlog
80 分	完整恢复步骤与演练
95 分	RPO/RTO；延迟从库/闪回；权限与审核预防

【关联题】

同一知识簇： 第 71 题（迁移）、第 59 题（复制）、第 62 题（大事务导致延迟）
运维： 第 70 题（故障处置习惯）

【自测】

误删恢复的两大支柱是什么？ 参考答案：最近全量备份 + 之后的 binlog 重放（跳过误删）。
RPO 与 RTO 分别指什么？ 参考答案：RPO 最多可丢多少数据；RTO 多久恢复服务。
延迟从库的作用？参考答案：只在「误操作发生在延迟窗口之内、从库尚未重放到该条」时才可作为抢救数据源或切换目标；题干那种半夜误删、次日才发现的情况，1 小时档延迟从库早已重放完，主路径要走全量备份＋binlog 重放到误删时刻前。