如果你维护过任何一个日活过万的APP后端早晚会遇到同一个问题用户一直往下滑列表一直加载突然某一次请求响应时间从50ms涨到了800ms数据库CPU也跟着飙了上去。打开慢查询日志一看罪魁祸首多半就是那条ORDER BY id DESC LIMIT 1000000, 20。这不是段子是我自己踩过的坑。传统分页OFFSET分页在小数据量时看起来很美好但当数据量走到百万千万级、并发再上来一点它的问题就会集中爆发。今天的这篇内容不绕圈子就讲一件事用滚动分页查询也叫游标分页、Keyset Pagination替换掉OFFSET分页从原理到实操从单字段游标到复合游标包括我实际排查过的线上问题一次说清楚。1. 为什么传统分页撑不住了——OFFSET分页的三大痛点1.1 深分页的性能断崖先把OFFSET分页的执行逻辑拆开看。一条SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 1000000数据库到底干了什么以MySQL InnoDB为例它需要先根据ORDER BY created_at DESC找到符合条件的全部行或者尽量利用索引然后从第一个结果开始数数到第1000020行时只把最后20行返回给你。前面那1000000行数据不是不查而是查出来又丢掉这个过程就是全表扫描或全索引扫描的资源黑洞。这带来一个很致命的现象分页深度和查询耗时成正比。第一页可能3ms第一百页可能300ms第一千页直接奔着秒级去。用户真的会翻到那么深吗取决于业务形态。商品列表可能不会但交易流水、日志记录、消息中心用户真的能翻出几百页甚至上千页。更头疼的是这种慢查询还不是固定发生在某个时间点而是随着数据累积越来越严重直到某一天DBA找上门问你那条分页SQL怎么回事。可以用一个生活化类比OFFSET分页就像你在一本没有目录、没有页码的厚书里找第1000页的内容你只能从第1页开始翻翻到第999页丢掉才能看到第1000页。而你每一次翻页都要重新从第1页开始翻。滚动分页则是给这本书装了页码标签上次读到第999页这次直接把书翻到那个标签位置接着往下读就行。1.2 数据变动带来的重复与遗漏OFFSET分页还有一个隐蔽但杀伤力极大的问题当分页过程中有新数据插入或旧数据删除时用户看到的列表会出现重复或缺失。举个实际例子订单列表每页10条第一页显示的是第1~10条订单按ID倒序。这时候产生了一笔新订单它排到了最前面。用户下拉请求第二页时OFFSET 10取到的是原第11~20条吗不是。因为新订单把位置挤占了第二页变成了原第9~18条。原第9、第10条被重复展示了。删除的情况更常见。后台有人删了一条历史订单整个列表的位置全部前移一位用户接着往下翻就会看到之前已经展示过的一条记录再次出现在新页码里。这种重复和遗漏在信息流里尤其致命用户会以为你数据有问题甚至会误以为是“新内容”而重复操作。注意有人在产品层面说“强缓存”能解决这个问题或者“下拉刷新时重新拉第一页”。这些方案治标不治本缓存过期后问题依然存在而且下拉刷新本身也是要重新跑一遍查询的。根子在于OFFSET这个语义在数据持续变化时本身就是错的它不是缓存能救回来的。1.3 内存与排序开销的隐性成本第三个痛点比较隐蔽不深挖执行计划很难发现。当ORDER BY的字段没有索引覆盖时MySQL需要先做filesort文件排序。配合OFFSET深度增加排序的数据集是全部符合条件的行而不是“跳过多少行之后的那部分”。数据量大了以后文件排序会把临时数据写到磁盘上出现Using temporary和Using filesort。内存层面的问题表现为什么缓冲区膨胀、临时表空间增长有时候一个深分页查询就能把一台4G内存实例的临时表空间撑爆。生产环境里我见过最夸张的一次一个深度OFFSET查询产生了1.8G的磁盘临时表直接拖垮了同库上的其他业务。这和数据量、索引结构、排序字段都有关系但概率最大的触发点就是OFFSET数值太大。还有一个不那么起眼的问题为了知道“总共有多少页”很多项目习惯用SELECT COUNT(*)统计数据总量。这个操作在大表上同样是全表扫断言。滚动分页天然不需要总页数列表是无限往下滚的这反而帮你去掉了一个性能包袱。2. 滚动分页的核心原理——用索引记住位置而不是跳过数据2.1 游标分页的逻辑本质滚动分页的别名很多Keyset Pagination、Seek Method、Cursor Pagination。不管叫什么核心思想只有一句话每次查询携带上一次返回结果中最后一条记录的位置信息下一页直接从那个位置之后开始查。举个例子。第一页查询SELECT * FROM news WHERE status 1 ORDER BY id DESC LIMIT 20;返回结果中最后一条的ID 10500。第二页查询就带上这个值SELECT * FROM news WHERE status 1 AND id 10500 ORDER BY id DESC LIMIT 20;注意不是OFFSET 20而是WHERE id 10500。数据库通过主键索引直接定位到ID10500这条记录然后反向扫描索引取出后面20条。整个查询只涉及索引定位加20行数据扫描和OFFSET深度一点关系都没有。这就是“滚动”二字的含义位置信息随着每一页的返回结果滚动前进客户端不需要关心总共有多少数据也没有页码的概念只有“下一页”和“没有更多了”。所以你会发现几乎所有移动端信息流接口都长这样{ list: [], next_cursor: MTA1MDA, has_more: true }next_cursor就是游标has_more告诉客户端是否继续请求。这就是滚动分页在接口层的标准表达。2.2 为什么必须配合索引这里必须强调一个先决条件游标分页的性能完全依赖于游标字段上的索引。没有索引WHERE id 10500 ORDER BY id就会退化成全表扫描加排序比OFFSET还慢。为什么因为数据库需要先找出所有符合条件的行没有索引就只能逐行判断然后排序再取前20条。这和OFFSET的区别只是“少丢了一些行”但扫描和排序的开销并没有本质降低。有了索引之后id 10500可以被索引范围扫描直接处理。InnoDB的BTree索引本身是有序的数据库定位到10500这个索引项沿着叶子节点的链表往“更小”方向扫20条完事。整个过程的时间复杂度是O(logN 20)和总数据量没有关系和游标位置也没有关系。所以判断一个滚动分页SQL写得好不好唯一标准就是看执行计划里有没有走Range有没有Using index condition这类好信号。写得不对plan上出现type: ALL或者额外出现Using filesort那就说明游标没用对。2.3 游标字段怎么选——三条硬标准游标字段的选择直接决定这个方案能不能落地。我总结了三条硬标准缺一不可唯一性。游标字段必须能唯一确定一条记录。如果一个字段的值不唯一就会出现“上一页最后一条记录在下一次查询里被自动跳过”或者“多条记录同时满足游标条件”的问题。自增ID天然满足时间戳不满足同一毫秒也可能有两条数据所以时间戳通常要搭配ID做复合游标。有序性。这个字段的排序规则必须和业务需要的排序规则一致。如果你的列表是按发布时间倒序展示那游标最好就是发布时间按ID倒序展示游标就是ID。游标字段和ORDER BY字段不一致的时候就只能先排序后过滤无法走索引范围扫描。不可变性。字段一旦写入就不能再改变。反例是“状态字段”不适合做游标一条记录从“待审核”变更为“已发布”后游标位置就错位了翻页时这条记录可能会“凭空消失”。所以在实际工程里游标最常见的选择就是自增主键ID——永远不会变永远递增一票满足全部条件。如果业务排序维度很单一比如一直按创建时间倒序那自增ID作为一个单调递增的字段也可以间接代表创建时间顺序。这时候直接用ID做游标就够了简单可靠。只有排序条件不是“时间倒序”时才需要考虑把排序字段拼进游标。3. 实操从单字段游标到复合游标的完整实现3.1 场景建模与数据准备用一个典型业务来串整个实操过程一个内容社区的信息流列表。数据表结构大概是这样的CREATE TABLE articles ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, author_id INT UNSIGNED NOT NULL, title VARCHAR(200) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, status TINYINT NOT NULL DEFAULT 1, PRIMARY KEY (id), KEY idx_created_at (created_at), KEY idx_status_created (status, created_at) ) ENGINEInnoDB;业务需求是按发布时间倒序展示已发布的文章用户无限向下滚动加载更多。接口接收两个参数cursor上一页最后一条的编码值和limit每页数量默认20最大50。这里的排序字段是created_at但它作为DATETIME类型精度是秒级。同一秒内可能出现两篇甚至多篇文章所以复合游标是必需的。有一点需要提前说明为了演示通用方案下面复合游标的例子用(created_at, id)组合做游标如果你的业务可以接受“按ID倒序近似等于按时间倒序”那直接用单字段ID会更简单性能也会更好。两种方案的取舍我在3.4小节会详细对比。3.2 单字段游标自增主键的滚动查询如果业务上直接按ID倒序SQL简单粗暴-- 第一次加载 SELECT * FROM articles WHERE status 1 ORDER BY id DESC LIMIT 20;拿到结果后取最后一条的id记为last_id。响应里返回cursor last_id。-- 后续翻页 SELECT * FROM articles WHERE status 1 AND id :last_id ORDER BY id DESC LIMIT 20;这个方案的执行计划非常漂亮WHERE id ?走主键索引Range扫描ORDER BY id也不需要额外排序因为索引本身就是有序的。数据量哪怕几千万翻到“第无限页”查询成本也稳定在毫秒级。我实测过一个3500万行的订单表OFFSET 1000000 LIMIT 20耗时1.8秒左右换用id 50000000 LIMIT 20后耗时稳定在8~20ms。差距不是一星半点是量级的差距。有一点需要注意客户端传回来的游标必须做基础校验。防手滑也防恶意请求拼接一个超大ID或负数你可以限制last_id必须在合理区间0 ~ 当前最大ID。这种校验在ONLY_FULL_GROUP_BY那个年代大家可能不重视但在公网环境下这点成本必须花。3.3 复合游标按时间排序时的正确写法按created_at倒序时游标必须由(created_at, id)两个字段共同组成。第一页查询SELECT * FROM articles WHERE status 1 ORDER BY created_at DESC, id DESC LIMIT 20;返回结果中取(last_created_at, last_id)。这里要注意排查这条SQL之前先确认idx_status_created能覆盖排序。索引列顺序是(status, created_at)所以等值条件status1加上排序字段created_at DESC是可以直接在索引内完成定位的不需要额外的filesort。第二页查询SELECT * FROM articles WHERE status 1 AND (created_at :last_created_at OR (created_at :last_created_at AND id :last_id)) ORDER BY created_at DESC, id DESC LIMIT 20;这个OR条件背后的逻辑很直白往下翻页时下一页的所有数据要么创建时间早于游标时间要么创建时间相同且ID小于游标ID。写成一个条件的好处是语义严谨缺点是对优化器不友好——OR在某些版本里可能构造出索引合并index merge反而多一次索引扫描。更优雅的写法是用行值比较Row Value ComparisonPostgreSQL和MySQL 8.0以上直接支持-- PostgreSQL / MySQL 8.0 SELECT * FROM articles WHERE status 1 AND (created_at, id) (:last_created_at, :last_id) ORDER BY created_at DESC, id DESC LIMIT 20;这个写法等价于上面那个OR版本但优化器能更准确地走索引范围扫描。如果你的MySQL版本低于8.0只能用OR写法或者改写为UNION不影响正确性只是性能上限有细微差异。还有一个小坑(created_at, id) (:last_created_at, :last_id)这个比较的方向必须和ORDER BY方向一致。上面查询是ORDER BY created_at DESC, id DESC所以游标比较方向就是如果是升序就换成。方向反了结果就是空集或者数据乱套。3.4 接口层设计向前兼容的参数约定后端SQL是核心但接口层的设计同样重要。一个成熟的分页接口至少要包含三个字段字段类型含义listArray当前页数据next_cursorString游标值可能为空has_moreBoolean是否还有更多数据next_cursor在接口层最好是不透明字符串。所谓“不透明”就是客户端拿到后原样传回来不需要也不应该解析里面的内容。实践中我习惯将游标值进行简单的编码比如JSON序列化后Base64URL编码再加签名防篡改// 游标生成 { created_at: 2024-06-01 12:00:00, id: 10500 }编码后变成一个类似eyJjcmVhdGVkX2F0IjoiMjAyNC0wNi0wMSAxMjowMDowMCIsImlkIjoxMDUwMH0的字符串。这样有两个好处一是避免客户端把参数顺序、格式搞乱二是便于服务端做校验防止非法游标注入。接口的返回逻辑可以这样约定// 第一页响应 { list: [...], next_cursor: eyJjcmVhdGVkX2F0IjoiMjAyNC0wNi0wM..., has_more: true }前端的处理逻辑也非常直白has_more为true时把列表数据追加到底部并用next_cursor作为下一次请求的入参has_more为false时移除“加载更多”的触发条件展示“没有更多了”。我特别建议在接口文档里明确一点limit不传时用默认值传超过最大值时回退到最大值。这不是什么高深技术但是经常被忽略会导致接口被刷出超大响应包。3.5 单字段游标与复合游标的取舍上面两种方案各有适用场景我直接给结论列表排序规则就是固定按ID倒序或者时间顺序与ID单调相关用单字段ID游标。简单、快、不易出错。绝大多数内容信息流都满足这个条件因为自增ID本身就和插入顺序强相关。排序规则依赖业务维度按热度、按价格、按距离、按最后活跃时间需要把排序字段和ID拼成复合游标。热度、价格这些字段会有并列值必须用ID兜底。排序字段本身不唯一且更新频繁比如“按点赞数倒序”点赞数每分钟都在变这种场景滚动分页的游标语义会崩坏数据变动导致位置错乱更适合“先算好快照分数再分页”或引入额外排序表这不展开属于另一个话题。表用一张对比图总结对比维度单字段ID游标复合游标SQL复杂度极低中等排序灵活性弱仅限ID顺序强可按业务字段排序性能最优略逊但深分页下依然远优于OFFSET适用业务信息流、公告、消息中心按时间/价格/热度排序的列表从我个人经验来说能不复合就不要复合。单字段ID能解决问题就别为了“看起来更通用”引入多字段游标。加一个字段意味着加一份出错的可能性。4. 常见问题与排查技巧实录4.1 分页结果出现重复或漏数据这是滚动分页最常遇到的问题而且往往不是SQL写错是游标方向或者比较符号搞反了。先说漏数据第一页返回最后一条是(created_at: 2024-06-01 12:00:00, id: 10500)第二页SQL写成created_at :last_created_at那必然漏掉同一时刻之后的所有数据。要记住列表是倒序展示的所以游标条件是小于。每次写完SQL自己先在本地用一个100条预置数据的分页脚本自测三页以上。再说重复数据如果只用了created_at :last_created_at而没有搭配ID兜底且同一秒内发布了多条文章就会出现这样一个现象——第一页的最后一条记录和第二页的第一条记录是同一篇。因为游标唯一性不够LIMIT从那个时间点开始截取时把上页已经展示过的“同一时刻的其他记录”又捞了回来。排查方法很简单把第一页和第二页拼接起来检查有没有ID重复。如果有那就检查游标里是否包含了ID以及是否用了行值比较的写法。注意如果你用了(created_at, id) (:last_created_at, :last_id)写法MySQL 8.0优化器是比较聪明的但如果你在MySQL 5.7用OR写法务必用EXPLAIN看执行计划确认不是ALL全表扫。4.2 游标字段没有索引导致慢查询这个问题通常出现在“前期表小没建索引后期数据量上来了才意识到要用游标分页”的过渡期。比如表的唯一索引是主键ID但你的排序字段是created_at此时如果用created_at做游标而created_at上没有索引那么WHERE created_at ? ORDER BY created_at就是全表扫描加文件排序。这里要补充一个很容易混淆的概念在MySQL里DATETIME类型上的普通二级索引是可以支持范围扫描的但前提是你必须把查询条件和排序字段都塞进同一个索引里。我上面建表语句里的idx_status_created(status, created_at)就是为此设计的等值匹配status再用created_at做范围定位MySQL可以直接在索引树上完成整个查询连回表都只需要取需要的20行。排查慢查询的通用流程是这样的打开慢查询日志找到滚动分页相关SQLEXPLAIN执行计划看type是否为range、key是否为预期索引如果看到type: ALL或Using filesort优先建联合索引把游标字段作为索引最后一列参与等值匹配或排序索引建完再复测确认执行计划变化。4.3 时间精度陷阱与字符串排序问题这是一个特别隐蔽的坑。假设created_at是DATETIME类型其精度是秒级。如果你的业务插入并发特别高同一秒插入多条记录是家常便饭。此时单字段created_at作游标必然出问题不只是重复展示还可能直接跳过一整批“同一秒的记录”。更坑的是有些项目把时间字段设计成了VARCHAR类型存储比如2024-06-01 12:00:00存成字符串。字符串排序和DATETIME排序在某些格式下是一致的ISO格式可以按字典序排但一旦出现timestamp毫秒值、时区偏移量、或者格式不统一2024-6-1和2024-06-01混用排序结果就完全乱掉。正确做法只有一个设计表结构时用DATETIME(3)毫秒精度或BIGINT存毫秒时间戳并在应用层统一写入格式游标再从同一字段读取。不要尝试在SQL里用DATE_FORMAT、CAST去临时格式化那会导致索引失效。4.4 接口层状态管理无状态API与游标编码最后一个高频问题是开发人员误以为滚动分页需要在服务端记录“会话状态”于是引入了Session、Redis缓存上一页的查询条件甚至把游标直接存在内存里。这种设计违背了无状态API的原则一旦服务端重启或负载均衡到另一台机器游标失效用户下拉就报错。正确做法是游标必须完全由客户端持有服务端只负责根据游标解码出查询条件。也就是我上面说的Base64URL编码加签名。服务端在解码后能做简单校验但不依赖任何会话状态。这样接口天然支持水平扩容服务器重启、新增节点都不影响游标有效性。签名校验我多说一句不需要用复杂加密HMAC-SHA256就足够了。生成游标时用密钥签名解析时验签签名不合法直接返回参数错误。这样客户端无法拼造任意时间戳来探测数据边界。4.5 一个没法绕过去的局限不能随机跳页滚动分页最大的产品侧局限是你失去了“直接跳转到第N页”的能力。信息流、消息中心这类产品不需要随机跳页但如果你做的是PC端后台管理系统用户习惯用页码跳转、需要精确知道“这是第几页”那滚动分页就不适合。这不算缺陷是产品形态决定的。很多团队在后台管理系统里继续用OFFSET分页数据量控制在百万级以内索引优化做得够好也不是不行。真正的工程智慧是知道何时该用哪个工具而不是追求统一方案。我个人的风格是面向C端的高并发信息流一律滚动分页面向B端的后台列表如果数据量可控可以先留着OFFSET等出现慢查询了再改。如果你确实需要“既能滚动又能跳页”的折中方案可以预先把所有符合条件的ID存进Redis的ZSET用ZSET的rank做跳页然后用ID去查详情。这个方案适合ID集合不大、实时性不是极端敏感的场景属于另一种取舍。5. 最后说点实在的滚动分页不是什么新概念SQL Server 2012就开始推OFFSET FETCHPostgreSQL和MySQL社区也一直在推广Keyset Pagination但很多团队直到线上出现慢查询才开始重视它。从我实际经历来看改造本身并不复杂先确认排序字段和游标字段建联合索引重写两条SQL调整接口层的响应结构前端把页码改成游标传递。真正花时间的往往是想清楚业务排序规则、处理好时间精度、把接口层防篡改做好。如果你正打算改造我建议先从一张大表、一个列表接口开始试点不要一上来全量切换。跑一段时间对比一下接口P99延迟和数据库慢查询数量用数据说话再逐步推广。这比拍脑袋全量重构要稳妥得多。