一个千万级排行榜怎么实现,每个用户都有自己得名次,同时展示前100名
user_score表
create table user_score
(
id int auto_increment
primary key,
score int default 0 not null,
user_id int default 12 not null,
constraint user_score_id_uindex
unique (id)
);
方案1:冷热数据+redis有序集合
方案2:指定一个冷时间段,处理排名,前一百单存一个redis key,然后各自用户各自存一个redis key,用sh或php命令处理,不会中断(key value存json,外面用脚本跳表去排序)
跳表实现:http://www.xiaomlove.com/2020/03/19/skip-list-implement-by-php/
方案3:sql
select *
from (select row_number() over (order by total desc) as number, user_id, total
from (SELECT SUM(score) AS total,
user_id
FROM user_score
GROUP BY user_id
) t) s
where number <= 3;