在数据库表中存储视图计数 [英] Storing view counts in database table

问题描述

每次访问数据库记录时，存储观看计数的恰当和最有效的方法是什么？

code> ITEMS 包含以下字段： id，item_name

项目拥有自己的固定链接： http://domain.com/item_name

我想能够使用这些数据，并在页面上显示 Views：2,938 。哪种方法最好？

---

方法A $ b

在 ITEMS 表中创建一个附加字段 view_count ，并更新它以增加视图计数： / p>

  view_count = view_count + 1; 
  

方法B

创建一个包含以下字段的新表格 VIEWS ：

  id，item_id，timestamp，ip 
  

并在 VIEWS

$ b b

还有其他方法吗？

---

我已经看过方法A用于各种PHP论坛软件，但是我的大学教师告诉我方法B更好，因为 add（）需要比 update（）更少的压力 / p>

解决方案

事实上，INSERT操作通常比UPDATE语句消耗更少的资源;但是一个精心调整的UPDATE语句不一定是一个紧张的。

还有其他的考虑，如果唯一已知和预见的要求是一个总页面计数，那么单个行将比每个视图事件存储单独的行消耗少得多的存储，并且检索视图计数的查询将更有效率。

（在现实世界中的应变在存储中，而不仅仅是磁盘空间，而是磁带数量和备份所需的时钟时间量，恢复所需的时间等）。

如果您需要能够按小时或按天报告观看次数，那么有更详细的数据将为您提供这样做的能力，您无法从中获得

如果我需要一个摘要计数页面，我不想要对历史事件记录中的一大堆行进行查询，只是在下次查看页面时想出一个新的值2,939。对于数据库资源来说，从单个行中检索单个列值，而不是每次查看页面时，都会转换成千行的事件数据，将其聚合为一个计数，这将是一个紧张 。

如果我还需要能够按国家或按IP，按小时，按天或周或月份行政地报告观看次数，我还会存储个别事件行，以使其可用于切片和骰子分析。

What is the appropriate and most efficient way to store a view count each time a database record is access?

I have table ITEMS containing the following fields: id, item_name

Each item has its own permalink: http://domain.com/item_name

I would like to be able to use this data and display a Views: 2,938 on the page. Which method is best?

---

Method A

Create an additional field view_count in the ITEMS table and update it to increment the view count:

view_count = view_count + 1;

Method B

Create a new table VIEWS containing the following fields:

id, item_id, timestamp, ip

And add a new record to the VIEWS table each time a page is viewed.

Method C

Is there another method altogether?

---

I have seen Method A used in a variety of PHP forum software, however my University instructors have informed me that Method B is better because add() requires less strain than update()

解决方案

It's somewhat true that an INSERT operation will often consume less resources than an UPDATE statement; but a well tuned UPDATE statement isn't necessarily a strain".

There are other considerations. If the only known and foreseen requirement is for a "total" page count, then a single row will consume much less storage than storing individual rows for each "view" event. Also, the queries to retrieve the view count will be much more efficient.

(Where the "strain" is in the real world is in storage, not in terms of just disk space, but the number of tapes and the amount of clock time required for backups, time required for restore, etc.)

If you need to be able to report on views by hour, or by day, then having the more granular data will provide you the ability to do that, which you can't get from just a total.

You could actually do both.

If I needed a "summary" count to put the page, I wouldn't want to have to run a query against a boatload of rows in the historical event record, just to come up with a new value of 2,939 the next time the page is viewed. It would be much less of a "strain" on database resources to retrieve a single column value from a single row, than it would be to churn through thousands of rows of event data to aggregate it into a count, every time a page is viewed.

If I also needed to be able to administratively report "views" by country, or by IP, by hour, by day or week or month, I would also store the individual event rows, to have them available for slice and dice analytics.

这篇关于在数据库表中存储视图计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！