mysql - 带有加权分数的 Sql 流行度算法

Question

我正在实现一种算法，根据他的好恶返回当前的热门帖子。

为此，对于每个帖子，我都会添加他所有的喜欢 (1) 和不喜欢 (-1) 以获得他的分数，但每个喜欢/不喜欢都是加权的:最新的，最重的。例如，此时用户喜欢一个帖子，他喜欢的权重为 1。1 天后，它的权重为 0.95(如果不喜欢，则为 -0.95)，2 天后为 0.90，依此类推... 21 天后达到 0.01。 (PS:这些都是近似值)

我的表格是这样制作的:

帖子表

id | Title                 | user_id | ...
-------------------------------------------
1  | Random post           | 10      | ...
2  | Another post          | 36      | ...
n  | ...                   | n       | ...

点赞表

id | vote | post_id | user_id | created
----------------------------------------
1  | 1    | 2       | 10      | 2014-08-18 15:34:20
2  | -1   | 1       | 24      | 2014-08-15 18:54:12
3  | 1    | 2       | 54      | 2014-08-17 21:12:48 

这是我目前正在使用的 SQL 查询完成这项工作

SELECT Post.*, Like.*, 
SUM(Like.vote * 
    (1 - IF((TIMESTAMPDIFF(MINUTE, Like.created, NOW()) / 60 / 24) / 21 > 0.99, 0.99, (TIMESTAMPDIFF(MINUTE, Like.created, NOW()) / 60 / 24) / 21))
   ) AS score 
FROM posts Post 
LEFT JOIN likes Like ON (Post.id = Like.post_id) 
GROUP BY Post.id
ORDER BY score DESC

PS:我将 TIMESTAMPDIFF 与 MINUTE 一起使用，而不是直接使用 DAY 因为我自己计算日期，否则它会返回一个积分器，我想要一个浮点值，以便随着时间的推移逐渐衰减，而不是每天衰减。所以 TIMESTAMPDIFF(MINUTE, Like.created, NOW())/60/24 只给出了自从类似创建以来经过的天数，带小数部分。

这是我的问题:

1. 查看 IF(expr1, expr2, expr3) 部分:为了设置类似权重的最小值，它是必要的，因此它不会低于 0.01 并变为负数(等等之类的，年纪大了还是有点胖)。但我计算的是同一件事的 2 倍:expr1 与 expr2 相同。有没有办法避免这种重复的表达？
2. 我打算缓存此查询并每 5 分钟更新一次，因为我认为它在大型 Post 和 Like 表上会非常繁重。缓存真的有必要吗？我的目标是在一个包含 50 000 个条目的表上运行此查询，并且每 200 个相关的点赞(这构成了一个包含 10 000 000 个条目的 Like 表)。
3. 我应该在 Like 表中为 post_id 创建索引吗？而对于创造？

谢谢!

编辑:想象一个帖子可以有多个标签，每个标签可以属于多个帖子。如果我想获得给定标签或多个标签的热门帖子，我无法缓存每个查询；因为有很多可能的查询。查询仍然可行吗？

编辑最终解决方案:我终于做了一些测试。我创建了一个包含 30 000 个条目的 Post 表和一个包含 250 000 个条目的 Like 表。没有索引，查询非常长(超时 > 1000 万)，但是在 Post.id(主)、Like.id(主)和 Like.post_id 上有索引需要大约 0.5 秒。

所以我没有缓存数据，也没有使用每 5 百万次更新一次。如果表格不断增长，这仍然是可能的解决方案(超过 1 秒是 Not Acceptable )。

Answer 1

2: I was going to cache this query and update it every 5 minutes, as I think it will be pretty heavy on a big Post and Like table. Is the cache really necessary or not ? I'm aiming to run this query on a table with 50 000 entries, and for each 200 associated likes (that makes a 10 000 000 entries Like table).

10000 和 50000 在当前硬件上被认为很小。使用这些表大小，您可能不需要任何缓存，除非查询每秒运行几次。无论如何，在决定使用缓存之前，我会进行性能测试。

3: Should I create Index in Like table for post_id ? And for created ?

我会为 (post_id, created, vote) 创建一个索引。这样查询就可以从索引中获取所有信息，而根本不需要读取表。

编辑(回复评论):

额外的索引会稍微减慢插入/更新的速度。最后，您选择的路径将决定您在 CPU/RAM/磁盘 I/O 方面所需的特性。如果您有足够的 RAM 用于 DB，以便您希望将整个 Like 表缓存在 RAM 中，那么您最好只在 post_id 上建立索引。

就总负载而言，您需要考虑 insert 和 select 之间的比率，以及使用或不使用索引进行插入和选择的相对成本。我的直觉是总负载会随着索引的增加而降低。

关于并发问题(同时选择和插入)。发生什么取决于隔离级别。一般建议是使插入/更新尽可能短。如果您在 insert 开始和 commit 之间没有做不必要的事情，您应该没问题。