接手过一个后台项目，客户反馈非常一致：列表页翻得越深，响应越慢，到第 1000 页左右基本已经卡到让人抓狂。查了下监控，一条很普通的订单分页查询，耗时从最初的 30ms 飙到了 5 秒以上。SQL 长这样：

SELECT * FROM t_order WHERE shop_id = 123 AND status = 'PAID' ORDER BY id DESC LIMIT 100000, 20;

这就是典型的 MySQL 深分页问题。LIMIT 100000, 20 看起来只是“从第 100000 行开始取 20 行”，但 MySQL 实际执行时并不会直接跳过去，而是一行一行数过去、丢掉前面十万行，再把剩下的 20 行返回给你。服务端扫了一大堆无用数据，响应自然越来越慢。

本文我会按一条排查主线来写：先讲清楚深分页到底慢在哪，再说是怎么在生产环境定位到它的，接着对比几种常规优化手段，最后重点展开一套在电商、SaaS 后台里验证过多次的游标分页方案，以及各种边边角角的坑。

1. 深分页的根因剖析：LIMIT 与 OFFSET 的真实执行路径

1.1 偏移量分页的假象：不是“跳到第 N 行”，而是“先扫再扔”

很多开发者对 OFFSET 的理解是错的，以为 MySQL 能通过某种偏移计算直接定位到数据文件的第 100000 行，然后从那里开始读。实际上，MySQL 在处理 LIMIT 100000, 20 时，执行路径大致是：

1. 根据 WHERE 条件从索引或表中找到所有匹配的行；
2. 如果要排序，先按 ORDER BY 字段排序；
3. 从头开始数，数到 100000 行之后，再取 20 行返回。

这里最关键的是第二步和第三步。MySQL 没有办法“跳过”前面的 100000 行，它只能把匹配的行一条一条读出来、判断是否在窗口之外、然后丢弃。扫描的成本是 offset + limit，也就是至少 100020 行，而不是 20 行。所以深分页本质上是用几十倍甚至几万倍的工作量去换取一页数据。

用生活里的例子类比：你去自助餐厅取餐，假设菜台是一排很长的桌子，你要拿第 1000 道菜，但必须从第一道菜开始顺着走过去，边走边看，直到走到第 1000 道面前才能夹菜。这不是“定位到第 1000 道”，而是“肉身遍历前 999 道”。MySQL 的深分页就是这个“肉身遍历”。

1.2 二级索引扫描与回表：每一行都要付出的随机 I/O 成本

如果表上只有一个唯一主键索引，比如id，而查询条件是shop_id = 123 AND status = 'PAID'，那么 InnoDB 只能通过主键索引做全表扫描，或者扫描某个二级索引后再回到主键索引取完整行。

重点在“回表”这个词上。InnoDB 中，二级索引的叶子节点存储的是索引列值和主键值。假如你建立了idx_shopid_status (shop_id, status)，执行SELECT * FROM t_order WHERE shop_id = 123 AND status = 'PAID'，找到的是符合条件的全部主键 id，接着每一条都要拿着主键 id 到聚集索引里，再读取完整行数据。这个动作叫回表。

回表是随机 I/O。因为即使 shop_id 相同，这些订单分散在表空间的不同数据页里，并不是连续排列的。深分页时，被扫描的 100020 行里有差不多十万行都要做一次回表，而最终只需要 20 行。相当于把十万波随机 I/O 的绝大多数成本花在了“确定要不要丢弃”上。

更崩溃的是，深分页扫描越深，被扫描到的目标行越“孤立”。InnoDB 的预读机制通常按顺序把相邻页面加载到缓冲池，几十行的浅分页可能正好命中连续页，但十万行之后的数据页很难被预判，热数据命中率下降，I/O 成本进一步放大。

1.3 排序、filesort 与深分页的叠加放大

