3 万条预约数据 + SQLite:把查询从 800ms 压到 30ms 的五个决定

举报
年年真快乐 发表于 2026/11/13 08:44:45 2026/11/13
【摘要】 3 万条预约数据 + SQLite:把查询从 800ms 压到 30ms 的五个决定背景我们的校园自习空间调度系统,数据量不大但查询很"刁":• 3 万条预约记录、556 个座位、10 个房间;• 高频查询是"某房间某时段的到座率""某楼宇某时段还有多少空座""近 7 天各小时的热力画像";• 部署环境是一台普通办公电脑,用的是 SQLite(零运维、单文件、随包带走)。第一版跑起来首页要 ...

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;

• 索引不是越多越好,按"第一过滤条件"建复合索引;

• 缓存要按"数据陈旧的可容忍度"分级:看板可缓存,实时判定不可。

这套东西没有任何"高级优化",只是把数据模型和查询路径想清楚了。对校园级应用来说,这足够了。

【声明】本内容来自华为云开发者社区博主,不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0)

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。