2024 年我们一个运营后台的订单列表,被用户投诉"翻页越翻越慢"。这个列表数据量不小,单表两千多万行。前面几页点起来飞快,可运营同事为了导一批历史数据,一路往后翻,翻到几万页之后,接口直接卡到五六秒,再往后干脆超时报错。一开始我以为是数据量大、慢是正常的,直到我把那条 SQL 单独拎出来跑,看到 LIMIT 2000000, 20 这一行,才意识到问题根本不在数据量,而在我们用错了分页的姿势。这条 SQL 为了拿最后那 20 行,先把前面两百万行全部扫了一遍再扔掉。投了几天把"深分页"这件事彻底搞清并重构,本文复盘这次实战。
问题背景
业务:运营后台订单列表,单表 2000 万行,按 create_time 倒序分页
事故现象:
- 前几页打开飞快(几十毫秒)
- 翻到第 1 万页之后,接口耗时 3~6 秒
- 翻到更后面直接超时,运营导数据导不动
- 数据库 CPU 在有人深翻页时明显飙高
现场排查:
# 1. 看分页 SQL(MyBatis 拼出来的)
SELECT * FROM orders
WHERE status = 1
ORDER BY create_time DESC
LIMIT 2000000, 20; -- 第 10 万页,每页 20 条
# 2. EXPLAIN 看执行计划
+----+-------------+--------+------+---------------+------+---------+
| id | select_type | table | type | key | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+
| 1 | SIMPLE | orders | ref | idx_status |2003w | Using |
| | | | | | |filesort |
+----+-------------+--------+------+---------------+------+---------+
# rows 两千万,还有 filesort,慢得理所当然
# 3. 把 offset 调小再测
LIMIT 20 OFFSET 0 -> 18ms
LIMIT 20 OFFSET 10000 -> 95ms
LIMIT 20 OFFSET 1000000 -> 2100ms
LIMIT 20 OFFSET 2000000 -> 4300ms
# 耗时和 offset 几乎成正比 —— offset 越大越慢
根因:
1. LIMIT offset, n 的本质是"扫描 offset+n 行,丢弃前 offset 行"
2. offset 两百万,意味着要先白白扫描、回表两百万行
3. 用了 SELECT *,每一行都要回表取全部字段,放大了开销
4. 产品上允许"无限制地往后翻页",给了深分页可乘之机修复 1:先搞懂 LIMIT offset, n 到底慢在哪
=== LIMIT 2000000, 20 数据库做了什么 ===
很多人以为 offset 是"跳过",数据库能直接定位到第
200 万行。不是的。数据库的真实动作是:
1. 按 ORDER BY 的顺序,从头开始一行一行地取
2. 取到一行,offset 计数 +1
3. 在计数还没到 2000000 之前,取出来的行【直接丢弃】
4. 计数到了 2000000 之后,才开始真正收集,收满 20 行返回
=== 关键:被丢弃的两百万行,代价并不为零 ===
那两百万行不是"凭空跳过"的,数据库实实在在地:
- 沿着索引(或全表)扫描了这两百万个位置
- 如果是 SELECT *、且索引不是覆盖索引,
每一行还要【回表】一次,去主键聚簇索引取完整行
两百万次扫描 + 两百万次回表 —— 全部做完,只为了把它们扔掉。
=== 所以深分页慢的公式 ===
LIMIT offset, n 的代价 ≈ 正比于 (offset + n)
n 通常很小(一页 20 条),offset 才是大头。
offset 越往后,慢得越夸张,这不是 bug,是它的工作原理。
=== 一个重要的事实 ===
"翻到第 N 页"这个需求,和"offset = (N-1) * pageSize"
之间,并不是天然绑定的。
offset 分页只是实现"翻页"的一种方式,而且是
对深翻页极不友好的一种。换一种实现,就能绕开它。修复 2:覆盖索引 + 延迟关联,先榨干 offset 分页
-- === 优化思路:别让"被丢弃的行"也回表 ===
-- 原 SQL 的大头开销是:两百万行,每行都 SELECT * 回表。
-- 但这两百万行最后都被丢弃了,它们根本不需要完整字段!
-- 真正需要完整字段的,只有最后留下的那 20 行。
-- === 第一步:先用覆盖索引,只查出那 20 行的主键 id ===
-- 这个子查询只走 idx_status_time 索引,不回表,
-- 扫描两百万个【索引项】比扫描两百万【整行】快得多。
SELECT id FROM orders
WHERE status = 1
ORDER BY create_time DESC
LIMIT 2000000, 20;
-- 需要的索引:idx_status_time (status, create_time)
-- 这样 WHERE + ORDER BY 都能走索引,连 filesort 都省了
-- === 第二步:用这 20 个 id,去关联回主表取完整数据 ===
-- 这一步只回表 20 次,而不是两百万次。
SELECT o.* FROM orders o
INNER JOIN (
SELECT id FROM orders
WHERE status = 1
ORDER BY create_time DESC
LIMIT 2000000, 20
) AS t ON o.id = t.id
ORDER BY o.create_time DESC;
-- === 这个技巧叫"延迟关联"(deferred join)===
-- 把"回表取完整数据"这个昂贵操作,从两百万次
-- 推迟、缩减到了最后的 20 次。
-- 实测:同样 offset 200 万,4300ms -> 600ms 左右。
-- === 它的局限要看清 ===
-- 延迟关联确实快了不少,但它【没有改变本质】:
-- 那个子查询,还是要扫描两百万个索引项。
-- offset 再大(比如上千万),它依然会慢。
-- 它是"把 offset 分页优化到极限",而不是"消灭 offset"。修复 3:游标分页——彻底告别 offset
-- === 换个思路:不用 offset,用"上一页的最后一条"定位 ===
-- 翻页的本质是"接着上次的位置往下取",
-- 而"上次的位置",完全可以用上一页最后一行的
-- 排序字段值来表示 —— 根本不需要 offset 这个计数器。
-- === 第 1 页:正常取前 20 条 ===
SELECT id, order_no, create_time, amount
FROM orders
WHERE status = 1
ORDER BY create_time DESC, id DESC
LIMIT 20;
-- 记下这一页最后一行的 (create_time, id),假设是
-- create_time = '2024-06-01 10:00:00', id = 850000
-- === 第 2 页及以后:用上一页最后一行的值做 WHERE 条件 ===
-- "比上一页最后一条更旧的,再取 20 条"
SELECT id, order_no, create_time, amount
FROM orders
WHERE status = 1
AND create_time < '2024-06-01 10:00:00'
ORDER BY create_time DESC, id DESC
LIMIT 20;
-- === 为什么它快 ===
-- 有了 idx_status_time 索引,WHERE 里的
-- create_time < ? 可以让数据库【直接跳到】那个位置,
-- 沿着索引往后取 20 条就够了 ——
-- 不管你翻到第 1 页还是第 100 万页,
-- 它永远只扫描 20 行,耗时恒定在几十毫秒。
-- === 游标分页(keyset / cursor pagination)===
-- 这种用"上一页边界值"代替 offset 的方式,
-- 业内叫游标分页或 keyset 分页。
-- 它是大数据量列表/信息流分页的标准做法。
-- 微博、朋友圈那种"下拉加载更多",底层都是它。修复 4:排序字段不唯一?游标必须用复合键
// === 一个隐蔽的坑:create_time 可能重复 ===
// 如果同一秒(甚至同一毫秒)内有多条订单,
// create_time 就会重复。此时只用 create_time 做游标:
// WHERE create_time < '2024-06-01 10:00:00'
// 会出问题:边界值那一刻的多条记录,
// 可能【被跳过】或【重复出现】在相邻两页。
// === 正解:游标用"排序字段 + 唯一字段"的组合 ===
// ORDER BY create_time DESC, id DESC (id 唯一,做最终决胜)
// 游标也要带上 id,WHERE 写成"行值比较":
// (create_time, id) < (上一页最后的 create_time, 上一页最后的 id)
// MySQL 支持行值比较,可以直接这样写:
// WHERE (create_time, id) < (?, ?)
// 或者展开成等价的逻辑:
// WHERE create_time < ?
// OR (create_time = ? AND id < ?)
// === 后端把游标封装成一个不透明的 token ===
// 不要把 create_time、id 直接暴露给前端当参数,
// 把它们编码成一个 cursor 字符串,前端只负责透传。
public String encodeCursor(LocalDateTime time, long id) {
String raw = time.toString() + "|" + id;
return Base64.getEncoder()
.encodeToString(raw.getBytes(StandardCharsets.UTF_8));
}
public Cursor decodeCursor(String token) {
String raw = new String(Base64.getDecoder().decode(token),
StandardCharsets.UTF_8);
String[] parts = raw.split("\\|");
return new Cursor(LocalDateTime.parse(parts[0]),
Long.parseLong(parts[1]));
}
// === 查询时:第一页 cursor 为空,之后透传上一页返回的 cursor ===
public PageResult listOrders(String cursor, int size) {
List rows;
if (cursor == null || cursor.isEmpty()) {
rows = orderMapper.firstPage(size + 1); // 第一页
} else {
Cursor c = decodeCursor(cursor);
rows = orderMapper.afterCursor(c.time, c.id, size + 1);
}
// 技巧:多查一条(size+1),判断"还有没有下一页"
boolean hasNext = rows.size() > size;
if (hasNext) rows = rows.subList(0, size);
String nextCursor = hasNext
? encodeCursor(last(rows).getCreateTime(), last(rows).getId())
: null;
return new PageResult<>(rows, nextCursor, hasNext);
} 修复 5:总数 count 也是深分页的隐形杀手
-- === 别忽略了:分页接口往往还附带一个 count ===
-- 前端要显示"共 1234567 条 / 共 61728 页",
-- 后端就得多跑一条 count:
SELECT COUNT(*) FROM orders WHERE status = 1;
-- 在两千万行的表上,这条 count 本身就可能要 1~2 秒,
-- 哪怕你的分页查询已经优化到 20ms,它照样拖慢接口。
-- === 优化 1:count 和 list 并行查 ===
-- 它俩没有依赖关系,别串行。用线程池并行发两条 SQL,
-- 接口耗时 = max(count耗时, list耗时),而不是两者之和。
-- === 优化 2:count 结果缓存 ===
-- 总数对绝大多数列表来说,不需要实时精确。
-- 把 count 结果缓存进 Redis,设个较短过期时间(如 1 分钟),
-- 期间所有翻页请求共用这个缓存值。
-- === 优化 3:游标分页干脆"不要总页数" ===
-- 这是最彻底的做法。游标分页天然适合
-- "下一页 / 上一页 / 加载更多"的交互,
-- 它本来就不该有"跳到第 6 万页"这种功能。
-- 既然没有"跳页",也就不需要总页数,
-- 把 count 整个去掉,只返回"有没有下一页"(hasNext)。
-- 信息流类产品(各种 App 的 feed)全是这么做的。
-- === 优化 4:海量表用估算值 ===
-- 若一定要个数字给用户看,但不要求精确,
-- 可以用执行计划里的预估行数:
EXPLAIN SELECT * FROM orders WHERE status = 1;
-- 取 rows 字段作为近似总数,几乎零成本。
-- 适合"约 1200 万条结果"这种模糊展示。修复 6:深分页问题,一半要靠产品设计来解
=== 一个被忽视的真相:深分页常常是"伪需求" ===
我们最初的事故,导火索是运营"一路翻到几万页"。
但回头想:有谁会真的一页一页点到第 5 万页去看数据?
没有人。运营那么做,只是因为他想【找到/导出某批数据】,
而我们没给他更好的工具,他只能用"翻页"这个笨办法。
深分页的请求里,绝大多数是这种"被逼出来的"伪需求。
=== 产品层面的解法 ===
1. 限制最大翻页深度
超过一定页数(如第 100 页)就不再允许往后翻,
提示用户"请用筛选条件缩小范围"。
这不是偷懒,而是引导用户用对的方式找数据。
2. 提供足够强的筛选 / 搜索
用户翻到深处,本质是在"大海捞针"。
给他按时间范围、订单号、状态、用户等条件筛选,
筛完结果集小了,根本翻不到深处去。
3. 把"导数据"和"看列表"分开
运营要导历史数据,就给他一个专门的导出功能 ——
后台异步跑、用游标分批拉取、生成文件下载。
而不是让他用"页面翻页"来变相导数据。
=== 技术方案与产品方案要配合 ===
- 看列表 / 加载更多 -> 游标分页,耗时恒定
- 跳到任意页(浅层) -> offset 分页 + 延迟关联,够用
- 导出全量数据 -> 后台异步任务 + 游标分批拉取
- 深层任意跳页 -> 基本是伪需求,用筛选替代
纯靠 SQL 优化能把深分页从"几秒"压到"几百毫秒",
但要让它彻底消失,得靠产品设计把这个场景"设计掉"。修复 7:游标分页的 Mapper 实现
优化效果
指标 治理前 治理后
=============================================================
第 1 页耗时 18ms 15ms
第 1 万页耗时 2100ms 游标分页 ~25ms
第 10 万页耗时 4300ms / 超时 游标分页 ~25ms
翻页耗时随深度变化 正比增长,越翻越慢 恒定,与深度无关
查询字段 SELECT * 按需字段 + 延迟关联
ORDER BY filesort 全程走索引,无 filesort
count 查询 每次串行实时跑 ~1.5s 并行 + 缓存,~50ms
总页数展示 精确总页数 游标分页改为 hasNext
深翻页时数据库 CPU 明显飙高 平稳
导数据方式 运营手动翻页变相导 专用异步导出任务
治理过程:
- 定位 LIMIT offset 深分页根因:0.5 天
- 列表改游标分页 + 复合游标:1.5 天
- 浅层跳页保留 offset + 延迟关联:0.5 天
- count 并行化 + 缓存:0.5 天
- 异步导出功能 + 翻页深度限制:1 天避坑清单
- LIMIT offset, n 的本质是扫描 offset+n 行再丢弃前 offset 行,offset 越大越慢
- 深分页耗时与 offset 几乎成正比,这不是 bug 是它的工作原理,别指望"调优参数"解决
- SELECT * 会让被丢弃的每一行都回表,放大开销,深分页务必只查需要的字段
- 延迟关联:先用覆盖索引查出 20 个 id,再 join 回表,把回表从百万次降到 20 次
- 延迟关联只是把 offset 分页优化到极限,offset 极大时依然慢,没改变本质
- 游标分页用上一页最后一行的排序字段值做 WHERE 条件,翻页耗时与深度无关、恒定
- 排序字段可能重复时,游标必须用"排序字段+唯一字段(如 id)"复合键,否则漏数据
- 游标建议编码成不透明 token 给前端透传,不要直接暴露 create_time、id
- count 在大表上很慢,要和 list 并行查、结果缓存,游标分页干脆去掉总页数
- 深分页多是伪需求,要靠限制翻页深度、强化筛选、独立异步导出从产品上设计掉
总结
这次深分页的优化,最大的收获其实不是某条 SQL 怎么写,而是纠正了一个我一直以来的错误直觉。在动手排查之前,我下意识地以为"表里有两千万行数据,翻到很后面慢,是数据量大导致的、是正常的"——这个想法听上去合情合理,却完全错了。真正的原因不是数据多,而是我们取数据的方式错了。LIMIT 2000000, 20 这条 SQL,看起来只是想要 20 行数据,可数据库为了执行它,必须老老实实地从头开始,沿着索引一行一行地往下数,数满两百万行之后,才肯开始收集你真正要的那 20 行。换句话说,那被丢弃的两百万行,数据库一行都没少扫,该回表的回表、该读的页都读了,做完全部的脏活累活,只为了最后把它们扔进垃圾桶。理解了这一点,深分页为什么慢就一目了然了:它的代价正比于 offset,offset 翻到几百万,慢到几秒就是必然结果。这不是一个可以靠"调几个参数"或"加台机器"糊弄过去的性能抖动,它是 LIMIT offset 这个写法刻在骨子里的特性。想清楚根因之后,优化的路径就分成了两条,对应两种不同的真实需求。第一条路是,如果业务确实需要"跳到任意一页",但翻的深度不算夸张,那就用覆盖索引加延迟关联,把它优化到极限——核心思路是,既然那两百万被丢弃的行不需要完整字段,就别让它们回表,先用一个只走索引的子查询,轻快地查出最终那 20 行的主键 id,再拿这 20 个 id 去关联主表取完整数据,于是昂贵的回表操作就从两百万次锐减到了 20 次。这个办法立竿见影,但我必须诚实地说,它没有改变问题的本质,那个子查询依然要扫描两百万个索引项,offset 要是再大上一个数量级,它照样会慢——它是"把 offset 分页优化到极致",而不是"消灭 offset"。第二条路,也是真正治本的路,是游标分页。它的精妙之处在于换了一个看问题的角度:翻页这件事的本质,根本不是"跳到第 N 页",而是"接着上次停下的地方继续往下取";而"上次停下的地方",完全可以用上一页最后一行的排序字段值来精确表示,根本不需要 offset 这个累人的计数器。于是分页查询从"扫描并丢弃 offset 行"变成了"用 WHERE 条件直接定位到上次的位置,再往后取 20 条",借助索引,数据库可以一步跳到那个位置,无论你翻的是第一页还是第一百万页,它永远只扫描 20 行,耗时恒定在几十毫秒。这中间有一个很容易被忽略的坑:如果排序字段(比如 create_time)有重复值,只用它做游标会导致边界处的记录被跳过或重复,所以游标必须做成"排序字段 + 一个唯一字段(通常是 id)"的复合键,用 id 来给那些时间相同的记录做最终的决胜排序。除了分页查询本身,这次我还顺带收拾了一个一直被我忽略的隐形杀手——count。一个分页接口往往不只查列表,还要查一个总数给前端显示"共多少页",而在两千万行的大表上,一条 COUNT(*) 自己就要跑一两秒,哪怕你的列表查询已经优化到 20 毫秒,这条 count 也会稳稳地把整个接口拖回到秒级。对它的处理可以是并行查、可以缓存,而最彻底的做法是:既然游标分页本来就不支持"跳到任意页",它也就不需要总页数,直接把 count 整个去掉,只返回一个"还有没有下一页"的布尔值就够了——所有 App 的信息流都是这么做的。写到最后,这次复盘真正沉淀下来、让我觉得最有价值的一条经验是:深分页问题,有一半根本不该用技术来解,而该用产品设计来解。我们这次事故的导火索,是运营同事一路翻到了几万页,可冷静下来想想,世界上没有任何一个正常人,会真的有耐心一页一页点到第五万页去"浏览"数据——他那么做,唯一的原因是他想找到或导出某一批特定的数据,而我们没有给他一个像样的工具,他走投无路,只能把"翻页"这个功能硬掰成一个蹩脚的"导数据"工具来用。深分页的请求里,绝大多数都是这种被产品的缺失硬生生逼出来的伪需求。所以正确的应对,是限制最大翻页深度、是提供足够强大的筛选和搜索让用户能迅速把结果集缩小、是为"导出数据"提供一个专门的、后台异步用游标分批拉取的功能。纯靠 SQL 优化,我们能把深分页从几秒压到几百毫秒;但要让这个糟糕的场景彻底消失,靠的是产品设计把它从源头上"设计掉"。技术方案负责把该做的事做快,产品设计负责让不该发生的事压根别发生,这两者配合起来,深分页才算真正被解决了。
—— 别看了 · 2026