MySQL深分页问题四种方案解析先说我最近遇到的一件真实的事。一个跑了快两年的订单管理后台平时接口响应都在200ms左右结果某天运营反馈“翻到第100页之后页面基本打不开”。我一看慢查询日志罪魁祸首就是那条用了LIMIT 100000, 20的查询。这就是典型的MySQL深分页问题——数据量上去之后越往后的页面越慢而且慢得毫无道理。这篇文章我就把这四种业界常用的优化方案掰开揉碎讲一遍包括它们各自适合什么场景、为什么能快、有什么坑以及我在实际项目中踩过的雷希望能让你少走点弯路。1. 深分页为什么慢从执行计划看LIMIT offset的代价1.1 慢的根本原因不是查询慢是“白扫描”太多很多同学第一次查深分页问题的时候会一脸懵WHERE条件一样ORDER BY也一样只是OFFSET从0变成了100000为什么性能差了上百倍这里必须先搞清楚MySQL执行LIMIT offset, size时的真实流程。假设你执行的是SELECT * FROM orders WHERE status 1 ORDER BY create_time LIMIT 100000, 20;MySQL的Server层不会“聪明”地直接从第100001条开始读而是老老实实从满足status 1的第一条记录开始一条一条读取一直数到第100020条然后把前100000条全部丢掉只把最后20条返回给客户端。也就是说深分页真正慢的原因在于offset越大MySQL需要“数”过去的行数就越多而这些行最终一条都不会返回给用户。它们是纯粹的无效工作。你可以把这个过程理解为在图书馆找一本书普通分页就像每翻一页就从头开始数书架上的书数到第100000本再往后拿20本而优化方案更像是直接在第100000本的位置放一个书签下一次直接从书签位置开始拿。同样是拿20本前者要数100020本后者只需要拿20本这是本质区别。1.2 回表隐藏最深的那把刀“多扫描一些行”还不是最致命的真正致命的是“回表”。当SELECT *需要返回所有列时如果ORDER BY create_time用到了二级索引InnoDB存储引擎会先用二级索引找到满足条件的记录拿到主键ID然后再用主键ID去聚簇索引主键索引里查完整行数据。这个“用主键ID再次查询聚簇索引”的过程就叫回表。回表不是走内存里的数组而是要重新走一遍B树的搜索路径。在数据量大、索引热度不高的情况下每次回表都可能伴随磁盘I/O。所以你可以想象一下一个深分页查询先扫描100020条二级索引记录再回表100020次其中99000多次的努力全部白费这种查询不慢才怪。1.3 排序字段没索引时比回表更可怕的filesort如果ORDER BY的字段不在任何索引里MySQL就得把所有满足条件的记录先查出来放到排序缓冲区sort buffer里做排序。缓冲区装不下就转成磁盘临时文件用归并排序的方式排完再取。这就是我们常说的Using filesort。这个代价有多大假如满足status 1的记录有500万条MySQL要把这500万条记录全部读出来、参与排序、再丢弃前100000条最后只返回20条。这种情况下无论你怎么优化LIMIT的写法都收效甚微因为瓶颈已经不在分页本身而是排序。所以后面要讲的四种方案有一个大前提你的排序字段必须能走索引。如果这一步没做到请先回到索引设计上去解决再回来看分页优化。1.4 先确认你的慢查询是不是深分页做优化之前先用EXPLAIN看一眼执行计划确认两个关键信息type是不是range或ref如果是ALL说明连索引都没用上这不是深分页的问题是索引失效的问题Extra里有没有Using filesort有的话优先处理排序rows估算值是否远大于你关心的数据量如果rows是几十万甚至上千万基本上就坐实了深分页的“无效扫描”。我见过不少人拿着深分页优化方案去套一个排序字段完全没索引的SQL结果换了三种写法都毫无提升最后发现根子就在filesort上。先诊断再开药方这个顺序不能乱。2. 方案一延迟关联——把回表行数从offsetsize压缩到size2.1 核心写法与执行过程拆解延迟关联Deferred Join是我个人最推荐的首选方案因为它改动量小、通用性强、提升效果非常明显。核心思路就一句话先用覆盖索引查出当前页需要的主键ID再用这些ID去关联原表取完整行。改造后的SQL长这样SELECT t1.* FROM orders t1 INNER JOIN ( SELECT id FROM orders WHERE status 1 ORDER BY create_time LIMIT 100000, 20 ) t2 ON t1.id t2.id ORDER BY t1.create_time;我们把整个查询拆成两步来看第一步子查询只查id列而且WHERE status 1 ORDER BY create_time如果恰好命中联合索引(status, create_time)那么子查询的全部数据都来自索引页不需要回表。这一步的代价是扫描100020条二级索引记录但索引记录的体积比完整行小得多可能只是完整行的十分之一甚至更小而且二级索引在物理上更紧凑缓存命中率也更高所以这一步很快。第二步INNER JOIN时只拿20个ID回表查聚簇索引回表次数从100020次锐减到20次。MySQL的优化器会把t1.id t2.id当成等值连接用t2作为驱动表每条t2记录去t1的主键上查一次20次主键查找的代价微乎其微。2.2 和普通子查询的细微差别有人可能会问“直接用WHERE id IN (SELECT id FROM ... LIMIT 100000, 20)不行吗”可以但有个细节需要注意IN子查询的结果集顺序不能保证和外层查询的ORDER BY一致所以外层必须再排一次序。而且某些MySQL版本对IN子查询的优化并不总是走“半连接”semi-join可能导致执行计划不如JOIN稳定。所以我更习惯用INNER JOIN的写法让子查询作为派生表驱动连接执行计划更可控。2.3 实测效果与适用边界我在一个1600万行的订单表上做过测试表结构大概是id主键、user_id普通索引、status普通索引、create_time普通索引数据量约1600万行。原始SQLSELECT * FROM orders WHERE status 1 ORDER BY create_time LIMIT 100000, 20;耗时大约2.3秒。延迟关联改写后SELECT t1.* FROM orders t1 INNER JOIN ( SELECT id FROM orders WHERE status 1 ORDER BY create_time LIMIT 100000, 20 ) t2 ON t1.id t2.id ORDER BY t1.create_time;耗时降到约120毫秒左右提升接近20倍。不过要说清楚这个数字跟你的数据分布、硬件配置、缓存命中率都有关系别拿着我的数字当标准答案。但量级上的差距是普遍成立的从秒级降到百毫秒级是常见结果。延迟关联的适用边界也很明确适合SELECT *返回大量列的查询适合排序字段有索引、但索引可能不是覆盖索引的场景适合offset非常大比如超过10000的场景对offset较小的浅分页延迟关联的收益不明显没必要为了前几页去改SQL。2.4 容易被忽略的前提子查询必须命中覆盖索引这个坑我踩过一次。有个项目用了延迟关联结果查询还是慢得离谱EXPLAIN一看子查询的Extra列里赫然写着Using filesort。为什么因为当时的排序字段是create_time但子查询的WHERE条件是user_id 123单列索引只有user_idORDER BY create_time没法利用索引的有序性MySQL只能先按user_id把所有记录捞出来再sort。这种情况下换成什么方案都没用。正确的做法是给(user_id, create_time)建一个联合索引让WHERE过滤和ORDER BY排序都走同一个索引。记住一个原则延迟关联里子查询的WHERE条件和ORDER BY字段最好能构成同一个联合索引的最左前缀关系。这是优化生效的前提否则就是换汤不换药。3. 方案二游标分页——从业务层面消灭OFFSET3.1 为什么“下一页”可以不要OFFSET如果说延迟关联是在“减少无效回表”上做文章那游标分页Keyset Pagination的思路更激进干脆不要OFFSET。传统的页码分页是“你给我页码我算OFFSET然后从头数”。但很多业务场景其实根本不需要随机跳页大家都是一个劲儿往下翻比如朋友圈、订单流、资讯列表、消息记录。这种场景下用户点击“下一页”时前端能拿着当前页最后一条记录的一个标记字段传给后端后端直接用这个标记做WHERE条件跳过前面所有的数据。最简单的游标分页是用主键IDSELECT * FROM orders WHERE id 100000 ORDER BY id LIMIT 20;因为主键ID是有序的MySQL直接通过主键索引定位到id 100000的位置然后顺序往下取20条。整个过程只需要扫描20条记录跟之前在100万条里摸爬滚打是完全不同的复杂度。3.2 单字段游标与多字段排序的写法实际业务里很少会只用主键排序更多是“按时间倒序”。比如订单列表要按create_time DESC展示。这种场景下游标分页就不能只记一个ID要把当前页最后一条记录的create_time和id都记下来SELECT * FROM orders WHERE create_time 2024-06-01 10:00:00 OR (create_time 2024-06-01 10:00:00 AND id 100000) ORDER BY create_time DESC, id DESC LIMIT 20;这个写法的逻辑是如果create_time比游标时间早直接取如果create_time相同那就用id来打破平局。这是应对排序字段可能存在重复值时的标准处理方式避免同一时间戳的数据被漏掉或重复返回。使用MySQL 8.0或MariaDB的话还可以用行值比较Row Constructor简化写法SELECT * FROM orders WHERE (create_time, id) (2024-06-01 10:00:00, 100000) ORDER BY create_time DESC, id DESC LIMIT 20;但注意行值比较在MySQL 5.7及以下版本虽然语法支持优化器对它的索引匹配能力有限容易出现type ALL所以老版本尽量还是老老实实写OR的展开形式。多字段排序的游标分页对索引要求很高上例需要联合索引(create_time, id)这样ORDER BY create_time DESC, id DESC才能完全走索引。很多同学只建了create_time单列索引结果游标分页跑起来一样慢就是这个原因。3.3 游标分页的业务约束不能跳页但体验更好游标分页最大的限制就是不支持随机跳页。用户没法直接输入“第500页”只能一页一页往下翻。这在大多数C端列表场景里完全没问题用户也不需要精确跳到某一页。而且从产品体验角度看游标分页还有两个额外的好处一是每页数据更稳定。传统OFFSET分页在翻页期间如果有新数据插入会出现某条记录被重复展示、或某条记录被跳过的问题。原因是OFFSET算的是物理位置新数据插入后位置整体后移。游标分页基于“上次看到的最后一条”来定位天然免疫这种偏移。二是性能与页数无关。无论你翻到第100页还是第10000页查询扫描的行数都只跟LIMIT size有关。这对用户的使用体验是质的提升后端也不用心惊胆战地监控“超过第几页就超时”。3.4 并发写入场景下的游标陷阱游标分页有一个需要小心的地方如果排序字段的值可能在查询过程中被修改比如订单状态变更导致记录不再满足WHERE条件就会造成“翻页漏数据”。举个例子用户正在翻“待支付订单”列表翻到第2页的时候第1页里某条待支付订单被人为取消了它不再满足status 0那么基于游标的下一页查询会直接跳过它后面那条记录吗不会跳过但下一页的起始游标仍然是上一页最后一条记录的位置所以那条被取消的订单之后的数据都能正常查到。真正的问题是如果被取消的记录恰好是游标所在的那条下一页的起始位置就不好定义了。实际中这个情况影响很小因为每次翻页时前端会把当前页最后一条的游标传给后端而那一条大概率不会刚好处在“被修改”的边界上。但如果你的业务对数据一致性要求特别高要么在游标字段上加上版本号要么接受这种极低概率的事件发生。我个人的经验是在订单、日志、流水这类“只追加、不改动”的数据上游标分页几乎不会出问题在状态频繁变更的业务上优先考虑延迟关联或下面要讲的方案四。4. 方案三子查询提前拿到锚点ID——最少改动解决80%问题4.1 基于主键连续性的写法这个方案在不少技术文章里被归为“子查询优化”写法如下SELECT * FROM orders WHERE id ( SELECT id FROM orders ORDER BY id LIMIT 100000, 1 ) ORDER BY id LIMIT 20;逻辑很直白先用一个子查询找到第100001条记录的主键ID锚点然后用id 锚点ID加上LIMIT 20取出这一页的数据。因为子查询只查id走的是主键索引或覆盖索引速度很快外层查询从锚点位置开始按主键顺序取20条也只扫描20条记录。这个方案的好处是SQL改动最小原来分页逻辑基本不动只加一个子查询就行非常适合快速止血。4.2 这个方案和延迟关联的本质区别这一方案和延迟关联看起来有点像都是“先查ID再查数据”但原理上有本质区别。延迟关联的子查询拿到的是一页20个ID然后通过JOIN回表取这20条完整数据而“锚点ID”方案是先拿到一个ID再从这个ID开始顺序往后扫20条。前者适合排序字段复杂、不能单纯依赖主键顺序的场景后者只适用于按主键或唯一自增字段排序的场景。再进一步说延迟关联的子查询里可以自由搭配WHERE条件、ORDER BY、多字段排序而锚点ID方案基本绑定了ORDER BY id。如果你的排序字段不是主键那这个方案的写法就要变形得用窗口函数或者别的技巧复杂度会上升性价比就不如延迟关联了。4.3 主键空洞会不会出问题这是个很多人纠结的问题如果表里的主键因为删除操作出现空洞比如ID是1、2、3、5、7、9那LIMIT 100000, 1拿到的锚点ID加上LIMIT 20会不会导致返回的条数不足20条会但影响通常不大。因为锚点ID是子查询算出来的“第100001条记录的ID”而不是“物理位置100001”。哪怕这之后有几条ID被删了从锚点ID开始继续往后按主键扫描取到的仍然是从第100001条位置之后连续存在的20条记录只是可能因为这中间有删除操作导致“物理页被跳过”而已。更准确地说锚点ID方案取到的20条跟传统OFFSET取到的20条并不完全一样它在ID有空洞时会发生“偏移”——可能取了第100001条往后物理上的第21到40条。但对于绝大多数业务用户根本感知不到这种差异。反过来如果你真的要求分页结果和OFFSET完全一致那这个方案就不适用了得回去用延迟关联。4.4 什么时候该选它而不是延迟关联我的选型经验是如果你的排序字段是主键且表里没有复杂的多条件排序需求直接用锚点ID方案简单粗暴如果排序字段是create_time之类的二级索引锚点ID方案就得先子查询查出第100001条记录的create_time再按(create_time, id)做范围查询写起来跟游标分页有点像不如延迟关联通用如果排序字段没有索引这两个方案都白搭先去建索引如果表数据经常删除、ID空洞严重建议用延迟关联它对数据连续性没有依赖。5. 方案四ID缓存与分页汇总表——适合大厂后台的工程化方案5.1 场景定位翻页自由度和查询速度兼得前面三个方案都绕不开一个限制要么不支持跳页要么在极端深翻页下还有一定的能力边界。但在很多后台管理系统里产品经理就是要求用户能跳到第10000页同时还要求3秒内出数据。这种需求本质上是反数据库的但业务又真实存在怎么办答案是别让数据库在查询时才去算分页而是提前把分页结果算好。这就是方案四的核心思路用缓存或辅助表提前维护好每一页的ID列表查询时直接取ID列表再回表拿数据。5.2 用Redis缓存的实现思路最简单的做法是用Redis的List结构。假设订单表的主键是自增ID我们可以在闲时跑一个定时任务扫描订单表把符合条件的所有主键ID按顺序写入Redis List比如LPUSH order_ids 100001 100002 100003 ...分页时前端传页码和每页大小后端用LRANGE order_ids (page-1)*size (page*size)-1取出一页ID再用这些ID去MySQL里IN查询取完整数据因为ID是主键回表20次非常快。这套方案在查询上几乎是无敌的不管翻到第几页Redis的LRANGE都是O(N)操作MySQL只负责按主键取20条两边都不累。但代价也很明显Redis里要存全量ID数据量大的时候非常占用内存。1000万条ID每条8字节加上Redis的对象头轻松超过500MB。所以这个方法适合数据量在百万级别、且ID分布不太夸张的场景。5.3 用MySQL汇总表替代Redis的取舍如果你不想引入Redis也可以直接在MySQL里建一张“分页索引表”CREATE TABLE order_page_index ( page_no INT PRIMARY KEY, page_start_id BIGINT, page_end_id BIGINT );定时任务扫描订单表把每一页的起始ID和结束ID提前算好INSERT INTO order_page_index (page_no, page_start_id, page_end_id) SELECT FLOOR((t.rn - 1) / 20) 1 AS page_no, MIN(t.id) AS page_start_id, MAX(t.id) AS page_end_id FROM ( SELECT id, ROW_NUMBER() OVER (ORDER BY create_time) AS rn FROM orders WHERE status 1 ) t GROUP BY FLOOR((t.rn - 1) / 20);查询某一页时先查汇总表拿到该页的ID范围再回订单表取数据SELECT * FROM orders WHERE id BETWEEN 100001 AND 100020 ORDER BY create_time;注意如果每页不是固定20条或者存在并发写入导致的ID动态变化BETWEEN的范围就不一定准确了。实际落地时我更推荐在汇总表里存当前页完整的ID列表用一种类似“分页快照”的方式维护虽然存储成本高一些但查询最稳定。5.4 数据一致性不是问题善用后台报表的时效性看到这里有人会问定时任务算出来的分页索引跟实时数据肯定对不上啊新增的订单怎么办对这个方案牺牲的就是实时性。但你要想清楚什么业务会要求你翻到第10000页大概率是运营后台的订单导出、财务对账、日志检索、用户行为分析这类场景。这些场景的数据本来就允许几分钟甚至几小时的延迟。你完全可以每5分钟跑一次定时任务把分页索引刷新一遍。用户拉到第10000页时看到的数据是5分钟前的快照这在后台系统里完全可接受。所以这个方案的本质是用最终一致性换取查询性能的确定性。它不追求每次查询都反映最新状态但保证任何一次深翻页都能在几十毫秒内返回。5.5 这个方案的适用边界与翻车点我在实际项目中见过有人把这个方案用在了C端用户列表上结果被喷惨了——用户刚下单列表却看不到谁也忍不了。所以这里必须划清边界适合后台报表、管理后台、日志检索、定时刷新的榜单不适合对实时性要求高的前台业务比如用户订单、消息列表、社交动态翻车点一定时任务计算全量ID的成本很高数据量过大时任务本身会拖垮数据库。建议只对“热数据”建索引比如只统计最近三个月的订单翻车点二频繁的INSERT和DELETE会让ID列表和实际数据产生较大偏移要监控偏移率超过阈值就重建索引。说实话方案四不是每个团队都用得上但只要用对了地方它是唯一能让你在千万级数据下“任意跳页不卡顿”的MySQL原生化方案。6. 四种方案怎么选成本、场景和血的教训6.1 一张对比表看懂四种方案方案核心原理支持跳页实时性索引要求适用场景改造难度延迟关联先在覆盖索引查ID再回表取数据支持高高排序WHERE需命中索引通用C端列表低游标分页用记录游标代替OFFSET不支持高高游标字段需索引无限滚动列表、消息流中子查询锚点ID先查锚点再范围扫描支持高低主键排序即可主键排序场景低ID缓存/汇总表预计算分页索引支持低低只需要主键后台报表、大数据翻页高6.2 不同业务场景的最优选择根据我自己的项目经验可以把业务拆成几类分别对号入座用户端订单列表、商品列表、内容流数据量几百万以内优先游标分页如果产品不接受“没有页码”就延迟关联。别用锚点ID方案因为你大概率按create_time排序而非主键后台管理系统的通用列表数据量几百万到千万通常允许跳页用延迟关联最稳妥。如果排序字段刚好是主键锚点ID方案也很香运营/财务/日志类的超深翻页用户可能会翻到几百页甚至几千页之后延迟关联虽然快了但仍然要扫描几万条ID不够极致。这时候上ID缓存或汇总表方案对外API接口的列表如果接口调用方要求稳定可预期的响应时间强烈建议游标分页因为它能让查询时间基本恒定。6.3 无论选哪种索引设计都是及格线前面反复提到一个点这里单独拎出来说没有合适的索引这四种方案全部失效。延迟关联需要“WHERE字段 ORDER BY字段”组合成联合索引游标分页需要游标字段或多个字段命中索引锚点ID方案在ORDER BY id时天然走主键ID缓存方案虽然只查主键但生成索引的定时任务也要扫全表。所以动SQL之前先想清楚三件事排序字段是什么它有没有索引WHERE条件里哪个字段选择性最好能不能跟排序字段组成联合索引加了联合索引之后会不会影响写入性能我曾经在一个写多读少的日志表上为了优化分页加了一个五字段的联合索引结果写入性能直接下降了30%最后不得不砍掉两个字段。索引不是越多越好更不是越宽越好这一点在分页优化时尤其要克制。6.4 我踩过的坑优化器改写、版本差异、深浅分页混用先说优化器改写的问题。MySQL 5.7及以上的优化器在某些场景下会把IN (SELECT ... LIMIT ...)改写为EXISTS导致子查询无法利用LIMIT先缩小结果集性能反而更差。这也是我推荐用INNER JOIN而不是IN的原因——JOIN派生表的执行计划相对稳定不容易被优化器引入奇怪的改写。再说版本差异。MySQL 8.0对派生表的优化derived_merge默认开启后可能会把延迟关联的子查询展开合并进主查询这反而会破坏“先查ID再回表”的意图。遇到这种情况需要给子查询加SQL_NO_CACHE或者换一种写法强制物化。我自己在8.0.18上就遇到过子查询被merge后执行计划从DEPENDENT SUBQUERY变成了全表扫描折腾了半天才发现是优化器捣的鬼。最后说深浅分页混用。有些系统用同一套SQL服务所有分页请求不管第1页还是第10000页都走深分页优化。其实对于前几页的浅分页这些优化的收益基本是零反而可能因为多一层子查询导致略慢。更合理的做法是浅分页比如offset小于1000走原始查询深分页offset超过阈值走延迟关联或游标分页。用中间件或者MyBatis拦截器在SQL层做这个分流对业务方完全透明。6.5 还有一个终极建议限制翻页深度说了这么多方案最后我想泼一盆冷水从产品层面限制翻页深度往往比任何技术优化都有效。Google搜索结果翻到第20页之后会提示“换个关键词吧”淘宝的商品列表翻到第100页之后基本就到头了很多后台系统干脆只保留前100页的翻页按钮再往后就强制用条件筛选。这个思路背后的逻辑是深分页本身是反人性的需求用户真正想要的不是第10000页的数据而是更精确的筛选条件。与其在SQL上死磕不如联合产品经理把翻页改成“加载更多 筛选 搜索”让用户通过条件把数据量压到几千以内这时候传统分页性能就非常好了。什么时候需要“不限深度翻页”只有财务对账、全局导出这类后台场景才真正需要。而这些场景用方案四的预计算索引刚刚好。我个人的体会是深分页没有银弹它更像一个需要组合出招的问题前端交互上能限制就限制SQL上能走索引就尽量走索引极端场景再用预计算兜底。先把业务形态想清楚再决定优化方案往往比盲目套用某个“最牛写法”更有效。希望这篇总结能帮你在下次遇到类似问题时少走一些我当年走过的弯路。