用窗口函数实现排行榜：比自连接快一个数量级

排行榜、Top N、分组取最新一条——这类需求用窗口函数写起来最舒服。但老代码里常见的是自连接或子查询写法，数据量一上来就慢到不可用。

一个典型场景

每门课程要取分数最高的前 3 名学生。自连接写法：

SELECT s.*
FROM scores s
WHERE (SELECT COUNT(*) FROM scores t
       WHERE t.course_id = s.course_id AND t.score > s.score) < 3;

逻辑对，但复杂度是 O(n²)。一万行数据还能跑，一百万行直接卡死。

窗口函数写法

SELECT course_id, student_id, score, rn
FROM (
    SELECT course_id, student_id, score,
           ROW_NUMBER() OVER (
               PARTITION BY course_id
               ORDER BY score DESC, student_id ASC
           ) AS rn
    FROM scores
) t
WHERE rn <= 3;

PARTITION BY 相当于分组，ORDER BY 决定组内排序，ROW_NUMBER() 给出序号。一次扫描完成，配合索引可到 O(n log n)。

三种排名函数的区别

| 函数 | 并列时行为 | 适用场景 | |—|—|—| | ROW_NUMBER() | 强行区分，序号不重复 | 取前 N 条，不在乎并列 | | RANK() | 并列同名次，后续跳号 | 体育排名（1,2,2,4） | | DENSE_RANK() | 并列同名次，后续不跳号 | 奖项分级（一二三等奖） |

ORDER BY 一定要加唯一列做兜底，否则分数相同时 ROW_NUMBER() 的分配是不确定的，每次查询结果可能不同——这种不确定性在测试里很难复现。

其他常用窗口

取每组最新一条（比如每个用户的最后订单）：

SELECT * FROM (
    SELECT o.*, ROW_NUMBER() OVER (
               PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders o
) t WHERE rn = 1;

累计求和（月度累计销售额）：

SELECT month, amount,
       SUM(amount) OVER (ORDER BY month
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum
FROM monthly_sales;

移动平均（7 日平滑）：

SELECT d, qps,
       AVG(qps) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg7
FROM metrics;

索引建议

窗口函数的性能取决于排序。给 (course_id, score DESC, student_id) 建联合索引，MySQL 可以直接按索引顺序遍历，避免额外 filesort。用 EXPLAIN 确认没有出现 Using temporary; Using filesort。

MySQL 版本提醒

窗口函数要求 MySQL 8.0+（MariaDB 10.2+）。如果还在 5.7，要么升级，要么改用”关联子查询取最大值”的折中方案，但要有心理准备——数据量大了照样慢。