mysql 表设计用于每日轮换顶级评分

Question

我将热门浏览量和“喜欢”存储在名为“计数”的表中。我每晚运行一次这个查询

UPDATE `counts` SET rank=d7+d6+d5+d4+d3+d2+d1,d7=d6,d6=d5,d5=d4,d4=d3,d3=d2,d2=d1,d1=0

一周中的每一天都有一个 d1-d7 变量，我们每晚将其“向下”移动一位并重新计算总和。

随着我的网站不断发展，此查询现在大约需要 20 分钟。

我正在寻找有关如何更有效地组织它的建议，因为它似乎可能是一种常见模式。

Answer 1

正如评论所说，我们需要查看架构。但无论如何我都会提出一个建议。没有 7 个不同的字段 d1-d7。如果以后您决定将分数保留一年以上怎么办？哎哟。

我假设 counts 将 view_id 作为其 PK。然后有另一个表ranks，其中包含view_id列(设置为counts中的FK)，rank(概括d1-d7) ，无论它们是什么数据类型)和 rank_date，这是一个日期。现在每天晚上你都有

UPDATE counts SET rank = (SELECT SUM(rank) FROM ranks r WHERE r.view_id=counts.view_id
                        AND r.rank_date>=DATE_SUB(CURDATE(), INTERVAL 1 WEEK) );

[某些 RDBMS 允许在 UPDATE 查询中使用 JOIN 类型语法。我相信 MySQL 能够理解类似于以下内容的内容，但它不是我常用的 RDBMS

UPDATE counts, (SELECT view_id, SUM(rank) AS srank FROM ranks r 
                WHERE  r.rank_date>=DATE_SUB(CURDATE(), INTERVAL 1 WEEK) 
                GROUP BY r.view_id) AS q1 
SET rank = srank
WHERE counts.view_id=q1.view_id;   

]

如果是这样，它可能会比第一个版本运行得更快。

同时，为了进行清理，您可以选择从排名中删除超过 1 周的行，但在更灵活的架构中，您不必这样做。