存储数据库记录计数是否多余? [英] Is storing counts of database record redundant?

问题描述

我正在使用Rails和MySQL，并且有一个基于行计数的效率问题.

I'm using Rails and MySQL, and have an efficiency question based on row counting.

我有一个Project模型，即has_many :donations.

我想计算一个项目的唯一捐助者的数量.

I want to count the number of unique donors for a project.

projects表中是否有一个名为num_donors的字段，并在创建新的供体时增加它的好主意?

Is having a field in the projects table called num_donors, and incrementing it when a new donor is created a good idea?

或者由于数据库优化，像@num_donors = Donor.count(:select => 'DISTINCT user_id')这样的事情在效率方面会变得相似还是相同?这会要求我为user_id和我要计数的其他任何字段创建索引吗?

Or is something like @num_donors = Donor.count(:select => 'DISTINCT user_id') going to be similar or the same in terms of efficiency thanks to database optimization? Will this require me to create indexes for user_id and any other fields I want to count?

对捐赠的总金额求和是否也使用相同的答案?

Does the same answer hold for summing the total amount donated?

推荐答案

回答标题问题.是的，这是多余的，但是是否应该这样做取决于您的情况.

To answer the title question. Yes it is redundant, but whether you should do it depends on your situation.

除非您知道性能问题，否则请在应用程序中即时计算计数和总计，而不要存储它们.也就是说，除非没有其他选择，否则不要存储计算所得的值.

Unless you have known performance problems, calculate the counts and totals on the fly in your application and don't store them. That is, don't store calculated values unless you have no other choice.

在大多数情况下，您将不必求助于此，并且应该这样做.

In most situations, you wont have to resort to this and shouldn't.

如果必须存储计算值，请执行以下操作:

• 不要通过增加它来保持最新.每次更新时，都会从所有数据中重新计算计数/总计.
• 如果您没有太多更新， 将代码放入更新触发器中 保持计数/总计为最新.
• 冗余中的麻烦 数据库是当数字 不同意，您不确定哪个是 权威性.添加到 记录文档，说明来源 数据是权威来源 他们不同意并且可以被覆盖.

• Don't keep it up-to date by incrementing it. Recalculate the count/total from all the data each time you update it.
• If you don't have a lot of updates, put the code in an update trigger to keep the count/totals up to date.
• The trouble with redundancy in databases is that when the numbers disagree, you are unsure of which is authoritative. Add to the documentation a note that the source data is the authoritative source if they disagree and can be overwritten.

这篇关于存储数据库记录计数是否多余?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！