为什么人们在新数据库的列中保存 X 的计数？
让我们假设有三个表 - 'users'、'products' 和 'faves'。在用户表中存储了所有具有诸如'id'，'username'，'email'之类的用户；在 'products' 中：'id', 'name', 'price'; in 'faves' : 'id', 'product_id' (产品 ID，与 'products' 相关), < code>'user_id'（添加fave的用户ID，与'users'相关），'modified_on'。它的虚拟示例只是为了说明我正在谈论的内容......（：
我经常看到“产品”表还有一列。它被称为“count_of_faves”或类似的东西。为什么需要它？我意思是，当有很多数据时，它是否与计算它们的速度有某种关系？
额外问题：
有更好的方法吗 ？ ，更自动，方式对'count_of_faves'执行+1和-1然后每次创建更新该行的新查询？
Lets imagine that there are three tables - 'users', 'products' and 'faves'. In user table are stored all users with something like 'id', 'username', 'email'; in 'products' : 'id', 'name', 'price'; in 'faves' : 'id', 'product_id' (ID of product, related with 'products'), 'user_id' (ID of user who added fave, related with 'users'), 'modified_on'. Its dummy example and made only to illustrate what I'm talking about... (:
I often see that there are one more column for 'products' table. Its called 'count_of_faves' or something like that. Why does its needed? I mean, its easily possible to count faves for specified post like on-the-fly, right? Is it somehow related to speed of counting them when there are very many data?
Extra question:
Is there any better, more automatic, way to do +1 and -1 to 'count_of_faves' then each time making new query that updates that row?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在大型系统上，您可以通过快速检索
count_of_faves来节省大量处理时间，而不必每次都重新计算某些行集上的聚合COUNT()需要它。例如，如果您要在每个页面上显示count_of_faves，您要么需要每次重新计算它，要么将其缓存在某个地方。您可以选择将其缓存在应用程序代码中（例如会话中），也可以将其缓存在数据库中。将其缓存在数据库中有一些好处 - 在应用程序代码之外创建数据报告时，可以随时使用这些值。
该操作的
执行成本远低于始终重新计算聚合
COUNT()。On a large-scaled system, you can save a lot of processing time by quickly retrieving the
count_of_favesrather than having to recalculate the aggregateCOUNT()on some rowset every time you need it. If you were, for example, to display thecount_of_faveson each page, you would either need to recalculate it each time, or cache it somewhere. You can choose to cache it in your application code, in the session for example, or you can cache it in the database.Caching it in the database has a few benefits - when creating reports of your data outside your application code, the values are readily available for use.
The operation:
is far less expensive to perform than it is to be recalculating the aggregate
COUNT()all the time.这是一项优化，旨在减少可能存在大量关系时执行的 COUNT 数量。举个例子，如果“最爱”的数量很小，那么好处是有限的，并且可能是负的（因为每次创建或销毁“最爱”时都必须更新计数）。
但是，如果计数的读取次数远多于因添加/删除关系而发生的变化，那么存储该值就成为一种有效的缓存策略。
It's an optimisation made to reduce the number of COUNTs executed where there may be a large number of relations. To take your example if the number of 'faves' is small the benefit is limited and possibly negative (as the count has to be updated every time a 'fave' is created or destroyed).
But if the count is read far more often than it changes by relations being added / removed then storing the value becomes an effective caching strategy.