📖 本篇简介：快手（短视频 UGC/推荐）、滴滴（出行实时位置）、小米（IoT 海量设备）的 MySQL 题各有侧重——UGC 元数据、实时高频写、设备心跳、流水账单。本篇精选 20 道公开高频题型，覆盖位置数据、实时状态、特征存储、高频写抗住、地理空间查询、设备冷热分层。题目均为公开面经常见类型，解析讲通用原理，不涉及任何公司未公开信息。前置知识：第 9、17、18、24、26 章；本系列示例基于 MySQL 8.0。
目录
- 1 本篇考情与知识地图
- 题目 1：出行订单位置数据怎么存
- 题目 2：司机乘客实时状态表怎么设计
- 题目 3：推荐特征数据存哪里
- 题目 4：海量UGC元数据怎么设计
- 题目 5：实时位置高频写DB扛不住怎么办
- 题目 6：营销补贴活动DB怎么设计
- 题目 7：司机流水账单系统怎么设计
- 题目 8：附近的车附近的人怎么查
- 题目 9：UGC海量内容元数据存储
- 题目 10：IoT海量设备数据怎么存
- 题目 11：设备心跳在线状态怎么维护
- 题目 12：出行下单派单扣款的分布式事务
- 题目 13：用户行为埋点与MySQL边界
- 题目 14：实时数仓与业务库怎么同步
- 题目 15：行程进行中订单状态怎么流转
- 题目 16：高并发地理空间查询怎么优化
- 题目 17：灰度发布与DB变更怎么配合
- 题目 18：千万级订单怎么分库分表
- 题目 19：设备数据冷热分层怎么做
- 题目 20：异地多活在出行IoT场景
- 速查速记
- 常见坑与注意事项
- 复习清单
- 练习题
1 本篇考情与知识地图
三家场景差异大，但共享一个思路：高频写、海量数据、实时与历史分离。先建地图：
| 场景 | 高频数据 | 存储分工 |
|---|---|---|
| 出行 | 实时位置、订单 | 位置 Redis/时序，订单 MySQL |
| 短视频 | UGC 元数据 | MySQL 元数据 + OSS 媒体 |
| IoT | 设备心跳/上报 | 时序库 + MySQL 设备档案 |
主线：实时高频写别硬压 MySQL，元数据/状态才进 MySQL，明细进时序/列存。
题目 1：出行订单位置数据怎么存
考点
- 高频写；时序数据。
参考答案
1. 行程轨迹是高频写：每几秒上报一次，MySQL 扛不住这么高写 QPS；
2. 实时位置：存 Redis（当前位置），供"叫车看附近车"；
3. 历史轨迹：轨迹点写入时序库/列存/对象存储，MySQL 不存明细；
4. MySQL 只存：订单、起终点、状态这类结构化低频数据；
5. 行程结束归档：轨迹文件落 OSS，DB 存引用。
追问
- 问：行程中要查历史轨迹？答：从时序库/OSS 读，别查 MySQL。
- 问：位置写 MySQL 行不行？答：短期低量可以，量级上来必须迁时序库。
题目 2：司机乘客实时状态表怎么设计
考点
- 状态高频更新；缓存。
参考答案
1. 状态（空闲/接单/行程中）放 Redis：高频读、可过期、易失可重建；
2. MySQL 存档案：司机信息、车辆信息，低频改；
3. 状态落库：状态变更事件异步落 MySQL 留痕；
4. 在线状态：心跳维护 Redis 在线标记；
5. 对账：Redis 状态与 DB 事件最终一致。
追问
- 问：Redis 挂了司机状态？答：从 DB 最近事件重建，不是权威就丢得起。
- 问：状态频繁更新 DB？答：异步批量落，别每次心跳写库。
题目 3：推荐特征数据存哪里
考点
- 特征存储；MySQL 边界。
参考答案
1. 特征量大、读多：推荐每次要拉几百维特征；
2. 在线特征：放特征存储（Redis/专用 Feature Store），低延迟读；
3. 离线特征：数仓/Hive 训练用；
4. MySQL 只存：用户/物品档案这类结构化元数据；
5. 别把全量特征塞 MySQL：点查延迟和存储成本都不划算。
追问
- 问：特征更新频繁？答：流式更新到 Feature Store，MySQL 不参与在线特征读。
- 问：为什么不用 MySQL 拉特征？答：几百维 JOIN + 高 QPS，延迟顶不住。
题目 4：海量UGC元数据怎么设计
考点
- 元数据 vs 媒体文件。
参考答案
1. 媒体文件（视频/图）存 OSS，DB 只存 URL；
2. 元数据：作品 ID、作者、标题、标签、状态，存 MySQL；
3. 按作者分表：某作者作品落固定分片；
4. 多条件检索：标签/分类走 ES；
5. 状态机：审核中/通过/下架，条件更新。
-- UGC元数据表，本示例基于 MySQL 8.0
CREATE TABLE t_content (
content_id BIGINT PRIMARY KEY,
author_id BIGINT,
title VARCHAR(200),
media_url VARCHAR(500), -- 媒体文件在OSS，这里存URL
status TINYINT, -- 0审核中 1通过 2下架
created_at DATETIME,
KEY idx_author(author_id, created_at)
);
追问
- 问：视频封面？答：OSS 存封面图，DB 存 URL。
- 问：下架怎么改？答：
UPDATE ... SET status=2 WHERE content_id=?。
题目 5：实时位置高频写DB扛不住怎么办
考点
- 高频写优化；时序。
参考答案
1. 别直接高频写 MySQL：写 QPS 万级以上 MySQL 压力大；
2. 先写 Redis：当前位置覆盖更新（只留最新）；
3. 批量异步落：攒一批轨迹点批量写时序库；
4. 削峰：MQ 缓冲高频上报；
5. 降级：极端时只保留最新位置，丢中间点。
追问
- 问：只留最新位置够吗？答：叫车场景够；完整轨迹异步批量落时序库。
- 问：批量写仍慢？答：load data / 多行 insert，顺序写。
题目 6：营销补贴活动DB怎么设计
考点
- 活动预算；原子扣减。
参考答案
1. 活动预算表：总预算、已用、剩余；
2. 发放原子扣：UPDATE budget SET used=used+? WHERE act=? AND used+?<=total；
3. 发放流水：每笔补贴记流水；
4. 防刷：一人一单唯一索引 + 限流；
5. 对账：发放总额与预算对账。
追问
- 问：预算超了？答：条件
used+?<=total原子判断，超了不成功。 - 问：高并发抢补贴？答：预算前置 Redis，异步发。
题目 7：司机流水账单系统怎么设计
考点
- 流水与余额；对账。
参考答案
1. 流水表：每笔收入/支出一条，不可改；
2. 余额表：司机当前可提现余额；
3. 同事务：加流水 + 更新余额；
4. 对账：流水汇总 = 余额，每日校验；
5. 提现：状态机 + 幂等。
追问
- 问：流水表会很大？答：按司机/月份分表归档。
- 问：余额错了？答：以流水为准重算余额订正。
题目 8：附近的车附近的人怎么查
考点
- 地理空间；GeoHash/网格。
参考答案
1. MySQL 空间索引：POINT 类型 + SPATIAL INDEX，小范围可用；
2. 大量实时附近查询：走 Redis GEO（GEOADD/GEOSEARCH），性能高；
3. 地理网格（GeoHash）：把坐标编码成网格，按网格 + 邻近网格查；
4. 别全表算距离：先按范围框选再精算；
5. 出行场景：附近车辆放 Redis GEO。
-- MySQL 空间查询示意，本示例基于 MySQL 8.0
SELECT id FROM t_driver_loc
WHERE ST_Distance_Space(
point(lng, lat),
point(116.40, 39.90)) < 2000; -- 2000米内，实际生产多用Redis GEO
追问
- 问：Redis GEO 原理？答：GeoHash + 有序集合，半径查询很快。
- 问：为什么不全 MySQL 查？答：全表算距离慢，实时性要求高的用 Redis GEO。
题目 9：UGC海量内容元数据存储
考点
- 分表；归档；检索。
参考答案
1. 按作者分表：作者作品落固定分片；
2. 媒体文件 OSS；
3. 多条件/标签检索走 ES；
4. 冷内容归档：老作品元数据归档；
5. 热度缓存：热门作品挡 Redis。
追问
- 问：推荐流要拉元数据？答：推荐结果先在召回层拿到内容 ID 列表，再批量回 DB/缓存取元数据。
- 问：怎么避免 N+1？答：批量
WHERE id IN (...)一次取。
题目 10：IoT海量设备数据怎么存
考点
- 设备档案 vs 遥测。
参考答案
1. 设备档案（元数据）存 MySQL：设备 ID、型号、绑定用户，低频改；
2. 遥测数据（上报）：高频写，走时序库（如 InfluxDB/TDengine）；
3. 别把遥测塞 MySQL：写 QPS 高、保留周期短；
4. 告警阈值：规则引擎处理，结果落 MySQL；
5. 冷热分层：遥测数据到期删除/降采样。
追问
- 问：设备状态要看实时？答：当前状态存 Redis，历史走时序库。
- 问：为什么 MySQL 不行？答：写入吞吐和时间范围聚合不是它的强项。
题目 11：设备心跳在线状态怎么维护
考点
- 心跳；过期。
参考答案
1. 心跳写 Redis：device:online 带 TTL；
2. 心跳续期：每次上报刷新 TTL；
3. 超时判定：TTL 到期即离线，无需主动下线任务；
4. 状态落库：在线/离线事件异步落 MySQL；
5. 批量：心跳别每次写 MySQL。
-- 设备心跳维护在线状态，本示例基于 MySQL 8.0
-- Redis: SET device:online:10086 1 EX 60 (60秒无心跳自动离线)
-- MySQL只异步落事件，高频心跳绝不直接写库
INSERT INTO t_device_event(device_id, event_type, created_at)
VALUES (10086, 'online', NOW()); -- 异步批量落，别每次心跳都写
追问
- 问：Redis 挂了？答：在线状态短暂不准，心跳恢复后重建。
- 问：在线设备数量统计？答：Redis 计数 + 定时落 MySQL。
题目 12：出行下单派单扣款的分布式事务
考点
- 多服务；最终一致。
参考答案
1. 下单：本地下单事务写订单表；
2. 派单：异步 MQ 通知派单系统；
3. 扣款：行程结束后异步结算；
4. 最终一致：本地消息表串联各服务，失败重试 + 对账；
5. 不强求跨服务大事务：状态机驱动，中间态可接受。
追问
- 问：派单失败？答：订单取消，回补/释放。
- 问：扣款失败？答：挂账，对账后补扣。
题目 13：用户行为埋点与MySQL边界
考点
- 埋点不进 MySQL；
- 业务库关联。
参考答案
1. 埋点明细：Kafka → 数仓/ClickHouse，不进 MySQL；
2. MySQL 只存：业务状态、需要事务的核心数据；
3. 关联键：埋点带 user_id/order_id，需要时回业务库；
4. 实时看板：ClickHouse 直接查；
5. 别为分析建宽表压在线库。
追问
- 问：要从埋点回查某用户订单？答：业务库按 user_id 查，埋点在数仓。
- 问：为什么 MySQL 不做分析？答：行存事务开销，聚合慢。
题目 14：实时数仓与业务库怎么同步
考点
- binlog；CDC。
参考答案
1. 业务库 binlog → CDC（Canal）→ Kafka → 实时数仓；
2. 业务库是权威，数仓是派生；
3. 实时层：ClickHouse 供实时报表；
4. 离线层：T+1 入 Hive；
5. 一致性：近实时延迟可接受，定期对账。
追问
- 问：数仓和业务库不一致？答：binlog 延迟，定期校准。
- 问：大表同步压力？答：增量同步，别全量频繁刷。
题目 15：行程进行中订单状态怎么流转
考点
- 长时间状态；事件驱动。
参考答案
1. 节点：已下单 → 已派单 → 司机接驾 → 行程中 → 到达 → 已完成；
2. 事件驱动：定位/状态事件推进订单；
3. 条件更新：WHERE status=旧；
4. 状态流水：每节点留痕；
5. 超时：长时间无事件触发异常处理。
追问
- 问：行程中位置频繁上报改订单？答：位置不改订单主状态，只更新实时位置表。
- 问：中途取消？答：走取消/逆向分支状态机。
题目 16：高并发地理空间查询怎么优化
考点
- 网格；缓存。
参考答案
1. 实时附近查询走 Redis GEO；
2. DB 侧：先按经纬度框范围（BBox）缩小候选，再精算距离；
3. GeoHash 网格：按网格前缀粗筛 + 邻近网格；
4. 热点区域缓存：热门区域结果缓存；
5. 避免全表算距离。
追问
- 问：BBox 怎么写？答：
lng BETWEEN ? AND ? AND lat BETWEEN ? AND ?走索引。 - 问：网格边界问题？答：查自己 + 周围 8 个网格，跨边界不漏。
题目 17：灰度发布与DB变更怎么配合
考点
- 向后兼容；双写。
参考答案
1. 新增字段先兼容：老代码不读新字段，新代码双写；
2. 灰度切流：新代码按比例放量；
3. 可回滚：发现问题切回老代码，新字段留着不影响；
4. 删字段最后做：等老代码全下线再删；
5. 在线 DDL：大表变更用 gh-ost 不锁表。
追问
- 问：加字段老表大？答：在线 DDL，低峰执行。
- 问：灰度期间数据格式？答：新旧格式兼容，别一次切死。
题目 18：千万级订单怎么分库分表
考点
- 分片键；扩容。
参考答案
1. 按用户 ID 哈希分库：用户侧查询落单分片；
2. 预留分片数：2 的幂，便于扩容；
3. 商家/运营维度走 ES；
4. 分布式 ID：趋势递增；
5. 扩容双写迁移。
追问
- 问：分几片？答：单表控制在千万内，按预估增长预留。
- 问：跨分片查询？答：避免，复杂检索走 ES。
题目 19：设备数据冷热分层怎么做
考点
- 时序数据生命周期。
参考答案
1. 热数据：近 7 天，高频查询，高性能存储；
2. 温数据：近 3 个月，标准存储；
3. 冷数据：3 个月以上，降采样/压缩/对象存储；
4. 自动下沉：按时间生命周期策略；
5. 查询路由：按时间范围自动路由。
追问
- 问：冷数据还要精确点查？答：低频即可，慢一点接受。
- 问：降采样丢精度？答：按业务决定，趋势用降采样，精确留热层。
题目 20：异地多活在出行IoT场景
考点
- 单元化；就近接入。
参考答案
1. 就近接入：用户/设备按区域路由到本单元；
2. 同数据本单元写：避免跨单元写冲突；
3. 异步复制：跨单元异步，容忍延迟；
4. 故障切换：单元级故障切到邻近单元；
5. 一致性：同一设备/订单只在一个单元写，多活不双写同一行。
追问
- 问：跨城位置怎么同步？答：订单级数据以本单元为准，跨城查询走中心汇总。
- 问：多活最难？答：冲突，原则是单元化避免同数据两边写。
速查速记
| 场景 | 实时数据 | MySQL存 |
|---|---|---|
| 出行位置 | Redis GEO/时序库 | 订单起终点 |
| 司机状态 | Redis心跳 | 档案/事件 |
| 推荐特征 | FeatureStore | 用户物品档案 |
| UGC媒体 | OSS | 元数据 |
| IoT遥测 | 时序库 | 设备档案 |
| 附近查询 | Redis GEO | 框选+精算 |
| 埋点 | Kafka+数仓 | 业务状态 |
| 在线状态 | Redis TTL | 离线事件 |
常见坑与注意事项
- 坑 1：实时轨迹/遥测直接写 MySQL——写 QPS 扛不住，应走 Redis/时序库；
- 坑 2：附近查询全表算距离——慢，先 BBox/网格粗筛，实时用 Redis GEO；
- 坑 3：心跳每次写库——高频写拖库，用 Redis TTL 维护在线态；
- 坑 4：把推荐特征塞 MySQL——几百维高 QPS 点查延迟顶不住；
- 坑 5：媒体文件存 BLOB——视频图片存 OSS，DB 只存 URL；
- 注意：本篇均为公开题型与通用场景设计，不代表任何公司真实架构。
复习清单
| 题目 | 核心结论 | 易错点 |
|---|---|---|
| 位置数据 | 时序/Redis存轨迹 | 别全塞MySQL |
| 实时状态 | Redis心跳 | DB异步落痕 |
| 特征存储 | FeatureStore | 别JOIN几百维 |
| UGC | 媒体OSS元数据DB | 别存BLOB |
| 高频写 | Redis+批量异步 | 别同步高频写 |
| 附近查 | Redis GEO | 别全表算距离 |
| IoT | 遥测时序库 | 档案才进MySQL |
| 多活 | 单元化就近写 | 别双写同一行 |
练习题
练 1：司机每 5 秒上报一次位置，10 万司机同时在线，MySQL 直接写位置表会怎样？怎么改？
<details>
<summary>参考答案</summary>
10 万司机 × 每 5 秒 = 2 万 TPS 高频写，且每次只关心最新位置，MySQL 单表扛不住也没必要。改法：当前位置写 Redis（覆盖更新），完整轨迹点攒批异步写时序库/OSS；MySQL 只存订单和结构化低频数据。
</details>
练 2：要查"我附近 2 公里内空闲司机"，在 MySQL 里怎么写才不慢？生产级方案是什么？
<details>
<summary>参考答案</summary>
MySQL 里先用经纬度 BBox 框选缩小候选（lng BETWEEN ... AND lat BETWEEN ...，建空间索引），再 ST_Distance_Space 精算；但生产高频实时场景直接用 Redis GEO（GEOADD/GEOSEARCH），GeoHash + 有序集合半径查询，毫秒级返回附近司机。
</details>
下一篇预告：第 36 章 高频手写SQL真题精讲
文章回复
0 条公开回复