怎样在5秒内从5亿记录的用户积分表实时得到用户的排名

今天在群里有网友提出如下问题：

 有表 A 字段信息如下：(手机号码, 用户积分)怎么能够在5秒内返回用户积分在所有用户中的排名 (用户在登陆系统时给出登陆时的排名 ，用户积分可能增加、减少 ，在本次登陆后的积分变化在下次登陆时重新排名，积分相同的用户排名相同，这时并给出同一排名的用户数是多少)目前数据量在5亿左右 ，可以加索引，根据需要建立新表 等 硬件配置中等水平 。请给出具体方案

 

初一看这个问题我们可以通过预计算来解决，就是给该表增加一个排名字段，然后第天夜里计算一次，然后通过分区+索引只需要

select 排名 from 表 where 手机号=xxxx  在5秒内返回结果那是轻松加愉快的事情。

不过这个方案有个很明显的问题：实时性太差，一天才变化一次 只能用在用实时性要求低的地方。

 

那么怎么才能实时并快速的返回用户的排名呢？

 

我给出的解决方案如下：

 

建立结构如下的积分分布表：

积分	人数
10000	5000000
9998	2000000
9990	3000000

 

 

 

该表的数据可以在建表初期使用如下SQL填充：

 

Create table score_distribution as
select 积分,count([distinct] 手机号码) 人数
from 表
group by 积分 

 

假设积分最大值为100W，由于其具有不连续性，所以我们的score_distribution表未必有100W的数据，顶多2、3十万。

退一万步讲，就算这个值特别连续，由于我们的表只有两个字段，一行就算20字节，20*1000000/1024/1024=19M

表的数据量才19M左右，就算全表扫描也能在1秒能搞定。

 

有了这个表之后我们怎么实时的得到用户积分的排名呢？（要考虑积分在不停的改变）

 

如上面的表格所示，如果此时有一个用户的积分从9990变到了9999，那么他此时的排名应该是多少？

下面的SQL就可以告诉你：

select   sum( decode(积分,9999,0,人数))+1  from score_distribution where 积分>=9999

执行时间我相信不会超过1秒

 

另外，该用记的积分从9990变到9999的同时还有一件事情需要我们去做，那就向积分布表里插入新值，并更改现在值的人数，如下图所示：

积分	人数
10000	5000000
9999	1
9998	2000000
9990	2999999

 

此过程应该和积分改变的操作同属一个事务，并且考虑好并发以及锁的问题。

 

 那现在积分为9990的应该排名多少呢？

select   sum( decode(积分,9990,0,人数))+1  from score_distribution where 积分>=9990

 

 

到这里，我们就完美解决了这个问题。

 

最近送大家一句话，Design for performance,not tuning for performance!