去年我接手过一个订单查询接口，单表数据已经过亿，页面还是最传统的那种“下一页”式分页。用户翻到第60页之后，接口响应时间直接从几百毫秒跳到几秒；翻到100页以后，基本就是超时重试的节奏。我看数据库监控，CPU不高，IO不忙，SQL执行计划也走了索引，但就是慢得离谱。这套现象，做后端的同学一定不陌生——这就是典型的深分页问题。
深分页问题的本质，是LIMIT + OFFSET这种访问方式在数据量小的时候几乎没存在感，一旦碰上亿级数据，越往后翻越灾难。数据库没办法“跳过”OFFSET行，只能把前面的行全部扫描出来再丢弃。这篇文章把我从慢SQL排查、方案选型，到最终落地的一整套打法完整复盘一遍，包括游标分页、覆盖索引延迟回表、基于缓存的有序ID分页等主流方案，以及一路踩出来的坑。适合后端开发、DBA，以及所有被“数据量一大分页就卡”折磨过的同学对照参考。
1. 深分页到底慢在哪里：从执行计划到量化分析
1.1 一条“看起来没毛病”的SQL
先看一条再常见不过的分页SQL：
SELECT id, order_no, status, created_at FROM orders WHERE user_type = 1 ORDER BY id DESC LIMIT 20 OFFSET 1000000;逻辑很清楚：把用户类型为1的订单按主键倒序，从第1000001行开始取20行。很多人以为OFFSET会让数据库直接跳到第1000001行去读，实际上MySQL根本没有这个能力。它得先从满足条件的起始位置开始，一行一行扫描，累计到1000000行之后，把前面的全部丢掉，只返回最后这20行。也就是说，你最终只拿到20条数据，数据库实际触碰的行数却是一百万级的。
如果这张表只有一个主键索引，执行计划里的rows基本都是百万级估算值，Extra里大概率出现Using where；如果user_type上刚好建了二级索引，回表次数更是被放大。深分页慢的根本原因就在这里：扫描范围不会因为你只返回20行而缩小，页数越深，数据库做无用功越多。
1.2 并不只是OFFSET的锅：三个隐藏成本
深分页耗时，其实是三层成本叠加的结果。第一层是扫描行数放大，OFFSET=1000000时，至少100万条索引记录要被遍历，这是无法绕开的基础开销。第二层是回表随机IO：如果SELECT需要取二级索引之外的字段，比如order_no、status、created_at，每扫到一行满足条件的记录，都要回聚簇索引再查一次完整行。扫描行数一大，回表次数跟着翻倍，而索引叶子节点在磁盘上是离散分布的，每一次回表都是一次随机IO，代价相当大。
第三层是排序和临时表。当ORDER BY字段和索引顺序不一致，或查询里带了GROUP BY、DISTINCT，MySQL可能走filesort甚至生成临时表。深分页时临时表会变得很大，内存装不下就往磁盘写，整个查询就彻底回到了“慢”的领域。可以类比成在图书馆里按编号找书：管理员必须从第一本开始数，数到100万本才停下来；如果每数到一本还要拆开封面看一眼，自然更慢。几个隐藏成本叠加之后，OFFSET稍大就会产生比业务逻辑多几个数量级的开销。
1.3 模拟数据：从百万到亿级的变化曲线
为了直观，我之前在一台普通虚拟机上做过一组相对测试，单表按自增主键写入不重复数据，模拟不同数据量级下的深分页表现。大致的相对耗时趋势如下：
| 表内数据规模 | LIMIT 20 OFFSET 0 | LIMIT 20 OFFSET 10000 | LIMIT 20 OFFSET 1000000 |
|---|---|---|---|
| 100万行 | 3ms | 12ms | 320ms |
| 5000万行 | 4ms | 85ms | 3700ms |
| 1亿行 | 6ms | 180ms | 6800ms |
这组数字受内存缓存、磁盘类型、索引设计影响很大，不看绝对值，只看相对变化：OFFSET在1万以内时，哪怕亿级表也能在百毫秒级完成；一旦OFFSET到百万级，耗时直接跳到秒级，甚至出现接口超时。并且这个变化并不是均匀的，临界点通常出现在数据量突破内存热数据窗口、或回表频繁触发磁盘IO的时候，曲线会突然变陡。
1.4 什么样的业务最容易中招
最容易踩深分页的不是那些“看起来查询很复杂”的系统，恰恰是结构简单的流水型业务：订单列表、支付流水、行为日志、消息记录、操作审计。这类表只增不改，数据量天然大，页面又习惯按时间倒序展示。用户只要愿意往后翻，OFFSET涨到几十万只是时间问题。我见过一个运营后台，单表不到三千万行，用户为了找一个月前的单据，硬是点了七十多次“下一页”，最后把页面点死了。这类行为一旦没有约束，深分页就是迟早的事，和SQL本身写得多漂亮关系不大。
2. 四类优化方案全景：思路与选型逻辑
2.1 一张表看懂四类方案
先给一个全景对比，方便在动手前建立整体认知：
| 方案 | 核心思路 | 翻页能力 | 主要代价 | 推荐场景 |
|---|---|---|---|---|
| 游标分页（Keyset） | 用WHERE id > last_id代替 OFFSET | 仅上一页/下一页 | 不能任意跳页，排序字段受限 | APP信息流、实时列表 |
| 覆盖索引+延迟回表 | 子查询先在索引上拿主键，再回表取完整行 | 支持任意跳页 | SQL改写成本高，OFFSET仍有索引扫描代价 | 后管列表、报表页 |
| ID区间分页 | 按自增主键或唯一序号分段 | 支持任意跳页 | 主键空洞、条件组合时易失效 | 数据单调写入，无删除场景 |
| 缓存有序ID | 预热ID列表到Redis ZSet，按score分页 | 支持任意跳页 | 一致性维护成本高，内存占用大 | 消息中心、报表、活动页 |
先理解一个关键点：所有方案的本质，都是尽量把“扫描+丢弃”变成“直接定位”。只要你还在用LIMIT OFFSET，数据库就无法逃避“把前面所有页重复读一遍”这件事；只要换成游标、区间或缓存，你就是在主动告诉数据库“从哪开始”，而不是让它自己数到那儿。
2.2 游标分页：把“数页码”变成“记路标”
游标分页是最接近“物理正确”的方案。LIMIT OFFSET的痛点是让数据库从起点开始数页码；游标分页则让客户端每次翻页带上上一页最后一条记录的游标值，通常是主键ID。服务端拿到游标后，直接用WHERE id < last_id去定位，不再管前面有多少页。相当于你爬山时在关键路口做了标记，第二次不需要从山脚重新走，直接去标记处继续前进。
-- 传统深分页：第N页 SELECT ... FROM orders WHERE user_type = 1 ORDER BY id DESC LIMIT 20 OFFSET 10000; -- 游标分页：从上一页最后一条 id=80001 开始 SELECT ... FROM orders WHERE user_type = 1 AND id < 80001 ORDER BY id DESC LIMIT 20;后者执行计划里的rows基本是“满足条件且停在80001之前”的数据量，不再随页码变大而膨胀。不过游标分页有一票否决条件：如果产品经理要求回到顶部能直接点“第12345页”，这个方案基本只能作罢。这类交互常见于后管系统，前端需要跳页，后端的游标就难以为继。另外要注意，游标分页依赖排序字段稳定且有索引，不能随便排。
2.3 覆盖索引延迟回表：保住跳页能力
当业务必须支持跳页，覆盖索引加延迟回表是我最常用的一招。“覆盖索引”是指一个二级索引已经把查询要读的列都包含了，查询只在索引页里就能拿到结果，Extra显示为Using index，不需要回表。“延迟回表”则是在此基础上，用子查询先在索引里扫描出目标页的主键ID集合，再用这个集合去聚簇索引里取完整数据行。
深分页场景里收益特别好理解：原查询可能要回表100万次，改写后无论OFFSET多大，回表只发生在最终命中的那一页数据上（比如20次）。但要注意，这个方案不是让数据库不扫描了，而是把扫描对象从“完整行+回表”降级成“窄索引页”，整体成本低一个数量级。适合后台管理列表这种必须支持跳页、又不至于让用户无限制翻几千页的场景。
2.4 三种补充思路与“选型三问”
除了上面两种，还有三个方向也值得纳入方案池。一是ID区间分页，利用自增主键或唯一有序编号做BETWEEN，实现任意跳页；但它对主键空洞敏感，一旦有删除、批量导入产生了坑，页码就会出现空白或错位，更适合单调写入、基本不做物理删除的表。二是冷热分离/归档，把N个月之前的历史数据迁到历史表，线上主表只保留热数据；这不是SQL优化而是数据治理，但往往最能治本。三是引入检索组件：当查询条件特别多、各种字段任意组合时，SQL索引很难覆盖所有场景，可以把筛选维度同步到搜索引擎，用倒排索引解决，但代价是引入额外组件和同步链路。
无论选哪条路，动手前一定要回答三个问题：业务能不能接受“只有上一页/下一页”？排序字段是否稳定，能不能提炼出唯一的复合排序键？数据能不能被业务维度先过滤一层（比如只看自己的订单、只看最近三十天）？这三个问题决定你该用游标、延迟回表还是缓存方案，也决定这个优化能不能活过需求评审。
3. 实操落地：从SQL改造到缓存方案的完整记录
3.1 落地前先摸清三张底牌
直接改SQL前，我习惯先花半天把三件事搞清楚。第一是表结构和索引现状：主键是自增还是雪花ID，排序字段上有没有联合索引，哪些列经常出现在SELECT里。第二是真实慢SQL：打开慢日志，把分页接口在生产上的实际SQL捞出来，别用开发环境的“假数据”推演。第三是业务约束：确认前端翻页交互、最大可接受的翻页深度，以及跳页、上一页这些能力是否必须保留。
这三张底牌决定方案方向。比如一张订单表主键是雪花ID、天然时间有序，那么游标分页几乎首选；但如果业务必须在列表顶部显示“共多少页”，游标方案基本没戏，得回到延迟回表。业务约束不先对齐，技术方案再漂亮也落不了地。另外，排查出慢SQL后要把基线记下来：优化前典型耗时、rows_examined、rows_sent，优化后同样记录一遍，后面验证效果才有依据。
3.2 改造一：游标分页的SQL与代码实现
这里给一个可运行的完整思路，用Python伪代码说明，换Java、Go、C#都是一样的逻辑：
# 约定每页20条 def list_orders(user_id, last_id=None, limit=20): cond = "user_id = %s" params = [user_id] if last_id: cond += " AND id < %s" params.append(last_id) sql = f""" SELECT id, order_no, status, created_at FROM orders WHERE {cond} ORDER BY id DESC LIMIT %s """ params.append(limit) rows = db.query(sql, params) # 下一页游标取当前页最后一条记录的id next_cursor = rows[-1]["id"] if rows else None return rows, next_cursor接口返回体里带上next_cursor，前端点“加载更多”时把上一页返回的next_cursor回传。第一页没有游标，传空即可。这里有一个重要前提：表上必须有(user_id, id)联合索引，否则WHERE user_id=? AND id < ?有可能走全表扫描，优化等于白做。执行计划检查到key包含user_id和id时，这条SQL的耗时才会随数据增长保持平稳。
如果业务要求按创建时间倒序而不是按主键倒序，游标要升级成二元组。因为同一毫秒内可能会出现多条订单，created_at不唯一，只用时间戳做游标必然漏数据。稳妥写法是用(created_at, id)作为复合游标：
SELECT id, order_no, status, created_at FROM orders WHERE user_id = :uid AND (created_at < :last_created_at OR (created_at = :last_created_at AND id < :last_id)) ORDER BY created_at DESC, id DESC LIMIT 20;联合索引建为(created_at, id)。这个写法的边界判定更准确，代价是SQL稍微复杂，但这是值得的。
3.3 改造二：覆盖索引延迟回表的SQL改造
再来看必须支持跳页的后台列表场景。原SQL往往是这种：
SELECT id, order_no, status, created_at FROM orders WHERE status = 1 ORDER BY id DESC LIMIT 1000000, 20;优化后的写法：
SELECT o.id, o.order_no, o.status, o.created_at FROM orders o INNER JOIN ( SELECT id FROM orders WHERE status = 1 ORDER BY id DESC LIMIT 1000000, 20 ) t ON o.id = t.id ORDER BY o.id DESC;前提是orders表上有(status, id)联合索引。执行流程拆开看：子查询在窄窄的二级索引页里只拿id，因为只需要id和status，不需要回表，即使扫描一百万条索引记录，也远比回表一百万次来得快。INNER JOIN再去聚簇索引里取这20条完整数据，回表次数降到20次。外层再排一次序，保证最终顺序稳定。
为了让大家对收益有体感，我在本机模拟数据上做过一次对比测试，数据量压在3000万行左右，同样的OFFSET=1000000，两种写法的耗时和回表次数差异非常大，大致整理如下表：
| 写法 | 索引使用 | 回表次数 | 模拟耗时 |
|---|---|---|---|
SELECT * ... LIMIT 1000000, 20 | 二级索引后逐行回表 | 约100万次 | 5800ms |
| 覆盖索引+延迟回表 | 子查询使用覆盖索引 | 20次 | 880ms |
需要说清楚，这个方案不是银弹。OFFSET仍然存在，子查询仍然要扫描到一百万行索引记录，只是避开了回表随机IO。如果业务允许用户翻到几百万页，索引扫描本身也会成为瓶颈。我的经验是：后管系统加上最大OFFSET限制，比如超过20000页就提示缩小筛选范围，再配合延迟回表，工程上既简单又稳妥。
3.4 改造三：基于Redis有序ID的分页落地
第三种方案适合读多写少、列表数据可以被预生成的场景，比如报表、消息中心。核心思路是：用一个后台任务或异步回调，把满足条件的有序数据ID写进Redis的ZSet，member是业务ID，score是排序值。前端分页时，直接在ZSet上按score区间取ID，再拿着ID去数据库批量查明细，数据库这边就再也不会出现“深分页慢查询”的问题了。
# 构建：score可以取毫秒时间戳，member用业务ID ZADD orders:report:202501 1735689600000 xid_10000001 ZADD orders:report:202501 1735689601000 xid_10000002 # 第一页：按score从大到小取20条 ZREVRANGEBYSCORE orders:report:202501 +inf 0 LIMIT 0 20 # 下一页：上一页最后一条score为1735689601000，取比它小的区间 ZREVRANGEBYSCORE orders:report:202501 1735689600999 0 LIMIT 0 20这里有一道细节题：如果score只取时间戳，同一毫秒内可能有多条数据，会出现边界区间的重复或遗漏。更省心的做法是，如果主键是雪花ID，直接用ID做score，因为雪花ID本身时间有序且唯一，ZSet天然稳定；如果主键是自增ID但排序要按业务时间，就要在score设计上保证唯一性，或者在边界判断时处理同score成员。这个细节单独抽出来讲，是因为我见过太多人在这里翻车。
内存方面也要提前估算。一个ZSet成员在Redis里的开销并不低，成员本身、score、跳表指针、字典对象加起来，往往要几十字节甚至上百字节。一个亿级成员的ZSet，内存占用很容易到十几GB甚至更高。所以我不建议把全量ID都塞进去，更合理的是按用户、月份、状态拆key，只缓存活跃窗口，并给不活跃的key设置TTL自动淘汰。
3.5 分页参数与容量的实用建议
最后给一组我自己常用的参考口径。分页大小：列表页单页20到50条足够，后管报表可以放宽到100条，但不建议更大，因为单页越大，LIMIT之后的回表、序列化和传输成本全部被放大。游标字段类型：优先bigint或雪花ID，varchar主键也能用，但比较和索引宽度都比整数差；想用UUID作为游标基本不建议，无序又宽，索引性能和缓存友好度都差。
容量估算可以用一个简单公式：单个分页条目在Redis里按100字节粗算，一个月5000万条数据大约5GB内存。如果你评估后觉得内存成本过高，就回到“按月拆key + TTL”的思路，让活跃窗口固定在可控规模。不要试图用一个ZSet扛住整张亿级表的所有历史，那会把Redis也变成另一个“深分页”。
4. 高频踩坑与排障实战：常见问题速查
4.1 常见问题速查表
把我在生产环境中遇到过的、以及朋友反馈过的高频问题整理成一张速查表，遇到类似现象可以直接对照：
| 现象 | 可能原因 | 排查动作 |
|---|---|---|
| 游标分页偶发少数据 | 查询期间有新数据插入，游标边界漂移 | 接受最终一致性或改为快照分页 |
| 复合游标排序乱 | 没建(created_at, id)联合索引 | EXPLAIN 看 key，补索引 |
| 延迟回表结果顺序乱 | 子查询排序后外层未再排序 | 外层加 ORDER BY |
| 覆盖索引没生效 | SELECT 里有回表字段或函数操作 | 去掉冗余列，避免函数包裹索引列 |
| 加了 LIMIT 还是全表扫描 | 条件字段没走索引/隐式类型转换 | 检查字段类型，看 possible_keys |
| 缓存分页出现幽灵ID | 源库删除后缓存未同步 | 做异步删除补偿任务 |
4.2 排查慢SQL的标准动作：从慢日志到执行计划
排查深分页，我有一套固定动作。先开慢日志，把超过1秒的查询抓出来：
SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; SHOW VARIABLES LIKE 'slow_query_log_file';抓到慢SQL后，用EXPLAIN看执行计划，重点看type、rows、Extra这三列。type尽量是range或ref，rows要和实际数据量对得上，Extra里出现Using filesort、Using temporary都要警惕。MySQL 8.0以上可以直接用EXPLAIN ANALYZE看每一步的耗时和读取行数，比全凭估算强太多。
还有一个长期有效的手段：记录rows_examined和rows_sent的比值。如果一条分页查询读了几十万行，最终只返回20行，rows_examined / rows_sent可能上万，这就是深分页在“做无用功”的直接证据。这个比值可以作为分页接口日常巡检的指标，数值超过一定阈值就自动告警。
4.3 真实踩坑记录：四则实例
第一个坑是游标方案被“总页数”需求直接打死。原以为改完SQL就能上线，结果产品要求在列表顶部显示“共N页”，还说这是运营长期习惯。游标分页根本不知道总页数，因为它从头到尾没有数过OFFSET。最后只能退回延迟回表，并把最大翻页深度压到用户可接受的范围。
第二个坑是排序字段不唯一导致重复数据。某个列表业务要求按created_at倒序，我一开始只用created_at做游标，结果同一秒内创建的多条订单，下一页用created_at < last_created_at就把同秒、ID更大的那批漏掉了。改成(created_at, id)二元组游标，用id做二级排序和边界判等，问题才真正解决。
第三个坑是延迟回表优化后，结果依然很慢。排查发现优化器把子查询中的LIMIT改写掉了，或者因为没有(status, id)联合索引，子查询先走了全表扫描再排序。后来我坚持在子查询的执行计划里确认出现Using index，才把它压下来。子查询的ORDER BY和LIMIT必须落在索引覆盖范围内，优化才有效。
第四个坑是Redis缓存和源库不一致。消息中心用了缓存ZSet分页后，用户删掉一条消息，缓存里还留着这个ID，翻页时就会出现一条已经删掉的“幽灵消息”。后来补了一个异步删除补偿任务，才把一致性稳住。这个方案适合接受短时间不一致的业务，不适合对数据强一致的场景。
4.4 上线前要做的系统级防御
优化方案上线，只是解决了“当前分页为什么慢”。真正要让系统长期不复发，还得做几道防御。第一，给分页接口设置最大OFFSET，超过阈值直接拒绝查询并提示用户缩小筛选范围，而不是让数据库硬扛。第二，前端交互上做约束，比如只允许连续翻页或滚动加载，不让用户凭空跳到太深的页数。第三，对“上一页/下一页”这种交互，后端主动缓存当前页游标和结果，减少重复计算。第四，数据生命周期管理，超过N个月的历史数据迁入历史表，主表保持热数据规模，让深分页在源头就没有土壤。
这四道防御不需要全是技术手段，很多是对产品交互和数据治理的约束，但往往比SQL优化更治本。
5. 多场景选型对照：别让业务为技术买单
5.1 后管系统：必须支持跳页的深分页
后台管理列表通常默认需要跳页，这种场景我排第一的是覆盖索引延迟回表，再叠加最大OFFSET限制。当OFFSET超过一定阈值，与其让用户看到海量空白页，不如提示他增加筛选条件，按时间、状态或负责人把数据圈定到合理范围。后管系统的用户通常也接受“你要找的数据太旧了，请加筛选条件”这类交互。
5.2 APP信息流：游标分页是天然选择
APP里的“下拉加载更多”“上滑翻下一条”，本质就是连续翻页，没有跳页需求。对这种场景，游标分页的SQL最简单、性能最稳。需要注意排序字段足够稳定，并用复合游标处理并发写入。只要你不需要总页数，就别走回LIMIT OFFSET的老路，这是性价比最高的做法。
5.3 报表/消息中心：用可接受的“延迟”换稳定
报表和消息中心的特点是数据量大、查询频繁、但允许一定的延迟。这种场景很适合预计算ID列表或直接生成结果集。报表可以把结果导出成文件分批下载，消息中心可以用Redis ZSet维护活跃消息。用户并不关心你是在查询时算出来的，还是提前备好的，只要结果一致、响应快，就是好方案。
5.4 多条件复杂筛选：让专业组件做专业的事
当一个列表同时支持时间、状态、金额、渠道等十几个筛选条件，并且每个条件都可以任意组合时，SQL索引几乎无法覆盖所有路径。与其在深分页和索引之间反复纠结，不如把筛选维度同步到检索组件里，用倒排索引和内存排序能力解决。代价是要维护数据同步链路、解决一致性问题，团队得有这个能力再上。如果团队规模不大，我会建议先用“固定筛选模板 + 索引 + 延迟回表”顶住，等真正需要再投入这块。
这些方案我并不是一次性全用上，而是踩了几个月的坑之后才沉淀出来的。对我来说，最深的体会是：深分页优化第一个要解决的其实是产品交互问题——深分页是用户翻页行为制造出来的，如果产品设计把翻页深度限制在合理范围，90%的系统根本不需要折腾。第二个才是技术选型，我自己的决策顺序是：能游标先游标，不能游标就用覆盖索引延迟回表，实在要跳页再考虑缓存ID、冷热分离和检索组件。最后分享一个让我长期受益的小习惯：分页接口上线后，每周跑一次慢查询巡检，把rows_examined和rows_sent比例超过一定阈值的SQL全部捞出来处理，在深分页还没爆发前就把它按住，比事后救火舒服得多。
