考试题库 · 计算机 & 公务员行测

外观

模块导航： 总入口 · 总纲与方法 · 后端场景 · AI与算法工程 · 国企与金融 · 网络问答

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

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

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

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

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

【选型判断树】

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

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

【口述骨架】（5 分钟；框架 C，骨架共性见文首第二节，本题只写特定内容）

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

【关键数字】

参数经验值说明
maximumPoolSize10–20/实例（按 DB 并行度与 max_connections 分摊后），DB 连接富余才往上给压测确定，别默认给到 50
经验上限DB 主机核数×2 + 有效磁盘数（再按应用实例数分摊）起点而非真理；别拿应用核数套
connectionTimeout3–5s默认 30s 过长
maxLifetime10–30min< DB wait_timeout
minimumIdle全局稳态并发（峰值 QPS × 单 SQL 平均耗时）÷ 实例数，且 ≤ maximumPoolSize用平均耗时而非 P99；配大了会被池抬到 max
使用率告警80%
wait_timeout默认 8h应用侧应更短

【追问链】（三层）

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

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

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

【评分标准】

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

【关联题】

【自测】

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

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

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

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

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

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

【原理溯源】

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

【选型判断树】

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

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

【口述骨架】（5 分钟；框架 B，骨架共性见文首第二节，本题只写特定内容）

时间任务内容
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
审计保留按法规数年归档而非物理删

【追问链】（三层）

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

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

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

【评分标准】

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

【关联题】

【自测】

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

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

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

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

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

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

【原理溯源】

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

【选型判断树】

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

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

【口述骨架】（5 分钟；框架 C，骨架共性见文首第二节，本题只写特定内容）

时间任务内容
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/子表注意更新成本

【追问链】（三层）

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

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

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

【评分标准】

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

【关联题】

【自测】

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

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

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

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

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

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

【原理溯源】

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

【选型判断树】

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

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

【口述骨架】（5 分钟；框架 A，骨架共性见文首第二节，本题只写特定内容）

时间任务内容
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 按已存在返回注意错误码

【追问链】（三层）

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

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

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

【评分标准】

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

【关联题】

【自测】

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

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

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

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

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

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

【选型判断树】

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

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

【口述骨架】（5 分钟；框架 A，骨架共性见文首第二节，本题只写特定内容）

时间任务内容
0:00–0:30定性「读不阻塞写靠多版本：读快照，写最新」
0:30–1:40行与链trx_id、roll_pointer、undo 链
1:40–3:00ReadView活跃集合与可见性判断；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

【追问链】（三层）

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

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

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

【评分标准】

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

【关联题】

【自测】

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

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

【考察内容】InnoDB 锁机制

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

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

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

【原理溯源】

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

【选型判断树】

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

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

【口述骨架】（5 分钟；框架 A，骨架共性见文首第二节，本题只写特定内容）

时间任务内容
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 题

【追问链】（三层）

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

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

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

【评分标准】

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

【关联题】

【自测】

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

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

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

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

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

  1. 排查顺序：SHOW PROCESSLIST 看谁在跑；慢日志；performance_schema/sys schema 热 SQL；EXPLAIN；看 QPS 是否异常；看是否有全表扫描、函数运算、排序、正则；看连接风暴与锁等待（锁等待有时也表现为 CPU 高）。（先 top/pidstat 确认 CPU 是 mysqld 占的，排除应用进程、备份任务与异常进程；再往下），
  2. 容量估算（步进）：假设异常 SQL 全表扫 5000 万行，单条就可能打满一核数秒；并发条数 ≥ DB 主机核数就把整机打满（示例按 20 核机取 20 条，8 核机 8 条即满）。目标：杀掉/限流异常 SQL，把 CPU 压回 50% 以下留缓冲；同时估算该 SQL 的 QPS×扫描行数，判断是优化还是必须先限流。
  3. 应急：KILL 突发慢会话；入口对该接口限流/降级；重启实例是末位手段且不能未经定位就重启（先抓现场、确认持久化与从库状态，否则掩盖根因、重启后还可能触发恢复风暴）；读流量切到从库；非核心分析查询立刻停掉。
  4. 根治：加索引/改写 SQL；杀循环应用线程（代码 bug 疯狂重试会表现为 CPU 高但无慢 SQL）；拆分分析型查询到数仓；防止定时任务齐峰（随机延迟）。
  5. 失败与降级：限流后用户侧错误页/排队；主库 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（如无索引全表扫）或限流，保留现场便于根因分析。

