第三章 数据库（MySQL）场景（第51-78题）
本章导览（元问题与表面主题逐字取自 《核心考点与元理论》§六）
- 本章真正考的那一个问题： 单机状态存储的正确性与扩展边界在哪？
- 表面主题： 索引、事务、分库分表
- 题号与题量： 第 51–78 题，共 28 题；刷题时不要按知识点记，要按上面那一个元问题记。
本章通用基线（原随题逐题重复，现收敛于此一张表；各题【关键数字】只列本题特有数字，涉及下表口径的以本表为准）
| 关注项 | 基线 | 判定 |
|---|---|---|
| 单表关注点 | 千万行级开始评估 | 非绝对魔数，看 RT/扫描/备份 |
| 连接池全局预算 | 实例数×池 max ≤ max_connections×0.8 | 预留运维连接 |
| 慢查询目标 | 扫描行数 < 1–5 万（在线） | 返回少扫描多必优化 |
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 要灰度。
验证：优化后重跑 EXPLAIN 比对 type/rows/Extra（filesort、Using temporary 应消失），再观察 15–30 分钟慢日志与 P99 曲线；还要排除「流量下降造成的假好转」——同窗口的 QPS 也要看。
预防：SQL 审核、慢查询大盘、扫描行数告警（>5 万即告警、>10 万按高危处理；与本题【关键数字】「在线扫描行数 <1–5 万」是同一条线的两档）。
【原理溯源】
为什么必须先抓 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 分钟；框架 D，骨架共性见文首第二节，本题只写特定内容）
| 时间 | 任务 | 内容 |
|---|---|---|
| 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 快速失败；MDL 等待另有 lock_wait_timeout，两个参数别混 |
| 优化验证窗口 | 上线后观察 15–30min 慢日志 | 对比优化前后 P99 |
【追问链】（三层）
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 分钟；框架 D，骨架共性见文首第二节，本题只写特定内容）
| 时间 | 任务 | 内容 |
|---|---|---|
| 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 | 应急手段 | 升级/统计变化后可能变差 |
【追问链】（三层）
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 分钟；框架 A，骨架共性见文首第二节，本题只写特定内容）
| 时间 | 任务 | 内容 |
|---|---|---|
| 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 万+ 行 | 算法：1000 扇出 → 第二层 1000 个叶子页组，叶页每页装 16KB÷行宽 ≈10~20 行 → 1000×1000×10~20 ≈ 1e7~2e7 行；与行宽、页填充率直接相关 |
| 5000 万行树高 | 通常仍 3–4 层 | 不是「树高爆了」 |
| 红黑树千万级树高 | 20+ | 每层一次 IO 不可接受 |
| 等值哈希 | O(1) | 无范围能力 |
【追问链】（三层）
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 题数据结构选型）
【自测】
- 判断对错：订单 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（先查
information_schema.innodb_trx无长事务；调小lock_wait_timeout——它管 MDL 等待，不是行锁的innodb_lock_wait_timeout；见第 52 题）；业务可先只查近 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 分钟；框架 D，骨架共性见文首第二节，本题只写特定内容）
| 时间 | 任务 | 内容 |
|---|---|---|
| 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 并列漏行 |
【追问链】（三层）
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 亿行；单库磁盘容量、备份与 DDL 窗口、写峰值都会到顶（树高只从 3 层升到 4 层，不是主因，见第 53 题）。写峰值 5000 TPS（口径含大促/营销脉冲；按 5 亿年单摊只有约 16 TPS、日常峰值百级），单库写上限约 2000–5000 TPS，脉冲档写上先不够用；读若按用户维度，分片后每库可回到可接受 QPS。
- 拆法：① 垂直拆：订单主表/订单明细/扩展信息分表分库；② 水平拆：按
user_id分库（用户查询友好）或order_idhash；范围查询/商家维度另建索引表或同步到分析库；③ 历史归档：热库只留 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 分钟；框架 B，骨架共性见文首第二节，本题只写特定内容）
| 时间 | 任务 | 内容 |
|---|---|---|
| 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 变更会重哈希 |
【追问链】（三层）
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 分钟；框架 D，骨架共性见文首第二节，本题只写特定内容）
| 时间 | 任务 | 内容 |
|---|---|---|
| 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) | 防并列漏重 |
【追问链】（三层）
L1｜「游标分页有什么缺点？」 → 不能随机跳页，只能「上一页/下一页」或无限滚动；前端需保存 last_id；排序键变化或过滤条件动态时状态要对齐。适合信息流，不适合「跳到第 300 页」的管理后台（可结合导出/筛选）。
L2｜「延迟关联到底省了什么？没省什么？」 → 省了深分页时对大量行的回表与宽列传输；没省「沿索引移动 offset」的扫描本身。所以它优于裸 LIMIT，劣于游标。
L3｜「用户就是要跳到第 5000 页做导出，怎么办？」 → 不要走在线 OLTP 深分页。改为：①异步导出任务（流式/分批扫）；②走数仓或只读副本；③条件收敛（时间范围）后导出。在线列表限制深度，导出走离线通道。
【评分标准】
| 档位 | 答案特征 |
|---|---|
| 60 分 | 知道 offset 大会慢，会说游标分页 |
| 80 分 | 解释扫描丢弃；对比游标与延迟关联适用场景 |
| 95 分 | 指出并列值导致游标漏/重及复合游标；产品限深与异步导出；区分延迟关联「省回表不省扫描」 |
【关联题】
【自测】
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 分钟；框架 B，骨架共性见文首第二节，本题只写特定内容）
| 时间 | 任务 | 内容 |
|---|---|---|
| 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 |
| 广播聚合 | 仅低频/小结果 | 高频禁止 |
| 映射写入 | 与订单同事务或本地消息表 | 防映射丢失 |
【追问链】（三层）
L1｜「为什么不用中间件自动把商家查询路由好？」 → 中间件只有分片键路由能力；商家不是分片键时只能广播或依赖额外索引结构。「自动」解决的是工程接入，不是物理上「不用遍历」。
L2｜「映射表自己也几亿行，怎么办？」 → 映射表可按 order_no 再 Hash 分片（本身是点查）；热映射加 Redis；或把映射信息编进单号（基因法）省掉一张表。
L3｜「ES 与 MySQL 订单数据不一致，客服查到的状态和库不一样，怎么处理？」 → 分层：展示/筛选以 ES 为准可容忍秒级滞后；涉及金额、状态变更以 MySQL 为准。监控同步 lag，失败重试+死信+定时对账；严重不一致时触发重建索引。
【评分标准】
| 档位 | 答案特征 |
|---|---|
| 60 分 | 知道要映射表或 ES，承认非分片键查询难 |
| 80 分 | 点查映射、分析 ES、离线数仓分场景；说明广播代价 |
| 95 分 | 给出基因法适用边界；映射一致性与自身分片；ES/MySQL 一致性分层；拆分前规划旁路 |
【关联题】
【自测】
- 客服只有 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 分钟；框架 B，骨架共性见文首第二节，本题只写特定内容）
| 时间 | 任务 | 内容 |
|---|---|---|
| 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 | 资金/库存建议更勤 |
| 消息最终一致窗口 | 通常秒级～分钟级 | 视重试与故障 |
【追问链】（三层）
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_Source。压力大时延迟从毫秒级涨到秒级甚至分钟级。为什么「写后读主库」是工程首选？ 它不改复制协议，只改路由：同一请求链路内打标，读路由到主库，避开延迟窗口。代价是主库读压力上升，所以只对「写后马上读」的关键路径生效，而不是所有读。
半同步为什么不能根治？ Semi-sync 要求至少一个从库接收 binlog（或 ACK commit）后主库才返回，显著降低「主库宕机丢已提交事务」的概率；但不缩短从库回放延迟（ACK 只代表收到 binlog/写入 relay log），慢从库的 Seconds_Behind_Source 照涨；但网络分区/从库全挂时可能退化为异步，且写 RT 上升。它改善的是持久化（不丢已提交写），既不缩短从库回放延迟，更不是把复制变成强同步读。
为什么从库延迟要监控并摘流量？ 延迟大时从库读到的是历史。超过业务可容忍窗口（如 5s）就该把该从库从读池摘掉，避免「越慢越读、越读越慢」的正反馈，也避免用户看到严重过期数据。
【选型判断树】
写后读不到，按路径选择：
├─ 是否「写后立即读」关键路径（下单结果/支付状态/余额）？
│ └─ 是 → 路由主库（hint/注解/中间件），或写后短窗读主
├─ 能否接受秒级旧读？（列表/统计/详情展示）
│ └─ 可以 → 继续读从库 + 延迟监控
├─ 主挂丢数据风险是否不可接受？
│ └─ 是 → 半同步 / MGR 多数派
├─ 从库是否总是落后？
│ ├─ 大事务/无主键 → 治理（第 62 题）
│ └─ 重放慢 → 并行复制 / 升级从库硬件
└─ 极端：所有读都要像主库一样新
└─ 要么不拆读写（只读主），要么接受性能代价判断口诀： 关键读回主，延迟要监控，半同步降概率，根治靠路由策略。
【口述骨架】（5 分钟；框架 D，骨架共性见文首第二节，本题只写特定内容）
| 时间 | 任务 | 内容 |
|---|---|---|
| 0:00–0:30 | 定性 | 「读写分离是最终一致架构，写后读主是关键路径解法」 |
| 0:30–1:20 | 根因 | 异步复制 + 从库重放延迟 |
| 1:20–2:40 | 方案组合 | 写后读主 / 半同步 / 缓存补偿 / 摘延迟从库 / 并行复制 |
| 2:40–3:40 | 取舍 | 主库压力 vs 一致性；半同步 RT；哪些读可旧 |
| 3:40–4:30 | 监控 | Seconds_Behind_Source 阈值与自动摘除 |
| 4:30–5:00 | 收尾 | 「不是消灭延迟，而是让延迟不出现在关键路径」 |
【关键数字】
| 参数 | 经验值 | 说明 |
|---|---|---|
| 正常复制延迟 | 毫秒级 | 空载/低负载 |
| 压力下延迟 | 秒级甚至更高 | 大事务/网络时明显 |
| 摘从库阈值 | 常见 >5s 告警摘除 | 按业务容忍度 |
| 写后读主窗口 | 同请求链路或数百 ms–数s | 实现用 ThreadLocal/hint |
| semi-sync | 至少 1 从库 ACK | 写 RT 上升 |
| 并行复制 | 8–16 worker 常见 | 视版本与表冲突 |
【追问链】（三层）
L1｜「如何让某条查询强制走主库？」 → 应用层 hint：MyBatis 拦截器/注解、ShardingSphere HintManager、SQL 注释标记、按接口/表配置主库路由。下单详情接口默认主库。
L2｜「半同步会不会把主库拖死？」 → 会增加写 RT（等从库 ACK）。从库超时可降级异步保可用。需监控 ACK 超时与退化次数；不是把从库变成同步备机。
L3｜「用户就是投诉「刚支付完余额不对」，你线上怎么快速处置？」 → 先让该用户关键读临时切主库/缓存读写路径；查该从库延迟与是否异常；确认无数据丢失后再恢复读写分离。事后把支付结果页默认读主，并缩短缓存 TTL 或删除补偿。
【评分标准】
| 档位 | 答案特征 |
|---|---|
| 60 分 | 知道有主从延迟，会说读主库 |
| 80 分 | 组合方案：写后读主、半同步、延迟监控；说清异步复制根因 |
| 95 分 | 区分「丢数据 vs 读旧数据」；路由 hint 落地；半同步退化与代价；大事务加剧延迟 |
【关联题】
- 同一知识簇： 第 58 题（跨库一致性思想）、第 62 题（大事务拉大延迟）、第 78 题（binlog/备份）
- 缓存侧： 第 27/28 题（缓存一致性）、写后补偿缓存
- 对比： Redis 主从延迟（第 46 题，同构问题不同引擎）
【自测】
- 下单成功页应读主库还是从库？为什么？ 参考答案：读主库。写后立即读，从库可能未追上，用户会看到「单没了」。
- 判断对错：开了半同步复制就一定不会读到旧数据。 参考答案：错。半同步主要保证主库提交时至少一个从库有日志；读从库仍可能延迟，且从库故障时可退化。
- Seconds_Behind_Source 持续变大，优先排查什么？ 参考答案：大事务、无主键/无索引更新导致的行锁冲突、从库硬件与网络、是否单线程重放（开并行复制）。
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）；② 索引缺失 → UPDATE 给扫描到的每一行逐行加锁（RR 下还带 gap/next-key），锁定集合近似全表；③ 事务过大持锁久；④ 隔离级别 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 要扫全表并给扫到的每一行加锁，锁定集合接近全表（InnoDB 是逐行加锁，不存在「升级为表锁」）；有索引则只锁精确命中的行与少量间隙。锁定集合越小、持有时间越短，成环概率越低。
- 为什么统一顺序比「加大锁超时」更根本？ 超时与重试是缓解症状；顺序统一是消灭环的形成条件。代码层约定（如按 id 排序更新）成本低、效果稳。
【选型判断树】
出现 Deadlock，按层处置：
├─ 1) 取证：SHOW ENGINE INNODB STATUS / performance_schema
│ └─ 看两个事务 SQL、锁类型、等待环
├─ 2) 归类
│ ├─ 加锁顺序不一致 → 统一资源访问顺序（代码规约）
│ ├─ 事务过长（RPC/循环）→ 拆事务，RPC 出事务（第 62 题）
│ ├─ 锁范围过大（无索引/全表更新）→ 补索引、改点查
│ └─ 间隙锁冲突（RR）→ 评估降 RC 或改等值/唯一索引访问
├─ 3) 工程兜底
│ └─ 捕获 deadlock 异常有限次重试 + 监控死锁 QPS
└─ 4) 预防
└─ 压测复现、code review 加锁顺序、告警判断口诀： 先看现场再归类，首选统一顺序，RC 与索引降冲突面。
【口述骨架】（5 分钟；框架 D，骨架共性见文首第二节，本题只写特定内容）
| 时间 | 任务 | 内容 |
|---|---|---|
| 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 显著更高 | 降级需评估业务 |
| 取证保留 | 最近一次死锁 | 要持久化日志别只看一次 |
【追问链】（三层）
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 间隙锁角色；重试与监控；热点行治理 |
【关联题】
【自测】
- 两事务分别「先更新 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 分钟；框架 A，骨架共性见文首第二节，本题只写特定内容）
| 时间 | 任务 | 内容 |
|---|---|---|
| 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 |
【追问链】（三层）
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 前提 |
【关联题】
【自测】
- 「读到对方未提交的数据」对应哪类问题？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_Source飙升。读写分离架构下用户立刻感到「读旧」。 - 为什么拆成小批更优？ 每批 500–1000 条独立提交：锁窗口短、undo 小、失败重试粒度细、从库可流水重放。代价是「非原子」——要靠进度表/幂等保证断点续跑，业务上可接受（批处理本质允许中间态）。
【选型判断树】
发现长事务/事务内 RPC：
├─ 能否拆小？
│ ├─ 能 → 分批提交（500–1000/批）+ 进度记录 + 幂等
│ └─ 不能（强原子）→ 评估是否可改成状态机/补偿，而不是拉长事务
├─ 是否有 RPC/MQ/HTTP？
│ └─ 移出事务：先 commit，再本地消息表/事件发布
├─ 是否锁了不必要的行？
│ └─ 补索引、改点查、避免全表 UPDATE
└─ 监控
└─ innodb_trx 长事务告警；连接池使用率；复制延迟判断口诀： 事务内只碰数据库；批要小；远程一律出去。
【口述骨架】（5 分钟；框架 B，骨架共性见文首第二节，本题只写特定内容）
| 时间 | 任务 | 内容 |
|---|---|---|
| 0:00–0:30 | 定性 | 「大事务+事务内 RPC 是经典反模式」 |
| 0:30–1:40 | 危害四项 | 锁、undo、主从延迟、连接占用（与第 1 条、80 分档同一口径） |
| 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 | 快速失败 |
| 大事务复制延迟 | 可达分钟级 | 读写分离雪上加霜 |
【追问链】（三层）
L1｜「事务里调 RPC 为什么还可能丢数据一致性？」 → RPC 成功后本地事务回滚，外部已发货/已通知但库内无单；或本地提交成功但 RPC 失败且无重试。应本地事务成功后发消息，消费方幂等。
L2｜「拆事务后中途失败，怎么保证最终做完？」 → 记录批次进度/水位，任务断点续跑；每批幂等（唯一键/状态机）；失败重试+告警；必要时人工补跑。
L3｜「必须更新 100 万行且要尽量原子，怎么办？」 → 评估真原子必要性；多数场景可用「状态机推进+对账」替代单事务原子。若必须，低峰执行、控制锁、监控 undo 与延迟，并接受窗口内性能下降。
【评分标准】
| 档位 | 答案特征 |
|---|---|
| 60 分 | 知道大事务不好，会说拆批 |
| 80 分 | 列出锁/undo/主从/连接危害；RPC 移出事务 |
| 95 分 | 解释 undo 回滚与复制重放；进度+幂等续跑；监控指标 |
【关联题】
【自测】
- 为什么禁止在事务里调 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 分钟；框架 D，骨架共性见文首第二节，本题只写特定内容）
| 时间 | 任务 | 内容 |
|---|---|---|
| 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 | 会占连接 |
【追问链】（三层）
L1｜「为什么连接池不是越大越好？」 → 连接过多导致 DB 线程调度与锁竞争加剧，吞吐下降、延迟上升；应用侧也有线程与内存开销。池应匹配「并发 SQL 的有效并行度」（常接近 CPU 核数）。
L2｜「怎么证明是连接泄漏？」 → 看 Sleep 连接随时间单调增长且不随流量下降；开启池泄漏检测打印未归还栈；对比 maxLifetime 内连接数曲线。代码审查异常分支与未关闭 ResultSet/Connection。
L3｜「扩容应用副本后突然 Too many connections，为什么？」 → 总连接=副本×池，扩容线性放大。治理：每实例池调小、DB 提高 max_connections、加代理（ProxySQL）连接复用、或分库分片摊薄。
【评分标准】
| 档位 | 答案特征 |
|---|---|
| 60 分 | 会看 processlist，知道可能慢 SQL 或配置小 |
| 80 分 | 四类根因+容量公式；泄漏检测 |
| 95 分 | 应急顺序与「先治占用时长」；扩容打满机理；代理/分库 |
【关联题】
【自测】
- 应用从 4 副本扩到 8 副本，池 50 不变，DB max_connections=500，有何风险？ 参考答案：8×50=400，叠加运维连接可能逼近上限；应下调每池或提高上限/加代理。
- processlist 大量 Sleep 2 小时的连接，最可能是什么？ 参考答案：连接泄漏或应用持有不还；也可能是空闲未回收，需结合池 idle 配置与业务判断。
- 应急时为什么优先限流而不是重启连接池？ 参考答案：根因（慢 SQL/泄漏）还在，重启会再次被打满；限流先切断放大源。