我正在研究如何为我公司发布的Facebook赛车游戏实现全球排行榜。我想做的是能够存储玩家的用户ID和他们参加比赛的时间。我有一张像下面这样的桌子:
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| userID | mediumint(8) unsigned | NO | PRI | 0 | |
| time | time | YES | MUL | NULL | |
+--------+-----------------------+------+-----+---------+-------+
还有一组类似的数据样本:
+--------+----------+
| userID | time |
+--------+----------+
| 505610 | 10:10:10 |
| 544222 | 10:10:10 |
| 547278 | 10:10:10 |
| 659241 | 10:10:10 |
| 681087 | 10:10:10 |
+--------+----------+
我的查询将来自PHP。现在,如果我假设我有无限的资源,我可以做的是:
$q1 = "Set @rank := 0";
$q2 = "select @rank:=@rank+1 as rank,userID,time from highscore order by time asc where userID=$someUserID";
$q3 = "Set @rank := 0";
$q4 = "select @rank:=@rank+1 as rank,userID,time from highscore order by time asc where rank > $rankFromSecondQuery - 10 and rank < $rankFromSecondQuery + 10";
但我没有无限的资源,我必须能够扩大规模来支持数百万玩家,因为这将成为Facebook上的社交游戏。因此,在花了几天时间在谷歌上搜索之后,我已经能够将我的查询归结为:
$q5 = "select rank,userID,time from (select @rank:=0) r, (select @rank:=@rank+1 as rank,userID,time from highscore order by time asc) as myMine where userID=$someUserID"
$q6 = "select rank,userID,time from (select @rank:=0) r, (select @rank:=@rank+1 as rank,userID,time from highscore order by time asc) as myMine where rank > $rankFromFirstQuery - 10 and rank < $rankFromSecondQuery + 10";
这是可行的,但它不是很漂亮,每个查询的平均运行时间约为2.3秒。
编辑:以下是$q5和$q6在运行它们时给我的信息:
mysql> select rank,userID,time from (select @rank:=0) r, (select @rank:=@rank+1 as rank,userID,time from highscore order by time asc) as myMine where userID=11345;
+--------+--------+----------+
| rank | userID | time |
+--------+--------+----------+
| 423105 | 11345 | 12:47:23 |
+--------+--------+----------+
1 row in set (2.42 sec)
mysql> select rank,userID,time from (select @rank:=0) r, (select @rank:=@rank+1 as rank,userID,time from highscore order by time asc) as myMine where rank>423100 and rank<423110;
+--------+---------+----------+
| rank | userID | time |
+--------+---------+----------+
| 423101 | 2416665 | 12:47:22 |
| 423102 | 2419720 | 12:47:22 |
| 423103 | 2426606 | 12:47:22 |
| 423104 | 2488517 | 12:47:22 |
| 423105 | 11345 | 12:47:23 |
| 423106 | 92350 | 12:47:23 |
| 423107 | 94277 | 12:47:23 |
| 423108 | 114685 | 12:47:23 |
| 423109 | 135434 | 12:47:23 |
+--------+---------+----------+
9 rows in set (2.58 sec)
下面是解释扩展块$q5和$q6看起来几乎相同:
mysql> explain select rank,userID,time from (select @rank:=0) r, (select @rank:=@rank+1 as rank,userID,time from highscore order by time asc) as myMine where userID=11345;
+----+-------------+------------+--------+---------------+----------+---------+------+---------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+----------+---------+------+---------+----------------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 2500000 | Using where |
| 3 | DERIVED | highscore | index | NULL | idx_time | 4 | NULL | 2500842 | Using index |
| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+------------+--------+---------------+----------+---------+------+---------+----------------+
因此,最终,我真正希望能够做的是将其归结为一个查询,这样我就可以用一两个高CPU服务器来调整执行时间。要么是这样,要么我想找出一种方法,只对查询中与explain块中的derive3行相关联的部分进行索引,该行对表中的所有行进行索引。
以下是我迄今为止尝试过的几个没有成功的查询:
select rank,userID,time from (select @rank:=0) r, (select @playerRank := rank from (select @rank:=@rank+1 as rank,userID,time from highscore order by time asc) as myMine where userID=11345) as myFoo where @playerRank>423100 and @playerRank<423110;
select rank,userID,time from (select @playerRank := rank from (select @rank := 0) r, (select @rank:=@rank+1 as rank,userID,time from highscore order by time asc) as myMine where userID=11345) as myFoo where @playerRank>423100 and @playerRank<423110;
select * from (select @rank:=0) r, (select @playerRank := userID from (select @rank:=@rank+1 as rank,userID,time from highscore order by time asc) as myMine where userID=11345) as myFoo where @playerRank>423100 and @playerRank<423110;
前两个游戏我一个"错误1054(42S22):未知列'排名'在'字段列表'错误,第三个只是返回了一个空集,而不是我正在寻找的数据。
有人知道如何让上面列出的两个查询命中索引,从而减少执行时间,或者如何将两个查询合并为一个查询,从而只需要经历一次痛苦的执行时间吗?如果有人有使用类似Percona的经验并愿意分享他们的经验,我也愿意进行调整/优化,比如调整MySQL配置设置和/或使用类似Per科纳的东西。
运行完$q5
之后,您应该知道用户的级别,之后您应该能够使用限制来获得正确的行
$lowest_rank_to_fetch = max(0, $rankFromFirstQuery - 10);
$q6l = "SELECT userID, time
FROM highscore
ORDER BY time ASC
LIMIT {$lowest_rank_to_fetch}, 21";
/* some execute query function */
foreach(range($lowest_rank_to_fetch, $lowest_rank_to_fetch+21) as $current_rank)
{
/* some database fetch function */
/* add $current_rank to result */
}
您可以使用count()首先获得排名,这对于第一个查询来说应该会执行得更好:
SELECT COUNT(h.userID) as rank, h2.userID, h2.time
FROM highscore h
LEFT OUTER JOIN highscore h2 ON (h.time <= h2.time)
WHERE h2.userID = ?
然后你可以使用Puggan的技术来查询附近的排名。
SELECT ... ORDER BY time LIMIT $lowest_rank, 21
我想提出这个替代解决方案来实现您想要实现的目标。
制作一个单独的表格来存储排名。不要每次用户想知道他/她的排名时都计算它,也不要将它包含在现有的表中。当分数更新与排名计算竞争时,将排名放在一个单独的表中有望缓解锁定竞争问题。
定期重新计算列组。进行此重新计算时,请截断列组表并从头开始重新创建。使用大容量加载操作(load DATA INFOILE)或使其成为MyISAM表(在表末尾插入时速度很快)来执行此操作。无论哪种方式,实际写出表格都应该相对较快;至少比更新已经就位的表中的数百万行更快。这两种方法都会使列组表变得脆弱,在发生崩溃时容易丢失,但这没关系,因为这本质上是瞬态数据。只要你的分数表是稳定的,你是安全的。通过定期重新计算,您可以避免随着播放次数的增加而越来越频繁地进行计算的问题,直到您碰壁为止。
如果用户的得分在前100名之内,请立即推出他们的新得分。用户可能想浏览前100名,看看谁的得分最高。我认为几乎没有人想真正浏览这一点以下的列表。
允许用户立即查看朋友的分数,以及他们之间的相对排名。这可能是大多数用户感兴趣的排名。我知道当我妻子玩Facebook游戏时,她对自己的总体排名不感兴趣,但她非常想知道自己是否打败了大学同学。
显示玩家的总体排名,以及他们朋友的排名,在用户最近一次游戏后无效,并在下一次更新准备就绪时异步加载。
另一个考虑因素是,如果这场比赛持续几年,你的记分牌最终会被不活跃玩家的旧分数堵塞,尤其是在低端。您可能需要考虑是否值得将这些分数归档。例如,你可以说,任何在积分榜上排名较低75%的球员,只有在过去6个月内参加比赛,才会被考虑进入排名。然后,将他们的分数移到一个档案表中,在那里他们会被记住,如果该球员回来了,可以恢复到记分板上,但不必每次计算排名时都被包括在排序中。是的,这可以说会让你的排名不那么"真实",但人们无论如何都只是为了好玩。这会产生副作用,让他们的排名看起来更好,这也很有趣。记分牌上的一些细则会简要提到旧分数不包括在内,所以你仍然可以说一切都在上面。