今天在群里有网友提出如下问题:
有表 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!