MySQL超大分页查询优化：从深翻页原理到延迟关联与书签法实战

前几天刚帮同事解决一个线上问题后台管理系统点击第10000页接口直接超时。查看慢日志就是一条简单的 MySQL 分页查询LIMIT 199980, 20。这个场景我相信做后端的人都不陌生——数据量过百万、偏移量过大之后分页查询会肉眼可见地变慢甚至拖垮数据库。今天这篇就系统聊聊 MySQL 超大分页的处理思路从底层原理到实战方案再到我踩过的坑。1. 为什么超大分页会慢先看MySQL翻页的底层原理1.1 一个被忽略的事实LIMIT 1000000, 20到底执行了什么我们先看一条最简单也最典型的分页SQLSELECT * FROM t_order ORDER BY id LIMIT 1000000, 20;语法上没什么问题但执行过程很坑InnoDB需要从第一行开始顺着索引或全表扫描挨个数出1000020行再丢掉前1000000行最后才把第1000001到第1000020行返回给客户端。也就是说你以为只是拿了20条实际数据库已经吭哧吭哧扫描了超过一百万行。偏移量越大扫描行数越多耗时自然水涨船高。很多同学在这里有一个误区以为索引能像数组一样直接跳到偏移量位置。MySQL的B树索引结构确实能快速定位到某一具体KEY但LIMIT的offset并不是KEY它只是一个行数计数。数据库为了拿到第1000001行的位置只能老老实实地遍历前面的行中间没有任何跳过的能力。除非你显式给出一个可以定位的边界条件比如id 1000000否则MySQL就只能做这种数行数的笨功夫。我们可以用EXPLAIN验证一下。对一个有索引的字段执行EXPLAIN SELECT * FROM t_order ORDER BY id LIMIT 1000000, 20通常会看到typeAll或rangerows会达到百万级。这里有个容易踩的细节如果只查id优化器可能走覆盖索引但一旦SELECT *就必须回表取每行的完整数据。offset越大回表次数越多IO开销也越可观。1.2 索引在超大分页场景下的局限性给order by字段加索引不就行了这是第二大误区。索引确实能让排序不再使用filesort但offset带来的数行数问题并没有消失。MySQL依然会遍历索引叶子节点数到1000000行之后才开始取数据。只不过省掉了排序这一步扫描本身照旧。更麻烦的是回表。假设你在create_time字段上建了索引分页SQL是SELECT id, order_no, amount, create_time FROM t_order ORDER BY create_time LIMIT 1000000, 20;执行计划可能先是走create_time索引顺序扫描收集到1000000行主键之后再逐个回表读取完整记录。这里的回表次数和offset计算量基本是同一量级也就是约100万次随机IO。即便每行数据不大累积起来的延迟也足以让接口失去可用性。所以在超大分页面前索引并不是银弹。真正有效的优化要么减少回表次数要么从根上消除offset扫描。2. 动手之前先判断你的业务是不是伪需求2.1 区分真实分页与产品假想99%的用户不会翻到1000页不管技术方案多优雅先回答一个问题业务真的需要让用户翻到第几万页吗真实业务里用户连前10页都很少看完。常见的后台列表、App信息流用户只会往下翻几页或者使用搜索条件过滤。真正会点到第1000页的要么是数据导出场景要么是爬虫在刷接口要么是产品经理拍脑袋做出来的按页码跳转交互。对于大多数后台系统直接把最大页码限制在100页内80%的超大分页问题就消失了。我见过不少团队一上来就埋头优化SQL却没人质疑为什么要允许用户访问第10000页。这是典型的用技术解决业务设计问题成本高不说往往还治标不治本。产品侧把跳页改成加载更多后端用游标式翻页用户体验反而更好数据库压力也小很多。2.2 多大的offset需要优化一个经验阈值超大到底指多少没有一个绝对标准但根据我的实际经验可以给一个参考线offset在1000以下基本不需要折腾普通分页完全扛得住offset达到1万到10万之间如果表数据量在百万级开始考虑延迟关联offset超过10万甚至百万必须用书签法或者产品层面限制页码否则任何一个深翻页请求都可能拖慢整个实例。为什么是10万这个量级因为百万行扫描加百万次回表在普通SSD上至少需要几百毫秒到几秒如果并发一高CPU和IO会迅速被打满。比如InnoDB默认的缓冲池如果不够大每次深翻页都会触发大量物理读直接挤掉正常业务的缓存命中率这是很多数据库抖动事故的根源。所以遇到超大分页变慢第一件事不是改SQL而是打开慢查询日志看看到底是哪些页码、哪些接口在请求。先搞清楚量级再决定优化策略。3. 四类主流优化方案原理、SQL与适用边界3.1 延迟关联覆盖索引取主键再回表拿数据延迟关联是MySQL社区最经典的分页优化手段。核心思想是先用覆盖索引拿到limit偏移后真正需要的20个主键再用这20个主键去原表回表取完整数据。SELECT o.* FROM ( SELECT id FROM t_order ORDER BY id LIMIT 1000000, 20 ) tmp JOIN t_order o ON tmp.id o.id;为什么快子查询里只查id而id是主键这个查询可以完全走主键或覆盖索引不需要回表。虽然依旧要扫描100万行主键但每一行只返回一个int占用的内存和IO比SELECT *小得多。扫描完拿到20个id之后再通过主键精确回表取20行完整数据回表次数从100万次降到20次。实测中这条SQL的耗时通常能从秒级降到几十毫秒提升幅度非常明显。要注意的是如果子查询的排序字段不是索引或者WHERE条件过滤后仍然要filesort延迟关联的效果会大打折扣。它最擅长的是排序字段有索引 只需要返回少量行的场景。3.2 书签法/Seek Method把随机翻页变成单向翻页延迟关联虽然快但还是要扫描100万行主键。书签法更进一步从算法上直接消除offset。核心思路每次翻页时把上一页最后一条记录的某个唯一字段通常是主键id也可以是时间戳作为边界条件下一页只查比这个边界大/小的记录。-- 上一页最后一条id 1000000 SELECT * FROM t_order WHERE id 1000000 ORDER BY id LIMIT 20;这条SQL因为有了id 1000000的边界MySQL可以直接走主键索引定位到1000001附近然后顺序取20条连数行数这一层都省掉了。在千万级数据下耗时通常可以到毫秒级和普通小offset分页几乎没区别。但书签法有两个先决条件一、排序字段必须是唯一且严格有序的所以最常用的是主键id二、翻页方向固定不能随机跳页。想从第100页跳到第5000页书签法做不到因为浏览器/客户端没有记录第5000页的上一页边界。所以它更适合加载更多型列表比如信息流、评论区翻页、时间线。3.3 子查询内联与临时表不同写法之间的代价差异有人习惯把分页逻辑写进子查询比如SELECT * FROM t_order WHERE id IN ( SELECT id FROM t_order ORDER BY id LIMIT 1000000, 20 );看起来和延迟关联很像但在某些MySQL版本里优化器可能不会把IN子查询的limit下推而是先物化成一张临时表再与原表做半连接。临时表的代价可能比覆盖索引子查询还高。相比之下JOIN写法通常更稳定因为优化器会把tmp结果集当成派生表配合主键连接效率更高。这里想强调的是网上流传的优化SQL不一定适合你的MySQL版本和数据分布一定要用EXPLAIN看实际执行计划再决定用哪种写法。我甚至遇到过同样的SQL在一个5.7实例上走子查询很顺畅在另一个8.0实例上却因为物化策略不同反而更慢的情况。3.4 强制走索引与覆盖索引极简版不是万能的还有一种很朴素的思路如果业务只需要查询少数几个字段能不能建一个覆盖所有需要字段的联合索引让分页查询完全避免回表比如列表页只需要id, order_no, create_time, amount四个字段可以把索引建为ALTER TABLE t_order ADD INDEX idx_page_cover(create_time, id, order_no, amount);这样SELECT id, order_no, amount, create_time FROM t_order ORDER BY create_time LIMIT 1000000, 20可以直接从索引里返回结果不需要回表。如果是简单的两三个字段场景这种覆盖索引确实有用能够显著降低单条查询的IO。但别神话它。联合索引的字段顺序、排序方向、WHERE条件匹配规则都很讲究一旦业务字段增加覆盖索引就失效。而且超大分页时就算不回表索引扫描行数依然是百万级内存和CPU开销依旧不可忽视。所以覆盖索引适合作为锦上添花不适合作为深翻页的根治方案。4. 千万级数据实测不同方案的耗时和扫描行数4.1 测试环境与数据准备为了让你心里有个谱我把几种方案在一张千万级表上跑了实际测试。环境是MySQL 8.0InnoDB普通SATA SSD8核心机器表结构简化如下CREATE TABLE t_order ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, user_id BIGINT NOT NULL, amount DECIMAL(10,2) NOT NULL, create_time DATETIME NOT NULL, KEY idx_create_time (create_time) ) ENGINEInnoDB;用存储过程插入1000万行模拟数据保证id连续、create_time基本连续但不完全单调。之后关闭查询缓存统一执行深翻页offset取1000000每页20条对比各方案的表现。这种测试环境不算豪华但足够反映普通业务数据库的实际情况。你可以在自己机器上跑重点不是绝对值而是方案之间的相对差异。4.2 四种方案的执行计划与耗时对比测试结果如下方案SQL核心写法扫描/处理行数平均耗时普通limitLIMIT 1000000,201000020行 回表1000020次约4.8s延迟关联子查询取id后JOIN原表1000020行索引扫描 回表20次约0.35s书签法WHERE id 上一页id LIMIT 2020行约0.003s覆盖索引联合索引覆盖select字段1000020行索引扫描无回表约0.9s普通limit的耗时最大因为SELECT *要回表100多万行而且每次回表都是随机IO磁盘读放大非常严重。即使全表数据都进了缓冲池CPU遍历和行拷贝的开销也很大。延迟关联把回表次数压缩到20次但底层依然要扫描100万行主键所以耗时能降到几百毫秒。书签法直接从索引定位边界扫描行数约等于返回行数自然快到离谱。覆盖索引的表现介乎两者之间省掉了回表但索引本身也有100万行要过。可以理解为用空间换时间索引体积越大遍历成本越高。如果联合索引里塞了太多字段效果还会打折扣。4.3 从实验结论到选型建议从数据上可以直接得出选型倾向能接受不能随机跳页的产品优先用书签法性能和交互都最理想必须保留页码跳转业务又希望稳定使用延迟关联改造量小、收益大如果列表只需返回少数固定字段可以叠加覆盖索引让延迟关联子查询也走覆盖索引普通limit只适合offset不高的场景比如页数小于几百一旦超过经验阈值直接换方案。需要注意的是这些数据是在单表单主键、排序字段有索引的理想环境下测出来的。现实业务里还有多表join、where条件复杂、排序字段不唯一等问题不要机械照搬结论。5. 复杂排序场景与边界情况处理5.1 排序字段不是主键时书签法怎么写书签法最常见的问题order by 的不是id而是create_time。如果create_time允许重复单纯的WHERE create_time last_time可能漏数据。解决办法是复合边界SELECT * FROM t_order WHERE (create_time last_time) OR (create_time last_time AND id last_id) ORDER BY create_time, id LIMIT 20;这里以create_time, id作为联合排序键。因为id是主键最终能保证全局唯一有序。只要上一页记录了我们最后一条的create_time和id下一页就能准确接上。对应的索引建议建为(create_time, id)这样排序和过滤都能走索引。但这里有一个隐性问题如果下单频率很高同一秒内出现大量相同create_time的订单那create_time的区分度就会下降联合边界的多条件判断会变得复杂。如果业务上只需要粗略翻页也可以接受少量重复/漏掉但作为严谨的后端不该这么做。更稳妥的方案是给表增加一个连续的唯一业务键比如序号或者干脆坚持用自增主键排序。5.2 多字段排序、重复值与并发写入带来的坑当order by 字段有多个时书签法的边界条件会膨胀成一个复数条件。例如ORDER BY status, create_time, id对应的边界条件要写成WHERE (status last_status) OR (status last_status AND create_time last_create_time) OR (status last_status AND create_time last_create_time AND id last_id)每多一个排序字段边界的OR条件就多一层写起来繁琐而且索引设计难度剧增。所以我的建议是真的遇到这种复杂排序别硬用书签法退回延迟关联反而更省心。另一个坑是并发写入。使用自增id做书签的时候如果中间有事务回滚id会出现空洞。这不算问题因为id last_id只要求之后插入的记录id更大空洞不影响正确性。真正要注意的是排序字段是create_time如果某条记录因为历史数据导入或人工修正而插入了一个过小的create_time书签法就可能漏数据。所以生产环境里书签法对数据质量的要求比想象中高。5.3 业务侧兜底页数限制、缓存与产品交互改造技术优化不是全部。一套完整的超大分页方案通常还会搭配产品和架构侧的兜底策略前端页码控件限制最大页数比如只能翻到100页超出的提示数据过多请使用筛选条件后端接口对offset做校验超过阈值直接返回错误码或强制使用搜索条件热点列表页用Redis等缓存前面N页命中后完全打到缓存层导出类场景不走分页接口改为异步任务分批拉取避免把列表接口压垮。我参与过的项目中最立竿见影的措施其实是第一项限制页码。因为真正需要遍历全量数据的业务微乎其微一旦产品上砍掉跳转到1000页这个入口数据库的深翻页压力瞬间消失。技术方案再好也不如从源头少让数据库干傻活。6. 生产环境实战复盘从5s到0.05s的优化过程6.1 一个完整排查链路慢SQL日志、explain与优化最后分享一个真实的排查案例。当时线上后台的订单列表接口某天下午突然连续告警慢查询日志里全是同一条SQL按创建时间倒序取第几十万偏移量的订单耗时五六秒。第一反应是加索引但后来发现created_at上已经有索引SQL的EXPLAIN显示type是rangerows接近百万Extra里还带着Using filesort。这就很奇怪索引存在为什么还filesort后来仔细分析才发现SQL里select的是一个多表join的结果其中一个join字段的排序规则和主表不一致导致优化器放弃索引改用临时表排序。先把问题收窄改成单表深翻页后执行计划恢复正常。随后我采用了延迟关联把原本的表连接拆成两步先查出20个主键再join其余表。由于主键定位精确到行关联行数从百万级降为20行整个接口耗时从5s降到0.35s。再往后产品确认用户其实不需要随机跳页前端改成了加载更多按钮后端切换到书签法接口耗时最终稳定在50ms左右。这个案例说明深翻页慢的原因很多有可能是写法问题、索引失效、join顺序问题甚至是排序规则差异。定位阶段最重要的一步就是慢SQL日志配合EXPLAIN先搞清楚瓶颈在扫描、回表还是排序再去选择对应优化方案。6.2 常见优化误区和容易被忽略的细节实操中我见过不少翻车操作列几个典型盲目给所有排序字段加索引索引不是越多越好联合索引的顺序写反了SQL根本不会走只优化单条SQL忽略接口里其他的N1查询分页突然变快但接口整体还是慢因为每条记录又单独查了子表order by字段和limit字段不一致导致索引失效ORDER BY name LIMIT 1000000, 20如果name上有索引但id不是连续递增执行计划可能是索引全扫描加回表忘记在低峰期变更索引大表加索引可能锁表DDL最好使用pt-osc等在线工具否则优化还没上线业务先挂了测试环境数据量只有几万条用户环境几千万条方案效果天差地别。测深翻页优化测试数据量必须接近生产量级。这些小细节看起来琐碎但在生产环境里往往是决定优化能否落地的关键。我常跟团队说SQL优化不是写完一条语句就结束而是要从执行计划、索引设计、业务交互、数据规模四个维度一起考虑。6.3 深翻页问题的最好归宿是不需要翻那么深总结我这几年的经验MySQL超大分页的优化优先级从高到低是——先砍业务需求再改技术方案最后才考虑加机器。如果产品不允许限制页码那就用书签法或延迟关联把SQL本身优化到位如果连书签法也满足不了比如必须支持随机跳页且排序复杂恐怕就要考虑换存储引擎或者引入搜索引擎了但那已经超过分页优化的范畴了。从执行层面看我最推荐每个后端团队在项目里预设一个分页规范默认限制最大页数列表接口优先支持游标翻页深翻页需求必须经过DBA或资深研发评审。这样很多性能问题在设计阶段就被挡掉了不需要等线上炸了再来救火。希望这篇内容能帮你把MySQL超大分页的来龙去脉理清楚。如果你也遇到过更诡异的深翻页问题欢迎按照上面的排查链路自己试一遍多半能定位到问题所在。