MySQL高分榜仅保留前5条记录的高效SQL实现问询

问题回顾 #

你有一个Highscores表，结构示例如下：

核心需求是：每次录入新分数后，每个(world, level)分组仅保留前5高分记录；同分规则必须保留最后插入的最新记录（比如同分时，后插入的player_id=8的200分要保留，删除先插入的player_id=2的200分）。

你最初的两步实现思路可行，但确实可以优化得更高效，尤其是处理同分场景和减少操作步骤。

最优方案（MySQL 8.0+，利用窗口函数） #

首先建议给表添加一个AUTO_INCREMENT类型的主键tableid（你已经提到了这个优化点，非常关键），它能帮我们精准识别记录的插入顺序，完美解决同分问题。

完整操作流程

1. 插入新分数记录：如果允许同一个(world, level, player_id)存在多条分数记录，用INSERT；如果需要覆盖该玩家在该关卡的记录，用REPLACE：

-- 普通插入（保留历史记录）
INSERT INTO Highscores (world, level, player_id, score) VALUES (1, 1, 6, 500);

-- 替换（覆盖同(world, level, player_id)的记录）
REPLACE INTO Highscores (world, level, player_id, score) VALUES (1, 1, 6, 500);

2. 单条SQL清理所有分组的冗余记录：利用ROW_NUMBER()窗口函数，一次操作就能处理所有(world, level)分组，自动保留前5高分（含同分最新记录）：

DELETE h
FROM Highscores h
JOIN (
    SELECT 
        tableid,
        -- 按world+level分组，先按分数降序，再按插入时间（tableid）降序排序
        ROW_NUMBER() OVER (
            PARTITION BY world, level 
            ORDER BY score DESC, tableid DESC
        ) AS rank_num
    FROM Highscores
) ranked_records ON h.tableid = ranked_records.tableid
-- 删除排名超过5的记录
WHERE ranked_records.rank_num > 5;

方案优势

• 同分处理精准：ORDER BY score DESC, tableid DESC确保同分情况下，后插入的记录（tableid更大）排名更靠前，优先保留。
• 高效批量处理：不需要指定特定的world和level，一次SQL就能清理所有分组的冗余数据，比逐条处理分组效率高得多。
• 性能更优：窗口函数是MySQL 8.0+的原生优化特性，执行效率远高于多层嵌套子查询，数据量大时优势明显。

兼容MySQL 5.x版本（无窗口函数） #

如果你的MySQL版本低于8.0，无法使用窗口函数，可以基于自增主键tableid优化你的原始思路：

DELETE FROM Highscores 
WHERE (world, level, tableid) NOT IN (
    SELECT world, level, tableid
    FROM (
        SELECT 
            h1.world, 
            h1.level, 
            h1.tableid
        FROM Highscores h1
        LEFT JOIN Highscores h2 
            ON h1.world = h2.world 
            AND h1.level = h2.level 
            AND (h2.score > h1.score OR (h2.score = h1.score AND h2.tableid > h1.tableid))
        GROUP BY h1.world, h1.level, h1.tableid
        -- 统计比当前记录分数更高或同分更新的记录数，小于5则保留
        HAVING COUNT(h2.tableid) < 5
    ) AS keep_records
);

这个子查询的逻辑是：对每条记录，统计有多少条比它分数更高，或者同分但插入时间更新的记录。如果这个数量小于5，说明该记录属于当前分组的前5名，需要保留；否则删除。

关于原始方案的小问题 #

你最初的删除语句有个潜在问题：当分组内有超过5条同分记录时，会误删部分应该保留的最新记录。比如你举的同分示例中，原始语句会保留所有score>=100的记录（因为前5的min是100），但无法区分同分的新旧，而引入tableid后就能完美解决这个问题。

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