php - 如何优化 MySQL 游戏排行榜 - 大量子查询问题

Question

我的游戏服务器上有一个巨大的瓶颈，用于存储当前排行榜的以下查询。

我目前仅每 5 分钟通过 cron 调用此查询一次，但我希望对其进行优化，以便每分钟或在需要时调用一次。

查询耗时 30 秒，目前只有约 2000 名用户和 7000 场比赛(存储在 Games 和 TopPlayerScores 中)。恐怕只会越来越糟!!请帮助我 Overflow-Kenobi!你是我唯一的希望!

SET @rank=0;
INSERT INTO Board (TopScorePKID, GamePKID, UserPKID, UniquePlayerID, PlayerName, TopPlayerScore, Position, Date)
(SELECT bad.ID AS TopScorePKID, bad.GamePKID, bad.UserPKID, bad.UniquePlayerID, bad.PlayerName, bad.TopPlayerScore, @rank:=@rank+1 AS Position, bad.Date
FROM (
    SELECT g.GamePKID, g.TopPlayerScore, l.ID,  l.UserPKID, u.UniquePlayerID, u.PlayerName, (l.Date) AS Date
    FROM Games g, TopPlayerScores l, UserDetails u
    WHERE l.GamePKID = g.GamePKID
    AND u.UserPKID = l.UserPKID
    AND u.SECRET_DETAIL = 0 
    AND g.TopPlayerScore >= (SELECT DISTINCT k.TopPlayerScore AS Highest 
        FROM Games k, TopPlayerScores t 
        WHERE t.UserPKID = l.UserPKID
        AND k.GamePKID = t.GamePKID
        ORDER BY k.TopPlayerScore DESC
        LIMIT 1) 
    GROUP BY l.UserPKID
    ORDER BY g.TopPlayerScore DESC, Date ASC) 
AS bad);

请大家帮忙!!我应该把它分解成观点吗？或者使用 Inner Join 关键字？什么是最好的方法？

非常感谢你看到这个烂摊子 :D!

更新 1.0 :解释扩展结果:

id  select_type table   type    possible_keys   key key_len ref rows    filtered    Extra1   PRIMARY   ALL NULL    NULL    NULL    NULL    1521    100.00  2   DERIVED l   ALL NULL    NULL    NULL    NULL    6923    100.00  Using temporary; Using filesort2   DERIVED u   eq_ref  PRIMARY PRIMARY 4   DBNAME.l.UserPKID   1   100.00  Using where2   DERIVED k   eq_ref  PRIMARY PRIMARY 4   DBNAME.l.GamePKID   1   100.00  Using where3   DEPENDENT SUBQUERY  t   ALL NULL    NULL    NULL    NULL    6923    100.00  Using where; Using temporary; Using filesort3   DEPENDENT SUBQUERY  g   eq_ref  PRIMARY PRIMARY 4   DBNAME.t.GamePKID   1   100.00  Using where

UPDATED 2.0:Limited Schema for querying tables

Using the Games to store game scores and other information about a game

`Games` (
  `GamePKID` int(11) NOT NULL AUTO_INCREMENT,
  `TopPlayerScore` int(11) NOT NULL,
  `OTHER_MISC_STUFF_REMOVED` int(11) NOT NULL
  PRIMARY KEY (`GamePKID`)
)

使用以下内容将用户链接到游戏并存储时间/日期

`TopPlayerScores` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `UserPKID` int(11) NOT NULL,
  `GamePKID` int(11) NOT NULL,
  `Date` datetime NOT NULL,
  PRIMARY KEY (`ID`)
)

用于存储每个唯一的播放器

`UserDetails` (
  `UserPKID` int(11) NOT NULL AUTO_INCREMENT,
  `UniquePlayerID` char(40) NOT NULL,
  `PlayerName` char(96) NOT NULL,
  `SECRET_DETAIL` tinyint(1) NOT NULL DEFAULT '0',
  `isPlayer` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`UserPKID`)
)

Answer 1

我要注意的第一件事是，尽管这不会提高性能，但您使用的 JOIN 语法在 20 多年前已被 ANSI 92 expcict join 语法取代，这当然完全是主题，但是 Aaron Bertrand explains切换到新语法的一些很好的理由。

要注意的第二件事是您的结果将是不确定的。您正在选择不包含在聚合或分组依据中的列。虽然 MySQL 允许这样做，但您并未按照 MySQL 的预期使用该功能。 MySQL docs状态:

MySQL extends the use of GROUP BY so that the select list can refer tononaggregated columns not named in the GROUP BY clause. This meansthat the preceding query is legal in MySQL. You can use this featureto get better performance by avoiding unnecessary column sorting andgrouping. However, this is useful primarily when all values in eachnonaggregated column not named in the GROUP BY are the same for eachgroup. The server is free to choose any value from each group, sounless they are the same, the values chosen are indeterminate.

但是，您包含的某些列(g.GamePKID、g.TopPlayerScore、l.ID、l .Date)，不满足条件每个组都相同，因此，如前所述，MySQL 可以自由选择它喜欢的任何值，即使您有 ORDER BY g.TopPlayerScore DESC, Date ASC 这不会影响 MySQL 选择的每个组的单行。

第三，MySQL has limitations with correlated subqueries这会影响性能。如果您可以将这些更改为 JOIN，您应该会看到性能提升。

考虑到所有这些，我会这样重写您的查询:

SET @rank=0;
INSERT INTO Board (TopScorePKID, GamePKID, UserPKID, UniquePlayerID, PlayerName, TopPlayerScore, Position, Date)
SELECT  bad.ID AS TopScorePKID, 
        bad.GamePKID, 
        bad.UserPKID, 
        bad.UniquePlayerID, 
        bad.PlayerName, 
        bad.TopPlayerScore, 
        @rank:=@rank+1 AS Position, 
        bad.Date
