记账小程序月度预算：规则表、快照表与惰性生成

项目做到第七篇回头看记账小程序里最容易返工的一块不是账单录入也不是分类体系而是月度预算。账单是已经发生的事实结构再复杂它也只增不改预算偏偏相反它是还没发生的打算随时会被用户改主意。于是几乎所有自己动手写记账工具的人在动手建表的那一刻都会卡在同一个问题上设置月度预算是不是得给每个月都提前塞一条记录进去要么一次性把未来十二个月铺满要么每个月开头靠定时任务补一条。我第一次做的时候就选了铺满结果三个月后用户改了一次预算历史月份的数字跟着全变了用户来找我说我上个月明明没超支怎么记录变红了。那次之后我把这块彻底重做了一遍。下面这篇就是把这个决策从头到尾拆开讲清楚它为什么会成为一个问题、几种主流做法在实际项目里各自会烂在哪、我最后落地成什么结构、以及那些只有跑起来几个月才会冒出来的边界情况。1. 先把问题定性预算是配置项还是账本行1.1 一个问法背后其实是两种数据姿态很多人以为这是个要不要批量插入的技术问题其实不是。它是个数据建模问题问的是你把月度预算当成一份可以随时被覆盖的当前配置还是当成一条写进时间轴、事后不再轻易改动的事实记录。这两种姿态一旦选定后面的表结构、查询方式、修改逻辑、甚至前端交互全都跟着变。举个生活里的类比。家里的空调设定温度是一个当前配置你调了就是调了昨天设的 26 度没人关心。但物业的水电缴费单是一个历史事实二月份缴了多少就是多少你不能因为三月份涨价了把二月的单子也改了。预算这个东西尴尬在于它同时具备两种属性用户心里想的是我每个月都花这么多这是配置但用户月底复盘的时候看的又是我三月定了三千花了二千八这是事实。记账类产品里凡是同时具备两种属性的东西都必然要拆成两层来存。这不是我拍脑袋的规矩是踩坑踩出来的肌肉记忆。1.2 铺满全年的诱惑从哪来为什么大多数人第一反应是每个月加一条记录因为这个做法最符合直觉也最容易在前端偷懒。你想列表页要显示一月预算、二月预算、三月预算如果每个月都有一条记录直接select * from budget where user_id ? order by month就完了缺的月份天然就是空的UI 上显示未设置即可。我第一次做的时候就是这么想的还特意写了个初始化逻辑用户首次进入预算页自动把当年剩下的月份全建上金额默认等于用户填的那个数。当时的心理活动是用户反正每个月都一样一次性建好省得以后每次判断空不空。这个决定在头两周完全没暴露问题。问题出在第三周一个用户在三月中旬把月预算从三千改成了两千五因为他发现自己实际开销没那么大。改完之后他翻到二月的记录发现二月也变成了两千五二月本来花了二千八原本是没超支改完变成超支了红条刺眼地亮着。他来问你们把我二月的记录改了没有改是我三个月前铺的那十二条记录查询时取了最新设置值去覆盖显示。这就是把配置当事实用的后果。1.3 结论先摆在这里我不会卖关子。不要为每个未来月份预先创建记录也不要永远只维护一条会被覆盖的记录。正确的做法是中间态一层存预算规则用户当时怎么想的带生效时间一层存月度快照那个月具体是多少一旦生成就不再被后续修改影响。月度快照用惰性生成第一次真正需要这个月的数据时才建。下面几节就是把这条路线的每一个岔口讲透包括哪些人应该走别的路线。2. 三种路线放在一起比覆盖式、铺满式、规则加快照2.1 覆盖式一条记录走天下覆盖式最省事用户表旁边挂一个monthly_budget字段就行连表都不用建。改预算就是一次 update查询就是一次 select性能好到没话说。适合什么场景适合那种只关心当下、不关心历史的工具——比如一个本月还能花多少的简单计数器用户压根不会去翻上个月的预算是多少。但只要你做了月度对比超支历史年度预算完成率这类功能覆盖式立刻就崩了。因为历史数据从来没存在过你无从回溯。有些同学会说那我在账单表里记一下当时的预算不就行了这又引出另一个问题预算是随时会改的你记在账单上的是记账那一刻的预算不是那个月最终的预算两者在月中改预算的情况下根本对不上。我见过一个折中玩法每次修改预算往一张budget_history表里插一条(user_id, changed_at, amount)查询某个月的预算时取该月最后一天之前最后一条变更记录。这个思路其实是事件溯源的简化版也叫版本化预算理论上完全正确。问题在于查询特别啰嗦每个月的预算都要做一次区间查找一旦要按月批量算年度数据SQL 会写得非常难看而且索引不好建。我在一个统计接口里试过二十万条变更记录下年度报表的查询从一百二十毫秒涨到一点四秒。2.2 铺满式开号即建满一年铺满式的思路前面已经批判过一部分但它不是完全没有价值。如果你做的工具预算金额常年不变、且用户基本不会回改历史铺满式配合只允许修改当月及以后的限制是可行的。它的优点是查询极其简单UI 天然对齐缺月就是未设置。它真正的成本藏在三个地方。第一是新建与补建的维护成本跨年怎么办用户十二月注册要不要把明年一月也建上如果只建当年那到了明年一月用户打开应用发现未设置预算会懵。第二是修改语义的歧义用户在设置页改金额改的是仅本月还是本月及以后全部如果铺满了记录你就必须让用户明确选而这个选择对普通用户来说太复杂了。第三是删除与重置用户想清空预算你得删掉十二条记录还要考虑删完之后列表页空一片的观感。2.3 规则加快照把打算和事实分开放这是我现在固定用的方案结构分两张表budget_rule存用户从某个月开始每个月打算花多少budget_period存某年某月这个月实际生效的预算是多少。规则可以只有一条也可以随时间有多条用户改主意就新加一条旧规则保留生效区间快照按需生成生成之后就是一个独立的、可以被单独修改的数字。用户改预算时交互上是两个动作改以后的默认值动规则表或者改就这个月动快照表。前者不影响已经生成的历史快照后者只影响当前这一条。这两个动作在用户心智里是完全清晰的——我以后每月都改和我这次就特事特办日常沟通里大家就是这么说话的。下面这张表把三条路线的关键差异摆平了维度覆盖式单字段铺满式预建12月规则加快照推荐表数量0 到 112历史可回溯否是但会被批量改污染是快照生成后独立月中改预算语义无历史概念简单需要用户额外选择范围天然拆成两个动作跨年处理无所谓需要补建逻辑规则自动延续无需补建查询成本极低低低快照已物化首次初始化无需批量插入一条规则即可适合场景纯当月计数器预算恒定、不回改有历史、有对比需求2.4 什么时候你可以走捷径话说回来不是所有项目都值得上两层结构。如果你做的是给自己用的小工具或者产品明确只做本月还剩多少可花这一个功能覆盖式完全够用多建一张表都是浪费。判断标准很简单问自己一句用户会不会问我上个月预算多少。会就上两层不会就一条字段。3. 表结构落地规则表与月度快照的字段设计3.1 规则表只描述当时是怎么想的budget_rule的核心字段是金额、生效起始月、生效结束月可空表示一直有效、创建时间。注意这里我用的是月而不是日期因为预算的粒度天然就是月用YYYY-MM的字符串或int如 202403都比日期类型省心排序和比较都不会出错。CREATE TABLE budget_rule ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, amount DECIMAL(12,2) NOT NULL, -- 每月总额度 effective_from CHAR(7) NOT NULL, -- 2024-03 effective_to CHAR(7) DEFAULT NULL, -- NULL 表示持续有效 created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL ); CREATE INDEX idx_rule_user_from ON budget_rule(user_id, effective_from);一个用户理论上可以有多条规则形成一条时间链2024-03 到 2024-05 是三千2024-06 起变成两千五。查询某个月该用哪条规则就是找effective_from 目标月 AND (effective_to IS NULL OR effective_to 目标月)的那一条。这里有个必须说清楚的约定新规则生效时要把上一条规则的effective_to补上形成闭合区间。否则两条规则同时覆盖同一个月查询就会返回两行你得在代码里靠排序兜底那是隐患。我在代码里把这件事封成一个replaceRule方法写新规则和闭合旧规则放在同一个事务里避免出现时间链断裂。3.2 快照表只描述那个月定了多少budget_period才是真正被 UI 读取、被统计接口聚合的表。它的关键约束是(user_id, period)唯一一条记录代表一个用户一个月的预算事实。CREATE TABLE budget_period ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, period CHAR(7) NOT NULL, -- 2024-03 amount DECIMAL(12,2) NOT NULL, source TINYINT NOT NULL DEFAULT 1,-- 1由规则生成 2用户单独修改 rule_id BIGINT DEFAULT NULL, -- 溯源用可为空 created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, UNIQUE KEY uk_user_period (user_id, period) );source这个字段看着不起眼作用却很大。它让你能区分这个月的预算是我从规则里推出来的还是用户手动改过这个月。用户后来改规则的时候你可以只对source 1的月份不做任何动作已经生成的快照就是事实而对未来还没生成的月份自然用新规则。如果哪天要做重置本月为默认值也是靠这个字段判断该回退到哪条规则。rule_id是溯源字段出问题时能一眼看出这条快照是从哪条规则来的。别小看这个我曾经遇到用户投诉我明明设的是三千怎么显示两千八靠这个字段三分钟定位到是他自己在某个测试账号上留了一条脏规则。3.3 分类预算挂在快照下面不挂在规则下面如果产品要支持分类预算比如吃饭一千、交通五百分类必须挂在budget_period上形成一对多不能挂在规则上。原因还是那个分类预算是最容易被用户在中途调整的东西如果挂在规则上改一次分类就要动规则规则的时间链会碎成一地。CREATE TABLE budget_category ( id BIGINT PRIMARY KEY AUTO_INCREMENT, period_id BIGINT NOT NULL, category_id BIGINT NOT NULL, amount DECIMAL(12,2) NOT NULL, UNIQUE KEY uk_period_category (period_id, category_id) );有一个细节值得单独提分类预算之和不一定要等于总预算。很多产品强行校验相等用户体验极差因为用户往往只给几个主要分类定额剩下的留给其他。所以我在设计上允许分类之和小于总额剩余部分视为未分配额度前端展示成一条灰色的未分配占位。4. 月度快照什么时候生成惰性、预生成与查询兜底4.1 惰性生成用到才建回答标题那个问题——不需要给每个月都添加记录需要的时候再添加。这就是惰性生成。用户打开三月账单页系统发现budget_period里没有 2024-03就去budget_rule里找当前生效的规则把金额算出来插入一条快照然后返回。整个过程对用户无感。这个做法的好处非常直接没被访问过的月份表里就是没有记录不会产生任何冗余规则改了未来没访问过的月份自然用新规则规则删了未来没生成的月份自然没有预算逻辑自洽。def get_or_create_period(user_id, period): row db.query_one( SELECT * FROM budget_period WHERE user_id%s AND period%s, (user_id, period)) if row: return row rule db.query_one( SELECT * FROM budget_rule WHERE user_id%s AND effective_from %s AND (effective_to IS NULL OR effective_to %s) ORDER BY effective_from DESC LIMIT 1, (user_id, period, period)) if not rule: return None # 这条时间链上没有预算 try: db.execute( INSERT INTO budget_period(user_id, period, amount, source, rule_id, created_at, updated_at) VALUES(%s,%s,%s,1,%s,NOW(),NOW()), (user_id, period, rule[amount], rule[id])) except DuplicateKeyError: pass # 并发下另一个请求已经建好了 return db.query_one( SELECT * FROM budget_period WHERE user_id%s AND period%s, (user_id, period))上面这段代码有两个地方值得划重点。第一是DuplicateKeyError的捕获这是靠唯一索引兜并发比先查后插的写法安全得多双端同时打开预算页的场景比你想象中常见。第二是规则查询里加了ORDER BY effective_from DESC LIMIT 1这是为时间链里万一出现区间重叠的情况做兜底取最近生效的那条。4.2 预生成只有少数场景才值得有些同学担心惰性生成会让上个月没打开过应用的用户历史月份缺记录。这个担心是合理的但解法不是预生成而是在需要历史数据的地方也走同一个get_or_create_period。比如年度报表接口遍历十二个月挨个调用一次缺的补上逻辑统一没有第二条路径。真正需要预生成的场景只有一种你要给用户发月度预算提醒推送。推送是后台任务触发的没有用户访问这个动作所以必须由任务主动去把当月快照建出来。这时候写一个每月一日凌晨跑的定时任务只建当月一条不建未来。注意定时任务预生成时务必做成存在即跳过的幂等逻辑并且只建当月绝不建未来月份。我见过有实现把未来三个月一起建了结果用户月中改规则未来三个月还挂着旧金额排查了半天才发现是预生成的锅。4.3 月初零点的时区坑这个坑不写出来一定有人踩。服务器一般用 UTC用户在东八区。用户在当地时间五月一日早上七点打开应用UTC 时间还是四月三十日二十三时。如果你的当前是哪个月是用服务器时间算的系统会认为现在是四月把四月的快照又建了一遍用户看到的还是四月。处理办法很朴素月份的计算一律用用户所在时区不用服务器时区。用户表里存一个时区字段或者在客户端把当前YYYY-MM传上来服务端只做校验。预算这种按月粒度的东西用客户端传月份加服务端校验格式是成本最低也最不容易错的方案。5. 改预算的三个陷阱月中调整、补记旧账、删除重置5.1 月中改预算已经花掉的钱怎么算用户在三月十五号把预算从三千改成两千五而三月前半月已经花了二千二。这时候进度条应该怎么画按新预算算剩三百进度 88%用户会觉得很慌按旧预算算又不合逻辑因为他说了以后要按两千五过。我的处理是照实算但给一条提示。进度条按新预算走同时在旁边加一行小字本月预算已调整调整前支出 2200 元。这个提示的数据来源是快照表的updated_at加上账单表的时间过滤一条简单查询就能拿到。加这行字的转化效果很好用户会觉得这个工具懂我在干什么而不是这工具又抽风了。另一个更细腻的做法是记录预算的变更历史画一条折线图显示预算随时间的变化。这个属于锦上添花我建议第一版别做等有用户主动提再说。5.2 补记上个月的账撞上快照的时间线记账工具一定会有补记功能用户三月二十号想起来二月有一笔账没记补进去。这时候如果二月的快照还没生成过补账会触发一次惰性生成金额取的是当前生效的规则而不是二月份当时的规则。如果用户在三月改了规则二月的快照就会被填成新金额历史又被污染了。解决方案只有一个惰性生成时永远用目标月份去匹配规则不要用当前月份。上面那段代码里我用的是effective_from periodperiod是目标月不是今天就是为了这个。这个细节极其容易写错因为大多数人潜意识里觉得生成快照当然用现在的设置恰恰相反。5.3 删除预算时要分清删的是规则还是快照用户点清空预算你的代码要回答一个问题删的是以后都不设预算了还是这个月不想设了这两件事完全不同。界面上我把它拆成两个入口措辞也刻意区分开设置页里是关闭每月预算删规则未来不再生成月度详情页里是清除本月预算删这一条快照。前者删规则时我不物理删除而是闭合它的effective_to为当前月的前一个月这样历史规则的痕迹还在出问题能追溯后者直接删快照行因为它是一条独立事实。这里有个反直觉的点闭合规则时不要动已经生成的快照。用户关了预算但他翻看三月的记录三月那三千块应该还在因为他三月确实是按三千过的。如果连快照一起清了历史报表就变成一片空白用户会觉得数据丢了。6. 已用金额与剩余额度的计算口径6.1 实时聚合还是缓存计数预算模块最常被调用的接口是本月已花多少。这个数字要么实时从账单表聚合要么维护一个计数缓存。我一开始选了缓存在账单增删改时同步更新budget_period.spent跑了一段时间发现对不上账原因是补记、批量导入、跨月编辑这几条路径里有一条忘了更新缓存。后来我改回实时聚合靠索引顶SELECT COALESCE(SUM(amount), 0) AS spent FROM bill WHERE user_id %s AND bill_date %s AND bill_date %s AND deleted 0;配合(user_id, bill_date)的联合索引十万级数据下单次查询在十毫秒以内完全够用。只要你没到百万级、也没做复杂的多表关联就别上缓存缓存带来的是一致性债务记账工具最怕的就是数字对不上。如果哪天真的扛不住了正确地加缓存的方式是记账时给相关月份的缓存打个标记读取时发现标记过期就重算而不是增量加减。增量加减在并发和补记场景下极难保证正确。6.2 分类预算之和超过总预算怎么办用户给吃饭定两千交通定一千总额度只设了两千五。这种输入在真实用户里大量存在因为人是先想分类再想总数的。我的做法是允许保存但给一个温和的提示分类合计 3000已超过总预算 2500。不阻止不弹窗就在页面上标黄。为什么不硬拦因为你拦了他就填不出东西了用户会直接放弃设置分类预算这个功能。产品设计上有句话叫不要用校验阻止用户表达意图放在这里很合适。展示层面进度条以总预算为分母分类进度以各自额度为分母两者独立计算互不干扰。如果用户确实超了前端在总额度旁边标一个分类已超额的小标记足矣。6.3 剩余额度可能出现负数这是必须接受的现实。用户预算两千五花了三千二剩余就是负七百。不要 clamp 到零也不要显示成0。显示负数并配上已超支 700 元的文案才是用户真正需要的信息。我见过一些实现把负数截断成零进度条卡在 100%用户完全不知道自己超了多少这种设计属于自欺欺人。进度条本身也要做上限处理超过 100% 的部分用另一种颜色比如深红继续延伸一小段或者干脆封顶但数字照实显示。我的选择是封顶加数字照实因为无限延伸的进度条在窄屏上会把布局撑破。7. 迁移、索引与几个只有跑起来才会遇到的情况7.1 老数据迁移别想着一步到位如果你已经有了一批用覆盖式存的用户要迁到规则加快照千万不要跑一个脚本把所有历史月份都补出来。正确做法是为每个有预算的用户在其首次设置预算的那个月生成一条规则起始月就是那个月然后什么都不做。历史快照让它在用户真的访问时惰性生成金额取这条规则的值。这样迁移脚本极短出问题的影响面也小。我第一版迁移脚本图省事直接把过去十二个月全部补了快照结果有个用户的历史账单月份跨度是三年补出来的数字全是他现在的预算前后矛盾只能回滚。7.2 索引要按真实查询建budget_period上最关键的索引是(user_id, period)的唯一索引它同时承担了查询和并发保护两个职责。budget_rule上是(user_id, effective_from)。别在period单列上再建一个索引选择性不高纯属浪费。还有一个容易忽略的如果做年度报表要WHERE user_id ? AND period LIKE 2024-%LIKE前缀匹配在CHAR(7)上是可以走索引的但更稳妥的写法是period 2024-01 AND period 2024-12区间查询对优化器更友好。7.3 一个我自己踩过的并发坑双端同时打开预算设置页用户在一个端改了规则另一个端还拿着旧规则的数据提交。如果服务端直接覆盖后提交的会赢用户会觉得我明明改过。处理方式是给规则表加一个version字段更新时带版本号做乐观锁冲突时提示用户预算已在其他设备修改请刷新后重试。这个改动只多了一个字段和一行 where 条件但省掉了大量我的设置丢了的客诉。7.4 关于要不要给每个月都添加记录的最终回答回到标题。答案是不要预先给每个月添加记录但也要保证每个被访问到的月份最终有一条属于自己的记录。前一句是为了避免冗余和批量污染后一句是为了让历史可回溯、可比较、可解释。这两句话合起来就是规则表加惰性快照的完整含义。我自己在这个项目上前后推翻过两次设计第一次是因为铺满全年导致历史被追认第二次是因为只存单条导致年度报表做不出来。两次返工加起来的时间比一开始就老老实实拆两层多花了三倍。如果你现在正在纠结这个点我建议你别省这一步两张表、一个唯一索引、一个惰性生成的函数半小时就能搭好后面能省掉一整轮的返工。真正麻烦的从来不是表结构而是等到用户数据攒了三个月之后你才发现口径错了却不敢动。