SQL如何高效提取每组首条记录 ROW_NUMBER优化策略-SQL-PHP中文网

row_number()必须配合partition by和order by才能实现分组取首条，单独使用仅生成全局序号；正确写法需在子查询中指定分组与排序，并将where条件下推至内层以提升性能。

sql如何高效提取每组首条记录 row_number优化策略

ROW_NUMBER() 必须配合 PARTITION BY 和 ORDER BY 才能分组取首条

单独写 ROW_NUMBER() 不会自动按某列分组，不加 PARTITION BY 就变成全表编号，根本不是“每组首条”。常见错误是只写 ORDER BY，结果得到的是全局序号，一查就发现所有记录的 rn 都是 1 到 N 连续值，完全没分组。

正确做法是明确指定分组字段和排序逻辑：

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

PARTITION BY user_id 表示“每个 user_id 算一组”
ORDER BY created_at DESC 决定哪条是“首条”——这里取最新订单；若要最早，改用 ASC
外层 WHERE rn = 1 是必须的，窗口函数不能直接在 WHERE 中过滤

ORDER BY 字段缺失索引会导致全表扫描

数据库执行 ROW_NUMBER() 时，如果 ORDER BY 的字段没索引，尤其是大表上，性能会断崖式下跌。不是慢一点，是可能从毫秒级跳到几秒甚至超时。

比如按 created_at 排序却没建索引，MySQL 或 PostgreSQL 都得临时排序整个分区数据，内存不够还会落盘，IO 成瓶颈。

检查执行计划：看是否出现 Using filesort（MySQL）或 Sort 节点（PostgreSQL）
复合索引更有效：对 PARTITION BY a ORDER BY b，优先建 (a, b) 联合索引
注意 NULL 处理：如果 ORDER BY 字段允许 NULL，且业务要求 NULL 排最后，显式写 ORDER BY col DESC NULLS LAST（PostgreSQL 支持，MySQL 8.0+ 可用 IS NULL 模拟）

替代方案：用 DISTINCT ON（PostgreSQL）或 FIRST_VALUE（通用但有陷阱）

DISTINCT ON 在 PostgreSQL 里是更轻量的写法，语义清晰、通常更快，但它只存在于 PostgreSQL，跨数据库不可移植。

SELECT DISTINCT ON (user_id) *
FROM orders
ORDER BY user_id, created_at DESC;

而 FIRST_VALUE() 看似能避免子查询，但容易误用：

FIRST_VALUE(id) OVER (PARTITION BY user_id ORDER BY created_at DESC) 返回的是 id 值，不是整行，没法直接代替整行筛选
想拿整行必须配合 ROW_NUMBER() 或用 LATERAL / CROSS APPLY，反而更复杂
某些场景下 FIRST_VALUE() + GROUP BY 会因聚合逻辑错乱，尤其当 SELECT 列没全在 GROUP BY 里时，MySQL 5.7+ 严格模式直接报错 Expression #1 of SELECT list is not in GROUP BY clause

WHERE 条件必须下推到子查询内层，否则白优化

很多人把过滤条件写在外层 WHERE，比如先算出所有分组首条，再筛 status = 'paid'，结果是先生成百万行中间结果，再过滤——索引失效、内存爆满。

真正高效的写法是把业务约束提前放进子查询：

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY user_id ORDER BY created_at DESC
  ) AS rn
  FROM orders 
  WHERE status = 'paid'  -- ✅ 提前过滤
) t WHERE rn = 1;