祥哥的技术博客

主题

📅 2026年7月20日 🏷️ MySQL ⏱️ 10 分钟阅读 MySQL

分页查询优化：大偏移量 LIMIT 的 3 种解法 ​

问题：为什么 LIMIT 100000, 20 越翻越慢？ ​

几乎每个后端开发都遇到过这种场景：用户打开后台管理系统的第 1001 页，页面转了 5 秒才出来。翻到 5000 页直接超时。SQL 长这样：

sql
SELECT * FROM orders WHERE status = 1 ORDER BY id LIMIT 100000, 20;

直觉上你可能会想：「不就取 20 条吗？不应该快吗？」——错。MySQL 的实际执行方式是：先扫描 100020 行，排序，然后丢弃前 100000 行，只返回最后 20 行。偏移量越大，扫描的行数就越多，IO 和排序成本都线性增长。

实测数据（500 万行 orders 表，status=1 约 300 万行）：

分页位置SQL扫描行数耗时
第 1 页 (LIMIT 0,20)...LIMIT 0,20203ms
第 100 页 (LIMIT 1980,20)...LIMIT 1980,20200045ms
第 1000 页 (LIMIT 19980,20)...LIMIT 19980,2020000380ms
第 5000 页 (LIMIT 99980,20)...LIMIT 99980,201000001.8s
第 50000 页 (LIMIT 999980,20)...LIMIT 999980,20100000017s

结论：偏移量每增加 10 倍，耗时约增长 10 倍。2000 万行的表翻到最后几页，直接几分钟。

分析：为什么 MySQL 要这么干？ ​

MySQL 的 LIMIT offset, count 语义要求「从第 offset 行开始取 count 行」，但 InnoDB 无法直接跳到第 offset 行——B+ 树的叶子节点链表虽然有序，但无法跳过扫描。必须从头开始一行一行数，直到找到偏移量位置。

B+ 树扫描过程（文字时序）：

1. 优化器选择索引（假设是 (status, id) 联合索引）
2. 从 B+ 树根节点开始二分查找，定位到满足 status=1 的第一个叶子节点
3. 沿着叶子节点双向链表顺序扫描：
   → 第 1 行 → 计数 1  → 第 2 行 → 计数 2  → ... → 第 100000 行 → 计数 100000
4. 继续扫描 20 行，这些行才真正返回
5. 每行从二级索引回聚簇索引取完整数据（回表）

这就带来了三个问题：

  1. 无用扫描：前面 100000 行全部扫描但舍弃，浪费大量 IO
  2. 排序放大：如果加上了 ORDER BY，MySQL 需要先把这 100020 行全部读入 sort_buffer 排序，排序后再截取
  3. 回表放大：如果走了二级索引，每行都需要回表一次，100020 次回表 ≈ 100020 次随机 IO

sort_buffer_size 的坑：默认 256KB。如果排序列数据量超过这个值，MySQL 会用临时文件做归并排序，这就是为什么 LIMIT 1000000, 20 加上多字段排序能跑到几十秒的根因——大量磁盘 IO 在 sort_buffer 和临时文件之间来回倒。

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

核心思路：先用覆盖索引快速定位到需要的主键 ID，再通过主键回表取完整数据。

sql
-- 优化前
SELECT * FROM orders WHERE status = 1 ORDER BY id LIMIT 100000, 20;

-- 优化后：延迟关联
SELECT o.*
FROM orders o
INNER JOIN (
    SELECT id
    FROM orders
    WHERE status = 1
    ORDER BY id
    LIMIT 100000, 20
) AS tmp ON o.id = tmp.id;

原理：子查询只扫描了 (status, id) 覆盖索引（如果建立了联合索引的话），不需要回表。20 个 ID 确定后，外层 INNER JOIN 只回表这 20 行，随机 IO 从 100020 次降为 20 次。

实测对比（同 500 万行表）：

分页优化前延迟关联提升
LIMIT 100000,201.8s42ms43x
LIMIT 500000,209.2s51ms180x
LIMIT 1000000,2017s55ms309x

关键前提：必须建立 (status, id) 联合索引。如果只建了 (status) 单列索引，子查询依然需要回表扫描 100020 行，延迟关联效果归零。

适用场景：跳页分页（用户从第 1 页直接跳到第 5000 页），无法改用游标分页的场合。

解法二：游标分页（Cursor Pagination） ​

核心思路：不用 offset，而是基于上一页最后一条记录的位置开始查。

sql
-- 第一页
SELECT * FROM orders WHERE status = 1 ORDER BY id LIMIT 20;

-- 第二页（基于上一页最后一条记录的 id）
SELECT * FROM orders
WHERE status = 1 AND id > 100000
ORDER BY id
LIMIT 20;

为什么快：WHERE id > 100000 直接走主键索引定位到指定位置，然后顺序扫描 20 行。每次扫描固定 20 行，完全不受页码影响。

实测：第 50000 页（WHERE id > 999980）耗时仍为 3ms，和第一页一样。

排序字段有重复值时：如果排序字段不是唯一的（比如按 create_time 排序），需要加主键作为第二排序字段保证结果稳定。

sql
-- 假设上一页最后一条记录是 (create_time='2026-07-19 12:00:00', id=100000)
SELECT * FROM orders
WHERE status = 1
  AND (create_time, id) > ('2026-07-19 12:00:00', 100000)
