3 万条预约数据 + SQLite:把查询从 800ms 压到 30ms 的五个决定
3 万条预约数据 + SQLite:把查询从 800ms 压到 30ms 的五个决定
背景
我们的校园自习空间调度系统,数据量不大但查询很"刁":
• 3 万条预约记录、556 个座位、10 个房间;
• 高频查询是"某房间某时段的到座率""某楼宇某时段还有多少空座""近 7 天各小时的热力画像";
• 部署环境是一台普通办公电脑,用的是 SQLite(零运维、单文件、随包带走)。
第一版跑起来首页要 800ms 以上,热力图页面更是 2 秒开外。优化后同样的查询在 30ms 量级。下面是真正起作用的五个决定。
决定一:把"时段"存成整数小时,并且左闭右开
CREATE TABLE bookings (
id INTEGER PRIMARY KEY,
seat_id INTEGER, room_id INTEGER,
date TEXT NOT NULL, -- 'YYYY-MM-DD'
start_hour INTEGER NOT NULL,
end_hour INTEGER NOT NULL,
status TEXT NOT NULL -- completed/no_show/cancelled/upcoming
);
• 不存 timestamp:自习场景的粒度就是整点,存成 8~22 的整数,比较和聚合都是整数运算;
• 区间左闭右开:9-11 点只占 9、10 两个整点。这条口径不统一,后面所有指标都会偏。
决定二:重叠判定用"相交",不用"字段相等"
很多人第一反应是加唯一索引 UNIQUE(seat_id, date, start_hour, end_hour)——这是错的:
9-11 和 10-12 在四个字段上完全不同,但实际抢同一个座位。
正确做法是相交判定:
SELECT seat_id FROM bookings
WHERE date = ?
AND status IN ('completed','upcoming')
AND start_hour < :end AND end_hour > :start
这样"任意时长、任意起点"的查询都能一次扫出来。
决定三:宁可展开成"座位·小时",也不写 N+1
到座率需要「房间 × 小时」的占用计数。最容易写成 N+1:先查房间列表,再对每个房间查每小时——几十次往返,直接吃掉几百毫秒。
我们的做法是一次查询把原始行全取回来,在内存里展开成桶:
rows = SELECT room_id, start_hour, end_hour, status FROM bookings WHERE date >= ?
for r in rows:
for h in range(r.start_hour, r.end_hour):
occ[(room_id, h)] += 1
3 万行在 Python 里展开只要几十毫秒,比"几十次 SQL 往返"快一个数量级。
小数据量时,把聚合挪到应用层往往比堆 SQL 更划算。
决定四:索引只建两个,但建对
CREATE INDEX idx_booking_date ON bookings(date);
CREATE INDEX idx_booking_room ON bookings(room_id, date);
• date 是绝大多数查询的第一过滤条件(近 7 天、近 14 天、某一天);
• (room_id, date) 覆盖"某房间的时间序列"这类查询;
• 没有盲目给 status、seat_id 单独建索引——选择性低,建了反而拖慢写入。
另外开了 PRAGMA journal_mode = WAL,多线程读(Flask threaded=True)时明显更顺。
决定五:把"每次请求都重算"的东西缓存成一次
房间特征(饱和度/爽约率/停留时长/热度)是所有页面的公共输入,但计算要扫全表。
第一版每个接口各算一遍,驾驶舱一次请求要算 4 次。
改成进程内缓存 + 5 分钟 TTL:
_cache = {"t": 0, "data": None}
def room_features(days=14):
if time.time() - _cache["t"] < 300 and _cache["data"]:
return _cache["data"]
...
注意这是"可容忍 5 分钟陈旧"的场景(运营看板、推荐排序),所以缓存安全;
而"某座位此刻是否空闲"这种实时判断,必须直查,不能缓存。
效果
| 查询 | 优化前 | 优化后 |
|---|---|---|
| 驾驶舱首页(含 4 组聚合) | ~800 ms | ~30 ms |
| 房间×小时热力图(10×15 格) | ~2 s | ~120 ms |
| 座位推荐(含占用过滤) | ~400 ms | ~60 ms |
| 数据质量体检(7 项) | ~1.2 s | ~200 ms |
小结
• 口径先定死(整点、左闭右开、区间相交),否则优化只是把错误算得更快;
• 小数据量下 SQL 少而准 + 应用层聚合 往往优于复杂的单条大 SQL;
• 索引不是越多越好,按"第一过滤条件"建复合索引;
• 缓存要按"数据陈旧的可容忍度"分级:看板可缓存,实时判定不可。
这套东西没有任何"高级优化",只是把数据模型和查询路径想清楚了。对校园级应用来说,这足够了。
- 点赞
- 收藏
- 关注作者
评论(0)