有个数据列表接口,支持翻页,用的是最朴素的写法:SELECT * FROM orders ORDER BY id LIMIT ?, ?,前端传页码,后端算出 offset。刚上线时,数据量小,翻哪页都飞快。可随着数据涨到几百万行,用户(以及爬虫、导出任务)开始翻到很后面的页时,接口就慢得离谱——翻第一页几毫秒,翻到第一万页要几秒,翻到几十万页直接超时。诡异的是,每页明明都只取 20 条数据,数据量一模一样,凭什么翻到后面就这么慢?
我把那条慢查询拿去 EXPLAIN,再结合数据库的工作原理一想,真相就清楚了:问题出在 LIMIT offset, count 这个分页写法的底层机制上。很多人以为 LIMIT 1000000, 20 是数据库直接跳到第 100 万行、取 20 条,可实际上数据库做的是——从头开始,老老实实地扫描、并丢弃前面的 1000000 行,然后才取出第 1000001 到 1000020 这 20 行返回给你。也就是说,offset 有多大,数据库就要白白扫描并丢弃多少行。翻第一页 offset 是 0 不用丢,翻到第一万页 offset 是 20 万就要先扫描丢弃 20 万行,翻到几十万页要丢弃的行数大到让数据库不堪重负——这就是越翻到后面越慢的根源。
这就是数据库分页里一个极其经典、又极易被忽视的性能陷阱:深分页(deep pagination)——用 LIMIT offset, N 翻到很大的 offset 时,性能会随 offset 线性恶化,直至超时。它在小数据量、浅翻页时毫无征兆,却会在数据量增长、有人翻到深页时,突然暴露成一个严重的性能问题。这篇文章,就从这次"翻到后面页就超时"的事故出发,把深分页的成因、以及高性能分页的正确姿势,一次讲透。
先摆几个关于分页的想当然
动手复盘前,先把我自己曾经深信、后来被深分页教育的几个念头摆出来。
| 想当然的念头 | 残酷的真相 |
|---|---|
| "LIMIT 1000000,20 是直接跳到第100万行取20条" | 它要从头扫描并丢弃前 100 万行, 极慢 |
| "每页都只取 20 条, 翻哪页都一样快" | offset 越大, 要扫描丢弃的行越多, 越翻越慢 |
| "有索引了, 分页就快了" | 深分页即便走索引, 也要扫描跳过大量索引行 |
| "分页就 limit offset, 没别的写法" | 有游标分页等更高效、不随深度恶化的方案 |
| "小数据量没问题, 不用管" | 数据一涨、有人翻深页, 立刻暴露成超时事故 |
这些念头的共同病根,是想当然地以为 LIMIT offset, N 是一个"精准跳转、代价恒定"的操作,却不知道它在底层是"从头扫描、逐行丢弃"的——代价随 offset 线性增长。要看清这次事故,得先理解 LIMIT offset 到底是怎么执行的。
第一件事:LIMIT offset 的真相——扫描并丢弃前 offset 行
理解深分页慢的关键,是搞懂 LIMIT offset, count 的执行机制。数据库在执行它时,并不能"直接定位到第 offset+1 行"——因为它不知道"第 offset 行"具体在磁盘的哪个位置(行与行之间没有这样的页码索引)。它能做的,是按顺序(沿着索引或全表)从第一行开始读,一行一行地数,数到第 offset 行之前的都读出来又丢弃掉,数够了 offset 行之后,才开始真正收集接下来的 count 行返回。
所以,LIMIT 1000000, 20 的真实代价,不是"取 20 行",而是"读取并丢弃 1000000 行 + 取 20 行"。那 100 万行的读取和丢弃,全是白白的、纯浪费的工作——它们既不会出现在结果里,却实实在在地消耗了大量的 IO 和 CPU。这就是为什么 offset 越大越慢:慢的不是"取那 20 条",而是"为了到达那 20 条,前面要白扫、白丢的那一大片"。下面这张图,把浅分页和深分页的代价对比画出来:
看懂这张图,事故的根就清楚了:深分页的性能问题,源于 LIMIT offset 那个"必须从头扫描、丢弃前 offset 行"的固有机制——它的代价不取决于你"取多少",而取决于你"从多深的地方取"。翻得越深,白白浪费的扫描就越多。明白了这一点,优化的方向也就清晰了:想办法避免那个"从头扫描丢弃"的过程,让数据库能直接、高效地定位到目标位置。接下来,我们就看怎么做到。
第二件事:首选方案——游标分页(基于上次位置)
解决深分页最根本、最高效的办法,是游标分页(cursor / seek pagination),也叫"基于上次位置的分页"。它的核心思路是:不再用"第几页(offset)"来定位,而是用"上一页最后一条记录的位置(比如它的 id)"来定位——下一页就是"id 大于上一页最后那个 id 的、接下来的 N 条"。因为有索引,数据库能直接通过索引定位到那个 id,然后顺着往后取 N 条,完全不需要扫描、丢弃前面的任何行!
-- 反例:深分页, offset 越大越慢
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20; -- 要丢弃 100 万行
-- 正解:游标分页, 用上一页最后一条的 id 定位, 不随深度变慢
-- 第一页:
SELECT * FROM orders ORDER BY id LIMIT 20;
-- 假设第一页最后一条 id = 20, 取下一页:
SELECT * FROM orders WHERE id > 20 ORDER BY id LIMIT 20;
-- 再下一页(假设上页最后 id = 40):
SELECT * FROM orders WHERE id > 40 ORDER BY id LIMIT 20;
-- 关键:WHERE id > ? 能走索引直接定位, 无论翻到多深, 都一样快!
游标分页的精妙之处,在于它把"翻页"从"按页码偏移"变成了"按上次位置接着取",从而彻底消除了 offset 那个"扫描丢弃"的代价——无论你翻到第 1 页还是第 100 万页,数据库都是"用索引直接定位到上次的位置、再往后取 N 条",性能恒定、丝般顺滑。这也是为什么所有大型系统的"无限滚动""下一页"(微博、朋友圈那种流式列表),底层几乎都用的是游标分页,而不是 limit offset。
当然,游标分页有它的取舍:它天然适合"上一页/下一页"的连续翻页和无限滚动,但不支持"直接跳到第 N 页"(因为它依赖"上一页的位置")。所以它特别适合 feed 流、时间线这种"用户只会一直往下翻"的场景;而对那种"必须支持随意跳页"的传统页码导航,就需要别的办法。但在绝大多数现代产品里,无限滚动早已取代了页码跳转——所以游标分页,应该是你处理列表分页的首选;只有在确实需要随意跳页时,才退而求其次。
第三件事:必须用页码跳页?那就用"延迟关联"优化
如果业务确实需要支持"跳到第 N 页"(比如后台管理系统的表格),没法用游标分页,那么有一个能显著优化深分页的技巧:延迟关联(deferred join,或叫覆盖索引优化)。它的思路是:深分页慢,很大一部分代价在于"扫描丢弃前面 offset 行"时,每行都要回表读取完整数据(SELECT *)。而我们可以先只在索引上做分页、只取出那 20 行的主键 id(这一步因为只读索引、不回表,快得多),再用这 20 个 id 去关联查出完整数据。
-- 反例:深分页 + SELECT *, 扫描丢弃的每行都回表读完整数据, 极慢
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- 正解:延迟关联——先在索引上分页只取 id, 再用 id 关联取完整数据
SELECT o.* FROM orders o
INNER JOIN (
-- 子查询:只在主键索引上分页, 只取 id, 不回表, 快得多
SELECT id FROM orders ORDER BY id LIMIT 1000000, 20
) AS t ON o.id = t.id;
-- 子查询扫描的是"窄"的索引(只有 id), 丢弃 100 万行的代价小很多;
-- 最后只对真正需要的 20 行做回表取完整数据
延迟关联的优化效果,来自于"把'扫描丢弃大量行'这个昂贵的过程,从'在完整数据上做'变成了'只在窄窄的索引上做'"——扫描丢弃 100 万行索引项,比扫描丢弃 100 万行完整数据(还要回表)要快得多。它不能让深分页变得像浅分页一样快(offset 大的固有代价还在),但能显著缓解。这是在"必须支持跳页"约束下的一个务实优化。
此外,如果分页的排序字段是自增、连续的 id,还可以用一个更取巧的办法:直接用 WHERE id > 上一页起始id 来近似定位(本质上是游标分页的变体)。但如果 id 不连续(有删除),这个就不精确了。总之,在"必须跳页"的约束下,延迟关联是通用的缓解手段,而能否结合业务用上游标分页的变体,则要看数据特点。
第四件事:别忘了那个慢吞吞的 COUNT(*)
分页接口除了查"这一页的数据",通常还要查"总共有多少条"(算总页数用)。很多人用 SELECT COUNT(*) FROM orders WHERE ... 来取总数——可在大表上,COUNT(*) 本身也可能很慢,因为它往往要扫描大量行来计数。于是一个分页接口的慢,可能不全是深分页,也有一半是这个 COUNT 拖的。
-- 大表上的 COUNT(*) 可能很慢, 尤其带复杂 WHERE 条件时
SELECT COUNT(*) FROM orders WHERE status = 1; -- 可能要扫很多行
-- 优化思路:
-- 1. 如果只是要"大概多少", 用估算值代替精确 COUNT(MySQL 可查 EXPLAIN 的 rows
-- 或 information_schema 里的近似行数), 快得多
-- 2. 维护一个计数缓存:用 Redis 等单独存"总数", 增删时更新, 查询时直读缓存
-- 3. 产品上弱化"总页数":很多场景(尤其无限滚动)根本不需要显示总条数/总页数
-- 只需要"有没有下一页"——多查一条看看够不够 N+1 即可
这里有个很实用的产品视角:很多时候,"显示精确的总条数/总页数"这个需求本身,就值得商榷。对于无限滚动的列表,用户根本不关心"总共多少页",只关心"还有没有下一页"——而判断"有没有下一页",只需要每次多查一条(查 N+1 条,如果查到了第 N+1 条,说明还有下一页),根本不需要昂贵的 COUNT。所以优化分页,有时不只是优化 SQL,更是反思"这个分页到底需不需要总数"这个产品设计——去掉一个不必要的 COUNT,可能比优化 SQL 本身收益更大。
第五件事:给分页深度设上限,挡住恶意/无意的深翻
还有一个务实的防御手段:给分页的深度设一个合理的上限。想一想:正常的真人用户,真的会一页页手动翻到第 10 万页吗?几乎不会。那些把 offset 翻到天文数字的请求,绝大多数来自爬虫、恶意扫描、或写错的程序。所以,在接口层面限制"最多翻到第 X 页"(或 offset 不能超过某个值),既能挡住这类异常请求对数据库的冲击,又几乎不影响真实用户的体验。
// 给分页深度设上限, 挡住异常的深翻请求
public PageResult list(int page, int size) {
int MAX_PAGE = 1000; // 最多允许翻到第 1000 页
if (page > MAX_PAGE) {
// 超过上限, 拒绝或引导用户用搜索/筛选缩小范围
throw new BizException("翻页过深, 请用搜索或筛选条件缩小范围");
}
// ... 正常分页逻辑
}
// 思路:真人不会翻到几十万页, 限制深度能挡住爬虫/异常, 保护数据库
// 对真正需要遍历全量数据的(如导出), 走专门的游标分页/批处理通道
这个限制背后的思想是:用业务上的合理约束,去消除技术上的极端代价。"翻到第 100 万页"在技术上是个昂贵的操作,而在业务上又是个几乎不会发生的真实需求——既然如此,直接从产品/接口层面禁掉它,就是最简单有效的防护。当然,如果确实有"遍历全部数据"的合理需求(比如数据导出、离线分析),那就不该走这个面向用户的分页接口,而应该走专门的、基于游标的全量遍历通道(每次 WHERE id > 上次最大id 取一批,直到取完),那才是高效遍历海量数据的正确方式。
第六件事:换个存储?搜索/聚合场景的分页
最后拓宽一下视野:如果你的分页伴随着复杂的搜索、过滤、聚合(而不只是简单地按 id 翻),那么纯靠关系型数据库优化分页可能力不从心,这时该考虑用更适合的工具。比如 Elasticsearch 这类搜索引擎,就是为海量数据的搜索和分页而生的——不过要注意,它也有自己的深分页限制(默认 from+size 不能超过 1 万,深分页要用它的 search_after,这恰恰也是游标分页的思想!)。
不同场景的分页选型:
- 简单列表、连续翻页/无限滚动 → 游标分页(WHERE id > ? LIMIT N), 首选
- 必须支持跳到任意页 → 延迟关联优化 + 限制最大深度
- 复杂搜索/过滤/聚合 → 用 Elasticsearch 等搜索引擎(深分页用 search_after)
- 全量数据遍历/导出 → 专门的游标遍历通道, 别用面向用户的分页接口
- 总数展示 → 能不要就不要; 要的话用估算值或计数缓存
核心规律:无论哪种存储, "基于上次位置的游标分页"都是应对深分页的通用解药
有意思的是,你会发现无论是关系型数据库、还是 Elasticsearch,它们应对深分页的"终极方案",殊途同归地都是"游标分页(基于上次位置)"——ES 的 search_after 和 MySQL 的 WHERE id > ? 是完全一样的思想。这不是巧合,而是因为"用上次的位置直接定位、避免从头扫描丢弃"是应对深分页这个问题在原理上唯一高效的路径。理解了这个底层规律,你换什么存储都能举一反三。到这儿,分页的方方面面就齐了。我把选型思路收成一张决策图:
把这套理解建立起来,深分页这类性能问题就能被预防和根治。最后,拧成几条可直接照做的铁律:
- 牢记 LIMIT offset 是"扫描丢弃前 offset 行",代价随 offset 线性增长, 深分页必慢。
- 首选游标分页(WHERE id > 上次位置),用索引直接定位, 翻多深都一样快。
- 必须跳页就用延迟关联,先在索引上分页取 id, 再关联取完整数据。
- 给分页深度设上限,真人不会翻到几十万页, 挡住爬虫/异常请求。
- 警惕慢 COUNT(*),能不显示总数就别显示, 或用估算值/计数缓存。
- 全量遍历走专门的游标通道,别用面向用户的分页接口去导出海量数据。
- 复杂搜索分页换搜索引擎,但深分页同样要用 search_after 这种游标思想。
一张分页方案速查表
把各种分页方案的特点和适用场景汇成一张表,做分页设计时对照着选。
| 方案 | 性能 | 能跳页? | 适用 |
|---|---|---|---|
| LIMIT offset, N(传统) | 深分页随 offset 恶化 | 能 | 仅小数据量/浅翻页 |
| 游标分页(WHERE id 大于 上次) | 恒定快, 不随深度变 | 不能(只能上下页) | 无限滚动/feed 流, 首选 |
| 延迟关联(子查询取 id) | 显著缓解深分页 | 能 | 必须跳页的后台表格 |
| 限制最大深度 | 挡住极端深翻 | 限制内能跳 | 配合上面方案的防御 |
| ES + search_after | 恒定快 | 不能(游标式) | 复杂搜索/聚合的分页 |
| 专门游标遍历通道 | 高效遍历全量 | — | 数据导出/离线处理 |
更深一层:同一个需求,代价天差地别
这次事故让我对"性能"有了一个更深的体悟:同样一个看似简单的需求("翻页"),用不同的实现方式,性能可以有天壤之别——而这个差别,往往不在于"代码写得精不精巧",而在于"你对底层机制理解得深不深"。"翻到第 100 万页取 20 条"这个需求,用 LIMIT offset 实现,代价是扫描丢弃 100 万行;用游标分页实现,代价是用索引定位一次。同样的结果,前者可能超时,后者几毫秒——差距,完全来自于"你知不知道 LIMIT offset 底层在干什么"。
这就是"理解底层"的价值在性能领域最直观的体现:很多严重的性能问题,根本不需要什么高深的优化技巧去解决,只需要你理解了底层机制之后,换一种"顺应底层、而非对抗底层"的写法。LIMIT offset 慢,是因为它的写法逼着数据库做"从头扫描丢弃"这种它不擅长、代价高昂的事;而游标分页快,是因为它的写法让数据库做"用索引直接定位"这种它最擅长、代价极低的事。写出高性能代码的关键,常常不是"用更复杂的技巧",而是"理解底层喜欢什么、不喜欢什么,然后顺着它的脾性来写"。这一点,在数据库、在任何系统的性能优化里,都反复地成立。
而且这个坑还有一个典型的"温水煮青蛙"特征:它在小数据量、浅翻页时完全无害,让你毫无警觉地把这个写法带到了生产;直到数据量增长、有人翻到深页,它才突然暴露成一个严重问题。这提醒我们:写代码时,不能只考虑"现在的数据量",更要考虑"数据量增长 10 倍、100 倍后,这段代码还撑得住吗"。那些"现在没问题、将来会爆"的隐患,正是这类性能事故的温床——而对底层机制的理解,能让你在写下代码的那一刻,就预见到它在未来大数据量下的命运。
写在最后
这次"翻到后面页就超时"的事故,看似只是一个具体的数据库分页问题,却让我对"性能"这件事的本质,有了更通透的认识。它给我最深的一个启示是:性能问题,绝大多数时候不是"算法不够聪明",而是"做了大量不必要的、白白浪费的工作"。深分页慢,慢在那扫描丢弃的 100 万行——那是 100 万行纯粹的、不产生任何价值的浪费。而所谓"优化",本质上往往就是"识别出这些被白白浪费的工作,然后用一种聪明的方式把它们省掉"——游标分页之所以快,正是因为它把那 100 万行的无效扫描,彻底省掉了。看清了"浪费在哪",优化的方向也就有了。
而看清"浪费在哪"的能力,又回到了这个系列反复强调的那个内核:对底层机制的理解。如果你不知道 LIMIT offset 底层是"扫描丢弃",你就永远意识不到那 100 万行的浪费,也就永远想不到游标分页这个解法——你可能会去加机器、加缓存、调参数,在各种"表面优化"上打转,却始终碰不到问题的根。可一旦你理解了它的底层,解法几乎是不言自明的。这就是为什么我始终相信,对一个工程师而言,最值得投入的,不是去记忆海量的"优化技巧清单",而是去深入理解你所用的每一个工具(数据库、语言、框架)的底层运作原理——因为正是这份理解,让你能在性能问题面前,一眼看穿"浪费在哪",从而以最简单、最根本的方式解决它。这次深分页给我上的这一课,我会记很久:慢,往往是因为做了不必要的功;而快,常常只需要你理解底层后,别再让它做那些功。愿你我都能修炼出这双"看穿浪费"的眼睛,让自己写下的每一段代码,都既跑得对,又跑得在底层的脾性之上、顺畅而高效。
—— 别看了 · 2026