企业官网建设流程全解析

凌晨两点被值班电话叫醒，运营同事在群里说后台订单列表打不开了，接口直接超时。上去一看数据库 CPU 冲到 97%，慢查询日志里全是同一条 SQL——后台订单列表翻到第 10000 页那条OFFSET 200000 LIMIT 20。这个场景我至少经历过三次，每次的病因都一样：分页查询，看起来人畜无害，深翻页直接教做人。

分页查询是后端开发里最不起眼、但最容易埋雷的功能。小数据量时一切正常，等你表里几百万行、用户翻到几十页之后，性能说崩就崩；数据一边写入一边翻页时，还会出现重复、漏数据、排序错乱这些“灵异事件”。这篇文章不打算讲那种LIMIT 10 OFFSET 20的入门用法，而是系统拆解分页查询的稳定性问题从哪来、底层为什么、怎么根治，方案覆盖单表、多条件排序、高并发写入三种场景，能直接抄进你自己的项目里。

1. 分页查询的常见实现形态与稳定性问题根源

1.1 三种主流分页实现，没有一种是全能的

先说清楚市面上主流的三条技术路线，后面所有讨论都围绕它们展开。

第一种是offset/limit 页码分页，也是目前最普及的玩法。前端传page=1&size=20，后端翻译成LIMIT 20 OFFSET 0，页数加一，OFFSET 就加 20。优点是实现成本几乎为零，天然支持“跳到任意页”，后端只需要知道页码和每页大小就能算出来偏移量。

第二种是keyset 分页，也叫游标分页、seek 分页。核心思路是不用偏移量，而是借助一个唯一键定位“从哪开始取”。比如按主键倒序，每次取WHERE id < 上一页最后一条id ORDER BY id DESC LIMIT 20。优点是性能恒定，翻到一百万页和翻到第一页耗时几乎一样，缺点是实现比 offset 复杂，不支持跳跃式翻页。

第三种是基于快照/物化视图的分页，直接把要分页的数据在某个时间点固化下来，分页走的是静态快照而不是实时表。这种方案稳定性最好，但维护成本最高，需要额外的基础设施。

很多人觉得“默认用 offset 就行”，真不是这样。你选哪种方案，取决于对稳定性的要求有多高。下面这一段就是关键了——offset 到底为什么会在深翻页时出问题。

1.2 稳定性问题根源：OFFSET 的“先扫描后丢弃”机制

offset 分页性能崩盘的核心原因，在于它的底层执行逻辑。想象你在图书馆里找书，LIMIT 20 OFFSET 200000的意思是：工作人员从第一本开始，一本一本数过去，数够 200020 本，把前 200020 本全部从书架上搬下来，只把最后 20 本拿给你，其余 200000 本再放回去。

数据库里做的事一模一样。以 MySQL InnoDB 为例，执行SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 200000时，执行计划通常会走二级索引扫描：读索引定位到第一行满足条件的数据，然后沿着索引链表逐行向后遍历，一边遍历一边数数，数到偏移量位置的前一行都被标记为“冗余”，取够 200020 行时把前面的 200000 行丢掉，只回表读取最后的 20 行返回给客户端。

换句话说，OFFSET 越大，数据库需要扫描和处理的行数就越多，即使你最终只需要 20 行。这个扫描量按线性增长，但 CPU 消耗和延迟的增长在数据量变大时往往呈现更陡峭的曲线——因为索引页不在内存里就要触发磁盘读，一次深翻页可能把几万个索引页扫一遍，缓冲池命中率直接崩掉。

这还没算排序。如果ORDER BY的字段没有索引支撑，数据库还要先做 filesort，把满足条件的所有行都读出来、写进临时表、排序完毕后再丢前面的 200000 行。那种手动ORDER BY RAND()配合 offset 的写法，慢到怀疑人生也完全不意外。

所以这里给第一个结论：offset 分页的性能稳定性，和偏移量强相关，偏移量越大，稳定性越差。这不是某一种数据库的毛病，MySQL、PostgreSQL、Oracle、SQL Server 的优化器处理 offset 时的思路大同小异，都逃不开扫描—丢弃的过程。

2. 深度拆解 OFFSET 分页的四大稳定性陷阱

2.1 陷阱一：深翻页引发的性能雪崩

