分页查询原理与优化：从LIMIT/OFFSET到游标分页的实践指南

干后台开发这些年，“分页查询”大概是写过的最高频的一类SQL，需求听起来也永远很简单：列表接口返回前N条，前端点下一页再取N条。我第一次接触分页时，也觉得这是最没有技术含量的活，直到线上一个千万级的流水表，一条带OFFSET的查询把数据库CPU拉到90%，我才意识到，这行看起来平平无奇的SQL，里面的门道比想象中多得多。

这篇文章我会把分页查询从头到尾拆开讲一遍：从分页的核心模型、主流实现方式的底层原理，到手写一套分页查询的完整步骤、参数怎么换算、返回结构怎么设计，再到实际业务里最常见的几种分页故障和排查套路，最后聊一聊大数据量下的优化方案。适合作后端的开发同学参考，也适合刚入行不久、想把接口查询写得更扎实的初级工程师，前端同学看后半部分也能明白为什么有的列表接口越翻越慢。

1. 分页查询核心模型：你究竟在分什么

1.1 没有分页的世界会怎样

先想一个问题：如果一个列表接口不做任何分页，直接把整张表的数据返回，会发生什么？

假设表里有10万条记录，每条记录平均2KB，一次查询返回的数据就是近200MB。这个体积在数据库到服务端再到前端的整条链路上，基本上都是灾难级的。数据库要花大量时间和内存去扫描、发送这些数据，服务端要把整包数据加载进内存，网络传输会直接把带宽打满，前端拿到之后也没法一次性渲染——浏览器早就卡死了。用户侧的体验更是灾难：首屏加载几十秒，翻页根本不存在，手机流量被刷爆。

所以分页查询本质上做的是这么一件事：把一次大结果集的查询，拆成多次小结果集的查询，每次只返回用户想看的那一小段数据。这一段数据由两个参数决定：一个是页码（page），一个是每页条数（pageSize）。整个分页过程看似简单，但背后涉及数据量的裁剪方式、排序稳定性、接口响应体设计、SQL执行计划等一系列细节，哪一个环节处理不好，数据量一大就会爆雷。

1.2 逻辑分页与物理分页的区别

分页在最粗的粒度上，有两种实现路线。

第一种叫逻辑分页，也叫内存分页。业务逻辑把数据库全量数据一次性查出来，然后放到内存里，再根据页码和每页条数手工截取一小段返回给前端。在数据量只有几百条、几千条的时候，这种做法确实能跑，而且代码写起来极其简单——查出List，然后循环切片就行。但数据量一旦上到十万、百万量级，全量查询本身就慢，内存里塞下这么大的集合，还同时服务多个请求，程序不卡死也得频繁GC。我见过一个内部管理后台，查询范围稍微放大一点就直接OOM，后来定位到原因，就是某段代码用的逻辑分页，把整月流水全load进内存再切片。这种方案只能用在数据量明确小、且没有并发压力的场景，数据库已经有分页能力的情况下，尽量不要这么干。

第二种叫物理分页，也叫数据库分页。查询SQL本身就会做数据裁剪，数据库只把目标页那几条数据返回给应用层，比如MySQL里的LIMIT ... OFFSET ...，PostgreSQL里的LIMIT ... OFFSET ...，Oracle里的ROWNUM或新版的FETCH FIRST子句。这类方案能在数据库层大幅减少网络传输量和内存开销，是绝大多数业务系统的正确选择。

一句话总结：能物理分页就别逻辑分页；能用数据库裁剪数据，就别把全量数据拉到应用层再截取——这个原则在后面的所有优化里都贯穿始终。

1.3 返回结构与参数怎么设计

一个完整的分页接口，往返两侧需要约定的不只是每页数据，还有一堆用于控制状态的信息。常见的设计是这样的：

请求侧：

• page：当前页码，从1开始
• pageSize：每页条数

响应侧：

