MySQL百万级数据表排名更新查询优化（要求毫秒级执行）

问题根源 #

你的原SQL功能正常但耗时1.5秒，核心问题出在缺少合适的索引：

• 子查询需要过滤大量数据并按pfp_strength排序，没有索引的话数据库只能全表扫描后再排序，百万级数据下开销极大
• 关联更新时的id匹配也可能因为索引缺失导致效率低下

优化步骤 #

1. 创建覆盖索引（最关键的一步） #

针对过滤条件、排序字段和返回字段创建复合覆盖索引，让数据库直接从索引中获取所有需要的数据，无需回表：

CREATE INDEX idx_leaderboard_rank_calc ON all_time_leaderboard_rank (
    standard_test_category_id, 
    type, 
    sport_id, 
    school_id, 
    pfp_strength DESC, 
    id
);

这个索引包含了所有WHERE条件的字段、排序用的pfp_strength，以及需要返回的id，查询时数据库可以直接通过索引完成排名计算，完全避免全表扫描。

2. 简化更新语句 #

去掉冗余的WHERE 1（这行没有实际意义），保持语句简洁：

UPDATE all_time_leaderboard_rank nlr
JOIN (
    SELECT 
        id,
        RANK() OVER (ORDER BY pfp_strength DESC) AS at_rank
    FROM all_time_leaderboard_rank
    WHERE 
        standard_test_category_id = 53
        AND type = 'pfp_strength'
        AND sport_id = 1
        AND school_id = '386a06e0-758a-11e9-b57b-ab5b591d14f6'
) rt ON rt.id = nlr.id
SET nlr.`rank` = rt.at_rank;

3. 兼容低版本数据库的备选方案 #

如果你的MySQL版本低于8.0（不支持窗口函数），可以用用户变量模拟RANK()逻辑，同样依赖上面创建的索引：

SET @prev_strength = NULL;
SET @current_rank = 0;
SET @row_num = 0;

UPDATE all_time_leaderboard_rank nlr
JOIN (
    SELECT 
        id,
        pfp_strength,
        CASE 
            WHEN @prev_strength = pfp_strength THEN @current_rank
            ELSE @current_rank := @row_num + 1
        END AS at_rank,
        @row_num := @row_num + 1,
        @prev_strength := pfp_strength
    FROM all_time_leaderboard_rank
    WHERE 
        standard_test_category_id = 53
        AND type = 'pfp_strength'
        AND sport_id = 1
        AND school_id = '386a06e0-758a-11e9-b57b-ab5b591d14f6'
    ORDER BY pfp_strength DESC
) rt ON rt.id = nlr.id
SET nlr.`rank` = rt.at_rank;

注意：使用变量时必须保证子查询中的ORDER BY生效，否则排名逻辑会出错。

验证优化效果 #

执行索引创建后，用EXPLAIN分析子查询的执行计划：

EXPLAIN
SELECT id, RANK() OVER (ORDER BY pfp_strength DESC) AS at_rank
FROM all_time_leaderboard_rank
WHERE standard_test_category_id = 53
  AND type = 'pfp_strength'
  AND sport_id = 1
  AND school_id = '386a06e0-758a-11e9-b57b-ab5b591d14f6';

如果输出的key列显示idx_leaderboard_rank_calc，且Extra列显示Using index，说明索引生效，此时执行更新语句应该能达到毫秒级。

内容的提问来源于stack exchange，提问作者Arthur Atul