MySQL丨MySQL 深分页为什么慢？从 LIMIT 100000,20 到游标分页优化

type

Post

status

Published

date

Dec 17, 2025

slug

mysql4

summary

一、深分页的本质：offset 越大，被丢弃的数据越多

分页查询最常见的写法是：

第一页：

第 5000 页：

表面上看，两条 SQL 都只返回 20 条数据。

但它们的执行成本完全不一样。

第一页只需要拿到前 20 条。

第 5000 页通常需要先得到前 100020 条，再丢掉前 100000 条。

可以简单理解成：

LIMIT 100000, 20
= 按照查询条件和排序规则产生 100020 条候选结果
= 丢弃前 100000 条
= 返回最后 20 条

所以深分页真正慢的地方是：

为了返回后面的少量数据，MySQL 前面做了大量无效工作。

这些无效工作可能包括：

扫描
排序
过滤
回表
丢弃

offset 越大，MySQL 要处理和丢弃的数据就越多。

二、MySQL 可能怎么执行深分页？

同样是这条 SQL：

不同索引情况下，执行成本差别很大。

1. 没有合适索引：扫描后排序

如果没有合适索引，MySQL 可能需要：

扫描大量订单数据
→ 过滤 status = 1
→ 根据 create_time、id 排序
→ 取出前 100020 条
→ 丢弃前 100000 条
→ 返回 20 条

这种情况下，真正重的是前面的扫描和排序。

执行计划里可能会看到：

Using filesort

这里要注意，filesort 并不一定表示真的写了磁盘文件。

它的意思是：MySQL 需要额外排序，而不是直接利用索引顺序拿到结果。

如果排序数据量很大，内存放不下，才可能进一步使用磁盘临时文件，性能会更差。

2. 有合适索引：可以减少排序，但不能消除 offset

如果有联合索引：

那么这条 SQL：

MySQL 有机会沿着索引顺序扫描。

这比全表扫描后排序要好很多。

但要注意：

有索引不等于可以直接跳到第 100001 条。

MySQL 仍然要沿着索引往后扫描，跳过前 100000 条，再取 20 条。

所以索引可以减少排序成本，但不能让大 offset 消失。

3. `SELECT *`：可能带来大量回表成本

如果 SQL 写成：

即使 MySQL 可以通过索引找到排序顺序，也不代表一定很快。

因为二级索引里通常只保存索引字段和主键值，不保存完整行数据。

当你 SELECT * 时，MySQL 需要根据二级索引里的主键值，再去聚簇索引里读取完整行。

这个过程就是回表。

深分页里 SELECT * 的问题不只是字段多、网络传输大，更关键的是：

它可能破坏覆盖索引，让数据库在扫描大量 offset 数据时产生更多完整行读取和回表成本。

所以列表页不要轻易写 SELECT *。

列表页需要什么字段，就查什么字段。

详情页需要完整数据，再根据 ID 查详情。

三、怎么验证：用 EXPLAIN 看执行计划

SQL 优化不能靠感觉,要用 EXPLAIN 验证。

比如：

重点看这几个字段：

type
key
rows
Extra

它们分别代表：

字段	重点看什么
`type`	访问类型，是否从全表扫描变成索引范围扫描
`key`	实际使用了哪个索引
`rows`	优化器预估要扫描多少行
`Extra`	是否有 `Using filesort`、`Using index`、`Using temporary`

可以用下面这个示意表建立直观感知：

场景	key	rows	Extra	说明
无合适索引	`NULL`	1000000	`Using filesort`	扫描多，排序重
有联合索引但 `SELECT *`	`idx_status_create_time_id`	100020	`Using where`	可以利用索引顺序，但可能回表
覆盖索引	`idx_status_create_time_id`	100020	`Using index`	扫描索引记录，成本更低
游标分页	`idx_status_create_time_id`	较少	`Using where`	从游标位置继续扫描

这个表不是固定执行结果。

真实的 rows、Extra 会受到数据分布、索引区分度、MySQL 版本、统计信息影响。

但它能说明一个核心思路：

深分页优化是要让执行计划从“扫描大量数据 + 排序 + 回表”，尽量变成“命中索引 + 少扫描 + 少回表”。

尤其要关注 Extra。

如果看到：

Using filesort

说明排序没有很好地利用索引。

如果看到：

Using index

通常说明查询字段被索引覆盖了，不需要回表，这是比较理想的情况。

四、第一类优化：不要让用户无限翻页

有些深分页问题，其实不应该完全交给 MySQL 硬扛。

比如一个后台订单列表，用户真的需要手动翻到第 5000 页吗？

大多数情况下，用户不是想看第 5000 页，而是想找到某一批订单。

这时候更合理的方式是增加筛选条件，让用户缩小数据范围。

比如：

订单时间范围
订单状态
用户 ID
商品 ID
支付状态
订单来源
关键词搜索

如果用户想看很久之前的数据，不应该让他一页一页往后翻，而应该让他通过条件定位。

对于后台系统，也可以限制最大翻页深度。

比如：

最多查看前 100 页。
超过 100 页，请使用筛选条件。

