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

简化版

深分页慢，是因为 limit offset, size 需要先扫描并丢弃 offset 条记录，再返回 size 条，offset 越大浪费越多。常见优化是游标分页，或先用覆盖索引取目标页主键，再延迟关联回表查询完整行。

详细版

例如：

select * from orders order by id limit 1000000, 20;

MySQL 不是直接跳到第 1000000 行，而是要按顺序找到前 1000020 行，丢弃前 1000000 行后返回 20 行。如果 select * 还导致大量回表，成本更高。

优化方式：

游标分页：适合连续翻页。

select *
from orders
where id > ?
order by id
limit 20;

延迟关联：适合保留页码跳转。

select o.*
from orders o
join (
  select id from orders order by id limit 1000000, 20
) t on o.id = t.id;

子查询尽量走覆盖索引，只拿主键，外层只回表目标页记录。

完整版教学

一、深分页慢在“丢弃很多行”

limit 20 很快，不代表 limit 1000000, 20 也快。后者为了知道第 1000000 行之后是哪 20 行，需要先按过滤和排序规则走过大量记录。

如果排序字段有索引，MySQL 可以沿索引扫描，但仍要跳过很多索引项。如果查询列不在索引里，还可能伴随大量回表。慢的不是最终返回 20 行，而是前面跳过的一大段。

用数字看，limit 1000000, 20 至少要找到 1000020 条符合排序规则的记录，再丢掉前 1000000 条。如果 select * 导致每条候选都回表，前面被丢弃的记录也可能付出回表成本。最终用户只看到 20 条，但数据库已经做了接近 100 万级别的无效工作。

offset 分页：
扫描第 1 条
扫描第 2 条
...
扫描第 1000000 条，丢弃
扫描第 1000001-1000020 条，返回

二、游标分页是最常用优化

游标分页记录上一页最后一条的排序键，下次从这个位置继续：

select *
from orders
where id > 1000000
order by id
limit 20;

数据库可以直接定位到 id > 1000000 的范围起点，再向后取 20 条。这样不会从头扫描并丢弃大量记录。

如果按时间倒序翻页，建议加稳定的第二排序键：

where (create_time, id) < (?, ?)
order by create_time desc, id desc
limit 20;

这样能避免相同时间下重复或漏数据。

游标分页适合信息流、订单列表、消息列表这类“上一页/下一页”场景。它把“第 N 页”转换为“从上次看到的位置继续”，数据库可以利用索引定位范围起点。例如 (create_time,id) 索引下，上一页最后一条是 ('2026-07-20 10:00:00', 8888)，下一页就从比它更早的位置继续取 20 条。

分页方式	查询成本	适合场景	局限
offset 分页	offset 越大越慢	页数浅、后台管理	深页浪费扫描
游标分页	接近稳定	连续翻页、信息流	不适合任意跳页
延迟关联	降低回表	必须支持跳页	仍要跳过大量索引项

记忆钩子：游标分页快，是因为它让数据库“从位置继续走”，不是“从头数到第 N 页”。

三、延迟关联降低回表成本

有些后台系统必须支持跳到第 N 页，这时不能完全改成游标分页。可以先用窄索引找主键，再回表：

select o.*
from orders o
join (
  select id
  from orders
  order by create_time desc, id desc
  limit 1000000, 20
) t on o.id = t.id
order by o.create_time desc, o.id desc;

子查询只扫描索引里的 create_time, id，避免对前 1000000 条都回表。外层只对 20 个主键取完整数据。

延迟关联不是让 offset 消失，而是把“跳过 100 万条完整行”变成“跳过 100 万条较窄索引记录”。如果二级索引包含 create_time,id，子查询只在索引树上扫描，外层再按 20 个 id 回表取完整行，回表次数从可能接近 1000020 次降到 20 次。这个优化特别适合返回列很多但排序过滤列较少的场景。

四、业务上也要限制深分页

技术优化不是无限兜底。很多场景没有必要开放任意页码，尤其是前台列表和搜索页。可以限制最大页数、要求用户缩小搜索条件、按时间范围查询，或者将复杂检索交给 Elasticsearch 等搜索系统。

比如搜索页只允许翻到前 100 页，超过后提示缩小筛选条件；订单后台必须查历史数据，则要求带时间范围。对爬虫和批量导出尤其要加限制，否则单个无边界深分页请求就可能占用大量数据库资源。优化深分页时，产品规则和技术方案要一起设计。

五、排序键必须稳定

深分页优化绕不开排序稳定性。如果只按 create_time desc 排序，而同一秒有 1000 条订单，翻页过程中新增或删除数据，就可能出现重复或漏数据。加上唯一键作为第二排序字段，例如 order by create_time desc, id desc，可以让顺序变成确定的。

-- 下一页游标条件示例
where (create_time < ?)
   or (create_time = ? and id < ?)
order by create_time desc, id desc
limit 20;

这类写法的重点是排序字段和游标条件保持一致，并配套建立 (create_time,id) 或结合过滤列的联合索引。

六、怎么用 Explain 验证优化是否有效

验证深分页优化时，不只看返回 20 行。要看子查询是否走了覆盖索引，rows 是否仍很大但回表次数是否下降，Extra 是否出现额外排序。游标分页则要确认条件能定位到索引范围起点，而不是仍然全索引扫描。

优化目标：
offset 分页：扫描大量记录 + 大量回表
延迟关联：扫描大量窄索引 + 只回表目标页
游标分页：定位游标位置 + 扫描 page_size 条附近记录

七、常见误区与追问

误区：加了排序索引就彻底解决深分页。 索引能减少排序或回表成本，但 offset 很大时仍要跳过大量索引项。
误区：游标分页可以无损替代所有页码分页。 游标分页适合连续翻页，不适合直接跳到第 50000 页这类需求。
误区：游标只用时间字段就够。 时间可能重复，应该加唯一键兜底，避免翻页重复或漏数据。
误区：延迟关联子查询写 select * 也有效。 子查询必须尽量只取索引覆盖的小字段和主键，否则前面跳过的数据仍可能大量回表。
追问：延迟关联为什么能优化？ 它不能消除 offset 扫描，但能把前面大量候选记录的完整行回表推迟掉，只对目标页主键回表。
追问：前台列表为什么常限制最大页数？ 因为无限深分页对用户价值低、对数据库成本高，限制页数能从源头保护系统。

八、加强记忆

深分页慢的根因是 offset 让数据库从头走过并丢弃大量记录，最终返回 20 行不代表只处理 20 行。连续翻页优先用游标分页，让查询从上次位置继续；必须支持跳页时，用覆盖索引子查询先拿主键，再延迟关联回表。排序键要稳定，最好用业务排序字段加唯一 id；业务上还要限制最大页数和查询范围，避免把数据库当成无限滚动的计数器。

简化版

详细版