如果查询里还有 ORDER BY，并且排序字段和索引顺序不匹配，MySQL 会走排序缓冲甚至临时文件，这个过程叫 filesort。排序本身需要把结果集放入sort_buffer_size指定的内存区域，放不下就溢写到磁盘临时文件，会产生额外的磁盘读写。

深分页遇到 filesort 是最糟糕的组合。例如：

SELECT * FROM t_order WHERE status = 'PAID' ORDER BY create_time DESC LIMIT 100000, 20;

即使status上有索引，只要 order by 的不是索引顺序，仍可能先取出所有status='PAID'的行，把所有需要的列塞进排序缓冲区，排序完成后再砍掉前十万行。也就是说，排序的是十万行，甚至几十万行，而返回值只有 20 行。

深分页的性能问题不是单因子的，而是扫描行数、回表次数、filesort 磁盘写、数据页分散四者叠加的结果。任何一项单独看都不致命，但四个因素一起出现时，慢是必然的。

2. 生产环境下的深分页“症状”与快速定位

2.1 哪些业务场景最容易中招

不是所有分页都会触发深分页。我梳理一下最容易中招的场景，这些都是真实的线上典型：

• 管理后台列表：订单、会员、对账单、日志，动辄几百万行数据，默认按创建时间倒序排列，翻页没有上限；
• 带有筛选条件的列表：比如“查询某店铺全部已支付订单”，命中行数巨大，第几万页之后毫无约束；
• 报表导出前的前端预览：先加载前 500 页的分页数据，用户在页面上拖到很后面；
• 移动端瀑布流接口被改造成“页码分页”：本应做游标翻页，结果前端还是用了page*size来请求；
• 定时任务把大数据集分批处理：每批LIMIT offset, batch_size，并循环执行，越往后越慢。

这些业务有个共同特点：查询条件本身不够收敛，或者产品没有限制最大页数，让已经膨胀的偏移量无限变大。后台系统的分页最容易出问题，因为用户是运营或客服，他们真的会翻到第几万行去找一条历史单据。

2.2 用 EXPLAIN 和 EXPLAIN ANALYZE 确认深分页

排查深分页时，第一件事是拿到出问题的 SQL，放到测试库执行EXPLAIN，重点看四个字段：

但EXPLAIN里的 rows 是优化器估算值，不一定准确。MySQL 8.0.18 之后可以用EXPLAIN ANALYZE直接看到实际执行过程中扫描了多少行、每条算子耗时多少：

EXPLAIN ANALYZE SELECT * FROM t_order WHERE shop_id = 123 AND status = 'PAID' ORDER BY id DESC LIMIT 100000, 20;

输出里会显示类似rows_examined: 100020的数据，如果这个数字远远大于rows_sent: 20，深分页问题基本坐实。我见过很多“看似只查 20 行”的慢 SQL，实际扫描了几十万行，上线后数据库 CPU 直接被拖垮，原因就在这里。

2.3 从慢查询日志和性能指标侧确认

如果线上表现是间歇性的，可以通过慢查询日志采集证据。打开慢日志并设置阈值：

SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1;

然后在慢日志里找到对应 SQL，观察Rows_examined和Rows_sent的差距。举例来说，某条查询Rows_sent: 20，但Rows_examined: 120040，这条 SQL 的“有效工作率”只有 0.017%。数据库大部分时间都在做无用功。

此外，还可以通过 performance_schema 的语句历史表确认：

SELECT THREAD_ID, SQL_TEXT, ROWS_EXAMINED, ROWS_SENT, TIMER_WAIT FROM performance_schema.events_statements_history_long WHERE SQL_TEXT LIKE '%LIMIT%' ORDER BY TIMER_WAIT DESC LIMIT 10;

观察到的典型特征是：查询时间随页号线性增长，CPU 在数据库实例上飙升，磁盘 I/O 频繁，但 QPS 总量并不高。这种“单个查询吃掉大量资源”的表现就是深分页的特征。

3. 常规优化手段盘点：能用，但有边界