或者：

最多翻到第 500 页。
更深的数据请通过导出任务处理。

这看起来不像 SQL 优化，但在线上非常有效。

因为好的优化，是从产品和系统边界上避免它频繁发生。

五、第二类优化：导出场景不要走实时分页

还有一种常见场景：

运营要导出全部订单做分析。

如果这个时候还让前端一页一页请求分页接口，本质上就是不断制造深分页查询。

比如：

第 1 页
第 2 页
第 3 页
……
第 5000 页

越往后越慢，数据库压力也越来越大。

这种场景不应该走实时分页接口，而应该改成异步导出任务。

流程可以变成：

用户点击导出
→ 后端创建导出任务
→ 后台任务分批查询数据
→ 生成 Excel / CSV 文件
→ 导出完成后通知用户下载

对应链路可以这样理解：

这样做有几个好处：

不会让用户请求一直阻塞
可以控制后台任务并发
可以分批读取数据
可以避开业务高峰期
可以支持失败重试和进度查询

分页接口适合实时查看少量数据。

全量导出应该走异步任务。

六、第三类优化：避免 SELECT *，设计联合索引

很多分页 SQL 慢，不只是因为 LIMIT，还因为字段查得太多、排序没有走好索引。

比如：

这个查询里有三个关键点：

WHERE status = 1
ORDER BY create_time DESC, id DESC
SELECT *

优化时不能只盯着 LIMIT。

1. 列表页只查必要字段

更好的写法是：

列表页只展示列表字段。

详情页再根据 id 查完整数据。

这样可以减少：

行数据读取
回表成本
网络传输
对象映射成本

但这里要注意一个细节。

如果索引只有：

那么它只能覆盖：

不能覆盖：

因为 order_no、user_id、amount 不在这个索引里。

如果想让列表字段也被覆盖，理论上可以把这些字段也放进联合索引：

但这会让索引变宽，增加存储成本和写入维护成本。

所以真实业务里要权衡：

不是所有列表字段都值得为了覆盖索引塞进联合索引里。

如果字段较少、查询非常高频，可以考虑覆盖索引。

如果字段较多、更新频繁，就不要盲目建很宽的索引。

2. 联合索引要同时服务 WHERE 和 ORDER BY

对于这个查询：

可以考虑建立联合索引：

这里的逻辑是：

status：等值过滤字段
create_time：排序字段
id：稳定排序字段

很多初学者会误以为，只要给 create_time 建索引就够了：

但这个 SQL 里不只有排序，还有：

所以索引设计要同时考虑过滤和排序。

更准确地说：

联合索引要服务整条 SQL 的访问路径。

3. 为什么排序要加 id？

分页最好不要只写：

因为 create_time 可能重复。

如果很多订单的创建时间相同，只按 create_time 排序，结果顺序可能不稳定。

分页时就可能出现：

第一页出现过的数据，第二页又出现
某些数据被跳过
同一批数据在不同查询中顺序变化

所以更推荐写成：

用 id 作为兜底排序字段，形成稳定顺序。

稳定排序是分页查询里非常重要但经常被忽略的细节。

七、第四类优化：必须跳页时，用覆盖索引 + 延迟关联

如果业务必须支持跳到第 5000 页，比如后台管理系统要求任意页码跳转，那游标分页不一定能直接替代。

这时候可以考虑：

覆盖索引 + 延迟关联。

原 SQL 是：

它的问题是：深分页阶段可能扫描大量数据，而且 SELECT * 可能导致大量回表。

可以改成两步。

第一步，先只查目标页的主键 ID：

如果有这个索引：

那么内层子查询有机会只扫描索引记录。

第二步，再根据这 20 个 ID 查询完整数据：

这个优化的核心是：

深分页阶段先用覆盖索引定位 ID，最后只回表查询目标页的 20 条完整数据。

它把深分页阶段扫描的数据从“完整行记录”尽量降级成“索引记录”。

这样可以减少大量无意义的整行读取和回表成本。

外层这里最好再写一次：

因为 JOIN 之后的结果顺序不一定天然保持内层子查询的顺序。

也就是说，内层排序是为了找到目标页 ID，外层排序是为了保证最终返回给用户的结果顺序稳定。

但延迟关联不是银弹。

它适合：

必须保留页码跳转
内层子查询能走覆盖索引
排序字段和过滤字段比较清晰

它不适合：

过滤条件非常复杂
排序无法利用索引
内层子查询本身就很慢
业务可以改成游标分页

还要特别注意：

延迟关联不能消除 offset，它只是降低深分页扫描过程中的数据读取成本。

所以它是降成本方案，不是根治方案。

八、第五类优化：连续翻页时，用游标分页

如果业务不需要“跳到第 N 页”，而是类似信息流、消息列表、订单列表这种“下一页、下一页”的加载方式，更推荐使用游标分页。

传统 offset 分页是：

它的含义是：

跳过前 100000 条，再取 20 条。

游标分页是：

它的含义是：

从上一次看到的位置继续往后取 20 条。

比如第一页返回的最后一条记录 ID 是 5000。

