存储总计还是即时计算？ [英] Store totals or calculate on the fly?

问题描述

我的数据库中有很多表，其中包含用户可以喜欢，不喜欢，共享，喜欢的项目（照片，文章，视频）的信息。

I have many tables in my database that hold information on items (Photos, Articles, Videos) that can be Liked, Disliked, Shared, Favourited etc by users.

用户每次对某项操作时，都会将其记录在一个简单的表格中，例如：

Each time a user takes an action on a item, it is recorded in a simple table like such:

ItemID | UserID  | Liked | Shared | Favourited
1        1         NULL    1        NULL
2        25        1       1        1
3        18        0       NULL     NULL

当我编写查询以从表中返回项目列表（例如照片）时，我还想返回每个项目所拥有的赞，分享等总数。目前，我正在使用嵌套的SELECT语句即时计算此值。如果我的项目表的大小增加了数十万，并且我经常需要赞，分享等统计信息，那么继续动态计算统计数据是否安全还是应该将它们存储为总计在数据库中的某个地方？

When I'm writing a query to return a list of items from a table (e.g. Photos) I also want to return the total number of Likes, Shares etc each item has. At the moment I'm calculating this on the fly using a nested SELECT statement. If my item tables grow in size by hundreds of thousands, and I constantly need stats on Likes, Shares etc, then would it be safe to continue calculating stats on the fly or should they be stored as totals somewhere in the database?

推荐答案

我建议不要存储总计，因为这似乎是一个事务性数据库，您将非常频繁地插入行，因此，如果存储总计，每次插入一行时，您都需要更新总计。

I would suggest NOT to store totals, as this seems a transactional Database and you will be inserting rows quite frequently, So if you store totals, every time you insert a row , you will need to update your totals.

实际上，表中的每个插入都将跟随一条update语句，以保持总计更新。事务数据库的设计听起来很糟糕。

So in reality each insert in the table will be followed by an update statement just to keep totals updated. sounds a very poor design for a transactional database.

对于那些数据几乎不变的数据仓库，存储总计是一个不错的选择。

Storing totals is a good option for a data warehouse where data hardly changes , if ever.

我的建议是创建视图，它将为您即时计算总计。添加适当的索引以使这些查询高效。当数据变得太大时，即使表上的索引也做不到，请考虑使用索引视图。

My suggestion would be creating Views, that will calculate totals for you on the fly. Add appropriate indexes to make these queries efficient. When your data grows too big that even indexes on table arent doing enough , consider indexed views.

这篇关于存储总计还是即时计算？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！