四、MySQL、缓存与消息队列（28～39）

28. 亿级订单表如何设计主键、索引和冷热分层？

查询簇先行。 用户查最近订单、商家按状态和时间筛选、支付回调按订单号点查，是三类核心路径。主键用趋势递增分布式 ID，降低页分裂；order_no 唯一索引用于幂等和外部查询。

索引。 用户侧 (user_id, create_time, id)，商家侧 (shop_id, status, create_time, id)；列表只取轻字段，详情再按主键查。大 JSON、快照和扩展字段拆到扩展表或对象存储，避免宽行拖累 Buffer Pool。索引必须由真实查询验证，不能为所有筛选组合排列组合。

冷热分层。 近期订单留在线交易库，历史订单归档到冷库/列存/搜索；用户跨年查单由查询网关并行访问热库和归档索引。归档以状态终结且超过保留期为条件，必须可校验和回放。运营复杂查询走数仓，不拖垮 OLTP。

具体示例

具体设定。 订单表 20 亿行，用户常查最近 3 个月订单，商家后台按状态查最近 7 天订单。

落地例子。 主键 id 用趋势递增分布式 ID；外部 order_no 唯一。用户列表索引 (user_id, create_time, id)，商家索引 (shop_id, status, create_time, id)。3 个月内热库，历史订单归档到冷库/搜索，详情大 JSON 放扩展表。

面试可讲。 索引从查询簇出发，不是把所有字段组合都建一遍。列表查轻字段，详情再回表；运营复杂查询走数仓，不能拖垮 OLTP。

29. 一条 SQL P99 很慢，如何从证据到优化闭环？

定位。 获取完整 SQL、绑定参数、表结构、统计信息和慢查询样本，区分执行慢、锁等待和连接排队。按版本、实例、分片和参数维度比较，避免平均值掩盖局部热点。

计划。 使用 EXPLAIN ANALYZE 看 actual rows、loops、访问类型、回表、排序与临时表；估算行数偏差可能来自统计信息失真。联合索引围绕等值、范围、排序和覆盖设计；避免函数、隐式转换、无前缀 LIKE 和 SELECT *（这些会导致索引失效）。

实例。 SQL 合理仍慢，则检查 Buffer Pool 命中、IO、CPU、锁、长事务、连接池和复制延迟。优化后用真实数据分布回放，比较 P50/P99、扫描行数和写放大，再灰度上线。

为什么。 优化目标是减少数据页访问、回表、排序和等待，而不是机械加索引。新增索引会增加写成本、DDL 风险和存储，必须证明净收益。

具体示例

具体设定。 SQL：select * from orders where user_id=123 and status=1 order by create_time desc limit 20，P99 从 30ms 变 2s。

落地例子。 先看慢日志和 EXPLAIN ANALYZE，发现只命中 idx_user_id，扫描该用户 50 万历史订单后排序。优化为联合索引 (user_id, status, create_time, id)，列表只查必要列；上线前用真实大用户参数回放，看扫描行数和 P99 是否下降。

面试可讲。 慢 SQL 不等于立刻加索引。要先区分执行慢、锁等待、连接池排队和参数热点，再证明新增索引收益大于写入成本。

30. 深分页为什么慢？如何支持稳定翻页和必要的跳页？

LIMIT 1000000,20 仍需读取并丢弃前 100 万条。连续翻页应使用 Keyset Pagination，游标携带 (create_time, id)，下一页条件为小于上一页末尾，并有相同联合索引。id 解决同时间戳下重复和漏数。

必须跳页时，可用覆盖索引先查目标页主键再回表，维护稀疏页锚点，或产品限制最大页深。数据导出走按主键范围分段，不走在线 Offset。若翻页期间数据变化，要定义快照语义；推荐流通常容忍少量变化，财务列表则使用固定 snapshot/version。

追问。 多字段排序时游标必须包含完整比较元组；跨分片分页由每分片游标 + K 路归并组成，不能只有一个全局 offset。

具体示例

具体设定。 后台导出第 100000 页订单，每页 20 条，用 limit 2000000,20 很慢。