第一个陷阱最容易理解，也最容易在生产环境引爆事故。

假设订单表orders有 200 万行数据，每页显示 20 条，翻到第 1000 页时OFFSET = 19980 * 20 = 399600。这条 SQL 即使命中索引，也要遍历并丢弃近 40 万行。我用真实环境测过，同样的查询条件，第一页响应时间大约 30 毫秒，翻到第 500 页大约 780 毫秒，翻到第 2000 页直接到 5 秒以上。数据库的 CPU 使用率和平均负载跟着飙升，而应用层加什么缓存、什么超时都不管用——因为慢查询本身把数据库的连接池打满了，其他正常请求全部排队等连接。

这种事故在后台管理系统中特别常见。运营人员对比数据时喜欢用“下一页”一路点下去，加上每页条数设置过大（比如一页 100 条），很容易就触发深翻页。电商大促期间的订单查询页、财务系统的流水列表、日志分析平台的结果列表，都是重灾区。

需要特别强调的一个场景是反向分页。很多列表默认按时间倒序，但用户要求“从最早的数据往前翻”，这时候 offset 一样深，但排序方向反了，如果索引定义的是倒序而查询用的是正序，InnoDB 可能会直接放弃索引扫描，选择 filesort，那性能崩得更彻底——不是扫描几万行的问题，是排序几十万行的问题。

2.2 陷阱二：数据变动导致的重复与缺失

offset 分页第二个坑比性能更容易被忽略，它不影响接口速度，但影响业务正确性：翻页过程中，如果底层数据在持续写入或删除，用户会看到同一行数据出现在两页里，或者某一行数据永远翻不到。

道理很简单。第一页取到了 offset=0 到 19 的行，用户在这一瞬间点击“下一页”，此时请求发出前，恰好有一条新数据插入到列表最前面（比如新订单、新评论、新消息），整张表的行序整体后移一位，于是第二页的 offset=20 实际上取到的还是刚才第一页的最后一条数据——重复了。

反过来，如果删除了一条数据，后面所有的行都往前补位，用户会有一条数据永远看不到——它被“跳过”了。

你以为这只会发生在极端并发下？我实际遇到过比这更隐蔽的情况：运营人员在后台批量审核订单，每审核一条就更新状态，列表恰恰按“未审核优先”排序，于是每点一次下一页，前面就有 N 条被审核过的数据从查询结果里消失，后面的数据集体往前顶，用户翻了几百页，永远看不到最后那几条数据——因为查询条件的结果集本身在持续变小，而 offset 是死的。

这种“数据变动导致分页错乱”的现象，英文社区里叫pagination drift，在绝大多数的业务场景下很难被自动化测试发现，因为测试环境的数据往往是静态的，只有上了生产、有了真实并发写入后才会暴露。

2.3 陷阱三：排序不稳定引起的数据错位

第三个陷阱藏在ORDER BY里：当你排序的字段有重复值时，分页结果不保证稳定。

拿最常见的ORDER BY created_at DESC来说，同一秒内创建了几十张订单，created_at 完全相同，数据库在执行排序时对这几十行“相同排序列”的内部顺序是不确定的。如果第一页和第二页执行时，排序器对相同键值的内部排列方式发生了变化（比如数据页物理位置变了、索引合并策略变了），那同一行数据就可能出现在两页的交界处。

更麻烦的是并发场景。两个相同created_at的订单，第一页取到了 A 没取到 B，翻到第二页时 B 又进来了——因为 B 在排序结果里的位置被排到了第 21 位。用户看到的现象就是“上下翻页的时候，一行数据反复出现”。

这个问题在 MySQL 里尤其恼人。当ORDER BY走的是 filesort 而不是索引顺序时，相同排序键值的行序受扫描顺序影响，而扫描顺序受物理存储位置影响。你以为是 stable 的排序，实际上不是。PostgreSQL 的排序算法在大部分场景下是 stable 的，但也不能完全依赖。

要根治这一点，排序设计必须遵循一个原则：排序键要具有唯一确定性，也就是在排序字段的基础上，叠加一个全局唯一字段作为次级排序键。最常用的做法是ORDER BY created_at DESC, id DESC，因为 id 全局唯一，排序结果严格确定。只有排序完全确定，分页才谈得上稳定。

2.4 陷阱四：并发写入下的页漂移与额外压力

