MySQL 深分页优化：2026 年的工程答案

上篇我们验证了 LIMIT offset 的性能瓶颈，以及用主键范围查询绕开它的基本思路。这篇接着聊——到 2026 年，这个问题有没有更系统的解法？

先把问题说清楚

LIMIT offset 慢的根源没有变：InnoDB 的 B+ 树叶子节点存储完整行数据（聚簇索引），执行大偏移时 MySQL 必须从头遍历叶子链表，把前 N 行数据读出来再整体丢弃，偏移越大，白做的 I/O 越多。

这不是 MySQL 的 bug，而是关系型数据库的结构性约束。所以 2026 年并没有出现什么"引擎层的魔法补丁"——解法依然在应用层，只是实践上更系统了。

实验环境

在 MySQL 8.0.46 / InnoDB 上造了 200 万行的 logs 表，字段包括自增主键 id、level、message 和 created_at，每组数据取 3 次均值，尽量排除缓存干扰。

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

原理是把"跳过 N 行"改成"从上次停的地方继续"，完全消灭 OFFSET：

-- 传入上一页最后一条的 id
SELECT * FROM logs WHERE id > :last_seen_id ORDER BY id LIMIT 20;

按主键排序，实测数据：

Keyset 的响应时间不随偏移量增长——从第 1 条翻到第 190 万条，延迟始终在 1ms 以内。这正是它的核心价值：O(log N) 的索引定位，不存在退化。

非主键排序怎么办？

现实中经常需要 ORDER BY created_at，这时 cursor 要携带两个值，用 OR 展开写法保证能命中复合索引 (created_at, id)：

-- 需要先建复合索引
CREATE INDEX idx_created_at_id ON logs (created_at, id);

-- 多列 cursor：展开写法，比 Row Value Syntax 更稳定地走 range scan
SELECT * FROM logs
WHERE created_at > :last_dt
   OR (created_at = :last_dt AND id > :last_id)
ORDER BY created_at, id
LIMIT 20;

⚠️ 关于 Row Value Syntax

MySQL 8 支持 WHERE (created_at, id) > (:dt, :id) 这种写法，语法更简洁，但实测发现 MySQL 8.0 优化器有时会把它降级成 type: index（全索引扫描）而不是 type: range，需要用 EXPLAIN 验证执行计划。如果计划不理想，回退到 OR 展开写法更保险。

按 created_at 排序，实测数据（OR 展开写法）：

非主键排序的 OFFSET 方案比主键排序还慢得多（1100ms vs 250ms），因为 ORDER BY created_at 无法利用聚簇索引，需要额外的排序开销。Keyset 在这种场景下的收益更加显著。

Keyset 的约束：

• 不支持随机跳页（"跳到第 500 页"做不到）
• cursor 字段必须稳定唯一，非唯一字段要加主键做 tiebreaker
• 前后端接口需要改造，传递 cursor 而不是页码

适合：无限滚动、Feed 流、日志查询、API 游标翻页。这覆盖了绝大多数大表分页的真实需求。

方案二：Deferred Join（延迟关联）

对于"必须支持页码跳转"的场景，Deferred Join 是目前最成熟的折中方案。

思路是把一次查询拆成两步：先用覆盖索引只取主键（子查询走纯索引，不回表），再用主键 JOIN 原表取完整行，回表次数从 offset 次压缩到只有 20 次。

SELECT l.*
FROM logs l
INNER JOIN (
    SELECT id FROM logs ORDER BY created_at LIMIT 20 OFFSET :offset
) AS ids ON l.id = ids.id;

按 created_at 排序，与原始 OFFSET 对比：

Deferred Join 比原始 OFFSET 快了一个数量级，但注意它的延迟仍随偏移量线性增长——偏移 190 万时已经到 245ms，这是它无法逾越的上限。加速比也随偏移量的加深而收窄（77x → 5x），因为子查询阶段的索引扫描开销仍然存在，只是去掉了大量回表。

两种方案的适用边界

         │  支持随机跳页  │  深翻不退化  │  改造成本
─────────┼───────────────┼─────────────┼──────────
 Keyset  │      ✗        │      ✓      │   中（接口改）
 Deferred│      ✓        │      ✗      │   低（SQL 改）

实践中常见的组合策略：浅页用 Deferred Join，深页截断或引导缩小范围。大多数产品的真实用户几乎不会翻到第 500 页以后，设一个合理的深度上限（如最多 100 页），配合 Deferred Join，是成本最低、效果足够的方案。

方案三：Table Partitioning（超大表）

当表膨胀到数十 GB 乃至 TB 级，上面两种方案都只是延缓问题。加列、重建索引、备份都会变成数小时的阻塞操作。这时候需要分区。

最常见的是按时间做 RANGE 分区：

ALTER TABLE logs
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

查询时带上时间条件，MySQL 会做 Partition Pruning，直接跳过不相关分区，相当于把一张大表拆成多张小表来查。

分区不会改变 SQL 写法，对应用透明，但有一个约束：分区键必须包含在所有唯一索引中，这会影响索引设计，上线前需要仔细评估。

小结

这个问题到 2026 年没有被"解决"，但已经被"绕开"了——大多数需要大偏移分页的场景，换成 Keyset 之后问题从根本上消失；确实需要页码的场景，Deferred Join 的改造成本足够低，效果也足够好。

剩下的就是工程判断：你的产品真的需要用户跳到第 500 页吗？