MySQL 的 LIMIT offset, size 分页不是“直接跳到第 N 页”，而是从头开始扫描、排序、跳过前 offset 行，再取 size 行——偏移量越大，浪费的扫描和丢弃动作就越多。当 offset 达到几十万甚至百万级时，查询可能从毫秒级飙升至数秒，甚至超时。
为什么 OFFSET 越大越慢
执行 SELECT * FROM orders ORDER BY id LIMIT 100000, 20 时：
- MySQL 先对全表（或满足 WHERE 条件的部分）按
id排序 - 然后逐行读取，跳过前 100000 行（这些行仍需加载、排序、判断）
- 最后返回接下来的 20 行
- 整个过程实际扫描了 100020 行，其中 100000 行被丢弃，但 I/O、CPU、内存开销已产生
基于主键/时间戳的游标分页（推荐首选）
不依赖偏移量，改用上一页末尾记录的排序字段值作为“锚点”，直接定位下一页起始位置。
- 要求排序字段有索引且组合唯一（如
id自增主键，或create_time DESC, id DESC） - 第一页：
SELECT * FROM orders ORDER BY id DESC LIMIT 20 - 假设最后一条
id = 98765，第二页：SELECT * FROM orders WHERE id < 98765 ORDER BY id DESC LIMIT 20 - 支持高并发、低延迟场景（如消息流、订单列表滚动），查询耗时基本恒定
- 不支持跳页（如直接翻到第 100 页），需配合前端“上一页/下一页”逻辑使用
延迟关联 + 覆盖索引优化（适合需跳页场景）
把“找数据”和“取数据”拆开，先用索引快速拿到目标主键，再精准回表。
- 确保排序字段（如
id）在索引中，最好建复合索引覆盖常用查询列 - 优化写法：
SELECT o.* FROM orders o INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 20) tmp ON o.id = tmp.id - 子查询只走索引，扫描量大幅减少；主查询通过主键精确匹配，避免全表扫描
- 比原始语句快一个数量级以上，且仍保留
page参数语义，支持跳页
其他实用补充建议
单靠 SQL 优化还不够，结合架构和使用习惯效果更稳：
- 强制添加
ORDER BY：无排序的LIMIT查询结果不稳定，多次执行可能返回不同行 - 限制最大页码：后端校验
page * pageSize <= 100000，避免用户触发深度分页 - 冷数据归档：将历史订单、日志等迁出主表，缩小在线表体积
- 高频分页结果缓存：用 Redis 缓存第 1–50 页的 ID 列表，降低数据库压力
