一个用 LIMIT offset 做分页的接口,翻到第几万页时一条查询要跑十几秒,把数据库拖垮:一次深分页性能的深度复盘

那个性能问题是被一个"爬数据"的脚本暴露出来的:我们一个列表接口用最常规的 LIMIT offset, size 做分页,前几页快得飞起,可那个脚本一页页往后翻,翻到几万页之后,同一个接口、同样查 20 条,却要跑十几秒,慢查询告警响成一片,数据库 CPU 被打满。我一开始很困惑:不都是查 20 条吗?第 1 页查 20 条是毫秒级,第 5 万页查 20 条凭什么要十几秒?我盯着那条 SELECT * FROM orders ORDER BY id LIMIT 1000000, 20 看了半天,才终于想明白这个"深分页"的要命之处,后背发凉:LIMIT 1000000, 20 的真实含义,并不是"直接跳到第 100 万行、取 20 条",而是"从头开始扫描、找出符合条件的前 1000020 行,然后把前 1000000 行全部丢弃,只返回最后 20 行"。也就是说,offset 越大,数据库要"白白扫描并丢弃"的行就越多——翻到第 5 万页,它要先吭哧吭哧地扫描、回表、再扔掉前一百万行,做的全是无用功,自然越来越慢。这篇就把这次"LIMIT offset 深分页性能崩塌"的坑,从头到尾复盘一遍。

故障现场:一个 offset 越来越大的分页查询

问题代码,是一个再标准不过的分页查询:

-- ✗ 出问题的分页: 用 LIMIT offset, size, offset 随页码线性增大
-- 第1页:    SELECT * FROM orders ORDER BY id LIMIT 0, 20;       -- 快(毫秒)
-- 第100页:  SELECT * FROM orders ORDER BY id LIMIT 1980, 20;    -- 还行
-- 第50000页:SELECT * FROM orders ORDER BY id LIMIT 1000000, 20; -- ✗ 十几秒!

-- 同样是查20条, 为什么第5万页这么慢?
-- LIMIT 1000000, 20 的真实执行:
--   1. 从头按 id 顺序扫描;
--   2. 找出前 1000020 行(每行可能还要"回表"取 SELECT * 的完整数据);
--   3. 把前 1000000 行【全部丢弃】;
--   4. 只返回最后 20 行。
-- → 它真正"有用"的只有最后20行, 却为此【扫描+回表了一百万零二十行】, 全是无用功!
-- → offset 越大, 要扫描并丢弃的行越多, 查询越慢(O(offset)级别的代价)。

-- 雪上加霜: SELECT * 还要"回表"
--   即使 id 上有索引, 扫描索引拿到前100万个id后, 还要逐个回主键表取整行(SELECT *),
--   一百万次回表 → 大量随机IO → 慢上加慢。

-- 关键: LIMIT offset 的代价随 offset【线性增长】; offset很大时(深分页),
--       绝大部分工作都耗在"扫描并丢弃"上 → 深分页是分页性能的头号杀手。

第一次理解 LIMIT offset 的真实行为时,我恍然大悟又有点无奈:"我一直以为 offset 是'直接定位',原来它是'从头数到那、再把前面全扔了'?"这个坑最隐蔽的地方在于:它在前几页(小 offset)时完全正常——开发和测试时,我们几乎总是只看前几页,LIMIT 0,20、LIMIT 40,20 都快得很,根本测不出问题;它只在翻到很后面(大 offset)时才暴露,而"翻到第 5 万页"这种操作,通常是爬虫、批处理、或导出才会触发的,普通用户很少翻那么深。于是这个坑潜伏得很深:功能完全正常、前台体验也正常,直到某个深翻的场景把数据库拖垮。下面就来拆解,深分页为什么慢、怎么破。

第一件事:搞懂 LIMIT offset 为什么随 offset 变慢

我认真研究了 MySQL 执行 LIMIT offset 的过程,才彻底理解深分页的性能根源。

LIMIT offset, size 为什么 offset 越大越慢?

【核心: 数据库无法"直接跳到第offset行", 它必须从头扫描到第offset+size行, 再丢弃前offset行】

1. LIMIT offset, size 的真实语义:
   - 不是"直接定位到第offset行"(数据库做不到随机跳到第N行);
   - 而是"按顺序扫描, 数出前 offset+size 行, 丢弃前offset行, 返回后size行"。

2. 为什么不能"直接跳"?
   - 数据/索引是按顺序组织的(B+树), 要知道"第1000000行是谁",
     只能从头一个个数过去——它没有"第N行的直接地址"。
   - → 所以必须实际扫描经过前面所有行(哪怕马上要丢弃)。

3. 代价随 offset 线性增长:
   - LIMIT 0,20:    扫20行 → 快;
   - LIMIT 1000000,20: 扫1000020行、丢100万 → 慢;
   - → 工作量 ∝ offset+size, offset主导 → offset越大越慢(O(offset))。

4. SELECT * 让情况更糟(回表):
   - 若用二级索引(如id索引)扫描, 拿到的是id, 取SELECT *的整行还要"回表"(去主键树找整行);
   - 一百万行就要一百万次回表 → 大量随机IO。

5. 深分页的本质问题:
   - "翻到很后面"这个需求, 和 "offset分页"的实现方式, 天然不匹配;
   - offset分页适合"翻前几页", 不适合"深翻"。

类比: LIMIT 1000000,20 像"想看一本书的第50001页, 却必须从第1页开始一页页翻过去、
   翻到第50000页全部跳过、只看第50001页那一页"——前面5万页全白翻了。

一句话: LIMIT offset要从头扫描到offset+size行再丢弃前offset行(数据库无法直接跳到第N行),
   代价随offset线性增长; SELECT *还要回表; 所以offset越大(深分页)越慢。

这套原理,是整个坑的根。LIMIT offset, size 的真实语义不是"直接定位到第 offset 行"(数据库做不到随机跳到第 N 行),而是"按顺序扫描、数出前 offset+size 行、丢弃前 offset 行、返回后 size 行"。为什么不能直接跳?数据/索引按 B+树顺序组织,要知道"第 100 万行是谁"只能从头数过去(没有第 N 行的直接地址),所以必须实际扫描经过前面所有行(哪怕马上丢弃)。于是代价随 offset 线性增长(O(offset)),而 SELECT * 还要回表(二级索引扫描拿到 id 后逐个回主键树取整行,一百万次回表 = 大量随机 IO)。就像想看书的第 50001 页却必须从第 1 页一页页翻过去、前 5 万页全白翻。一句话:LIMIT offset 要从头扫描到 offset+size 行再丢弃前 offset 行(数据库无法直接跳到第 N 行),代价随 offset 线性增长;SELECT * 还要回表;所以 offset 越大(深分页)越慢。

第二件事:正解——用游标(keyset)分页,或延迟关联,绕开大 offset

搞懂了原理,正解就清晰了:用"游标/keyset 分页"(记住上一页最后一条的 id,下一页 WHERE id > last_id)彻底绕开 offset;或用"延迟关联"先在索引上分页拿 id 再回表;深翻场景从产品上避免。

-- ====== 正解一(推荐): 游标/keyset 分页, 用 WHERE 替代 offset ======
-- 思路: 不用offset, 而是记住"上一页最后一条记录的id", 下一页从它之后开始取。

-- 第一页:
SELECT * FROM orders WHERE id > 0 ORDER BY id LIMIT 20;
-- 假设这页最后一条 id = 20, 客户端记住它;

-- 下一页: 用上一页最后的id做条件, 而不是offset
SELECT * FROM orders WHERE id > 20 ORDER BY id LIMIT 20;
-- 再下一页: WHERE id > 40 ... 以此类推

-- 翻到很后面也一样快:
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;
-- → id上有索引, WHERE id > 1000000 直接用索引【定位】到那个位置(B+树查找O(log n)),
--   再顺序取20条 → 不需要扫描+丢弃前面一百万行 → 无论翻到多后面都是毫秒级!

-- 优点: 性能恒定(不随页数增长), 是大数据量分页的标准解法(也叫"游标分页/seek method")。
-- 限制: 只能"上一页/下一页"顺序翻, 不能直接跳到"第5万页"(但深跳页本就是反模式)。

-- ====== 正解二: 延迟关联(deferred join), 减少回表 ======
-- 如果业务非要用offset(比如要支持跳页), 至少减少SELECT *的回表代价:
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 20   -- 先只在【索引】上分页拿id
) t ON o.id = t.id;
-- → 子查询只查id(走覆盖索引, 不回表), 扫描100万个id比扫描100万整行快得多;
--   最后只对要的20个id回表取整行 → 回表从100万次降到20次。

-- ====== 正解三: 从产品/接口设计上避免深分页 ======
-- 1. 限制最大页数(如最多翻到100页), 超过引导用户用"搜索/筛选"缩小范围;
-- 2. 导出/批处理用游标分页(按id顺序拉), 而不是offset翻页;
-- 3. "上一页/下一页"用游标分页, 不提供"直接跳到第N页"(或跳页用游标近似)。

-- ====== 配套: 只查需要的列, 别SELECT * ======
-- SELECT id, order_no, amount(只取要展示的列), 配合覆盖索引可避免回表。

-- 核心: 优先用游标/keyset分页(WHERE id>last_id, 性能恒定、绕开offset); 必须用offset就延迟关联
--   减少回表; 产品上限制深翻、导出用游标; 别SELECT *、善用覆盖索引。

修复的核心,是"用 WHERE 条件定位、绕开'扫描并丢弃'"。正解一(推荐):游标/keyset 分页——记住上一页最后一条的 id,下一页 WHERE id > last_id ORDER BY id LIMIT 20;id 上有索引,WHERE 直接用 B+树定位(O(log n))再顺序取 20 条,不扫描丢弃前面的行,无论翻多后面都是毫秒级、性能恒定;限制是只能顺序翻、不能跳页(但深跳页本就是反模式)。正解二:延迟关联——非要用 offset 就先在索引上分页拿 id(覆盖索引不回表)、最后只对 20 个 id 回表,回表从百万次降到 20 次。正解三:产品上避免深分页(限制最大页数、导出用游标、不提供跳到第 N 页)。配套别 SELECT *、用覆盖索引。归根结底:优先用游标分页(WHERE id>last_id,性能恒定);必须 offset 就延迟关联减少回表;产品上限制深翻;别 SELECT *。

第三件事:数据库分页与查询性能的其他常见坑

排查后我把分页和查询性能相关的其他常见坑也系统梳理了一遍。

分页与查询性能的其他常见坑

# 1. LIMIT offset深分页(本文): offset越大越慢。→ 游标/keyset分页。

# 2. SELECT * 回表: 取不需要的列、二级索引还要回表。→ 只查需要的列+覆盖索引。

# 3. count(*)算总数慢: 大表count每次全扫。→ 缓存总数/估算(SHOW TABLE STATUS)/不显示精确总数。

# 4. ORDER BY 没走索引: 排序字段无索引导致filesort。→ 排序字段建索引(配合分页)。

# 5. 分页排序字段不唯一: ORDER BY create_time(有重复)分页, 边界处可能漏/重。→ 加唯一字段(id)兜底。

# 6. N+1查询: 列表查N条, 再循环为每条查关联。→ 一次性JOIN/IN批量查。

# 7. 大IN列表: WHERE id IN (上万个), 可能慢/超长。→ 分批/临时表/JOIN。

# 8. 没有合适索引: 查询条件/排序/分组的字段缺索引, 全表扫。→ 按查询建合适索引。

# 共同根源: 很多查询在小数据量时都"够快", 性能问题只在【数据量大/翻得深/并发高】时暴露;
#   而很多写法(offset分页、SELECT *、N+1)的代价是随数据规模【增长】的, 早晚会撞墙。

# 核心: 写查询要考虑"数据量大了会怎样"; 深分页用游标、避免SELECT *和N+1、按查询建索引、
#   用EXPLAIN验证; 让查询的代价不随数据规模线性恶化, 是大数据量下性能的关键。

排查让我把分页性能的其他坑也梳理清了。一、LIMIT offset 深分页(本文)。二、SELECT * 回表(只查需要的列+覆盖索引)。三、count(*) 慢(缓存/估算)。四、ORDER BY 没走索引(filesort)。五、分页排序字段不唯一(加 id 兜底)。六、N+1 查询(JOIN/IN 批量)。七、大 IN 列表。八、没有合适索引。它们的共同根源是:很多查询在小数据量时都"够快",性能问题只在数据量大/翻得深/并发高时暴露;而很多写法(offset 分页、SELECT *、N+1)的代价是随数据规模增长的,早晚撞墙。核心是:写查询要考虑"数据量大了会怎样";深分页用游标、避免 SELECT * 和 N+1、按查询建索引、用 EXPLAIN 验证;让查询代价不随数据规模线性恶化。下面这张图,是这次深分页坑的成因与解法:

第四件事:offset 分页 vs 游标分页对比表

这次踩坑后,我把 offset 分页和游标分页的差异整理成一张表,选型时对照。

这张表把两种分页的取舍钉清了。核心是:offset 分页简单、能跳页,但深翻性能差;游标分页深翻性能恒定、翻页更稳,但只能顺序翻、不能跳页;没有绝对的"谁更好",只有"哪个更适配你的场景"——后台管理(要跳页、数据量小)用 offset,移动端无限滚动/大数据导出用游标。它给我的最大启发是:技术选型几乎从来不是"找一个最好的",而是"在一组各有优劣的方案里,根据你的具体约束做权衡"——offset 用"能跳页"换了"深翻慢",游标用"不能跳页"换了"性能恒定";每个方案都是一组 trade-off,关键是搞清你的场景最看重什么、最不能忍什么。这让我对做技术决策有了更成熟的认识:面对选型,与其问"哪个技术更好",不如问三个问题:"我的场景的核心约束是什么(性能?跳页?数据量?)""每个方案在这些约束上各是什么表现""哪个方案的'缺点'是我能接受的";"没有银弹,只有权衡"——理解每个方案"用什么换了什么",并匹配自己的场景,才是选型的正道。理解 offset 与游标分页的 trade-off、按场景约束做权衡选型——是这个坑带给我的技术决策认知。

第五件事:这个坑暴露的"小数据测不出大数据问题"

这次最该反思的,是它为什么没在开发测试阶段被发现。我把"小数据下隐藏、大数据下暴露"的性能坑整理成表。

这张表道出了一类最容易"漏网"的 bug。核心是:有一大类性能问题,在开发和测试的小数据量下"完全正常"、根本暴露不出来,只在生产的大数据量/高并发下才原形毕露(offset 深分页、N+1、全表扫、全量加载、O(n²));它们的共性是"代价随数据规模增长"——小规模时代价小到忽略、大规模时代价大到致命。它给我的深刻启发是:开发测试环境和生产环境之间,有一道"数据规模的鸿沟"——你在几十条测试数据上验证"没问题",完全不能保证它在生产几百万条数据上也没问题;"功能正确"和"规模下依然高效"是两个维度,前者小数据能测、后者必须考虑大数据。这让我养成了一个习惯:写任何"会随数据量增长"的代码(查询、循环、加载)时,都主动地在脑子里(或用真实数据量)做一次"规模推演"——问自己"当这里是 100 万条数据、而不是我测试的 10 条时,它会怎样?";有条件时,用接近生产规模的数据做性能测试,别让"小数据的绿灯"给你虚假的安全感。警惕小数据测不出大数据问题、对随规模增长的代码做规模推演——是这个深分页坑,从测试与设计层面教我的功课。

第六件事:做分页时,我现在的决策习惯

现在每当我要做一个分页,我都会按这张图先想清楚:

这张图的精髓,是"数据大就用游标,要跳页就限页数+延迟关联"。小数据/浅翻 offset 够用;大数据顺序翻用游标分页;大数据要跳页就限制最大页数+延迟关联;一律别 SELECT *、排序字段加索引。这套习惯,让我从"分页一律 offset"变成了"先想数据量和翻页方式"——核心始终是:分页要按数据量和翻页方式选方案,大数据深翻用游标,让代价不随页数增长。

我立下的几条规矩

这场"LIMIT offset 深分页拖垮数据库"的事故,换来了我做分页时,刻进骨子里的几条铁律:

1. LIMIT offset 的代价随 offset 线性增长。它从头扫描再丢弃前 offset 行。
2. 大数据/深翻优先用游标分页。WHERE id>last_id,索引定位,性能恒定。
3. 必须用 offset 就延迟关联减少回表。先索引上分页拿 id 再回表。
4. 产品上限制最大页数。深跳页本就是反模式,引导用筛选缩小范围。
5. 别 SELECT *,只查需要的列。配合覆盖索引避免回表。
6. 分页排序字段要唯一且有索引。否则边界处可能漏/重、还会 filesort。
7. 写随规模增长的代码先做规模推演。问"百万条时会怎样",用真实量测。

附:游标分页在多字段排序时怎么写

有同学问:游标分页很好,但我的列表不是按 id 排,而是按"创建时间倒序"排,游标怎么写?这其实是游标分页落地时最常见的进阶问题。

-- 按 create_time 倒序分页, 但 create_time 可能重复 → 要加 id 做"第二排序键"兜底唯一
-- 第一页:
SELECT * FROM orders ORDER BY create_time DESC, id DESC LIMIT 20;
-- 记住这页最后一条的 (create_time, id), 假设是 ('2026-06-01 10:00', 5000)

-- 下一页: 用 (create_time, id) 这个"复合游标"做条件
SELECT * FROM orders
WHERE (create_time < '2026-06-01 10:00')
   OR (create_time = '2026-06-01 10:00' AND id < 5000)   -- 时间相同时用id继续
ORDER BY create_time DESC, id DESC
LIMIT 20;
-- → 需要 (create_time, id) 的联合索引来支撑这个条件和排序;
--   用"复合游标"保证即使排序字段有重复, 也能精确、不漏不重地接着上一页翻。

这段展示了游标分页落地时的关键细节。核心是:游标分页的"游标"不一定是 id,而应该是"排序所依据的字段组合";当排序字段(如 create_time)可能重复时,必须追加一个唯一字段(如 id)作为第二排序键、并用"复合游标"条件(时间<X OR (时间=X AND id<Y))来翻页,才能保证不漏不重;同时需要相应的联合索引支撑。它给我的启发是:一个好的方案(游标分页),在落地到真实复杂场景(多字段、可重复排序)时,往往需要处理好"边界与细节"才能真正可靠——"大方向对"只是开始,"把重复值、排序唯一性、索引支撑这些细节都考虑周全"才是工程落地的真功夫;魔鬼在细节里,一个方案能不能在生产稳定跑,常常取决于这些容易被忽略的边界处理。把游标分页的复合游标、唯一性、索引等细节处理周全——是这个坑教我的落地功课。

写在最后

回头看,这场由"LIMIT offset 深分页"引发的、数据库被拖垮的事故,真正教给我的,远不止"深分页要用游标"这一个技巧。它让我对"一个看似 O(1) 的操作,底层的实现可能让它的真实代价随某个量线性增长",有了一次刻骨的体会。我栽跟头,根源在于我对 LIMIT offset 有一个关于"代价"的错误想象:我以为"查 20 条"的代价,就只和"20"有关——无论第 1 页还是第 5 万页,不都是"取 20 条"吗?代价该是一样的呀。可我完全没意识到:LIMIT offset, 20 的真实代价,主要不取决于你要的那"20",而取决于你跳过的那个 "offset"——它的底层实现决定了它必须"扫描并丢弃"前面所有行,于是代价随 offset 线性增长。我用"看起来要做的事(取 20 条)"去估算代价,却忽略了"它底层实际做的事(扫描并丢弃一百万行)"——这两者,差了十万八千里。这让我领悟到一个关于性能的深刻认知:一个操作的"表面语义"和它的"真实代价",常常不是一回事——你以为 list.contains() 是查一下、它其实是 O(n) 遍历;你以为字符串拼接是加一下、它其实每次都复制整个串;你以为 LIMIT offset 是取几条、它其实要扫描丢弃 offset 行;"它看起来在做什么"和"它底层实际花了多少代价",是两个必须分开看的问题。这给了我一种对性能的底层警觉:关心性能时,不能停留在操作的"表面语义",而要往下看一层、问"它底层到底做了什么、这个代价随什么量增长"——搞清楚每个关键操作的真实复杂度(是 O(1)、O(log n)、O(n) 还是 O(offset)),以及它会随哪个量恶化;"知道代价从哪来",才能预判性能瓶颈、写出在规模下依然高效的代码。看穿操作的表面语义与真实代价、搞清复杂度随什么量增长——这,是我用一次深分页拖垮数据库的事故,换来的、关于数据库、也关于如何理解一切性能问题的、最朴素也最深刻的领悟。如果这篇复盘,能让你下次写下 LIMIT offset 时,想一句"offset 大了它要扫描丢弃这么多行啊",转而用上游标分页,那我对着那条十几秒的查询排查的这段时间,就值了。

—— 别看了 · 2026