下一页就可以这样查：

这样 MySQL 可以通过索引定位到 id > 5000 的位置，然后继续向后扫描 20 条。

它不需要重新扫描前面已经看过的数据。

所以游标分页比 offset 分页快，本质原因是：

offset 分页每次都要跳过前面的数据；游标分页可以从上一次的位置继续扫描。

按 create_time 排序时，游标分页怎么写？

很多业务不是按 ID 排序，而是按创建时间排序。

比如订单列表通常是：

这时候不能只用 lastId。

因为 create_time 可能重复。

更稳妥的方式是使用复合游标：

lastCreateTime + lastId

第一页查询：

拿到第一页最后一条数据后，记录：

lastCreateTime
lastId

下一页查询：

这里的逻辑是：

先按 create_time 倒序翻页
如果 create_time 相同，再按 id 倒序翻页

对应索引可以考虑：

游标分页的关键不是简单地把：LIMIT 100000, 20

改成：

而是要维护一个稳定的查询边界。

也就是说：

排序字段、游标字段、比较方向必须一致，排序结果必须稳定，否则容易出现重复数据或漏数据。

游标分页也有边界

游标分页适合：

上一页 / 下一页
下拉加载
滚动加载
消息列表
订单列表
日志列表
按照固定顺序继续查询

但它不适合：

直接跳到第 5000 页
展示精确总页数
用户频繁切换复杂排序字段
后台管理系统强依赖任意页码跳转

因为游标分页依赖“上一次最后一条记录的位置”。

如果用户直接输入：

跳到第 5000 页

游标分页并不能天然知道第 5000 页从哪里开始。

如果一定要支持上一页，可以保存上一页的游标，或者反向查询后在应用层调整顺序。

但整体上，游标分页天然更适合“继续向后加载”，不适合复杂页码导航。

所以真实系统里一般是这样取舍：

前台信息流：优先游标分页
后台管理页：限制最大页码 + 增加筛选条件
导出场景：异步导出
必须深跳页：覆盖索引 + 延迟关联降低成本

九、别忽略 COUNT(*)

很多分页接口还会返回总条数：

共 500000 条
共 25000 页

于是后端经常会写两条 SQL：

很多人只盯着第二条分页 SQL，却忽略了第一条 COUNT(*)。

不是所有 COUNT(*) 都慢。

如果条件很精准，比如：

并且有合适索引，那么统计成本可能还能接受。

危险的是：

大表
宽条件
低选择性筛选
频繁实时统计
每次分页都要精确总数

比如：

如果一半订单都是 status = 1，这个 COUNT(*) 可能需要扫描大量索引记录。

所以分页接口不一定每次都要实时返回精确总数。

可以根据业务场景做取舍：

不展示总页数，只返回 hasNext
缓存总数
定时统计
异步统计
使用近似值

很多系统慢，不只是慢在查当前页，也可能慢在每次分页前都要做一次大范围实时统计。

十、深分页优化的完整决策链路

深分页优化不要一上来就说“加索引”或者“改游标分页”。

更合理的思路是先判断业务场景，再决定技术方案。

可以用这张图理解：

这张图可以概括整篇文章的核心：

能不深翻页，就不要深翻页；
能用游标分页，就不要用大 offset；
必须保留页码跳转时，再考虑覆盖索引 + 延迟关联；
分页接口慢，也要检查 COUNT(*) 和导出场景。

十一、怎么回答深分页优化？

如果问：

MySQL 深分页为什么慢？怎么优化？

好的回答可以分成四层。

第一层：解释慢的原因

LIMIT offset, size 不是直接跳到 offset 位置，而是通常需要按照执行计划产生 offset + size 条候选结果，再丢弃前面的 offset 条。

offset 越大，中间被扫描、排序、过滤、丢弃的数据就越多，所以深分页会越来越慢。

第二层：解释执行计划差异

如果没有合适索引，可能会全表扫描后 Using filesort。

如果有合适索引，可以沿索引顺序扫描，减少排序成本，但仍然要跳过 offset。

如果查询写了 SELECT *，还可能破坏覆盖索引，产生大量回表成本。

所以深分页不只是 LIMIT 的问题，还和索引、排序、查询字段、回表有关。

第三层：给出优化方案

可以从业务和技术两层优化。

业务上：

限制最大页码
增加筛选条件
导出改异步任务

SQL 上：

避免 SELECT *
设计 WHERE + ORDER BY 联合索引
用 EXPLAIN 验证执行计划
必须跳页时用覆盖索引 + 延迟关联
连续翻页时用游标分页
大范围 COUNT 做缓存、异步或近似统计

第四层：说明边界

游标分页性能更好，但不适合任意跳页。

延迟关联可以减少深分页阶段的回表成本，但不能消除 offset。

COUNT(*) 不是一定慢，但在大表、宽条件、低选择性、频繁实时统计的场景下，很容易成为隐藏瓶颈。

最后可以总结成一句话：

深分页优化的核心，是尽量不要让 MySQL 扫描、排序、回表、丢弃那么多无用数据。