最后一个陷阱是前面几个的综合效应，表现在高并发写入场景下整体分页体系的不确定性。

举一个论坛帖子的回复列表场景：用户 A 正在翻第 3 页，此时用户 B 发布了一条新回复，帖子回复总行数上涨，第 3 页的 offset 对应位置后移；同时用户 C 删除了自己的一条回复，位置前移；这两个操作发生在同一秒，A 的请求过来时，查询结果集已经变化了两次。页漂移让分页结果和用户上一次看到的页面之间缺乏连续性。

页漂移本身只是业务逻辑问题，但它带来的隐藏成本更值得注意：为了保证用户每次翻页看到的都是“最新数据”，后端往往会放弃缓存，强制实时查询，这反而把大量读压力打到了数据库上。后台列表、移动端信息流、管理后台报表，只要你是 offset 分页并保证实时性，数据库的读负载就降不下来，因为每次翻页都意味着一次全量偏移计算。

这种场景下，稳定性和性能是不可兼得的。你要数据比并发写入更“稳”，要么接受牺牲实时性（快照），要么换游标分页（从“按页翻”变成“按位置翻”）。很多人在这里纠结，其实没有方案是完美的，看你接受什么样的取舍——下一节直接讲怎么根治。

3. 根治方案：基于游标的 keyset 分页实战

3.1 单字段排序的游标实现：从 OFF SET 到 WHERE

keyset 分页的核心思路一句话就能讲明白：不依赖偏移量，而是把“上一页最后一条数据的位置”作为下一页的起点，通过 WHERE 条件定位。

以最常见的按主键倒序为例。第一页执行：

SELECT id, order_no, amount, created_at FROM orders ORDER BY id DESC LIMIT 20;

前端拿到这 20 条后，把最后一条的id记录下来，比如last_id = 10024。用户点“下一页”时，SQL 变成：

SELECT id, order_no, amount, created_at FROM orders WHERE id < 10024 ORDER BY id DESC LIMIT 20;

关键差异在于：这条 SQL 不需要扫描任何多余的行。InnoDB 通过主键索引直接定位到id < 10024的位置，然后向后（倒序方向）取 20 行，完事。数据从 200 万行翻到第 10 万页，本质上是定位到id值附近取 20 行，扫描量永远恒定为 20 行，时间复杂度 O(20)，和总数据量完全无关。

这种方案的稳定性还体现在另一个层面：新数据插入不会影响翻页结果。用户翻到第 10000 页时，页尾拿到last_id = 500023，即使此时有人在前面插了一万条新数据，下一页依然是id < 500023继续往后取——结果集是连续的，不会重复也不会漏数据。

当然，它有一个明显短板：不支持“跳页”。你没法直接翻到第 10000 页，因为游标的定位必须依赖上一页的最后一条last_id。这是业务层面的取舍问题，后面的第 4 节会讲怎么处理。

3.2 多字段排序的游标条件推导

实际业务里很少有人只按一个字段排序。最常见的场景是列表页按created_at倒序排列，同秒数据很多，必须叠加id DESC保证排序稳定。这时候游标就不只是一个值，而是一个复合条件。

假设排序规则是ORDER BY created_at DESC, id DESC，上一页最后一条数据是last_created_at = '2024-03-18 10:30:45', last_id = 10086。下一页的条件推导逻辑是这样：

SELECT id, order_no, created_at FROM orders WHERE (created_at < '2024-03-18 10:30:45') OR (created_at = '2024-03-18 10:30:45' AND id < 10086) ORDER BY created_at DESC, id DESC LIMIT 20;

这个条件的含义是：要么created_at更晚（在主排序上领先），要么created_at相同但id更小（在次级排序上领先）。这恰恰是(created_at, id)这个复合键在字典序上严格小于上一个复合键的完整表达。

如果你的业务排序是三字段甚至四字段，比如ORDER BY status ASC, created_at DESC, id DESC，推导会更加复杂，但逻辑相同：前面的字段比较相等时，才比较下一个字段，最后一定有一个全局唯一字段（一般是主键）收尾，保证排序确定性。

写起来确实比OFFSET麻烦，但性能收益值得。基于(created_at, id)建立复合索引后，这条 WHERE 条件能够完美命中索引范围扫描，每次查询只扫描 20 行。

