在数据库调优这件事里，“分页查询”看起来是最不起眼的需求之一。但恰恰是这个小需求，隔三差五就能把一个线上服务拖垮。尤其当单表数据量冲到千万级甚至亿级之后，一句看似人畜无害的limit offset会把数据库资源吃满，接口响应从几十毫秒飙到几秒，页面转圈、用户投诉、报警群炸锅——我经历过很多次这种场面，所以今天想好好聊一聊大数据量下的分页查询优化。
这篇文章适合谁看？只要你负责的业务里存在大表查询、后台列表、日志检索、订单流水这类场景，并且已经隐约感觉到“再这么翻下去迟早要出事”，那这篇文章能给你一套从原理到落地的完整优化思路。我会从分页慢的根源讲起，拆解几种主流方案，给出可直接参考的SQL和参数选择过程，最后再整理一些我实际踩过的坑。
1. 分页查询为什么会在大数据量下“翻车”
1.1 先看看limit offset到底做了什么
很多人写分页就是这一句：
select * from t_order order by create_time desc limit 20 offset 100000;直觉上会觉得：数据库只需要跳过前面10万行，然后取20行，这不很快吗？但真实执行逻辑完全不是这样。MySQL在没有特殊优化的情况下，会先把前100020行全部读出来，然后丢弃前面的100000行，只把最后的20行返回给客户端。
这个行为的本质是什么？就是把每一页需要的数据，都从第一行开始重头数一遍。你翻到第1万页，它就要从头数到第20万行。页面越深，扫描的数据量越大，响应时间自然线性上涨。更麻烦的是，这10万行数据如果命中的是二级索引，还需要回主表取完整行，产生的随机IO会让磁盘忙到冒烟。
我之前碰到过一个真实场景：某业务系统的消息记录表有接近8000万行，运营同学翻到第500页左右的时候，单条查询耗时已经超过12秒，直接把连接池打满。看执行计划，扫描行数显示的是rows=250000，索引选择也没问题，但问题就出在“深度偏移”本身。
1.2 深分页的代价：回表、随机IO与内存浪费
来量化一下代价。假设分页查询是：
select * from t_order where status = 1 order by id desc limit 20 offset 200000;如果status = 1走了二级索引，那么数据库会这样做：
- 在二级索引上扫描200020个索引条目。
- 对于每个符合条件的条目，根据主键回聚簇索引取完整行。
- 取到200020行后，丢弃前200000行，返回20行。
这里每一步都在烧资源。二级索引扫描本身虽然相对快，但200020次回表是实打实的随机IO。更被动的是，很多情况下二级索引只筛选出一个很大的中间结果集，排序还得用临时文件，一旦 sort buffer 装不下，就会走磁盘归并排序，性能直线下降。
这也是为什么数据库明明配置不差，但深分页一上来CPU和IO双双爆掉。很多人以为加个索引就万事大吉，实际上索引解决的是“查找”问题，而offset深分页的本质是“重复遍历”，索引能加速遍历的第一圈，却不能跳过前面的圈数。
1.3 数据量增长后，问题会成倍放大
数据量小的时候，这种低效执行根本感知不到。10万行数据，哪怕全表扫描也就几毫秒。但到达千万级后，每多翻一页，磁盘扫描的范围就大一圈。用公式来感受一下：
单页扫描成本 ≈ 偏移量 × 单行索引读取成本 + 偏移量 × 回表成本
假设单行主键定位耗时约0.01ms，回表随机IO约0.1ms，偏移20万时：
- 扫描索引：200000 × 0.01ms = 2s
- 回表：200000 × 0.1ms = 20s
当然这个数字是极度理想化的估算法，实际并发、缓冲池命中率都会影响。但至少能看出，深分页的耗时会随偏移量线性增长，而索引命中的“前期好日子”会在某个临界点后戛然而止。
2. 分页优化的几个主赛道
2.1 核心思路：不扫无关行
所有有效的分页优化，本质上都指向同一个目标：让数据库少扫描那些最终会被丢弃的行。只要能提前把扫描范围缩小，性能问题就解决了一大半。
这里有个常见的误解，不少人一听到分页优化就想到“改缓存”，觉得把所有查询结果缓存到Redis里就不慢了。但如果列表是千人千面、实时排序的动态数据，缓存命中和失效的控制成本非常高，甚至会引入新的数据一致性问题。真正成熟的优化还是先把SQL执行路径改对，再考虑缓存来扛热点。
从工程上分，分页优化可以切入的层面至少有三个：前端交互层、应用查询层、索引结构层。很多人只盯最后一个，但往往前两层的调整成本更低、收益更明显。
2.2 前端交互：把“深分页”变成“无限滚动”
为什么用户会翻到第1万页？绝大多数业务场景里，用户根本不会真的翻那么深。要么是运营人员为了找某条历史数据，要么是爬虫/脚本在拉全量数据。
如果是面向普通用户的产品，最直接的办法是把传统的页码分页改成“下一页”模式，也就是无限滚动或 “加载更多”。这种交互下，用户脑袋里没有“第100页”的概念，只需要不断取“当前页之后”的数据。应用层拿到上一次最后一条记录的排序字段值，往下传一个游标，数据库只需要往后取一页，扫描行数始终可控。
这个方案看起来简单，但效果立竿见影。我改造过的一个内容列表就是从limit offset改成基于 id 的游标分页，接口平均耗时从1.8s降到了120ms，而且数据量再涨也没出现明显的劣化。
2.3 请求层面：别去统计那些没人关心的总量
很多后台列表设计了一个非常消耗性能的组件：总记录数。每次翻页都要执行一次select count(*)，而且这个count在大表上同样会扫大量数据。有的系统还做了多条件筛选，count 的代价甚至比取数本身还高。
如果产品同学没有强需求，可以直接去掉“共XX条”的展示，或者退而求其次只显示“已加载XX条”。实在需要精确总数，也只能通过汇总表、维护计数列等方式异步更新，而不是在请求关键路径上实时数。
2.4 存储层面：什么时候该上搜索引擎
当表本身已经复杂到索引优化都救不回来的时候，就得考虑换存储载体了。典型的信号包括：多字段任意组合筛选、模糊搜索需求增加、数据量到了亿级且还在快速膨胀。
这时候把查询能力迁移到搜索引擎或分析型数据库，是一种合理的技术演进。但要明白，这不是分页优化技巧，而是架构层面的重构，成本较高。能用索引和SQL解决的问题，没必要动架构。真正的高手是先榨干现有方案，再考虑换赛道。
3. 四种常用优化方案实操拆解
3.1 覆盖索引 + 延迟关联
先看第一种场景：列表页展示的字段很多，但筛选条件集中在少数几个字段上。比如订单表有30个字段，列表页要显示订单号、金额、状态、下单时间，筛选条件通常是状态、时间范围、店铺ID。
如果直接select *，即使筛选条件命中了复合索引，也要大批量回表。这时候可以考虑“延迟关联”：先用覆盖索引查出本页需要的主键ID，再用主键去关联原表取完整数据。
select o.* from t_order o inner join ( select id from t_order where shop_id = 123 and status = 1 and create_time >= '2024-08-01 00:00:00' order by create_time desc limit 20 offset 100000 ) tmp on o.id = tmp.id order by o.create_time desc;这里的关键点是子查询里select id只走了二级索引，不需要回表，速度会快很多。因为二级索引的叶子节点已经包含了shop_id, status, create_time, id，只要这几个字段都在索引里，这个查询就完全在索引上完成，不产生随机IO。等拿到20个id后再回表取完整行，回表次数只有20次。
这个方案的适用场景很广，尤其是筛选条件很多但都集中在某几个字段上的大宽表。需要注意的一点是：延迟关联的子查询本身仍然使用了offset 100000，所以它解决的是“回表太多”的问题，并没有完全解决“偏移越深扫描越多”的问题。在数据量特别大、翻页特别深的时候，还需要结合下一节的游标方案。
3.2 键集分页（游标分页）
键集分页是我在实际项目中用得最多的方案。它不依赖页码，而是依赖上一次返回的最后一条记录的排序字段值。假设列表按id倒序：
select id, order_no, amount, create_time from t_order where id < 1000000 order by id desc limit 20;客户端拿到这一页20条数据后，记录最后一条的id，比如是999980。请求第2页，只需要把条件改成：
where id < 999980 order by id desc limit 20;这背后的原理是：数据库利用主键索引直接定位到id = 999980的位置，然后往前扫20行，全程扫描行数只有20行，跟总数据量无关。
这个方案有几个先决条件：
- 排序字段必须是唯一的，最常用的是主键
id。 - 排序字段必须是索引的最左前缀。
- 业务上不需要“跳页”，只能一页接一页往下翻。
比如很多App的信息流、订单列表、操作日志，都满足这些条件。我之前接手过一个日志查询接口，原来用的是offset，日志到了3000万行之后每翻一页都在崩溃边缘。改成where id < xxx limit 20后，单页查询稳定在2ms左右，而且数据量继续涨，耗时几乎不波动。
如果排序字段不是id，而是create_time，那么键集分页的写法要小心。因为时间字段理论上可能重复，直接用<会导致某些记录被漏掉。业界常见做法是把排序字段扩展成(create_time, id)组合，并通过复合索引实现，后续我会在避坑环节详细展开。
3.3 子查询定位偏移点
有些产品形态就是要求保留页码，比如PC端的后台管理表格，产品经理说“第123页”一定要存在。这种场景无法改成游标，那就要想办法尽量缩小扫描范围。
一个比较实用的招数是：子查询先锁定偏移位置的起点。
select * from t_order where create_time >= ( select create_time from t_order order by create_time desc limit 100000, 1 ) order by create_time desc limit 20;这段SQL的逻辑是：先通过一个只查排序字段的轻量子查询，拿到第100001条记录对应的create_time，然后主查询通过这个值来过滤，最后再取20条。这样做的意义在于，子查询扫描的是二级索引叶子节点，不需要回表，成本远低于直接offset+ 回表。
从测试结果来看，同一个100万偏移点的查询，优化前需要回表100万次，优化后只需要扫100万条二级索引记录，耗时降低到原来的30%到50%。如果二级索引的体积也比原表小很多，差距会更大。
不过这个方案对排序字段有要求：字段值分布必须足够分散。如果create_time大量重复，一次偏移会跨过超过20条数据，那么主查询里limit 20就无法定位到正确的一页。更稳妥的做法是改用(create_time, id)组合条件，但组合条件的子查询直接写起来会比较复杂，建议直接用复合索引加双字段比较。
3.4 排序字段冗余与辅助索引设计
很多分页慢的根源，其实在表结构设计阶段就埋下了。数据量大、筛选多、排序字段不在索引里，导致每次查询都要filesort。这种情况下，优化SQL只是治标，调整索引才是治本。
以订单列表为例，常见的筛选条件是shop_id + status + create_time，展示按create_time倒序。那么一个联合索引(shop_id, status, create_time)就是很合适的。数据库可以先用前两个字段筛选，再用第三个字段排序，避免生成临时表。
如果列表还会按金额排序，比如运营要看某个店铺金额最大的订单，那可以考虑额外建一个(shop_id, amount)索引。多建索引确实会增加写入成本，但这种查询型业务表往往读多写少，索引开销完全可以接受。
这里分享一个判断技巧：用explain看执行计划时，如果出现Using filesort或Using temporary，就要高度警惕。大数据量表上的filesort是分页查询的头号敌人，它可能动用临时文件、消耗大量内存，而且没办法从任何一层缓存里获得豁免。
4. 落地选型：不同业务场景怎么选
4.1 先判断你属于哪一类分页
不是所有分页都值得改成游标，也不是所有场景都适合页码。我列一个简单的判断表，方便你做选型。
| 业务特征 | 推荐方案 | 原因 |
|---|---|---|
| 移动端信息流、日志流 | 游标分页 | 交互上无页码，天然适合按位置往下取 |
| PC管理后台，固定页码 | 覆盖索引 + 延迟关联 | 保住页码，减少回表 |
| 深翻页 + 可接受无页码 | 子查询定位偏移点 | 相比原始 offset 明显降低扫描成本 |
| 导出全量数据 | 游标分页或分批id扫描 | 游标避免深翻页，分批可控 |
| 统计分析，需要总数 | 汇总表 | 不在实时查询路径上 count |
还有一种场景：筛选条件很多，但每个条件可选择性都不高，比如一个联合索引也覆盖不过来，那再优化SQL也打不过全表扫描。要么接受查询延迟，要么走异步任务生成快照，要么上搜索引擎。不要幻想一条索引能包打天下。
4.2 结合实际业务的混合打法
拿一个我参与过的后台订单系统举例。订单表单表约5000万行，筛选字段有店铺、渠道、订单状态、下单时间，列表需要按时间倒序展示，且必须支持翻页。
第一轮改造时，我先给表加了联合索引(store_id, order_status, create_time)，并将列表查询改成延迟关联的方式。这一步直接把深分页场景下的平均响应从8s降到了2s以内，收益很明显。但业务方说2s还是太慢，尤其运营经常翻到第200页以后。
第二轮改造，我跟产品协商后把页面上“跳页输入框”去掉了，只保留上一页/下一页。然后把查询改成游标分页，排序基调固定为create_time desc, id desc。改写之后，不管翻到第几千页，单页响应都稳定在50ms以内。
这个案例想说明什么？技术方案再强，也要业务交互愿意配合。给产品讲清楚“深度翻页的代价”之后，绝大多数场景都能找到一个既保体验又保性能的方案。
4.3 一个完整改造案例的流程复盘
再分享一个更完整的复盘。某跨平台系统的操作日志表，每天新增200万行，保留90天，总行数接近1.5亿。列表页支持按操作人、模块、结果筛选，按时间倒序。
改造前接口耗时分布：
- 正常首页查询：80ms
- 翻到第20页：450ms
- 翻到第100页：3.8s
- 翻到第300页：直接超时
我做的改造步骤：
- 分析慢日志，确认瓶颈集中在深偏移后的回表和排序。
- 将
select *改成延迟关联，先取id再join原表，首轮耗时降低到1/3。 - 进一步将
offset翻页改为游标式翻页，接口不需要页码，只传上一页的最后一条日志ID和时间。SQL变为where (create_time, id) < (?, ?) order by create_time desc, id desc limit 50。 - 为这套组合排序新建复合索引
(create_time, id)，原有的单列时间索引直接删掉，减少写入和维护成本。
改造后效果：从第1页翻到第300页，耗时始终在30-60ms之间。加上应用层对相同参数的短缓存，基础查询甚至能压到20ms以内。
5. 常见问题与避坑实录
5.1 深分页时排序字段不唯一导致数据重复或缺失
这是游标分页最容易翻车的地方。假设你只按create_time倒序作为游标：
where create_time < '2024-08-01 10:00:00'如果这个时间点有1000条记录，而你一页只取50条，下一页的条件会把上一页剩下的950条全部跳过。结果就是数据大面积漏掉。
解决办法是让排序键唯一。常见做法是把主键作为排序的二级条件：
where (create_time, id) < ('2024-08-01 10:00:00', 1234567) order by create_time desc, id desc limit 50;这里用到了MySQL的“行值比较”语法，它能把多个字段的联合比较压缩到一个条件里。前提是索引必须是(create_time, id)这种复合结构。我实测过这个写法，在1亿行的表上也能稳定走Range扫描，性能完全可接受。
5.2 NULL值排序不稳定
第二个坑来自排序字段的可空设计。如果列表按某个可空字段排序，比如deleted_at，那么order by deleted_at在MySQL里默认会把NULL排在最前面。假如你用游标方案按这个字段往下翻，很容易出现死循环或者漏数据，因为NULL值之间没有唯一区分。
对策很直接：要么把可空字段设置默认值，比如deleted_at datetime not null default '1970-01-01 00:00:00'，要么干脆不要用可空字段做排序键。更推荐的还是锁定一个非空且唯一的字段，最稳的就是id。
5.3 键集分页在多个排序条件下怎么做
如果业务必须按多个条件排序，比如先按金额倒序，再按时间倒序，那游标条件会变成：
where (amount, create_time, id) < (100.00, '2024-08-01 10:00:00', 1234567) order by amount desc, create_time desc, id desc limit 20;这个方案的细节在于：所有排序字段都必须按照相同的方向排列，要么都倒序，要么都正序。如果排序方向不一致，行值比较的语义就很容易搞错，容易产生结果集错乱。所以多字段游标分页的表结构设计一定要前置参与，最好在需求阶段就和开发约定好排序组合，尽量使用同一方向排序。
5.4COUNT(*)也跑不掉
深分页优化了一段时间后，往往还会遇到另一个拦路虎：前端需要返回总条数。哪怕你已经把列表查询优化到几十毫秒，一个count(*)也可能把接口拖回两三秒。
对于大表上的精确count，唯一的出路就是维护计数缓存或汇总表。比如每天订单总量写到一张统计表，筛选条件的所有组合都提前物化到某张宽汇总表里。小数据量时这么做看着很笨，但上了千万级别，这是最踏实的做法。
如果确实做不到精确，至少把列表接口拆成两个接口：一个分页取数，一个异步查总数。让列表先把数据渲染出来，总数图标先显示“加载中”，体验上不会比精确数字差太多。
5.5 缓存分页结果值得做吗
很多人问：能不能把深分页的结果缓存到Redis？我的经验是，如果数据实时性要求不高，比如后台报表列表，缓存完全可行。但如果是用户直接看到的订单状态、消息列表这类实时性强的数据，缓存深度分页的结果会带来两个问题：一是内存占用巨大，二是删除缓存时机的控制很麻烦。
真要做缓存，建议只缓存首页和热门前几页。深分页场景本身访问量极低，不值得占用缓存空间。与其缓存深分页结果，不如用上面的游标方式让深分页也变快，这样全链路没有一致性隐患。
5.6 只在测试环境验证远远不够
最后一个坑是我强调过很多次的：分页性能问题在小数据量下根本测不出来。本地库2万条数据，怎么查都是毫秒级。一旦上线，数据量涨到千万级，深分页的问题立刻暴露。
我建议在任何分页优化改造完成后，都用脚本模拟生产环境的深度翻页压测。压测方法很简单：写一个脚本循环查询第1页、第100页、第1000页、第5000页，记录每个档位的响应时间和执行计划。如果在万行和千万行数据量下，深分页的耗时曲线都平缓，才说明优化真正达标。
结尾：优化分页，先想清楚产品要什么
踩过这么多坑之后，我个人的体会是：分页优化最怕的不是技术方案复杂，而是需求定义模糊。如果产品上非要保留第1000页的页码，那无论怎么改SQL，都不可能绕过“扫描大量已读行”的宿命。反过来，只要业务愿意把翻页模式改成“加载更多”，所有SQL层面的优化都会变得特别顺。
所以接到分页优化的需求，我第一步永远是去问：这个列表用户会翻多深？排序字段是什么？要不要精确总数？这三个问题搞清楚，方案基本就定了一半。技术实现上，优先游标分页，其次延迟关联，再不行就压索引，最后才是上汇总表和搜索引擎——这条路线我验证了很多次，绝大多数场景都能在不动架构的情况下把性能拉回来。