3.1 先从产品侧砍掉深分页

优化 SQL 之前，先确认业务是否真的需要支持第 10000 页之后的数据。大部分场景下，用户看前几十页就满足了。产品可以限制最大页码，比如最多翻到第 200 页，超过后提示“请增加筛选条件或使用导出功能”。这一步做掉之后，深分页在后端直接没有机会发生。

另一种产品侧调整是强制加时间筛选。比如订单列表没有时间范围时默认只能看近 90 天，或者必须选择一个时间区间才能查询。这个约束能大幅减少扫描行数，比任何索引优化都有效。对一个合格的后端系统来说，“约束查询范围”应当是第一优先级。

接口层也可以设计防御手段：后台分页接口的page参数超过某个阈值就拒绝执行，并对导出操作走异步任务通道，不直接同步跑深分页。

3.2 覆盖索引把回表砍掉

覆盖索引的意思是，查询要返回的列全部包含在某个二级索引里，这样 InnoDB 就不需要回表了。比如列表只需要显示订单号、创建时间、状态，那么建立(status, create_time, id, order_no)索引，查询时所有字段都能从索引叶子节点拿到。

SELECT order_no, create_time, status FROM t_order WHERE status = 'PAID' ORDER BY create_time DESC LIMIT 100000, 20;

如果走了覆盖索引，扫描十万行索引记录依然存在，但没有了回表随机 I/O。相比全表扫描，性能提升是数量级的。不过覆盖索引有局限：后台列表通常要展示几十个字段，不可能全部塞进索引里。你把索引建得很宽，写入性能就下降，而且索引体积膨胀后扫描成本也被抬高。所以覆盖索引适合字段少的场景，比如列表的“摘要接口”或“导出任务取数”。

3.3 延迟关联：只回表真正要返回的 20 行

延迟关联的核心思路是：先在一个覆盖索引上查出需要的 20 个主键 id，再用这 20 个 id 去关联原表查出完整数据。原来的 SQL 是扫描十万行并回表十万次，优化后只回表 20 次。

SELECT t.* FROM t_order t INNER JOIN ( SELECT id FROM t_order WHERE shop_id = 123 AND status = 'PAID' ORDER BY id DESC LIMIT 100000, 20 ) tmp ON t.id = tmp.id ORDER BY t.id DESC;

内层查询只取主键 id，可以在索引上快速扫描，外层按照 20 个主键回表。深分页的扫描行数并没有减少，内层仍然要扫十万行索引，但回表次数从十万降到了 20，省下了大部分随机 I/O。

需要提醒的是，内层这种写法在 MySQL 8.0 里有些版本会做派生表合并，效果可能退化成和原 SQL 一样。执行计划里如果看到 Extra 没有Materialized或Derived，而还是直接全表访问，就需要考虑用optimizer_switch临时关闭派生表合并，或者用别的写法保证内层先物化。线上改造这类 SQL 时，一定要用EXPLAIN对比优化前后的执行计划，不要想当然。

3.4 用主键排序替代业务字段排序

很多业务排序字段是create_time，但创建时间是有可能重复的，而且未必有索引。如果表的主键是自增 ID 或者雪花算法生成的单调递增 ID，那么ORDER BY create_time的结果通常和ORDER BY id是相同的，但用id排序可以天然走主键索引，避免 filesort。

-- 改造前 SELECT * FROM t_order WHERE status = 'PAID' ORDER BY create_time DESC LIMIT 100000, 20; -- 改造后 SELECT * FROM t_order WHERE status = 'PAID' ORDER BY id DESC LIMIT 100000, 20;

这里有一个前提：创建时间和主键顺序在同向单调关系上是“业务可接受”的。如果订单允许后补、或者数据由多套系统写入且时钟不一致，就不能直接用 id 排序。这个判断必须结合业务确认，否则排错序就不是性能问题了，是正确性问题。

4. 生产级解决方案：键集分页（游标分页）

