MySQL环境下ELO天梯排名查询、相邻玩家获取及窗口函数性能问题
业务背景与现存问题
本人了解此类问题过往已有相关提问与成熟解决方案,但现有方案无法适配我的业务场景,具体需求与现存问题如下:
核心业务需求
- 需实现两类ELO榜查询能力:
- 获取ELO榜Top20玩家的排名数据(该部分开发已完成)
- 获取某指定用户的全局排名,同时返回该用户排名之上、之下各10名的相邻玩家数据
当前开发痛点
- 无法通过单条高效查询同时获取单个用户的自身排名、及其上下相邻各10名的玩家数据
- 对
RANK() OVER()窗口函数在数据量超过100万行场景下的执行性能存在顾虑 - 业务要求即使玩家的ELO值相同也不得出现并列排名,因此采用多字段排序逻辑实现排名区分
现有数据库与代码实现
当前使用MySQL InnoDB存储引擎,相关表结构与已开发的查询逻辑如下:
elo_ladder 表结构定义
CREATE TABLE IF NOT EXISTS elo_ladder ( elo_ladder_incr INT NOT NULL AUTO_INCREMENT, player_id INT DEFAULT NULL, elo_rank INT DEFAULT 1000, elo_rank_wins INT DEFAULT 0, PRIMARY KEY (elo_ladder_incr), KEY elo_rank_key (elo_rank), KEY elo_rank_wins_key (elo_rank_wins), FOREIGN KEY (player_id) REFERENCES users(player_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
Top20玩家查询(已完成开发)
// Get top ranked players SELECT player_id, elo_rank, elo_rank_wins FROM elo_ladder ORDER BY elo_rank DESC, elo_rank_wins DESC LIMIT 20;
指定用户排名查询(当前实现)
SELECT * FROM ( SELECT player_id, t.elo_rank, (RANK() OVER (ORDER BY t.elo_rank DESC, t.elo_rank_wins DESC)) AS global_rank FROM ( SELECT player_id, elo_rank, elo_rank_wins FROM elo_ladder GROUP BY player_id ) AS t ) AS rt WHERE rt.player_id = 30;
内容的提问来源于stack exchange,提问作者nonerth
相关产品推荐
相关产品推荐

