请教一个游戏排行榜数据库设计的问题

[Adam.Lu]

Hi，大家好。

工作上遇到一个数据库设计的问题，希望能在group中找到解决方案。

现在有score表，这张表用于记录所有游戏的玩家分数，每一款游戏可以有多个排行榜（category_id）。

结构如下:
score(id, category_id, player_id, score, created)

需求：
－ 查询某个排行榜最近一周（7天）的排行榜列表（前n名次的玩家列表）
－ 查询某个排行榜最近一个月的排行榜列表（前n名次的玩家列表）
－ 查询某个排行榜全部时间的排行榜列表（前n名次的玩家列表）
－ 查询指定玩家在某个排行榜中指定时间段（最近一周、最近一个月、全部时间）的排名

对于同分的情况，先提交分数的玩家排名在前。

数据库环境：
mysql （ innodb），score表有100w+的数据。目前数据量不大的情况查询速度已经不太理想。准备入手进行优化。

－ 索引的话，因为几乎每个字段都会在查询条件中出现，实在不知道应该如何简历索引进行优化。
－ 如果每个游戏对应一张排行榜表的话，程序上不好控制，因为游戏会越来越多。
－ 如果将排行结果缓存，那么当某个玩家刚刚提交了高分，却发现自己排名没有改变，应该会觉得挺郁闷：（

针对排行这个问题，希望大家给点意见。谢谢：）

[Nick@Simple!]

我觉得像这种报表统计的东西，每次都实时查，会很耗资源。

给我做的话，我可能会以下两个方案

1. 如果报表的实时性要求不是太高的话，我会提取数据放到数据仓库中，定时做统计

2. 如果实时性要求比较高的话，可以将排行结果缓存。如果用户提交了高分，又想马上看到，可以把这个分数更新到相应的排行榜结果中，并且把排行版原来的最后一名的数据移除。

--
您收到此信息是由于您订阅了 珠三角技术沙龙（http://techparty.org） 论坛。
要在此论坛发帖，请发电子邮件到 guangzhou-...@googlegroups.com
要退订此论坛，请发邮件至
guangzhou-tech-p...@googlegroups.com
更多选项，请通过
http://groups.google.com/group/guangzhou-tech-party?hl=zh-CN 访问该论坛

--
To love is not to hold!

[mrshelly]

分表分库，做好查询缓存

[Yi Zhang]

2011/7/28 Nick@Simple! <oxt...@gmail.com>

我觉得像这种报表统计的东西，每次都实时查，会很耗资源。

给我做的话，我可能会以下两个方案

1. 如果报表的实时性要求不是太高的话，我会提取数据放到数据仓库中，定时做统计

2. 如果实时性要求比较高的话，可以将排行结果缓存。如果用户提交了高分，又想马上看到，可以把这个分数更新到相应的排行榜结果中，并且把排行版原来的最后一名的数据移除。

+1，不能指望数据库完成所有实时计算吧。这样无论怎么设计总会有拖死的一天的。

[B.Tag]

这个东西 我做过~~~flash小游戏的得分排行。。不过有点戳~~ 

-------------------------------------------------------------------------

        learn log: http://me.boolsir.com 

        my life log: http://www.boolsir.com

-------------------------------------------------------------------------

[Adam.Lu]

嗯，尝试使用第2个方法试试。不过统计当前玩家在所有时间段内的排行（或者在朋友之间的排行）不大好做缓存吧。要为每个用户都缓存排行，还要维护排行更
新。

On Jul 28, 11:27 pm, "Nick@Simple!" <oxti...@gmail.com> wrote:
> 我觉得像这种报表统计的东西，每次都实时查，会很耗资源。
>
> 给我做的话，我可能会以下两个方案
> 1. 如果报表的实时性要求不是太高的话，我会提取数据放到数据仓库中，定时做统计
> 2.
> 如果实时性要求比较高的话，可以将排行结果缓存。如果用户提交了高分，又想马上看到，可以把这个分数更新到相应的排行榜结果中，并且把排行版原来的最后一名的数据移除。
>

[王君]

