分页查询这事看起来就是个LIMIT offset, size的事但真到你手里几十万条订单数据翻到第 2000 页的时候那酸爽只有经历过的人才懂。我在实际项目里接过的分页需求少说也有十来个从最初只用了三五行 SQL 就让页面跑起来到后来被深分页的慢查询搞到半夜起来看监控这中间踩过的坑和沉淀下来的方法确实值得好好整理一篇。这篇东西不做理论堆砌直接围绕分页查询的完整落地过程来展开覆盖基础写法、参数设计、不同数据库适配、性能优化还有一堆你在文档里查不到的实践经验。这篇内容适合谁看后端开发、全栈工程师、刚入门但想系统掌握数据查询技巧的朋友甚至是你正在做的管理后台、移动端列表页、报表系统都可能用得上。无论你是第一次写分页还是已经被深分页折磨过这篇文章都能给你一个可以直接照着做的完整参考。1. 分页查询的基础认知看似简单的问题为什么值得深挖1.1 分页查询的本质与核心价值分页查询说白了就是不一次性返回所有数据而是按每页固定的条数分段把数据交给前端展示。这个需求几乎所有带列表页的系统里都存在——订单列表、用户列表、日志记录、商品列表没有分页前端得一次渲染几千条数据浏览器卡死不说后端数据库也得被一次全表扫描拖垮。这里要理解分页背后真正的两个痛点第一个是网络传输成本。假设你的订单表有 50 万条记录每条记录 500 字节一次性查出来大约 250MB 数据扔给浏览器用户直接崩溃。第二个是数据库查询成本。一次查询 50 万行对数据库的 IO 和 CPU 消耗是实打实的分页能把一次大查询拆成多次小查询让系统在高并发下还能撑得住。还有一个很多人忽略的核心价值分页给前端带来了更好的交互体验。用户不需要等全部数据加载完才看到页面先看到前 20 条然后滚动加载或者点击下一页感知上的响应速度会快很多。这也是为什么现在很多 App 和网页都采用“下拉加载更多”的方案本质上也是一种分页只是把“页码”换成了“游标”。我在做某个电商后台的订单列表时一开始就用了最简单的LIMIT分页数据量大概 20 万条的时候一切正常到了 100 万条第 100 页之后的请求开始频繁超时。这时候就不得不认真思考分页不只是“加个 limit 就行”它的实现方式、参数设计、索引利用、查询代价每一样都需要仔细权衡。1.2 物理分页与内存分页的分水岭很多初学者容易混淆两个概念物理分页和内存分页。物理分页是指数据库层面直接只查当前页需要的数据核心是 SQL 中带LIMIT或者ROWNUM、TOP这类关键词数据库只把那一小段数据返回给应用层。这是最推荐的方式因为每页查询成本低数据量再大都不会出现内存暴涨。内存分页则是先把所有数据一次性加载到应用内存里再通过代码切割出当前页要显示的部分。这种方式在小数据量比如几千条配置数据下没什么问题代码写起来还特别简单但一旦数据量上万甚至上十万每次请求都全量加载内存和 IO 的压力会非常明显。我在一些老项目里见过这种写法负责人给出的理由是“数据量不大不用折腾”结果后来数据量涨了十倍接口响应时间从 200ms 涨到 8 秒最后加班重写。从实际工程角度我给你的建议是默认使用物理分页除非你已经确认数据量长期稳定在一个很小的范围内。判断标准就是单表数据量是否超过 5000 行——超过就说不上“很小”了。2. 分页查询的经典实现方案与完整示例代码2.1 MySQL 下 LIMIT 基础分页写法与参数设计先看最经典的 MySQL 分页 SQL 写法-- 第 1 页每页 20 条 SELECT * FROM orders ORDER BY create_time DESC LIMIT 0, 20; -- 第 3 页每页 20 条 SELECT * FROM orders ORDER BY create_time DESC LIMIT 40, 20;这里LIMIT offset, size的含义是跳过前 offset 条记录然后取 size 条。第二页的 offset 就是 20第三页就是 40规律是offset (pageNum - 1) * pageSize。参数设计上有一个很重要的点前端传过来的 pageNum 和 pageSize 必须做校验不能直接拼进 SQL。如果前端传一个 pageSize10000甚至 pageNum 是负数轻则查询超时重则把数据库资源耗尽。我惯用的参数校验规则是这样的pageSize必须大于 0小于等于 200超过 200 强制按 200 算。pageNum必须大于等于 1小于等于 10000超过就直接返回空列表不查数据库。排序字段用白名单机制不能允许前端随便传一个字段名拼到 ORDER BY 里防止 SQL 注入。基础分页的问题非常明显。LIMIT的 offset 越大MySQL 需要扫描并跳过越多的行。比如LIMIT 1000000, 20数据库不是直接定位到第 100 万条而是从第一条开始数数到 100 万条之后才返回 20 条前面那 100 万条全被白白扫描了一遍。这就是深分页性能瓶颈的本质。2.2 分页查询的 COUNT 统计与总页数计算一个完整的列表分页接口除了返回当前页的数据通常还要返回总记录数和总页数前端才能渲染出“共 1234 条第 3/62 页”这种效果。这时候需要额外一条 COUNT 查询SELECT COUNT(*) FROM orders WHERE status 1;这条 COUNT 查询看起来简单实际也是性能杀手。如果没走索引MySQL 就得全表扫描才算得出来总行数。为了缓解这个问题有几个经验性的做法第一个做法是尽量让 COUNT 查询走覆盖索引。比如你只需要统计数量可以写SELECT COUNT(id) FROM orders WHERE status 1如果status是索引字段查询会更快但本质上还是要扫描所有符合条件的数据。第二个做法是在数据量特别大的情况下考虑不返回总页数只返回一个hasMore布尔值。每次多查一条比如LIMIT pageSize 1查出来是 pageSize1 条就说明还有下一页这样做省去了 COUNT 的全表扫描成本。第三个做法是对超大数据集使用估算值。比如根据统计信息大概算出总页数用户翻到最后一页时再查一次真实总数体验损失很小性能提升巨大。我在实际项目中更倾向第二种方案因为列表页大多数时候用户看的是前几页根本不会翻到最后一页。把 COUNT 语句省掉让接口快 50% 以上这才是实实在在的优化。2.3 非 MySQL 数据库的分页方言适配很多项目不是只用 MySQLOracle、PostgreSQL、SQL Server 的分页语法都不一样你要做多数据库支持的话必须提前规划好。PostgreSQL 和 MySQL 类似支持LIMIT ... OFFSET ...写法兼容性最好SELECT * FROM orders ORDER BY create_time DESC LIMIT 20 OFFSET 40;SQL Server 早期版本用的是TOP加子查询的方式逻辑很绕SELECT TOP 20 * FROM orders WHERE id NOT IN ( SELECT TOP 40 id FROM orders ORDER BY create_time DESC ) ORDER BY create_time DESC;SQL Server 2012 之后的版本支持了OFFSET ... FETCH语法SELECT * FROM orders ORDER BY create_time DESC OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;Oracle 则一直用ROWNUM做分页经典的三层嵌套写法效率还算可以SELECT * FROM ( SELECT t.*, ROWNUM rn FROM ( SELECT * FROM orders ORDER BY create_time DESC ) t WHERE ROWNUM 60 ) WHERE rn 40;选择分页方案的时候顺带要考虑的一个点是你的项目需不需要做多数据库兼容。如果项目要同时兼容 MySQL 和 PostgreSQL直接用LIMIT OFFSET语法就对了如果你要兼容三种以上数据库就得引入 ORM 框架通过框架的方言适配自动转换 SQL。我在做某跨平台系统的时候就遇到过一次数据库从 SQL Server 迁移到 MySQL 的情况幸好当时用的是框架内置的分页能力换数据源只需要改配置底层 SQL 自动重新生成不然排查工作量会非常大。3. 深分页性能瓶颈与优化实战3.1 分页查询越来越慢的根因分析先说一个我亲测的真实数字有一个 500 万行数据的订单表LIMIT 0, 20的耗时是 130msLIMIT 200000, 20的耗时直接跳到 5.8 秒翻了 40 多倍。这不是偶发情况而是深分页的必然结果。原因要从 B 树索引的底层机制说起。InnoDB 的索引本质上是一棵 B 树主键索引的叶子节点存的是完整行数据二级索引的叶子节点存的是主键值。当你执行LIMIT 200000, 20的时候优化器依然会从第一行记录开始扫描 B 树依次数出 200000 行然后丢弃前 200000 行只把第 200001 到 200020 行返回给客户端。也就是说数据库花了大量时间去读取、传输、丢弃了 20 万条你不想要的数据。很多人以为加了 ORDER BY 就能让分页变快其实如果排序字段没有索引数据库得先把 200000 行记录全部取出在临时表里做一次完整的排序再应用 LIMIT。这个过程不但要扫描大量行还要用到临时文件和磁盘 IO慢上加慢。深分页对索引的利用也是有限的。如果 WHERE 条件里有范围查询比如订单状态和创建时间范围都用上了那么索引的使用很可能只覆盖其中一部分另一部分走了回表甚至全表扫描。我排查过不少慢查询最后定位到的原因都是索引失效而不是 SQL 本身写错了。3.2 延迟关联改造法一张表拆两步查延迟关联Late Row Lookup是解决深分页问题最直接有效的方法之一。核心思路是先用覆盖索引查出当前页需要的主键 ID再用这些 ID 去反查完整行数据。这样数据库就不用从头扫描超过 offset 条完整行扫描的只是索引里的主键和排序字段数据量小很多扫描速度会快一个数量级。SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 200000, 20 ) t ON o.id t.id ORDER BY o.create_time DESC;这里子查询只查了id和排序字段create_time这两个字段都在复合索引里可以直接走覆盖索引不需要回表读取整行数据。数据库定位到那 20 个 id 后再通过主键回表查询完整记录。主键是 B 树聚簇索引根据主键查找是最高效的路径。我在某个项目里用这招把深分页查询从 5.8 秒优化到了 0.7 秒效果非常显著。不过要注意延迟关联只对排序字段有索引的情况比较理想如果你的排序字段没有索引子查询一样要在临时表排序优化幅度就会大打折扣。所以使用延迟关联前请先确认你的复合索引设计是到位的。延迟关联还有一个变体就是只把 ID 列表返回给应用层再由应用层用WHERE id IN (...)的批次查询补全数据。这种写法在数据需要从多个系统聚合、或者需要对查出来的数据做二次加工时更灵活。3.3 游标分页与滑动窗口思路如果说延迟关联是在原有分页模式上做优化游标分页就是换了一套思路不按页码划分按排序字段的位置划分。所谓游标不是数据库里的 CURSOR而是指客户端传给服务端的一个“上次最后一条记录的位置值”服务端只返回这个位置之后的数据。看一个基于创建时间的游标分页示例-- 客户端传 lastId10086服务端返回 create_time 10086 之后的 20 条 SELECT * FROM orders WHERE create_time 2025-06-01 12:00:00 -- 这个时间来自上一次分页的最后一条记录 ORDER BY create_time DESC LIMIT 20;这种写法天生不会有深分页问题因为不管翻了多少页SQL 里的条件始终是WHERE create_time xxx数据库通过索引直接定位实际扫描的数据量和你是在第 1 页还是第 10000 页没有任何关系。它非常适合“下拉加载更多”这种移动端场景因为用户不需要看到具体的页码只需要一条一条往上翻就行。游标分页也有一些需要处理好的细节。如果按时间倒序排列同时存在多条相同时间的数据你用“小于”去判断会漏掉同一时间戳的不同记录。解决办法是把游标设计成复合条件(create_time xxx) OR (create_time xxx AND id lastId)同时把id作为第二排序条件。这个细节我一开始忽略过结果用户反馈列表数据会有 1 到 2 条重复或缺失排查了很久才定位到是排序值重复导致数据错位。另外游标分页不提供跳页能力。如果 UI 设计上有“跳转到第 20 页”这种交互游标分页就没法直接支持还得退回 LIMIT 方式或者用缓存把页码映射到游标位置。所以我不建议把所有场景都无脑改成游标分页而是要看产品交互形态。3.4 基于索引覆盖的快速分页与分页缓存策略如果你的数据量特别大而且确实需要支持任意翻页延迟关联也不是最优解这时候还有两个方向可以考虑。第一个是索引覆盖全部查询字段。假设列表页只展示订单号、创建时间、订单状态、金额这几个字段你可以建立一个覆盖索引把需要返回的所有字段都放到索引里。这样数据库根本不需要回表直接从索引叶子节点就能取到全部数据SELECT order_no, create_time, status, amount FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 200000, 20;如果(status, create_time, order_no, amount)是一个复合索引这个查询就能在索引里直接完成不产生回表。但要注意覆盖索引不是万能的字段越多、索引越大插入和更新时的维护成本越高所以只建议给高频、字段固定的查询场景设计。第二个是加一层分页缓存。比如 Redis 里有page:orders:{pageNum}:{pageSize}这样的 key第一次查询时把结果和总页数缓存起来设置一个合理的过期时间比如 5 到 30 秒。用户快速翻页时命中缓存不需要打数据库。这里要特别留意数据一致性的问题订单状态变更后旧的缓存页可能显示过时数据。解决办法有两个要么在订单变化时主动删除相关分页缓存这个成本可控要么缓存时间设短一点比如 10 秒作为降级手段而不是主链路。我个人实践下来分页缓存最适用的场景是用户高频率翻页、数据变更频率低的管理后台列表比如城市列表、类目列表、配置列表一个月都改不了几次。对于订单这类高频增量数据缓存命中率低还不如把精力放在优化 SQL 上。4. 分页查询常见问题与排查实录4.1 排序不稳定导致的分页数据重复与遗漏这是分页查询里最容易出现的隐性问题而且特别难排查。现象是用户翻到第 2 页时看到了第 1 页里见过的一条记录或者第 1 页有一条记录在第 2 页消失不见。根子在于ORDER BY的字段有重复值而且数据库不承诺重复值之间的相对顺序。比如你按create_time排序但同一秒有多个订单创建那么 MySQL 返回时这些订单的先后是不确定的第一页可能取到其中一个第二页如果数据库的内部扫描顺序变化了可能又取了另一个或者漏掉了一个。解决办法很简单一句话总结排序条件里必须带上唯一字段。最稳妥的写法SELECT * FROM orders ORDER BY create_time DESC, id DESC LIMIT 20;给排序条件加 id 作为第二关键字可以保证排序的唯一性和稳定性。这个经验在新手项目里几乎不会被注意到但上线后用户反馈数据对不上时排查到这个问题的人都会记忆深刻。我还遇到过一个更隐蔽的版本排序字段二选一前端可以传“按时间排序”或“按金额排序”切换时没有清空已加载的列表数据导致同一批次数据在不同排序规则下重复展示。这个虽然更多是前端问题但也提醒我们分页接口的排序规则一旦定下来在整条列表链路里必须统一不能中途改变。4.2 并发写入下的分页数据不一致分页还有个天然劣势就是当数据在持续变化时分页结果无法保证一致性。我做过一个实时订单看板每秒钟都有新订单插入。用户正在查看“第 2 页”此时来了 5 条新订单它们排到了第 1 页原有第 2 页的数据整体下移用户在第 2 页看到的其实是原来的第 3 页内容。在高频插入场景下用户翻页时漏看数据的概率不小。这个问题在 LIMIT 分页下很难彻底解决只能缓解。缓解手段包括对长时间停留在列表页的用户提供前端“刷新”按钮重新查询第一页或者在后端做翻页时记录当前第一页的最大排序值后续翻页都基于这个值往后取类似游标分页的变体。有些系统干脆引入“快照”的概念用户点开列表时后台生成一个快照 ID所有分页查询都指向快照数据。快照可以用临时表或 Redis 存储数据量控制在几十万条以内是可行的但这是重方案需要权衡成本和收益。我个人经验是对一致性要求不是绝对严格的列表直接接受这种小的数据偏移并加上刷新机制是比较务实的做法。毕竟产品用户对列表翻页时偶尔出现重复或遗漏的容忍度比我们想象的高真正要严格一致的是订单详情、账务明细这类静态数据那些页面本身就不该频繁变化。4.3 常见的分页慢查询排查方法与工具如果你接手一个已经线上跑着的系统遇到分页接口响应慢怎么迅速定位问题第一步是拿到完整的 SQL。很多框架的分页器会自动生成 SQL包括 COUNT 语句和数据查询语句。先把它捞出来到测试库或者从慢查询日志里找到执行计划和耗时。第二步是看执行计划。用EXPLAIN分析 SQL重点看几个关键列type最好是 range 或者 ref不是 ALL 全表扫描、key实际用到哪个索引不是 NULL、rows预估扫描行数这个数字过大一定有问题、Extra有没有 Using filesort 或 Using temporary。第三步是模拟深分页。手动把 offset 调大比如 10 万、50 万、100 万看耗时增长曲线。如果增长是线性的说明 SQL 还有救通过延迟关联或者索引优化能解决如果增长是跳跃式甚至指数式的往往意味着排序字段没有索引或者 WHERE 条件存在隐式转换。工具方面我常用mysqldumpslow来分析慢查询日志用performance_schema查看锁等待和 IO 情况。如果项目在云上直接用云服务商提供的慢查询分析面板基本能一键定位 Top N 慢 SQL然后再针对性地去分析执行计划。4.4 分页参数注入与边界条件防护分页接口是典型的对外入口很容易被恶意调用搞出大量数据库查询。比如你不限制 pageSize对方直接传个 10 万你的数据库就会被一次全表查询拖垮如果 pageNum 传一个 1 亿深分页会直接塞满内存和 IO。我见过一个真实事故某个活动的排行榜接口由于没有对pageSize做上限限制被人用脚本持续请求数据库 CPU 直接爆满连带了同库的支付业务超时。事后复盘问题就是在网关层和服务层都没有对分页参数做任何限制。这里给出一套我经过反复验证的参数防护规则// 服务端统一分页校验伪代码 public PageResult queryOrders(int pageNum, int pageSize) { // 页码最小为 1超出 10000 返回空列表 if (pageNum 1 || pageNum 10000) { return PageResult.empty(); } // 每页大小限制在 1~100 之间超出会被正确钳制 if (pageSize 1) { pageSize 10; } if (pageSize 100) { pageSize 100; } // 排序字段使用白名单 String orderBy getSafeOrderBy(paramMap.get(orderBy)); // 继续执行查询... }另外必要的时候对分页接口加LIMIT 200, 20类似极值限制比如限制单次 COUNT 扫描的最大行数当rows超过设定阈值时直接拒绝查询并返回 503。这个虽然有点激进但对保护核心数据库资源非常有效。从安全角度来说排序字段的白名单至关重要。如果你直接把前端传的字段名拼进 ORDER BY轻则被查到全表字段名重则可能借助自定义表达式实施注入攻击这些都是真实存在过的攻击方式。5. 分页查询方案选型与实际项目落地经验5.1 不同业务场景下分页方案对比与选择总结下来没有一种分页方案是银弹不同的业务场景需要不同的策略。我从实际项目经验出发把常见的业务场景和对应推荐方案整理成一个对照表供你参考业务场景数据量级推荐方案理由后台管理列表 页码跳转10 万以下LIMIT 基础分页实现简单功能完整后台管理列表 页码跳转10 万以上延迟关联分页保证跳页能力同时性能可控C 端信息流 / 订单流水百万级游标分页无深分页问题响应稳定移动端下拉加载更多任意量级游标分页无页码概念体验最佳低频变更配置列表千级以下内存分页或缓存分页省去 SQL 优化成本响应最快实时数据大屏监控高频追加时间游标 只读最近页保证数据一致性感知这个表我建议你直接收藏真正上手做方案选型的时候对着看一遍基本不会错。选择分页方案时还有两个容易被忽略的点一是前端交互形态决定了你能不能使用高效的游标分页所以做技术方案前一定先跟产品确认好列表是“页码式”还是“流式”二是技术栈能力边界如果你用的 ORM 框架不支持自定义分页 SQL那么高级优化方案需要你有能力写原生 SQL否则只能在框架能力范围内做取舍。5.2 一套实用的分页接口设计规范为了让分页查询在团队内部不踩坑、不重复造轮子我建议团队约定一套统一的分页接口设计规范。这套规范我从多个项目里提炼出来执行落地后磨合成本降低了很多。接口请求参数统一如下pageNum: 页码从 1 开始 pageSize: 每页条数默认 10最大 100 sortBy: 排序字段白名单默认 create_time sortOrder: asc/desc默认 desc接口响应结构统一如下{ code: 0, data: { list: [], pageNum: 1, pageSize: 20, totalPage: 50, totalCount: 1000, hasMore: true } }这里hasMore字段特别有用前端可以做“还有没有下一页”的快速判断不需要等totalCount返回。当数据量很大时服务端可以有意不计算总条数只返回这个布尔值性能提升明显。统一规范的一个额外好处是前端可以封装一套通用的分页组件不再需要为每个页面单独写分页逻辑开发效率能明显提升。我经历过团队从各自写分页到统一封装的转变接口联调的工作量下降了很多。5.3 迭代演进中分页查询的分阶段优化路线分页查询不是一上来就要做到最优的合理的演进路线能帮你把精力花在刀刃上。我习惯把分页优化分成四个阶段第一阶段是能用。上线初期数据量不大直接 LIMIT 分页配合一个 COUNT 查询功能完整代码结构清晰。这个阶段的目标是让功能快速交付不需要过早优化。第二阶段是好用。数据量开始明显增长比如超过 20 万行以后深分页慢查询开始出现。这时候做两类事情一是给排序字段和 WHERE 条件字段补上复合索引二是把 COUNT 查询改成hasMore模式或者加缓存。这样基础体验能保持住。第三阶段是优化。数据量超过百万后某些核心列表还是慢。引入延迟关联或游标分页根据具体场景选择适合的方案。这时候需要和产品沟通交互形态确定哪些列表可以改游标式哪些必须保留页码跳转。第四阶段是扩展。数据量到千万级别单表已经很难支撑这时候就不是分页 SQL 的问题了而是做数据归档、分库分表或者引入搜索引擎。分页方案要和数据分布策略联动通常是一个中间件层面的改造工作量会大很多。这个分阶段路线的好处是每一阶段都有明确的目标和验证标准不会出现一开始就做重架构、最后发现需求根本用不上的情况。我在项目里遵循这个思路既保证了上线速度又在恰当的时机做了合理的优化。6. 实践中的进一步思考与经验沉淀分页查询做到这步基本已经覆盖了从基础到进阶的大部分问题。但我还是想额外分享几个容易被忽略的思考角度。6.1 分页并不一定是最优的信息展示方案虽然分页查询是通用的解决方案但有些场景里分页本身就不是最好的选择。比如一个报表页面要展示全年的日销售数据不超过 400 条那一次性全量返回给前端做图表渲染比翻页体验好得多。又比如一个排行榜只需要 Top 10直接查前 10 条返回即可根本不需要分页参数。所以拿到需求先别急着写分页代码看清数据量级和交互需求采取更合适的方案。分页与搜索的联动也值得思考。如果你的列表背后是搜索引擎或者全文检索服务这类系统天然有自己的分页机制原理上和数据库分页类似但细节上有差异。比如 Elasticsearch 的from size同样存在深分页问题解决方案是search_after或 Scroll 游标思路跟数据库的游标分页如出一辙。掌握了数据库分页的底层逻辑迁移到搜索引擎上也能举一反三。6.2 从异常日志中快速定位分页问题的实战技巧最后分享一个排查技巧在分页接口的日志里一定要打印页码、每页条数、查询耗时、SQL 执行计划的关键信息。我见过太多团队的分页接口日志就一行“查询成功”出了事之后完全无从下手。推荐的日志格式是[分页查询] pageNum3 pageSize20 sortBycreate_time total1658 耗时135ms如果再配合把慢查询 SQL 单独打印出来线上问题定位的速度会快很多。有一次用户反馈某个列表打开要 7 秒我通过日志发现这个请求 pageNum842、pageSize50实际 SQL 已经翻到第 4 万多行马上定位到是前端调用逻辑异常导致页码暴增不是 SQL 本身的问题。如果没有日志这种排查可能要花几小时有了日志几分钟就搞定。从实际项目到通用方法论分页查询的细节远比想象中多希望这篇梳理能让你少走一些弯路。最后想说的是分页这类基础功能往往不被重视但恰恰是这类看似简单的基础功能最能体现一个开发者的工程素养。遇到分页问题不要急着堆方案先把场景想清楚把参数校验和日志做好再谈性能优化这个顺序千万不能反。