去年我接手过一个订单查询接口单表数据已经过亿页面还是最传统的那种“下一页”式分页。用户翻到第60页之后接口响应时间直接从几百毫秒跳到几秒翻到100页以后基本就是超时重试的节奏。我看数据库监控CPU不高IO不忙SQL执行计划也走了索引但就是慢得离谱。这套现象做后端的同学一定不陌生——这就是典型的深分页问题。深分页问题的本质是LIMIT OFFSET这种访问方式在数据量小的时候几乎没存在感一旦碰上亿级数据越往后翻越灾难。数据库没办法“跳过”OFFSET行只能把前面的行全部扫描出来再丢弃。这篇文章把我从慢SQL排查、方案选型到最终落地的一整套打法完整复盘一遍包括游标分页、覆盖索引延迟回表、基于缓存的有序ID分页等主流方案以及一路踩出来的坑。适合后端开发、DBA以及所有被“数据量一大分页就卡”折磨过的同学对照参考。1. 深分页到底慢在哪里从执行计划到量化分析1.1 一条“看起来没毛病”的SQL先看一条再常见不过的分页SQLSELECT 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的锅三个隐藏成本深分页耗时其实是三层成本叠加的结果。第一层是扫描行数放大OFFSET1000000时至少100万条索引记录要被遍历这是无法绕开的基础开销。第二层是回表随机IO如果SELECT需要取二级索引之外的字段比如order_no、status、created_at每扫到一行满足条件的记录都要回聚簇索引再查一次完整行。扫描行数一大回表次数跟着翻倍而索引叶子节点在磁盘上是离散分布的每一次回表都是一次随机IO代价相当大。第三层是排序和临时表。当ORDER BY字段和索引顺序不一致或查询里带了GROUP BY、DISTINCTMySQL可能走filesort甚至生成临时表。深分页时临时表会变得很大内存装不下就往磁盘写整个查询就彻底回到了“慢”的领域。可以类比成在图书馆里按编号找书管理员必须从第一本开始数数到100万本才停下来如果每数到一本还要拆开封面看一眼自然更慢。几个隐藏成本叠加之后OFFSET稍大就会产生比业务逻辑多几个数量级的开销。1.3 模拟数据从百万到亿级的变化曲线为了直观我之前在一台普通虚拟机上做过一组相对测试单表按自增主键写入不重复数据模拟不同数据量级下的深分页表现。大致的相对耗时趋势如下表内数据规模LIMIT 20 OFFSET 0LIMIT 20 OFFSET 10000LIMIT 20 OFFSET 1000000100万行3ms12ms320ms5000万行4ms85ms3700ms1亿行6ms180ms6800ms这组数字受内存缓存、磁盘类型、索引设计影响很大不看绝对值只看相对变化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; -- 游标分页从上一页最后一条 id80001 开始 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_idNone, limit20): 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万行左右同样的OFFSET1000000两种写法的耗时和回表次数差异非常大大致整理如下表写法索引使用回表次数模拟耗时SELECT * ... LIMIT 1000000, 20二级索引后逐行回表约100万次5800ms覆盖索引延迟回表子查询使用覆盖索引20次880ms需要说清楚这个方案不是银弹。OFFSET仍然存在子查询仍然要扫描到一百万行索引记录只是避开了回表随机IO。如果业务允许用户翻到几百万页索引扫描本身也会成为瓶颈。我的经验是后管系统加上最大OFFSET限制比如超过20000页就提示缩小筛选范围再配合延迟回表工程上既简单又稳妥。3.4 改造三基于Redis有序ID的分页落地第三种方案适合读多写少、列表数据可以被预生成的场景比如报表、消息中心。核心思路是用一个后台任务或异步回调把满足条件的有序数据ID写进Redis的ZSetmember是业务IDscore是排序值。前端分页时直接在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或雪花IDvarchar主键也能用但比较和索引宽度都比整数差想用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或refrows要和实际数据量对得上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全部捞出来处理在深分页还没爆发前就把它按住比事后救火舒服得多。
去年我接手过一个订单查询接口单表数据已经过亿页面还是最传统的那种“下一页”式分页。用户翻到第60页之后接口响应时间直接从几百毫秒跳到几秒翻到100页以后基本就是超时重试的节奏。我看数据库监控CPU不高IO不忙SQL执行计划也走了索引但就是慢得离谱。这套现象做后端的同学一定不陌生——这就是典型的深分页问题。深分页问题的本质是LIMIT OFFSET这种访问方式在数据量小的时候几乎没存在感一旦碰上亿级数据越往后翻越灾难。数据库没办法“跳过”OFFSET行只能把前面的行全部扫描出来再丢弃。这篇文章把我从慢SQL排查、方案选型到最终落地的一整套打法完整复盘一遍包括游标分页、覆盖索引延迟回表、基于缓存的有序ID分页等主流方案以及一路踩出来的坑。适合后端开发、DBA以及所有被“数据量一大分页就卡”折磨过的同学对照参考。1. 深分页到底慢在哪里从执行计划到量化分析1.1 一条“看起来没毛病”的SQL先看一条再常见不过的分页SQLSELECT 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的锅三个隐藏成本深分页耗时其实是三层成本叠加的结果。第一层是扫描行数放大OFFSET1000000时至少100万条索引记录要被遍历这是无法绕开的基础开销。第二层是回表随机IO如果SELECT需要取二级索引之外的字段比如order_no、status、created_at每扫到一行满足条件的记录都要回聚簇索引再查一次完整行。扫描行数一大回表次数跟着翻倍而索引叶子节点在磁盘上是离散分布的每一次回表都是一次随机IO代价相当大。第三层是排序和临时表。当ORDER BY字段和索引顺序不一致或查询里带了GROUP BY、DISTINCTMySQL可能走filesort甚至生成临时表。深分页时临时表会变得很大内存装不下就往磁盘写整个查询就彻底回到了“慢”的领域。可以类比成在图书馆里按编号找书管理员必须从第一本开始数数到100万本才停下来如果每数到一本还要拆开封面看一眼自然更慢。几个隐藏成本叠加之后OFFSET稍大就会产生比业务逻辑多几个数量级的开销。1.3 模拟数据从百万到亿级的变化曲线为了直观我之前在一台普通虚拟机上做过一组相对测试单表按自增主键写入不重复数据模拟不同数据量级下的深分页表现。大致的相对耗时趋势如下表内数据规模LIMIT 20 OFFSET 0LIMIT 20 OFFSET 10000LIMIT 20 OFFSET 1000000100万行3ms12ms320ms5000万行4ms85ms3700ms1亿行6ms180ms6800ms这组数字受内存缓存、磁盘类型、索引设计影响很大不看绝对值只看相对变化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; -- 游标分页从上一页最后一条 id80001 开始 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_idNone, limit20): 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万行左右同样的OFFSET1000000两种写法的耗时和回表次数差异非常大大致整理如下表写法索引使用回表次数模拟耗时SELECT * ... LIMIT 1000000, 20二级索引后逐行回表约100万次5800ms覆盖索引延迟回表子查询使用覆盖索引20次880ms需要说清楚这个方案不是银弹。OFFSET仍然存在子查询仍然要扫描到一百万行索引记录只是避开了回表随机IO。如果业务允许用户翻到几百万页索引扫描本身也会成为瓶颈。我的经验是后管系统加上最大OFFSET限制比如超过20000页就提示缩小筛选范围再配合延迟回表工程上既简单又稳妥。3.4 改造三基于Redis有序ID的分页落地第三种方案适合读多写少、列表数据可以被预生成的场景比如报表、消息中心。核心思路是用一个后台任务或异步回调把满足条件的有序数据ID写进Redis的ZSetmember是业务IDscore是排序值。前端分页时直接在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或雪花IDvarchar主键也能用但比较和索引宽度都比整数差想用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或refrows要和实际数据量对得上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全部捞出来处理在深分页还没爆发前就把它按住比事后救火舒服得多。
平方复杂度无法被常规优化消除｜TaoToken 统一 Key 通道实测)
