在一个订单管理或者日志分析类的系统里，最常见的需求就是各种维度的统计报表：每天的订单量、每月的销售额、每个分类的商品销量排行等等。如果这些统计都是查询的时候现算，也就是每次都对着几十万上百万行的明细表执行GROUP BY，SQLite这种单文件数据库很快就会扛不住。解决办法是引入中间表，也就是预先把聚合结果算好存起来，查询的时候直接读中间表，一条索引命中的查询几乎可以在毫秒级返回。本文结合一个完整的实战项目，把中间表的设计、维护和优化过程完整讲一遍。
为什么需要中间表：从一条慢SQL说起
假设我们有一张订单明细表，结构大致如下，业务系统运行半年后表里已经积累了八十多万行数据。运营后台首页要展示每天的订单数量和销售额，最初的实现非常直接，就是一条聚合查询：
-- 每日订单统计，直接对明细表聚合
SELECT date(create_time) AS stat_date,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM t_order
WHERE create_time >= '2024-01-01'
GROUP BY date(create_time)
ORDER BY stat_date DESC;这条SQL在数据量小的时候没有任何问题，但随着明细表增长到几十万行，查询时间逐渐从几十毫秒涨到两秒以上。原因不难分析：GROUP BY配合date函数导致无法有效利用索引，SQLite必须做全表扫描，对每一行调用函数后再分组，这个计算成本随着数据量线性增长。更要命的是，后台首页可能同时展示日统计、月统计、分类排行好几个报表，每打开一次页面就要跑好几条这样的重查询。
中间表的核心思路是把计算提前。既然统计结果的变化频率远低于明细数据的变化频率，我们完全可以把聚合结果算好存起来，明细表每多一批新数据，只需要增量更新对应的统计行。这样查询压力就从全表扫描变成了一次主键查找，性能差距往往是几个数量级的。本质上这是一种典型的用空间换时间、用写入复杂度换查询速度的取舍。
中间表结构设计与维度规划
设计中间表的第一步是梳理清楚统计维度。拿订单场景来说，常见的维度有时间、商品分类、订单状态，统计指标则有订单数、销售额、客单价等。维度和指标的组合方式决定了中间表的数量。这里推荐按时间粒度拆分成多张表，而不是一张大宽表塞所有维度，因为不同报表的查询模式差异很大，拆开后每张表都能保持结构简单。
以每日统计中间表为例，结构可以这样设计：
CREATE TABLE t_stat_daily (
stat_date TEXT NOT NULL, -- 统计日期，格式 YYYY-MM-DD
category_id INTEGER NOT NULL, -- 商品分类，0表示全部分类
order_count INTEGER NOT NULL DEFAULT 0, -- 订单数
total_amount REAL NOT NULL DEFAULT 0, -- 销售额
refund_count INTEGER NOT NULL DEFAULT 0, -- 退款单数
updated_at TEXT, -- 最后更新时间
PRIMARY KEY (stat_date, category_id)
);有几个设计细节值得注意。第一，主键采用复合主键(stat_date, category_id)，这样查询任意一天任意分类的统计就是一次主键定位，不需要额外建索引。第二，category_id为0的行表示全站汇总，虽然存在一定的数据冗余，但可以避免查询时再做SUM合并，报表接口读起来非常省事。第三，金额字段用REAL存储，如果业务对精度敏感，可以改用整数存分，避免浮点误差。第四，updated_at字段记录了统计数据的最后刷新时间，方便排查数据是否延迟。
除了日统计，通常还需要一张月统计表t_stat_monthly，结构与之类似，把stat_date换成stat_month（格式YYYY-MM）即可。分类排行这类非时间维度的报表，则可以单独设计一张汇总表，只保留category_id作为主键。维度规划的原则是：一张中间表服务一类报表，不要试图用一张表覆盖所有查询，否则维度组合爆炸后维护逻辑会变得极其复杂。
数据维护策略：增量更新与全量重建
中间表的生命力在于数据能跟上明细表的变化，维护策略主要有两种。第一种是增量更新，只处理新增或变更的明细数据，适合数据持续写入的在线系统。实现方式可以用SQLite的触发器，在明细表插入时自动更新统计行：
CREATE TRIGGER trg_order_insert AFTER INSERT ON t_order
BEGIN
INSERT INTO t_stat_daily (stat_date, category_id, order_count, total_amount, updated_at)
VALUES (date(NEW.create_time), NEW.category_id, 1, NEW.amount, datetime('now'))
ON CONFLICT(stat_date, category_id) DO UPDATE SET
order_count = order_count + 1,
total_amount = total_amount + NEW.amount,
updated_at = datetime('now');
END;触发器方案的优点是实时性最好，统计结果和明细数据几乎零延迟。但它也有明显的代价：每插入一笔订单都要多执行一次upsert操作，写入吞吐量会下降，而且当明细表存在更新和删除（比如订单退款、撤销）时，对应的触发器逻辑会变得很难写对，减法运算一旦哪里漏了，统计数据就会悄悄漂移，排查起来相当痛苦。
第二种策略是定时任务全量或半全量重建，这也是我在实际项目里最终采用的方案。做法是每隔一段时间（比如每五分钟或每小时）跑一次统计任务，只重算最近N天的数据，用DELETE加INSERT的组合把旧结果替换掉：
-- 在事务中重算指定日期范围的统计
BEGIN;
DELETE FROM t_stat_daily WHERE stat_date >= '2024-06-01';
INSERT INTO t_stat_daily (stat_date, category_id, order_count, total_amount, updated_at)
SELECT date(create_time), category_id, COUNT(*), SUM(amount), datetime('now')
FROM t_order
WHERE create_time >= '2024-06-01'
GROUP BY date(create_time), category_id;
COMMIT;这种重算方式的好处是逻辑天然幂等，不管明细数据怎么被修改或删除，只要落在重算窗口内，下次任务跑完统计结果一定和明细表一致，彻底避免了增量减法出错的问题。配合SQLite的事务特性，整个替换过程对读请求来说是原子的，报表查询不会读到中间状态。窗口大小可以根据业务调整，订单场景取七天基本够用，因为历史订单极少回溯修改。而对于确实需要实时性的场景，可以把两者结合：触发器维护当天的数据，定时任务每天凌晨重算前一天，兜住触发器可能累积的误差。
查询侧改造与实际效果对比
中间表建好之后，业务侧的报表查询要全部改写成查中间表。改造本身很简单，比如原来的日统计接口改成下面这样：
-- 改造后：直接读中间表，毫秒级返回 SELECT stat_date, order_count, total_amount FROM t_stat_daily WHERE category_id = 0 AND stat_date BETWEEN '2024-06-01' AND '2024-06-30' ORDER BY stat_date;
改造前后的差距非常直观。在八十万行明细数据的规模下，原来那条GROUP BY查询平均耗时约2.3秒，改成中间表后走主键范围扫描，耗时稳定在1毫秒以内，提升超过两千倍。同时因为报表查询不再碰明细表，明细表上的写入也变得更轻快，两者互不干扰。月报表的对比更夸张，跨一年数据的聚合原来接近8秒，现在同样是一次索引范围查找解决。
还有两点运维层面的经验值得分享。一是要给统计任务加上执行日志，记录每次任务的耗时和处理的行数，一旦发现耗时异常增长，往往是明细表出现了预料之外的大批量写入，能及早暴露问题。二是中间表本身也需要备份策略，虽然它可以随时从明细表重建，但重建历史全量数据可能耗时较长，定期把SQLite文件纳入备份计划仍然是更稳妥的做法。整套方案不依赖任何外部组件，触发器、定时任务、事务全部由SQLite自身能力承担，特别适合嵌入式设备、桌面软件这类没有独立数据库服务器的项目直接落地。