我在游戏服务器上有一个巨大的瓶颈,用于存储当前排行榜的查询。
我目前只通过cron每5分钟调用一次此查询,但希望优化它足以每分钟或需要时调用。
查询耗时30秒,目前只有~2000个用户和7000个游戏(存储在games和TopPlayerScores中)。恐怕情况只会变得更糟!!请救救我,溢出-克诺比!你是我唯一的希望!
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);
请帮帮我!!我应该把它分解成视图吗?或者使用内连接关键字?最好的方法是什么?
谢谢你看了这么乱:D!
UPDATED 1.0:解释扩展结果:
<>之前id select_type表类型possible key key_len ref rows filtered Extra1 primary all null null null null 1521 100.00(1)使用临时文件;使用filesort2 DERIVED u eq_ref PRIMARY PRIMARY 4 dbname . l.s userpkid 1 100.00 Using where2派生k eq_ref PRIMARY PRIMARY 4 DBNAME.l.GamePKID 1 100.00使用where3从属子查询ALL NULL NULL NULL NULL 6923 100.00使用临时的;使用filesort3依赖子查询g eq_ref PRIMARY PRIMARY 4 DBNAME.t.GamePKID 1 100.00使用where之前更新2.0:查询表的有限模式
使用游戏来存储游戏分数和其他关于游戏的信息
`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`)
)
我要注意的第一件事是,尽管这不会提高性能,但您正在使用的JOIN语法在20多年前就被ANSI 92预期连接语法所取代了,这当然完全是主题,但是Aaron Bertrand解释了切换到新语法的一些很好的理由。
要注意的第二件事是,您的结果将是不确定的。选择的列既不包含在聚合中,也不包含在分组中。虽然MySQL允许这样做,但您并没有按照MySQL的意图使用该功能。MySQL文档状态:
MySQL扩展了GROUP BY的使用,以便选择列表可以引用未在GROUP BY子句中命名的非聚合列。这意味着上面的查询在MySQL中是合法的。您可以使用此功能通过避免不必要的列排序来获得更好的性能分组。但是,这主要用于每个中的所有值未在GROUP BY中命名的非聚合列对于每个列都是相同的组。服务器可以自由地从每个组中选择任何值,因此除非它们相同,否则所选的值是不确定的。
然而,您所包含的一些列(g.GamePKID
, g.TopPlayerScore
, l.ID
, l.Date
)不满足对每个组相同的条件,因此,如前所述,MySQL可以自由选择它喜欢的任何值,即使您有ORDER BY g.TopPlayerScore DESC, Date ASC
,这也不影响MySQL选择的每个组的单行。
第三,MySQL在关联子查询方面有限制,这会影响性能。如果您可以将这些更改为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;
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;
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;
使用ROW_NUMBER()