ROW_NUMBER()分页是伪优化,因需全表编号再过滤,无索引加速、内存压力大、执行计划更差;真正有效的是游标分页或索引优化。

窗口函数不能优化深分页,反而会让性能更差;真正有效的优化必须绕过 LIMIT OFFSET 本身。
为什么 ROW_NUMBER() OVER() 分页是伪优化
很多人看到 ROW_NUMBER() 就想用它“重写分页”,比如:
SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM users ) t WHERE rn BETWEEN 21 AND 30;
这看起来像分页,但 MySQL 必须先对全表计算每一行的 rn(哪怕你只要 10 行),再过滤。数据量越大,浪费越严重。
- 没有索引加速:窗口函数执行在排序之后,无法利用索引跳过前 N 行
- 内存压力大:中间结果集需缓存全部行号,可能触发磁盘临时表
- 执行计划显示
Using filesort+Using temporary,比原生LIMIT OFFSET还多一重开销
真正可用的窗口函数加分页场景
窗口函数在分页中唯一靠谱的价值,是「附带总行数」或「分区内分页」,而不是替代 LIMIT。
安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。
- 需要总数又不想两次查询?用
COUNT(*) OVER():
SELECT id, name, COUNT(*) OVER() AS total_count FROM users ORDER BY id LIMIT 10 OFFSET 20;
- 按分类取每类 Top N?用
PARTITION BY+ROW_NUMBER():
SELECT user_id, category, score
FROM (
SELECT user_id, category, score,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY score DESC) AS rn
FROM rankings
) t
WHERE rn
- 注意:
PARTITION BY后的分页是逻辑隔离的,不解决跨分区深分页问题
深分页(OFFSET > 10000)该怎么做
当用户翻到第 500 页(OFFSET 4990)时,必须放弃基于偏移量的思维。
- 用游标分页(cursor-based pagination):记住上一页最后一条的
id值
SELECT id, name, created_at FROM users WHERE id > 12345 ORDER BY id LIMIT 10;
- 要求排序字段有索引且唯一(如主键、带时间戳的联合索引)
- 前端需传回上一页末尾值,不能依赖页码参数
- 不支持跳转任意页,但响应稳定、可预测、无性能衰减
最容易被忽略的细节
即使你坚持用 LIMIT OFFSET,以下三点不处理,迟早出问题:
-
ORDER BY字段没索引 → 全表排序,性能断崖式下跌 - 排序字段存在重复值(如多个用户同一天注册)→ 分页结果错位、漏行或重复
- 写入频繁的表未加
FOR UPDATE或版本控制 → 分页过程中新插入/删除导致“幻读”式偏移
窗口函数在这里帮不上忙;能靠的只有索引设计、唯一排序组合(如 ORDER BY created_at, id)、以及业务层对游标语义的理解。










