把aggregated value 存在一个DB column vs live aggregation

最近面试被问到的。请教大家。
假设有一个DB table

1. users

复制代码

，里边有1 million existing records.
SchemaCREATE TABLE users (
        id int,
        name text,
        created_at timestamp,
        ....
);
然后有另一个DB table

1. user_actions

复制代码

.
SchemaCREATE TABLE user_actions (
        id int,
        user_id int,
        action text,
        created_at timestamp,
        ....
);
现在要设计一个API：如果每个user 最多只能perform 2 个 actions，返回所有可以再perform action 的users。
楼主当时说：
可以有两种design，
一种是live calculate the user action count. 比如用这个queryselect *
from users
where id not in (
        select user_id
        from user_actions
        group by user_id
        having count(id) >= 2
)
另一种是在

1. users

复制代码

table 加一个column

1. action_count

复制代码

或

1. is_available_for_more_actions

复制代码

. 每次user_actions 新写入一行，就update 那个新加的column。
楼主被问这两个有什么优缺点。
楼主说

• 第一个比第二个慢
• 第二个的一个潜在的缺点是concurrent update 可能存在race condition。但这个是可以克服的。比如 1）用 lock 2) 用DB trigger 3）用ORM 里的insert event listener。

被follow up:

• 第一种query 的时间复杂度是O(n), 第二种其实也是O(n)，所以其实scalability 一样啊？（楼主：但第一个花的时间也是要2X第二个啊？）
• 如果1,2,3 都没有还有什么办法确保
  1. action_count

  复制代码
  的准确性？（楼主缴械了，不知道。）
• 所以final design 要用哪个？（楼主说第二个。）

大家怎么看？

感觉楼主进了面试官的陷阱。第二种方法需要额外的写入操作，明显overall timeline要低于第一种。

回到题目本身，当数据量足够多的时候，单个api call不可能返回所有数据，你需要做的是分页，如果这个api经常被访问，那你需要提前cache一些数据。cache过期或者超出limit之后重新去db检索，返回的结果同时附在cache里。而且分页之后你可以通过db query的offset进行查询。

overiams 发表于 2025-04-02 07:31:08
谢谢。请问cleanup 这里指什么？意思是写入的时候总归还是要额外做一次calculation？

比如dump这个record去history table。这样这个table永远都是可以再act的user了，每次都是fullscan。

Smith_1298 发表于 2025-4-2 18:05
现在要设计一个API：如果每个user 最多只能perform 2 个 actions，返回所有可以再perform action 的users。 ...

我也是醉了。我要是面试官，对你的评价是不知道clarify requirements，没有辩证思维，混淆概念。

首先，你在提出分页的时候有考虑过user_actions table里有多少数据，data pattern 又是什么吗？假设有1 million users，999,999 个users 都有2个user actions了，请问你的API返回几个record？假设这个data pattern 永远是这样，导致API 永远返回很小的数据量，比如这个API是用于anormaly detection，你还要分页吗？

还有，我列出的方法1和方法2都可以分页，所以你提出分页的初衷是什么？我之所以没在贴里提分页是想剥离这个问题，在其他条件都相同的情况下请问大家2个方案的优缺点。

至于txn，你知道你用txn 最后是要干嘛的吗？还不是要lock 那个row？

能觉得我说的是in memory lock也蛮神奇，怎么不觉得我要propose 自己implement 一个新的RDBMS。算我没说清楚好了。

LXU 发表于 2025-04-01 16:14:51
感觉楼主进了面试官的陷阱。第二种方法需要额外的写入操作，明显overall timeline要低于第一种。
回到题目本身，当数据量足够多的时候，单个api ca

Typo:  第二种分法overheads更高

第二个每次user action要多写一条数据，之后query的时候也是要全局filter，不比第一个快吧

如果要额外写的话，是不是在建议额外cleanup。

现在要设计一个API：如果每个user 最多只能perform 2 个 actions，返回所有可以再perform action 的users。

要是users很多的化，你不管用第一个还是第二个都是很慢的。所以这里的关键在于这个API用来干嘛的。这个API不太可能用来处理用户请求，因为处理用户请求我们应该是去判断当前user是不是还可以再执行action，而不需要所有可以再执行action的user。所以这个看上去是某个管理后台用的api，这样的话比较好的方法是分页。

楼主说
第一个比第二个慢 -- 第一个更新比第二个快，但是查找比第二个慢；第二个查找比第一个快，尤其是有分页的情况下，但是更新比第一个慢。
第二个的一个潜在的缺点是concurrent update 可能存在race condition。但这个是可以克服的。比如 1）用 lock 2) 用DB trigger 3）用ORM 里的insert event listener。 -- 我不知道你在说什么，很明显这里应该是用数据库transaction（事务）。当然你可以说事务是用lock实现的，但是你只说lock我会认为你是在内存里面加了个锁，这显然是不够的。

我要是面试官的化，对你这位候选人的评价是没有写过需要和数据库交互的应用，不知道返回大量数据需要分页，也不清楚事务的概念和使用方法。

LXU 发表于 2025-4-2 07:14
感觉楼主进了面试官的陷阱。第二种方法需要额外的写入操作，明显overall timeline要低于第一种。

回到题目 ...

谢谢~分页和cache都是很好的建议。
想请问是不是也应该考虑usage pattern 呢？如果read >>> write，也是live calculation比较好吗？

argumentt 发表于 2025-4-2 11:18
第二个每次user action要多写一条数据，之后query的时候也是要全局filter，不比第一个快吧

谢谢~如果在读远多于写的情况下，第一个还是更优吗？那样的话每个API call 不是都要额外的query或join？

t__c___ 发表于 2025-4-2 13:13
如果要额外写的话，是不是在建议额外cleanup。

谢谢。请问cleanup 这里指什么？意思是写入的时候总归还是要额外做一次calculation？