《Spring Boot + JPA 实战》第11招 深分页为什么慢
## 01、痛点 上周凌晨，订单服务告警，SQL 慢查询堆成山。用户只是在管理后台点了最后一个分页按钮，第 50000 页，数据库 CPU 直接飙到 90%。开发第一反应是索引失效，结果 EXPLAIN 一看，索引走得好好的，可查询还是慢。慢在哪？慢在 `LIMIT 999980, 20`。MySQL 为了扔掉前 999980 行，需要扫描百万级数据，而这只是取 20 条记录。 更让人头疼的是，Java 这一端用的是 `Pageable`，参数看起来只是 `page=49999&size=20`，分页简单得像切蛋糕。但代码里返回 `Page` 的那一刻，底层还偷偷多执行了一条 `COUNT(*)`。这两件事叠加，一个小小分页接口就成了数据库杀手。 为什么现在要讲？因为这类问题不会在测试环境 1000 条数据时暴露，往往等到订单量上百万、接口超时、用户骂慢，才在线上被逼着复盘。分页看起来是 UI 问题，本质是数据库访问模式问题。 ## 02、是什么 先把几个词说清楚，否则后面容易混。 - `Pageable`：Spring Data 里的分页参数抽象，通常包含页码、每页大小和排序条件。它把分页代码简化成 `page/size`，但它不负责优化 SQL。 - `Page`：返回一页数据，同时返回总记录数和总页数。总记录数来自额外的 `COUNT` 查询。 - `Slice`：只返回当前页数据和“有没有下一页”，不查总记录数，因此不会触发额外 `COUNT`。 - `Sort`：排序条件。分页一旦有排序，就必须和索引配合，否则排序文件或临时表会让分页雪上加霜。 - `Count Query`：分页组件为了知道总条数执行的 `SELECT COUNT(*)`，在大表上不可忽略。 - `Offset Pagination`：也就是 `LIMIT offset, size`。页码越深，offset 越大，数据库需要扫描并丢弃的行越多。 - `Keyset/Seek Pagination`：也叫游标分页。用上一页最后一条记录的唯一游标，比如 `(created_at, id)`，直接 `WHERE` 到下一段数据，不依赖 offset。 这组概念最关键的区别在于：`Page` 需要总量，`Slice` 只表达相对位置；`Offset` 按行号跳，`Keyset` 按值跳。后面实战会看清楚两者的差距。 ## 03、为什么 为什么 `Page` 通常额外执行一条 COUNT SQL？ 因为分页组件要渲染出“共 1000000 条、共 50000 页”，它就必须知道总行数。数据库不会在返回 20 条数据时顺手赠送一个准确的总数。`SELECT COUNT(*) FROM orders` 在 InnoDB 里没有放之四海皆准的 O(1) 魔法，它要扫描某个索引。即使优化器选了最短的二级索引，百万行也要付出几百毫秒。更糟的是，这条 `COUNT` 和分页 SQL 会同时打到库上，每一页都重复。 为什么 Feed、流水、消息列表很多时候根本不需要总页数？ 用户的动作是往下滑、点“加载更多”，他们不关心“第 3000 页 / 共 8000 页”。在持续写入的场景里，总数本身也在变化，展示总页数甚至可能误导用户。移动端、信息流、聊天记录天然是连续滚动体验。此时真正的需求不是“跳到第 n 页”，而是“给我上一页最后一条之后的 20 条”。所以 `Slice` 或 Keyset 比 `Page` 更合适。 深 OFFSET 为什么会慢？ `LIMIT 999980, 20` 的实现方式不是指哪打哪，而是从符合条件的第一行开始，一行一行读，读到 offset 位置后，再取 20 行。即便走索引，也至少要扫过 999980 个索引条目，还可能回表。页号越深，丢弃的数据越多，耗时接近线性增长。索引能帮的是排序和定位，但救不了“跳过大量行”这一步。 ## 04、架构 分页应该放在整个访问链路上看，而不是只改一个 SQL。我一般会把分页分成三层。 第一层是 API 契约。管理后台需要总页数，可以返回 `total`、`totalPages`、`page`、`size`。连续滚动场景则返回 `items` 和 `nextCursor`，不承诺总量。API 一旦承诺总页数，查询层就不得不做 COUNT。 第二层是查询层。Offset 分页适合有界数据、需要跳页、需要导出、数据量可控。Keyset 分页适合无界流、时间线、Feed、流水、消息列表。查询层要把游标翻译成稳定的 `WHERE` 条件，而不是继续套 `page/size`。 第三层是存储层。索引必须和排序条件对齐。常见订单列表按 `created_at DESC, id DESC` 排序，那就建 `KEY idx_created_id (created_at, id)`。如果游标不唯一，比如只用 `created_at`，同一毫秒订单可能重复，排序就会不稳定，数据可能丢或重。因此游标字段要带一个唯一键兜底，比如自增 `id`。如果 `id` 是无序 UUID，还可以考虑业务时间加单调序号，但首要原则是排序必须稳定。 架构上的取舍也很清晰：`Page` 方便，但代价是每次翻页都可能有 COUNT，深页不可控；`Slice` 少了 COUNT，但仍然基于 offset，深页问题没有根除；只有 Keyset 从索引值出发，才能把深页复杂度从 O(offset) 压到 O(size) 附近。 ## 05、实战 下面构造一个百万级订单表，看看不同分页方式差多少。 ```sql CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, user_id BIGINT NOT NULL, amount DECIMAL(10,2) NOT NULL, created_at DATETIME(3) NOT NULL, KEY idx_created_id (created_at, id) ); ``` 先批量造数据。可以写一个脚本分批插入 100 万行，`created_at` 模拟过去一年的时间分布，避免集中在一个时间点。 第 1 页查询： ```sql SELECT id, order_no, amount, created_at FROM orders ORDER BY created_at DESC, id DESC LIMIT 0, 20; ``` 在联合索引上，这个查询能直接命中前 20 条，执行很快，通常在亚毫秒到几毫秒量级。 第 50000 页查询： ```sql SELECT id, order_no, amount, created_at FROM orders ORDER BY created_at DESC, id DESC LIMIT 999980, 20; ``` `offset = (50000 - 1) * 20 = 999980`。InnoDB 需要从索引末尾往前数，扫过约 100 万行，再丢掉前 999980 行。实测这个查询会从几毫秒变成数百毫秒，甚至上秒。如果数据在缓冲池里、硬件较好，可能是 200～600 毫秒；一旦数据从磁盘冷读，情况更糟。 再执行一条 `Page` 场景下的 COUNT： ```sql SELECT COUNT(*) FROM orders; ``` 百万行表上，这条语句也需要扫索引，耗时通常和表大小、缓冲池相关，轻松达到数百毫秒。 换 Keyset 分页。假设上一页最后一条是： ```text created_at = '2025-01-15 10:20:30.123' id = 789012 ``` 下一页查询写成： ```sql SELECT id, order_no, amount, created_at FROM orders WHERE (created_at items; private Long nextId; private LocalDateTime nextCreatedAt; } ``` 查询层把 `nextId` 和 `nextCreatedAt` 作为下一页游标。不要返回 `total`，避免 COUNT。 简单对比： | 方式 | SQL 额外成本 | 第 1 页 | 第 50000 页 | |---|---:|---:|---:| | Offset | 无 COUNT | 约 1ms | 约 500ms | | Page + COUNT | 多一次 COUNT | 约 400ms | 约 1000ms | | Keyset | 无 COUNT | 约 1ms | 约 1ms | 数字只是量级，不是标准值，但趋势是确定的：Offset 越深越慢，Keyset 基本稳定。 ## 06、洞见 分页首先是数据库访问模式问题，其次才是 UI 问题。 很多项目是先设计一个带页码的分页条，再让后端返回 `Page`，数据库就开始多跑 COUNT，深页就开始 `OFFSET` 爆炸。这个顺序是反的。正确做法是先问三个问题：数据有没有明显边界？用户需要跳页还是连续加载？数据是否频繁增长？ 管理后台订单列表有边界，需要按条件筛选、跳到第几页、看总数，那就用 `Page`。Feed、流水、消息列表没有边界，用户几乎是“向下滑”，只关心接下来有没有内容，那就用 Keyset 或 `Slice`。在这些场景里强调“共多少页”不仅浪费数据库资源，有时候还是一种产品设计上的噪声。 关键还要理解索引。分页优化不是改一个参数叫 `pageable` 就能完成的。真正起作用的是排序字段和唯一键构成联合索引，让数据库可以用范围扫描替代大偏移。数据库访问模式选错，前端怎么设计都是补救；访问模式选对，分页才能稳定。 ## 07、行动 第一，管理后台继续用 `Page`，但要有保护措施。例如限制最大可跳页数，或者用近似总数、缓存 COUNT，避免每翻一页都执行全量 COUNT。100 万的表不是不能 COUNT，但 5000 万、1 亿行时，每次 COUNT 都可能成为慢查询。 第二，连续滚动场景不要再返回 `Page`。返回 `Slice` 或者自定义 `nextCursor`。接口可以是： ```text GET /orders/stream?createdAt=2025-01-15T10:20:30.123&id=789012&size=20 ``` 后端把 `createdAt` 和 `id` 组合成联合索引游标，SQL 写成等值加小于条件的组合。 第三，如果列表还需要支持条件筛选，Keyset 仍然可用，但游标必须在新的排序语义下保持稳定。例如筛选某个用户订单，索引可能是 `(user_id, created_at, id)`，游标要携带 `user_id`、`created_at`、`id`。查询条件需要保证同一排序键下不会漏数据。 第四，每次分页改造后，用 `EXPLAIN` 验证执行计划。关注 `type` 是否是 `range`，`rows` 是否接近页大小而不是全表。不要把“感觉快了”当依据。 ## 08、小结 `Pageable` 简化了 Java 分页代码，但没有消除 MySQL `OFFSET` 和 `COUNT` 的执行成本。`Page` 会为了总页数执行额外 COUNT，`Offset` 会在深页时扫描并丢弃大量行。对于管理后台这种有界、需要跳页的场景，可以继续忍受这些成本；对于 Feed、流水、消息列表这类连续滚动场景，Keyset Pagination 是更合理的默认解。记住一点：分页不是给数据库套一层 `page/size`，而是先想清楚数据访问模式和索引。 ## 09、链接与概念说明 - `Pageable`：Spring Data 分页参数抽象，把页码、页大小和排序传给查询层。 - `Page`：带总记录数和总页数的返回结构，通常触发额外 COUNT。 - `Slice`：不带总记录数，只说明是否有下一页，适合连续滚动。 - `Count Query`：为了获得总条数执行的 SQL，常见为 `SELECT COUNT(*) FROM ...`。 - `Offset Pagination`：`LIMIT offset, size` 模式，深页扫描代价高。 - `Keyset/Seek Pagination`：基于上一页游标的有序遍历范围查询。 - 联合索引：例如 `(created_at, id)`，是 Keyset 稳定的前提。 - 可参考 MySQL 官方文档《LIMIT Query Optimization》和 Spring Data Commons 中 `Pageable`、`Slice` 的说明。