注意：多字段排序的游标分页，索引设计必须和排序顺序严格匹配。MySQL 8.0 支持降序索引，8.0 之前只能用ALTER TABLE ... ADD INDEX idx_created_id (created_at, id DESC)的近似写法，或者干脆在应用层接受“物理升序、逻辑倒序”的折中，这个在第 5 节讲。

3.3 游标编码与前端交互设计

keyset 分页在实际落地时，上下游协同设计必须提前对齐，不然容易做歪。

首先，前端不能把游标当成一个“筐”传任意值。后端应该把游标打包成不透明字符串再传给前端。常见做法是把排序字段的值拼接成一个 URL-safe 的 base64 串，比如后端内部用一个 JSON 结构：

{"created_at": "2024-03-18 10:30:45", "id": 10086}

序列化后做 base64 编码，前端看到的就是eyJjcmVhdGVkX2F0IjoiMjAyNC0wMy0xOCAxMDozMDo0NSIsImlkIjoxMDA4Nn0=这样一串鬼画符，不用理解里面是什么。前端翻页时只需要把它作为cursor参数原样传回来，后端解码后还原成(created_at, id)条件。

这里有个容易踩的坑：base64 的 URL 兼容性。标准 base64 包含+和/两个字符，在 URL 参数里可能被解析成分隔符或路径。实际传输时要么用 URL-safe 的 base64 变体（把+换成-，把/换成_），要么整体重新 URL encode。我用过最省心的方案是直接把两个值拼成时间戳_id的格式，例如1710743405000_10086，后端用split('_')拆开就行，可读性和调试体验比 base64 好太多——反正前端不解析，后端只要能无损还原就不需要加密。

其次，下一页接口应返回next_cursor和has_more两个字段，前端根据has_more决定是否显示“加载更多”。这和 offset 分页的 “总页数”语义不同：keyset 分页没有“页”的概念，只有“上一批/下一批”。产品要求显示 “第 3/共 80 页”的场景，keyset 做不了，这种就得留在后面第 4 节的混合方案里处理。

3.4 索引设计要点

keyset 分页的性能上限，完全取决于索引设计。这里单独拉出来讲，因为很多人在这一步翻车。

最典型的例子：表里已经有created_at的单列索引，业务侧按ORDER BY created_at DESC, id DESC分页，研发觉得“有索引就行，反正 created_at 在上面”。结果查询计划走了索引扫描created_at之后，无法同时利用id的有序性，InnoDB 只能对created_at对应的所有行做额外排序，性能照样崩。正确做法是建一个复合索引，让排序字段和游标字段都在一个索引里：

CREATE INDEX idx_orders_created_id ON orders (created_at DESC, id DESC);

索引建的要是复合的、方向和查询一致的，查询优化器才能直接沿着索引的物理顺序，从游标位置取 20 行，连一次 filesort 都没有。

一个常见误区是觉得游标分页“反正只查 20 行，不用索引也没事”。事实上，没有合适索引时，MySQL 为了判断WHERE created_at < '...' OR (...)的处理方式会比较棘手——OR条件对索引扫描不友好，可能会优化成两个子查询再合并，实际执行可能是索引合并（index merge）甚至全表扫描，性能完全不可控。所以，“游标分页 + 复合索引”是配套使用的，缺一不可。

另外一个细节：如果查询需要返回的字段比较多，可以考虑把常用字段加到索引里做成覆盖索引（covering index），让查询阶段只读索引页、免去回表。特别是那种列表页要显示order_no、amount、status的场景，一个覆盖索引能再省掉 90% 的随机 IO。

4. 混合方案：在真实业务场景里怎么取舍

4.1 前 offset 后 keyset 的切换策略

keyset 分页虽好，但业务上有个绕不开的硬需求：用户要能跳到第 X 页。纯游标分页不支持这一点，对后台数据分析、报表查看类场景几乎不可用。

现实工程里最实用的策略是混合分页：前 N 页用 offset（满足跳页需求），N 页之后自动切换成游标（保证深翻页性能不崩）。

具体实现也不复杂。后端接口接收页码page，先判断是否超过切换阈值。比如阈值设为 100 页，page < 100就直接用LIMIT 20 OFFSET (page - 1) * 20，数据量小翻得浅，性能没有问题；page > 100时，前端需要传一个cursor，后端用游标方式继续取数。

