第 21 章：分页优化——深分页的多种解法

星辰小山
0 阅读11分钟

第 21 章：分页优化——深分页的多种解法

⏱ 阅读时间：约 40 分钟 📖 前置知识：索引原理（第 12 章）、EXPLAIN 执行计划（第 15 章） 🎯 读完本章你将：理解深分页为什么慢，掌握 4 种生产级解法，能根据业务场景选对方案

一个让人血压升高的问题

你一定见过这样的代码：

SELECT * FROM orders ORDER BY create_time DESC LIMIT 1000000, 10;

这条 SQL 看起来人畜无害——"给我第 100 万页的第 1 到第 10 条数据"。

但你的数据库不这么想。

在生产环境中，这条 SQL 可能需要 5 秒、10 秒甚至更久 才能返回。用户盯着转圈的页面，客服电话被打爆，老板在群里@你："为什么后台卡了？"

这不是夸张。Stack Overflow 上有一个 235 票的著名问题，浏览量超过 119000 次，标题就是：MySQL 巨大表上的分页怎么做？

今天我们就来把这个经典问题彻底搞明白。

深分页到底慢在哪里？

先搞清楚一个基本事实：MySQL 的 LIMIT offset, size 并不是"直接跳到那一行"。

它的工作方式更像是——从第一行开始，一行一行地数，数到 offset + size 行，然后把前面的 offset 行全部丢掉，只留下最后的 size 行。

听不懂？没关系，看个比喻。

image.png

更糟糕的是，如果你的 SQL 涉及 SELECT *，MySQL 不仅要做 100 万次索引扫描，还要做 100 万次回表——每行都从二级索引找到主键，再回聚簇索引取完整行数据。

每一行都可能触发一次磁盘 I/O（如果没被 Buffer Pool 缓存的话）。

这就是深分页的罪魁祸首：扫描了大量不需要的行，并且每一行都触发了回表。

四种解法，四种武器

面对深分页这个难题，MySQL 社区经过多年沉淀，总结出了四种主流解法。每种解法都有自己擅长的场景，就像不同类型的武器适合不同类型的战斗。

image.png

下面我们逐一拆解。

解法一：延迟关联（Deferred Join）

核心思想

延迟关联的思路很简单：先通过覆盖索引快速定位到需要的行的 ID，然后再用这些 ID 去回表取完整数据。

关键在于——覆盖索引只需要扫描索引，不需要回表。所以"扫描 100 万行"变成了"扫描 100 万行索引"，速度快了几个数量级。真正需要回表的只有最后那 10 行。

image.png

实际 SQL

-- 延迟关联写法
SELECT o.* 
FROM orders o
INNER JOIN (
    SELECT id 
    FROM orders 
    ORDER BY create_time DESC 
    LIMIT 1000000, 10
) AS tmp ON o.id = tmp.id;

前提条件

需要在排序字段上建索引：

ALTER TABLE orders ADD INDEX idx_create_time (create_time);

适用场景

  • ✅ 通用性最强，适用于大多数分页场景
  • ✅ 不需要前端配合改造
  • ✅ 实现简单，改动最小

局限

  • ❌ 子查询仍需扫描大量索引行（虽比回表快，但 offset 极大时仍有开销）
  • ❌ 排序字段有大量重复值时，优化器可能不选这个执行计划

解法二：书签法（Seek Method）

核心思想

不用 OFFSET，改用 WHERE 条件定位。

想象你在看书，用书签记住上次读到的位置。下次翻页时，不需要从第一页开始数到第 100 页——你只需要从上次的书签位置继续往后读就行。

image.png

实际 SQL

假设上一页最后一条记录的 create_time 是 '2024-12-15 10:30:00'，id 是 999990：

-- 书签法写法：用 WHERE + ORDER BY 替代 OFFSET
SELECT * FROM orders 
WHERE create_time < '2024-12-15 10:30:00'
   OR (create_time = '2024-12-15 10:30:00' AND id < 999990)
ORDER BY create_time DESC, id DESC
LIMIT 10;

💡 为什么需要同时比较 create_time 和 id？ 因为 create_time 可能重复。加上 id 作为"决胜局"条件，保证不会漏行也不会重复行。

需要的索引