【选型判断树】

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

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

【口述骨架】（5 分钟；框架 D，骨架共性见文首第二节，本题只写特定内容）

时间任务内容
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_time0.1–1s抓更多
CPU 危险区持续 >70%–80%预警
连接 vs 核数连接远多于核则切换贵

【追问链】（三层）

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 题联动

【关联题】

【自测】

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

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

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

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

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

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

【原理溯源】

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

【选型判断树】

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

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

【口述骨架】（5 分钟；框架 D，骨架共性见文首第二节，本题只写特定内容）

时间任务内容
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 条同口径
校验全量关键表+抽样
停写窗口可做到秒～分钟双写可更短

【追问链】（三层）

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

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

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

【评分标准】

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

【关联题】

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

【自测】

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

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

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

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

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

  1. 表设计（步进估算）：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”时的生存前提。
  2. 扣减：UPDATE stock SET num=num-1 WHERE item_id=? AND num>0，影响行数判断成败；批量可 WHERE num>=?。高并发热点行会有锁排队，RT 上升但正确性可保证；一人一单用唯一订单键防重复扣。
  3. 减少热点：库存分段（按题干口径 1 万拆 10 段、每段 1000）应用层选段扣减；或对热点商品排队串行化；列表页库存可短 TTL 缓存，下单路径仍走 DB 条件更新。
  4. 失败与降级：扣减超时重试幂等；支付取消/超时关单回补库存；对账订单数与库存；限流保护主库；DB 慢时排队页而不是放开写。
  5. 口径：没有 Redis 也能不超卖，代价是吞吐受限；先保证正确，再用限流和拆热点提吞吐；后续加缓存预扣是性能优化不是正确性前提。

【原理溯源】

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

【选型判断树】

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

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

【口述骨架】（5 分钟；框架 A，骨架共性见文首第二节，本题只写特定内容）

时间任务内容
0:00–0:30定性「防超卖靠原子条件更新，不是先查后改」
0:30–1:30SQL 论证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 为准

【追问链】（三层）

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

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

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

【评分标准】

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

【关联题】

【自测】

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

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

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

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

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

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

【原理溯源】

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

【选型判断树】

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

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

【口述骨架】（5 分钟；框架 C，骨架共性见文首第二节，本题只写特定内容）

时间任务内容
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收尾「冗余是空间换时间，一致性要提前设计」

【关键数字】

参数经验值说明
同步延迟秒级最终一致
对账小时/天视业务
快照字段商品名/价/规格订单核心

【追问链】（三层）

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

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

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

【评分标准】

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

【关联题】

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

【自测】

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

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

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

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

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

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

【原理溯源】

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

【选型判断树】

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

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

【口述骨架】（5 分钟；框架 C，骨架共性见文首第二节，本题只写特定内容）

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

【关键数字】

参数经验值说明
页大小16KB
3 层容量约 2000 万级估算
舒适区500 万–2000 万视模式
评估分表指标恶化且优化穷尽

【追问链】（三层）

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

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

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

【评分标准】

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

【关联题】

【自测】

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

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

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

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

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

  1. 容量估算（步进）：口径要先对齐——批量/本机 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 分钟压到 几分钟内。
  2. 手段：① 批量插入/LOAD DATA INFILE；② 导入期关闭非唯一辅助索引，导完重建（或延迟索引）；③ 单事务分批提交，避免超大事务；④ 并行导入注意主键无冲突与分库路由；⑤ 关闭唯一检查仅在安全场景，慎用。
  3. 优先 LOAD DATA：纯文件导入通常比 JDBC 快一个数量级。
  4. 失败与降级：失败从断点续导（记录进度表）；磁盘与主从延迟监控；高峰禁导；校验总行数与抽样 checksum；大表 DDL/建索引低峰。
  5. 口径：导入优化三板斧——批量、减索引、拆事务；同时保证可重入可校验。

【原理溯源】

  • 为什么单条 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 在宕机时可能丢事务；生产导入要评估是否可接受，结束后立即恢复默认。

