MySQL深分页性能优化：从OFFSET到Keyset游标分页实战-平芜编程栈

深度分页这个话题，凡是写过两年以上 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

注意rows=1000010，这不是巧合，就是 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;

这里没有 OFFSET，MySQL 借助主键索引的 B+ 树直接定位到id=10086，然后往小的一侧顺序扫描 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只访问主键 id，InnoDB 可以走覆盖索引扫描，全程不碰数据页。等它数出那 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 就得 kill：

SELECT 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 的逻辑虽然能保证结果正确，但深分页的坑一个不少，选型时一定要把中间件的分页逻辑纳入评估。