php - 如何优化具有不同具体分数和排名的分数/排名表？

Question

在我们的项目中，我们有一个用户表，其中存储了具有名称和不同类型分数(总分数、任务分数等)的用户数据。这些值的计算方式并不重要，但请将它们分开。

让我们看看下面的表“用户”

  id   name   score_overall   score_trade   score_quest    

  1    one    40000           10000         20000
  2    two    20000           15000         0
  3    three  30000           1000          50000
  4    four   80000           60000         3000

为了显示分数，有一个虚拟表和一个用于每种分数的表，其中用户名与分数和排名一起存储。所有表看起来都一样，但名称不同。

id  name  score  rank  

它们是分开的，以便用户搜索和过滤表格。假设有一行玩家“playerX”的排名为 60。因此，如果我过滤“playerX”的分数，我只会看到这一行，但排名为 60。这意味着排名是“硬存储”的，不仅如此通过行号或类似的东西动态显示。

不同的分数表通过 cronjob 填充(并使用附加的虚拟表)，其执行以下操作:

1. 将用户数据复制到虚拟表
2. 按按分数排序更改虚拟表
3. 将虚拟表复制到特定分数表，以便 AI 主键(排名)自动填充正确的值，代表每个用户的排名。

这意味着:有 5 个具体分数的同时，还有 5 个分数表和虚拟表，总共 6 个。

如何优化？

我想做的是优化整个过程并删除重复的表(并尽可能避免虚拟表)以将所有分数数据存储在一个具有以下列的表中:

userid, overall_score, overall_rank, trade_score, trade_rank, quest_score, quest_rank

我现在的问题是如何以最好的方式做到这一点，是否还有如上所示的另一种方法(具有所有不同的表格)？欢迎使用 MYSQL 语句和/或 php 代码。前一段时间，我尝试使用行号，但这不起作用 a) 因为它们不能在插入语句中使用 b) 因为在过滤每个玩家时(如上例中的“playerX”)将处于排名 1 作为这是唯一返回的行。

Answer 1

那么，您可以尝试使用以下配置创建一个表:

id | name | score_overall | score_trade | score_quest | overall_rank | trade_rank | quest_rank

如果这样做，您可以使用以下查询来填充表:

SET @overall_rank:=-(SELECT COUNT(id) FROM users);
SET @trade_rank:=@overall_rank;
SET @quest_rank:=@overall_rank;

SELECT *
FROM users u
INNER JOIN (SELECT id,
                   @overall_rank:=@overall_rank+1 AS overall_rank
            FROM users
            ORDER BY score_overall DESC) ovr
ON u.id = ovr.id
INNER JOIN (SELECT id,
                   @trade_rank:=@trade_rank+1 AS trade_rank
            FROM users
            ORDER BY score_trade DESC) tr
ON u.id = tr.id
INNER JOIN (SELECT id,
                   @quest_rank:=@quest_rank+1 AS quest_rank
            FROM users
            ORDER BY score_quest DESC) qr
ON u.id = qr.id
ORDER BY u.id ASC

我准备了一个SQL-fiddle为你。

尽管我认为如果您开始获得大量记录，性能就会受到影响。

一点解释:@*_rank 是 SQL 变量。每增加一行，它们就会增加 1。