ALTER TABLE orders ADD INDEX idx_create_time_id (create_time, id);

前端需要配合的改造

传统分页传的是页码（page=100001），书签法需要传的是上一页最后一条记录的位置。这意味着：

  • 前端的分页组件需要改造，不能再用简单的页码跳转
  • 用户不能直接跳到"第 N 页"，只能上一页/下一页
  • 滚动加载（瀑布流）天然适合这种模式

适用场景

  • ✅ 性能最好，无论多深的分页都一样快（O(1) 回表）
  • ✅ 适合移动端 App 的下拉加载更多
  • ✅ 适合社交媒体的信息流

局限

  • ❌ 不支持随机跳页
  • ❌ 需要排序字段 + 决胜字段的联合索引
  • ❌ 前后端都需要改造

解法三：基于游标的分页

核心思想

游标分页是书签法的"服务端增强版"。书签法需要客户端记住上一页的位置，而游标分页由服务端来维护这个位置。

服务端在返回数据的同时，返回一个不透明的"游标"（cursor）。客户端翻页时只需把上一次的游标传回来，服务端就知道从哪里继续。

image.png

游标通常是什么？

游标本质上就是"上一条记录位置"的编码版本：

// Base64 编码位置信息
{ "next_cursor": "eyJjcmVhdGVfdGltZSI6IjIwMjQtMTItMTUgMTA6MzA6MDAiLCJpZCI6OTk5OTkwfQ==" }
// 解码后：{"create_time":"2024-12-15 10:30:00","id":999990}

适用场景

  • ✅ RESTful API 设计的推荐方案（Twitter、GitHub、Stripe 都用）
  • ✅ 游标对客户端不透明，服务端可自由改变内部实现
  • ✅ 适合 OpenAPI / GraphQL 等标准接口

局限

  • ❌ 同样不支持随机跳页
  • ❌ 服务端需要维护游标编码/解码逻辑
  • ❌ 如果两次请求之间数据被删除，可能导致游标失效

解法四：覆盖索引 + 子查询

核心思想

这个解法和延迟关联类似，但更进一步——让索引覆盖查询需要的所有字段，彻底消除回表。

如果你的分页列表页只需要展示少量字段（比如 id、title、create_time），而这些字段恰好都在一个索引里，MySQL 可以直接从索引中获取数据，一次回表都不需要。

image.png

实际 SQL

-- 只查索引中已有的字段
SELECT id, order_no, create_time 
FROM orders 
ORDER BY create_time DESC 
LIMIT 1000000, 10;

-- 需要的索引
ALTER TABLE orders ADD INDEX idx_cover (create_time, id, order_no);

如何确认走了覆盖索引？

用 EXPLAIN 检查，看 Extra 列是否出现 Using index：

mysql> EXPLAIN SELECT id, order_no, create_time FROM orders
    -> ORDER BY create_time DESC LIMIT 1000000, 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
         type: index
possible_keys: NULL
          key: idx_cover
      key_len: ...
          ref: NULL
         rows: 1000010
        Extra: Using index    ← 这就是覆盖索引！

适用场景

  • ✅ 列表页只需要展示部分字段时最佳选择
  • ✅ 配合延迟关联使用效果更佳
  • ✅ 实现简单，不需要改前端

局限

  • ❌ 如果 SELECT * 包含了索引外的字段（如 TEXT、JSON），就覆盖不了
  • ❌ 仍然需要扫描 offset + size 行索引
  • ❌ 索引宽度有限（InnoDB 单索引最大 3072 字节），字段太多建不了

实战 Benchmark：百万级深分页四种解法大 PK

光说不练假把式。我们来做一个真实的 benchmark 对比。

测试环境

image.png

Benchmark 数据

下面是四种解法在不同 offset 深度下的表现对比（单位：秒）：

image.png

数据解读

这张表里有几个关键结论：

1. 书签法是绝对的性能王者。

无论 offset 是 1 万还是 100 万，书签法的耗时都是 0.002 秒。因为它永远只扫描 10 行，永远只回表 10 次。这是 O(1) 的时间复杂度。

2. 延迟关联是性价比最高的通用方案。

从 8.2 秒降到 0.8 秒，提升了 10 倍，而且不需要前端改造。对于大多数后台管理系统的分页场景来说，这已经足够了。

