做后端开发的朋友多半都有过这种经历接口在测试环境翻得飞快一到线上数据量上来翻到几十页之后明显卡顿响应时间从几十毫秒直接飙到几百毫秒甚至几秒。打开慢查询日志一看十有八九是LIMIT OFFSET在捣鬼。MySQL 分页场景变慢几乎都是深分页引起的也就是 OFFSET 值非常大的情况。这篇文章我要把LIMIT OFFSET为什么慢这件事一次性讲透包括执行计划怎么读、成本到底贵在哪、有哪些真正有效的优化手段以及我踩过的那些坑。先给一个结论性判断LIMIT 10 OFFSET 5000和LIMIT 10 OFFSET 500000的执行时间可能会差几十倍但返回的结果行数是一样的。也就是说慢的不是“取数据”而是“找数据”的过程。下面我从原理开始层层拆解。1. 慢的根源OFFSET 不是“跳过”而是“扫描丢掉”1.1 深分页的代价很多人第一次听到OFFSET的原理时会觉得反直觉数据库怎么知道要跳过前面 50000 行它根本不知道也不能像翻书一样直接翻到第 50000 行。InnoDB 里数据是按 B 树组织的物理存储上并不保证逻辑顺序连续。要让第 50001 行出现MySQL 只能老老实实从满足条件的第一行开始数数到第 50000 行时把头 50000 行全部丢掉再把后面 10 行返回给客户端。这个过程可以概括成三个字扫、排、丢。扫描所有候选行按ORDER BY排序然后丢掉 OFFSET 指定数量的行。深分页慢主要是两个成本叠加一个是排序成本一个是无效扫描成本。OFFSET 越大需要扫描和丢弃的行就越多但真正有用的始终只有最后一小段。对比一下 LIMIT 优化LIMIT 10理论上最多只需要扫描 10 行就能停下前提是走对了索引。但是如果有了 OFFSET这个“提前停止”的能力就废了必须把 OFFSET LIMIT 这么多行全部处理完才能确定最终结果。所以判断一条分页 SQL 是否健康最直接的指标就是 OFFSET 值本身。1.2 一个具体的成本估算我用一个订单表举例表结构大致是这样的CREATE TABLE orders ( id bigint NOT NULL AUTO_INCREMENT, user_id bigint NOT NULL, order_no varchar(64) NOT NULL, amount decimal(12,2) NOT NULL, status tinyint NOT NULL DEFAULT 0, created_at datetime NOT NULL, PRIMARY KEY (id), KEY idx_user_id (user_id), KEY idx_created_at (created_at) ) ENGINEInnoDB;假设表里有 200 万行执行这条常见的分页查询SELECT id, order_no, amount, status, created_at FROM orders WHERE status 1 ORDER BY created_at DESC LIMIT 10 OFFSET 200000;这条 SQL 的扫描过程大致是InnoDB 根据优化器选择的索引进行扫描逐行判断status 1命中的行进入排序缓冲排序完成后跳过前 200000 条结果再取出 10 条。如果status 1的行占全表的 50%那它大概要扫过 40 万行左右才能收集到 20 万条符合条件的结果。这个量级的扫描和排序在普通服务器上跑几百毫秒到几秒都很正常。更大的问题在回表。如果走的是二级索引idx_created_at二级索引里只保存索引字段和主键值查询要求返回order_no、amount、status这些列MySQL 必须拿着主键去聚簇索引里查完整行。这个过程叫回表。深分页场景下每一条候选记录都可能触发一次主键查找随机 I/O 一多性能立刻崩盘。1.3 回表与随机 I/O 的影响回表为什么比想象中更伤性能这里得说清楚 InnoDB 的存储方式。聚簇索引的叶子节点就是整行数据数据按照主键顺序排列二级索引的叶子节点只索引列值和主键。两种索引的 B 树不是同一棵回表意味着在索引树和主键树之间来回跳。如果主键是自增的回表访问的页面相对集中因为相邻插入的数据大概率在相邻页面上。但如果是按created_at倒序分页命中的主键分布就没那么规整了每回表一次都可能落在不同的数据页上。传统的机械硬盘最怕这种随机 I/OSSD 虽然好很多但大量随机小读仍然会占用 InnoDB buffer pool 的带宽和 CPU 资源。所以深分页慢的完整链条是OFFSET 过大导致扫描行数暴涨扫描行数暴涨导致排序和回表量同步放大回表引发的随机 I/O 进一步放大了延迟。想优化就得从打断这条链条入手。2. 从执行计划开始定位慢在哪2.1 Explain 输出怎么读遇到一条分页慢查询第一步永远是看执行计划。直接在生产库上小心执行EXPLAIN SELECT id, order_no, amount, status, created_at FROM orders WHERE status 1 ORDER BY created_at DESC LIMIT 10 OFFSET 200000;重点看几个字段。type如果是ALL或者index意味着全表扫描或者全索引扫描分页性能基本没救ref、eq_ref、range才是比较理想的访问方式。key告诉我们实际用了哪个索引如果显示了idx_created_at说明排序走了索引但别高兴太早还得看rows。rows是优化器估算的需要扫描的行数深分页场景下这个数字通常会远远大于 OFFSET。比如执行计划里估算的rows是 400000那就意味着 InnoDB 实际要扫这么多行。再看Extra列如果出现Using filesort说明排序没有走索引MySQL 要把数据加载到排序缓冲区量一大就会产生临时文件额外增加磁盘开销。真实经验是排查分页慢不能只看EXPLAIN还要把 LIMIT 条件拆开来测。分别执行LIMIT 10和LIMIT 10 OFFSET 200000对比耗时和rows差距立刻暴露。2.2 分析 rows 与回表的映射关系执行计划里的rows只是估算值实际扫描行数可以通过SHOW SESSION STATUS LIKE Handler_read%和Innodb_rows_read看到。这是排查深分页问题非常关键的一步。以刚才那条 SQL 为例在会话里开启状态统计FLUSH STATUS; SELECT id, order_no, amount, status, created_at FROM orders WHERE status 1 ORDER BY created_at DESC LIMIT 10 OFFSET 200000; SHOW STATUS LIKE Innodb_rows_read;如果Innodb_rows_read的值是 400000 左右而最终只返回 10 行说明有 399990 行的读取完全是无效消耗。这时候再去对比走idx_user_id或者idx_created_at的读取量就能直观感受到哪个索引更合适。另外一个容易埋坑的点是WHERE status 1这种过滤条件。如果status的区分度很低优化器很可能认为扫描二级索引再回表不划算干脆选择全表扫描。这种情况下的分页慢OFFSET 只是加剧因素根本问题在索引选择策略上。把过滤字段和排序字段放进同一个复合索引才是治本的方向。2.3 监控到线程状态再判断执行计划分析不出问题时可以看一下 MySQL 当时的线程状态SHOW FULL PROCESSLIST;深分页 SQL 常见的 State 是Sending data或Sorting result。Sending data不代表网络传输慢而是 InnoDB 正在扫描和读取行这个阶段通常占整个查询时间的大头Sorting result则说明 filesort 在进行。如果一个查询长时间停留在Sorting result排序字段和索引不匹配的问题就基本实锤了。顺带说一句慢查询日志的Rows_examined字段非常有用。开启慢日志后能看到 MySQL 为这条 SQL 检查了多少行。Rows_examined远超返回行数说明无效扫描严重。我把这个指标当作分页健康度的晴雨表如果Rows_examined / 返回行数超过 50就该考虑优化了。2.4 常见伪优化现象排查过程中我见过不少“看似优化了其实没用”的操作简单列几个方便大家避坑。第一种是盲目加索引。给status加单列索引然后再跑同样的 LIMIT 分页执行计划里 index 是idx_status但rows仍然很高。因为单列索引只能帮你过滤 status排序和 OFFSET 扫描还是要大范围遍历。第二种是把 OFFSET 调小比如改成每页 5 条表面上看查询快了但用户翻页次数翻倍总扫描量并没有减少只是把压力分摊到了多次请求上整体效率反而更低。第三种是把LIMIT 10 OFFSET 200000换成LIMIT 199990, 10这种写法没有任何区别只是换汤不换药不会改变执行计划。3. 几套可落地的优化方案3.1 延迟关联先索引扫描再回表取数延迟关联的思想很简单与其拿着 SELECT 的完整列去深分页不如先把最后一页的主键查出来再用主键去关联取完整数据。核心目标是把回表动作压缩到 LIMIT 范围内的少量行上而不是让回表发生在 OFFSET 区间内的几十万行上。具体写法如下SELECT a.id, a.order_no, a.amount, a.status, a.created_at FROM orders a INNER JOIN ( SELECT id FROM orders WHERE status 1 ORDER BY created_at DESC LIMIT 10 OFFSET 200000 ) t ON a.id t.id ORDER BY a.created_at DESC;这里的子查询只访问二级索引索引里存了id和created_at排序也可以直接在索引上进行不需要回表。子查询筛选出 10 个主键后再一次性回表拿到完整行回表次数从几十万降到 10 次性能提升往往非常夸张。这个方案保留了 OFFSET 分页的语义对现有的分页器改动最小很多基于 MyBatis-Plus 或者手写 LIMIT 的老项目只需要把业务 SQL 改造成这种 JOIN 写法不需要改服务端的分页逻辑落地成本很低。我实测过一个 500 万行的业务表第 500 页的查询从 1.8 秒降到 40 毫秒左右。虽然延迟关联能大幅缓解深分页问题但只要 OFFSET 还在子查询内部依然要扫描和丢弃几十万行所以它治疗的是“回表 排序”的症状不能完全删除扫描成本。3.2 游标分页用定位点替换 OFFSET真正需要根治深分页时我会优先建议游标分页也叫 keyset pagination 或者 seek method。核心思路是彻底摒弃 OFFSET改用一个定位条件来锚定上一页的最后一条记录。比如按created_at倒序分页用户请求第二页时不再写OFFSET 10而是把第一页最后一条记录的created_at和id传回来写成SELECT id, order_no, amount, status, created_at FROM orders WHERE status 1 AND (created_at 2024-05-01 12:00:00 OR (created_at 2024-05-01 12:00:00 AND id 12345)) ORDER BY created_at DESC, id DESC LIMIT 10;这里的(created_at, id)组合条件就是一个游标。下一页的起点是上一页最后一条记录查询从起点开始向后扫描每页只读取 10 行时间复杂度稳定在 O(10)。不管翻到第几页性能几乎不变这才是深分页场景真正的治本方案。这里有个细节排序字段必须唯一且稳定。如果排序字段有大量重复值就会出现分页漏数据的问题所以通常会把主键作为次级排序条件确保游标能唯一定位。排序字段不唯一时比如只按created_at排序并带上LIMIT 10就不太适合直接游标分页必须加上id作为第二排序键。游标分页还有一个优势是并发场景下更稳定。用户如果正在按顺序翻页期间新增了一条记录OFFSET 分页会导致整页数据错位用户可能看到重复记录游标分页基于锚点查询一般不会受到新增数据的影响因为新数据通常在游标位置之外。代价是分页逻辑要改。前端不能再回传页码要回传 lastId 或者时间戳服务端的接口参数也得跟着调整。如果产品硬要保留“跳到第 N 页”的功能游标方案就行不通了这种情况我会建议限定最大翻页深度比如只允许访问前 100 页超过部分走搜索或条件筛选。3.3 分段分页与预处理有些场景既不想改接口协议又确实有深分页需求可以试试分段分页的思路。比如把数据按时间切片每隔一段时间生成一张汇总表把位置映射关系预处理好。用户请求第 N 页时先查映射表拿到该页对应的起始主键再直接走主键查询返回数据。这种方案本质上是用额外的存储成本换取查询速度。适合那些数据量大、翻页模式固定、数据很少变动的业务比如历史报表、审计日志。因为汇总表需要定时维护带来一定复杂度所以我建议只有在深分页频率很高、游标改造成本过大的场景才考虑。另一种折中方案是把深分页请求改造成定期导出或异步任务。用户想看第 100 万条附近的记录与其在线跑不如预先把结果集导出成文件让用户去文件里搜索。这不是数据库层面的优化但在很多 To B 系统里反而是最务实的做法。3.4 方案选型对比优化方案性能提升幅度改动成本适用场景缺点延迟关联中到大低改造 SQL 即可OFFSET 必须保留的老系统子查询内部仍会扫描 OFFSET 行不够彻底游标分页大翻页深度不再影响性能中接口协议和前端逻辑要改移动端列表、Feed 流等按序翻页场景不支持随机跳页分段分页中到大高需要维护映射表数据冻结、翻页模式固定的场景数据实时性差限制最大翻页深度无低后台管理系统只是约束不是性能优化4. 实操中的避坑清单与故障排查4.1 排序字段是最大的陷阱分页 SQL 里ORDER BY的性能权重经常被低估。如果排序字段没有索引MySQL 会把满足 WHERE 条件的所有行都放进filesort这个过程在数据量大时可不止翻页变慢这么简单还可能拖垮服务器内存甚至出现临时文件占满磁盘的情况。我见过一个真实案例业务方在订单列表上支持按amount排序分页而amount没有索引。平时数据量小没感觉双十一之后订单量暴增线上直接出现大查询把临时目录写爆。后来把amount加入复合索引情况立刻缓解。给排序字段建索引时要注意方向。索引定义时默认是升序如果查询经常按降序排列MySQL 8.0 支持降序索引能避免额外的反向扫描开销。如果复合索引里既有过滤字段又有排序字段顺序也很关键基本原则是等值过滤字段放前面排序字段放后面比如KEY idx_status_created (status, created_at DESC)。4.2 分页总数计算的坑LIMIT OFFSET 慢只是问题的一角分页查询往往还伴随一个COUNT(*)操作。前端要显示总页数和总记录数后端就得多执行一条SELECT COUNT(*) FROM orders WHERE status 1;这条 COUNT 在数据量大时也会扫描大量行虽然不用排序和回表但同样可能很慢。如果接口里先跑 COUNT 再跑分页 SQL慢就直接翻倍。优化思路有三个方向。一是把 COUNT 结果缓存起来定时刷新或按条件维度缓存允许一定时间内的数据不精确。二是用近似值替代精确值很多后台管理系统根本不需要精确总数显示“大约 12 万条”完全够用。三是把 COUNT 和查询并行化但并行只能减少响应时间不能减少数据库压力。我个人比较推荐的做法是如果产品允许直接用游标分页替代传统分页天然不需要总条数。列表页做成“上拉加载更多”的形式就不存在 COUNT 问题了。4.3 索引失效的典型场景分页 SQL 里最常见的索引失效写法是对索引字段做函数运算。比如WHERE DATE_FORMAT(created_at, %Y-%m-%d) 2024-05-01 ORDER BY created_at DESC LIMIT 10;这种写法会让created_at索引失去作用。正确的写法是范围条件WHERE created_at 2024-05-01 00:00:00 AND created_at 2024-05-02 00:00:00另外隐式类型转换也很容易踩雷。比如order_no是 varchar 类型查询时用了整数参数MySQL 可能无法使用索引。字段类型对不上时不仅分页慢整个查询都会慢。还有一个被忽略的点是ORDER BY字段和WHERE字段属于不同索引时优化器只能二选一大概率走上一个索引后做 filesort。复合索引的设计是解决这类问题的唯一出路单列索引堆得再多也没用。4.4 常见问题速查表现象可能原因排查方式处理建议OFFSET 越大越慢深分页导致扫描行数增加EXPLAIN 看 rows状态变量看 Innodb_rows_read游标分页或延迟关联排序字段没有索引Extra 出现 Using filesortEXPLAIN 看 Extra 列为排序字段建索引调整复合索引翻页出现重复或漏数据排序字段不唯一新增数据导致偏移对比两次翻页的结果增加主键作为第二排序键LIMIT 100000, 10 长时间卡住OFFET 扫描 回表大查询线程 State 为 Sorting result限制最大页数或改用游标COUNT(*) 同样很慢满足条件行数过多单独执行 COUNT 耗时对比使用近似计数或换加载更多模式索引字段被函数包裹索引失效EXPLAIN 看 key 字段为 NULL改写为范围查询5. 写在最后的一点心得做了这么多年业务系统我的体会是分页慢的问题很少是单点问题它往往是索引设计、排序逻辑、业务交互模式共同叠加的结果。OFFSET 分页最大的问题不是“慢”本身而是慢得毫无规律数据量一涨性能衰退几乎是线性甚至超线性的这种体验非常糟糕。如果是新项目我基本会直接使用游标分页要求前端传上一页的最后一条记录标识后端用定位条件查询。如果是老项目又不想大改接口延迟关联是比较合适的过渡方案。至于 LIMIT OFFSET我的态度是它并没有完全失去意义——数据量小、翻页深度浅、后台管理类页面用起来既简单又直观但一旦预判数据会快速增长最好还是提前把深分页的优化方案规划进去。最后再分享一个小技巧给所有分页接口加一个最大 OFFSET 保护超过阈值直接返回错误提示引导用户使用筛选条件。这个保护可能只要几行代码却能防住绝大多数深分页把数据库打挂的突发情况。别等线上出问题再想着优化分页性能优化永远是越早做越省事。