深度分页这个话题凡是写过两年以上 SQL 的人基本都踩过坑。SELECT * FROM orders ORDER BY id DESC LIMIT 1000000, 10——这条 SQL 看起来人畜无害逻辑上也没错就是从第 100 万条之后取 10 条。可真要是在线上这么跑一次轻则接口超时重则数据库 CPU 打满、连接池被拖垮整个业务跟着一起雪崩。我见过不止一个团队因为这种深度分页的写法在深夜拉起了告警群。今天不绕弯子直接拆解 LIMIT 深分页为什么会致命、执行计划背后的真实成本以及生产环境里到底该怎么选型。1. 从执行计划看真相OFFSET 分页的成本是线性增长的1.1 一条 LIMIT 到底在服务器内部做了什么很多人以为LIMIT 1000000, 10的意思是找到第 100 万行然后往后拿 10 行就像编程语言里用数组下标访问元素一样是 O(1) 的操作。但数据库里根本没有行号指针这种物理概念。InnoDB 的数据保存在 B 树里引擎并不知道第 1000000 行具体在哪个页、哪个槽位上。于是它只能老老实实从头开始数沿着索引或者全表扫描的路子一条一条读取符合条件的记录前 100 万条读完直接丢弃不回传给客户端等数到第 1000001 条才开始收集凑满 10 条才结束返回。这个从第一条数到指定位置的过程就是 OFFSET 的真正含义。所以对一个 2000 万行的表来说LIMIT 1000000, 10实际读取的行数至少是 1000010 行只多不少。数据量越大、页码越深读的越多。成本跟 offset 的大小严格线性相关没有任何取巧空间。PostgreSQL 的OFFSET ... FETCH、SQL Server 的OFFSET ... FETCH本质上也一样谁也别笑谁。1.2 用 EXPLAIN 验证扫描行数不会骗你我在测试库建了一张 500 万行的订单表把create_time故意不加索引模拟一下线上最常见的裸奔写法EXPLAIN SELECT * FROM orders ORDER BY create_time DESC LIMIT 1000000, 10;执行计划里的关键信息长这样id | select_type | table | type | possible_keys | key | rows | Extra 1 | SIMPLE | orders | ALL | NULL | NULL| 1000010 | Using filesort注意rows1000010这不是巧合就是 MySQL 优化器估算出来的要跳过 100 万行再取 10 行的代价。Using filesort说明排序也没走索引得先全表扫描、再做文件排序最后才能应用 LIMIT。如果你在生产库上打开慢查询日志long_query_time设成 1 秒这种 SQL 几乎一抓一个准。真正可怕的是这类语句往往不是只跑一次——管理后台有人点一下第 10 万页或者某个导出任务循环分批拉数据瞬间就是十几个这样的查询同时打进来。1.3 数字推演100 万 OFFSET 的真实代价我们来算一笔账。假设订单表每行记录平均占用 400 字节全表扫描 100 万行光是把这些数据页从磁盘读进内存就是几 GB 级别的 IO 流量。即便数据都在 buffer pool 里处理 100 万行记录、再做一次 filesort 排序的 CPU 开销也够受的。如果走的是二级索引比如ORDER BY create_time且 create_time 有索引情况也未必更好如果是SELECT *每扫到一条索引记录都要回表取完整行这意味着 100 万次随机 IO。机械硬盘就别提了即使是 SSD几十万次随机页读取也足以让这条查询跑到几十秒甚至分钟级。更隐蔽的是连带伤害。慢 SQL 长时间占用 CPU、IO 和内存其他正常查询全得排队。在写并发比较高的场景下事务的执行时间被拉长行锁、间隙锁的持有时间跟着变长死锁概率明显上升。我见过一个订单系统就是这么挂的白天业务高峰期一条后台报表的深分页查询耗光了 CPU前台用户下单接口全部超时最后是 DBA 手动 kill 掉慢查询才缓过来。2. 为什么加了索引依然是治标不治本2.1 索引帮你定位单行但帮不了你跳行面对深分页很多人的第一反应是给排序字段加索引不就行了。这个思路对了一半。B 树索引确实能把单行查找从 O(N) 降到 O(log N)但它只解决查到某一个具体值的问题解决不了数到第 100 万条的问题。原因在于要确定第 1000001 条是哪条数据库必须沿着索引的叶子节点从头往后遍历、逐条计数。这个遍历过程是 O(N) 的索引帮不上忙。你可以在脑海里把 B 树叶子节点想象成一条排好队的长龙索引结构能让你瞬间找到队伍里的某个人但想知道第 100 万个是谁你还是得从队头开始一个一个数过去。所以即使create_time上有索引ORDER BY create_time DESC LIMIT 1000000, 10依然要遍历 100 万个索引条目一条都省不了。2.2 回表的随机 IO比全表扫描更隐蔽的杀手更麻烦的是回表。如果索引是(create_time)而查询是SELECT *那么每扫到一条索引记录InnoDB 都要根据主键再去聚簇索引里取完整行。这是一次随机 IO。也就是说100 万次索引遍历 100 万次回表随机读。这个组合在某些硬件条件下比全表顺序扫描还慢因为顺序扫描可以预读、可以批量读页而随机 IO 完全打乱磁盘的访问模式。MySQL 优化器往往会通过成本估算意识到这一点于是干脆放弃索引直接选择全表扫描 filesort就像我们在 1.2 节看到的那样。那有没有办法不回表有只要让查询只访问索引里的列就行也就是覆盖索引。比如只查SELECT id FROM orders ORDER BY create_time LIMIT 1000000, 10配合(create_time, id)联合索引就能做到Using index全程只扫索引页、不回表。但问题来了业务要的是整行数据只拿 id 不够。这就引出了后面要讲的延迟关联。2.3 filesort 和临时表当排序无法走索引时的灾难还有一种常见场景WHERE status 1 ORDER BY create_time LIMIT ...。如果status和create_time上各有一个独立索引MySQL 只能选其中一个另一个就可能导致 filesort。MySQL 的 filesort 会把待排序的数据放进sort_buffer_size指定的内存区域放不下就分批写到磁盘上用归并排序。对于LIMIT 1000000, 10这种语句优化器需要的东西比想象中更夸张它得知道前 100 万条是谁才能决定最后 10 条从哪开始。虽然 MySQL 对ORDER BY ... LIMIT有优先队列优化但维护一个容量达到 100 万的堆内存照样爆最后还是得落盘。磁盘临时表 归并排序 100 万条记录这种组合跑出来的延迟足够让用户的请求在网关层超时重试。你如果接过第三方 API应该对exceeded retry limit或者429 too many requests这种报错不陌生——线上深分页拖垮数据库之后上游网关和服务端重试机制会把雪崩效应进一步放大客户端每重试一次数据库就多挨一次打。3. 真正适配深分页的方案Keyset游标分页实战3.1 从翻页到滚动核心思路就一句话与其每次从头数到第 100 万条不如记住上一页最后一条记录的位置下一次直接从那个位置接着往后取。这就是 Keyset 分页也叫游标分页、Seek 分页。核心逻辑一句话不要跳行要滚动。以最简单的自增主键为例。上一页最后一条记录的id 10086那么下一页就是SELECT * FROM orders WHERE id 10086 ORDER BY id DESC LIMIT 10;这里没有 OFFSETMySQL 借助主键索引的 B 树直接定位到id10086然后往小的一侧顺序扫描 10 条即可。不管数据累积到 2000 万还是 2 亿每一页查询的代价都恒定在 O(limit)跟翻了多少页毫无关系。这就是它跟 OFFSET 分页最本质的区别。3.2 单字段游标与复合游标的 SQL 写法如果排序字段是唯一的比如主键直接用单字段游标就行。但很多时候排序字段并不唯一比如ORDER BY create_time DESC同一秒内可能插入多行光凭create_time ?会漏数据。正确的做法是排序字段 主键组成复合游标。假设上一页最后一行是create_time 2024-03-01 12:00:00且id 10086下一页查询可以这么写SELECT * FROM posts WHERE (create_time, id) (2024-03-01 12:00:00, 10086) ORDER BY create_time DESC, id DESC LIMIT 20;MySQL 5.7 支持行构造器的这种写法。如果项目用的版本比较老或者中间件不支持就拆成等价的展开形式SELECT * FROM posts WHERE create_time 2024-03-01 12:00:00 OR (create_time 2024-03-01 12:00:00 AND id 10086) ORDER BY create_time DESC, id DESC LIMIT 20;注意一个关键前提必须在(create_time, id)上建联合索引而且字段顺序必须是先排序字段、再唯一字段。这样 MySQL 才能用上这个索引做范围扫描同时天然保证排序有序不需要 filesort。后端接口的写法也简单把游标参数从page/pageSize换成cursor/size。前端把上一页最后一条的create_time和id原样传回来即可一般会封装成一个不透明字符串避免前端直接操作内部字段。3.3 游标分页的代价不能跳页以及怎么跟产品沟通Keyset 分页最大的缺点是不支持跳页。你没法从第 1 页直接跳到第 500 页因为每一页的查询条件都依赖上一页的游标。这在传统管理后台里是个硬伤——运营人员经常说我就要看第 300 页到底有什么。但大多数情况下这种非看第 300 页不可的需求都是伪需求。用户的真实意图是想找到一个时间段的订单或者看看有没有异常数据。这些用筛选条件、时间范围搜索完全可以覆盖。我一般会跟产品这样对齐面向 C 端的内容流、信息流天然是加载更多或下拉刷新的模式直接上 Keyset体验更好。面向内部运营的表格如果数据量可控比如筛选后总量不超过几千传统分页完全够不需要过度设计。如果筛完还是几十万行那就让产品接受只提供前 N 页 条件筛选的交互而不是无限翻页。记住一个原则分页方案是给业务形态服务的不是技术栈自嗨。产品经理愿意改交互你的技术选型空间就大得多。4. 深分页的辅助救援手段覆盖索引、延迟关联与缓存兜底4.1 覆盖索引 延迟关联让回表只发生在最后一页有些业务实在改不动必须保留跳页能力数据库层面也不是完全没救。最实用的优化叫延迟关联Deferred Join核心思路是先把深分页的扫描动作限制在索引上最后只对真正需要的那 10 行回表。SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY id LIMIT 1000000, 10 ) tmp ON o.id tmp.id;子查询SELECT id FROM orders ORDER BY id LIMIT 1000000, 10只访问主键 idInnoDB 可以走覆盖索引扫描全程不碰数据页。等它数出那 10 个 id 之后外层 JOIN 再对这 10 行回表取完整数据。这样回表次数从 100 万次锐减到 10 次查询耗时有数量级的下降。它没有消除数 100 万条索引记录的成本但 Index Only Scan 的成本比扫描 回表低了不止一个量级很多深分页场景靠这一招就能从分钟级降到秒级。如果有 WHERE 条件比如WHERE status 1 ORDER BY id那就在(status, id)上建联合索引子查询写成SELECT id FROM orders WHERE status 1 ORDER BY id LIMIT ...同样走覆盖索引。4.2 预计算分页快照与缓存兜底如果既要保留跳页又不想让数据库扛压力可以考虑预计算分页边界快照。思路是这样的用后台任务提前把整个排序结果扫描一遍把每一页的第一条记录游标存下来比如存到一张小表或者 Redis 里。用户请求第 300 页时直接从快照里拿到第 300 页的起始游标再用 Keyset 查询查出这一页的 20 条数据。page_300_start (create_time: 2024-02-01 10:00:00, id: 99999) SELECT * FROM posts WHERE (create_time, id) (2024-02-01 10:00:00, 99999) ORDER BY create_time DESC, id DESC LIMIT 20;这种方案的查询成本几乎恒定跳页能力也有代价是快照数据会过期——新增数据、删除数据都会让页码对应的内容漂移。所以它只适合对一致性要求不高的场景比如热度榜、推荐列表这种允许轻微变化的页面。生成快照还有一笔后台成本通常放在业务低峰期跑。更轻量的办法是给热门页做缓存。大多数列表的访问热度都集中在前几十页用 Redis 缓存前 100 页的 id 列表命中就返回没命中再查库。这样深分页的请求根本落不到数据库上。4.3 兜底策略限制翻页深度比任何优化都有效我发现一个很有意思的现象很多团队愿意花好几个通宵优化深分页 SQL却不愿意在代码里加两行限制。其实最有效的兜底策略就是业务逻辑上拒绝过深的翻页。比如在 ORM 查询层统一加一个约定offset limit超过 20000 就抛异常提示用户数据量过大请使用筛选条件缩小范围。管理后台可以限制只提供前 500 页页码组件最多渲染到 500再往后提示使用时间范围搜索。很多网站早就这么干了你翻电商订单、翻搜索引擎结果翻到很深的时候都会看到没有更多了或者直接引导你去筛选。这种限制在数据库层面就拦截了绝大多数深分页请求配合延迟关联、缓存等手段生产环境基本不会被打挂。看似简单粗暴实际是最低成本的止损方案。5. 工程化兜底熔断、限流与分页方案选型清单5.1 慢查询监控与止损技术方案讲完聊点工程层面的细节。首先是监控务必把慢查询日志打开long_query_time设置成 1 秒并定期用pt-query-digest之类的工具分析 Top SQL。深分页问题最典型的特征就是某条 SQL 的Rows_examined远大于Rows_sent比例可能高达十万比一。这种 SQL 迟早出事早发现早处理。止损手段也要预备好。MySQL 支持在单条查询上设置执行超时SELECT /* MAX_EXECUTION_TIME(3000) */ * FROM orders ORDER BY create_time DESC LIMIT 1000000, 10;超时 3 秒直接报错返回不让它无限拖垮数据库。应用层连接池也要设置查询超时时间比如 JDBC 的socketTimeout、Druid 连接池的queryTimeout防止慢 SQL 长期占用连接导致连接池耗尽。一旦连接池满了上游调用方拿不到连接会疯狂报错重试甚至触发网关层的429 too many requests瞬间把雪崩传到整个服务集群。线上遇到正在执行的深分页大查询该 kill 就得 killSELECT id, time, info FROM information_schema.processlist WHERE state executing ORDER BY time DESC; KILL thread_id;平时演练一下别等到凌晨 3 点告警响了才开始查命令。5.2 分页方案选型对照表最后把几种方案的适用场景列个表方便直接抄作业方案深层页查询成本支持跳页改造量适用场景传统 OFFSET 分页随页码线性增长支持无数据量小、翻页浅、筛选后结果集可控Keyset 游标分页恒定 O(limit)不支持中C 端流式列表、加载更多、海量数据覆盖索引 延迟关联线性但回表量大减支持低必须保留跳页的深分页数据量大预计算分页快照接近恒定支持高榜单、热帖等允许轻微数据漂移的场景限制翻页深度从不触发深分页部分支持低管理后台、搜索类业务搜索引擎 search_after恒定不支持高全文检索、日志检索另外提醒一句如果你接入了 ShardingSphere 这类分库分表中间件GROUP BY、ORDER BY、LIMIT往往会被中间件改写下发到每个分片再把所有分片的结果汇聚重排。这种场景下深分页的成本还要乘上分片数问题会被放大好几倍。中间件改写 LIMIT 的逻辑虽然能保证结果正确但深分页的坑一个不少选型时一定要把中间件的分页逻辑纳入评估。5.3 一点个人体会做数据库性能优化这么多年我的体感是深分页问题很少是SQL 写错了这么简单它背后往往是数据量增长超出了当初的设计预期加业务交互没有限制翻页深度两个因素共同作用。单点技术手段能缓解真正根治要在产品交互和技术选型两端同时发力。我自己的习惯是新项目设计列表接口时先问三个问题——数据规模会不会超过十万级用户需不需要任意跳页筛选条件能不能有效收敛数据量这三个问题答完分页方案基本就确定了。别等线上挂了再回头补课那会儿要付出的代价比现在多写几十行代码贵得多。