这里的核心问题是切换边界的游标来源。我的做法是：第 100 页的接口响应里额外返回一个next_cursor，即便它自己是通过 offset 算出来的，也照常计算并序列化给前端。前端从第 101 页开始带cursor请求，后端走 keyset 分支，两边无缝衔接。

这个方案能保证深翻页性能恒定，同时前 100 页的跳页体验和传统分页完全一致。切页阈值建议根据表数据量动态调整——100 万行以上的表阈值调低到 50，数据量小的表可以放到 500，经验值是以OFFSET × 每页行数不超过 5 万行为安全线。

4.2 总页数与页码跳转问题的处理

混合方案里还有个绕不开的话题：总页数到底怎么算。

offset 分页时代，前端要显示总页数，后端通常要执行一遍SELECT COUNT(*) FROM orders WHERE ...。数据量大时，这个 COUNT 本身就是深分页之外的另一颗性能炸弹。200 万行的表，带几个过滤条件的 COUNT 可能跑几百毫秒甚至几秒，比列表查询还慢。

我给三个可落地的思路，按成本从低到高排列。

第一，用近似值替代精确值。MySQL 里EXPLAIN的rows字段可以拿到优化器估算的扫描行数，PostgreSQL 可以直接读优化器的 estimate。对绝大多数业务来说，列表页显示 “约 100 万条” 还是 “1,000,023 条”，用户感知几乎没有差别，但获取成本的差别是几毫秒和几秒的差距。

第二，定期物化 COUNT。后台报表类场景，数据写入量有周期规律，比如订单每天几十万条，那每小时或者每 10 分钟做一次总量缓存，用户看到的总数允许有几分钟的滞后，就能避免频繁 COUNT。

第三，直接放弃精确页码，改成“加载更多”。这是移动端的主流交互，配合 keyset 分页，后端完全不需要知道总数，只需要返回has_more判断有没有下一页，体验上反而更顺滑。

跳页需求本身也要重新审视。ToC 产品里用户真的会跳到第 5000 页吗？绝大多数场景是不会的。如果是 ToB 管理后台，把“跳页”改成“按条件过滤定位”，往往比无限翻页更符合真实使用习惯。产品设计上减少对“精确页码”的依赖，技术方案自然就能往 keyset 上靠。

4.3 数据缓存与物化页思路

游标分页解决的是性能和稳定性问题，但更进一步，如果你的场景对结果稳定性要求极其苛刻（比如财务报表、对账列表，用户必须看到完全一致的数据，不能被中途写入影响），那么任何实时查询的分页方案都不过关，你需要的是物化页。

物化页的思路和搜索引擎的索引分片类似：定时任务在后台把符合条件的全量数据排好序，每 20 条存成一个快照页（物理存储或缓存），用户分页时直接按页码取快照，完全不碰业务表。

这套方案我在一个百万级流水、需要精确导出对账的场景里用过。后台每 5 分钟生成一轮快照，用户翻页时读的是当前快照，结果绝对稳定——不会重复、不会漏数据、不受写入影响，查询耗时全部在个位数毫秒级别。缺点是占用额外存储空间，数据量大时快照生成本身耗时较长，而且实时性有几分钟的滞后。

折中方案是用 Redis 缓存热数据页。列表页前 50 页放进缓存，翻页时从缓存读，第 51 页开始实时查询。这种做法在小数据量、固定查询条件的场景里性价比极高，但需要额外处理缓存失效、数据一致性、内存占用这三个老问题，适合规模不大却频繁被查的列表，比如热门的分类商品列表。

5. 常见问题与排查技巧实录

5.1 问题场景与排查思路速查表

我在 Nina 次实战中整理了一些典型问题和对应的排查方向，做成表格方便对照：

5.2 避坑：keyset 分页容易忽略的四个细节

keyset 方案也不是高枕无忧，下面几个细节都是我在实际项目里踩过之后总结出来的。

第一，游标字段的值类型要稳定。如果你的游标里塞的是时间字符串，数据库里存的是DATETIME，应用层拿到的是String，在拼接条件时务必统一转成数据库类型，否则可能因为隐式类型转换导致索引失效。最容易出问题的就是created_at：前端传回来的是2024-03-18 10:30:45，后端如果直接拼到 SQL 里，MySQL 有时会尝试把字符串转成日期比较，有时会用隐式转换规则，行为很微妙。建议在服务端用参数化查询，让驱动按字段类型做绑定。

