用延迟关联改写大偏移 OFFSET 分页以命中覆盖索引

业务里最常见的翻页 SQL 大概是 SELECT * FROM t ORDER BY id LIMIT 20 OFFSET 500000。前几页很快，翻到几万页之后响应时间从十几毫秒涨到几秒，CPU 和 IO 一起飙升，慢查询日志里全是它。这不是 MySQL 的 bug，而是 OFFSET 语义决定的成本：数据库必须先定位并跳过前 N 行，再取 M 行。本文

业务里最常见的翻页 SQL 大概是 SELECT * FROM t ORDER BY id LIMIT 20 OFFSET 500000。前几页很快，翻到几万页之后响应时间从十几毫秒涨到几秒，CPU 和 IO 一起飙升，慢查询日志里全是它。这不是 MySQL 的 bug，而是 OFFSET 语义决定的成本：数据库必须先定位并跳过前 N 行，再取 M 行。本文给出一套可直接落地的改写方案——延迟关联（deferred join），让深分页先只在索引上走一遍拿到主键，再用主键回表，从而把大范围的回表 IO 压到最小，并让第一段查询稳定命中覆盖索引（covering index）。

为什么 OFFSET 越翻越慢

很多人以为 LIMIT 20 OFFSET 500000 只读 20 行，实际上 InnoDB 要沿索引顺序扫描 500020 条索引记录，对每一条都需要判断是否需要回表，然后把前 500000 条丢弃。如果查询列不在索引里，还要对每条记录做一次回表（回聚簇索引取整行）。代价模型大致是：

  • 索引扫描行数 ≈ OFFSET + LIMIT；
  • 回表次数 ≈ OFFSET + LIMIT（除非优化器能用到索引覆盖）；
  • 排序成本：如果 ORDER BY 列与 WHERE 条件无法共用同一个索引，还会出现 Using filesort。

下面建一张贴近真实业务的表来复现。表里带一个 status 过滤条件和一个 created_at 排序，模拟“按状态筛选、按时间倒序”的列表页：

