MySQL深分页：亿级表翻到1万页直接崩库，4种解法

从 12.4 秒到 30 毫秒：一个老 DBA 写给同事的分页优化实战

图1：LIMIT 1000000, 20 把数据库压出裂缝——12400ms vs 30ms

周三晚 11 点，刚躺下手机就炸了。运营同学发来一张监控图：订单后台翻页到第 5000 页，接口直接超时，CPU 100%，整个数据库实例卡死。

我远程一看，慢查询日志里躺着一行：

SELECT * FROM orders ORDER BY create_time DESC LIMIT 1000000, 20;

12.4 秒。一条 SQL 把 8 核 16G 的库直接打瘫。

改完之后，30 毫秒。同样的查询，同样的数据。本文就把这中间发生的事讲清楚——为什么 LIMIT 这么慢，4 种工业级解法怎么选，以及最关键的：上线前就要避开的坑。

一、问题现象：分页越翻越慢，到几千页就崩

这不是个例。我后来翻了一下过去半年的慢查询日志，超过 50% 的"翻页类慢查询"都长这样：

-- 运营后台：翻页查订单
SELECT * FROM orders WHERE user_id = 12345 ORDER BY create_time DESC LIMIT 1000000, 20;
-- 数据分析：导出用户行为
SELECT * FROM user_behavior ORDER BY id DESC LIMIT 500000, 100;
-- 商品列表：分页加载
SELECT id, name, price FROM products WHERE status = 1 ORDER BY id DESC LIMIT 200000, 50;

页码小的时候，几十毫秒返回；翻到第 5000 页，5 秒；翻到第 1 万页，12 秒以上。运维同事在旁边说：这表才 8000 万行，等涨到几亿，是不是连查询都提交不了？

答案：是的。OFFSET 越大，数据库需要扫的行数线性增长，查询耗时按 O(N) 走，到亿级量级确实会"算不动"。

二、底层原理：LIMIT 不是"跳过"，是"扫过去再扔"

图2：LIMIT 1000000, 20 的真实执行过程——扫了 100 万 + 20 行，只留 20 行

大部分开发者的直觉是：LIMIT 1000000, 20 ≈ 数据库从第 100 万行开始取 20 行。这是错的。

▍第一步：定位起点位置

MySQL 进入 create_time 的二级索引树，按顺序找到第 100 万 + 20 条记录的位置。

▍第二步：把这 100 万零 20 条全部加载到 Server 层

——不管你最终要不要这 100 万条，MySQL 都会先把它们读出来。

▍第三步：把前 100 万条全部丢弃，只保留最后 20 条返回

这一步更致命：因为查询是 SELECT *，MySQL 必须拿着每个 ID 回聚簇索引（主键索引）取完整字段。100 万次回表 = 100 万次随机磁盘 IO。

一句话总结：所谓的"深分页慢"，慢的不是 LIMIT 本身，而是 OFFSET 逼着数据库做大量"毫无意义的回表 + 排序 + 丢弃"。

这张图就是 tsight.io 真实事故复盘里截的——某电商平台 8000 万行订单表，翻到 5 万页时单次请求 12 秒，CPU 95%。同样的数据，优化后 30 毫秒以内。

三、解法对比：5 种优化方法，性能差距 1500 倍

图3：5 种分页方案在 8000 万行订单表上的实测延迟

我用同一张 8000 万行的 orders 表做了实测，每页 20 条，跳到第 5 万页（OFFSET = 999980），结果如下：

方案 延迟 适用场景
LIMIT 1000000, 20（反例） 12400 ms 永远不要在生产用
覆盖索引（仅查 id + create_time） 120 ms 仅需返回少量字段
延迟关联（Deferred Join） 35 ms 通用方案，90% 场景
游标分页（Cursor Seek） 8 ms 无跳页需求
Elasticsearch search_after 15 ms 复杂过滤、亿级跳页

性能差距 1500 倍。下面把这 4 种正优化逐个拆开讲。

四、解法一：覆盖索引——能省回表就省回表