4.1 换一种思路：前端不再传页码，而是传上一页的最后一条记录

前面几种优化其实都治标不治本，因为只要还在用LIMIT offset, size，扫描行数就会随着 offset 的增长线性上涨。真正生产级的做法是砍掉“偏移量”这个概念，改用“锚点”来翻页，也就是键集分页，也叫游标分页、keyset pagination、seek method。

核心思想非常简单：上一页返回的最后一条记录，已经给出了一个“锚点”。下一页的请求不用再说“我要第 100000 行之后的 20 行”，而是说“我要从某个排序键之后开始拿 20 行”。比如按主键倒序分页，第一页返回的最大 id 是 100050，那么第二页直接请求：

SELECT * FROM t_order WHERE shop_id = 123 AND status = 'PAID' AND id < 100050 ORDER BY id DESC LIMIT 20;

这样 MySQL 可以利用主键索引快速定位到id = 100050附近，然后顺序向后扫描 20 条记录，扫描行数大约是 20 行，而不是十万行。无论你翻到多深，单页成本几乎是恒定的。这才是深分页场景下值得长期维护的方案。

4.2 单字段游标的落地实现

先看最简单的单字段游标：假设列表排序字段就是主键id，且是倒序。前端请求参数除了原来的筛选条件之外，多一个cursor字段。后端维护一个游标，值为上一页最后一条记录的 id。

第一页请求：

GET /api/order/list?shop_id=123&status=PAID&page_size=20

返回给前端的数据里带上下一次请求游标：

{ "list": [...], "next_cursor": "100050" }

前端翻下一页时带上这个游标：

GET /api/order/list?shop_id=123&status=PAID&cursor=100050&page_size=20

后端解析出 cursor 中的数值，生成 SQL：

SELECT * FROM t_order WHERE shop_id = 123 AND status = 'PAID' AND id < 100050 ORDER BY id DESC LIMIT 20;

如果返回的list长度小于page_size，说明没有更多数据了，终止游标携带。这个过程中间不需要维护任何“页码”状态，服务端是无状态的，游标本身就是状态。

4.3 复合排序键的游标写法

单字段游标只能应对“按主键排序”的场景。很多列表是按时间倒序排的，主键没有业务含义，这时需要多个排序键来锁定位置。

比如列表按create_time DESC, id DESC排序。上一页最后一条记录的create_time是2025-06-01 12:30:00，id是582310。那么下一页的条件就可以用行值比较（row value constructor comparison）：

SELECT * FROM t_order WHERE shop_id = 123 AND status = 'PAID' AND (create_time, id) < ('2025-06-01 12:30:00', 582310) ORDER BY create_time DESC, id DESC LIMIT 20;

MySQL 会把(create_time, id) < (...)翻译成：

create_time < '2025-06-01 12:30:00' OR ( create_time = '2025-06-01 12:30:00' AND id < 582310 )

这种写法恰好能利用(status, create_time, id)这样的复合索引，先按等值条件过滤status，再在索引上做一个范围定位。不管前面的页有多少，只要锚点确定，MySQL 都能在索引树里通过范围找到目标位置，然后再顺序取 20 条。

升序分页同理，条件方向反过来：

SELECT * FROM t_order WHERE shop_id = 123 AND status = 'PAID' AND (create_time, id) > ('2025-06-01 12:30:00', 582310) ORDER BY create_time ASC, id ASC LIMIT 20;

很多团队对行值比较不熟，容易写出两层嵌套子查询，结果索引利用不了，速度反而慢。直接利用 MySQL 的元组比较能力，配合 position 索引，是这套方案最高效的写法。

4.4 游标的编码、校验与防篡改

生产环境用游标分页，不能直接把create_time和id拼成字符串传给前端，这里有几个工程问题：

1. 游标信息可能包含多个字段，需要结构化编码；
2. 前端可以随意修改游标，后端必须校验；
3. 游标要和当前查询条件绑定，否则用户把 A 列表的游标拿过来查 B 列表，结果会错乱。