CREATE TABLE article (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  author_id   BIGINT UNSIGNED NOT NULL,
  category_id INT UNSIGNED    NOT NULL,
  status      TINYINT         NOT NULL DEFAULT 0,
  title       VARCHAR(200)    NOT NULL,
  content     MEDIUMTEXT      NOT NULL,
  created_at  DATETIME        NOT NULL,
  PRIMARY KEY (id),
  KEY idx_status_created (status, created_at, id),
  KEY idx_author (author_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 造 200 万行数据（用递归 CTE，MySQL 8.0+）
SET SESSION cte_max_recursion_depth = 3000000;
INSERT INTO article (author_id, category_id, status, title, content, created_at)
WITH RECURSIVE seq(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 2000000
)
SELECT
  n % 5000,
  n % 40,
  n % 3,
  CONCAT('title-', n),
  REPEAT('x', 300),
  TIMESTAMPADD(SECOND, n, '2024-01-01 00:00:00')
FROM seq;
ANALYZE TABLE article;

原始写法的执行计划：

EXPLAIN ANALYZE
SELECT id, author_id, title, created_at
FROM article
WHERE status = 1
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 500000;

典型输出（MySQL 8.0，实际数字随机器不同）：

-> Limit: 20 row(s)  (cost=... rows=20) (actual time=1520.4..1520.5 rows=20 loops=1)
    -> Index lookup on article using idx_status_created (status=1)
       (cost=... rows=... ) (actual time=0.05..1180.2 rows=500020 loops=1)

注意 rows=500020：索引确实被用上了，但为了产出 20 行，它老老实实读了 50 万条索引项并回表。慢查询日志里能看到 Rows_examined: 500020，Handler_read_next 暴涨。这就是要解决的问题。

延迟关联的核心思路

把“定位”和“取数”拆成两步：

  1. 子查询只查主键 id（或少数在索引里的列），走覆盖索引扫完 OFFSET + LIMIT 行，但完全不回表；
  2. 外层用 IN (子查询) 或 JOIN 按主键回表，只回表 LIMIT 行。

改写后：

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

关键点在于子查询的 SELECT id。只要 status、created_at、id 三列都在同一个二级索引 idx_status_created 里，子查询就是纯索引扫描，执行计划会显示 Using index（即覆盖索引）。外层的 JOIN 只对 20 个主键做聚簇索引查找，代价可以忽略。

注意：InnoDB 的二级索引叶子节点天然带主键值，所以 KEY idx_status_created (status, created_at) 也能覆盖 SELECT id；显式把 id 写进索引定义只是让意图更清晰，不是必须。

让第一条查询真正命中覆盖索引

“延迟关联”能提速的前提是子查询不产生回表。验证方式只有一种：看 EXPLAIN 的 Extra 列。用 MySQL 8.0 的 EXPLAIN FORMAT=TREE 更容易看清：

EXPLAIN FORMAT=TREE
SELECT a.id, a.author_id, a.title, a.created_at
FROM article AS a
JOIN (
  SELECT id FROM article
  WHERE status = 1
  ORDER BY created_at DESC, id DESC
  LIMIT 20 OFFSET 500000
) AS t ON t.id = a.id;
-> Nested loop inner join  (cost=... rows=20)
    -> Filter: (t.id is not null)
        -> Table scan on t
            -> Materialize  (cost=... rows=20)
                -> Index lookup on article using idx_status_created (status=1)
                   (cost=... rows=500020)
                   (reverse)

子查询那一层出现了 Index lookup ... using idx_status_created 并且没有任何 Table scan on article，说明它是覆盖扫描加反向读（reverse 对应 DESC 排序，利用了索引有序性，避免了 filesort）。如果这里出现 Sort: article.created_at DESC，或者出现 Table scan on article，说明覆盖没生效，必须回去改索引。

常见的三种失败情形：

现象（EXPLAIN Extra / TREE 节点） 原因 解决
Using where; Using filesort ORDER BY 列不在 WHERE 索引的后续位置，如索引是 (status) 建 (status, created_at, id)，让过滤与排序共用一个索引
Using index condition 且有 Table scan on article 子查询里多选了非索引列，如 SELECT id, title 子查询只保留索引内列，其余列交给外层回表
Using index for skip scan 或直接全表扫 WHERE 过滤性太差（如 status 只有 3 个值，某值占 90%） 换分布均匀的过滤维度，或用“游标分页”替代 OFFSET

还有一种容易被忽略的情况：ORDER BY created_at DESC, id DESC 与索引 (status, created_at, id) 的列顺序和方向完全一致时，优化器才能用 reverse 反向扫索引。若写成 ORDER BY created_at DESC, id ASC，MySQL 8.0 的降序索引虽支持混合方向，但二级索引默认是升序定义的，仍可能触发 filesort。要么统一方向，要么显式建降序索引：

ALTER TABLE article
  ADD KEY idx_status_created_desc (status, created_at DESC, id DESC),
  ALGORITHM=INPLACE, LOCK=NONE;

JOIN 与 IN 两种写法的差异

延迟关联有两种等价形态，性能差异在高并发下才显现：

-- 写法 A：JOIN 派生表（推荐）
SELECT a.id, a.title FROM article a
JOIN (SELECT id FROM article WHERE status = 1
      ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 500000) t
  ON t.id = a.id
ORDER BY a.created_at DESC, a.id DESC;

-- 写法 B：IN 子查询
SELECT id, title FROM article
WHERE id IN (SELECT id FROM article WHERE status = 1
             ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 500000)
ORDER BY created_at DESC, id DESC;

在 MySQL 5.6 之后，IN (SELECT ... LIMIT) 的语义需要保留 LIMIT 的作用范围，优化器不会把它改写成半连接（semi-join），因此两种写法都会被物化（materialize）成临时表再关联，执行计划基本一致。实测差异主要来自外层 ORDER BY：写法 A 在外层按 a.created_at 排序，如果优化器没能识别主键顺序，会产生一次 20 行的 filesort（代价极小）；写法 B 同理。稳妥做法是外层也按 id 排序，或直接去掉外层 ORDER BY 交由应用层保证——因为子查询已经按目标顺序产出了 id 集合，20 行的顺序完全可以在应用层重排。

可以用 EXPLAIN FORMAT=JSON 确认物化行为：

EXPLAIN FORMAT=JSON
SELECT a.id, a.title FROM article a
JOIN (SELECT id FROM article WHERE status = 1
      ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 500000) t
  ON t.id = a.id\G

关注输出里的 "materialized_from_subquery" 节点及 "using_index": true。

用 Handler 计数器量化收益

不看真实数字就说“快了很多”没有说服力。用 Handler_read_* 状态变量对比两种写法，能精确看到回表次数：

FLUSH STATUS;
SELECT SQL_NO_CACHE id, author_id, title, created_at
FROM article
WHERE status = 1
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 500000;
SHOW SESSION STATUS LIKE 'Handler_read%';

FLUSH STATUS;
SELECT SQL_NO_CACHE a.id, a.author_id, a.title, a.created_at
FROM article a
JOIN (SELECT id FROM article WHERE status = 1
      ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 500000) t
  ON t.id = a.id;
SHOW SESSION STATUS LIKE 'Handler_read%';

第一段的 Handler_read_next 通常在 50 万量级，Handler_read_key 同样接近 50 万（每次回表一次）；第二段 Handler_read_next 仍是 50 万（索引扫描无法避免），但 Handler_read_key 只有 21 左右——这就是省下来的 50 万次聚簇索引查找。也就是说，延迟关联优化的不是扫描行数，而是回表次数，这一点必须讲清楚，否则容易被误解为“OFFSET 可以被优化掉”。

如果表有几十个 TEXT/BLOB 大字段、单行很大，回表一次要读多个页，收益会成倍放大；如果表很窄且 buffer pool 能全部装下，收益会小很多，此时应优先考虑游标分页（keyset pagination）。

什么时候不该用延迟关联

  • OFFSET 不大（如前 1000 行内）：改写带来的临时表开销可能盖过收益，保持原样即可。
  • 业务允许改接口：直接用游标分页，WHERE (created_at, id) < ('2024-03-01 10:00:00', 12345)，每次都是常量定位，O(1) 级别。这是深分页的终极方案，但要求前端不再提供“跳页”。
  • 需要精确总数：SQL_CALC_FOUND_ROWS 已在 MySQL 8.0.17 标记弃用，深分页场景下做一个 COUNT(*) 也往往比原查询更慢，建议改为“最多显示 N 页”的产品策略。
  • 排序字段无法建索引：如按随机数或复杂表达式排序，延迟关联无能为力，得考虑搜索引擎或预计算表。

一个常见的报错是改写后仍然报慢，并在 SHOW WARNINGS 里看到 Note 1003 /* select#1 */ ... 提示子查询被合并（merged）。MySQL 8.0 的派生表合并优化（derived merge）在某些场景下会把子查询展开成普通连接，导致 LIMIT 语义变化。此时用 /*+ NO_MERGE(t) */ 提示阻断合并：

SELECT /*+ NO_MERGE(t) */ a.id, a.title
FROM article a
JOIN (
  SELECT id FROM article
  WHERE status = 1
  ORDER BY created_at DESC, id DESC
  LIMIT 20 OFFSET 500000
) t ON t.id = a.id;

若提示把物化也禁掉了（NO_MERGE 只阻止合并，不阻止物化），仍可配合 SET optimizer_switch='derived_merge=off' 做对照实验，但不要在生产会话中长期关闭。

小结

大偏移 OFFSET 的成本主要来自“扫完还要回表”，延迟关联把查询拆成“索引上定位主键”和“按主键取行”两步，让前半段命中覆盖索引、后半段只回表 LIMIT 次。落地时按这个顺序检查：确认 (过滤列, 排序列, 主键) 的联合索引存在且顺序匹配、子查询只选索引内列、EXPLAIN 中出现 Using index 且无 filesort、再用 Handler_read_key 验证回表次数确实降到 LIMIT 量级。超出这个范围，就该上 keyset 分页了。

FAQ

Q1：子查询里写 SELECT id，为什么 EXPLAIN 显示 Using index，但耗时没降？

先看 Handler_read_key。如果它仍在 OFFSET 量级，说明外层 JOIN 没走主键等值查找，可能是 t.id 与 a.id 类型不一致（例如 BIGINT UNSIGNED 对 INT），触发了隐式转换，导致关联列上的索引失效。用 SHOW CREATE TABLE 对齐两边类型即可。

Q2：能不能只靠 FORCE INDEX 让原查询变快？

FORCE INDEX (idx_status_created) 只能保证优化器选这个索引，无法避免 OFFSET 范围内的逐行回表。除非查询列全部被索引覆盖，否则 FORCE INDEX 不解决深分页问题，反而可能在过滤性差的 status 值上更慢。

Q3：MySQL 8.0 的降序索引（descending index）对这里有用吗？

有用，但要看方向。若业务固定 ORDER BY created_at DESC, id DESC，升序索引配合反向扫描（EXPLAIN 中的 reverse）已经足够；混合方向排序（一升一降）才需要真正的降序索引，否则会出现 Using filesort。降序索引在 8.0 才被真正支持，5.7 中声明 DESC 会被静默忽略。

Q4：改写后 SQL 变长，ORM 里怎么维护？

把两段 SQL 拆成两个方法：selectIdsByPage(status, offset, limit) 和 selectByIds(List<Long> ids)，在 Service 层组合。这样既保留了覆盖索引的意图，也避免 MyBatis 动态 SQL 里嵌套 limit 子句带来的可读性问题。第二段用 WHERE id IN (...) 时注意 IN 列表长度，MyBatis 的 foreach 拼 20 个参数完全没有压力。

Q5：有没有一个通用阈值判断何时该改写？

经验值是 OFFSET > 10000 且单行较大的表（含 TEXT/BLOB）收益明显；可以先用 EXPLAIN ANALYZE 看 actual time 是否随 OFFSET 线性增长，若是，就改写并用 Handler_read_key 复测。不要凭感觉，用计数器说话。