在数据库中存储主动更新的字段
我有一个数据库，其中一个表存储文章。对于每一篇文章，我都需要存储其被查看的次数。现在，我的想法是将此值存储在数据库中，并在每次有人访问包含该文章的页面时更新它，我现在正在考虑两种替代方案：
- 在表
articles 中创建一个列views并更新它。 - 创建一个单独的表
view_count，其中包含指向文章的 FK 指针以及该文章的浏览次数。
我的问题是：这两种方法在速度方面有什么区别吗？为什么？还有更好的选择吗？
我使用的数据库是 PostgreSQL。
注意，我没有考虑在数据库外部的单独文件中累积这些值并稍后插入的不同方式，因为 i) 我们没有获得尽可能多的点击 ii) 次要功能，如果它会需要这样的麻烦，我们宁愿放弃它并使用其他一些服务。
I have a database with one of the table storing articles. For every article I need to store the number of times it was viewed. Now, my idea is to store this value in the database and update it every time someone visits the page with that article and I am considering two alternatives now:
- Create a column
viewsin the tablearticlesand update it. - Create a separate table
view_countwith FK pointer to the article and the number views for that article.
My question is: is there any difference between these two approaches in terms of speed and why? Are there any better alternatives?
The database I use is PostgreSQL.
Note, that I am not considering the different ways of accumulating those values in separate files, outside of database and later inserting because i) we do not get as many hits ii) the feature in minor and if it would require such a hassle we would rather abandon it and use some other service for that.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
两个提案的差异非常微妙，我是这样看待它们的：
同一个表
单独的表
，您还可以节省空间，即使加入可能会增加所需的 I/O，但这可能会带来更好的性能注释：
该评论适用，但很微妙：记录大小可能不会受到显着影响以影响所有查询。使用单独的表减少 I/O 的好处可能无法察觉。
The differences of two proposals are quite subtle, here's how I see them:
Same table
nx more I/O depending on the record size/block size, if the record size is much less then the block size then the effect for the index lookups depends on the type of query/order of the data on the disk - if choosing records from the same block you will feel the performance hit, if choosing sparse data you will not feel it)Separate tables
Notes:
The comment apply, but are subtle: record size will probably not get significantly affected to influence all queries. The benefits of less I/O with separate table might not be perceivable.
如果
articles表获得大量写入，则由于写入时的表锁定，更新列可能会慢一些。使用单独的表只是读取速度慢一点/重一点，但就更新而言是最轻的。但是在大量写入之后，您可以轻松地重建表，而不会造成太大的性能损失，因为它将是一个相当轻的表。
如果您的数据库没有高负载，那么我会采取添加列的简单选择。
If the
articlestable gets a lot of writes than updating the column might be a bit slower because of the table locks while writing.Using a separate table is only a little bit slower/heavier to read but is lightest in terms of updates. But after loads of writes you can easily rebuild the table without much performance penalty since it will be quite a light table.
If your database doesn't have a high load than I would take the easy option of adding a column though.
我还有另一种方法，即在当天晚些时候或当网站流量太少时进行更新。
我会以这种方式完成这项工作，当任何用户阅读文章时，用户 ID 将存储在一个新表中，稍后通过脚本我可以更新用户阅读该文章的次数。
I have one more approach, where I do the update later in the day or when the traffic to the site is too less.
I would do the job in this way, when ever any user reads the articles, there user id will be stored in a new table, and later through script I can update How many times the article have been read by the users.