LIMIT offset, count 的性能为什么会随 offset 增大而暴跌？如何用游标式翻页（keyset pagination）优化？

考察要点

这道题面试官想看你是否深入理解 MySQL 中 LIMIT offset, count 的执行原理，而不只是停留在会用的层面。核心考察点包括：

1. LIMIT 分页的性能陷阱：是否知道 offset 越大，查询越慢，以及其背后的根本原因——服务器需要扫描并丢弃 offset 条记录。
2. 索引与分页的关系：是否理解排序（ORDER BY）在分页查询中的作用，以及索引（特别是覆盖索引）如何影响 LIMIT 的性能。
3. 高级优化方案：是否掌握游标式翻页（Keyset Pagination / Seek Method）这一业界标准的深度分页优化方案，并能写出正确的 SQL。

核心概念

1. LIMIT offset, count 的工作原理

LIMIT offset, count 并非直接定位到第 offset 条记录开始读取，而是：

1. 根据 WHERE 条件和 ORDER BY 子句，从存储引擎中取出所有满足条件的记录。如果 ORDER BY 的列没有索引，会进行全表扫描和文件排序（filesort），性能极差。
2. 在服务层（Server Layer），将结果集进行排序。
3. 服务器读取并扫描（scan）并丢弃（discard） 前 offset 条记录。
4. 最后，读取并返回接下来的 count 条记录。

这个过程可以用下图表示：

 1graph LR
 2    A[存储引擎] -- 满足WHERE条件的全部数据 --> B(MySQL Server)
 3    B -- ORDER BY排序 --> C{排序后结果集}
 4    C -- 扫描并丢弃 offset 条记录 --> D(丢弃的记录)
 5    C -- 读取并返回 count 条记录 --> E(最终结果)

性能瓶瓶颈：查询的成本与 offset + count 成正比，而非仅仅 count。当 offset 达到几十万甚至上百万时，扫描和丢弃记录的 I/O 和 CPU 开销会变得巨大，导致查询性能急剧下降。

即使 ORDER BY 的列有索引，MySQL 仍然需要：

• 遍历索引：从索引的 B+ 树中找到起点，然后向后遍历 offset + count 个索引条目。
• 回表（Lookups）：如果不是覆盖索引，每遍历一个索引条目，就需要根据主键值回到聚簇索引中去读取完整的行数据。对于一个 LIMIT 1000000, 20 的查询，这意味着可能要进行 1,000,020 次回表，而其中 1,000,000 次都是无用功。

2. 游标式翻页 (Keyset Pagination)

游标式翻页，也称作 "Seek Method" 或 "Keyset Pagination"，其核心思想是放弃 offset，改为使用上一页最后一条记录的排序列的值作为“书签”或“游标”，来定位下一页的起始位置。

示例：假设我们有一个 posts 表，按 id 降序分页。

传统分页（LIMIT offset）：

• 第一页: SELECT id, title FROM posts ORDER BY id DESC LIMIT 2; -> 返回 (105, 104)
• 第二页: SELECT id, title FROM posts ORDER BY id DESC LIMIT 2, 2; -> 返回 (103, 102)
• 第 N 页: SELECT id, title FROM posts ORDER BY id DESC LIMIT (N-1)*2, 2;

游标式翻页（Keyset Pagination）：

• 第一页: SELECT id, title FROM posts ORDER BY id DESC LIMIT 2; -> 返回 (105, 104)。客户端记录下最后一行的 id 是 104。
• 第二页: SELECT id, title FROM posts WHERE id < 104 ORDER BY id DESC LIMIT 2; -> 返回 (103, 102)。客户端记录下最后一行的 id 是 102。
• 第 N 页: SELECT id, title FROM posts WHERE id < [上一页的最小id] ORDER BY id DESC LIMIT 2;

优势：WHERE id < 104 可以高效地利用 id 的主键索引，直接定位到 id=104 的下一个位置开始扫描，需要扫描的行数始终是 count。查询成本是常量时间 $O(\text{count})$，与页码无关。

边界条件：

• 排序键不唯一：如果排序的列（如 created_at）不唯一，必须加上一个唯一键（如 id）作为次要排序条件，以防止数据重复或遗漏。
• SQL 写法：
  • MySQL 8.0+ 支持行构造函数：WHERE (created_at, id) < ('2023-10-27 14:00', 104)
  • MySQL 5.7 及更早版本：WHERE created_at < '2023-10-27 14:00' OR (created_at = '2023-10-27 14:00' AND id < 104)

