优化MySQL分组查询：获取分组内最高grade的完整行

原查询性能问题分析 #

原查询使用相关子查询，外层每一行数据都会触发一次子查询计算对应分组的最高grade，大数据集下重复计算量极大，导致耗时超3000秒；另外外层的GROUP BY p.user_id, p.step_id属于冗余操作（甚至不符合SQL标准，因为SELECT了所有列但只按两个字段分组），会额外消耗资源。

你改写的内连接查询错误原因 #

子查询中GROUP BY p2.user_id, p2.step_id但同时SELECT了p2.id，这会导致数据库随机返回分组内的某一个id，而这个id和MAX(p2.grade)没有对应关系，最终关联得到的行自然不是每组grade最高的记录，结果集也就和原查询不一致。

正确的高效查询方案 #

以下两种方案均能保证结果与原查询一致，且性能远优于原查询：

方案1：保留所有同最高grade的记录（与原查询逻辑完全匹配）

使用RANK()窗口函数，同一(user_id, step_id)分组内grade相同的记录会被全部保留：

SELECT *
FROM (
    SELECT 
        *,
        -- 按用户+关卡分组，按grade降序排名，同grade排名相同
        RANK() OVER (PARTITION BY user_id, step_id ORDER BY grade DESC) AS rank_num
    FROM plays
    WHERE user_id = 24 
      AND time_started BETWEEN '2020-08-01' AND '2022-09-27'
) ranked_plays
WHERE rank_num = 1
ORDER BY time_started ASC;

方案2：同最高grade时仅保留最早的记录

如果需要在同最高grade的记录中只保留一条（比如最早开始的），使用ROW_NUMBER()替代RANK()：

SELECT *
FROM (
    SELECT 
        *,
        -- 按用户+关卡分组，先按grade降序，再按开始时间升序排序，生成唯一行号
        ROW_NUMBER() OVER (PARTITION BY user_id, step_id ORDER BY grade DESC, time_started ASC) AS row_num
    FROM plays
    WHERE user_id = 24 
      AND time_started BETWEEN '2020-08-01' AND '2022-09-27'
) numbered_plays
WHERE row_num = 1
ORDER BY time_started ASC;

性能提升关键：添加复合索引 #

创建覆盖查询所有条件的复合索引，让数据库无需回表即可获取所需数据：

CREATE INDEX idx_plays_user_time_step_grade ON plays(user_id, time_started, step_id, grade);

这个索引包含了过滤条件user_id、time_started，分组字段step_id，以及排序字段grade，能大幅减少查询的IO开销。

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