MySQL 深度分页如何优化？

结论：LIMIT 1000000, 10 慢的根因是「先取出前 1000010 行再丢掉 1000000 行」，成本与偏移量成正比。优化方向只有两类：让服务器少读（延迟关联、覆盖索引），或者干脆不按偏移量翻页（游标/书签、按范围裁剪）。

一、慢在哪一步

SELECT * FROM article ORDER BY id LIMIT 1000000, 10;

InnoDB 侧                           Server 侧
┌────────────────────────┐          ┌────────────────────┐
│ 1. 二级索引顺序扫描     │          │                    │
│ 2. 每行回表取整行 ──────┼─────────▶│ 3. 攒够 1000010 行 │
│    （随机 I/O）         │          │ 4. 丢弃前 1000000  │
│                        │          │ 5. 返回最后 10 行  │
└────────────────────────┘          └────────────────────┘
        成本 ≈ 偏移量 × 单行回表代价

两个放大器：SELECT * 让每一行都必须回表；ORDER BY 非索引列时还会叠加 filesort 与临时表。

二、四种方案对比

三、代码与执行计划

准备一张百万行表观察差异：

CREATE TABLE article (
  id      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  author  VARCHAR(32)  NOT NULL,
  title   VARCHAR(128) NOT NULL,
  ctime   DATETIME     NOT NULL,
  content TEXT         NOT NULL,
  PRIMARY KEY (id),
  KEY idx_ctime (ctime)
) ENGINE=InnoDB;

-- 反面教材：扫 100 万行并逐行回表
EXPLAIN SELECT * FROM article ORDER BY id LIMIT 1000000, 10;

延迟关联把「回表」推迟到最后 10 行：

SELECT a.*
FROM article a
JOIN (
  SELECT id FROM article ORDER BY id LIMIT 1000000, 10
) t ON a.id = t.id;

子查询走的是覆盖索引（只读 id，不回表），代价从「100 万次随机 I/O」降到「100 万次顺序索引扫描 + 10 次回表」。

书签分页把偏移量彻底消掉：

-- 第一页
SELECT id, title, ctime FROM article ORDER BY id LIMIT 10;

-- 后续页：把上一页最后一行的 id 传给下一页
SELECT id, title, ctime FROM article
WHERE id > 1000000
ORDER BY id
LIMIT 10;

按时间排序的业务，可以让索引与排序方向对齐，避免 filesort：

ALTER TABLE article ADD KEY idx_ctime_id (ctime, id);

SELECT id, title, ctime FROM article
WHERE ctime >= '2026-01-01' AND ctime < '2026-02-01'
ORDER BY ctime, id
LIMIT 10;

四、翻页总数怎么办

SELECT COUNT(*) 在千万级表上同样是全扫。常见做法是：列表页只显示「有下一页」，用 LIMIT n+1 探测；确需总数时走汇总表或近似值（SHOW TABLE STATUS 的 Rows、information_schema.tables.table_rows 都是估算值，误差可能很大）。

常见追问

• 延迟关联为什么有效？ 因为子查询只查主键，可以走覆盖索引，避免了百万次回表；外层 JOIN 只处理 10 行。
• 书签分页的缺点？ 不能随机跳到第 N 页，只适合顺序浏览；排序键必须唯一且稳定，否则会漏行或重复。
• ORDER BY 非索引列时怎么办？ 要么建联合索引让排序走索引，要么把排序字段收敛到业务可接受的范围，靠 filesort 就是内存与临时文件的博弈。
• LIMIT 数值很大时还会慢吗？ 不会，LIMIT 只是截断；慢的是它前面的 OFFSET。
• 分库分表后深度分页怎么处理？ 各分片各取前 offset+limit 条，在内存归并后再截断，偏移越大内存压力越大，最终还是要改成游标式。
• ES 里怎么翻页？ from/size 同样受 max_result_window 限制，深翻页用 search_after + 排序游标。

小结

深分页的优化本质是「把 O(offset) 的扫描改成 O(limit) 的定位」。能改交互就上书签分页；必须支持跳页就上延迟关联 + 覆盖索引；两者都不行，就把查询搬到专用搜索引擎或限制可翻页深度。