我建议的做法是：在后端把游标字段组装成一个字符串，做一次签名，再 Base64 编码。游标解码失败、签名不匹配、字段类型不合法时，直接返回参数错误，而不是当成有效游标执行 SQL。

一个简单的签名思路：

• 把需要参与游标定位的字段 JSON 序列化；
• 拼上当前查询条件的哈希值；
• 用统一密钥做 HMAC 签名；
• 把签名 + 数据一起做 Base64URL 编码，传给前端。

后端收到游标后先解码、校验签名、比对查询条件哈希，再解析出排序字段。这样既保证安全，也解决了“改了筛选条件但游标还是旧的”这类脏数据请求。

另外注意：游标分页天然不支持“跳页”。如果产品必须保留页码跳转能力，那只能在“前几页用偏移分页，之后改成游标”或者“限制最大页数”之间选一个。不能既想支持无限页码跳转，又想扫描行数恒定，这两个诉求在单机 MySQL 里是互斥的。

4.5 与偏移量分页的对比

把两种方式放到一起看，优劣势很清楚：

线上接入游标分页后，我观察到的最直观变化是：同一个深列表接口，原来翻到 500 页时耗时 3 秒，现在翻到第几万页也是 20ms 左右。代价是前后端协议多了一个cursor字段，以及后端多了编码校验逻辑。

5. 常见问题与避坑实录

5.1 排序字段重复导致数据重复或漏读

这是一个很容易踩的坑。假设列表只用create_time排序，但同秒内有多条订单，游标携带的锚点也是create_time。因为排序键不唯一，同一时间点可能出现几十条数据，游标判断create_time < '2025-06-01 12:30:00'时，等于这一秒内的部分记录被丢掉，或者下一次查询又重复返回上一秒的数据。

解决办法是：ORDER BY 永远加上一个唯一键做二级排序，例如ORDER BY create_time DESC, id DESC，游标也必须携带create_time和id两个字段。之前提过，这是游标分页的底线，不是建议项。

5.2 排序字段为 NULL 时的诡异行为

MySQL 对 NULL 的排序默认是：升序时 NULL 排最前，降序时 NULL 排最后。如果排序字段允许为空，锚点判断会出现很多边界 bug。比如用create_time DESC排序，某个订单的create_time为 NULL，它会排在列表最后，但游标条件create_time < last_create_time永远匹配不到它。

我的建议是：数据库设计阶段就把排序字段设为 NOT NULL，给定默认值。如果线上已经是 NULL，那就先在查询里用COALESCE转成默认值再参与排序，但要注意函数包裹字段可能影响索引。最省心的方式还是在应用层写入数据时保证排序字段非空。

5.3 优化器吞掉派生表，延迟关联失效

前面提到延迟关联时，如果内层查询被优化器合并到外层，整个优化就白做了。实践中不是每条 SQL 都这么听话，尤其查询里没有聚合、没有 LIMIT 时，MySQL 8.0 会倾向于把派生表合并掉。

遇到这种情况，我先看执行计划，如果发现实际执行的扫描行数没降下去，就临时关闭该语句的派生表合并，或者改写为临时表形式，迫使 MySQL 先物化内层结果。建议上线前用EXPLAIN ANALYZE实测rows_examined，不要只看逻辑上“写得对不对”。

5.4 批量导出任务还在用 offset 循环

有些系统做批量导出时，用类似for page := 1; ; page++的方式分批拉取全部数据，每次都执行LIMIT (page-1)*size, size。数据量一大，前面批次还快，后面批次越来越慢，整个导出任务可能要跑几个小时。

正确的做法是改成游标循环：每次查询拿到一批数据，记录最后一条的排序键，下一批用这个键继续查。这样不仅快，而且不会因为导出过程中有新增数据导致重复扫描。对导出、同步、清洗这类离线任务，游标分页是必然选择。

