如何在网络应用程序中保存过去一周的访问记录？

Question

我正在开发一个像报纸一样的MVC网络应用程序，它有一个文章内容类型，有一个访问领域。数据库是MariaDB。

每次访问者请求一篇文章时，文章行的“访问”字段都会增加1，所以我可以轻松地通过他们的总体访问来查询文章。

但我想要的是根据过去一周每篇文章的访问次数来整理文章。

我想有一个单独的表，比如weekly-stats，在这个表中，我可以在每次请求时插入和增加他们最近访问的文章信息，但是接下来我需要每周作为一个cron作业清空该表，这并不理想，在数据被清除之后，它会使stats保持为空。

我还想让文章的访问保存在像redis这样的过期缓存中，并将缓存的记录设置为在一周后过期，但随后它带来了插入和查询redis散列元素的所有复杂性。

所以我想可能有一些更直截了当的设计策略，我错过了。我怎样才能用最少的麻烦实现这一点呢？

Answer 1

每次访问者请求一篇文章时，文章行的“访问”字段都会增加1，所以我可以轻松地通过他们的总体访问来查询文章。...，但我想要的是根据过去一周每篇文章的访问次数来整理文章。

你可以那样做。不过，我会记录下来访者的身份，除非你有太多的交通流量来做这个。

CREATE TABLE article_visits (
  article_id  int REFERENCES article,
  user_id     int REFERENCES users,
  ts_visit    timestamp with time zone DEFAULT now()
);
CREATE INDEX ON article_visits ( article_id, ts_visit );

SELECT article_id, count(*)
FROM article AS a
LEFT OUTER JOIN article_visits USING (article_id)
WHERE ts_visit > now() - interval '1 week' -- PostgreSQL has intervals.
GROUP BY article_id;

我会用PostgreSQL。在MySQL上，您将不得不使用他们愚蠢的专有DATE_SUB()和WEEKOFYEAR()。谁知道他们在想什么。他们讨厌标准。

Answer 2

weekly_stats表每周可以有一条记录，例如：

CREATE TABLE weekly_stats (
  week_start date PRIMARY_KEY, -- e.g. '2018-03-05'
  article_id int unsigned,
  view_count int unsigned DEFAULT 0,
  CONSTRAINT week_stats_fk1 FOREIGN KEY (article_id) 
    REFERENCES `article` (`article_id`) ON DELETE CASCADE ON UPDATE CASCADE
);

然后，您的应用程序将在每次查看文章时更新weekly_stats.view_count。如果该周的记录不存在，应用程序将不得不创建它。

如果要删除旧数据(这可能不是必需的:而不是cron作业)，则可以使用MariaDB事件。将其设置为只删除超过某些限制的记录，例如365天。

Answer 3

你几乎肯定不想删除这些数据，你可能想把它保存下来，以便与下周或明年进行比较，或者用于查看这个月的访问情况，而不是本周的数据。

我认为日志表将更符合您的要求:每请求一行，包含项目ID和时间戳。如果您的用户是经过身份验证的，那么也有一个用户ID。然后，您可以通过简单的聚合查询来计算这个日历周、滚动周、上个星期、这个月、两个月的访问次数等等。如果由于访问次数很大，每次读取的效率都很低，尽管索引选择很好，那么请考虑缓存结果和/或在单独的报告数据库中而不是在活动数据库中处理结果的复杂性。

一旦数据变大，您可能会考虑将旧数据存档，但这可能是很长的将来。假设每一行都有一个32位整数项目ID，时间戳有一个8字节的日期时间值，主键有一个大的(8字节整数)值，以及项目ID的索引，那么一百万视图将接受32M字节的空间顺序。即使使用128位UUID值，而不是对每百万日志记录请求使用72兆字节的整数。

为了考虑将日志数据保存更长时间的查询效率，我做了一个快速测试，用随机数据填充100篇文章的表，这些文章每天读取1000条数据(因此每天总共记录10,000个请求)。安排表格，包括一个月的数据和一年值得运行的数据，如下所示：

SELECT COUNT(*) FROM onemonth WHERE articleID = 42 AND timeOfReq BETWEEN '2018-02-28' AND '2018-03-08'
SELECT COUNT(*) FROM oneyear  WHERE articleID = 42 AND timeOfReq BETWEEN '2018-02-28' AND '2018-03-08'

结果分别访问了1919和1920页面。数据大约大12倍，但查询速度只慢了0.06%。同样的情况也出现在

SELECT COUNT(*) FROM onemonth WHERE timeOfReq BETWEEN '2018-02-28' AND '2018-03-08'
SELECT COUNT(*) FROM oneyear  WHERE timeOfReq BETWEEN '2018-02-28' AND '2018-03-08'

也可以使用更复杂的查询，包括分组、聚合和连接到其他地方。结果表(包括索引)的大小分别为220 tables和2.5GByte。这是在MS中实现的(使用来自SET STATISICS IO ON的输出来衡量每个查询所做的工作)，但是您将看到与其他数据库类似的结果。我没有使用任何技巧，如行/页压缩，以减少数据大小，尽管我怀疑它们对这些数据非常有效。