落地例子。 连续翻页改成游标：第一页 order by create_time desc,id desc limit 20，下一页带 where (create_time,id) < (?,?)。必须跳页时，先用覆盖索引拿主键，再批量回表，或离线导出按主键范围扫描。

面试可讲。 Offset 深分页慢是因为数据库仍要读并丢弃前面大量记录。稳定翻页要靠排序键游标，同时间戳必须加 id 防止重漏。

31. 十亿行表在线加字段、索引或迁移新库，怎么做？

DDL 评估。 先确认版本与变更是否支持 INSTANT/INPLACE，评估元数据锁、表重建、磁盘峰值和主从延迟。设置短 lock wait timeout，避免 DDL 排队后突然锁住业务。

影子表流程。 创建新结构；通过 CDC/触发器同步增量；按主键范围限速复制存量；持续校验行数、分桶 checksum 和业务抽样；追平后先影子读对比，再灰度切读写。应用变更遵循 expand-contract：先兼容新旧，再切换，最后清理旧结构。

回滚。 切换前保留旧库与可回放日志；写切换通过单一路由控制，避免各实例配置漂移。双写不是天然原子，需要 Outbox/CDC 和对账。大事务、无主键表、字符集与时区差异都要单独处理。

具体示例

具体设定。 给 10 亿行订单表加 risk_level 字段，并逐步迁到新库。

落地例子。 先确认 MySQL 版本是否支持 instant add column。迁库时建影子表，通过 CDC 同步增量，历史数据按主键范围每批 5000 行限速复制，分桶 checksum 校验。应用先兼容双读/双写，再灰度切流。

面试可讲。 大表变更最怕元数据锁、主从延迟和回滚困难。要按 expand-contract 做：先扩展兼容，再迁移验证，最后收缩旧结构。

32. 单表 20 亿订单如何分库分表并支持扩容？

先证明要拆。 如果瓶颈可通过索引、归档、读写分离解决，过早分片只会增加复杂度。确需拆分时，选择让主要查询单分片的键：用户查单占主导则按 user_id；订单号查询则让 order_id 内编码路由位或维护路由索引。

逻辑槽。 使用大量固定逻辑槽 hash(key) % 4096，槽映射到物理库表；扩容只迁移部分槽，不直接按机器数取模。跨分片商家统计、搜索和运营查询由 CDC 同步到搜索/数仓。

迁移。 新旧路由版本并存，先复制、再双读校验、最后灰度切流。全局唯一约束用全局 ID 或按业务键独立唯一服务；跨分片事务尽量通过业务拆分和 Saga 避免。

热点。 大客户可能让 user_id 分片倾斜，需要二级拆分或独立租户分片。时间分片会把近期写集中，不应作为唯一策略。

具体示例

具体设定。 用户查单占 80%，按订单号点查占 15%，运营查询占 5%。

落地例子。 主分片按 user_id，建 4096 个逻辑槽，槽映射到物理库表。订单号里编码槽号，或建 order_route(order_no, user_id, shard) 路由索引。扩容时迁移一部分槽，不重新 hash % 新机器数。

面试可讲。 分片键要让主查询单分片完成。逻辑槽让扩容可控；运营跨分片查询应该同步到搜索/数仓，不要在线扫所有分库。

33. MySQL 主从延迟导致写后读不到，如何分级解决？

一致性分级。 强读己之写的订单状态、权限等读主库或带位点等待；普通列表和推荐读从库接受最终一致。不要让所有查询都回主库。

位点方案。 写成功返回 GTID/binlog position，后续读请求携带 token；路由层选择已追到该位点的从库，等待超时则回主。简单场景可写后短时间 sticky master，但精度不如位点。

治理。 监控复制延迟和最老未应用事务，延迟过大自动摘除；限制大事务、慢 DDL 和单线程回放瓶颈。故障切换时需要确认新主的数据完整性和旧主 fencing。

错误方案。 固定 sleep 100ms 既不保证正确又增加延迟；半同步复制降低丢失风险，但不自动保证任意从库立即可读。

具体示例

具体设定。 用户刚支付成功，刷新订单页却在从库读到 PAYING。

落地例子。 支付写成功返回 GTID 位点，订单详情读请求携带 read_after_write_token。路由层选择已经追到该位点的从库，最多等待 50ms，超时回主。普通订单列表仍读从库。

