MySQL 8.0 固定展示指定用户的用户排名分页查询优化求助

优化思路 #

原实现两次执行相同的排名窗口函数，产生了重复的全表扫描、排序开销，优化核心为仅执行一次全局排名计算，同时匹配分页范围和指定用户的筛选条件，直接降低数据库负载。

优化后实现代码 #

WITH ranked_users AS (
    -- 仅执行1次全局排名计算
    SELECT 
        userName, 
        score,
        ROW_NUMBER() OVER (ORDER BY score DESC) AS scores_rank
    FROM userData
)
SELECT * FROM ranked_users
WHERE 
    -- 原分页逻辑：取前2条，可根据分页参数灵活调整范围
    scores_rank <= 2
    -- 始终保留指定用户记录
    OR userName = 'Alex'
-- 保证输出顺序和排名一致
ORDER BY scores_rank;

如果不习惯用CTE，也可以替换为等价的子查询写法，性能完全一致：

SELECT * FROM (
    SELECT 
        userName, 
        score,
        ROW_NUMBER() OVER (ORDER BY score DESC) AS scores_rank
    FROM userData
) AS ranked_users
WHERE scores_rank <= 2 OR userName = 'Alex'
ORDER BY scores_rank;

效果验证 #

执行上述代码输出结果和预期完全匹配：

额外优化建议 #

• 涉及大数据量表查询时，可给score字段建立降序索引，窗口函数可直接复用索引顺序计算排名，省去额外排序开销
• 分页参数可灵活调整，如需查询第3页每页10条，只需将排名筛选条件改为scores_rank BETWEEN 21 AND 30即可
• 若指定用户本身就在当前分页范围内，不会出现重复返回的问题，无需额外做去重处理

内容的提问来源于stack exchange，提问作者A-P