• list：当前页的数据列表
• pageNum：当前页码，原样回显给前端
• pageSize：本页实际返回条数
• total：符合条件的总记录数
• pages：总页数
• hasNext：是否还有下一页

total和pages这两个字段很关键。前端要做“共X条记录”的提示，要做最后一页的判断，都要靠它们。但很多人会忽略一个事实：count查询本身就是一条额外SQL，在数据量大时它可能比查数据还慢，这一点后面会专门展开。

参数校验上也容易踩坑。page传0、传负数、传字符串，pageSize传一个超大值，这些都要处理。我个人的习惯是：

• page最小值为1，小于1直接置为1
• pageSize最小值1，最大值按业务上限限制，大部分列表控制在50以内，最多100，超了按最大值处理
• 对于可能的注入风险，用参数化查询，别把参数直接拼接进SQL

换算公式很简单：offset = (page - 1) * pageSize。这个公式几乎在所有数据库的分页实现中都是通用的。

2. 主流分页实现方式的底层原理

分页最痛苦的地方不在“怎么写”，而在“数据量大了之后怎么还快”。这会引出三种常见的实现路线，它们的底层逻辑完全不同，适用场景也各不一样。

2.1 偏移式分页：LIMIT/OFFSET的真面目

先看最常见的一类写法：

SELECT id, name, create_time FROM user ORDER BY id LIMIT 10 OFFSET 20;

这条SQL意思是从第21条开始取10条，对应page=3、pageSize=10的场景。

大多数人对它的理解是：数据库很快就定位到了第21条，然后往后拿10条返回。实际完全不是这么回事。MySQL InnoDB在执行这条SQL时，并不知道哪一行是第21条，它的执行过程是：从第一个符合条件的叶子节点开始，沿着B+树索引逐条往下扫描，数到前20条，一条一条丢弃，直到第21条才当作有效结果开始收集，再往后取满10条才停下。

换句话说，OFFSET越大，数据库要扫描和丢弃的行就越多，消耗的时间就线性上涨。翻到第100000页的时候，数据库可能要扫描上百万行，只为了丢掉前面那一大堆你不要的数据。这也是为什么很多系统，列表接口只要翻到几十页之后就明显变慢，甚至超时。

我做一个不太严谨但很好懂的生活类比：这就像你想读一本书的第500页，正常做法是直接把书翻到那一页。但偏移式分页的做法是让印刷厂从第1页开始印刷，中间所有页都印一遍，印到第500页才撕下来给你，前499页全部作废。你翻页越深，印刷厂要白干的活就越多。

2.2 游标分页：基于键集的高性能方案

第二种方式是游标分页，也叫基于键集分页（Keyset Pagination）、seek method。它不再用OFFSET去跳过前面的行，而是记住“上一页最后一条数据的位置”，然后把查询条件改成只取这条数据之后的数据。

假如主键自增，ORDER BY id，上一页最后一条记录id为100，那下一页的SQL就可以写成：

SELECT id, name, create_time FROM user WHERE id > 100 ORDER BY id LIMIT 10;

由于B+树索引天然支持范围定位，这条SQL可以直接从id=100这个位置开始扫描，完全不需要丢弃前面的任何一行。即使翻到第几十万页，性能也跟翻第一页几乎一样。

代价也很明显：它不支持直接跳页。你想从第一页跳到第100页，只能一页一页翻过去，不然没有起点。所以游标分页特别适合移动端列表的“加载更多”、Feed流、订单流水这种向下滚动场景，不适合需要页码跳转的传统后台管理系统。

如果需要按时间或按其他字段排序，游标分页也能做，但思路要稍微绕一下。假设排序字段是create_time，不是唯一值，那么游标要同时记录多个字段：

WHERE (create_time < @last_time) OR (create_time = @last_time AND id < @last_id) ORDER BY create_time DESC, id DESC LIMIT 10;