3. 覆盖索引在列表页场景下表现优秀。

如果你的列表页只需要展示 3-5 个字段，覆盖索引可以把回表降到 0。但注意——如果 SELECT 的字段不在索引里，这个方案就失效了。

4. 原始方案在浅分页时其实没问题。

offset=1 万的时候，原始方案只要 0.08 秒。所以深分页优化不是要一刀切，而是要在 offset 超过某个阈值时才启用优化方案。

方案选型决策树

面对实际业务，到底该选哪种方案？来看决策流程：

image.png

生产环境的额外建议

除了选对方案，还有几个生产环境中的实践建议：

1. 限制最大页码

即使用了延迟关联，offset=1000 万时仍然需要扫描 1000 万行索引。很多系统会直接限制最大可翻页数：

// 后端代码
int maxOffset = 500 * pageSize; // 最多翻到第 500 页
if (offset > maxOffset) {
    throw new BizException("数据量过大，请使用搜索条件缩小范围");
}

2. 避免大 OFFSET 的另一种思路：缩小结果集

与其翻到第 10 万页，不如让用户通过筛选条件缩小结果集。Google 搜索最多展示十几页结果，不是因为它没有更多结果，而是因为后面的结果对用户来说价值为零。

3. 缓存热点数据

对于"最新 100 条"这种高频访问的分页，直接缓存 Redis 即可，连数据库都不用查。

4. 考虑 ES / ClickHouse

如果你的业务确实需要频繁深翻页 + 复杂筛选 + 多字段排序，那可能不是 MySQL 该干的活。考虑用 Elasticsearch 做搜索分页，或者用 ClickHouse 做分析型分页。

本章小结

image.png

面试必问 TOP 5

Q1：MySQL 的 LIMIT offset, size 为什么在 offset 很大时很慢？

答： MySQL 执行 LIMIT offset, size 时，需要先扫描并丢弃前 offset 行，然后返回 size 行。即使只需要 10 条数据，也需要处理 offset + 10 行。如果涉及回表（SELECT *），每一行都需要从二级索引查找主键再回聚簇索引取数据，导致大量随机 I/O。本质上是一个 O(offset) 的问题，offset 越大，代价越高。

Q2：什么是延迟关联？它为什么能优化深分页？

答： 延迟关联（Deferred Join）是先用子查询通过覆盖索引只查出需要的行的主键 ID（这一步不需要回表），然后再用这些 ID 通过主键去回表取完整数据。这样就把回表次数从 offset + size 次降到了 size 次。因为覆盖索引扫描比回表快几个数量级，所以整体性能大幅提升。

Q3：书签法（Seek Method）的原理是什么？有什么局限性？

答： 书签法的原理是用 WHERE 条件替代 OFFSET 来定位分页起点。记住上一页最后一条记录的排序字段值（如果有重复则加上决胜字段如 ID），下一页查询时直接 WHERE sort_field < last_value ORDER BY sort_field DESC LIMIT size。这样只需要扫描 size 行，时间复杂度 O(1)。局限是不支持随机跳页，只能顺序翻页；需要排序字段+决胜字段的联合索引；前后端都需要改造。

Q4：如何通过 EXPLAIN 确认查询走了覆盖索引？

答： 执行 EXPLAIN 后查看 Extra 列，如果出现 Using index 就说明走了覆盖索引，MySQL 直接从索引中获取数据，不需要回表。如果出现 Using index condition 则是索引下推，不同于覆盖索引。如果什么都没有或出现 Using filesort，说明分页可能存在性能问题。

Q5：在实际项目中你会怎么设计分页方案？

答： 我会根据场景选择：管理后台等需要跳页的场景用延迟关联（同时限制最大页码如 500 页）；C 端 App 的信息流/瀑布流用书签法或游标分页（性能最优）；列表页字段少时优先考虑覆盖索引。浅分页（offset < 1 万）不需要优化。如果业务确实需要频繁深翻页+复杂筛选，会考虑引入 Elasticsearch。

下一章预告： 第 22 章我们将深入 MySQL 的排序优化，看看 GROUP BY + ORDER BY 的那些坑，以及如何通过参数调优让你的数据库快到飞起。

avatar
文章
阅读
粉丝