MySQL的LIMIT M,N分页变慢的根本原因是需扫描前M+N行再丢弃前M行，导致I/O、CPU和内存开销随OFFSET增大而上升；优化方案包括游标分页、延迟关联和主键范围查询。
MySQL 的 LIMIT M,N 分页不是“直接跳到第 M 行再取 N 行”，而是先扫描并生成前 M+N 行完整结果，再丢弃前 M 行，只返回后 N 行。偏移量 M 越大，扫描的数据量就越多，I/O、CPU 和内存开销同步上升——这才是分页变慢的根本原因。
为什么 OFFSET 越大越慢？
数据库执行 SELECT * FROM t ORDER BY id LIMIT 1000000, 20 时：
- 必须先按
id排序，生成至少 1000020 条有序记录（哪怕只有索引，也要回表） - 即使
id有主键索引，优化器仍需遍历 B+ 树中前 1000000 个叶子节点位置 - 若排序字段无索引，还会触发临时文件排序，性能断崖式下跌
- 在分库分表场景下（如 ShardingSphere），SQL 会被改写为
LIMIT 0, 1000020，跨节点拉取全部中间数据再归并，带宽和延迟剧增
三种主流优化方案对比
游标分页（推荐用于时间线/列表流）
- 依赖单调、唯一、有索引的排序字段（如
created_at + id） - 第一页：
SELECT * FROM orders ORDER BY created_at DESC, id DESC LIMIT 20 - 后续页：
SELECT * FROM orders WHERE (created_at, id) < ('2024-01-01', 9999) ORDER BY created_at DESC, id DESC LIMIT 20 - 优势：毫秒级响应，不依赖偏移量；缺点：不支持跳转任意页、需客户端保存游标值
延迟关联（适合后台管理类系统）
- 先用子查询只查主键：
SELECT id FROM t ORDER BY id LIMIT 1000000, 20 - 再用主键 JOIN 原表：
SELECT t.* FROM t INNER JOIN (子查询) tmp ON t.id = tmp.id - 大幅减少回表次数，避免传输大量无效字段
覆盖索引 + 主键范围（最简落地方式）
- 假设每页 20 条，上一页最后一条
id = 876543，则下一页：SELECT * FROM t WHERE id > 876543 ORDER BY id LIMIT 20 - 要求主键连续或业务可接受“跳过少量空洞”；若用 UUID，需确保排序稳定且有索引
哪些情况容易踩坑？
不是所有优化都能直接套用：
- ORDER BY 字段不是主键且无索引 → 游标和范围查询都失效，必须先建联合索引
- 业务要求支持“跳到第 100 页” → 游标分页不适用，可考虑缓存总页数 + 小偏移量兜底，或预计算分页锚点
- 使用 JPA 的
Pageable→ 默认生成LIMIT + OFFSET，需手动改写为原生 SQL 或自定义 Repository 方法 - WHERE 条件含高过滤率字段（如
status = 'paid'）→ 应将该字段加入排序索引前缀，避免索引失效
本质不是语法问题，而是数据访问模式与存储引擎能力的匹配问题。选对策略比调优单条 SQL 更重要。
