mysql - 您好，我已经编写了以下 mysql 查询，但它占用了我太多的 cpu。我该如何优化它

Question

set @points := -1;
set @c := 1;
set @num := 0;
SELECT `player_id`,`name`,`firebase_token`,`highscore`,`fb_pic_url`,`profile_pic`
          , @num := if(@points = `highscore`, @num, @num + @c) as rank
          , @c := if(@points = `highscore`, @c+1, 1) as dummy
          , @points := `highscore` as dummy2
        FROM 
        (SELECT  `Match_History`.`player_id`,`players`.`name`,`players`.`firebase_token`,`players`.`profile_pic`,`players`.`fb_pic_url`,`Match_History`.`highscore`
            FROM `Match_History`,`players`
            WHERE (`Match_History`.`player_id`,`Match_History`.`highscore`) IN 
            ( 
              SELECT `player_id`, MAX(`highscore`)
              FROM `Match_History`
              WHERE `contest_id`=128
              GROUP BY `player_id`
            ) 
            AND `Match_History`.`player_id`=`players`.`id`
                GROUP BY `players`.`id`
            ORDER BY `Match_History`.`highscore` DESC ,`Match_History`.`timestamp`
        ) t 
        ORDER BY `highscore` desc, `player_id` asc;

我正在使用上面的 mysql 查询从下面提到的表中创建排行榜:

Match_History:

id int(11) NOT NULL,

player_id int(11) NOT NULL,

contest_id int(11) NOT NULL,

高分 int(11) NOT NULL,

时间戳 时间戳 NOT NULL DEFAULT CURRENT_TIMESTAMP

玩家:

id int(11) 不为空， name varchar(100) DEFAULT NULL,

profile_pic varchar(30) DEFAULT NULL,

fb_pic_url varchar(150) DEFAULT NULL,

firebase_token varchar(500) DEFAULT NULL,

时间戳 时间戳 NOT NULL DEFAULT CURRENT_TIMESTAMP

上面的查询占用了过多的 CPU，我想对其进行优化。我正在非常努力地尝试，但无法找到一种正确的方法来以更优化的方式获得相同的结果。有人可以帮我吗？

Answer 1

这是使用连接的第一次尝试:

SELECT  `Match_History`.`player_id`,`players`.`name`,`players`.`firebase_token`,`players`.`profile_pic`,`players`.`fb_pic_url`,`Match_History`.`highscore`
FROM `Match_History` MH 
JOIN `players` P on MH.player_id = P.id
JOIN  ( 
  SELECT `player_id`, MAX(`highscore`)
  FROM `Match_History`
  WHERE `contest_id`=128
  GROUP BY `player_id`
) HS on HS.player_id = MH.player_id and HS.highscore = MH.highscore
GROUP BY `players`.`id`
ORDER BY `Match_History`.`highscore` DESC ,`Match_History`.`timestamp`

确保它表现良好，然后在它表现良好时，添加剩余的逻辑。

在查询前添加EXPLAIN可以查看查询计划。

我要补充一点，如果它使用大量 CPU，那么很可能您缺少索引，并且时间花在了扫描内存页(表扫描)上。确保对所有连接列和 where 子句中的所有列进行适当的索引。