我最早意识到分页查询不是“写个 LIMIT 就完事”的东西是在维护一个订单后台列表的时候。那张表其实不算大几百万行接口就是很普通的列表查询前几十页都很快结果用户翻到第 200 页直接转圈圈接口超时。一开始我以为就是缺索引翻来覆去加了好几个索引也没用。后来把执行计划拉出来才看明白问题恰恰出在“分页查询”的实现方式上数据库并不知道你要的是“第 200 页”它只知道要把前 20000 条记录全扫一遍然后丢掉前面那些。这篇文章会把分页查询这件事从头到尾拆一遍。你会看到最常见的偏移分页LIMIT offset, size、适合 C 端列表的游标分页和键集分页以及它们各自的完整示例代码还会有一段真实的深分页卡顿排查链路最后是我自己根据不同业务场景整理的选型建议。如果你正在写列表接口或者你手上的后台报表越翻越慢这篇可以直接对照着改。1. 从一次翻车现场说起为什么要认真对待分页查询1.1 第一反应是加索引结果加了也没用那次事故的表结构其实很常规订单主表主键是自增 id业务上有一个创建时间字段状态字段上建了普通索引。列表接口的 SQL 大概长这样SELECT * FROM t_order WHERE status 1 ORDER BY id DESC LIMIT 20000, 20;我当时的判断是“这么简单的查询肯定是缺索引导致的全表扫描”。于是检查了一遍status 有索引id 是主键order by 也能走主键有序索引层面看起来没有任何问题。但接口就是慢而且规律非常明显page 越大响应越慢。第 50 页 20 毫秒第 200 页 500 毫秒第 500 页直接飙到 2 秒以上。后来我把执行计划打出来看到rows那一列的数字随 offset 直线上升才算真正理解分页查询的瓶颈从来不是“找不到数据”而是“找到了太多不需要的数据还把它们全部扫描了一遍”。1.2 分页查询要同时满足三件事一次合格的分页查询背后其实压着三个互相拉扯的约束性能约束不管用户翻到第几页服务端都应该在可控的时间内返回。深分页不能无限退化。一致性约束同一份数据在翻页过程中如果发生了插入、删除、更新用户看到的结果不能出现离谱的重复或漏读。接口体验约束前端要么能跳页要么能无感加载更多API 参数和返回结构要清晰地表达“现在在第几页”“还有没有下一页”。这三个约束里偏移分页做到了“接口体验”但牺牲了性能和一致性游标分页和键集分页牺牲了“跳页能力”换来了稳定性能和相对的一致性。后面几个章节的示例都会围绕这三个约束展开。2. 三种常见分页方案的原理与适用边界2.1 偏移分页最直观但深翻页是灾难偏移分页的写法最直白几乎所有后端初学者第一天就会用SELECT * FROM t_product ORDER BY id DESC LIMIT 20000, 20;它等价于LIMIT 20 OFFSET 20000。执行过程可以这样理解数据库按照 order by 指定的顺序从第一条开始逐条检查数到第 20020 条然后只把最后 20 条返回给你前面 20000 条全部白扫。打个比方你想读一本厚书第 1000 页的内容但这本实体书不具备“直接翻到第 1000 页”的能力你必须从第 1 页开始一页一页翻过去。翻前 20 页很轻松翻前 20000 页就是灾难。优点很明确语法简单、支持跳页、配合COUNT(*)就能算总页数后端管理系统的“上一页/下一页/跳转某页”交互全靠它。缺点同样致命响应时间随 offset 线性增长而且数据频繁增删时页码会漂移用户看到的内容会错位。2.2 游标分页用“记住上次的位置”替代“从头数到尾”游标分页的核心思路是不再指定“第几页”而是告诉数据库“从哪一条记录之后继续取”。数据库可以借助主键或唯一索引直接定位到那个位置不需要扫描前面的所有行。-- 第一页取最新的 20 条 SELECT * FROM t_product ORDER BY id DESC LIMIT 20; -- 第二页从上一页最后一条记录的 id 开始往前取 SELECT * FROM t_product WHERE id 1001 ORDER BY id DESC LIMIT 20;注意这里的细节上一页返回的是 id 从 1020 到 1001 的 20 条记录下一批要从 1001 开始往前查条件是id 1001。有些同学第一次改写时会写成id lastId ORDER BY ASC结果拿回来的顺序完全反了重新排序又因为内存分页造成另一份混乱。游标分页的深分页性能非常好因为每次查询都只扫描LIMIT size指定的那少量记录和用户翻到第几页没有关系。代价是放弃跳页能力——你没有办法直接告诉接口“我要看第 100 页”。2.3 键集分页按业务字段排序时的进阶版如果排序字段不是主键而是创建时间created_at这种可能有重复的字段单纯用id lastId做游标就不够了。假设两条记录的created_at完全相同而你用WHERE created_at lastCreatedAt那一整批同时间戳的记录都会被跳过用户会发现某几秒内的数据凭空消失。正确的做法是采用组合条件业界一般叫键集分页keyset paginationWHERE (created_at #{lastCreatedAt}) OR (created_at #{lastCreatedAt} AND id #{lastId}) ORDER BY created_at DESC, id DESC LIMIT #{pageSize};理解这个条件的关键在于排序时是“先按 created_at 排再按 id 排”所以翻页的边界条件也是“先比 created_at如果相同再比 id”。有人会问能不能简化成created_at lastCreatedAt不能。因为上一页最后一条记录所在的时间戳可能有多条下一页再取会把时间戳相同但 id 更大的记录重复捞出来。为了配合这种查询数据库索引需要建成(created_at, id)联合索引让索引天然就在排序和过滤上都可用尽量避免 filesort。2.4 三种方案横向对比维度偏移分页游标分页键集分页SQL 形态LIMIT offset, sizeWHERE id lastId ORDER BY idWHERE (a,b) (lastA,lastB) 组合条件深分页性能差offset 越大越慢好扫描量恒定好扫描量恒定跳页能力支持不支持不支持数据增删时的一致性差页码漂移较好较好实现成本低中偏高SQL 条件较绕适用场景后台小表、明确限定页数C 端列表、下拉加载按业务时间排序的 C 端列表选型时我的经验是能不用偏移分页的地方尽量不用但也不要为了炫技强行上游标。后面第 6 章会具体展开判断标准。3. 完整可跑的示例从建表到接口落地3.1 示例表结构与造数思路为了让分页查询的示例能直接跑先建一张商品表CREATE TABLE t_product ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, product_no VARCHAR(32) NOT NULL, name VARCHAR(128) NOT NULL, status TINYINT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_product_no (product_no), KEY idx_created_id (created_at, id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;联合索引idx_created_id是专门给键集分页准备的。造数时要注意一点别让所有数据的 created_at 完全相同。如果全表记录都集中在同一秒键集分页的created_at lastCreatedAt AND id lastId条件虽然不会漏但观察起来不够直观。最好让 created_at 按时间均匀分布可以用你熟悉的脚本循环插入模拟至少 10 万条记录这样偏移分页的性能问题和执行计划的差异才能真实暴露出来。3.2 偏移分页示例与执行计划解读准备一个深分页查询EXPLAIN SELECT * FROM t_product ORDER BY created_at DESC, id DESC LIMIT 20000, 20;执行计划里最值得关注的就是rows字段实际优化器估算出来的扫描行数大概率非常接近 20020。这意味着数据库在取这 20 条结果之前已经把 20020 条记录的排序工作做完了只是把前 20000 条扔掉了。即使把 order by 改成主键id DESCrows 一样会随 offset 变大。原因很简单InnoDB 要沿着索引顺序数到第 20020 条记录再去回表读取完整行。偏移量越大这部分开销越大。加索引解决不了“从头数到位”的问题。3.3 键集分页 SQL 示例第一页查最新的 20 条SELECT id, product_no, name, status, created_at FROM t_product ORDER BY created_at DESC, id DESC LIMIT 20;假设返回的最后一条记录是created_at 2025-04-02 10:00:00id 10020。那么下一页查SELECT id, product_no, name, status, created_at FROM t_product WHERE (created_at 2025-04-02 10:00:00 OR (created_at 2025-04-02 10:00:00 AND id 10020)) ORDER BY created_at DESC, id DESC LIMIT 20;如果产品要求支持“上一页”可以这样查最后在应用层把结果反转一下SELECT id, product_no, name, status, created_at FROM t_product WHERE (created_at 2025-04-02 10:00:00 OR (created_at 2025-04-02 10:00:00 AND id 10020)) ORDER BY created_at ASC, id ASC LIMIT 20;这套 SQL 看起来啰嗦但胜在稳定。按 50 万 offset 深翻时偏移分页可能已经超时键集分页依然能保持几十毫秒的响应。3.4 接口参数设计与返回结构游标/键集分页的接口不适合继续用page参数建议改成这样GET /api/v1/products?limit20GET /api/v1/products?cursorxxxlimit20其中cursor是上一页返回的不透明字符串前端不知道里面是什么也不需要知道。服务端内部把最后一条记录的排序字段和主键编码进去比如把{lastCreatedAt:2025-04-02 10:00:00,lastId:10020}做一次编码得到类似eyJsYXN0Q3JlYXRlZEF0IjoiMjAyNS0wNC0wMiAxMDowMDowMCIsImxhc3RJZCI6MTAwMjB9的串。好处是以后如果排序字段要扩展旧的 cursor 格式还可以做兼容处理。返回结构用 hasMore 代替 totalPages{ list: [ { id: 10020, productNo: PN0010020, name: 示例商品, status: 1, createdAt: 2025-04-02 10:00:00 } ], nextCursor: eyJsYXN0Q3JlYXRlZEF0IjoiMjAyNS0wNC0wMiAxMDowMDowMCIsImxhc3RJZCI6MTAwMjB9, hasMore: true }hasMore的实现技巧是查数据时取limit 1条如果多查出来一条说明后面还有内容然后返回前 limit 条把hasMore置为 true。这样避免为了判断“还有没有下一页”再多发一次 count 查询。3.5 ORM 场景下的适配写法在没有完全使用 SQL 直连的项目里常见的半自动 ORM 框架也都可以照这套逻辑写。核心就是动态拼接 where 条件select idpageByCursor resultTypeProduct SELECT id, product_no, name, status, created_at FROM t_product where if testcursor ! null ![CDATA[ (created_at #{lastCreatedAt} OR (created_at #{lastCreatedAt} AND id #{lastId})) ]] /if /where ORDER BY created_at DESC, id DESC LIMIT #{limit} /select在全自动 ORM 里难度会大一些因为(a x OR (a x AND b y))这种条件不容易用模型方法直接表达。所以我的建议是这类查询不要硬套全自动 ORM 的链式 API直接写 SQL 反而维护成本低。4. 深度分页卡顿的完整排查链路4.1 现象确认与慢查询日志定位回到开头说的那个订单后台列表。出问题后的第一步不是加索引而是先看慢查询日志。当时捞出来的典型慢 SQL 长这样SELECT * FROM t_order WHERE status 1 ORDER BY id DESC LIMIT 150000, 20;日志里的执行时间普遍在 1.8 秒到 3 秒。这个现象可以用一条简单的规律概括响应时间几乎和 offset 数值成正比增长。看到这个规律基本可以锁定不是网络问题、不是应用代码问题而是数据库端扫描行数太多。4.2 用执行计划拆出真正的瓶颈对那条慢 SQL 执行 EXPLAIN看到的简化结果如下id: 1 select_type: SIMPLE table: t_order type: ref key: idx_status rows: 150000 Extra: Using index condition; Using filesort解读一下数据库先用 status 索引过滤出大约 15 万条候选记录然后因为排序字段是 id理论上主键本身有序但经过 status 过滤后的结果集不是按 id 有序的所以额外出现了 filesort。再加上要丢掉前 15 万条最终就是“过滤 15 万行 排序 15 万行 丢弃 15 万行”慢是必然的。这里也给一个经验和前面章节呼应执行计划里 rows 不完全是真实扫描行数但它的数量级能准确反映分页查询的恶化趋势。优化前后只需要对比这个数字就心里有数。4.3 为什么加索引也没用扫描量大是核心矛盾很多人会下意识地给status和order by id各加一个索引但会发现收效甚微。原因在于status索引只能帮你快速找到所有 status1 的记录但无法帮你跳过前 15 万条order by id虽然能利用主键有序但在status1这种过滤条件下索引顺序和 id 顺序并不完全一致优化器仍然要排序。所以“索引缺失”根本不是这个问题的主要矛盾。主要矛盾是分页查询的 offset 决定了数据库必须扫描足够多的行才能定位到目标页。这一点可以再补一个实验把 select * 改成只查 idSELECT id FROM t_order WHERE status 1 ORDER BY id DESC LIMIT 150000, 20;确实会比 select * 快一些因为省去了大量回表但 rows 仍然接近 150000深分页会继续退化只是慢得没那么明显。4.4 延迟关联优化示例如果存量接口暂时改不成游标分页又需要立刻把性能压下去最常用的优化手段是延迟关联先走覆盖索引查出目标 id再用 join 回原表取完整行。SELECT t.* FROM t_order t INNER JOIN ( SELECT id FROM t_order WHERE status 1 ORDER BY id DESC LIMIT 150000, 20 ) tmp ON t.id tmp.id ORDER BY t.id DESC;这个技巧的核心是让最耗时的“排序 丢弃”阶段只处理 id 这一列回表操作被压缩到最终需要的 20 行上。实测下来同样翻到第 500 页左右原来的 SQL 要 2 秒多延迟关联改写后能压到 200 到 300 毫秒。不过要清醒地认识到延迟关联并没有改变“扫描前 15 万行”的本质它只是让每次扫描的代价变小了。数据量继续增长offset 继续变大总有一天还是撑不住。4.5 优化方案落地与验证那个后台项目最终采用的不是彻底推翻分页方式而是“限制极端翻页”接口层限制page * page_size的最大值比如不允许超过 100 页前端把页码组件改成“最多显示前后 10 页”超过 100 页后不再提供跳转对于想看很久以前数据的用户引导用“按订单号搜索”或“按时间区间筛选”进入小范围列表。这样既保住了后台熟悉的跳页交互又把最坏性能卡死在可控范围内。上线后观察了一周慢查询日志里再也没有出现 offset 超过 30000 的记录。这个例子说明分页方案的选择不是纯技术题它必须结合产品交互和业务约束一起拍板。5. 分页查询的一致性陷阱与兜底手段5.1 插入新数据导致页码错位偏移分页最容易被忽视的问题是列表头部不断插入新数据时页码会集体“后退”。举个例子第一页取了 id 1 到 20用户在认真看这时候系统新增了一条记录并排在列表最前面。用户点第二页offset20 实际拿到的是新的第 21 条到第 40 条其中就包含了旧的第一页末尾那条记录。用户会感觉“这一条我刚刚看过了为什么又出现一遍”。游标分页在这种情况下就干净很多第二页的边界是上一页最后一条记录的 id 或时间戳新插入的数据只会影响“下一页会不会出现它”不会倒灌到已翻过的区间里。5.2 删除数据导致漏读删除带来的问题更隐蔽。假设用户已经读到第 9 页末尾第 10 页按 offset 去取时如果第 9 页前面有几条记录中途被删掉数据库的行序号会自动补位原本属于第 11 页的数据被提前拉了上来。用户会觉得自己“跳过了几页没看”但因为无法证明体验上非常尴尬。游标分页的补位逻辑不同它严格从上一页最后位置往后取删除只会让本次结果少几条不会把未读数据直接吞掉。如果配合前端“已渲染 id 去重”几乎不会出现重复和跳读。5.3 并发写入时的边界表现数据库隔离级别解决的是“一次查询里能看到什么”解决不了“两次分页之间其他事务提交了新数据”带来的集合变化。偏移分页的页码漂移本质就是这个问题它是结构性的很难通过 SQL 本身兜住。如果业务对一致性要求极高比如审核队列、对账批次这种场景我一般不建议用“实时列表分页”承载而是给这批任务打个批次号生成一份稳定的快照数据然后基于快照做游标扫描。代价是数据不是实时最新但每一页之间保证严格一致。5.4 兜底设计清单所有列表接口对外都要明确语义是“页码分页”还是“游标分页”不允许混着来前端做“已加载 id 去重”面对偶发重复时至少 UI 不要重复渲染后端用limit 1判断 hasMore不要依赖“查 count 再去减 offset”如果必须返回总数优先用缓存型计数或者异步统计不要在大表上高频实时 count。6. 结合真实业务场景的选型建议6.1 不同业务列表的最佳实践我实际维护过的项目里大体可以分成四类场景后台订单/商品列表需要跳页数据量在几十万到几百万之间。最终选择偏移分页加延迟关联优化同时限定最大页码符合后台人员“翻到某页”的固有习惯。C 端订单/消息列表手机端都是下拉加载更多没有页码概念。选择键集分页按创建时间倒序配合联合索引性能稳定。批量导出任务不是给用户分页而是程序分批捞数据。直接用id lastId ORDER BY id ASC LIMIT 5000的游标方式方便断点续跑。站内搜索列表如果底层是搜索引擎那么 from/size 就等同于偏移分页search_after 就等同于游标。不要把搜索引擎的深翻页当成无底洞能用 search_after 的场景尽量用。6.2 什么时候可以继续用偏移分页并不是所有列表都要改成游标偏移分页在下面这些前提里依然是合理的数据量可控比如单表过滤后不会超过 1 万行产品明确需要页码跳转比如后台表格里的“直接去第 30 页”数据基本不频繁插入删除或者用户对轻微重复不敏感有强过滤条件用户实际上不会主动翻到几百页之后。如果这四条都满足强行上游标分页反而会增加前后端沟通成本和产品复杂度。6.3 改造成本的现实评估从偏移分页切换到游标/键集分页不是只改一条 SQL 的事接口参数从page变成cursor前端列表组件的“页码渲染”要替换成“加载更多”排序字段必须唯一且不可变如果业务需要“按热度动态排序”游标分页很难稳定total和totalPages的语义被削弱产品需要接受hasMore页面 URL 无法直接分享“第 300 页”对后台运营的某些场景会不友好测试用例要覆盖“游标过期”“数据被删”“时间戳重复”等边界。这些成本我踩过之后最大的感受是改造前先问产品和前端一句话——你们真的需要无限翻到第几千页吗很多时候答案是不需要。6.4 针对新老项目的一句话建议新项目里的列表接口如果预计数据量会增长默认就用游标/键集分页并把游标封装成公共组件存量项目如果收到了深分页慢查询投诉先用延迟关联优化止血再评估是否腾出版本彻底切换。两种方案不要混在一个接口里否则日志都说不清当前走的是什么逻辑。最后再分享一个小习惯我在所有列表接口的日志里都会埋一个scan_rows字段把执行计划里那个 rows 的近似值打出来。上线初期看不出差异但数据量一旦翻倍这个数字会先于接口超时暴露风险。分页查询表面上是几行 SQL实际牵涉到索引选择、排序稳定性、接口语义和数据一致性值得把它当成一个专门的设计点在项目里定成规范。