5.5 数据删除和状态变更后的游标稳定性

游标分页看起来比偏移分页稳，但在有删除或状态变更的场景下也会带来困惑。上一页的最后一条记录，在用户点击下一页时如果刚好被删了，下一页从这条记录的锚点继续查，一般不会丢失数据，因为游标只用于定位，不需要回查那条记录本身。

但状态变更更麻烦。比如列表条件限定status = 'PAID'，上一页最后一条是已支付订单。用户在这一页把某条订单取消支付了，那么这条记录已经不符合查询条件。下一次从锚点往后翻，这条被改状态的记录不会出现，但它在当前页已经展示给用户过了，用户可能感觉“列表里少了一条”。这类问题很难通过数据库层解决，需要前端在下拉刷新或重新加载时重建游标。这也是交互上不要“无限下拉但永不刷新”的原因之一。

6. 从深分页反推索引设计与监控体系

6.1 游标分页对索引的要求其实很讲究

索引设计口诀可以总结为三句话：等值条件放前面，排序字段放中间，唯一键兜底放最后。

以订单列表为例：

SELECT * FROM t_order WHERE shop_id = 123 AND status = 'PAID' AND (create_time, id) < ('2025-06-01 12:30:00', 582310) ORDER BY create_time DESC, id DESC LIMIT 20;

复合索引可以设计为idx_shop_status_time_id (shop_id, status, create_time, id)。shop_id和status在查询中是等值条件，放在最前；create_time和id用于排序和游标范围，放后面。由于shop_id、status已经通过等值条件过滤掉，MySQL 真正要处理的只是每个店铺和状态下的一段索引范围，效率非常高。

如果查询还有AND type = 2这种条件，就要评估哪个等值列区分度更高，把高区分度的列放前面。必要时可以用多套索引分别覆盖不同的筛选组合。索引设计不是一次建一堆，而是基于实际 SQL 模式去配，配合慢查询日志迭代。

6.2 深分页排查需要长期监控的指标

生产环境不可能每次都等到用户投诉才去抓慢 SQL。可以做一套针对性的监控：

• 慢查询日志里Rows_examined / Rows_sent比值超过 1000 的 SQL 全部接入告警；
• 对依赖分页接口的表，定期抽查EXPLAIN的预估 rows；
• 性能数据采集任务中，比对聚合索引 scans per query这类指标；
• 记录接口的 P99 耗时和深分页请求占比，一旦深页访问量增加立刻预警。

很多时候深分页是一个“慢慢恶化”的问题，因为数据量在增长，同样的 offset 查询扫描行数也在增长。今天第 100 页不慢，不代表三个月后还不慢。所以监控的价值不只是发现问题，更是发现趋势。

6.3 什么时候该放弃深分页 SQL

游标分页能解决大部分单表深分页，但有些业务天然不适合在 MySQL 里翻页。比如：

• 需要跨多张表、多维筛选、全文搜索的复杂列表；
• 需要支持任意“跳页”的对外搜索引擎式产品；
• 数据量千万级且查询条件组合特别多，MySQL 索引数量已经失控。

这类场景更适合把查询压力转移到搜索引擎或离线数仓，MySQL 只做核心事务存储。不要抱着“所有查询都必须走 MySQL”的执念。深分页优化的目的不是把 MySQL 变成全能查询引擎，而是把数据库性能维持在一个可预期的健康区间。

最后分享一个我自己的经验：很多团队在解决深分页时，第一反应是加内存缓存或者上搜索引擎，但实际定位后发现问题非常初级——只是缺一个合适的复合索引，以及分页协议该从页码改成游标。把这两件事做好，超过九成的深分页问题都能在 MySQL 层解决。真遇到跨库、多字段全文搜索这类需求，再去引入重组件也不迟。每个系统都该定期翻一翻自己的慢查询，特别是那些“翻页越深越慢”的接口，它们往往早就在日志里呼喊了很久。