深分页问题：LIMIT offset性能与优化方案——教程向

翻后台列表的时候，你有没有遇到过这种诡异的现象：前几页接口响应 20ms，翻到第 500 页变成 1.2s，翻到第 5000 页直接 8s 超时。日志里那条 SQL 平平无奇，就是一句 `LIMIT 1000000, 20`。

这就是典型的深分页问题。很多人第一反应是"数据库不行了"，于是加机器、加缓存、上分库分表，折腾一圈发现没什么用。其实问题出在对 `LIMIT offset` 的误解上——它根本不是"跳过前 N 行"这么简单。

offset 的本质：不是跳跃，是"数过去"

`LIMIT 1000000, 20` 的语义是"取出第 1000001 到第 1000020 行"，但 InnoDB 的执行方式是：先按顺序取出 1000020 行，再丢掉前 1000000 行，只返回最后 20 行。

如果走的是二级索引，事情会更糟。假设查询条件是 `WHERE status = 1 ORDER BY created_at DESC`，使用 `idx_status_created(status, created_at)` 索引时，每取出一条索引记录，还要拿主键回表读一次完整行——因为 `SELECT *` 需要 title、content 这些字段。也就是说，为了返回 20 行数据，可能发生了一百万次随机 I/O 回表。

这些读出来的行最终全被丢进垃圾桶。CPU 在扫索引，磁盘在随机读，内存 buffer pool 被污染，而它们服务的是一个没人会看的第 5000 页。

动手复现一次

先建一张在后台系统里很常见的表：

CREATE TABLE article (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  author_id INT NOT NULL,
  status TINYINT NOT NULL,
  title VARCHAR(200) NOT NULL,
  content TEXT,
  created_at DATETIME NOT NULL,
  KEY idx_status_created (status, created_at)
) ENGINE=InnoDB;

-- 灌入 200 万行测试数据后执行
EXPLAIN SELECT * FROM article
WHERE status = 1
ORDER BY created_at DESC
LIMIT 1000000, 20;

看 `rows` 这一列，你会发现它给出的估算值接近 1000020。这个数字就是答案：优化器已经明确告诉你，它准备扫描一百万行。

方案一：延迟关联

思路是先只查主键，拿到 id 之后再回表取完整数据。前面那句慢查询可以改写成：

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

子查询里只查 `id`，而 InnoDB 的二级索引叶子节点本来就存了主键值，所以 `idx_status_created` 天然覆盖这个查询，完全不需要回表。一百万行的扫描变成纯粹的顺序索引遍历，代价小一个数量级；真正回表的只有最后那 20 行。

实测在类似结构上，这条改写的 SQL 通常能把 8s 压到几百毫秒。缺点是随着 offset 继续增大，耗时仍然是线性增长的——它治好了回表，没治好扫描。

方案二：游标分页（Keyset Pagination）

如果你的业务是"无限滚动""下一页"而不是"跳到第 372 页"，那最彻底的方案是把 offset 彻底消灭。做法是把上一页最后一条记录的排序键当作游标传回来：

SELECT * FROM article
WHERE status = 1
  AND (created_at, id) < ('2024-05-01 12:00:00', 987654)
ORDER BY created_at DESC, id DESC
LIMIT 20;

这里用 `id` 做第二排序键是为了处理 `created_at` 重复的情况，避免翻页时漏数据或重复。由于条件直接定位到索引中的某个位置，无论翻到第几页，扫描量都是恒定的 20 行，性能曲线是一条水平线。

代价也很明确：不能跳页。用户想从第 1 页直接跳到第 800 页，游标方案做不到——除非你愿意先顺序扫过去。所以它更适合信息流、日志、消息列表这类场景。Elasticsearch 的 `search_after`、各大云数据库的"分页游标"，本质都是这个思路。

方案三：覆盖索引与索引下推

有时候问题不在 offset，而在索引没设计对。如果列表页只需要展示 `id, title, created_at`，那就别 `SELECT *`，直接把这几个字段做成联合索引：

ALTER TABLE article ADD KEY idx_cover (status, created_at, title);

这样查询全程在索引里完成，一行都不回表。索引当然会变宽、写入会变慢，但对读多写少的后台列表来说，这笔账通常划算。

另外，MySQL 5.6+ 的索引条件下推（ICP）会把部分 WHERE 条件下推到存储引擎层过滤，减少回表次数。执行计划里出现 `Using index condition` 就说明生效了。不过它主要优化的是"过滤"，对深分页的"跳过"帮助有限。

几个工程上的土办法

- 限制最大页数：绝大多数后台系统，用户根本不会翻到第 1000 页。阿里云控制台、GitHub 的搜索结果都做过类似限制，超出范围直接提示"请缩小筛选条件"。
- 缓存热门页：前 10 页的访问量往往占 90% 以上，缓存收益极高。
- 换存储：当分页筛选维度非常多、又要支持任意跳页时，把列表查询交给 ES、ClickHouse 这类为检索而生的系统，MySQL 只负责存明细。

怎么选

| 方案 | 适用场景 | 主要代价 |
| --- | --- | --- |
| 延迟关联 | 必须支持跳页，改动最小 | 深 offset 仍线性变慢 |
| 游标分页 | 无限滚动、下一页为主 | 不支持跳页 |
| 覆盖索引 | 列表字段固定且较少 | 索引变宽，写入变慢 |
| 限制页数 / 缓存 | 后台管理系统 | 体验受限 |

深分页的本质，是让数据库为了一屏数据干了一百万行的活。优化的方向无非两条：**要么减少每一行的代价（延迟关联、覆盖索引），要么减少要处理的行数（游标分页、限制页数）**。先跑一次 `EXPLAIN` 看清楚 `rows` 是多少，再决定动哪一刀——大多数情况下，一句改写就能解决问题，不必急着上架构。