MySQL深分页优化实战：LIMIT大偏移查询的三种改进

老涂 2026-11-03 09:13 14 ℃ 0 条

内容摘要MySQL深分页慢的根因是LIMIT offset会先扫描并丢弃前offset行，回表还会带来大量随机IO。本文给出延迟关联、游标分页加覆盖索引三种改进方案，并用EXPLAIN对比rows与type验证效果，最后说明各自的适用场景与注意事项。

MySQL深分页优化实战

翻页到第 10 万页时响应从 20 毫秒涨到 15 秒，往往不是故障，而是 LIMIT 1000000, 20 要求 MySQL 先取出并丢弃前 100 万行。下面讲清原因与改进方案。

一、MySQL深分页为什么慢

1. LIMIT offset 的执行过程

MySQL 执行 LIMIT offset, count 时无法直接跳到第 offset 行。存储引擎必须沿 B+ 树叶子节点从第一行顺序读取，每读一行就交给 Server 层判断是否丢弃。LIMIT 1000000, 20 实际扫描 1000020 行，前 100 万行读完即弃，开销全在“读并丢”。

2. 回表带来的随机 IO

若查询列不在索引里，每条保留的记录都要按主键回表取整行。排序字段与过滤条件不一致时，优化器还可能选择全表扫描加 filesort：先排好所有行再切出 offset 之后的 20 行。三者叠加，耗时随 offset 近似线性增长。

二、三种可落地的优化方案

1. 延迟关联：先用覆盖索引拿主键

延迟关联（deferred join）先在索引上完成分页，再按主键回表。覆盖索引不触发回表，扫描代价大幅下降：

SELECT a.id, a.title, a.created_at
FROM article AS a
INNER JOIN (
  SELECT id FROM article
  WHERE status = 1
  ORDER BY id DESC
  LIMIT 1000000, 20
) AS t ON a.id = t.id
ORDER BY a.id DESC;

子查询的 SELECT id 走覆盖索引，只在二级索引上扫描并丢弃；外层仅对 20 个主键回表。

2. 游标分页：用上次的最大 id 做起点

游标分页（keyset pagination）把定位换成范围，彻底消除 offset：

SELECT id, title, created_at
FROM article
WHERE status = 1 AND id < 1000000
ORDER BY id DESC
LIMIT 20;

前端把上一页最后一条的 id 传回作为起点，配合 (status, id) 复合索引，每次只扫描 20 行。代价是不能跳页，适合无限滚动或批量导出。

3. 覆盖索引 + 范围查询

排序字段不是主键时，可建覆盖索引把排序与过滤都吃进索引：

ALTER TABLE article ADD INDEX idx_status_time (status, created_at, id);

SELECT id, title, created_at
FROM article
WHERE status = 1 AND created_at < '2026-11-01 00:00:00'
ORDER BY created_at DESC
LIMIT 20;

索引列顺序必须与 ORDER BY 一致，范围条件放在等值条件之后，否则优化器仍会退化成 filesort。