深分页为什么慢
LIMIT 1000000, 20 看起来只是跳过前一百万行，实际执行时 MySQL 要先读出这一百万零二十行，把前一百万行丢掉，再返回最后二十行。如果查询还需要回表，每一行都要按主键去聚簇索引取一次数据，随机 IO 会把耗时进一步放大。翻到后面几页，接口从几十毫秒变成几秒，这不是数据库“变慢”了，而是偏移量本身就有线性成本。
覆盖索引能缓解但不能根治：即使 EXPLAIN 显示 Using index，MySQL 仍然要扫描并丢弃 offset 条索引记录。数据量到千万级，任何依赖大偏移量的分页都会遇到瓶颈。
延迟关联：先取主键再回表
最常见的优化是“延迟关联”。子查询只查主键，利用覆盖索引完成过滤和排序，外层再用主键关联取完整字段，回表次数从一百万次降到二十次：
SELECT o.id, o.user_id, o.amount, o.created_at
FROM orders o
INNER JOIN (
SELECT id
FROM orders
WHERE status = 'PAID'
ORDER BY id
LIMIT 1000000, 20
) t ON t.id = o.id;
要让子查询走覆盖索引，需要类似 (status, id) 的联合索引。这个写法仍然要扫描一百万条索引记录，只是避免了大量回表，适合无法改造接口、只能改 SQL 的场景。它能把几秒降到几百毫秒，但解决不了“越翻越慢”的趋势。
游标分页：用上一页的位置代替 offset
真正把复杂度降下来的方案是 keyset 分页，也叫游标分页。不传页码，传上一页最后一条记录的排序键，查询变成“从这个位置往后取 20 条”：
SELECT id, user_id, amount, created_at
FROM orders
WHERE status = 'PAID'
AND id > 1000000
ORDER BY id
LIMIT 20;
配合 (status, id) 索引，MySQL 可以直接定位到 id > 1000000 的位置，扫描 20 行就结束，耗时和页码无关。代价是不能跳页，前端只能“下一页/上一页”，适合信息流、消息列表、导出这类顺序浏览场景。
如果排序字段不是主键，要用“排序字段 + 主键”组成游标，避免排序值重复导致跨页丢数据：
SELECT id, user_id, amount, created_at
FROM orders
WHERE status = 'PAID'
AND (created_at, id) < ('2024-06-01 12:00:00', 1000000)
ORDER BY created_at DESC, id DESC
LIMIT 20;
这里必须保证 (status, created_at, id) 有联合索引，并且排序方向与索引一致。MySQL 8.0 对行构造器比较的支持比旧版本好，但上线前仍要用 EXPLAIN ANALYZE 确认没有退化成全表扫描。
业务层的配合
不是所有分页都值得用游标。后台管理系统需要“跳到第 50 页”，产品经理不会接受只能点下一页，这时候可以限制最大页数，比如只允许前 100 页，超出后要求加时间范围、状态等过滤条件。把“无限翻页”改成“带条件的检索”，往往比任何 SQL 技巧都有效。
数据量再大，应该考虑归档和分区。历史订单按年归档到冷表，热表只保留近一年数据，分页的基数直接降一个数量级。搜索类需求交给 Elasticsearch，用 search_after 做深分页，MySQL 只负责按主键取详情。
总数 COUNT(*) 也是深分页的隐藏成本。翻到第一百万行时，COUNT(*) 可能比数据查询还慢。产品上可以接受“约 1.2 万条”这种近似值，或者把总数缓存起来，不要每次翻页都精确统计。
排查步骤
先用 EXPLAIN ANALYZE 对比两种写法，重点看 actual rows 和 loops：
EXPLAIN ANALYZE
SELECT id, user_id, amount, created_at
FROM orders
WHERE status = 'PAID'
ORDER BY id
LIMIT 1000000, 20;
EXPLAIN ANALYZE
SELECT id, user_id, amount, created_at
FROM orders
WHERE status = 'PAID'
AND id > 1000000
ORDER BY id
LIMIT 20;
再看回表次数和扫描行数：
SHOW SESSION STATUS LIKE 'Handler_read%';
执行前后对比 Handler_read_next、Handler_read_rnd_next 的增量，能直观看到 offset 写法多扫了多少行。如果 EXPLAIN 里出现 Using filesort，说明排序没走索引，先补索引再谈分页优化。
常见坑
1. 排序字段不唯一
只按 created_at 排序，同一秒有多条记录时，MySQL 不保证跨页顺序稳定，用户会看到重复或漏掉的数据。排序键一定要带主键做兜底，例如 ORDER BY created_at DESC, id DESC。
2. 游标参数被篡改
把 lastId 直接暴露给前端，用户可以传一个任意值跳过权限过滤。游标值要做签名或服务端加密，并且和查询条件绑定，不能只当成一个普通参数。
3. 深分页和 JOIN 一起用
多表 JOIN 时，LIMIT 作用在最终结果集上，参与 JOIN 的每一行都可能被扫描和排序。先把主表分页取出来，再关联详情，通常比直接 JOIN 后再分页稳定。
4. 用 LIMIT 做“导出”
导出全量数据时写 LIMIT 0, 1000000，一次性把百万行读进内存，数据库和 JVM 都扛不住。导出应该按主键分批拉取，每批几千条，处理完再取下一批。
5. 忽略索引顺序
游标分页的 WHERE 条件里，等值列要放在范围列前面。(status, id) 可以用，(id, status) 在 status 是范围条件时效果会差很多。索引顺序要和查询模式匹配，不能只看字段有没有被索引。
小结
深分页的本质是“数据库为跳过的行付出了真实扫描成本”。延迟关联减少回表，游标分页消除 offset，业务限制和归档降低数据量。三种手段可以叠加使用，但优先选游标分页——它把复杂度从 O(offset) 降到了 O(limit)。
评论