FROM    (   SELECT  g.GamePKID, 
                    g.TopPlayerScore, 
                    l.ID,  
                    l.UserPKID, 
                    u.UniquePlayerID, 
                    u.PlayerName, 
                    l.Date
            FROM    Games g
                    INNER JOIN TopPlayerScores l
                        ON l.GamePKID = g.GamePKID
                    INNER JOIN UserDetails u
                        ON u.UserPKID = l.UserPKID
                    INNER JOIN
                    (   SELECT  TopPlayerScores.UserPKID, MAX(games.TopPlayerScore) AS MaxPlayerScore
                        FROM    TopPlayerScores
                                INNER JOIN Games
                                    ON Games.GamePKID = TopPlayerScores.GamePKID
                        GROUP BY TopPlayerScores.UserPKID
                    ) MaxScore
                        ON MaxScore.UserPKID = l.UserPKID
                        AND MaxScore.MaxPlayerScore = g.TopPlayerScore
            WHERE   u.SECRET_DETAIL = 0 
        ) AS bad
ORDER BY bad.TopPlayerScore DESC, bad.Date ASC;

Example on SQL Fiddle

子查询 MaxScore 应该具有将结果限制为每个玩家一行的效果(只有他们的最高分)，尽管可能需要额外的逻辑来处理平局(例如，玩家有相同的分数)超过一场比赛的最高分)。在不知道确切要求的情况下，我无法更正此问题。

编辑

为了删除玩家在 2 场或更多场比赛中得分相同的重复项，并使其真正具有确定性，您需要添加进一步的子查询:

SET @rank=0;

SELECT  bad.ID AS TopScorePKID, 
        bad.GamePKID, 
        bad.UserPKID, 
        bad.UniquePlayerID, 
        bad.PlayerName, 
        bad.TopPlayerScore, 
        @rank:=@rank+1 AS Position, 
        bad.Date
FROM    (   SELECT  Games.GamePKID, 
                    Games.TopPlayerScore, 
                    TopPlayerScores.ID,  
                    TopPlayerScores.UserPKID, 
                    UserDetails.UniquePlayerID, 
                    UserDetails.PlayerName, 
                    TopPlayerScores.Date
            FROM    Games
                    INNER JOIN TopPlayerScores
                        ON TopPlayerScores.GamePKID = Games.GamePKID
                    INNER JOIN UserDetails
                        ON UserDetails.UserPKID = TopPlayerScores.UserPKID
                    INNER JOIN
                    (   SELECT  TopPlayerScores.UserPKID, MAX(games.TopPlayerScore) AS TopPlayerScore
                        FROM    TopPlayerScores
                                INNER JOIN Games
                                    ON Games.GamePKID = TopPlayerScores.GamePKID
                        GROUP BY TopPlayerScores.UserPKID
                    ) MaxScore
                        ON MaxScore.UserPKID = TopPlayerScores.UserPKID
                        AND MaxScore.TopPlayerScore = Games.TopPlayerScore
                    INNER JOIN
                    (   SELECT  TopPlayerScores.UserPKID, games.TopPlayerScore, MAX(Date) AS Date
                        FROM    TopPlayerScores
                                INNER JOIN Games
                                    ON Games.GamePKID = TopPlayerScores.GamePKID
                        GROUP BY TopPlayerScores.UserPKID, games.TopPlayerScore
                    ) MaxScoreDate
                        ON MaxScoreDate.UserPKID = TopPlayerScores.UserPKID
                        AND MaxScoreDate.TopPlayerScore = Games.TopPlayerScore
                        AND MaxScoreDate.Date = TopPlayerScores.Date
            WHERE   UserDetails.SECRET_DETAIL = 0 
        ) AS bad
ORDER BY bad.TopPlayerScore DESC, bad.Date ASC;

Example on SQL Fiddle

---

注意如果/当 MySQL 引入诸如 ROW_NUMBER() 之类的分析函数时，或者如果您切换到已经支持它们的 DBMS，此查询将变得更加简单，以防万一这些事情发生，这里是使用 ROW_NUMBER() 的解决方案`

SELECT  bad.ID AS TopScorePKID, 
        bad.GamePKID, 
        bad.UserPKID, 
        bad.UniquePlayerID, 
        bad.PlayerName, 
        bad.TopPlayerScore, 
        ROW_NUMBER() OVER(ORDER BY TopPlayerScore DESC) AS Position, 
        bad.Date
FROM    (   SELECT  Games.GamePKID, 
                    Games.TopPlayerScore, 
                    TopPlayerScores.ID,  
                    TopPlayerScores.UserPKID, 
                    UserDetails.UniquePlayerID, 
                    UserDetails.PlayerName, 
                    TopPlayerScores.Date,
                    ROW_NUMBER(PARTITION BY UserDetails.UserPKID 
                                ORDER BY Games.TopPlayerScore DESC,
                                        TopPlayerScores.Date DESC) AS RN
            FROM    Games
                    INNER JOIN TopPlayerScores
                        ON TopPlayerScores.GamePKID = Games.GamePKID
                    INNER JOIN UserDetails
                        ON UserDetails.UserPKID = TopPlayerScores.UserPKID
            WHERE   UserDetails.SECRET_DETAIL = 0 
        ) AS bad
WHERE   bad.RN = 1
ORDER BY bad.TopPlayerScore DESC, bad.Date ASC;

Example on SQL Fiddle using ROW_NUMBER()