如何优化收藏夹中新帖子的数量

Question

我想说，我很高兴收到任何回复。我将尝试构建我的文本以便更好地理解。

情况 我经营主题互联网论坛，您可以在菜单中将主题添加为您的最爱，菜单会显示这些主题中的新帖子数量。因此，每次您重新加载页面（转到整个网站的任何位置）时，都会检查您收藏夹中所有主题的新帖子。

问题 这在数据库上当然是相当昂贵的，因为通常有 20-50 个收藏夹，我必须检查数据库是否在这些主题中添加了任何帖子。平均主题有 1000-2000 个帖子。每个用户的每次综合浏览量都会发生这种情况，即每月大约 900,000 次综合浏览量。

可能的解决方案1 我存储每个主题中的帖子总数，并为每个用户存储每个主题的最后查看的帖子数。这可能是最快的，但它有很多缺点，这些缺点是功能性的（删除、过滤帖子等）。

可能的解决方案2 我为每个用户存储每个主题的上次查看帖子的 ID。这是非常好的解决方案，但比前一个方案慢大约十倍。

数据库 我将所有主题的所有帖子存储在一张巨大的表中=数十万个帖子。

问题 我想消除解决方案 1 带来的问题，但我需要保持速度。我想过为每个主题创建一个表并使用解决方案 2，但我不知道它是否有帮助。因此，如果您有任何经验，请告诉我最快的解决方案是什么。

非常感谢。

Answer 1

首先：不知道您的架构或数据库系统，但这应该相对简单，假设您保留了用户上次看到的时间的记录（下面示例中的 $DATE_USER_WAS_LAST_SEEN ），并且您的每个帖子可能都与其主题相关联类型的 id，并且您有所有 $FAVOURITE id 的列表。

SELECT topic_id, count(*) AS count FROM posts 
WHERE topic_id IN ($FAVOURITES) 
    AND created_date > $DATE_USER_WAS_LAST_SEEN 
GROUP BY topic_id

将为您提供如下输出：

topic_id   |   count
---------------------
  3        |     20
  1        |     27
  33       |     120

对于这种规模来说，这应该是可接受的速度，您可以通过不使用 IN 并制作长 (topic_id = 1 OR topic_id = 2或者 topic_id = etc) 字符串（如果您的数据库没有自动优化这些内容）。

其次：不要太担心如何保持这些值最新。人们会将它们用作有新消息的指示器，而不是根据它们做出生活决策，因此缓存每个用户的这些请求（可以在用户自己的记录上，也可以使用某种内存缓存，例如 memcache，如果您熟悉这些） ）并每隔 5 分钟左右使缓存过期，这将从根本上减少对数据库的命中

Answer 2

我想你的帖子 ID 是连续的并且总是递增的。

为您最喜欢的表创建一个至少包含以下字段的表：user_id、topic_id、last_post_id

然后您可以使用这个简单的查询检查新帖子：

select topics.id, count(posts.id)
from users
inner join favorites on favorites.user_id = users.id
inner join topics on topics.id = favorites.topic_id
inner join posts on 
    posts.topic_id = topics.id and
    posts.id > last_post_id
where users.id = $id
group by topics.id

这应该运行得相当顺利。

您还必须在用户每次访问主题时更新last_post_id，但这应该非常简单。

Answer 3

如果您在巨大的 all_posts 表上有一个索引（topic_id，post_id），那么执行此查询的成本应该不会太高：

select topic_id, count(*)
from all_posts a
inner join user_favorites u on u.topic_id = a.topic_id
where a.post_id > u.post_id and u.user_id = @user_id
group by topic_id