查询排行榜玩家排名性能指数下降，求优化方案

你的问题很典型——这种自连接+COUNT(DISTINCT)的排名计算方式，本质是对每一行都要和同排行榜的所有行做比较，时间复杂度是O(n²)，数据量上去后性能必然指数级下滑，完全符合你观察到的现象。下面给你几个实用的优化方向：

1. 用窗口函数（首选，支持现代数据库版本） #

如果你的数据库是MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的版本，这是最优解。你原查询的COUNT(DISTINCT s2.score)逻辑，其实和DENSE_RANK()窗口函数的行为完全一致——相同分数的用户共享同一排名，不同分数的排名连续递增。

替换后的查询语句：

SELECT 
  *,
  DENSE_RANK() OVER (PARTITION BY leaderboard ORDER BY score DESC) AS `rank`
FROM leaderboards;

• PARTITION BY leaderboard：按排行榜分组计算排名
• ORDER BY score DESC：分数从高到低排序
• DENSE_RANK()：保证相同分数同排名，下一个不同分数的排名直接+1（和你原查询的逻辑完全匹配）

这个方案的时间复杂度是O(n log n)，数据量再大也能保持稳定的性能，代码还简洁易维护。

2. 用变量计算（适配旧版MySQL） #

如果你还在使用MySQL 5.7及以下不支持窗口函数的版本，可以用用户变量来实现排名计算，性能同样远优于自连接：

SELECT 
  leaderboard,
  user_id,
  score,
  @current_rank := IF(@prev_leaderboard = leaderboard, 
                      IF(@prev_score = score, @current_rank, @current_rank + 1), 
                      1) AS `rank`
FROM leaderboards,
     (SELECT @current_rank := 0, @prev_leaderboard := NULL, @prev_score := NULL) AS vars
ORDER BY leaderboard, score DESC;

注意：必须按leaderboard和score DESC排序，否则变量的状态跟踪会出错。这个方案是单表扫描一次，性能比自连接提升几个数量级。

3. 预计算排名（适合查询极频繁的场景） #

你提到的“每次更新时计算排名”也是可行的，适合查询量远大于更新量、或者对实时性要求不是极高的场景：

• 触发器维护：在leaderboards表的INSERT/UPDATE触发器中，自动更新当前用户所在排行榜的所有用户排名。但要注意，如果更新频繁，触发器可能会带来额外的性能开销，建议只在必要时使用。
• 应用层/定时任务：在用户提交新分数时，由应用代码计算好排名再写入数据库；或者后台跑定时任务（比如每天凌晨）批量计算所有排行榜的排名，适合非实时的周榜、月榜等场景。

为什么原查询性能差？ #

再补充下原查询的问题：它通过自连接把每个用户和同排行榜的所有用户做关联，然后统计比当前用户分数高或相等的不同分数数量。当你的排行榜有1000个用户时，就会产生1000*1000=100万条关联数据，分组计算时的开销会爆炸式增长，这就是性能指数级下降的原因。

内容的提问来源于stack exchange，提问作者Il Duke