SQL 深分页的典型优化方案

OFFSET N LIMIT M 深分页变慢是因为数据库必须扫描并跳过前N行，I/O和CPU成本随OFFSET线性增长；应优先采用游标分页、覆盖索引+延迟关联或预生成映射表等优化方案。

为什么 OFFSET N LIMIT M 在深分页时变慢

因为数据库必须扫描并跳过前 N 行，哪怕你只想要第 100 万页的 20 条数据。InnoDB 的聚簇索引顺序读 + 逐行计数机制决定了这个过程无法跳过中间数据，I/O 和 CPU 成本随 OFFSET 线性增长。

常见现象包括：查询响应从几毫秒飙升到数秒、执行计划中 type 变为 ALL 或 index、Extra 出现 using filesort 或 Using temporary。

• 即使有 created_at 索引，ORDER BY created_at LIMIT 1000000, 20 仍需定位到第 1000001 行物理位置
• count(*) 全表统计 + OFFSET 混用会触发双重全扫
• mysql 8.0+ 的 SKIP LOCKED 对深分页无加速作用，它只解决并发更新冲突

用游标分页（Cursor-based Pagination）替代 OFFSET

核心是利用排序字段的**唯一性 + 单调性**，把“我要第 N 页”转成“从上一页最后一条记录之后开始取”。要求排序字段（如 id 或 created_at）有索引且尽量避免重复值。

示例：假设按 id 降序分页，上一页最后一条是 id = 5000000，下一页查询写成：

select * FROM orders WHERE id < 5000000 ORDER BY id DESC LIMIT 20;

• 必须确保 id 是主键或有唯一索引，否则可能漏数据或重复
• 若排序字段非唯一（如 status），需组合二级条件去重：WHERE (created_at, id)
• 前端需保存上一页末尾的完整游标值，不能只传页码——这是最容易被忽略的协作点

覆盖索引 + 延迟关联减少回表

当必须用 OFFSET（比如管理后台支持跳转任意页），优先让 WHERE 和 ORDER BY 走覆盖索引，再用主键回查详情，避免大范围全字段扫描。

例如查询用户订单列表，只展示 order_id、user_id、amount、created_at：

SELECT t1.order_id, t1.user_id, t1.amount, t1.created_at FROM orders t1 INNER JOIN (     SELECT id FROM orders      WHERE status = 'paid'      ORDER BY created_at DESC      LIMIT 1000000, 20 ) t2 ON t1.id = t2.id;

• 子查询只查 id（主键），走 status + created_at 联合索引即可完成排序和偏移
• 外层用 JOIN 回查，比直接 SELECT * 少读大量非索引列
• 注意 MySQL 5.7 中子查询 LIMIT 不走索引优化，建议升级到 8.0+ 或改用物化 CTE

预生成分页映射表或使用 ES 同步

对实时性要求不高的场景（如后台报表、历史归档），可把分页位置固化下来。本质是用空间换时间，规避每次计算偏移量。

• 建一张 page_offset_map 表，每 1000 条存一条记录：page_no、min_id、max_id、total_count，定时任务维护
• 搜索类分页直接交给 elasticsearch，用 search_after 实现游标分页，性能远超 MySQL 原生方案
• 不要在 MySQL 里用 SELECT COUNT(*) 动态算总页数——加个 WHERE 条件后误差可能极大，前端显示 “共 50000 页” 本身就不准确

游标分页不是银弹：它不支持跳转任意页、无法获取总条数、对排序字段稳定性要求高。真正要落地，得先理清业务是否真的需要“第 N 页”这个概念——很多时候只是 ui 设计惯性而已。