1，排行榜的，维护一份缓存，每插入或更新一行记录时，计算要不要更新。2，个人的，维护一份分数结构，那个分数段多少人等等，每个人的准确排名也可实时出来。3，核心是，所有结果从缓存出而不是从数据库查。

[白鹏]

解决方案有很多，

索引优化的话，即可以为一个字段单独建索引，也可以为多个字段建联合索引，这要看sql语句的写法了，order、group,sum,avg等等统计函数都有自己的优化方式。设计完索引后最重要的是要做测试，看效果。

同时建立周月粒度中间结果统计表，后台定时程序自动统计写入中间结果。要尽量减少对score表的访问。

用缓存的话可以参考下这个：

http://code.google.com/p/groupkeycache/ 看看能不能直接用，如果不行顺着这个思路改改应该也差不多。

[赖勇浩]

2011/7/28 Adam.Lu <qing...@gmail.com>:
> Hi，大家好。
>
> 工作上遇到一个数据库设计的问题，希望能在group中找到解决方案。
无意中看到 Sina App Engine 架构师 kobe 的一篇文章，希望有所启发。
http://1.saecloud.sinaapp.com/?p=33
千万级的数据，每条记录如何能够实时准确的知道自己的排名？

> --
> 您收到此信息是由于您订阅了 珠三角技术沙龙（http://techparty.org） 论坛。
> 要在此论坛发帖，请发电子邮件到 guangzhou-...@googlegroups.com
> 要退订此论坛，请发邮件至
> guangzhou-tech-p...@googlegroups.com
> 更多选项，请通过
> http://groups.google.com/group/guangzhou-tech-party?hl=zh-CN 访问该论坛
>

--
web site：http://laiyonghao.com
twitter: http://twitter.com/laiyonghao

[雨晗]

Hi Adam:

你的游戏里，分数的区间大么？如果不大的话（比如分数小于100000），可以考虑换个思路做。

首先，简化模型，不考虑游戏的类型、查询的周期。用一个列表，记录每个分数的玩家数目，比如

CREATE TABLE ScoreCount (

  Score INT,

  Count INT,

) PRIMARY KEY(Score);

这样，可以通过简单的查询，获得某一个分数的排名。例如我们要查询1000分的玩家的排名。

SELECT SUM(Count) FROM ScoreCount WHERE Score > 1000;

只要记录下每个玩家的分数，就可以很快地获知他在排行榜上的排名了。而如果要查询前100名的排行榜，则首先可以查询前100名的分数为多少，再根据这个分数，去玩家表里查询对应的玩家即可。

解决了查询的问题，再看更新。更新玩家的分数时，需要一个事务，同事更更新三个状态：

1. 玩家的分数

2. ScoreCount中原分数的Count--

3. ScoreCount中新分数的Count++

OK，这样简化模型的问题就解决了。

再考虑增加游戏类型，这个非常简单，只要在ScoreCount表中增加一个字段: Category记录游戏类型，就可以满足需求了。

最后一个问题，排行榜的查询周期，例如查询7天的查询全部的，这个暂时没想到好的办法，大家集思广益？

btw：如果不用数据库的话，搞法很多的，不过玩数据结构始终没有玩数据库来得方便！

2011/7/28 Adam.Lu <qing...@gmail.com>

[Weng Wei]

有无可能使用redis的sorted set去做？

http://redis.io/commands/zrank
O(log(N))

100w+级别应能处理。

2011/7/28 Adam.Lu <qing...@gmail.com>:

[Weng Wei]

http://blog.nosqlfan.com/html/2235.html

2.排行榜应用，取TOP N操作
这个需求与上面需求的不同之处在于，前面操作以时间为权重，这个是以某个条件为权重，比如按顶的次数排序，这时候就需要我们的sorted
set出马了，将你要排序的值设置成sorted set的score，将具体的数据设置成相应的value，每次只需要执行一条ZADD命令即可。

2011/7/29 王君 <gelosi...@gmail.com>:

[Adam.Lu]

谢谢提供的思路：）不过由于有多个游戏，种类不同，排行榜种类也不同，所以分数区间肯定是很大的：（

2011/7/29 雨晗 <zou...@gmail.com>