多种解法

假设我们有表 t_posts，需要在 update_time 列上进行深度分页。

 1CREATE TABLE `t_posts` (
 2  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
 3  `title` VARCHAR(255) NOT NULL,
 4  `content` TEXT,
 5  `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 6  PRIMARY KEY (`id`),
 7  KEY `idx_update_time` (`update_time`)
 8) ENGINE=InnoDB;

解法 1：游标式翻页 (Keyset Pagination) - 推荐

这是最高效、最可扩展的方案，适用于无限滚动、加载更多的场景。

• 标注版本：✅ 所有 SQL 版本均支持（语法略有差异）。

• SQL 实现： 假设按 update_time 降序，id 降序分页，每页 20 条。上一页最后一条记录的 update_time 是 last_update_time，id 是 last_id。

  sql

  ⧉⛶

   1-- MySQL 8.0+ / PostgreSQL
   2SELECT id, title, update_time
   3FROM t_posts
   4WHERE (update_time, id) < ('2023-10-27 10:00:00', 12345) -- 假设上一页最后一条是 (2023-10-27 10:00:00, 12345)
   5ORDER BY update_time DESC, id DESC
   6LIMIT 20;
   7
   8-- MySQL 5.7 及以下
   9SELECT id, title, update_time
  10FROM t_posts
  11WHERE update_time < '2023-10-27 10:00:00'
  12   OR (update_time = '2023-10-27 10:00:00' AND id < 12345)
  13ORDER BY update_time DESC, id DESC
  14LIMIT 20;

• 时间/空间复杂度：$O(\text{count})$。每次查询的成本是固定的。

• 索引依赖：必须在排序列上建立复合索引 (update_time, id) 以获得最佳性能。

解法 2：延迟关联 (Deferred Join) - 折衷方案

当业务场景必须支持“跳转到任意页码”时，此方案是 LIMIT offset 的一个重要优化。它通过覆盖索引减少回表次数。

• 标注版本：✅ 所有 SQL 版本均支持。

• SQL 实现： 思路是先通过覆盖索引快速定位到目标页的 id 列表，然后再用这些 id 去关联主表获取完整数据。

  sql

  ⧉⛶

   1-- 假设查询第 50001 页，每页 20 条
   2-- offset = 1000000, count = 20
   3SELECT p1.id, p1.title, p1.update_time
   4FROM t_posts AS p1
   5INNER JOIN (
   6    -- 子查询只在覆盖索引上操作，快速丢弃 offset 条记录
   7    SELECT id
   8    FROM t_posts
   9    ORDER BY update_time DESC, id DESC
  10    LIMIT 1000000, 20
  11) AS p2 ON p1.id = p2.id
  12ORDER BY p1.update_time DESC, p1.id DESC; -- 外部的 ORDER BY 保证最终顺序

• 时间/空间复杂度：子查询仍然需要扫描 offset+count 个索引条目，但避免了对 offset 条记录进行回表，成本远低于朴素的 LIMIT。回表成本从 $O(\text{offset})$ 降为 $O(\text{count})$。

• 索引依赖：ORDER BY 的列必须有覆盖索引（如 (update_time, id)），否则此优化无效。

常见低分回答

1. 只说 LIMIT 慢，但说不清原理：“当 offset 很大时，LIMIT 查询会变慢。” —— 这是现象，不是原因。没有解释“扫描并丢弃”的内部机制，说明理解不深入。
2. 优化方案治标不治本：“把每页的 count 调大一点，比如从 20 改成 100，这样请求次数就少了。” —— 这只是延缓了问题，当用户翻到更深的页数时，性能问题依然存在。
3. 游标式翻页 SQL 写错：在处理非唯一排序列时，忘记使用唯一键（如 id）作为 tie-breaker。
   sql

   ⧉⛶

    1-- 错误示例：当 update_time 有重复时，会丢失或重复数据
    2SELECT id, title FROM t_posts
    3WHERE update_time < '2023-10-27 10:00:00' -- 漏掉了 update_time = '...' 的情况
    4ORDER BY update_time DESC
    5LIMIT 20;

4. 滥用子查询做分页：使用 ROW_NUMBER() 窗口函数然后筛选行号。在 MySQL 中，这通常比 LIMIT 更慢，因为它无法利用底层的 LIMIT 优化，而且通常需要扫描更多数据。
   sql

   ⧉⛶

    1-- 性能通常更差的方案
    2SELECT id, title FROM (
    3    SELECT id, title, ROW_NUMBER() OVER (ORDER BY update_time DESC) as rn
    4    FROM t_posts
    5) AS sub
    6WHERE rn > 1000000 AND rn <= 1000020;

5. 提出不切实际的方案：“把数据都查出来放到 Redis 里，然后在 Redis 里分页。” —— 对于海量数据，这会消耗巨大的内存，并且数据同步也是一个难题。

工程实践与优化

• 执行计划分析：使用 EXPLAIN 是诊断性能的关键。
  • 坏查询 (LIMIT offset, count)：EXPLAIN SELECT ... LIMIT 1000000, 20; 的输出中，rows 字段会显示一个接近 1000020 的巨大数字。
  • 好查询 (Keyset Pagination)：EXPLAIN SELECT ... WHERE id < ... LIMIT 20; 的输出中，type 通常是 range，rows 字段会接近 20。
  • 延迟关联：EXPLAIN 子查询时，rows 很大；但 EXPLAIN 整个 JOIN 查询时，驱动表（子查询结果）的 rows 只有 20，这证明了优化的有效性。
• 索引是前提：所有分页优化的前提是 ORDER BY 的列上建有合适的索引。对于游标式翻页，强烈建议建立 (排序列, 唯一键) 的复合索引。
• 产品设计协同：游标式翻页对产品形态有要求。它天然适用于“加载更多”或“无限滚动”的场景，但无法支持“跳转到第 N 页”的功能。在技术选型时，需要和产品经理沟通，明确分页交互方式。
• 实现反向翻页：实现“上一页”功能时，需要反转 ORDER BY 和 WHERE 的比较逻辑。例如，对于 id 降序的列表，获取 id > [当前页第一条记录的id] 的数据，并用 ORDER BY id ASC，取 LIMIT count，然后在应用层将结果集反转后展示。

常见追问与延伸题目

1. 追问：如果排序的列不是唯一的（比如 update_time），游标式翻页怎么保证不丢数据、不重复？

   • 回答要点：必须引入一个唯一列（通常是主键 id）作为次要排序条件，这被称为“tie-breaker”（决胜局）。WHERE 条件需要同时对两个列进行比较。
     • 方案A (推荐)：使用行构造函数语法（MySQL 8.0+），简洁且意图明确：WHERE (update_time, id) < ('2023-10-27 10:00:00', 12345)。
     • 方案B (兼容)：使用 OR 逻辑展开：WHERE update_time < '...' OR (update_time = '...' AND id < ...)。这两种写法的查询计划通常是一样的，都能有效利用 (update_time, id) 复合索引。

2. 追问：延迟关联优化和游标式翻页，各自的优缺点和适用场景是什么？

   • 回答要点：
     • 游标式翻页 (Keyset Pagination)
       • 优点：性能最好，查询成本恒定，不受分页深度影响。是业界处理无限滚动 Feed 流的标准方案。
       • 缺点：无法实现跳页功能，只能“上一页/下一页”。实现双向翻页逻辑稍复杂。
       • 适用场景：移动端 App 的信息流、社交网络动态、新闻列表等无限滚动或“加载更多”的场景。
     • 延迟关联 (Deferred Join)
       • 优点：保留了跳页功能 (offset 依然可用)，是对传统 LIMIT 分页的一个巨大优化，能有效缓解深度分页的回表压力。
       • 缺点：性能仍会随 offset 增大而下降（尽管斜率平缓了很多），因为索引扫描的成本依然存在。
       • 适用场景：PC 端的后台管理系统、论坛帖子列表等需要显示页码并支持任意跳转的传统分页场景。

3. 追问：如果我必须支持跳页，但数据量巨大，延迟关联也开始变慢了，还有什么办法？

   • 回答要点：当单表 SQL 优化到极限时，需要从架构和产品层面思考。
     • 限定查询范围：在产品层面，限制用户能够翻页的深度。例如，Google 搜索只允许你翻几十页。或者引导用户使用更精确的筛选条件（如时间范围）来缩小结果集。
     • 数据归档/分片：对于时序数据（如日志、订单），可以将历史数据归档到冷表中，或按时间进行分表/分区。分页查询只在最近的热数据上进行。
     • 引入搜索引擎：当查询需求非常复杂（多维度筛选、排序、全文检索）且数据量巨大时，最专业的解决方案是使用 Elasticsearch 或 OpenSearch 等搜索引擎。它们使用倒排索引等技术，为分页、聚合和搜索提供了极致的性能。将需要检索的数据同步到 ES 中，由 ES 提供分页查询能力。