百万级数据分页查询优化技巧

dbaai

百万级数据分页查询优化技巧

一、具体的问题

很多后台列表页一开始只有几千条数据，用 LIMIT 20 OFFSET 100 翻页没有任何感觉；等业务跑了一年、表涨到几百万行，"跳到第 5000 页"的工单就来了——前端点一下要等 3 到 5 秒，数据库 CPU 直接拉满，连带其它查询一起变慢。真正要回答的是一个具体问题：为什么 OFFSET 越往后越慢，以及百万级数据到底该怎么翻页才稳。

二、核心原理

1. OFFSET 的代价不是"跳过的行数"

LIMIT 20 OFFSET 1000000 在多数数据库里并不是"直接从第 1000001 行开始读"，而是先扫描并丢弃前 1000000 行，再取后面的 20 行。扫描过程要读索引、回表、排序，这一百万行的成本一分都少不了，页数越深，丢弃得越多，查询就越慢。这就是为什么第 1 页 10 毫秒、第 5000 页 5 秒——不是数据变多了，是浪费的扫描变多了。

2. 游标分页（Keyset Pagination）绕开 OFFSET

思路换成"记住上一页最后一条的主键或排序值"，下一页直接 WHERE id > last_id ORDER BY id LIMIT 20。数据库只要从上次的位置往后顺序扫 20 行，不再扫描丢弃；无论翻到第几页，耗时都和第一页几乎一样。代价是不支持"跳到第 N 页"，只能"上一页/下一页"，但这正好契合绝大多数后台场景（用户从来不会真去点第 5000 页，只是下拉加载更多）。

3. 延迟关联（Deferred Join）救场"必须按非主键排序"的场景

如果列表必须按"最新时间"或"得分"排序，而排序键不是主键，纯游标分页就不好套。这时用子查询先只取主键，再用主键回原表拿完整行：先 SELECT id FROM t ORDER BY score DESC LIMIT 20 OFFSET 1000000 拿到 20 个 id，再 SELECT * FROM t WHERE id IN (...)。因为子查询只走覆盖索引、不回表，丢弃百万行的成本大幅下降；第二步用主键等值查，毫秒级。

4. 让索引"包住"排序和过滤

分页慢常源于"排序没走索引被迫 filesort"。给 ORDER BY + WHERE 的组合建联合索引（排序键放在过滤键之后），让排序在索引内完成；再配合覆盖列，避免回表。关键是让执行计划里的 Extra 出现 Using index，而不是 Using filesort 或 Using temporary。

三、实例参考（动手步骤）

以一张 500 万行的订单表 orders，按 created_at DESC 倒序翻页为例，对比两种写法。

1) 慢写法（深翻页必慢）：

2. SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 1000000;

复制代码

用 EXPLAIN 看：type 是 index/ALL，rows 接近百万，Extra 出现 Using filesort。

2) 游标写法（推荐，需业务上能拿到"上一页最后一条的 created_at 和 id"）：

2. SELECT * FROM orders
3. WHERE (created_at < '2026-08-01 09:00:00')
4.    OR (created_at = '2026-08-01 09:00:00' AND id < 88231)
5. ORDER BY created_at DESC, id DESC
6. LIMIT 20;

复制代码

这里用 (created_at, id) 联合索引，数据库顺序定位到上次断点，扫描量恒定为 20 行。

3) 延迟关联写法（按时间排序且要跳页时）：

2. SELECT o.* FROM orders o
3. JOIN (SELECT id FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 1000000) t
4.   ON o.id = t.id
5. ORDER BY o.created_at DESC;

复制代码

子查询只扫索引取 id，外层用主键回表，实测比纯 OFFSET 快一个数量级。

4) 前后对比：同一张 500 万行表，OFFSET 1000000 从约 4200ms 降到游标/延迟关联的约 30ms。验证办法是 EXPLAIN 里 rows 从百万级变成 20 附近，且 Extra 不再有 Using filesort。

四、实操检查清单

• 列表是否真的需要"任意跳页"？若只需上下翻，优先用游标分页（keyset），彻底弃用 OFFSET。
  
• 游标断点是否唯一且有序？用 (排序键, 主键) 双列做游标，避免同分数据重复或漏读。
  
• 排序键上有没有联合索引？ORDER BY + WHERE 的组合要能走覆盖索引，消除 filesort。
  
• 是否误用 SELECT *？分页只取需要的列，或先取主键再关联，减少回表代价。
  
• 深翻页是否命中"延迟关联"？必须按非主键排序又不能改游标时，用子查询先拿主键再 JOIN。
  
• 总数统计 COUNT(*) 是否成了瓶颈？列表页可改为"估算总数"或异步缓存，别每次翻页都全表数。
  
• 前端是否限制了最大页码？把"跳到第 N 页"的上限压到几百页内，超出走搜索而非盲翻。
  
• 落库后是否用 EXPLAIN 复核？确认 rows 和 Extra 符合预期，再上生产。