面试可讲。 不要全量读主，也不要固定 sleep。按业务分级：详情和权限读己之写要强，列表可最终一致。

34. MySQL 与 Redis 的缓存一致性如何设计？

常规模式。 读 Cache，未命中读 DB 并回填；写先提交 DB，再删除 Cache。删除失败进入可靠重试，或订阅 binlog 做统一失效。缓存项带 data_version，旧读回填时如果版本落后则拒绝覆盖。

强一致场景。 写后短时间绕过缓存，或把关键状态固定读权威库。不要宣称 Cache-Aside 是绝对强一致；并发旧读仍可能在删除后回填，版本条件、延迟双删或串行重建只能缩小窗口。

为什么删除而非更新。 同一 DB 数据可能对应多个缓存形态，删除更简单；更新缓存容易出现并发覆盖和漏更新。代价是下一次读回源，需要热点互斥重建。

具体示例

具体设定。 商品价格从 100 改到 80，缓存里还有旧价格。

落地例子。 写链路先更新 DB：price=80, version=12，提交成功后删除 product:123 缓存；删除失败写重试任务，binlog 消费者也会按 version 失效缓存。读 miss 回填时使用 set if version >= current，避免旧读覆盖新值。

面试可讲。 Cache-Aside 不是强一致，只是把不一致窗口压小。关键状态写后可以短时间绕缓存读 DB，普通展示接受最终一致。

35. 缓存穿透、击穿、雪崩如何系统治理？

穿透。 参数校验、Bloom Filter、空值短 TTL；防止攻击者制造无限随机 Key，必须配合限流和 Key 规范化。

击穿。 单热点过期时使用互斥重建、逻辑过期返回旧值、后台刷新；锁要有超时，获取失败不能让所有请求阻塞。热点可设为主动更新而非被动 TTL。

雪崩。 TTL 随机化、多级缓存、Redis 集群高可用；缓存整体故障时通过并发隔离、数据库保护水位和降级阻止全量回源。

观测。 命中率不够，要看回源 QPS、热点分布、重建耗时、Key 大小和单命令 P99。应急开关允许返回旧值或默认值。三类问题诱因不同，不能只回答“加锁”。

具体示例

具体设定。 攻击者请求大量不存在的商品 ID；同时一个热点商品缓存过期，所有请求打到 DB。

落地例子。 不存在 ID 先过 Bloom Filter，DB 查空后缓存空值 30 秒。热点商品用逻辑过期：过期后先返回旧值，一个后台线程拿互斥锁刷新。大量 key TTL 加随机偏移，Redis 故障时限流并返回降级值。

面试可讲。 三类问题诱因不同：穿透是不存在 key，击穿是单热点过期，雪崩是大面积失效或 Redis 故障。方案不能只说“加锁”。

36. Redis 热 Key、大 Key 与慢请求如何定位和治理？

定位。 从命令级时延、CPU、网络、hotkey sampling、Key size 和 slowlog 判断是读热、写热、大对象还是 fork/网络问题。慢请求也可能被前面的 O(N) 命令阻塞。

读热。 本地缓存 + 主动失效；复制多个 Key 并稳定散列读取；超级热点静态数据可下沉 CDN。写热计数按 bucket 分散再聚合。

大 Key。 Hash/Set 按业务维度或时间拆分，限制单次返回；删除使用 UNLINK/渐进删除，避免主线程阻塞。禁止生产使用 KEYS 和无界范围查询。

取舍。 本地缓存带来短暂不一致和内存管理；Key 副本增加更新成本；拆分增加读聚合。方案必须与访问模式匹配，并配热点自动发现和降级。

具体示例

具体设定。 hot_post:9001 每秒 30 万次读取，user_follow_set:10001 有 1 亿成员。

落地例子。 热读 key 加本地缓存，或复制成 hot_post:9001:{0..31} 分散读。大 Set 按 bucket 拆成 fans:10001:000..1023，分页读取；删除用渐进任务或 UNLINK，禁止一次性 SMEMBERS。

面试可讲。 Redis 慢不一定是 CPU，也可能是大对象、O(N) 命令、网络包太大或 fork。先定位类型，再决定本地缓存、分桶、拆 key 还是限流。

37. 如何保证 MQ 端到端不丢消息？

三段论。 业务到生产者、Broker 内部、Broker 到消费者分别保证。生产端用本地事务 Outbox：同一事务写业务和待发送事件，后台反复投递；发送开启 Confirm，超时复用同一 message_id。

Broker。 开启持久化、多副本和满足 RPO 的副本确认；监控 under-replicated partition、磁盘和 ISR。不能只说“持久化”就结束。

消费。 关闭自动 ACK，业务事务成功后确认；失败按错误类型指数退避，超过阈值进 DLQ 并告警。至少一次投递意味着消费端必须幂等。

追问。 发送超时状态未知，不可生成新 ID；消费者 DB 成功但 ACK 丢失会重复，因此唯一约束/状态机是必要设计。

具体示例

具体设定。 下单成功后必须发“创建订单”事件给库存和履约，不能因为服务重启丢事件。

落地例子。 订单事务里同时写 orders 和 outbox(event_id, status=NEW)。Publisher 扫描 outbox 发 MQ，收到 broker confirm 后标记 SENT。消费者业务成功落库后再 ACK；消费端用 event_id 唯一键幂等。

面试可讲。 端到端要分三段：业务到生产者、Broker 持久化、消费者处理。只说“MQ 持久化”漏掉了业务事务和消费 ACK 这两段。

38. MQ 如何同时处理重复、乱序和顺序消费？

顺序范围。 通常只需订单内有序。生产端按 order_id 固定分区，单分区由一个消费者实例顺序读取；多生产者仍可能乱序，因此消息携带 aggregate_version。

幂等。 用业务唯一键、唯一流水或 (message_id, consumer) 去重记录，并与业务写放在同一事务。状态更新带 from_state/version 条件，旧版本不能倒退状态。

缺口。 收到 v5 但当前是 v3，可短暂缓冲等待 v4、触发重试，或从权威源重建最新状态。毒消息不能永久阻塞整个分区，应隔离到按 Key 的重试队列，并保留审计。

扩容。 分区数变化会改变映射，迁移期间要暂停相关 Key、使用一致性路由版本或容忍跨分区版本校验。不要用单队列换全局顺序。

具体示例

具体设定。 同一个订单先发 PAY_SUCCESS(v3)，后发 REFUND(v4)，但消费者可能先收到 v4。

落地例子。 按 order_id 分区保证大多数情况下同订单顺序；消息带 aggregate_version。消费者当前版本是 v2，收到 v4 时先缓冲或触发补拉，不能直接跳到退款。重复消息用 (event_id, consumer) 唯一表忽略。

面试可讲。 顺序要先定义范围，通常是订单内顺序，不是全局顺序。乱序最终靠版本号和状态条件兜底。

39. MQ 积压数亿条，如何止损、扩容和清空？

量化。 计算生产速率 P、消费速率 C、净积压 P-C、最老消息年龄和预计清空时间。判断是生产突增、消费者故障还是下游 DB 变慢。

止损。 限制非核心生产，扩大 Broker 磁盘并保护副本；坏消息隔离，避免无限重试。不要直接加消费者把 DB 打垮，先找真实瓶颈。

恢复。 能并行则增加分区和消费者；批量拉取、批量写入、合并远程调用；按业务优先级拆 Topic。过期消息经业务确认后归档或跳过，保留 manifest 和补偿方式。

限制。 Kafka 并行度受分区数约束，扩分区影响 Key 顺序。恢复期间设置下游令牌桶和重试预算，持续观察 lag、失败率、DB 连接池和 P99。

具体示例

具体设定。 消费者故障 2 小时，Topic 积压 3 亿条，生产 10 万/s，消费 2 万/s。

落地例子。 先算净积压 8 万/s，继续恶化；马上限制非核心生产，修复消费者错误。若下游 DB 是瓶颈，增加消费者只会压垮 DB，要改批量写、合并 RPC、按优先级拆 Topic。过期无价值消息经业务确认后跳过并记录 manifest。

面试可讲。 积压治理先止血再扩容。核心指标是生产速率、消费速率、最老消息年龄和预计清空时间，不是盲目加机器。

---