如何优化row_number()窗口函数排名查询的性能？

表结构 #

表user_power_rank的结构如下：

现有索引 #

该表已创建的索引：

原查询语句 #

用于获取指定用户排名的SQL：

SELECT * FROM (
  SELECT id,power,row_number() OVER (ORDER BY power DESC, atime ASC) ranking
  FROM user_power_rank
) WHERE id="the-data-id"

性能表现 #

不同数据量下的平均查询时间：

执行计划 #

当前查询的执行计划：

1. 改写查询逻辑，避免全表排序 #

原查询的核心问题是先对全表开窗排序，再过滤单个id，导致必须扫描全表并生成完整排序结果后才能筛选目标数据。可以改为先获取目标用户的power和atime，再统计排名高于他的用户数量，直接计算排名：

SELECT 
  id, 
  power, 
  -- 统计power更大，或power相同但atime更小的用户总数，加1即为当前用户排名
  (SELECT COUNT(*) 
   FROM user_power_rank 
   WHERE power > upr.power 
      OR (power = upr.power AND atime < upr.atime)) + 1 AS ranking
FROM user_power_rank upr
WHERE id = "the-data-id"

该写法可直接利用现有i_power_desc_atime_asc索引快速定位符合条件的记录，彻底避免全表排序操作。

2. 新增覆盖索引（进一步提升性能） #

如果上述改写后仍有优化空间，可创建覆盖索引，让查询无需回表读取主表数据：

CREATE INDEX i_power_atime_id ON user_power_rank (power DESC, atime ASC, id);

这个索引包含了子查询所需的所有字段，可直接从索引中获取统计数据，大幅减少IO开销。

3. 预缓存排名结果（实时性要求低时最优） #

若用户的power和atime并非实时更新，可定期预计算所有用户的排名并存储到缓存表中：

• 创建user_rank_cache表，包含id、ranking字段
• 定时执行全表排名计算任务，更新缓存表数据
• 查询时直接读取缓存：SELECT ranking FROM user_rank_cache WHERE id = "the-data-id"
  此方案能将查询耗时降至毫秒级以下，是性能最优的选择。

内容的提问来源于stack exchange，提问作者Ace.Yin