我的列表分页接口翻到前几页飞快、翻到几十万页却越来越慢直到超时,我盯着那条带巨大 OFFSET 的 SQL 排查了大半天才搞懂深分页的真相
这是一个让我对"分页"这件最稀松平常的事,彻底改观的故事。我有一个数据列表接口,用最经典的 LIMIT offset, size 做分页。它在前面几页,响应飞快,几十毫秒就返回了。可有用户反馈:翻到很后面的页,就越来越慢。我自己一测,倒吸一口凉气:第 1 页,20 毫秒;第 1000 页,200 毫秒;翻到几十万页(LIMIT 1000000, 20),竟然要好几秒,甚至直接超时!
奇怪的是,每一页,我都只取 20 条数据啊,数据量一模一样,凭什么越往后越慢?我把那条慢 SQL 拎出来 EXPLAIN,又翻了 MySQL 的执行原理,才终于揭开真相,补上了我对分页一个天大的误解:问题,出在那个巨大的 OFFSET 上。LIMIT 1000000, 20 的真实含义,并不是"直接跳到第 1000000 行、取 20 条";而是——数据库会老老实实地,从头开始,扫描出前 1000020 行,然后,把前面的 1000000 行,全部丢弃,只返回最后那 20 行!也就是说,OFFSET 越大,数据库要"扫描并丢弃"的行,就越多;翻到第 100 万行,它就要白白地扫描、再丢弃 100 万行,做的是大量"读出来,又扔掉"的无用功——这,就是我接口越翻越慢的根源。我这才痛彻地明白:我一直想当然地以为,LIMIT offset, size 是"精准定位、直达目标"的,以为"每页都只取 20 条,开销就该一样";殊不知,OFFSET 的代价,是随着它的增大而线性增长的。这种"翻到后面页就慢到无法忍受"的问题,有个专门的名字,叫"深分页(deep pagination)"——它是几乎所有用 LIMIT offset 做分页的系统,迟早会撞上的一堵墙。
故障现场:巨大的 OFFSET,让数据库扫描并丢弃海量行
我把这个"越翻越慢"的现场,用 SQL 摊开给你看:
-- ✗ 灾难: 用巨大 OFFSET 做深分页, 越往后越慢
-- 第 1 页: 快
SELECT * FROM orders ORDER BY id LIMIT 0, 20; -- 扫 20 行, 20ms
-- 第 1000 页: 慢一点
SELECT * FROM orders ORDER BY id LIMIT 20000, 20; -- 扫 20020 行, 丢 20000, 200ms
-- 第 5 万页: 慢到离谱
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20; -- ✗ 扫 1000020 行, 丢 100 万行! 几秒~超时
-- LIMIT offset, size 的真相:
-- 它 NOT "直接跳到第 offset 行", 而是:
-- 1. 从头按 ORDER BY 顺序, 扫描出 offset + size 行。
-- 2. 把前 offset 行, 全部丢弃。
-- 3. 只返回最后 size 行。
-- → offset 越大, 扫描并丢弃的行越多 → 越慢(代价随 offset 线性增长)。
-- EXPLAIN 看现象:
EXPLAIN SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- rows 一栏会显示扫描了约 100 万行 —— 大量无用功(读出又扔掉)。
-- 即使 id 上有索引也救不了:
-- 走索引能避免全表扫, 但仍要"沿索引数 offset 个"才到目标 → offset 大照样慢。
-- 根因: 巨大 OFFSET 让数据库扫描并丢弃海量行, 代价随 offset 线性增长 → 深分页慢。看着这几条 SQL 和它们的耗时,我才算彻底想明白了这场"越翻越慢"的根源。问题的核心,是那个巨大的 OFFSET。LIMIT offset, size 的真相,不是"直接跳到第 offset 行",而是:第一,从头按 ORDER BY 的顺序,扫描出 offset + size 行;第二,把前 offset 行,全部丢弃;第三,只返回最后 size 行。所以:OFFSET 越大,数据库要扫描并丢弃的行,就越多;翻到第 100 万行,它就要白白扫描、再丢弃 100 万行,做的全是"读出来又扔掉"的无用功——EXPLAIN 里 rows 一栏,会赤裸裸地显示它扫了约 100 万行。更要命的认知是:这个问题,即使你在 id 上建了索引,也救不了——索引能让它避免全表扫描,但它仍然要"沿着索引,一个一个数过 offset 个节点",才能到达目标位置;offset 一大,照样慢。归根结底:我一直误以为 LIMIT offset, size 是"精准定位、每页等价"的;真相是——巨大的 OFFSET,会让数据库扫描并丢弃海量行,其代价随 offset 的增大而线性增长,这,就是"深分页"慢的根本原因。
第一件事:搞懂 OFFSET 的代价为什么随页数增长
定位到根源,我必须把"深分页为什么慢"这件事,从原理上彻底吃透:
深分页慢的本质: OFFSET 越大, "扫描并丢弃"的行越多
# LIMIT offset, size 干了什么?
# - 数据库无法"凭空跳到"第 offset 行(它不知道第 N 行的物理位置)。
# - 只能从头按顺序读: 读够 offset + size 行, 丢掉前 offset 行, 返回后 size 行。
# - 代价 ∝ offset(读了多少行, 就花多少功夫, 哪怕大部分要丢)。
# 为什么"每页只取 20 条"还会越来越慢?
# - 慢的不是"返回 20 条", 而是"为了找到这 20 条, 要先跳过/丢弃前面 N 条"。
# - 第 1 页跳过 0 条; 第 5 万页要跳过 100 万条 → 这才是开销所在。
# 索引能解决吗? 部分:
# - 没索引: 全表扫描 + 排序(filesort), 更慢。
# - 有索引(覆盖 ORDER BY): 避免全表扫, 但仍要沿索引数 offset 个 → offset 大照样慢。
# - 索引解决"怎么扫", 解决不了"要扫并丢这么多行"这件事本身。
# 深分页的连带问题:
# - SELECT * 还会回表 offset+size 次(每行去聚簇索引取完整数据), 雪上加霜。
# - 大 offset + 排序, 可能用临时表/filesort, 内存吃紧。
# 关键认知: 分页性能不应"随页码增长而退化"。
# 理想分页, 翻到第 1 页和第 100 万页, 开销应该差不多。
# 要做到这点, 就不能依赖 OFFSET "数过去", 而要靠"定位"直接跳到起点。
# 核心: OFFSET 的代价随它增大而线性增长(扫描并丢弃);
# 深分页慢的本质是"为了拿 N 条要先丢弃 offset 条", 索引治标不治本。原理终于清晰了。为什么 LIMIT offset, size 的代价,会随 offset 增长?——因为数据库无法"凭空跳到"第 offset 行(它不知道第 N 行在磁盘上的物理位置),它只能从头按顺序读:读够 offset + size 行,丢掉前 offset 行,返回后 size 行;读了多少行,就花多少功夫,哪怕大部分都要丢。那为什么"每页只取 20 条"还会越来越慢?因为慢的不是"返回 20 条",而是"为了找到这 20 条,要先跳过、丢弃前面那 N 条"——第 1 页跳过 0 条,第 5 万页要跳过 100 万条,这,才是开销所在。索引能解决吗?只能部分:没索引,全表扫描加排序,更慢;有索引,能避免全表扫,但仍要沿索引数过 offset 个节点,offset 大照样慢——索引解决的是"怎么扫",解决不了"要扫并丢这么多行"这件事本身。而且深分页还有连带问题:SELECT * 会回表 offset+size 次、大 offset 加排序可能用临时表 / filesort,雪上加霜。由此,我建立起一个关键认知:分页性能,不应该"随页码增长而退化"——理想的分页,翻到第 1 页和第 100 万页,开销应该差不多;要做到这点,就不能依赖 OFFSET "一个个数过去",而要靠"定位",直接跳到这一页的起点。这,就是我下一步要找的正解的方向。
第二件事:正解——用游标/keyset 分页,直接定位到起点
搞懂了原理,正解就清晰了:不要用 OFFSET "数过去",而要用上一页的最后一条记录的值,直接定位到这一页的起点。这叫"游标分页 / keyset 分页"(也叫 seek method)。
-- ✓ 正解: 游标/keyset 分页 —— 用上一页最后一条的 id 做"定位锚点"
-- 第 1 页(没有上一页, 直接取前 20)
SELECT * FROM orders ORDER BY id LIMIT 20;
-- 记下这页最后一条的 id, 比如 last_id = 20
-- 下一页: WHERE id > 上一页最后的 id, 再取 20
SELECT * FROM orders WHERE id > 20 ORDER BY id LIMIT 20; -- ✓ 走索引直接定位到 id>20, 再取 20
-- 记下 last_id = 40
-- 再下一页
SELECT * FROM orders WHERE id > 40 ORDER BY id LIMIT 20; -- ✓ 同样快!
-- 翻到"第 100 万行附近"也一样快:
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20; -- ✓ 索引一跳就到, 20ms
-- 为什么快?
-- - WHERE id > last_id 走 id 索引, 直接二分定位到起点(不用数过 offset 个)。
-- - 然后顺着索引取 20 条即可。
-- - 开销和"在哪一页"无关 —— 第 1 页和第 100 万页, 一样快!
-- 代价对比:
-- LIMIT 1000000, 20 → 扫描并丢弃 100 万行(几秒)
-- WHERE id > 1000000 LIMIT 20 → 索引定位 + 取 20 行(毫秒级)
-- 核心: 游标分页用"上一页最后的值"做锚点, WHERE > 锚点走索引直接定位,
-- 开销与页码无关, 第 1 页和第 100 万页一样快。修复的思路,堪称釜底抽薪。正解,是用"游标 / keyset 分页":不再用 OFFSET 去"数过去",而是记住上一页最后一条记录的 id(作为"锚点"),下一页就查 WHERE id > 上一页最后的 id ORDER BY id LIMIT 20。为什么这样就快了?因为 WHERE id > last_id 能走 id 索引,直接二分定位到这一页的起点,根本不用"一个个数过 offset 个节点",然后顺着索引取 20 条就行。最关键的是:它的开销,和"你翻到第几页",完全无关——翻到第 1 页,和翻到第 100 万行附近,是一样快的(都是"索引一跳就到 + 取 20 条",毫秒级)。对比一下就触目惊心:LIMIT 1000000, 20 要扫描并丢弃 100 万行(几秒);而 WHERE id > 1000000 ... LIMIT 20,只需索引定位 + 取 20 行(毫秒级)。归根结底:游标分页,用"上一页最后的值"做锚点,WHERE > 锚点 走索引直接定位,开销与页码无关,第 1 页和第 100 万页一样快——这,才是分页该有的样子。
第三件事:游标分页的坑——排序字段必须唯一、有序
游标分页虽好,但用错了照样翻车。我踩了它的几个坑,也总结了它的适用边界:
游标分页的注意事项: 锚点字段要唯一、有序、有索引
# 1. 锚点字段必须"唯一且有序"(否则会漏数据/重复)
# - 用 id(主键, 唯一递增)最稳。
# - 若按 create_time 排序而时间可能重复 → 同一时间的多条会出错!
# 正解: 用"复合锚点" (create_time, id), WHERE 用 (time, id) > (last_time, last_id)。
# 2. 锚点字段必须有索引(否则 WHERE > 还是慢)
# - ORDER BY 的字段, 要能走索引定位。
# 3. 游标分页的"限制": 不能直接跳到"任意第 N 页"
# - 它只能"下一页/上一页"(因为要靠上一页的锚点)。
# - 适合: 瀑布流、无限滚动、"加载更多"、后台批量遍历导出。
# - 不适合: 必须显示"第 1 2 3 ... 999 页"页码、能随机跳页的场景。
# 4. 倒序分页同理:
# ORDER BY id DESC, WHERE id < last_id LIMIT 20。
# 折中方案(如果业务必须支持跳页):
# - 限制最大可翻页数(比如最多翻到 100 页), 超过引导用户用筛选缩小范围。
# - 或: 先用覆盖索引查出主键 (SELECT id ... LIMIT offset, size), 再回表 —— 减少回表开销。
# - 搜索类场景: 交给 ES 等搜索引擎(也有 search_after 这种游标机制)。
# 核心: 游标分页锚点要唯一有序有索引(首选主键 id);
# 时间排序加 id 做复合锚点; 它只支持顺序翻页, 跳页场景需折中。游标分页的"使用说明书",我也摸清了。第一,锚点字段必须"唯一且有序"——用主键 id 最稳;如果你按 create_time 排序、而时间可能重复,那同一时刻的多条记录就会出错(漏掉或重复),正解是用"复合锚点"(create_time, id),WHERE (time, id) > (last_time, last_id)。第二,锚点字段必须有索引,否则 WHERE > 还是慢。第三,要清楚它的限制:游标分页不能直接跳到"任意第 N 页"(因为它要靠上一页的锚点),它只能"下一页 / 上一页"——所以它完美适配瀑布流、无限滚动、"加载更多"、后台批量遍历导出;但不适合那种必须显示"第 1 2 3 … 999 页"、能随机跳页的场景。那万一业务必须支持跳页呢?折中:限制最大可翻页数(比如最多 100 页,再多就引导用户用筛选缩小范围);或先用覆盖索引查出主键、再回表,减少回表开销;搜索类场景,干脆交给 ES(它有 search_after 这种游标机制)。归根结底:游标分页的锚点要唯一、有序、有索引(首选主键 id),时间排序要加 id 做复合锚点;它只支持顺序翻页,跳页场景需折中处理。
下面这张图,是分页方案随数据规模的演进决策:
第四件事:几种分页方案的横向对比
修复时我把能想到的分页方案,按"深翻性能"和"能否跳页"两个维度,横向比了一遍,按场景对号入座。
| 方案 | 深翻性能 | 能否随机跳页 | 适用场景 |
|---|---|---|---|
| LIMIT offset, size | 差(随 offset 线性变慢) | 能 | 数据少、总页数不多的后台列表 |
| 游标/keyset(WHERE id>last) | 优(与页码无关) | 否(只能顺序翻) | 瀑布流、无限滚动、批量遍历 |
| 覆盖索引 + 延迟回表 | 较好(减少回表) | 能 | 必须跳页、但想优化深分页 |
| 限制最大页数 | 规避问题 | 有限 | 用户极少翻到很深的 C 端列表 |
| ES search_after | 优 | 顺序为主 | 搜索、大数据量复杂排序 |
把它们排在一起,选择逻辑就清楚了。判断只看两点:会不会翻到很深的页、需不需要随机跳页。如果数据少、总共没几页,那 LIMIT offset, size 最简单、够用,别过度设计;如果会深翻、且是瀑布流/无限滚动这种顺序加载的交互,游标分页是最优解(深翻性能与页码无关);如果必须支持跳页、又想优化深分页,可以用覆盖索引先查主键、再延迟回表;C 端列表用户极少翻到很深,可以直接限制最大页数规避;而搜索、大数据量复杂排序,交给 ES 的 search_after。核心是:没有"最好的分页",只有"最适配你交互形态和数据规模的分页"——但有一条铁律:只要数据会涨、会被深翻,就别用裸的大 OFFSET。
第五件事:深分页背后,那些"代价随规模增长"的隐形陷阱
这次踩坑后,我警觉地意识到:深分页只是冰山一角,代码里还藏着许多"小数据量下没事、数据一大就指数级恶化"的写法。我把它们一并排查了。
| 隐形陷阱 | 小数据量表现 | 大数据量真相 |
|---|---|---|
| LIMIT 大 offset 深分页 | 飞快 | 扫描丢弃海量行, 越深越慢直到超时 |
| SELECT * 取全字段 | 没感觉 | 大字段/回表放大 IO, 网络与内存暴涨 |
| COUNT(*) 算总数给前端 | 瞬间 | 大表 count 要扫全表/索引, 很慢 |
| ORDER BY 无索引字段 | 没感觉 | filesort 全量排序, 数据一大就慢 |
| 一次性 IN (上万个 id) | 正常 | SQL 超长、解析慢、可能不走索引 |
这张表,让我对"规模"这个变量,有了刻骨的敬畏。这些陷阱的共性,和深分页一模一样:它们的代价,都随着数据规模的增长而恶化,只是在小数据量下,被"快"的假象,完美地掩盖了。SELECT * 在小表上没感觉,大表上大字段和回表会放大 IO;COUNT(*) 给前端显示"共 N 条",在大表上要扫全表、很慢(很多产品干脆改成"只显示有没有下一页"就是为了避开它);ORDER BY 一个没索引的字段会触发 filesort 全量排序;一次性 IN 上万个 id 会让 SQL 超长、解析慢、甚至不走索引。它们都在提醒我同一件事:写任何一条 SQL、任何一段数据处理,都要在脑子里把数据量放大一千倍、一万倍,问一句:"当这张表有一亿行时,它还撑得住吗?"——因为线上的数据,只会越来越多,今天的"飞快",很可能就是明天的"雪崩"。
第六件事:面对一个分页需求,我现在会怎么决策
现在,每当我接到一个"列表 + 分页"的需求,脑子里都会过一遍这张决策图——核心不是"怎么写 SQL",而是"数据会涨到多大、用户会怎么翻"。
这张图的灵魂,是把决策前移到了"理解数据与交互",而不是"埋头写 SQL"。第一问:这张表的数据量,未来会很大吗?——如果顶天几千行,那 LIMIT offset 最简单、够用,别过度设计;如果会持续增长,就必须认真对待深分页。第二问:用户需要随机跳到任意页吗?——不需要、只顺序往下翻,那游标分页就是最优解;需要跳页,再分 C 端还是后台:C 端用户极少深翻,就限制最大页数;后台或必须深翻,就覆盖索引 + 延迟回表,或交给 ES。最后,无论选哪条,我都会用 EXPLAIN 验证它确实走了索引、没有扫描海量行,才敢上线。
我立下的几条规矩
这场"越翻越慢直到超时"的事故,换来了我做分页和数据查询时,刻进骨子里的几条铁律:
- 数据会涨的表,别用裸的大 OFFSET 分页。OFFSET 的代价随页码线性增长,深翻必慢,这是原理问题,不是调优能救的。
- 能用游标分页就用游标分页。WHERE id > last_id ORDER BY id LIMIT n,开销与页码无关,瀑布流/无限滚动的天然搭配。
- 游标锚点要唯一、有序、有索引。首选主键 id;按时间排序就用 (time, id) 复合锚点,避免漏数据/重复。
- 必须跳页时,用覆盖索引延迟回表或限制最大页数。别硬刚大 offset,给深翻一个边界或优化路径。
- 写每条 SQL 都把数据量放大一万倍想。SELECT 别 *、COUNT 大表要警惕、ORDER BY 要有索引、IN 别塞上万个。
- 慢就 EXPLAIN,看 rows 和 type。扫描行数异常大、type 是 ALL,就是优化信号,真相都在执行计划里。
- 分页性能不该随页码退化。把"第 1 页和第 100 万页一样快"当成衡量分页方案好坏的标尺。
附:后台批量遍历导出,游标分页的标准写法
深分页最常见的"重灾区",其实是后台的"批量遍历/导出"——要把一张大表的所有数据,一页页全捞出来处理。用 OFFSET 遍历,越到后面越慢,几百万行能跑到天荒地老。这里给一段游标遍历的标准写法:
# ✓ 用游标分页遍历整张大表(导出/迁移/批处理), 全程恒定速度
def iterate_all(conn, batch_size=1000):
last_id = 0
while True:
rows = conn.query(
# ✓ WHERE id > last_id 走主键索引直接定位, 与已遍历多少无关
"SELECT id, name, amount FROM orders "
"WHERE id > %s ORDER BY id ASC LIMIT %s",
(last_id, batch_size),
)
if not rows:
break # 没有更多数据, 遍历结束
for r in rows:
yield r # 交给上层处理(导出/计算/写入...)
last_id = rows[-1]["id"] # ✓ 关键: 记下这批最后一条的 id 作为下批锚点
# 用法: 无论表有多大, 每批都是"索引定位 + 取 1000 条", 速度恒定
for row in iterate_all(conn):
export(row)
# ✗ 反面教材: 用 OFFSET 遍历, 越到后面越慢
# offset = 0
# while True:
# rows = query("... LIMIT %s OFFSET %s", (batch, offset)) # ✗ offset 越大越慢
# if not rows: break
# offset += batch # 几百万行能跑到天荒地老
# 核心: 批量遍历大表用游标(记 last_id, WHERE id>last_id), 每批恒定速度;
# 千万别用 OFFSET 递增遍历, 那是 O(n^2) 的灾难。这段代码,是我现在所有"遍历大表"任务的标准模板。它的精髓,就是用 last_id 这个游标,代替不断增大的 OFFSET:每一批,都是 WHERE id > last_id ORDER BY id LIMIT 1000,走主键索引直接定位、取 1000 条,然后记下这批最后一条的 id,作为下一批的锚点。这样,无论这张表有一万行还是一亿行,每一批的速度,都是恒定的。而它要替换掉的反面教材,正是那个看起来人畜无害的"OFFSET 递增遍历":用 OFFSET += batch 去遍历,越到后面,每批越慢,整个遍历的总代价是 O(n²) 级的——几百万行的表,能让你的导出任务,跑到天荒地老、甚至把数据库拖垮。同样是"把全表数据捞一遍",一个匀速、优雅地跑完,一个越跑越慢、最终失控——而它们的差别,仅仅在于:你是用"数过去"的思维,还是用"定位"的思维,去面对海量的数据。
写在最后
回头看,这场由一个最普通的 LIMIT offset 引发的、越翻越慢直到超时的事故,真正教给我的,是一个比"深分页"本身更深远的道理:很多性能问题,不是"写错了",而是"在小数据量下,你永远发现不了它写得不对"。那个 LIMIT offset 分页,在开发时、在测试时、在上线初期数据还少时,一直都"飞快"、一直都"对"——它用这份长久的"看起来没问题",让我彻底放松了警惕;直到数据,在时间的长河里,悄无声息地涨到了百万、千万级,它才露出獠牙,慢给我看。所以,写数据相关的代码,最需要的,是一种"对规模的想象力":你必须能在数据还只有几千行的今天,就预见到它有一亿行的那一天,并问自己:"到那时,我现在写的这行代码,会变成什么样子?"真正的好性能,从来不是"等慢了再去优化",而是在写下它的那一刻,就已经为"它终将面对的海量数据"做好了准备。把"当这张表有一亿行时"当成每一次落笔前的默念——这,是我用一次"翻到深处就超时"的崩溃,换来的、关于数据库最朴素、也最值钱的领悟。如果这篇复盘,能让你在下一次写下分页 SQL 之前,多想一句"用户翻到很后面会怎样",那我 EXPLAIN、熬的这大半天,就值了。
—— 别看了 · 2026