这个性能问题,有一个特别有意思的特征:它"越往后越慢"。我们一个数据列表接口,支持翻页查看,前几页快得飞起,几十毫秒就返回;可有用户反馈,翻到很后面的页(几百页、上千页)时,接口越来越慢,翻到几千页时甚至直接超时了。我一开始很困惑:同样是"查 20 条数据"啊,第 1 页查 20 条很快,第 5000 页也是查 20 条,凭什么就慢了几百倍、甚至超时?难道后面的数据有什么特别之处?
排查之后,真相指向了一个几乎人人都在用、却很少有人深究其代价的写法——用 LIMIT offset, size 做分页。我们第 5000 页的查询,写出来是 LIMIT 100000, 20(偏移 10 万、取 20 条)。我一直以为,数据库会"聪明地"直接跳到第 100000 行、然后取 20 条。可真相是:数据库做不到"直接跳过",它必须老老实实地从头扫描、数出前 100000 行,把它们一个个读出来、再统统丢弃,最后才取到你要的那 20 行。也就是说,你要第 5000 页那 20 条数据,数据库实际上付出了"读取并丢弃 10 万行"的代价!offset 越大,它白白扫描、再丢弃的行就越多,自然越来越慢——这就是臭名昭著的"深分页"性能问题。这篇文章,就从这次"翻页越翻越慢"的事故讲起,把 LIMIT offset 这个看似无害、实则在深分页时代价高昂的坑,以及它的正解,讲清楚。
故障现场:为了 20 条,白扫 10 万行
先把这个"白扫"的代价讲透。一个典型的分页查询是这样的:
-- 第 1 页: offset 0, 很快
SELECT * FROM orders ORDER BY id LIMIT 0, 20;
-- 第 5000 页: offset 100000, 慢得要死
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;
-- ↑ 数据库要先扫出前100000行再丢弃!
-- LIMIT offset, size 的真实执行逻辑:
-- 1. 从头开始, 按 ORDER BY 的顺序, 一行行地读
-- 2. 一直读到第 (offset + size) 行 (这里是 100020 行)
-- 3. 把前 offset 行 (前 100000 行) 全部丢弃
-- 4. 只返回最后的 size 行 (那 20 行)
-- 代价: 为了20行结果, 实际读取处理了 100020 行! offset越大越慢
看明白这个"白干"的过程了吗?LIMIT 100000, 20 的语义,不是"跳到第 100000 行取 20 个",而是"取出前 100020 行,然后扔掉前 100000 行"。数据库没有办法"凭空跳到"第 100000 行——因为它不知道第 100000 行具体在哪、除非它老老实实地从第 1 行开始一行行数过去。所以 offset 有多大,它"数着扔掉"的行就有多少。第 1 页 offset 是 0,不用扔,所以快;第 5000 页 offset 是 100000,要先读出并扔掉 10 万行,所以慢;翻到更后面,offset 更大,读了扔的更多,慢到超时也就不奇怪了。
这就完美解释了"越往后越慢"的现象:分页查询的耗时,不取决于你"取多少条"(size),而主要取决于你的偏移量"offset 有多大"——因为 offset 代表了数据库要白白扫描并丢弃的行数。同样是取 20 条,offset=0 几乎零成本,offset=100000 就要付出读取 10 万行的代价。LIMIT offset, size 这个写法,在 offset 小的时候(前几页)毫无问题、性能很好,可一旦 offset 变得很大(深分页),它的代价就会随 offset 线性飙升,最终拖垮查询。这个坑,几乎是所有"列表 + 翻页"功能都会埋下、却在数据量和翻页深度上来之前一直隐藏着的定时炸弹。
第一件事:理解 offset 的代价——它是"数着丢",不是"跳着取"
要避开这个坑,核心是纠正一个几乎人人都有的错误直觉:我们以为 LIMIT offset, size 里的 offset 是"跳过"(数据库直接定位到那个位置),但实际上它是"丢弃"(数据库先把前面的都读出来,再扔掉)。"跳过"是零成本的,而"读出来再丢弃"是有实实在在的扫描成本的。这一字之差,就是深分页性能问题的全部根源。
直觉(错): LIMIT 100000, 20 = 直接跳到第10万行, 取20条 (以为零成本)
真相(对): LIMIT 100000, 20 = 从头读出前100020行, 丢掉前100000行, 返回20条
↑ 那被丢掉的10万行, 是实实在在被读取、处理过的, 这就是成本
为什么数据库不能"直接跳过"?
因为它需要按 ORDER BY 的顺序定位到"第N行", 而"第N行在哪"这个信息,
普通的 offset 分页里, 只能靠"从头一行行数过去"才能知道。
→ offset 越大, 要数(并丢弃)的行越多, 成本越高
关键认知是:LIMIT offset, size 的成本,主要由 offset 决定,且随 offset 线性增长;offset 越大,数据库要"读取并丢弃"的行就越多,查询就越慢。所以,基于 offset 的分页,有一个根本的、无法回避的缺陷:它对"深分页"(翻到很后面的页)的支持是很差的、代价是高昂的。它在前几页表现优异,会给你一种"分页很快"的错觉,从而掩盖了这个隐患,直到某天数据量大了、有人翻到了很后面,炸弹才引爆。理解了"offset 是数着丢、不是跳着取"这个本质,你就明白了为什么深分页会慢,也就为找到正解指明了方向——我们需要一种能让数据库"真正跳过、而不是数着丢"的分页方式。
第二件事:正解——用"游标分页"让数据库真正跳过
正解的核心思路,是把"翻到第几页"(基于 offset 的位置),换成"从上次的位置继续往后取"(基于游标的位置)。具体做法是:记住上一页最后一条记录的排序值(比如它的 id),下一页查询时,用 WHERE id > 上次最后的id 来定位,再 LIMIT 20。这种方式叫"游标分页"(cursor / keyset / seek pagination)。
-- 反面: offset 深分页, 慢
SELECT * FROM orders ORDER BY id LIMIT 100000, 20; -- 白扫10万行
-- 正解: 游标分页, 用上一页最后的 id 作为"游标"
-- 第1页:
SELECT * FROM orders ORDER BY id LIMIT 20;
-- 记住这一页最后一条的 id, 假设是 1234
-- 下一页: 不用 offset, 而是从 id > 1234 开始取
SELECT * FROM orders WHERE id > 1234 ORDER BY id LIMIT 20;
-- ↑ 关键! id 上有索引, 数据库能直接定位到 1234 之后,
-- 真正地"跳过"前面的, 无需扫描丢弃 → 永远很快
这个方案为什么快?关键在那句 WHERE id > 1234:因为 id 上有索引(主键天然有索引),数据库可以利用索引的有序性,直接、高效地定位到 id 大于 1234 的第一条记录,然后顺着取 20 条就行——它是真正地"跳过"了前面的数据(靠索引定位),而不是"读出来再丢弃"。所以,无论你翻到多后面,这个查询的成本都是恒定的、很低的(始终是"定位 + 取 20 条"),再也不会随翻页变深而变慢。游标分页用"记住上次位置"替代了"计算偏移量",从而把"读取并丢弃 N 行"的线性成本,降成了"靠索引直接定位"的恒定成本——这就是它能根治深分页问题的根本原因。我把两种分页的成本差异画成图:
这张图把两种分页的本质区别摆得很清楚:offset 分页是"算位置"(从头数 offset 行),成本随深度线性增长;游标分页是"记位置"(记住上次到哪了,靠索引续上),成本恒定。这背后是一个很通用的优化思想——当"重复地从头计算位置"很昂贵时,想办法"记住上次的位置、从那里增量地继续",往往能把线性成本降为恒定成本。这种"用记忆替代重复计算"的思路,在很多性能优化场景里都用得上。
第三件事:游标分页的注意事项与权衡
游标分页虽好,但它不是免费的午餐,有几个使用上的注意事项和它做不到的事,得讲清楚,免得你用错。
-- 注意1: 排序字段必须"唯一且有索引", 否则游标会有歧义/遗漏
-- 若按 create_time 排序而它可能重复, 要加上 id 作为第二排序键保证唯一:
SELECT * FROM orders
WHERE (create_time, id) > ('2024-01-01 10:00:00', 1234)
ORDER BY create_time, id LIMIT 20;
-- 注意2: 游标分页"只能上一页/下一页"地顺序翻,
-- 无法直接"跳到第 5000 页"(因为它依赖上一页的游标)
-- 注意3: 用上一页的游标(id)往后查, 而不是页码;
-- 前端要传的是"上一页最后一条的id", 而非"页码"
游标分页的几个关键点:第一,排序字段要唯一且有索引——游标靠"上次最后一条的排序值"来定位,如果排序字段有重复值(比如按 create_time 排,而同一时刻有多条),游标就会产生歧义、可能漏数据或重复;解法是用一个"唯一的组合"来排序和定位(比如 (create_time, id),用 id 来打破并列)。第二,也是它最大的局限:游标分页只能"一页页顺序地往前/往后翻",没法"直接跳到第 N 页"——因为它依赖"上一页的游标",你不先翻到第 4999 页,就拿不到翻第 5000 页所需的游标。所以游标分页天然适合"无限滚动""下一页"这种顺序浏览的场景(像各种 App 的信息流),而不适合"需要随意跳页"的传统页码分页。这就引出了一个需要权衡的选型:offset 分页支持随意跳页但深分页慢,游标分页深分页飞快但不能跳页——选哪个,取决于你的产品到底需不需要"跳到任意页"这个功能。这个权衡,正是下一节要细说的。
第四件事:必须用页码分页时,怎么缓解深分页
如果你的产品确实需要"传统页码、能随意跳页"(比如后台管理系统),没法用游标分页,那也有一些办法能缓解(注意是缓解,不是根治)深分页的痛。
-- 缓解1: 延迟关联(子查询先用覆盖索引定位主键, 再回表取数据)
-- 思路: 先只在索引上做深分页拿到那20个id(快), 再用id去取完整行
SELECT * FROM orders o
JOIN (
SELECT id FROM orders ORDER BY id LIMIT 100000, 20 -- 只查id, 走覆盖索引, 快很多
) t ON o.id = t.id;
-- 原理: "丢弃10万行"这步只在窄窄的索引上做(只有id), 比丢弃整行快得多
-- 缓解2: 业务上限制最大可翻页数(很多产品都这么干)
-- "最多只能看前100页", 超过的引导用户用搜索/筛选缩小范围
-- 因为: 真有用户会手动翻到第5000页吗? 深分页往往是不合理的需求
-- 缓解3: 如果是导出全部数据, 别用分页, 用游标流式导出
这几个缓解办法各有适用:"延迟关联"是个聪明的优化——它把"丢弃 offset 行"这个昂贵操作,限制在"只含主键的窄索引"上做(子查询 SELECT id ... LIMIT 100000, 20 只扫索引、不回表取整行),拿到那 20 个 id 后,再用 id 精准地取完整数据;因为在窄索引上"读了又丢"比在整行上快得多,所以能显著缓解。"限制最大页数"则是个朴素却极其有效的产品手段——很多产品都规定"最多翻 100 页",因为说实话,真的会有用户手动一页页翻到第 5000 页吗?深分页的需求,本身往往就是不合理的;与其费劲优化一个没人真正需要的极端场景,不如从产品上限制它、引导用户用"搜索、筛选"来缩小范围、快速定位到他想要的数据(这才是用户翻到深处的真实意图)。把几种分页方案的特性列成一张表对比:
| 方案 | 深分页性能 | 能跳任意页吗 | 适用场景 |
|---|---|---|---|
| offset 分页(裸用) | 差(随深度线性变慢) | 能 | 数据少、不会深翻 |
| 游标分页 | 极好(恒定) | 不能(只能顺翻) | 无限滚动、信息流、导出 |
| offset + 延迟关联 | 较好(缓解) | 能 | 必须页码跳页的后台 |
| offset + 限制最大页 | 规避(不让翻太深) | 能(但有上限) | 大多数有页码的产品 |
第五件事:怎么选——先问"产品到底需要什么"
讲完了所有方案,到底怎么选?我的体会是:分页方案的选型,技术只是一半,另一半在于先想清楚"产品到底需要怎样的翻页体验"。因为不同方案,本质上是在"深分页性能"和"能否随意跳页"之间做权衡,而这个权衡的答案,藏在你的产品需求里。我把选型的决策依据整理成一张表:
| 产品场景 | 翻页需求 | 推荐方案 |
|---|---|---|
| App 信息流 / 评论 / 消息 | 下拉加载更多, 顺序浏览 | 游标分页(性能最优) |
| C端商品列表 | 很少深翻, 主要靠搜索筛选 | 游标 或 offset+限页 |
| 后台管理系统列表 | 要显示总页数、跳任意页 | offset+延迟关联+限页 |
| 数据全量导出 | 遍历全部, 不需跳页 | 游标分页流式导出 |
| 数据量小(几千条内) | 怎么翻都不慢 | offset 裸用即可, 别过度优化 |
这张表传达的核心,是"方案服务于需求,而非反过来":你得先搞清楚用户到底怎么用这个列表——是像刷信息流那样不停往下滚(那游标分页完美),还是真的需要看到"共 5000 页"并跳到任意一页(那只能 offset,再用延迟关联和限页去缓解),还是数据量本就不大、根本不会慢(那就用最简单的 offset,别没事找事去过度优化)。最后这一点尤其想强调:不要在数据量很小、根本不会触发深分页的场景里,过度设计、强上游标分页——那只会徒增复杂度。性能优化的前提,永远是"这里真的会成为瓶颈";对一个总共才几千条数据的列表,offset 分页怎么翻都快,上游标分页就是杀鸡用牛刀。先评估数据规模和真实的翻页行为,再决定用不用、用哪种优化——这才是务实的工程态度。
一张"分页方案怎么选"的决策图
把这次踩坑沉淀成一张图。每次你要做一个带翻页的列表时,照着它走一遍:
这张图的两个关键判断:先问"会不会深分页"(数据量小就别折腾),再问"需不需要跳任意页"(不需要就上游标分页、需要就用 offset+延迟关联+限页缓解)。把这两问理清,分页方案就选对了。深分页这个坑,与其等它在生产爆发再补救,不如在设计列表功能的那一刻,就照这张图想清楚。
我立下的几条分页规矩
这次"翻页越翻越慢"的事故后,团队的规范里加了这么几条:
- 警惕大 offset 深分页:LIMIT offset 的成本随 offset 线性增长,数据量大时深分页会拖垮查询,设计列表时就要预判。
- 顺序浏览优先游标分页:无限滚动、信息流、下拉加载等场景,用基于游标(WHERE id > last_id)的分页,深分页恒定快。
- 游标排序字段要唯一有索引:游标分页的排序字段必须唯一(否则用 id 打破并列)且有索引,保证定位准确高效。
- 页码跳页用延迟关联缓解:必须支持跳任意页时,用子查询先在索引上拿主键再回表,缓解深分页。
- 产品上限制最大页数:深翻往往是伪需求,限制最大可翻页数,引导用户用搜索筛选,既提性能又改善体验。
- 导出用游标流式:全量遍历/导出别用 offset 分页一页页拉,用游标流式遍历。
- 数据量小别过度优化:几千条内的列表,offset 裸用怎么翻都快,别强上游标分页徒增复杂度。
这几条里,第五条"产品上限制最大页数"是我觉得最有"四两拨千斤"之妙的。我们做技术的,遇到性能问题,本能反应是"从技术上把它优化掉";可这次让我意识到:有些性能问题,与其从技术上硬啃,不如回到产品层面问一句——"这个场景,真的有必要存在吗?"真的会有用户,一页一页地、手动翻到第 5000 页去找数据吗?几乎不会。一个想看很后面数据的用户,他的真实意图,几乎一定是"找某个特定的东西",而满足这个意图的正确方式是"搜索、筛选",而不是"翻几千页"。所以,限制最大页数、引导用户去搜索,非但不是"功能阉割",反而是更贴合用户真实需求的设计——它同时解决了性能问题和体验问题。这让我学到:面对一个棘手的技术难题,先别急着埋头硬啃,退一步想想"这个需求本身合理吗、有没有更好的产品路径绕过它"——有时候,最好的技术方案,是不用技术方案。
写在最后:别想当然地相信"它会很聪明"
这次被深分页坑到的经历,留给我一个反复出现、却总被忽视的教训:我们太容易"想当然地相信底层系统会很聪明地帮我们优化",而忽略了去了解它"实际上是怎么干活的"。我那次,就是想当然地以为"LIMIT 100000, 20 嘛,数据库肯定会聪明地直接跳到第 10 万行",却从没想过它其实笨拙地"从头数了 10 万行再扔掉"。这个美好的、却错误的假设,让我对深分页的性能代价毫无察觉,直到它在生产里爆发。我们对工具,常常抱有一种过高的、未经核实的期待,以为它无所不能、总会用最优的方式工作;而真相往往是,它只是忠实地、按它的实现机制去执行,而那个机制,未必如你想象的那般"聪明"。
想通这一点,我对"了解工具的实际行为"这件事,有了更强的自觉。用任何一个工具(数据库、框架、语言特性),都别停留在"我会用它的语法"这个层面,而要往下多问一层:"我这么用,它在底层实际上是怎么执行的?代价有多大?"——尤其是在性能敏感的地方。LIMIT offset 你会写,但你知不知道它 offset 大时要白扫多少行?索引你会建,但你知不知道什么写法会让它失效?这些"实际行为",才是决定你的代码快还是慢、稳还是崩的关键,而它们,往往藏在你"想当然"的那层美好假设之下。把"它会很聪明"的想当然,换成"我去搞清楚它实际怎么干"的求证,你就能在性能问题爆发之前,提前看见那些埋着的雷。
所以,如果你也在和数据库、和各种底层系统打交道,我想把这次踩坑最想说的话送给你:请对你写的每一个性能敏感的操作,都保有一份"它实际上是怎么执行的"的好奇与求证,别轻易地假设"它会聪明地优化"。 写下一个 LIMIT offset 时,想想它 offset 大了会怎样;写下一个看似简单的查询时,EXPLAIN 一下看它真实的执行计划。因为你和性能事故之间的距离,常常就是一个"我以为它很聪明"的想当然;而拉开这个距离的,正是你愿意往下多看一眼、去搞懂它"实际怎么干"的那份较真。那个翻到第 5000 页就超时的列表,最终教给我的,正是这份对"工具实际行为"的较真——它让我从此不再天真地把"我会用"等同于"我懂它",而是养成了在每个性能关口,都去亲自确认"它到底是怎么干的"这个习惯。而这个习惯,值千金。
—— 别看了 · 2026