ORDER BY create_time, id
LIMIT 20;

为什么必须加 id：如果只按 create_time 排序，且同一秒内有 50 条数据，LIMIT 20 每次取到的 20 条可能不同。加上 id 作为第二排序字段，保证每行有唯一排序位置，分页结果确定。

适用场景：Feed 流、信息流、App 列表、滚动加载。不适用于跳页分页（如页码 1→100）。

解法三：子查询定位起始点 ​

核心思路：先通过子查询拿到偏移量位置的主键，再从该位置开始取数据。

sql
SELECT * FROM orders
WHERE id >= (
    SELECT id FROM orders
    WHERE status = 1
    ORDER BY id
    LIMIT 100000, 1
)
ORDER BY id
LIMIT 20;

原理：子查询利用覆盖索引快速定位到第 100001 行的主键值，外层查询利用主键索引从该位置开始扫描 20 行。和延迟关联本质类似，但写法更简洁。

注意：id >= 语义上会多取一行（如果第 100001 行被删除了，会往后推到下一个存在的 id），但 LIMIT 20 会截断，实际结果正确。如果排序字段允许重复值，可以用 >= + 第二排序字段处理。

和延迟关联的区别：子查询定位只做了一次子查询取 ID，延迟关联把 20 个 ID 全部取出来再 JOIN。本质相同，延迟关联的写法在复杂 WHERE 场景下更灵活（可以加更多条件在子查询里）。

三种方案对比 ​

方案是否支持跳页性能提升（vs 原始 LIMIT）实现复杂度索引要求推荐场景
延迟关联✅10x-300x（偏移量越大越明显）中等需要覆盖索引后台管理列表、跳页场景
游标分页❌恒定（1000x+，无偏移量增长）低主键或唯一索引信息流、App、滚动加载
子查询定位✅10x-300x简单需要覆盖索引快速改造、单表分页

生产案例：一个 5 秒超时的翻页问题 ​

现象：某电商后台订单管理页面，翻到第 200 页以上就开始超时（5 秒超时设置）。翻到第 500 页直接报 504。

表结构：

sql
CREATE TABLE `orders` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `order_no` varchar(32) NOT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '0',
  `create_time` datetime NOT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  -- ... 还有 15 个字段
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB;

原始 SQL：

sql
SELECT * FROM orders
WHERE status IN (1,2,3)
ORDER BY create_time DESC
LIMIT 10000, 20;

根因分析：

  1. idx_status 单列索引，过滤后还是需要回表全扫
  2. ORDER BY create_time 无法走索引（WHERE 条件不是等值匹配），走了 filesort
  3. sort_buffer_size 默认 256KB，排序数据量约 2MB，触发了临时文件归并排序
  4. 总扫描行数 10020 行，但回表 + 磁盘排序导致实际耗时 5.3s

优化方案（延迟关联 + 索引优化）：

sql
-- 第一步：建立联合索引
ALTER TABLE orders ADD INDEX idx_status_create_time (`status`, `create_time`);

-- 第二步：改写 SQL
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders
    WHERE status IN (1,2,3)
    ORDER BY create_time DESC, id DESC
    LIMIT 10000, 20
) tmp ON o.id = tmp.id;

优化效果：5.3s → 62ms，提升 85 倍。

生产建议 ​

  1. 不要等慢了再优化：在 performance_schema.events_statements_summary_by_digest 中设置监控规则——rows_examined 远大于 rows_sent 的 SQL 就是排查对象。特别是 rows_examined > 10000 且 rows_sent < 100 的，基本就是大偏移量分页。

  2. 游标分页优先原则：产品设计层面，优先用「加载更多」替代「页码跳转」。这不仅是 SQL 问题，也是交互体验的改善。但产品经理不一定买账，所以延迟关联和子查询定位必须掌握。

  3. 延迟关联的索引前提：延迟关联的效果依赖于覆盖索引的存在。如果 WHERE 条件字段和 ORDER BY 字段能组成联合索引，效果最佳。否则子查询本身也需要扫描大量行，优化的意义就小了。

  4. 分页 + 复杂排序场景：如果 ORDER BY 是多个字段且无法覆盖索引，延迟关联比游标分页更通用。此时优先优化排序本身（见第 12 题「文件排序 vs 索引排序」）。

  5. 面试追问点：面试官可能会问「如果不用覆盖索引，延迟关联还有效吗？」——答案是效果有限，因为子查询 SELECT id 本身也需要回表。所以延迟关联的核心前提是覆盖索引。

总结 ​

大偏移量 LIMIT 慢的本质是扫描行数随偏移量线性增长，而三种优化方案的核心思路都是减少扫描行数：

  • 延迟关联：用覆盖索引扫描代替全表扫描 + 回表
  • 游标分页：用索引定位代替偏移量跳过
  • 子查询定位：用覆盖索引的快速定位能力

选择哪种方案取决于你的业务场景：能游标就游标，不能就延迟关联。没有银弹，但绝对不要在生产环境写 LIMIT 1000000, 20 这种 SQL。

面试一句话总结：分页慢的本质是扫描行数 = offset + limit，延迟关联/游标分页的核心都是把扫描行数降为 limit 固定值。

手撕 → 框架 → 生产化，一步步把 AI Agent 工程化搞透。

Copyright © 2026 祥哥