▍适用场景

查询只返回少量字段，且这些字段刚好都被索引覆盖。

▍做法：建联合索引

假设你的查询是：

SELECT id, create_time FROM orders WHERE status = 1 ORDER BY create_time DESC LIMIT 1000000, 20;

建一个 (status, create_time) 的联合索引：

ALTER TABLE orders ADD INDEX idx_status_createtime (status, create_time);

现在 MySQL 在二级索引树里就能拿到 id 和 create_time，不用回表，扫描速度从 O(N) 降到 O(offset+size)。

局限：业务上往往要 SELECT *，这一招就失灵。订单详情、商品列表都需要完整字段。

五、解法二：延迟关联——面试最推荐的方案

▍核心思想

先在二级索引里把目标 20 个 ID 找出来，再拿这 20 个 ID 回表取完整字段。回表次数从 100 万次降到 20 次。

▍SQL 改写

反例：

-- 反例：100 万次回表
SELECT * FROM orders ORDER BY create_time DESC LIMIT 1000000, 20;

正解：

-- 正解：先找 ID，再回表
SELECT o.*
FROM orders o
INNER JOIN (
SELECT id FROM orders
ORDER BY create_time DESC
LIMIT 1000000, 20
) AS tmp ON o.id = tmp.id;

原理：内层子查询只查 id，利用覆盖索引，不需要回表，速度极快；拿到 20 个 ID 后，外层只回表 20 次。

实测：从 12400ms 降到 35ms，性能提升约 350 倍。这是大多数业务的"标准答案"。

▍进一步优化：别用 INNER JOIN，用半连接提示

如果 explain 之后发现临时表太大，可以强制走半连接：

SELECT o.* FROM orders o
WHERE o.id IN (
SELECT id FROM orders
ORDER BY create_time DESC
LIMIT 1000000, 20
);

MySQL 8.0 对 IN + 子查询有优化，在 OFFSET 不大时效果和 JOIN 相当，看执行计划选优。

六、解法三：游标分页——根本不让 OFFSET 出现

▍核心思想

既然 OFFSET 是万恶之源，那就彻底废掉它。改成"用上一页最后一条的 ID 去定位下一页起点"。

▍SQL 改写

假设上一页最后一条的 id 是 999980：

-- 上一页的 SQL（拿最后一条的 id）
SELECT id FROM orders ORDER BY create_time DESC LIMIT 999980, 1;
-- 假设结果是 8881234
-- 下一页的 SQL
SELECT * FROM orders WHERE id < 8881234 ORDER BY id DESC LIMIT 20;

无论翻到第几页，性能都是 O(1)——只用主键索引定位起点，扫 20 行就结束。

实测：8 毫秒。比延迟关联还快 4 倍。

▍局限：不能跳页

这是它最大的"问题"——用户不能直接跳到第 5000 页，因为服务端不知道第 5000 页起始的 id 是多少。

但话说回来：百度搜索结果、淘宝商品列表、微信朋友圈、抖音关注流——这些大厂 C 端场景全部禁用了 OFFSET，全部用游标。普通用户真的很少跳页。

如果业务一定要"跳页"，可以折中：

-- 后台管理：跳页用延迟关联
SELECT * FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY create_time DESC LIMIT 1000000, 20) tmp
ON o.id = tmp.id;
-- 前台用户：下一页用游标
SELECT * FROM orders WHERE id < 8881234 ORDER BY id DESC LIMIT 20;

后台给运营用，允许跳页，慢一点没关系；前台给用户用，只给"上/下一页"，必须快。

七、解法四：Elasticsearch 兜底——亿级跳页的终极方案

▍什么时候用

当业务要求：亿级数据 + 支持跳页 + 复杂过滤（多字段组合查询），MySQL 已经扛不住了，把数据同步到 Elasticsearch 用 search_after。

▍做法：用 search_after 而不是 from/size

ES 的 from/size 本质和 MySQL 的 OFFSET 一样，深分页性能同样差。正确做法是用 search_after：

