MySQL 分页越往后翻越慢改成游标分页

为什么 MySQL 分页越往后翻越慢

在使用 MySQL 开发列表接口时，很多开发者会直接使用 LIMIT offset, pageSize 进行分页。你可能会发现一个现象：前几页查询很快，但越往后翻页（offset 越来越大）响应时间就越长，甚至导致慢查询。

根源：OFFSET 不是跳过，而是“扫描并丢弃”

MySQL 执行 SELECT * FROM table ORDER BY id LIMIT 1000000, 20 时，实际上会经历以下过程：

1. 根据 ORDER BY 条件扫描索引或数据行；
2. 从第一行开始计数，一直数到第 1,000,000 行；
3. 丢弃前面 1,000,000 行，只返回接下来的 20 行。

也就是说，offset 越大，需要扫描和丢弃的行数越多，CPU 和 I/O 开销随页码线性增长。即使用到了覆盖索引，也需要回表读取完整行，性能损耗依旧明显。

常见误区：添加索引并不能根治

在 id 上建了索引，LIMIT 1000000, 20 依然很慢，因为 MySQL 需要沿着索引扫描并计数丢弃 100 万条记录。这与索引的选择性无关，而是 offset 机制本身的问题。

游标分页：用“游标”替代 OFFSET

游标分页（Cursor-based Pagination）的思路是：记录上一页最后一条数据的某个唯一、有序字段（常用主键 ID 或时间戳），下一页直接从该字段之后开始取数据。

这样可以避免数据库扫描无关数据，只需利用索引快速定位起点，然后取 N 条记录即可，复杂度为 O(log N + pageSize)，与偏移量无关。

如何将传统分页改造为游标分页

1. 选择合适的游标字段

游标字段必须满足：

• 有序：通常是自增主键 id（BIGINT）或创建时间 created_at；
• 唯一：如果使用时间戳，需要加二级排序（比如 id）以确保分页不丢数据；
• 有索引：游标字段上必须有索引（主键自带索引，时间字段需单独加索引）。

推荐使用自增主键 ID作为游标，简单可靠。

2. 查询写法改造

传统写法（OFFSET）

SELECT * FROM articles ORDER BY id LIMIT 1000000, 20;

游标分页写法（第一页）

SELECT * FROM articles ORDER BY id LIMIT 20;

游标分页写法（后续页，传上一页最后一条的 id）

假设上一页最后一条记录的 ID 是 123456：

SELECT * FROM articles
WHERE id > 123456
ORDER BY id LIMIT 20;

必须使用 > 而不是 >=，否则会重复返回边界记录。

3. 降序游标分页

如果业务需要按 id 降序展示（例如消息列表最新在前），游标逻辑要反转。

第一页（取最新 20 条）

SELECT * FROM articles ORDER BY id DESC LIMIT 20;

上一页最后一条 id 为 999

SELECT * FROM articles
WHERE id < 999
ORDER BY id DESC LIMIT 20;

4. 使用时间戳作为游标的注意事项

如果使用 created_at（例如 DATETIME(3)）作为游标，必须处理可能重复的时间值。标准做法是加上唯一字段作为二级排序，并且 WHERE 条件使用复合比较（行值表达式）：

SELECT * FROM orders
WHERE (created_at, id) > ('2025-03-10 12:30:00.000', 8765)
ORDER BY created_at, id LIMIT 20;

MySQL 5.7+ 支持行值表达式，等效于：

WHERE created_at > '2025-03-10 12:30:00.000'
   OR (created_at = '2025-03-10 12:30:00.000' AND id > 8765)

这样可以保证分页的稳定性，不重不漏。

5. 在代码中传递游标

前端无法再传页码，而是传上一页最后一条记录的游标值和方向。接口设计示例：

GET /api/articles?cursor=123456&limit=20  // 向后翻页
GET /api/articles?cursor=999&limit=20&direction=prev // 向前翻页（可选）

后端解析 cursor，动态拼接 SQL。若 cursor 为空，则为首页查询。

游标分页的优缺点

优点

• 性能恒定：无论翻到第几页，查询时间几乎不变，只取决于索引查找速度；
• 避免重复/遗漏：在数据实时插入的场景下（如按照时间排序），使用游标分页比 offset 更稳定。Offset 分页因数据新增可能导致同一记录在不同页重复出现或消失。

局限与解决方案

实战改造：性能对比测试

模拟 200 万条数据表 test_cursor：

CREATE TABLE test_cursor (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    data VARCHAR(200),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- 插入 200 万条数据（略）

• OFFSET 方式：SELECT * FROM test_cursor ORDER BY id LIMIT 1900000, 20
  执行时间：约 1.2 秒（随 offset 越大越慢）
• 游标方式：SELECT * FROM test_cursor WHERE id > 1900000 ORDER BY id LIMIT 20
  执行时间：约 0.001 秒

可见分页越深，游标分页的优势越明显。

改造步骤总结

1. 确定游标字段：选择有索引、唯一且单调递增/递减的列；
2. 修改数据访问层：将基于 page/offset 的查询替换为 WHERE id > cursor ORDER BY id LIMIT n；
3. 调整 API：入参改 cursor，返回结果中附带每条记录对应的游标值（用于前端下一次请求）；
4. 处理降序、多字段排序等特殊情况；
5. 若必须保留页码概念，可采用“估算总数+有限跳页”或引入搜索引擎辅助。

对于大多数高并发、深分页场景，游标分页是低成本高收益的优化方案。它让 MySQL 只需要做它最擅长的事——基于索引快速定位并返回少量数据，而不是做大量无意义的计数和丢弃。