分页查询几乎所有后端开发都写过LIMIT 10 OFFSET 200这种 SQL 闭着眼都能敲出来。但真正在生产环境扛过几年流量的人看到分页这两个字心里都会多一根弦——它远没有表面看起来那么人畜无害。我接手过一个交易系统的订单列表页平时 P99 也就 200ms某天晚上大促压测页码翻到 50 页以后接口直接飙到 30 秒超时数据库 CPU 被打满连带其他核心服务一起抖。后来排查原因就是最普通的ORDER BY create_time DESC LIMIT 20 OFFSET 1000。从那以后我养成了一个习惯只要代码评审里出现分页查询我一定追着问一句——你这个分页稳吗这里说的稳定性很多时候比单纯性能慢更麻烦。性能问题至少表现明显慢就是慢你能看见稳定性问题则是间歇性的、上下文相关的明明昨天还正常今天同样的参数就翻车了而且翻车的姿势还千奇百怪。分页查询的稳定性陷阱本质上是把性能退化、数据一致性、环境依赖三件事搅在了一起。这篇文章我不打算只讲 SQL 优化而是要站在一个完整系统的视角把分页查询从性能、数据、架构三个层面拆开看然后给你一套能落地、能根治的组合方案。适合正在做业务后端、被接口性能和数据错乱问题折腾过的同学参考也适合准备做系统重构的团队拿来当检查清单。1. 内容整体设计与思路拆解1.1 表面正确的分页 SQL其实藏着线性退化的性能陷阱先聊聊最基础的场景。大多数业务系统里的分页查询写出来就是SELECT * FROM t_order ORDER BY create_time DESC LIMIT 20 OFFSET 1000。在小数据量、低并发的情况下这条 SQL 跑起来毫无压力因为 MySQL 的优化器可能直接走了覆盖索引或者内存排序数据量小到可以忽略不计。但一旦表数据量超过百万页码再往后翻问题就出来了。这里要理解一个关键点OFFSET 1000的含义不是从第 1001 行开始读而是先从表里把前 1000 行数据都捞出来然后全部丢掉再往后取 20 行。你可以把它想象成在一本厚厚的书里找第 100 页的内容图书管理员不是直接翻到第 100 页而是从第 1 页开始一页一页地翻过去数到第 100 页才停下来。前面的 99 页他全都翻过了但一页都不会给你看。页码越深白白翻过的页数越多耗时就越长——这就是所谓的线性退化。有人会问那给create_time加上索引不就行了吗这是个很普遍的误解。索引确实能帮助 MySQL 快速定位到第一条create_time DESC的记录但OFFSET的处理机制决定了数据库依然需要从索引的第一个符合条件的叶子节点开始沿着链表向后遍历 1000 次每次遍历都伴随一次主键回表才能拿到你要的那 20 行数据。换句话说索引解决了排序的效率但没有解决跳过的效率。深度分页的时候回表的次数是OFFSET LIMIT而不是LIMIT这就导致性能随着页码增大而稳定地恶化。1.2 真正的稳定性陷阱藏在性能之外的三层问题里如果仅仅只是慢那工程师早就想办法优化了。分页查询真正让人头疼的是稳定性问题通常以三种面目出现而且经常一起发作。第一层是数据漂移问题。你在第 1 页看到了一条记录等翻到第 3 页的时候这条记录又出现了一次或者反过来第 2 页上有一条记录翻到第 3 页就找不到了。原因很简单在两次查询的间隙有新的数据插入或者有旧的数据被删除、修改了排序字段的值。举个例子一个按create_time DESC排序的商品列表用户在第 1 页看到第 20 条商品后后台突然又上架了 3 个新商品。等用户翻到第 2 页时因为排序位置被新商品挤占原本在第 21 到 40 条的商品会整体后移导致第 2 页重新出现了第 1 页已经看过的内容。这不是 SQL 写错了而是数据集合在两次查询之间发生了变化而分页查询默认假设数据集合是静态的。第二层是环境一致性陷阱。现在绝大多数中大型业务系统都是读写分离的架构主库负责写入从库负责查询。为了减轻主库压力列表页的分页查询通常会打到从库上。问题在于主从复制是有延迟的哪怕正常情况下延迟只有几十毫秒。用户在前端刚提交了一个订单前端立刻跳转到订单列表第 1 页结果发现刚下的单不见了——因为查询请求走的是从库而那条新记录还没来得及从主库同步过来。这种写入后立即查询的场景在分页接口上表现得尤其明显而且难以通过加索引来解决。第三层是索引与写入模式的稳定性问题。你以为索引建好了就万事大吉但建索引的字段和主键的设计方式本身就会影响查询的稳定性。比如用 UUID 作为主键这是一种随机散列的值InnoDB 在插入新记录时索引页需要频繁地做页分裂和合并产生大量碎片。后果就是同一张表的查询今天走索引耗时 50ms明天同样的查询却要 200ms性能抖动剧烈。这种不稳定是藏在底层的你不用EXPLAIN仔细看根本发现不了问题出在主键设计上。1.3 根治思路把翻页思维切换成摘取思维理解了上面三层陷阱根治方案也就自然浮出水面了。核心思路是从翻页切换到摘取。传统的OFFSET分页本质上是一种我要第 N 页的东西你帮我跳到那一页的思维。为了实现跳这个动作数据库必须消耗大量资源去数前面所有的行——这就是所有麻烦的根源。而另一种分页思路——Keyset 分页游标分页 / Seek Method——则完全不同。它不关心页的概念只认上一次看到的那条记录的位置。SQL 会写成WHERE create_time 2024-05-01 10:00:00 ORDER BY create_time DESC LIMIT 20意思是给我拿排在最后一条已展示记录后面那 20 条。数据库可以直接通过索引精确定位到create_time 2024-05-01 10:00:00的位置然后从那里开始顺序往下取 20 条。整个过程中需要扫描的数据量和翻页深度完全无关永远只和本次要取多少条相关。这就是它性能稳定的核心原因。从项目整体设计的角度来说我建议把所有分页需求先做一次分类如果是给用户后端管理用的、数据量可控的表格可以继续用OFFSET但凡是 C 端用户直接访问、数据量可能快速增长、对响应时间和数据一致性有要求的列表页都必须走 Keyset。这次重构的思路就是基于这个分类原则展开的。2. 核心细节解析与实操要点2.1 Keyset 分页的正确姿势单字段排序要小心多字段排序必须有锚点Keyset 分页的原理说起来简单但真正落地写 SQL 的时候坑比想象中多。第一版改造的时候我们只用了最简单的写法WHERE id ? ORDER BY id DESC LIMIT 20这对于单字段自增主键排序的场景是完全够用的但现实业务里几乎没有哪个列表是按主键排序的大家习惯按create_time或者update_time这样的业务时间字段排。这就引出了 Keyset 分页最经典的陷阱排序字段重复。假设订单表里有 100 条记录它们的create_time都精确到秒其中第 40 到第 60 条恰好是同一秒内创建的。用户在翻第 2 页的时候最后一条记录的create_time是2024-05-01 10:05:00于是第 3 页的 SQL 就写成WHERE create_time 2024-05-01 10:05:00。结果呢这 20 条同一秒创建的记录里只要查询条件稍微偏一点点就会发生两种情况要么一条不漏地全部带上要么全部丢掉。因为在数据库看来create_time 2024-05-01 10:05:00根本不包含10:05:00这一秒的数据而上一页最后展示的那条数据恰好就是10:05:00于是这一秒的数据整体丢失了。正确的做法是引入一个唯一且稳定的锚点字段作为次级排序条件。MySQL 里最方便的锚点就是自增主键id。完整的多字段 Keyset 分页写法是-- 按 create_time 倒序id 倒序作为 tie-breaker SELECT * FROM t_order WHERE (create_time #{lastCreateTime}) OR (create_time #{lastCreateTime} AND id #{lastId}) ORDER BY create_time DESC, id DESC LIMIT 20;这样写MySQL 可以完美命中(create_time, id)这个联合索引。排序字段create_time和锚点字段id组合成了一个序列化的、严格递增的游标任何一行数据的排序位置都是唯一的不会因为字段重复而出现边界数据被漏掉或重复读取的问题。这里要特别强调一个实操细节锚点字段必须与排序字段一起建联合索引而且顺序不能反。你自己类比一下你要在一排书架里按出版日期书编号找书索引就是那个目录目录必须先按出版日期排好再按编号排你才能快速翻到那一页。如果你只建了单列索引create_time条件里的id #{lastId}就只能在索引过滤后做二次筛选性能会明显下降数据的稳定性没问题但查询速度会退化成扫表。2.2 数据漂移的根源从 MVCC 快照到翻页会话的隔离取舍聊完性能和 SQL 写法再回头解决数据漂移问题。前面提到数据漂移的根源是两次查询之间数据集发生了变化。要根治有两个思路。第一个思路是利用 InnoDB 的 MVCC 快照读。在默认的REPEATABLE READ隔离级别下一个事务内第一次执行普通SELECT时InnoDB 会生成一个一致性读视图ReadView之后这个事务里所有普通查询都基于这个视图读到一致的数据快照。换句话说如果我把从第 1 页翻到第 10 页这个过程放进同一个数据库事务里那么无论中途有多少新人下单、旧记录被删我看到的始终是同一个历史快照数据完全一致不会漂移。但这里有个很现实的问题一个 HTTP 请求通常只对应一个短事务用户翻页的操作是多个独立的 HTTP 请求跨事务的 MVCC 快照是保护不了你的。把整个翻页会话包进一个长事务又会导致数据库连接被长期占用事务隔离性的设计初衷也不是干这个的。所以我的取舍是能用 MVCC 快照解决的就用解决不了的就接受并做好兜底。比如对于一些数据集合变化不频繁的后台报表可以直接在分页接口里开启一个只读事务保证一次翻页内的数据一致性。第二个思路是在应用层做一个游标锚点增量补全机制。在 Keyset 分页的基础上当发现下一页返回的记录里出现了与上一页完全重复的 ID 集合时可以主动触发一次按 ID 范围重新拉取的逻辑用主键把重复的记录补齐。这种做法不能百分之百消除漂移但可以把错误率降低到业务可接受的范围。说到底数据漂移问题有一个经济学上的本质完全没有数据漂移的系统要么是静态数据要么性能极差。你需要结合业务场景决定容忍度然后选择对应的方案。2.3 主键设计对稳定性的隐性影响UUID 的代价你未必付得起前面提到的第三层稳定性隐患——主键设计这里单独展开说。很多团队在数据库设计阶段习惯用 UUID 或者雪花 ID 作为主键理由很充分分布式生成、不用依赖自增、全局唯一。这个选择在分布式场景下没错但如果你在这个主键上建索引同时表又有大量写入那么查询稳定性就会受到极大的挑战。原因在于 InnoDB 的索引结构是 BTree聚簇索引也就是表数据本身按照主键值的顺序物理组织数据。自增主键插入数据时新行总是追加到树的右边缘操作非常顺畅不需要移动已有数据。但 UUID 主键是随机值新行会被插入到索引树的中间位置为了给新值腾地方InnoDB 不得不频繁地做页分裂把当前页一半的数据搬到新页然后重新调整指针。这个过程会产生大量索引碎片并带来随机的磁盘 IO 抖动。结果就是即使你的分页查询写得再完美表结构本身的物理组织不稳定性能数据也会忽高忽低。如果你已经用了 UUID 主键分页查询要做的补偿至少有两个一是所有业务排序字段都不要依赖主键的物理位置尽量用显式的业务字段排序二是定期做索引碎片整理ALTER TABLE ... ENGINEInnoDB或OPTIMIZE TABLE在低峰期重建表数据。如果你的项目还在设计阶段我的建议很直接单机用自增主键分布式用带时间序的雪花 ID 变体比如按位分配时间的自定义方案尽量不要用纯随机 UUID 作为聚簇索引主键。这条建议值得放进你们的数据库设计规范里。2.4 大表 COUNT(*) 的稳定性策略别让总数统计拖垮列表页分页查询还有最后一个暗雷——总条数统计。前端要显示共 10 万条记录共 5000 页就必须执行SELECT COUNT(*) FROM t_order WHERE create_time ...。在 InnoDB 引擎下COUNT(*)没有快速跳过的手段它必须把满足条件的索引项从头到尾扫一遍并计数。当表数据量超过千万级、命中条件的数据有几十万行的时候这个COUNT(*)扫索引的耗时可能比列表本身还长。最直接的替代方案是不统计总数只判断有没有下一页。方法很简单每次查询多取一条数据即LIMIT 21如果返回了 21 条就说明还有下一页然后只展示前 20 条。这个方案配合 Keyset 分页非常丝滑因为在数据量大的场景下有没有下一页才是用户真正关心的事精确到个位数的共多少条对 C 端体验毫无意义。如果业务方死活要显示精确总数那就只能用计数表或者缓存了。我踩过的一个实践是在 Redis 里维护一个计数器每次插入或删除订单时更新但要注意两点一是计数和实际数据的一致性必须通过事务消息或者对账任务保障因为 Redis 一旦过期或者宕机就丢数据二是最后展示给用户时数字和实际列表可能误差几条产品上要能接受这种近似值。稳定的分页系统从来都是把成本花在刀刃上而不是花在看得舒服上。3. 实操过程与核心环节实现3.1 一套可落地的 Keyset 分页接口改造方案直接上活儿。以一个典型的电商订单列表为例接口原本长这样请求GET /api/orders?page1pageSize20 返回{ list: [...], total: 12345, page: 1 }改造后的接口参数设计变成了这样请求GET /api/orders?cursor2024-05-01T10:05:00,102345pageSize20 返回{ list: [...], nextCursor: 2024-05-01T09:59:00,102321, hasMore: true }这里cursor不是一个简单的页码而是把排序字段的值和主键 ID 打包成了一个不透明字符串通常是base64(create_time , id)的形式。每次请求只带上一页最后一条记录的游标服务端解析游标后生成对应的 SQL。完整的改造步骤我拆成了六步照着走不会踩大坑确认排序字段与锚点字段。排序字段用业务字段如create_time锚点字段用主键id并确保两者按顺序建立了联合索引(create_time, id)。设计接口的游标参数格式。建议用base64({lastCreateTime},{lastId})作为隐藏参数前端不用理解含义服务端负责编解码。这样还能防止用户手改参数因为游标里带了校验信息。改查询 SQL。把原来的OFFSET条件换成WHERE (create_time ? OR (create_time ? AND id ?)) ORDER BY create_time DESC, id DESC LIMIT ? 1。改返回体。不再返回page和total返回nextCursor和hasMore其中hasMore由 LIMIT1 判断。兼容首屏请求。首屏没有游标走全量排序取前 N 条逻辑上等价于cursor为空。改造前端组件。把上一页/下一页 页码跳转换成首页/上一页/下一页去掉页总数显示或者显示已加载 N 条。这里用一个完整的 MyBatis 代码片段来说明 SQL 层的写法。注意 Mapper XML 里游标条件要动态拼接select idselectOrderPage resultTypeOrder SELECT id, order_no, create_time, amount, status FROM t_order where if testcursorCreateTime ! null (create_time lt; #{cursorCreateTime} OR (create_time #{cursorCreateTime} AND id lt; #{cursorId})) /if /where ORDER BY create_time DESC, id DESC LIMIT #{pageSize} /select这段 SQL 里最需要注意的就是OR条件的写法。有些同学会把两个条件合并成一个create_time ? AND (create_time ? OR id ?)这样写结果可能是错的因为联合索引的匹配规则会失效。老老实实按上面这种第一个排序条件的分支 等值匹配下的锚点分支来写执行计划才会是 Range 访问用上联合索引。3.2 如何把稳定性量化三个监控指标与巡检方法改完 SQL 只是第一步怎么验证稳定性确实好了得靠数据说话。我建议在生产环境上线之前和之后分别盯紧三个指标。第一个指标是深度分页接口的 P99 耗时曲线。改造前你可以写一个压测脚本模拟用户随机点击第 1、20、50、100 页记录各场景下的响应时间分布。改造后同样模拟随机游走翻页但此时游标会随机指向任意深度的位置。改造前 P99 会随着页号显著上升改造后应该是一条近似水平的直线这就说明分页深度不再影响性能了。第二个指标是慢 SQL 数量和数据库 CPU 使用率。上线之后重点观察 DBA 平台上的慢查询日志确认分页相关的 SQL 语句不再出现在 Top N 慢查询里。同时数据库 CPU 的波动率应当明显下降尤其是大促期间不会再出现某个时间段 CPU 突然飙升的问题。第三个指标是翻页错乱的上报量。这个指标需要在前端埋点把用户翻页时出现的下拉刷新后重复显示已读条目或者点击下一页但仍在原页等行为自动上报。这个数据不会很多但只要出现就说明系统里还有某个角落用了老式的分页方案。我建议巡检频率是每周一次把这类错乱率控制在万分之五以下才算合格。巡检的方法也很简单每周抽一天在业务低峰期对线上库做EXPLAIN抽查重点看分页查询的执行计划是否稳定命中联合索引type是否为range而不是ALL或index。一旦发现执行计划退化基本可以断定索引被谁删了或者数据分布发生了重大变化需要人工介入。3.3 前端跳页需求与游标分页的妥协方案Keyset 分页的一大缺陷就是无法直接支持跳到第 20 万页这种操作。因为游标只知道上一页最后一条记录的位置并不知道第 20 万页从哪开始。但这个需求在 C 端产品里真的很少见通常只是后台管理系统需要。如果你实在绕不开跳页我给两个补救思路。第一个思路是时间点定位法。给用户提供一个按时间段跳转的入口。比如用户想回到两周前的订单可以在前端选一个日期后端把该日期当天 00:00:00作为游标传入从那个位置开始往下翻。这种方案非常适合订单、日志、操作流水这类天然带有时间维度的数据本质上是用一个时间段锚点替代页码锚点既满足用户很久以前有某条记录的诉求又保住了查询性能。第二个思路是独立的深翻页服务。如果业务真的有查看第 500 页这种刚需比如后台管理表格那就不要把深分页的压力打到业务表上。可以做一个独立的分片表或者专用的只读副本配合离线聚合、定期抽样等方式把这个高频深翻页场景隔离开。它的准确性和实时性可以适当放宽因为管理后台的数据精确到分钟级别完全够用。这属于架构上的妥协牺牲一点实时性的准换回整个系统的稳。4. 常见问题与排查技巧实录4.1 翻页翻到一半数据重复或遗漏怎么办这是分页系统里最经典的灵异事件。我遇到过一次非常典型的定位过程用户反馈订单列表里出现了重复订单而且只在翻页超过 5 页之后才偶发。我先确认了接口用的是OFFSET分页紧接着检查排序字段create_time发现这个字段在那一批次里有大量完全相同的值因为是批量导入的。用户翻到第 5 页时排序边界上恰好有几条数据create_time相同数据库在两次独立查询中对这些同位置记录的返回顺序并不稳定就会发生重复。排查这类问题的固定套路是先看排序字段有没有唯一性如果排序字段值不唯一100% 会出现数据重复或遗漏。修复方案也简单要么给排序条件自动追加主键作为次级排序条件即使还是OFFSET把ORDER BY create_time DESC, id DESC加上也能大幅降低错乱概率要么直接上 Keyset 分页。这是投入产出比最高的一处改动。4.2 刚写入的数据在列表页查不到这个问题的排查链路稍微长一点。某次业务方反馈用户在小程序里刚提交了一个商品回到列表页却看不到非要隔一两秒刷新才有。起初我以为是缓存问题检查后发现列表接口根本没有加缓存于是把目光转向主从架构。订单写入走的是主库列表查询走的是从库主从复制延迟在正常情况下只有几十毫秒但瞬时写入压力大的时候延迟可以放大到几百毫秒甚至秒级用户快速操作时就会撞上这个窗口。排查手段很简单先在列表查询接口里加一个开关临时把流量全部路由到主库验证如果问题消失就确认是主从延迟导致的秒级不可见。根治方案分两步走一是对写后立即读的场景做特殊路由比如用户刚提交完订单前端在 2 秒内发起的列表请求带上标记后端根据标记强制走主库二是优化主从复制链路使用并行复制、减少大事务来降低延迟。注意这个问题的本质不是分页查询本身而是读写分离架构下的一致性边界但分页接口往往是最先暴露问题的受害者。4.3 order by 字段重复导致下一页错乱但 EXPLAIN 显示走了索引这种现象最迷惑人。你明明加了索引执行计划也是range但翻页结果就是不对劲。有一个真实的坑我遇到过一张表用status做排序字段status的值其实只有 0、1、2 三种。这样的索引选择性极差虽然优化器认为它会使用索引但实际上满足条件的数据有几十万行MySQL 在执行WHERE status 1 ORDER BY status LIMIT 20 OFFSET 200时无法从索引中快速跳过前 200 行符合条件的记录只能沿着索引逐个扫描到 220 行才停下来。从执行计划看确实用了索引但性能依然随偏移量线性下降。遇到这种问题先做一个简单的测试把排序字段换成高选择性的字段比如创建时间看查询性能是否明显改善。如果改善就说明排序字段的选择性太差不适合作分页排序条件。方案也简单改用主键或时间字段排序功能上的差异可以通过索引设计和业务逻辑来补偿。分页查询的排序字段应当优先从唯一性高、有单调趋势、查询频率高的字段里选这是我在这个 case 之后总结出的选型铁律。4.4 count(*) 返回很慢拖垮了整个分页接口这是最容易被人忽略的稳定性杀手。有一个列表页查询列表本身 50ms但count(*)花了 900ms直接导致接口 P95 破秒。根因就是条件字段上虽然有索引但count(*)在 InnoDB 里必须扫描所有满足条件的索引项做计数。我给出的方案是二选一第一去掉 count用LIMIT 21判断hasMore前端展示已经加载了 N 条这是最彻底的做法第二如果产品不能去掉总数就维护一张独立的计数器表在业务事务里同步更新查询时单独取计数。注意计数器表更新时不能直接操作业务表尽量用异步消息或本地事务事件的方式避免事务范围过大导致写放大。4.5 分页参数被恶意调大数据库瞬间打爆还有一个稳定性问题不是由数据产生的而是由人产生的。分页接口的pageSize参数如果不设上限攻击者可以把pageSize调到 10000一条 SQL 就把数据库的 IO 打满。这种问题处理起来最简单最直接服务端必须严卡pageSize上限比如 100超过直接拒绝同时为了防止深度翻页请求把数据库连接池耗尽要对分页接口做并发限制和熔断。我习惯在网关层加一条规则列表类接口单 IP 每秒请求数上限分页接口再单独收紧一旦触发就返回友好的提示。系统稳定性从来不是一个单点问题SQL 写得好只是起点参数校验和流控必须跟上。写在最后坦白说分页查询是我见过的最容易被低估的技术点之一。它写起来只要几行代码跑起来却能把整个数据库拖垮它看起来只是上一页下一页的交互背后却牵扯着索引设计、主键策略、事务隔离、主从架构和参数安全。我在实际项目中踩得最深的一个坑就是早期把所有分页需求都无脑统一成LIMIT/OFFSET结果大促压测的时候分页接口成了第一个崩溃的环节。那次教训之后我把这套 Keyset 分页 深翻页降级 监控巡检的方案沉淀成了团队内部的代码模板和检查清单后续再上新的列表页只要是 C 端入口默认就走游标分页。最后再分享一个实用的小技巧游标参数里可以带上一个校验位比如把lastId和lastCreateTime做一次加盐哈希拼在游标末尾服务端解码时先校验能有效地防止用户伪造游标去查询不存在或不属于自己的数据顺便也堵住了不少刷接口的乱来。分页稳定的关键不在于某一个精妙的 SQL 技巧而在于你肯不肯在业务膨胀之前就想清楚数据、索引和架构之间那层隐形的关联。希望这篇总结能帮你少走一些弯路如果你们团队也在做分页相关的改造可以对照目录里的检查项逐条过一遍至少能筛掉不少常见雷区。