MySQL大表更新玩家周排名超时 求高效优化方案

你的核心问题在于原查询使用了关联子查询，每一行数据都需要执行两次嵌套子查询来计算排名——对于10万行的表来说，这会产生百万级的重复查询，自然会超时。加上如果没有合适的索引，数据库需要全表扫描多次，性能雪上加霜。下面是几个高效的优化方案，按推荐程度排序：

1. 优先使用窗口函数（MySQL 8.0+ 推荐） #

MySQL 8.0及以上支持窗口函数，能一次性完成分组排名计算，效率比关联子查询高几个数量级。根据你的示例（相同分数的玩家共享排名，不跳过名次），应该用DENSE_RANK()函数：

WITH ranked_players AS (
    SELECT 
        player_id,
        yearweek,
        DENSE_RANK() OVER (PARTITION BY yearweek ORDER BY ranking_points DESC) AS new_ranking_pos
    FROM players_weekly_rankings
)
UPDATE players_weekly_rankings pr
JOIN ranked_players rp 
    ON pr.player_id = rp.player_id 
    AND pr.yearweek = rp.yearweek
SET pr.ranking_pos = rp.new_ranking_pos;

为什么这更高效？ #

• 窗口函数只需要一次全表扫描就能完成所有分组的排名计算，而原查询每一行都要单独计算。
• 配合合适的索引（下文会提到），扫描效率会进一步提升。

2. 为旧版本MySQL使用变量+临时表（MySQL 5.x） #

如果你的MySQL版本不支持窗口函数，可以用用户变量来逐行计算排名，再通过临时表批量更新：

-- 创建临时表存储计算好的排名（添加主键加速关联）
CREATE TEMPORARY TABLE temp_ranks (
    player_id INT,
    yearweek VARCHAR(7),
    new_ranking_pos INT,
    PRIMARY KEY (player_id, yearweek)
);

-- 初始化变量
SET @current_yearweek = '';
SET @current_points = NULL;
SET @rank = 0;

-- 计算每个玩家的排名并插入临时表
INSERT INTO temp_ranks (player_id, yearweek, new_ranking_pos)
SELECT 
    player_id,
    yearweek,
    -- 按年周分组，相同分数保持排名，不同分数递增
    CASE 
        WHEN yearweek != @current_yearweek THEN 
            @rank := 1
        WHEN ranking_points != @current_points THEN 
            @rank := @rank + 1
        ELSE 
            @rank
        END AS new_ranking_pos,
    -- 更新变量状态
    @current_yearweek := yearweek,
    @current_points := ranking_points
FROM players_weekly_rankings
ORDER BY yearweek, ranking_points DESC;

-- 批量更新原表
UPDATE players_weekly_rankings pr
JOIN temp_ranks tr 
    ON pr.player_id = tr.player_id 
    AND pr.yearweek = tr.yearweek
SET pr.ranking_pos = tr.new_ranking_pos;

-- 清理临时表
DROP TEMPORARY TABLE temp_ranks;

3. 必须添加的索引优化 #

不管用哪种方案，都需要给表添加复合索引，让数据库快速按年周分组并按分数排序：

CREATE INDEX idx_yearweek_points ON players_weekly_rankings(yearweek, ranking_points DESC);

这个索引能让数据库在计算排名时，直接按yearweek分组，同时按ranking_points降序获取数据，避免全表扫描。

4. 可选：分批更新减少压力 #

如果单次更新10万行仍然超时，可以按yearweek分批处理，每次只更新一个周的数据：

-- 示例：先更新2020/01的排名
WITH ranked_players AS (
    SELECT 
        player_id,
        DENSE_RANK() OVER (ORDER BY ranking_points DESC) AS new_ranking_pos
    FROM players_weekly_rankings
    WHERE yearweek = '2020/01'
)
UPDATE players_weekly_rankings pr
JOIN ranked_players rp ON pr.player_id = rp.player_id
SET pr.ranking_pos = rp.new_ranking_pos
WHERE pr.yearweek = '2020/01';

-- 依次处理其他yearweek

这种方式能降低单次更新的资源占用，避免因锁表或内存不足导致超时。

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