第二，多条件游标的索引必须覆盖所有排序字段。前面 3.2 节推导的那个(created_at, id)条件，如果索引只建了created_at，查询优化器为了处理OR (created_at = ? AND id < ?)分支会回表取 id 再过滤，性能大打折扣。用EXPLAIN观察 key_len 是否为复合索引的完整长度，小于预期就说明索引没被吃满。

第三，游标分页里的“上一页”概念要小心。有些工程师实现 keyset 时，第一页请求不传 cursor，返回成功后，前端顺手把next_cursor赋值给了last_cursor。第二次请求时后端发现 cursor 非空就执行WHERE id < ?。这本身没错，但如果第一页数据在两次请求之间被删光了，last_cursor已经不在表里，WHERE id < 10086依然能正确返回后续行——这恰恰是 keyset 比 offset 强的地方。真正要注意的是：别把next_cursor和last_cursor搞混，用错了位置会导致漏掉一行。我见过一次事故，前端把上一页游标和下一页游标同时传进来，后端没做参数校验，直接按WHERE id < ?处理，结果跳过了中间一页的数据。

第四，分布式环境下的全局排序键选择。如果你用分库分表，每个分片的自增 id 不全局递增，全局排序还依赖 id 就会出问题。这种场景需要引入全局唯一且单调递增的字段（比如发号器生成的雪花 ID），或者在应用层做多分片归并排序后再取游标。后者比较复杂，不是简单改 SQL 能解决的。

5.3 关于稳定性的另一个隐藏维度：连接池与超时

分页查询的稳定性不仅仅在 SQL 层面，应用层的连接池配置同样会把它拖垮。

深翻页慢查询会把连接池的连接长时间占住。假设连接池最大连接数 50，页数一深，10 个慢查询就把连接池占掉一大半，后续所有正常请求排队等连接，接口的 P99 延迟从 100ms 变成 3 秒，业务方只会觉得“系统整体变卡了”，而排查定位到分页查询需要花很长时间。

所以配合分页优化，建议做三件事：一是给分页查询接口设置独立的小连接池或独立的限流阈值，慢查询和正常业务隔离；二是给数据库侧设置max_execution_time针对查询超时熔断；三是在网关层对分页接口的响应时间做监控告警，连续超过阈值时自动降级（比如直接切到游标分页模式）。

我经历的那次凌晨事故，最终的恢复动作反而简单：先手动把列表接口的每页条数从 50 降到 20，再禁止翻页超过 500 页，连接池压力立刻缓解，然后再花一周时间把整个后台列表切换成游标分页。事后复盘时我最大的感慨是：如果设计之初就考虑到深翻页，事前的二十分钟改造就能避免事后的两小时救火。

6. 分页之外：关于稳定性的最后一点个人体会

我在实际排查分页问题时越来越意识到一个规律：稳定性的问题很少是单点引起的，往往是设计假设出了偏差。offset 分页的设计假设是“数据量有限、数据基本不变、查询都浅”，一旦任何一个假设被现实打破，整套方案就会露出裂缝。而 keyset 分页的设计假设是“数据无上限、写入持续存在、查询靠游标推进”，每个假设都踩在真实业务的基线上，所以它的稳定性不是靠优化调出来的，而是设计本身决定的。

所以每次有人问我“分页用什么方案”，我的第一反应不是立刻给出技术选型，而是先问：这个列表的数据量增长曲线是什么样？用户会不会翻很深？数据写入频繁吗？页面需要支持跳页吗？这四个问题回答了，方案基本自己就浮出水面——纯 offset、混合分页、还是纯游标。

最后分享一个小技巧：切换分页方案时，不要一刀切改所有接口，先挑一个数据量大、翻页深、对稳定性要求高的列表做试点，上线后对比慢查询数量、P99 延迟、数据库负载三个指标。我自己统计的实测数据里，光是把一个 200 万行的后台订单列表从 offset 切到 keyset，同一时段内慢查询从每小时 300+ 条降到了个位数，DB CPU 从 60% 落回 20% 以下。改动不大，收益立竿见影，值得一试。