如果这个问题太具体，我提前道歉，但我认为这是一个相当典型的场景:join 和 group by 使数据库和解决它的最佳方法。我的具体问题是我需要根据以下内容创建记分牌:

• 播放 (userid,gameid,score) 4000 万行
• 游戏 (gameid) 10 万行
• app_games (appid,gameid) 即，游戏被分组到应用程序中，应用程序的总分是所有相关游戏的总分 <20>

用户可以玩多次，每次游戏的最佳成绩都会被记录下来。制定查询很容易，我做了几个变体，但它们有一种令人讨厌的倾向，即在负载下锁定在“复制临时表”中 30-60 秒。

我能做什么？是否有我应该调整的服务器变量，或者有没有办法重新制定查询以使其更快？我正在使用的查询的派生版本如下(减去用户表连接以获取名称):

    select userID,sum(score) as cumscore from  
        (select userID, gameID,max(p.score) as score 
        from play p join app_game ag using (gameID)  
        where ag.appID = 1 and p.score>0
        group by userID,gameID ) app_stats 
    group by userid order by cumscore desc limit 0,20;

或者作为临时表:

    drop table if exists app_stats;
    create temporary table app_stats 
        select userID,gameID,max(p.score) as score 
        from play p join app_game ag using (gameID)  
        where ag.appID = 1 and p.score>0
        group by userid,gameID;
    select userID,sum(score) as cumscore from app_stats group by userid 
        order by cumscore desc limit 0,20;

我有如下索引:

show indexes from play;
+-------+------------+----------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name             | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| play  |          0 | PRIMARY              |            1 | playID           | A         |    38353712 |     NULL | NULL   |      | BTREE      |         |
| play  |          0 | uk_play_uniqueID     |            1 | uniqueID         | A         |    38353712 |     NULL | NULL   | YES  | BTREE      |         |
| play  |          1 | play_score_added     |            1 | dateTimeFinished | A         |    19176856 |     NULL | NULL   | YES  | BTREE      |         |
| play  |          1 | play_score_added     |            2 | score            | A         |    19176856 |     NULL | NULL   |      | BTREE      |         |
| play  |          1 | fk_playData_game     |            1 | gameID           | A         |       76098 |     NULL | NULL   |      | BTREE      |         |
| play  |          1 | user_hiscore         |            1 | userID           | A         |      650062 |     NULL | NULL   | YES  | BTREE      |         |
| play  |          1 | user_hiscore         |            2 | score            | A         |     2397107 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+

最佳答案

我怀疑当您创建临时表时，这两个查询基本上都需要遍历您表中的所有数据(同样在您的一次性查询中)。如果您有大量数据，那只需要一点时间。

我会维护一个单独的表，其中包含每个玩家的 ID 和总分。每当您更新播放表时，也会更新汇总表。如果它们不同步，只需停止汇总表并从播放表重新创建数据。 (或者，如果您已经在您的基础设施中使用了 Redis，您可以在那里维护摘要——它具有使这个特定的事情变得非常快的功能)。

关于mysql - 临时表拖垮数据库——如何优化？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/10837504/