【选型判断树】

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

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

【口述骨架】（5 分钟；框架 B，骨架共性见文首第二节，本题只写特定内容）

时间任务内容
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 线程常见防锁竞争

【追问链】（三层）

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

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

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

【评分标准】

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

【关联题】

【自测】

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

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

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

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

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

  1. 机制（步进估算）：二级索引找到主键后还要回表查聚簇索引，若命中 100 万行，100 万次随机回表，RT 可到秒级；覆盖索引后只扫描二级索引，顺序 IO，可能降到 几十～几百 ms，性能差 1–2 个数量级。

  2. 判断：EXPLAIN Extra 出现 Using index 说明覆盖；否则看 rows 很大且筛选列多来自少数列，且 type 为 ref/range 但仍然慢，多半是回表放大。

  3. 手段：① 建覆盖索引 (user_id, create_time, status, amount) 等把查询列包进去；② 只查需要的列，禁止 SELECT *；③ 延迟关联：先二级索引取主键再回表少量行；④ 必要时汇总进 ES/汇总表，OLTP 只服务点查。

  4. 代价与容量：覆盖索引写放大、占磁盘；按查询频率建最关键的几个；亿级表上大回表查询禁止直接暴露给 C 端，必须限流或走分析库。 哪些查询天然适合覆盖索引：① 主键点查（聚簇索引叶子本就是整行）；② 只取少数列的列表/时间轴查询；③ 只用索引列做过滤与排序的计数类查询（COUNT(*) 走最小二级索引）。要返回宽行或 SELECT * 的不适合——覆盖反而把索引撑大。

  5. 失败与降级：紧急限流该接口；上线索引监控命中与回滚；业务强制走覆盖路径，查询列白名单；新索引未生效时降级为近N天查询。

【原理溯源】

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

【选型判断树】

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

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

【口述骨架】（5 分钟；框架 C，骨架共性见文首第二节，本题只写特定内容）

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

【关键数字】

参数经验值说明
覆盖标志Extra: Using index
ICP 标志Using index condition仍回表
回表每行约一次聚簇点查量大即慢

【追问链】（三层）

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

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

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

【评分标准】

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

【关联题】

【自测】

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

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

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

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

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

  1. 规则：查询条件必须从最左列连续使用，才能用上索引前缀；跳过 a 只有 b、或 a 用了范围后再想高效用 c 可能受限（范围后列多用于过滤/覆盖，排序能力受限）。
  2. 例子：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。
  3. 设计建议（步进）：等值列在前、范围列在后；高区分度列靠前并非绝对，以真实 SQL 频次与业务访问模式为准；不同访问模式可能需要多个索引或冗余索引表；上线前用慢 SQL 预演验证。
  4. 容量：区分度低的列放最左意义不大；索引不是越多越好，写入成本随索引数上升；联合索引列数控制在 2–4 个常用列内（再多写放大明显）。
  5. 失败与降级：历史 SQL 与新索引不匹配时改写 SQL 或补索引；生产加索引用 online DDL 低峰执行，执行前先确认无长事务持表（DDL 起止要拿 MDL 排他锁，长事务在场时会把后续查询全堵死，见第 52 题；超大表走 gh-ost/pt-osc）；监控慢 SQL 变化与索引命中率。
  6. 口径：联合索引像电话簿排序键，不按前缀查就无法二分定位；先列访问模式再设计索引顺序。

【原理溯源】

  • 为什么有最左前缀？ 联合索引先按 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，则无法利用索引序。

【选型判断树】

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

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

【口述骨架】（5 分钟；框架 D，骨架共性见文首第二节，本题只写特定内容）

时间任务内容
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 例外）

【追问链】（三层）

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 分解释树上有序；优化器重排；范围后列定位限制

【关联题】

【自测】

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

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

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

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

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

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

【原理溯源】

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

【选型判断树】

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

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

【口述骨架】（5 分钟；框架 D，骨架共性见文首第二节，本题只写特定内容）

时间任务内容
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半同步等

【追问链】（三层）

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

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

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

【评分标准】

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

【关联题】

【自测】

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

持续学习，持续积累。

Copyright © 2026 smallxuetua