分页查询原理与优化：从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直接置为1pageSize最小值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条对应page3、pageSize10的场景。大多数人对它的理解是数据库很快就定位到了第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可以直接从id100这个位置开始扫描完全不需要丢弃前面的任何一行。即使翻到第几十万页性能也跟翻第一页几乎一样。代价也很明显它不支持直接跳页。你想从第一页跳到第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 三种常见分页方式横向对比这里把偏移式分页、游标分页、以及一种“先覆盖索引查到目标主键再回表拿完整数据”的优化写法放到一起对比维度偏移式分页 (OFFSET)游标分页 (Keyset)覆盖索引回表优化实现难度极低中中小数据量性能好好好大数据量深翻页差越翻越慢稳定几乎恒定较好优于直接OFFSET跳页支持支持不支持支持排序字段要求无特殊要求必须有稳定唯一键需要覆盖索引设计适用场景后台管理、数据量中等移动端加载更多、Feed流深翻页但必须跳页的业务这三者不是互斥关系。一个系统里完全可以是后台列表用偏移式分页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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这里的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 5pageSize 10offset (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却要把所有符合条件的行数全部数一遍这个工作量完全不是一个量级。我的排查思路一般是这样的先确认慢的是count还是list。最简单的方法是手工执行两条SQL各看耗时。看count SQL有没有走合适的索引。没有索引就建索引。如果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本身写错了而是排序不稳定和并发变化共同作用的结果。解决办法有三个方向排序字段追加主键ORDER BY create_time DESC, id DESC。主键唯一这样排序结果就是确定性的重复和遗漏的概率大幅下降。使用快照读在事务里加上一致性快照保证翻页过程中看到的是同一版本的数据。多数的数据库隔离级别同事务内用普通select连续查询能够看到一致的快照只要把多页查询放到同一个事务里即可。业务上接受不完全一致很多列表本身不需要严格分页一致只要没有明显重复用户感知不到可以不用过度设计。我自己的原则是先保证排序字段加主键这一步几乎零成本能解决70%的乱序问题剩余并发一致性需求再根据业务重要性决定要不要引入成本更高的方案。4.4 用EXPLAIN快速定位慢SQL排查分页问题最常用的工具就是EXPLAIN。看一条慢的列表SQLEXPLAIN 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但rows2000000说明虽然走了索引但过滤效果很差也要继续优化。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 避坑清单分页场景选型速查最后整理一份我平时会参考的场景速查表业务场景推荐方案为什么后台管理列表数据量百万以内偏移式分页合理索引实现简单支持跳页性能可控后台管理列表深翻页但必须支持跳页覆盖索引先查主键再回表能显著降低深翻页扫描成本App/移动端加载更多游标分页不跳页、深翻页性能恒定大数据量流水报表游标分页冷热分离异步total兼顾查询性能和统计需求千万级用户列表筛选复杂筛选条件精简覆盖索引限制深度复杂筛选下深翻页优化空间有限从产品上限制最有效个人经验来说遇到分页慢先别急着上复杂的优化方案按顺序走先看索引在不在再看排序稳定不稳定然后确认是不是OFFSET过大最后判断场景能不能换游标分页。把这三个问题排查完90%的分页性能问题都能定位到根因。我自己踩过最深的坑就是早期所有列表统一用OFFSET分页到了千万级数据量之后一条看似简单的后台查询就能把数据库拖垮。后来逐步引入游标分页和覆盖索引优化才真正扛住了流量。分页查询这个东西入门只需要一行LIMIT想做好需要的是对整个数据读取链路的理解。希望这篇分享能帮你避开我踩过的那些坑也欢迎你按自己项目的场景把这里的方案改造成更合适的做法。

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

相关推荐

MySQL事务隔离级别实战：脏读、不可重复读、幻读复现与锁机制解析

系统分区原理与实战：从MBR/GPT到SSD对齐和双系统部署

字节把豆包、飞书、火山引擎合三为一：AI办公大战开打，开发者连夜补Agent课

更多资讯

给PL/0增加%和^：运算符优先级与结合性的完整实现指南

如何快速安装Calabash-Android：从环境准备到零踩坑的完整教程

南航编译原理课程设计：Java手写词法与语法分析器实战指南

用Python实现本地模拟横向联邦学习：从FedAvg到单机可复现实验

Java+SSM+Django仓库管理系统：从业务建模到并发控制实践

YOLO实时物体检测实战：从齿条螺栓螺母裂纹数据集到TensorRT部署

今日更新

再见，SSE！你好，Streamable HTTP！轻松开发 Streamable HTTP MCP Server 并接入 TaoToken

工业软件标准化路线图：国产替代的落地施工图

VCMI安卓版实操指南：原生运行英雄无敌3的3步技术落地

本周精选

CMOS设计三大物理效应：体效应、沟道长度调制与亚阈值导电

微信小程序在线阅读毕设全解析：Java后端+MySQL+避坑实战

Android音频问题五层分析法：从App到Codec的系统化诊断

本月热门

我发现了一个新思路：用 Remotion + Claude Code 像写代码一样自动化生成短视频

Windows下 Codex 中 Chrome 和 Computer Use 插件不可用问题排查及解决参考方式：TaoToken 统一 Key 配置与验证

2026 大模型集体涨价：用 Python 做企业 Token 成本测算与选型避坑（附配置）

读完文章，想聊聊您的网站？