订单表翻页到 50 万条记录要 6.3 秒 游标分页怎么写

现象

订单列表翻到第 2 万页时接口要 6.3 秒,LIMIT 500000,20 越往后越慢,前面几页却很快。数据库 CPU 在翻页瞬间冲高,同时在线的人一多,其他接口也跟着变慢。

原因

LIMIT offset 的写法要求先扫描并丢弃前 offset 行,offset 越大成本越高;排序字段没有索引,产生临时表和文件排序;SELECT 带大字段,丢弃的行也要回表;并发翻页时重复扫描同一批数据,缓存根本帮不上忙。更糟的是,深页码往往来自爬虫或误操作,业务上并不需要。

解决步骤

第一步,确认扫描行数。用 EXPLAIN 看 rows 与 Extra,确认是全表扫描还是文件排序,再决定用游标还是覆盖索引。

EXPLAIN SELECT id, order_no, amount FROM orders
  WHERE status = 1 ORDER BY id DESC LIMIT 500000, 20;
SELECT id FROM orders WHERE status = 1 AND id < 500000
  ORDER BY id DESC LIMIT 20;

第二步,改成游标分页。前端传上一页最后一条的 id,SQL 用 WHERE id < last_id ORDER BY id DESC LIMIT 20,扫描行数固定为 20 行,不再随页码增长。

第三步,补索引并限制深翻。建立 (status, id) 联合索引让过滤与排序走同一棵树;产品上限制最大页数或强制先筛选再查询,减少无意义的深翻。

验证

深翻页耗时从 6.3 秒降到 30ms,扫描行数从 50 万降到 20,数据库 CPU 峰值消失,翻页体验前后一致,其他接口也不再被拖慢。

小结

大表分页慢在 offset 扫描。能用游标就别用 offset,配合联合索引和产品限制,深翻页也能稳定在几十毫秒,别让爬虫把库拖垮。

您可以还会对下面的文章感兴趣: