列表页前几页飞快、翻到几千页后接口直接超时,我用 LIMIT 一百万逗号二十去查那一页才发现数据库默默扫描并丢弃了前一百万行:一次深度分页性能塌陷的深度复盘

那个性能问题是用户投诉"翻到后面页面就一直转圈、最后报错"才暴露的:我做了个数据列表页,支持翻页,后端分页用的是最常见的写法——SELECT * FROM orders ORDER BY id LIMIT #{offset}, #{size}。前几页飞快,没人有意见;可一旦用户翻到很靠后的页(比如第几千页),接口就慢得令人发指、甚至直接超时 500。我把那条慢 SQL 抓出来一看,offset 已经到了百万级:LIMIT 1000000, 20。我一开始还纳闷:我明明只要 20 条啊,怎么会这么慢?直到我用 EXPLAIN 分析、把 MySQL 的执行过程看清,才恍然,后背发凉:LIMIT 1000000, 20 这个写法,数据库的执行方式不是"直接跳到第 1000000 行、取 20 条",而是"从头开始,老老实实地扫描、并丢弃前 1000000 行,然后才取出接下来的 20 行"。也就是说,为了拿到我想要的那 20 条,数据库实际读取并处理了 1000020 行,然后把前面 1000000 行全扔掉;offset 越大,它要"白扫并丢弃"的行就越多,所以越翻到后面越慢——这就是经典的"深度分页(deep pagination)"性能陷阱。问题的根,是 LIMIT offset, size 的 offset 不是"免费跳过",而是"扫描并丢弃";offset 一大,代价就线性飙升。这篇就把这次"深度分页性能塌陷"的坑,从头到尾复盘一遍。

故障现场:LIMIT 一百万逗号二十,慢到超时

问题在于用 LIMIT offset, size 分页,大 offset 时数据库要扫描并丢弃海量行:

-- ✗ 出问题的分页写法: LIMIT offset, size
SELECT * FROM orders
ORDER BY id
LIMIT 1000000, 20;     -- ✗ 翻到第5万页(每页20条): offset=1000000

-- 现象: 前几页(offset小)飞快; offset到百万级时, 这条SQL慢到几秒甚至超时。

-- 用 EXPLAIN 看, 会发现它扫描了海量的行(rows≈1000020), 而不是只碰20行。

-- 为什么这么慢? LIMIT offset, size 的真实执行方式:
-- - 它【不是】"直接定位到第offset行, 取size条";
-- - 而是: 从符合条件的数据【从头开始】, 一行一行地读、计数,
--   读到第 offset 行之前的都【读出来又丢弃】(只是为了"数够offset个"),
--   数够 offset 后, 才把接下来的 size 行真正返回;
-- - → 为了 LIMIT 1000000, 20, 数据库实际读取/处理了 1000020 行, 丢弃前 1000000 行!

-- 关键点: offset 不是"免费的跳过", 而是"要付出扫描+丢弃成本的略过";
--   即使 ORDER BY id 用了主键索引, 它仍要沿着索引扫过前 offset 行(不能凭空跳到第offset行)。

-- 更糟: 若 ORDER BY 的列没索引, 还要先全表排序, 雪上加霜。
-- 还有: SELECT * 会回表取整行, 进一步加重(下面正解会提到覆盖索引)。

-- 对比: 第1页 LIMIT 0, 20 → 只读20行, 飞快;
--       第5万页 LIMIT 1000000, 20 → 读100万+行再丢弃, 极慢。
--       同样是要20条, 代价天差地别 —— 这就是"深度分页"陷阱。

-- 关键: LIMIT offset, size 的 offset 是"扫描并丢弃前offset行", 不是"免费跳过";
--       offset越大要白扫的行越多, 故列表越翻到后面越慢 → 深度分页性能塌陷。

第一次看懂"原来 offset 是要一行行扫过来再丢掉的"时,我又懊恼又意外:"我一直以为 LIMIT 1000000, 20 就是'翻书翻到第 1000000 页直接看',压根没想到它是'从第一页一页页数到一百万页、数过的全撕掉'。"这个坑最隐蔽的地方在于:它前几页完全正常(offset 小,扫描少),问题只在 offset 大了才暴露;而开发/测试时很少有人去翻到几千页,所以开发期几乎发现不了,一旦线上有用户/爬虫翻到深处,就塌陷。而且它不报错,只是越来越慢,直到超时。下面就来拆解,分页该怎么写才不会随页数塌陷。

第一件事:搞懂深度分页为什么会塌陷

我顺着这次事故,把 LIMIT offset, size 的代价模型彻底理清了。

深度分页(大offset)为什么越翻越慢?

【核心: LIMIT offset,size 的 offset 是"扫描并丢弃前offset行"而非"免费跳过"; offset越大白扫的行越多, 代价随offset线性增长】

1. LIMIT offset, size 的真实语义:
   - 不是 "跳到第offset行, 取size条"(那需要"随机定位到第N行", 数据库做不到凭空跳);
   - 而是 "从头按顺序扫, 数过offset行(扫出来丢掉), 再取接下来size行返回";
   - → 实际处理的行数 ≈ offset + size, 不是 size。

2. 为什么不能"直接跳到第offset行"?
   - 数据(或索引)是按顺序组织的, "第offset行"这个位置, 数据库不知道它的物理位置;
   - 它只能"从头沿着顺序数过去"才知道哪是第offset行 → 必须扫过前面所有行。
   - (即便走主键索引, 也是"沿索引顺序扫过offset个", 而非O(1)跳转。)

3. 代价模型:
   - 第1页 offset=0: 处理 size 行 → 快;
   - 第N页 offset=(N-1)*size: 处理 (N-1)*size + size 行 → 随页数N线性增长;
   - 翻到很深(offset百万级): 处理百万行只为返回几十行 → 极慢/超时。

4. 雪上加霜的因素:
   - ORDER BY的列无索引 → 还要先排序(全表/临时表排序), 更慢;
   - SELECT * → 每行都要回表取完整数据(即便最终丢弃), 加重IO;
   - 数据量越大、翻得越深, 塌陷越明显。

5. 为什么开发期发现不了:
   - 测试数据量小、且测试很少翻到几千页 → offset一直很小 → 一直很快;
   - 上线后数据量大、有用户/爬虫翻深页 → offset暴涨 → 突然塌陷。

6. 本质: offset分页的代价, 取决于"你要跳过多少", 而不是"你要取多少";
   - 真正的浪费, 是"为了到达目标位置, 而扫描+丢弃的那一大片数据"。

一句话: LIMIT offset,size 的 offset 不是免费跳过, 而是"从头扫描并丢弃前offset行", 实际处理offset+size行;
   offset越大代价越高(线性增长), 故深度分页越翻越慢; 解法是避免大offset(改用游标/键集分页)。

这套认知,是整个坑的根。LIMIT offset, size 的真实语义:不是"跳到第 offset 行取 size 条",而是"从头按顺序扫,数过 offset 行(扫出来丢掉),再取接下来 size 行",实际处理 offset+size 行。为什么不能直接跳:数据库不知道"第 offset 行"的物理位置,只能从头沿顺序数过去(即便走索引也是沿索引扫过 offset 个,非 O(1) 跳转)。代价模型:第 N 页处理 (N-1)*size+size 行,随页数线性增长,翻到百万级 offset 就极慢/超时。雪上加霜:ORDER BY 无索引要排序、SELECT * 要回表。为什么开发期发现不了:测试数据少、很少翻深页,offset 一直小、一直快;上线数据大、有人翻深页就塌陷。本质:offset 分页的代价取决于"你要跳过多少",而非"你要取多少";浪费在为到达目标位置而扫描+丢弃的那一大片数据。一句话:LIMIT offset,size 的 offset 不是免费跳过,而是从头扫描并丢弃前 offset 行,实际处理 offset+size 行;offset 越大代价越高,故深度分页越翻越慢;解法是避免大 offset。

第二件事:正解——游标/键集分页(WHERE id > last_id),避免大 offset

搞懂了原理,正解就清晰了:不要用大 offset 翻页,改用"游标/键集分页(keyset pagination)"——记住上一页最后一条的 id,下一页用 WHERE id > last_id LIMIT size 直接定位,不再扫描丢弃。

-- ====== 正解一: 游标/键集分页(keyset pagination, 最推荐) ======
-- 第一页(没有上一页的id):
SELECT * FROM orders
ORDER BY id
LIMIT 20;
-- 记住本页最后一条的 id, 比如 last_id = 20;

-- 下一页: 用 WHERE id > last_id 直接定位, 不用offset!
SELECT * FROM orders
WHERE id > 1000000          -- ★ 上一页最后一条的id; 走主键索引直接定位到这里
ORDER BY id
LIMIT 20;                    -- ✓ 只需沿索引读20行, 不管翻到多深都一样快!
-- → 关键: WHERE id > last_id 能用主键索引【直接定位】到那个位置, 然后顺序读20行;
--   不再"扫描并丢弃前offset行" → 第1页和第100万页一样快(O(size), 与页深无关)。

-- ====== 正解二: 用覆盖索引"延迟回表"优化(若必须用offset) ======
-- 先用覆盖索引(只查id)走完offset, 再用id关联取整行, 减少回表:
SELECT o.* FROM orders o
JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 20  -- 子查询只查id, 走索引, 不回表
) t ON o.id = t.id;
-- → 子查询扫的是"窄"的索引(只id), 比SELECT *扫整行快; 但仍是offset, 治标不治本, 不如游标分页。

# ====== 游标分页的注意点 ======
# 1. 排序列要"唯一且有索引": 用主键id最理想(唯一、有索引、顺序);
#    若按非唯一列(如create_time)排序, 要加上id做第二排序键防止漏/重:
#    WHERE (create_time, id) > (:last_time, :last_id) ORDER BY create_time, id LIMIT 20;
# 2. 游标分页的限制: 只能"上一页/下一页"顺序翻, 不能"直接跳到第N页"(因为它靠"上一条的id");
#    → 但绝大多数列表(信息流、无限滚动)本就只需"加载更多", 完全够用。
# 3. 真要"跳到任意页码": 考虑①限制最大页数; ②用搜索引擎(ES)的search_after; ③业务上避免深翻。

# ====== 其他配套手段 ======
# - 限制可翻的最大页数/最大offset(如最多翻100页), 拒绝病态的深度分页请求;
# - 列表改"无限滚动/加载更多"(天然适合游标分页), 而非传统页码;
# - 大数据导出别用分页循环, 用流式/按id区间分批拉取。

# 核心: 避免大offset是根本解 —— 用游标/键集分页(WHERE id > last_id LIMIT size), 靠索引直接定位,
#   做到"翻多深都一样快(与页深无关)"; offset分页只适合浅翻; 必要时限制最大页数或改无限滚动。

修复的核心,是"别用大 offset 扫描丢弃,改用游标分页靠索引直接定位"。正解一:游标/键集分页(最推荐)——记住上一页最后一条的 id,下一页用 WHERE id > last_id ORDER BY id LIMIT size,走主键索引直接定位、只读 size 行,第 1 页和第 100 万页一样快(与页深无关)。正解二:覆盖索引延迟回表(若必须 offset,子查询只查 id 走索引、再 join 取整行,减少回表;但治标不治本)。注意点:排序列要唯一且有索引(用主键 id 最理想);非唯一列排序要加 id 做第二排序键;游标分页只能顺序翻不能跳页(但信息流/无限滚动本就够用)。配套:限制最大页数、列表改无限滚动、大数据导出用按 id 区间分批。归根结底:避免大 offset 是根本解——用游标/键集分页(WHERE id > last_id LIMIT size)靠索引直接定位,做到翻多深都一样快;offset 分页只适合浅翻;必要时限制最大页数或改无限滚动。

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

排查后我把数据库分页、查询性能相关的其他坑也系统梳理了一遍。

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

# 1. 深度分页大offset(本文): 扫描丢弃海量行。→ 游标/键集分页。

# 2. SELECT * 回表取全列: 多余IO, 用不上覆盖索引。→ 只查需要的列。

# 3. ORDER BY 列无索引: 触发filesort全量排序。→ 给排序列建索引。

# 4. count(*)总数慢: 大表统计总数慢。→ 缓存总数/近似值/去掉总页数(改无限滚动)。

# 5. LIKE '%xxx' 前导通配符: 用不上索引, 全表扫。→ 改后缀通配/全文索引/搜索引擎。

# 6. 隐式类型转换/函数包裹索引列: 索引失效(同342篇)。→ 类型一致、别在索引列上套函数。

# 7. IN (海量值)/OR 过多: 可能不走索引或很慢。→ 控制数量/改join/临时表。

# 8. 没有合适的复合索引: 查询条件命不中索引最左前缀。→ 按查询设计复合索引。

# 共同根源: 数据库的性能, 极度依赖"查询能否高效地用索引定位到目标数据, 而不是扫描大量无关数据";
#   很多慢查询的本质都是"为了拿到少量结果, 却扫描/处理了海量数据"(深度分页是其典型)。

# 核心: 优化查询的核心是"减少扫描的数据量"——让查询走索引精准定位、只取需要的列、避免大offset/全表扫/filesort;
#   时刻问"这条SQL为了返回这点数据, 实际扫了多少行?"(EXPLAIN看rows), 把"扫描量"压下来。

排查让我把分页与查询性能的其他坑也梳理清了。一、深度分页大 offset(本文)。二、SELECT * 回表。三、ORDER BY 列无索引触发 filesort。四、count(*) 总数慢。五、LIKE '%xxx' 前导通配符。六、隐式类型转换/函数包裹索引列。七、IN 海量值/OR 过多。八、没有合适的复合索引。它们的共同根源是:数据库的性能极度依赖"查询能否高效地用索引定位到目标数据,而不是扫描大量无关数据";很多慢查询的本质都是"为了拿到少量结果,却扫描/处理了海量数据"(深度分页是其典型)。核心是:优化查询的核心是"减少扫描的数据量"——让查询走索引精准定位、只取需要的列、避免大 offset/全表扫/filesort;时刻问"这条 SQL 为了返回这点数据,实际扫了多少行?"(EXPLAIN 看 rows),把扫描量压下来。下面这张图,是这次深度分页坑的成因与解法:

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

这次踩坑后,我把"offset 分页"和"游标/键集分页"的关键区别对比成一张表。

这张表把两种分页钉清了。核心是:两者的根本差异在于"如何到达目标位置"——offset 分页靠"从头数过去"(代价随距离增长),游标分页靠"索引直接定位到上次的位置"(代价恒定);这就像"从书第一页一页页翻到第 500 页" vs "用书签直接翻到上次读到的地方"。它给我的最大启发是:"记住上次到了哪(游标/书签)",几乎总是比"每次都从头数到目标位置(offset)"更高效——前者是 O(1) 的"接着上次继续",后者是 O(N) 的"每次重新走一遍到目标";这个"用一个'位置标记'记住进度、下次从标记处直接继续"的思想,在很多地方都能省下大量重复工作(增量同步记 last_sync_time、断点续传记 offset、流式消费记 consumer offset)。这给了我一种处理"连续/分批访问"的清醒:每当我要"分批/连续地处理一个大序列"时,都优先考虑"能不能记住一个'进度标记', 下次从标记处直接续上",而非"每次从头定位到本批的位置"——前者随处理进度恒定开销, 后者开销越来越大;"用游标/进度标记接续, 而非每次从头跳转",是高效处理大数据集分批访问的一个核心模式。认清记住进度比每次从头数更高效、用游标接续而非反复从头跳转——是这个坑带给我的认知。

第五件事:这次事故暴露的"小数据下不显、大数据下塌陷"的陷阱

这次让我反思更深一层:这个坑之所以能溜到线上,是因为它在"开发期的小数据/浅翻"下完全不显形。我把"小规模 vs 大规模"下的表现差异整理成表。

这张表道出了这类坑的可怕之处。核心是:这个坑属于一类特别危险的问题——"在小规模下完全不显形、随规模增长才逐渐恶化乃至塌陷"的问题;它骗过了开发和测试(那时数据小、offset 小,一切都快),却在线上数据涨上来、有人翻深页时突然爆发;这类"规模相关的性能问题",是最容易"带病上线"的。它给我的深刻启发是:评估一段代码/一个设计时,不能只看"它现在(小规模下)跑得怎么样",更要看"它的代价, 是怎么随规模(数据量、用户量、并发量)增长的"——是 O(1)/O(logN) 的优雅增长,还是 O(N)/O(N²) 的随规模塌陷?;"小数据下快"是一种廉价的、有欺骗性的"好";"大数据下依然不塌陷"才是真正的、值得信赖的"好"。这给了我一种工程上的远见:写涉及"会增长的数据/会被反复调用"的代码时,要主动地用"规模放大十倍、百倍"的眼光去审视它——"当数据从一千变成一千万, 这段逻辑会怎样?这个 offset 会变多大?这个循环会扫多少?";"用'规模增长'的视角提前审视代价曲线、而非只看当下小数据的表现",是写出能扛得住增长、不会在某天突然塌陷的系统的关键远见。认清小数据下不显大数据下塌陷的陷阱、用规模增长的视角审视代价曲线——是这个深度分页坑带给我的工程态度。

第六件事:写分页查询时,我现在的自检习惯

现在每当我要写一个分页查询,我都会先按这张图问自己:

这张图的精髓,是"数据会大就别用大 offset,优先游标分页"。小数据浅翻offset 够用、大数据信息流用游标分页、要页码跳转限制最大页数+覆盖索引或搜索引擎,任何分页都确认 ORDER BY 有索引、别 SELECT *。这套习惯,让我从"分页一律 LIMIT offset,size"变成了"先看数据规模和翻页方式、深翻就用游标"——核心始终是:避免大 offset,数据量大/会被深翻就用游标分页(WHERE id > last_id LIMIT size)靠索引直接定位,做到翻多深都一样快。

我立下的几条规矩

这场"翻到深页接口超时塌陷"的事故,换来了我写数据库分页时,刻进骨子里的几条铁律:

1. LIMIT offset, size 的 offset 不是免费跳过,是扫描并丢弃前 offset 行。实际处理 offset+size 行。
2. offset 越大越慢,深度分页随页深线性塌陷。这是 offset 分页的固有缺陷。
3. 数据量大/会被深翻,用游标分页:WHERE id > last_id ORDER BY id LIMIT size。恒定快。
4. 游标分页的排序列要唯一且有索引(用主键最佳)。非唯一列要加 id 做第二排序键。
5. 必须支持页码跳转时,限制最大页数或用覆盖索引延迟回表。别放任无限深翻。
6. 分页查询确认 ORDER BY 列有索引、别 SELECT *。减少排序和回表。
7. 用"规模放大百倍"的眼光审视查询的代价曲线。小数据快不代表大数据不塌陷。

写在最后

回头看,这场由"一个最常见的 LIMIT offset 分页写法"引发的、翻到深页就塌陷的事故,真正教给我的,远不止"用游标分页代替大 offset"这一个技巧。它让我对"一个东西'当下、小规模下'表现良好, 完全不能保证它'未来、大规模下'依然良好; 真正的健壮, 要经得起'规模'的考验",有了一次刻骨的体会。我栽跟头,是因为我写下那个 LIMIT offset, size 时,它在我眼前(开发环境、小数据、翻前几页)表现得'完美无缺'——快、简单、能用,我没有任何理由怀疑它。可我忽略了:它的'好',是建立在'offset 一直很小'这个我没意识到的隐含前提上的;当线上的数据涨到百万、当有用户/爬虫翻到几千页,这个前提崩了,它的代价就顺着 offset 一路飙升,从'飞快'塌陷成'超时';我看到的'它很好',只是'它在我测试的那个小小范围里很好'。这让我领悟到一个关于"当下表现与规模韧性"的深刻认知:评判一个方案的好坏,有两个截然不同的维度——"它当下/小规模下表现如何"和"它的代价/复杂度随规模如何增长";前者是"此刻的快照",后者是"未来的趋势";一个"当下很快、但代价随规模线性/指数增长"的方案,是一颗'定时炸弹'——它一定会在规模到达某个点时引爆;而"真正可靠的方案, 是那些代价随规模优雅增长(O(1)/O(logN))、能陪你走过十倍百倍增长"的。这给了我一种工程师的远见:做任何"会面对增长"的设计/编码时,都要把目光从"它现在好不好用"抬高到"它能不能陪我的数据/用户一起长大, 而不在某个规模点突然塌陷"——主动识别那些"代价随规模恶化"的写法(深度分页、N+1、全表扫、笛卡尔积、内存里攒全量),在它们还没引爆前就换成'规模无关'的方案;"不只为当下的规模而设计, 更为未来的规模而设计",是构建能长期扛住增长的系统的核心远见。认清当下表现不等于规模韧性、为未来的规模而非只为当下而设计——这,是我用一次深度分页塌陷的事故,换来的、关于数据库、也关于如何构建经得起规模增长的系统的、最朴素也最深刻的领悟。如果这篇复盘,能让你下次写分页、面对会增长的数据时,先停下来想想"翻到很深会怎样"、换上游标分页,那我对着那条 LIMIT 1000000, 20 的慢查询排查的这段时间,就值了。

—— 别看了 · 2026