Mysql - 有效地获取给定ID的每个组的最大值

Question

我们有一个表格，它记录了用户在某些游戏中的得分。我们想要的是显示每个用户每场比赛的最高得分。请注意，每个用户可能已经提交了 1000+ 分数，社区很大(因此用户越来越多)并且有很多游戏。我们已经在此处的类似问题中使用了此查询:

SELECT f1.userkey, f1.score, dateachieved, timeachieved, `MA_users`.username
FROM (
      (
        SELECT userkey, max( score ) AS score
        FROM `MA_scores`
        WHERE gameid = '$gameid'
        AND STATUS = 'approved'
        GROUP BY userkey
      ) AS f1
      JOIN (
        SELECT userkey, score, dateachieved AS dateachieved, timeachieved AS timeachieved
        FROM `MA_scores`
        WHERE gameid = '$gameid'
        AND STATUS = 'approved'
        GROUP BY score DESC , userkey
        ORDER BY `score` DESC
      ) AS T2 
      ON f1.userkey = T2.userkey
      AND f1.score = T2.score
)
JOIN `MA_users` ON f1.userkey = `MA_users`.userkey
LIMIT 0 , 20;

上面的问题是它需要一些时间才能完成，因此站点不能快速访问(延迟几秒钟)(因此我们看到 50% 的 cpu 使用来自 mysql)。我们知道这是已知的最大 n-per-group 问题，并在此处阅读了一些问题和一些博客文章，但它们似乎并没有充分提高查询速度。

所以问题是:是否有更高效的查询来检索所需的结果？此外，查询应使用哪些索引以加快查询速度？ (为了避免使用文件排序；也使用临时文件等)谢谢!

Answer 1

与其每次都查询它，不如拥有一个所谓的 Statistics 表，其中有两列:userkey 和 max_score .您可以很容易地回填这张表；诀窍是使它保持最新。

您可以在插入新行时在 MA_scores 表上放置一个触发器。触发器只需从表中为该 userkey 选择 MAX(score) 并更新 Statistics 表。

我不知道它如何适合您现有的架构，或者它是否是一个很棒的主意，但它会具有极快的读取时间，并且在 MA_scores 表上有适当的索引, 总体上可能相当快。