数据库优化 - 有总计字段或查询表吗？
我有一个应用程序，用户可以根据他们执行的操作获得积分 - 简单的操作可以获得 1 分，困难的操作可以获得 2 分。 我希望向用户显示他在我的应用程序中获得的积分总数以及本周（从星期一午夜开始）获得的积分。
我有一个表记录所有动作，以及它们的时间和点数。
我有两种选择，但不确定哪个更好：
- 每次用户看到报告时，都会执行查询并对用户获得的积分进行求和
- 向每个用户添加两个字段，记录迄今为止获得的积分数（总计和每周） ）。每周积分值每周一午夜将设置为 0。
第一个选项更容易，但恐怕因为我会得到很多用户和操作查询将需要很长时间。
第二个选项存在操作表与汇总值不一致的风险。
我对您认为最好的选择非常感兴趣。
谢谢， 多里安
I have an app where users get points for actions they perform - either 1 point for an easy action or 2 for a difficult one.
I wish to display to the user the total number of points he got in my app and the points obtained this week (since Monday at midnight).
I have a table that records all actions, along with their time and number of points.
I have two alternatives and I'm not sure which is better:
- Every time the user sees the report perform a query and sum the points the user got
- Add two fields to each user that records the number of points obtained so far (total and weekly). The weekly points value will be set to 0 every Monday at midnight.
The first option is easier, but I'm afraid that as I'll get many users and actions queries will take a long time.
The second option bares the risk of inconsistency between the table of actions and the summary values.
I'm very interested in what you think is the best alternative here.
Thanks,
Dorian
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
选择选项一。 稍后扩展
我的想法是采用更简单的方法，每次查询表，当您的网站上有太多用户导致服务器速度减慢时，您可以在那时进行切换。如果您的用户太多，导致服务器速度变慢，那么您可能还需要扩展应用程序的其他部分，但不要在早期阶段过度扩展应用程序。当需要扩展时进行扩展。
我的观点取自 37signals 的书“Getting Real”，第 4 章 - Scale Later
http://gettingreal.37signals.com/ch04_Scale_Later.php
Take option one. Scale later
My thought is to take the simpler approach of querying the table each time and when you have the problem of so many users on your site that it's slowing down your server, you can switch at that time. If you have so many users that your server is slowing down then probably you'll need to scale other parts of your application too, but don't go out of your way terribly at these early stages to scale your application. Scale when you need to scale.
My point of view is taken from the 37signals book "Getting Real", chapter 4 - Scale Later
http://gettingreal.37signals.com/ch04_Scale_Later.php
两种解决方案都同样有效。你自己说得对：第一个替代方案会更容易实现一些，但需要在用户每次进入分数页面时总结分数。另一方面，对于第二种选择，您只需要在用户表中添加和维护 2 个附加属性。如果您仔细编程，这几乎不会危及应用程序的完整性。
Both solutions will work equally well. You put it right yourself: The first alternative will be a little easier to implement, but will need to sum up scores every time a user enters the score page. On the other hand for the second alternative, you only need to add and maintain 2 additional attributes in the user table. This will hardly put the integrity of your application into jeopardy if you program carefully.