翻到 1000 页慢了 200 倍：OFFSET 不是翻页——是扫了再扔
场景：分页列表翻到第 5000 页时接口从 50ms 飙升到 10 秒，ORDER BY id LIMIT 100000, 20 路径：EXPLAIN → rows=100020 → OFFSET 的执行本质 → 游标/子查询延迟关联
上篇讲了少了个引号查询慢 80 倍——MySQL 隐式转换让索引失效的排查路径。这次我们来看一种不需要改索引就能解决的问题：深度翻页时 LIMIT OFFSET 的代价。
SELECT * FROM orders ORDER BY id DESC LIMIT 100000, 20;
——翻了 10 万行，只拿了 20 行。
不是数据量太大——是 OFFSET 的工作原理注定了它必须扫这么多行。
以下分析基于 MySQL 8.0.32。
【现象】翻到第 5000 页：从 50ms 到 10 秒
业务背景
告警平台弹了一条消息：订单列表接口（分页查询）平均耗时从 50ms 飙升到 10 秒。
表结构很简单：
CREATE TABLE orders (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
order_no VARCHAR(32) NOT NULL,
user_id BIGINT NOT NULL,
amount DECIMAL(12,2),
status TINYINT NOT NULL,
created_at DATETIME NOT NULL,
INDEX idx_status (status),
INDEX idx_created_at (created_at)
) ENGINE=InnoDB;
约 500 万行数据。查询是按 id 倒序的分页列表：
SELECT * FROM orders
ORDER BY id DESC
LIMIT 100000, 20;
翻到第 5000 页（每页 20 行）。
EXPLAIN 关键信号
| id | select_type | table | type | key | rows | Extra |
|---|---|---|---|---|---|---|
| 1 | SIMPLE | orders | index | PRIMARY | 100020 | Using where |
type=index — 扫描索引树的所有叶子节点。比 type=ALL（全表扫描）好，因为只扫索引不扫数据行，但 rows=100020 这个数字本身就是问题。
rows=100020 — 预估扫描 10 万行。这张表一共 500 万行，一个分页查询预估扫 10 万行——这就是第一个红旗。
关键信号：rows 约等于 OFFSET + LIMIT。不是索引选错了，是 OFFSET 本身就要扫这么多。
翻页越深，扫描行数越线性增长
把同一个查询在不同页数下的表现拉出来：
| 页码 | SQL | 扫描行数 | 耗时 |
|---|---|---|---|
| 第 1 页 | LIMIT 0, 20 | ~20 行 | 50ms |
| 第 100 页 | LIMIT 2000, 20 | ~2020 行 | 130ms |
| 第 500 页 | LIMIT 10000, 20 | ~10020 行 | 600ms |
| 第 1000 页 | LIMIT 20000, 20 | ~20020 行 | 1.2s |
| 第 5000 页 | LIMIT 100000, 20 | ~100020 行 | 10s |
翻到第 N 页，扫描行数 ≈ N × page_size。
花了 10 秒，扫了 10 万行——只为了拿 20 行。效率比是 5000 : 1。
【解析】OFFSET 不是跳过——是扫了再扔
OFFSET 的执行真相
大多数开发者以为 LIMIT 100000, 20 是跳着找的。不是。
type=index — 按索引顺序扫描，每读一行判断是否满足 WHERE，计数是否达到 OFFSET。在它找到你要的偏移量之前，经过的所有行都必须读入并检查。用 OFFSET？每次翻页，MySQL 都要把第 1 行到你指定的偏移量之间所有行重新读一遍。
B+ 树的叶子节点是双向链表。PRIMARY 聚簇索引有 500 万个叶子节点条目。LIMIT 100000, 20 让 MySQL 从头遍历链表——走 100020 个节点，扔 100000，留 20。
这不是优化器选错了。这是 LIMIT OFFSET 这个语义本身决定的。
MySQL 为什么不能直接跳到第 100000 行
这是 InnoDB 的 B+ 树索引结构决定的。
B+ 树每个叶子节点存一批索引条目（通常一个节点 100~200 条记录），节点之间通过双向链表连接。索引按值排序，但没有"行号"或"偏移量指针"这一说。
要找到第 N 行，MySQL 只能从第一个叶子节点开始，沿着链表一路数过去。没有捷径。不像数组可以通过 base + offset * sizeof(element) 直接定位——B+ 树不支持随机偏移访问。
InnoDB 的索引不保存"行号"——只保存"值"和指向下一节点的指针。 你要第 100000 行的值？它不知道在哪，只能从头数。
深层问题：LIMIT OFFSET 的成本模型
优化器为什么没有警告你"这个查询要扫 10 万行"？因为它不觉得这是问题。
MySQL 的 cost 模型把 LIMIT OFFSET 的扫描成本当作普通索引扫描算。在我的测试中，LIMIT 100000, 20 的 EXPLAIN FORMAT=JSON：
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "10120.00"
},
"table": {
"table_name": "orders",
"access_type": "index",
"key": "PRIMARY",
"rows_examined_per_scan": 100020,
"rows_produced_per_join": 20,
"cost_info": {
"read_cost": "10100.00",
"eval_cost": "2.00",
"prefix_cost": "10102.00",
"data_read_per_join": "2K"
}
}
}
}
优化器算的是扫描 100020 行的成本（约 10100 个成本单位）。它觉得不高——对于 500 万行的表，扫 10 万行索引确实不贵。但它没告诉你的是：每次翻页都要重新扫一遍同样的前 N 行。
第一页扫 20 行，第 5000 页扫 100020 行——同样的数据库，同样的查询，成本差了 5000 倍。
数据分布分析：
| 维度 | 值 |
|---|---|
| 全表行数 | ~500 万 |
| 返回行数 | 20 |
| 翻到第 5000 页扫描行数 | ~100020 |
| 扫描/返回比 | 5000 : 1 |
| 前 10 页总扫描行数 | ~2000 |
| 前 5000 页总扫描行数 | ~2.5 亿 |
翻页越深，MySQL 为每一页付出的总劳动量呈平方级增长——前 5000 页累计扫了约 2.5 亿行，其中 99.98% 的行被读完后立刻丢弃。
把这个数字翻译一下：你的订单列表分页功能，把整张 500 万行的表完整的从头到尾读了 50 遍。 而用户真正看到的，只是第 5000 页那 20 行。
"OFFSET 不是跳过 N 行——是先查 N 行再扔掉。"
【路径】🔍 三类深度翻页的解决方案
遇到深度翻页时，没有银弹。三套方案各有适用场景：
方案一：游标分页（推荐）
用 WHERE id > :last_id 替代 LIMIT OFFSET。适合"下一页"按钮或无限滚动，不适合页码跳转。
-- Before: 翻到第 5000 页，扫 10 万行
SELECT * FROM orders ORDER BY id DESC LIMIT 100000, 20;
-- After: 游标分页，每次固定扫 20 行
SELECT * FROM orders WHERE id < :last_id
ORDER BY id DESC LIMIT 20;
每页固定扫 20 行，不受页数影响。第 1 页 50ms，第 5000 页还是 50ms。
方案二：子查询延迟关联
适合不需要游标但想减少扫描行数的情况。先查索引拿 ID，再回表取数据：
-- Before:
SELECT * FROM orders ORDER BY id DESC LIMIT 100000, 20;
-- After: 子查询先拿 id
SELECT * FROM orders WHERE id IN (
SELECT id FROM orders
ORDER BY id DESC LIMIT 100000, 20
);
子查询走 PRIMARY 索引，只扫索引条目（比全表行小得多），拿到 20 个 id 后再回表取完整数据。索引行比数据行小 10 倍以上，同样的扫描量 I/O 低很多。
方案三：业务妥协
有时候最优方案不是技术方案——是业务方案。
| 业务场景 | 技术方案不合适的原因 | 业务方案 |
|---|---|---|
| 用户不会翻到 100 页以后 | 全量数据加游标增加复杂度 | LIMIT 1000, 20 限制最大页数 |
| 导出全部数据 | 分页翻到 50 万页 | 异步导出 + 后台任务 |
| 搜索列表 | 游标不支持关键词翻页 | ES 的 search_after + 游标 |
| 管理后台 | 乱序翻页（按金额排序） | 子查询延迟关联 + LIMIT 限制 |
选型决策表
| 场景 | 推荐方案 | 原因 |
|---|---|---|
| 列表翻页（支持 Next/Prev） | 游标分页 | 每页恒定扫描行数，性能最优 |
Web 页编码跳转（页码 1 2 3 ... N） |
子查询延迟关联 | 游标不支持跳页 |
| 不需要跳页（无限滚动 / Load More） | 游标分页 | 体验和性能最佳 |
| 管理后台复杂排序（多字段 ORDER BY） | 子查询延迟关联 | 复合游标复杂度高 |
| 导出 / 后台大规模遍历 | 游标分页 | 可控的批量处理 |
| 翻页极少翻到底（< 10 页） | LIMIT OFFSET | 没必要优化 |
【重构】游标分页：从 OFFSET 到 WHERE
方案一：单字段游标（ORDER BY id）
这是最简单的情况。id 是主键，天然有序，游标分页就是利用 WHERE id < :last_id 代替 LIMIT OFFSET。
Before：
SELECT * FROM orders
ORDER BY id DESC LIMIT 100000, 20;
-- 扫描 100020 行，耗时 10s
After：
-- 第一页：客户端拿到 id=5000000
SELECT * FROM orders
ORDER BY id DESC LIMIT 20;
-- 第二页：传 last_seen_id = 5000000
SELECT * FROM orders WHERE id < 5000000
ORDER BY id DESC LIMIT 20;
-- 第三页：传 last_seen_id = 4999980
SELECT * FROM orders WHERE id < 4999980
ORDER BY id DESC LIMIT 20;
前后对比：
| 指标 | LIMIT OFFSET | 游标分页 |
|---|---|---|
| 第 1 页 | 20 行扫描，50ms | 20 行扫描，50ms |
| 第 100 页 | 2020 行扫描，130ms | 20 行扫描，50ms |
| 第 1000 页 | 20020 行扫描，1.2s | 20 行扫描，50ms |
| 第 5000 页 | 100020 行扫描，10s | 20 行扫描，50ms |
游标分页的扫描行数是常数，不受页数影响。
方案二：复合游标（多字段 ORDER BY）
当 ORDER BY 包含多个字段时，游标条件也要对应组合：
-- 排序：ORDER BY status DESC, created_at DESC, id DESC
-- 游标条件：
SELECT * FROM orders
WHERE (status, created_at, id) < (:last_status, :last_created_at, :last_id)
AND status = 1
ORDER BY status DESC, created_at DESC, id DESC
LIMIT 20;
MySQL 支持行值表达式（row constructor）的元组比较。注意索引必须包含所有排序字段：INDEX idx_status_ct_id (status, created_at, id)。
EXPLAIN before/after 对比
| 指标 | Before (LIMIT 100000, 20) | After (游标) |
|---|---|---|
| type | index | range |
| key | PRIMARY | PRIMARY |
| rows | 100020 | ~20（精确） |
| Extra | Using where | Using where; Using index |
| 耗时 | 10 秒 | 50ms |
type=range — 索引范围扫描。MySQL 通过 B+ 树直接定位到 id < 5000000 的起始位置，只扫需要的数据行。range 不需要从索引头部开始遍历。
type=index 和 type=range 的性能差异来自：type=index 是遍历链表，type=range 是 B+ 树的精确查找+范围扫描。前者时间复杂度 O(N)，后者 O(logN + M)（logN 定位 + M 扫描范围）。
方案三：Java / MyBatis 实战
把游标分页落地到代码层，核心就是 Mapper 接口的三个方法：
Service 层调用逻辑：
@Service
public class OrderService {
// 第一页：没有 lastId 就调这个
public List<Order> firstPage(int pageSize) {
return orderMapper.selectFirstPage(pageSize);
}
// 下一页：从当前页最后一条拿到 lastId 传进来
public List<Order> nextPage(long lastId, int pageSize) {
List<Order> page = orderMapper.selectNextPage(lastId, pageSize);
// 判断是否还有下一页（多取一条看有没有）
boolean hasMore = page.size() > pageSize;
if (hasMore) page.remove(page.size() - 1);
return page;
}
// 上一页：从当前页第一条拿到 firstId 传进来
public List<Order> prevPage(long firstId, int pageSize) {
List<Order> page = orderMapper.selectPrevPage(firstId, pageSize);
Collections.reverse(page); // 反转回 DESC 顺序
return page;
}
}
关键设计决策：
- hasMore 判断：每次 LIMIT pageSize + 1，如果返回 pageSize + 1 行说明还有下一页，取前 pageSize 行返回。这比 COUNT 查询高效得多——多取一行几乎零成本。
- 上一页用 ASC + 反转：
WHERE id > firstId ORDER BY id ASC LIMIT pageSize拿到前 pageSize 行，在 Java 里反转成 DESC 顺序返回。不需要 COUNT(OFFSET)。 - 前端配合：需要把 lastId 和 firstId 放在 API 响应中，前端翻页时传回服务端。
游标分页的使用限制
| 限制 | 说明 | 怎么办 |
|---|---|---|
| 不能跳页 | 只能在当前页基础上翻下一页/上一页 | 管理后台用子查询延迟关联 |
| 需要有序条件 | 必须有 ORDER BY 字段作为游标 | 复杂排序场景用子查询 |
| 依赖业务排序 | 排序字段变动影响游标准确性 | 固定排序逻辑 |
| 上一页实现复杂 | 需要用反向 ORDER BY 实现 | ORDER BY id ASC LIMIT 20 反向查 |
【标记】🗝 在你的项目里搜深度翻页
1. 搜 SQL 文件
# 搜所有 OFFSET 使用（游标的反而是少数）
grep -rn "OFFSET\|LIMIT.*," src/main/resources/ --include="*.xml" | \
grep -v "LIMIT 1" | \
grep -v "LIMIT ?,?" | \
head -30
# 重点：OFFSET 值大于 100 的翻页
grep -rn "OFFSET [1-9][0-9][0-9]" src/ --include="*.xml"
2. 搜 Java 代码
# 搜 PageHelper 深度翻页
grep -rn "PageHelper\|PageInfo\|startPage" src/main/java/
# 搜手写 OFFSET 分页
grep -rn "offset\|OFFSET" src/main/java/ --include="*.java" | \
grep -i "limit\|pag" | head -20
3. 从慢查询日志找
-- 找 LIMIT 值大 / 扫描行数远大于返回行数的查询
SELECT
digest_text,
count_star,
ROUND(avg_timer_wait / 1000000000, 2) AS avg_ms,
ROUND(sum_rows_examined / count_star) AS avg_rows,
ROUND(sum_rows_sent / count_star) AS avg_sent,
ROUND(
(sum_rows_examined / count_star) /
NULLIF(sum_rows_sent / count_star, 0)
) AS scan_send_ratio,
first_seen, last_seen
FROM performance_schema.events_statements_summary_by_digest
WHERE digest_text LIKE '%LIMIT%'
AND (sum_rows_examined / count_star) /
NULLIF(sum_rows_sent / count_star, 0) > 100
ORDER BY (sum_rows_examined / count_star) DESC
LIMIT 20;
scan_send_ratio 是扫描行数和返回行数的比值。比值 > 100 说明这个查询每发 1 行给客户端就要扫 100 行——深度翻页的嫌疑人。
4. 代码审查策略
# 每次 Code Review 时 grep 新加的 LIMIT OFFSET
diff --unified=0 HEAD~1 -- src/ | \
grep -B2 -A2 "LIMIT.*OFFSET\|LIMIT [0-9]\+,"
# 在 CI 中增加规则：禁止 LIMIT + OFFSET 组合用于翻页
# grep 出新引入的 LIMIT 表达式，在 Code Review 中标记
MySQL 的执行计划不会骗你——但 LIMIT OFFSET 的成本，优化器也不会替你算。你要自己知道：翻到第 N 页，MySQL 就替你扫了 N × page_size 行。
下回看到分页接口越来越慢，先查 SQL 里的 OFFSET 是多少。如果已经到几千几万——别建索引了，把 OFFSET 换成 WHERE id > last_seen_id。
"LIMIT 100000, 20 不是在查第 5000 页——是在查 10 万行数据后取最后 20 行。" "游标分页不是在优化翻页，是在消灭翻页——把每次查询都变成第 1 页。"
上篇：少了个引号查询慢 80 倍——MySQL 隐式转换让索引失效的排查路径 下篇我们聊 MySQL 死锁定位：SHOW ENGINE INNODB STATUS 怎么读——5 分钟定位死锁根因。
📺 公众号「Ai拆代码的曹操」 🌟 知识星球「Ai拆代码的曹操」