这里的核心思想是：把上一页最后一条数据看成一个“键”，下一页的数据必须严格排在这个键之后，而“之后”的判定依据就是复合排序条件下的一组比较规则。这时候一定要给(create_time, id)建立复合索引，否则查询效率会非常差。

2.3 三种常见分页方式横向对比

这里把偏移式分页、游标分页、以及一种“先覆盖索引查到目标主键，再回表拿完整数据”的优化写法放到一起对比：

这三者不是互斥关系。一个系统里完全可以是：后台列表用偏移式分页，App列表用游标分页，某个需要深翻页的报表接口用覆盖索引优化。没有银弹，只有“当前场景下更合适的选择”。

3. 从零写一个分页查询

理论讲完，动手写一套完整的分页查询。我用一个最典型的用户流水表做例子，从建表、写SQL、参数换算到返回体设计，一步步说清楚。

3.1 准备一张表和基础数据

这里先建一张简化的用户操作日志表：

CREATE TABLE `user_log` ( `id` BIGINT NOT NULL AUTO_INCREMENT, `user_id` BIGINT NOT NULL, `action` VARCHAR(32) NOT NULL, `remark` VARCHAR(255) DEFAULT NULL, `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_user_time` (`user_id`, `create_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这里的idx_user_time是一个复合索引。为什么特意建它？因为实际业务里，列表查询几乎都是“某个用户下的记录按时间倒序分页”，这个索引能让WHERE user_id = ? ORDER BY create_time DESC直接走索引，避免排序临时表和全表扫描。很多人分页慢，第一步就输在索引没建对。

插入一批测试数据，比如用存储过程或者脚本循环插入几千条到几万条，覆盖不同user_id的数据，方便后面验证分页效果。

3.2 基础SQL分页实现与参数换算

先看一个按user_id过滤、按create_time倒序的物理分页查询：

SELECT id, user_id, action, remark, create_time FROM user_log WHERE user_id = 10086 ORDER BY create_time DESC, id DESC LIMIT 10 OFFSET 40;

对应的是第5页、每页10条的请求。参数换算：

• page = 5
• pageSize = 10
• offset = (5 - 1) * 10 = 40

注意SQL里的ORDER BY为什么是create_time DESC, id DESC两列？因为create_time可能重复，如果只按create_time排序，当两条记录的create_time相同时，数据库返回顺序是不确定的，翻页时就可能出现重复或漏数据。把主键id作为第二排序条件，可以保证排序结果完全稳定。这是一个很基础但很多人会忽略的点。

对应的服务端伪代码，我用一种最常见的业务逻辑写出来：

def query_log_page(user_id, page, page_size): # 参数兜底 if page is None or page < 1: page = 1 if page_size is None or page_size < 1: page_size = 20 if page_size > 100: page_size = 100 offset = (page - 1) * page_size # 分页查当前页 rows = db.execute( """ SELECT id, user_id, action, remark, create_time FROM user_log WHERE user_id = :uid ORDER BY create_time DESC, id DESC LIMIT :page_size OFFSET :offset """, {"uid": user_id, "page_size": page_size, "offset": offset} ) # 查询总条数 total = db.scalar( """ SELECT COUNT(*) FROM user_log WHERE user_id = :uid """, {"uid": user_id} ) # 计算总页数 pages = (total + page_size - 1) // page_size has_next = page * page_size < total return build_response(rows, page, page_size, total, pages, has_next)

两条SQL都用了参数化查询，不拼字符串，这是防注入的基本操作。

这里有一个容易被忽略的性能问题：count查询和列表查询各跑一次，两条SQL都会扫描索引。如果这个接口的并发很高，count查询的开销会被放大，后面第4章会详细讲怎么优化。

3.3 返回结构设计示例

给前端的响应体，我通常统一返回下面这种结构：

{ "code": 0, "data": { "list": [ { "id": 123, "user_id": 10086, "action": "login", "remark": "登录成功", "create_time": "2025-01-12 10:00:00" } ], "pageNum": 5, "pageSize": 10, "total": 1234, "pages": 124, "hasNext": true } }

list放数据，pageNum和pageSize原样回显，total给前端统计总记录数，pages用于渲染总页数或快捷跳页，hasNext用于“加载更多”场景判断是否还能继续上拉。有些团队会在响应里再加一个endId字段，专门给游标分页用，也是一种很实用的约定。整体返回结构别搞太散，字段命名统一，前端对接和后端维护都会省心很多。

3.4 与主流持久层框架集成时的注意点

现在大部分项目不会手写原生JDBC，而是用某个持久层框架加它的分页插件。用这类插件时，有几个坑要提前避开。

第一，确认分页插件是否真的走了物理分页。有些框架自带的findAll方法会把整表数据查出来，然后在应用层做内存分页。你看着代码里有page、size参数，结果实际执行SQL没有LIMIT，数据一大就内存爆炸。如何确认？打开数据库慢日志，或者直接用日志打印的SQL看有没有LIMIT字句，没有就是逻辑分页，要及时换成物理分页模式。

第二，分页插件里嵌套查询要特别小心。原始写法如果包含子查询，插件生成的count SQL可能把整个子查询也套进去count，导致count性能极差，甚至生成出语法复杂到难以维护的SQL。遇到这种情况，不要吝啬，直接手写一个专门的count SQL，反而更快更可控。

第三，关联查询的分页要分清是对主表分页还是对关联结果分页。一对多场景下，如果先join再limit，可能出现“一页里同一个主记录出现多次”的怪象。标准做法是先对主表分页查出主键，再join子表数据，最后在应用层组装。这个问题的本质是SQL执行顺序，很多人排查半天没头绪，其实早在设计SQL时就应该按主表分页。

4. 分页查询常见问题与排查实录

这一章记录一些我实际工作中遇到的分页问题，每一个都真实到能拍大腿。

4.1 COUNT查询成为接口瓶颈

分页接口往往伴随一个COUNT(*)查询，少了它前端就没法知道总页数。普通体量下表数据几十万，count查询往往一瞬间就出结果，没人注意。可一旦数据量过千万，WHERE条件过滤出来的行数又大，count查询就可能成为整个接口最慢的部分。

为什么慢？InnoDB的COUNT(*)在未加条件的全表统计下会走聚簇索引扫描，数量级越大耗时越长。加过滤条件时还得在二级索引上做范围扫描并累加count。大家可以想一下：查一页数据只需要拿几十行，可count却要把所有符合条件的行数全部数一遍，这个工作量完全不是一个量级。

我的排查思路一般是这样的：

1. 先确认慢的是count还是list。最简单的方法是手工执行两条SQL各看耗时。
2. 看count SQL有没有走合适的索引。没有索引就建索引。
3. 如果count确实走了索引还是慢，考虑换思路。

一个有效的业务级优化是：把实时精确count换成缓存近似count。很多列表组件其实只需要一个“约X万条”级别的总量提示，完全可以把count结果缓存若干秒，或者用一张单独维护的统计表、Redis计数器来存总量，不需要每次都实时数一遍。另一个思路是限制分页深度，比如只允许查询前500页，超过500页提示用户使用筛选条件缩小范围。这类业务上的限制比纯技术优化更实用，很多大型系统都是这么干的。

4.2 深分页拖垮数据库

典型的深分页问题是这样的：同一个列表接口，前10页响应只要20ms，翻到第10000页的时候，接口响应变成5秒，数据库CPU飙升。

问题根源前面讲过了，还是OFFSET太大。直接看SQL执行计划，你会发现数据库扫描的行数远超返回的行数，甚至可能达几十万行。

解决深分页的方法有好几条路：

• 场景允许的情况下，换游标分页，这是根治手段。
• 必须用偏移分页时，先利用覆盖索引查出当前页的主键，再回表拿全部字段。

第二种方案对应的SQL大致长这样：

SELECT id, user_id, action, remark, create_time FROM user_log INNER JOIN ( SELECT id FROM user_log WHERE user_id = 10086 ORDER BY create_time DESC, id DESC LIMIT 10 OFFSET 40000 ) AS tmp ON user_log.id = tmp.id ORDER BY tmp.`create_time` DESC, tmp.`id` DESC;

在MySQL 5.6以上的版本里，你还可以直接用LIMIT 10 OFFSET 40000和SQL_CALC_FOUND_ROWS？不，这里不做这个推荐，因为SQL_CALC_FOUND_ROWS本身也有很多坑。

子查询里只查主键，走idx_user_time覆盖索引，这个查询几乎不需要回表。外层再根据10个主键回表拿完整数据。对比直接LIMIT 10 OFFSET 40000，数据库在前面要扫描和丢弃的行数没有减少，但内层扫描的是更窄的索引页，单行开销小很多，性能能提升数倍甚至更多。

4.3 分页结果重复与遗漏

这类问题通常在并发写入或排序字段不唯一时出现。

假设业务里按create_time排序，但create_time精确到秒，同一秒内插入了几条新记录。用户在第一页已经看到了其中一条，等翻到第二页的时候，因为排序不稳定，这条记录挪到了第一页的位置，第二页就少了它；反过来，如果在翻页过程中有人新增或删除记录，下一页可能会出现上一页看过的数据，或者中间漏掉一条。这类问题不是查询SQL本身写错了，而是排序不稳定和并发变化共同作用的结果。

解决办法有三个方向：

1. 排序字段追加主键：ORDER BY create_time DESC, id DESC。主键唯一，这样排序结果就是确定性的，重复和遗漏的概率大幅下降。
2. 使用快照读：在事务里加上一致性快照，保证翻页过程中看到的是同一版本的数据。多数的数据库隔离级别，同事务内用普通select连续查询，能够看到一致的快照，只要把多页查询放到同一个事务里即可。
3. 业务上接受不完全一致：很多列表本身不需要严格分页一致，只要没有明显重复，用户感知不到，可以不用过度设计。

我自己的原则是：先保证排序字段加主键，这一步几乎零成本，能解决70%的乱序问题；剩余并发一致性需求，再根据业务重要性决定要不要引入成本更高的方案。

4.4 用EXPLAIN快速定位慢SQL

排查分页问题，最常用的工具就是EXPLAIN。看一条慢的列表SQL：

EXPLAIN SELECT id, user_id, action, remark, create_time FROM user_log WHERE user_id = 10086 ORDER BY create_time DESC, id DESC LIMIT 10 OFFSET 40000;

返回结果里重点看三列：

• type：访问类型，const、ref、range都算合理，ALL就是全表扫描，要警惕。
• key：实际使用的索引，没值说明这条SQL没用上索引。
• rows：预估扫描行数，这个数字越大，说明SQL越“笨”。

假如你看到type = ALL、rows = 5000000，那就说明条件列没走索引，或者你写的条件让索引失效了。解决办法通常是检查复合索引的字段顺序、是否存在对索引列做函数运算、隐式类型转换等。

实际排查询时不要只看一列，要把三列结合起来看。比如type是ALL但rows只有100，表本身才100行，那这个全表扫描没什么好担心的。反过来，type是ref但rows=2000000，说明虽然走了索引但过滤效果很差，也要继续优化。EXPLAIN结果是用统计数据估算的，不是精确值，但对于定位问题已经足够。

5. 大数据量下的分页优化策略与个人经验

前面几章主要讲原理和排查，这一章把优化手段系统化地梳理一遍，并按场景给出建议。

5.1 用覆盖索引让回表消失

所谓覆盖索引，就是查询要的所有列都在索引里，数据库只需要扫描索引，不需要再回表查聚簇索引。举个例子，业务里只关心某个用户的create_time和action，那我可以建一个(user_id, create_time, action)的复合索引。查询：

SELECT user_id, create_time, action FROM user_log WHERE user_id = 10086 ORDER BY create_time DESC LIMIT 10 OFFSET 40;

这时候索引idx_user_time_action本身已经包含了所有需要返回的列，MySQL可以直接在索引上完成排序和分页，不需要回表。得益于二级索引通常比聚簇索引小很多，同样的内存可以缓存更多索引页，性能提升非常明显。

很多深分页优化都建立在覆盖索引之上，包括前面说的“先查主键再回表”，本质也是让分页扫描尽量落在窄索引上，缩小扫描开销。

5.2 滚动加载场景直接用游标分页

如果业务形态是App端“加载更多”、Feed流、消息列表这类无需跳页的滚动场景，直接放弃OFFSET分页，改用游标分页。和传统偏移分页相比，它的性能优势随着翻页深度拉大而越来越明显。

实现上，前端需要记录上一页最后一条数据的游标值，比如最大id，翻下一页时把它带给后端：

def query_log_cursor(user_id, cursor_id, page_size): if cursor_id is None: cursor_id = 0 rows = db.execute( """ SELECT id, user_id, action, remark, create_time FROM user_log WHERE user_id = :uid AND id > :cursor_id ORDER BY id ASC LIMIT :page_size """, {"uid": user_id, "cursor_id": cursor_id, "page_size": page_size} ) next_cursor = rows[-1]["id"] if rows else None return rows, next_cursor

注意这里只能用id > cursor_id，不能用id >= cursor_id，否则会重复返回上一页的最后一条。每次返回时把next_cursor一并交给前端，前端再作为下个请求的参数传进来。

我实测过同样的表，同样是翻到很深的页，OFFSET分页需要几百毫秒的时候，游标分页稳定在个位数毫秒。前提是id这个主键上天然有序，而B+树索引的定位能力保证了这个>查询几乎不消耗额外成本。代价是放弃页码跳转，这个取舍在移动端场景几乎是无感的。

5.3 业务层面的一些巧劲

除了SQL优化，业务层也能帮上忙：

限制查询深度。很多后台管理系统的实际用户根本不会翻到100页以后。与其在SQL上死磕深分页，不如在产品上约定“最多只能查询前100页”。这是成本最低、效果最直接的手段。

只返回需要的字段。列表接口经常把一张表的几十个字段全都select出来，一些大字段、埋点数据根本用不上。把不必要的字段从查询中去掉，减少回表和网络开销，对分页性能有很大帮助。

冷热数据分离。流水类数据会越积越多，但用户高频查询的往往只是最近几个月。把历史数据定期归档到历史表或归档库，主表只保留热数据，分页查询也就天然变快了。这个思路在很多日志、交易、流水系统里都在用。

total的降级策略。对于超大结果集，精确total会非常昂贵。可以让接口先走缓存，缓存过期后异步刷新，前端接受总量是“约等于”的。实际体验差别不大，数据库的负担能降一大截。

5.4 避坑清单：分页场景选型速查

最后整理一份我平时会参考的场景速查表：

个人经验来说，遇到分页慢，先别急着上复杂的优化方案，按顺序走：

先看索引在不在，再看排序稳定不稳定，然后确认是不是OFFSET过大，最后判断场景能不能换游标分页。把这三个问题排查完，90%的分页性能问题都能定位到根因。

我自己踩过最深的坑就是早期所有列表统一用OFFSET分页，到了千万级数据量之后，一条看似简单的后台查询就能把数据库拖垮。后来逐步引入游标分页和覆盖索引优化，才真正扛住了流量。分页查询这个东西，入门只需要一行LIMIT，想做好，需要的是对整个数据读取链路的理解。希望这篇分享能帮你避开我踩过的那些坑，也欢迎你按自己项目的场景，把这里的方案改造成更合适的做法。