SQL 如何实现“Top-N per group”且性能最优的写法对比

ROW_NUMBER()是最通用可控的Top-N写法，需确保PARTITION BY与ORDER BY列有联合索引；MySQL 5.7-需子查询（性能差）；PostgreSQL可用DISTINCT ON（Top-1极速）或LATERAL（大分组高效）。

用 ROW_NUMBER() 窗口函数是最通用且可控的写法

绝大多数场景下，ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY sort_col DESC) 是首选。它逻辑清晰、语义明确，且所有主流数据库（PostgreSQL、SQL Server、Oracle、MySQL 8.0+、Trino、BigQuery）都支持。

关键点在于：必须确保 PARTITION BY 列有索引（尤其是和 ORDER BY 列组合），否则窗口函数会触发全表扫描 + 排序，性能急剧下降。

• 避免在 ORDER BY 中使用函数或表达式（如 UPPER(name)），否则索引大概率失效
• 如果只要 Top-1，且业务允许“任意一条”（不严格要求排序稳定），MAX() / MIN() + GROUP BY 可能更快，但无法带回其他字段
• ROW_NUMBER() 保证每行唯一编号，适合需要精确 N 条的场景；RANK() 和 DENSE_RANK() 在有并列时行为不同，慎用

MySQL 5.7 或更老版本只能靠相关子查询或自连接

这些版本不支持窗口函数，ROW_NUMBER() 不可用。最常见写法是用相关子查询统计“本组内有多少条记录比当前行更优”，再过滤数量 ≤ N：

SELECT t1.* FROM orders t1
WHERE (
  SELECT COUNT(*) FROM orders t2
  WHERE t2.customer_id = t1.customer_id
    AND t2.order_date > t1.order_date
) < 3;

这个写法看似直观，但性能极差：对每行都要执行一次子查询，复杂度接近 O(n²)，数据量一过万就明顿。

• 必须给 (customer_id, order_date) 建联合索引，否则子查询无法走索引范围扫描
• 若 N 较小（如 Top-3），可改用 LIMIT + UNION ALL 模拟（每个 group 单独查再合并），但 SQL 冗长且 group 数多时不现实
• 升级到 MySQL 8.0+ 是根本解法——窗口函数性能通常比子查询高一个数量级

PostgreSQL 中 DISTINCT ON 是 Top-1 的极速替代方案

当只需要每个分组的第一条（按某字段排序后取第一条），DISTINCT ON 比 ROW_NUMBER() 更轻量，执行计划常省去窗口排序步骤：

一站式AI应用开发和部署工具

SELECT DISTINCT ON (customer_id) *
FROM orders
ORDER BY customer_id, order_date DESC;

它的限制很明确：ORDER BY 必须以 DISTINCT ON 的列开头，后续字段决定“第一”的选取顺序。

• 仅适用于 Top-1；想取 Top-N 就得退回 ROW_NUMBER()
• 依赖 (customer_id, order_date) 的索引，否则 ORDER BY 仍需全局排序
• 在高并发、小结果集（如每组最多几条）场景下，实测响应常快 30%~50%

大表分页取 Top-N 时，LATERAL 关联比窗口函数更省内存

当 group 数量极大（比如百万级用户），而每组只取前几条，用 ROW_NUMBER() 会先为全表打标再过滤，内存和临时空间压力大。PostgreSQL 和 SQL Server 支持 LATERAL（或 APPLY），让数据库“按需拉取”：

SELECT u.*, o.*
FROM users u
CROSS JOIN LATERAL (
  SELECT * FROM orders o2
  WHERE o2.customer_id = u.id
  ORDER BY o2.order_date DESC
  LIMIT 3
) o;

这种写法本质是“对每个 user 执行一次带 LIMIT 的子查询”，避免了全表打标，IO 更局部，尤其适合 SSD 环境。

• 必须确保 orders(customer_id, order_date) 有高效索引
• group 总数不宜太少（否则 LATERAL 的调度开销反而凸显）；建议 group 数 ≥ 10k 时重点考虑
• MySQL 目前不支持 LATERAL，MariaDB 10.11+ 已支持，但语法略有差异

实际选型时，别只盯着语法“看起来多简洁”。真正卡住性能的，往往是索引缺失、数据倾斜（某个 group 占据 90% 行数）、或误把 RANK() 当 ROW_NUMBER() 用导致结果条数远超预期。先看执行计划里的 WindowAgg 或 Subquery Scan 节点是否走了索引，再调写法。