GET /orders/_search
{
"query": { "bool": { "must": [
{ "term": { "user_id": 12345 }},
{ "range": { "create_time": { "gte": "2025-01-01" }}}
]}},
"sort": [
{ "create_time": "desc" },
{ "id": "desc" }
],
"search_after": [1714531200000, 8881234],
"size": 20
}

原理：传上一页最后一条的 sort 值，ES 直接定位起点，性能稳定 O(1)。

实测：15 毫秒。亿级数据 + 跳页 + 复杂过滤都能扛，但代价是要维护一套 ES 集群 + 数据同步链路。

▍同步方案选型

小数据量用 Canal + Kafka + ES；大数据量用 Logstash JDBC；实时性要求高用 Flink CDC。看团队技术栈选，不要硬上。

八、避坑提示：上线前必须检查的 8 件事

这些坑我都踩过，每一条都是真金白银换来的教训。

▍坑 1：联合索引顺序写反

建 (create_time, status) 而不是 (status, create_time)。WHERE 命中 status 之后再按 create_time 排序，索引才能用上。

▍坑 2：ORDER BY 字段不在索引里

如果 ORDER BY create_time 但索引是 (status)，MySQL 会 filesort，性能崩塌。索引必须包含排序字段。

▍坑 3：分页查询用事务包住

事务里跑深分页会持有锁不释放，并发场景下整个库都会被拖慢。分页查询务必放在事务外。

▍坑 4：COUNT(*) 跟着深分页一起跑

SELECT COUNT(*) ... + LIMIT ... 在 InnoDB 下是 O(N) 全表扫描。先把总数缓存到 Redis，每次翻页只查数据不查总数。

▍坑 5：游标分页的"数据漂移"

如果用 WHERE id < last_id 翻页，新插入的数据会影响排序。需要用 (create_time, id) 联合游标，不只是 id。

▍坑 6：分库分表后的深分页

分片后每个库都查 OFFSET + LIMIT 再内存合并，性能极差。要么用 ES，要么按 user_id 哈希到同一片。

▍坑 7：PageHelper 的"傻瓜式"调用

PageHelper.startPage(50000, 20) 内部自动生成 LIMIT 1000000, 20——它不优化，只翻译。生产环境禁用 PageHelper 的深翻页，改成手写游标。

▍坑 8：以为加索引就万事大吉

覆盖索引能省回表，但 OFFSET 依然要扫过前 N 行才丢弃。加索引只能优化 10 倍，治不了 1000 倍——必须改 SQL。

九、速查清单：4 种方案怎么选

图4：分页方案速查表——按业务特征选

记不住没关系，把这张图存下来：

▍只要"下一页" → 用 Cursor Seek

最快，O(1) 性能。朋友圈、消息流、商品列表用户端首选。

▍要跳页 + 数据量亿级 → 用 Deferred Join

通用方案，90% 后台场景适用。35 毫秒以内，满足大多数业务。

▍只查几个字段 → 用 Covering Index

在索引覆盖的场景下零回表，性能好得吓人。但要 SELECT * 就别想了。

▍复杂过滤 + 跳页 + 亿级 → 用 Elasticsearch

架构级方案，性能稳定。代价是要维护 ES 集群和数据同步链路，看团队能力决定。

最后说一句：分页优化的本质，是"减少无效回表"。

理解了 OFFSET 为什么慢（因为它逼着数据库做"扫 + 丢"的无效操作），所有优化方案都能一句话讲明白——它们的核心思路都是"跳过"那 100 万次无意义的回表。

上线前把 SQL review 一遍，看 EXPLAIN 的 rows 列估算有没有爆 10 万；review 一下代码，看 PageHelper.startPage 的页码有没有人传过大数。这两个动作，能挡掉 90% 的线上分页事故。

你踩过哪些分页的坑？评论区聊聊，看看谁的方案更野。觉得有用的话，点赞收藏，下一篇写"连接池调优"，关注我别错过。

#MySQL优化 #深分页 #数据库性能 #DBA实战 #后端开发