014-blog

Appearance

MySQL 深分页为什么慢？从 LIMIT 100000,20 到游标分页的实战改造 ​

这篇写给踩过坑的人：页面翻到第 5000 页突然卡死，数据库 CPU 还飙上去了。

先说人话结论 ​

LIMIT 100000, 20 慢，不是因为 MySQL “不会分页”，而是因为它要先跳过前 100000 行，再拿后 20 行。
偏移量越大，浪费越多。

如果是线上列表页，优先改成游标分页（也叫 Keyset Pagination）。

一、为什么深分页慢 ​

你写了这条 SQL：

sql
SELECT id, user_id, amount, create_time
FROM orders
WHERE status = 1
ORDER BY create_time DESC
LIMIT 100000, 20;

看起来只拿 20 条，但数据库实际工作量是：

  1. 先按条件和排序扫描大量记录
  2. 丢掉前 100000 条
  3. 返回最后 20 条

如果还伴随回表、filesort，成本会更高。

二、先给可落地方案：游标分页 ​

核心思路：不用 offset，改用“上次最后一条记录”作为下一页起点。

1) 第一页 ​

sql
SELECT id, user_id, amount, create_time
FROM orders
WHERE status = 1
ORDER BY create_time DESC, id DESC
LIMIT 20;

记住本页最后一条的 (create_time, id)，比如：

  • last_create_time = '2026-03-30 10:15:30'
  • last_id = 9527

2) 下一页 ​

sql
SELECT id, user_id, amount, create_time
FROM orders
WHERE status = 1
  AND (
    create_time < '2026-03-30 10:15:30'
    OR (create_time = '2026-03-30 10:15:30' AND id < 9527)
  )
ORDER BY create_time DESC, id DESC
LIMIT 20;

这样每一页都从“游标位置”继续扫，不需要跳过几十万行。

三、索引怎么配 ​

这个场景建议索引：

sql
CREATE INDEX idx_orders_status_ctime_id
ON orders(status, create_time DESC, id DESC);

为什么是这三个列：

  1. status 是过滤条件
  2. create_time, id 是稳定排序键（避免同一时间戳顺序抖动）
  3. 能支撑游标条件与排序方向

四、什么时候还能用 LIMIT offset ​

也不是说 offset 全不能用，下面两类可以接受：

  1. 后台管理页，数据量小（几千到几万）
  2. 用户只看前几页（比如 1~5 页）

但如果是 feed 流、订单流水、日志列表，建议尽早上游标分页。

五、一个改造清单（按这个做基本不会跑偏） ​

  1. 先确认排序字段稳定（最好 create_time + id）
  2. 给查询补复合索引
  3. 前端/接口协议增加 cursor 字段
  4. 第一次请求不带 cursor，后续请求带上页末游标
  5. 返回 hasMore，不要返回总页数（游标分页通常不强调跳页）
  6. 观测慢日志和 P95/P99，确认改造收益

六、常见坑 ​

坑 1：只按 create_time 排序 ​

如果多条记录时间一样，翻页可能重复或漏数据。
解决：加第二排序键 id。

坑 2：游标字段可变 ​

比如用 update_time 做游标，数据一更新就会“漂移”。
解决：优先用不变且单调的键（如创建时间 + 主键）。

坑 3：还想“跳到第 500 页” ​

游标分页擅长“下一页”，不擅长随机跳页。
如果业务必须跳页，可以保留一个受限的 offset 查询作为兜底。

最后总结 ​

深分页慢的本质是“跳过成本太高”。
把 LIMIT offset 换成游标分页，通常能稳定把大偏移分页从“高风险慢查询”变成“可控常规查询”。

一句话：列表越大，越要尽早改游标分页。