MySQL分页慢这个问题基本是所有做后端开发的人迟早都会撞上的。很多项目一开始数据量小LIMIT 10 OFFSET 20这种查询怎么写都秒回等表里数据涨到几百万、上千万行某天线上突然报慢查询DBA把慢日志捞出来一看排在最前面的十有八九就是这种深分页语句。这时候你才意识到原来分页不是加个 LIMIT 就行了这么简单。这篇文章我打算把 LIMIT OFFSET 为什么慢这件事彻底掰开来讲从 MySQL 底层的执行机制开始再到具体的优化方案对比最后附上我自己在实际排查中积累的定位手段和避坑经验。无论你是刚接触数据库没多久的新人还是已经被深分页折磨过的老手这篇内容应该都能给你一些直接能用的东西。1. 分页查询的工作原理与慢的根本原因1.1 LIMIT OFFSET 的执行机制先看一条最常见的分页查询语句SELECT id, title, content, author, create_time FROM articles ORDER BY create_time DESC LIMIT 10 OFFSET 5000;表面上看这条语句的含义是从第 5000 行开始取 10 行很多人会下意识地认为 MySQL 是直接定位到第 5000 行然后往后读 10 行就像翻书一样翻到第 500 页接着看就行。但 MySQL 内部的实际执行过程完全不是这样它没有随机跳转行号的能力。标准的执行路径是从存储引擎层读取满足WHERE条件的第一行记录按照ORDER BY指定的字段进行排序这里可能用到索引避免额外排序也可能需要 filesort从排序结果的第一行开始数把前 5000 行全部丢弃即跳过 OFFSET 数量的记录从第 5001 行开始取 10 行返回给客户端。也就是说MySQL 执行这条语句时实际扫描并处理的行数大约等于OFFSET LIMIT行这里是 5010 行而不是 LIMIT 子句里写的 10 行。OFFSET 越大MySQL 需要数过去的行就越多处理成本自然随之线性上升。这个跳过多余行的行为本质上是 MySQL 为了支持标准 SQL 语法而做出的实现取舍。LIMIT OFFSET 这种分页方式不需要记录任何游标状态每次查询都是无状态的完全由客户端传入偏移量所以实现起来最简单。但这恰恰也是它性能问题的根源MySQL 无法直接跳到偏移位置只能老老实实地逐行计数。1.2 为什么 OFFSET 越大越慢深分页的慢主要体现在三个层面。第一个层面是回表成本。如果查询的字段列表里包含了非索引列比如上面例子里的content字段MySQL 需要先用索引定位到满足条件和排序的索引项再根据主键回表读取完整行数据。二级索引中存储的只有索引列和主键值content这种大字段必须靠回表才能拿到。关键点在于回表的动作不是只对最终返回的 10 行执行的而是对跳过的那 5000 行也要执行。因为 MySQL 在完成排序和计数之前并不知道这 5000 行里哪些会被最终留下它必须读出完整的行数据参与排序或过滤。所以 OFFSET 越大无效回表次数越多随机 I/O 消耗就越严重。第二个层面是排序开销。当ORDER BY字段没有合适的索引时MySQL 需要把符合条件的所有行都放到 sort buffer 里做 filesort。如果数据量超过 sort buffer 容量还会产生临时文件走归并排序的多路合并流程大量占用磁盘 I/O。深分页场景下参与排序的数据基数往往非常大尤其是只取最后几页数据时相当于为了拿 10 条记录对几十万甚至上百万行做了一次完整排序。第三个层面是内存和临时表。在某些复杂查询场景下比如包含了多表 JOIN或者ORDER BY的字段不在同一张表里MySQL 可能会生成内部临时表来存储中间结果。临时表如果太大会从内存临时表转成磁盘临时表这个转换过程本身就非常耗时。再加上 LIMIT 是在临时表生成之后才截断的所以前面所有的 JOIN、排序、去重成本一分都不会少。在实际业务中最典型的场景就是用户翻页翻到很后面比如后台管理系统的列表用户一页 20 条翻到第 500 页OFFSET 就是 10000。这时候单条查询的耗时可能比翻第一页时慢了几十倍但用户看到的数据量其实还是一样的 20 条。这种体验落差就是深分页最直接的痛点。2. 深入剖析经典的分页性能瓶颈场景2.1 深分页为什么会拖垮整个库深分页带来的问题不只是某一条查询变慢这么简单。线上真实场景中我见过好几次因为一个深分页接口导致整个数据库 CPU 飙升、连接池被打满的事故。过程一般是这样某运营后台的列表页被人翻到了很后面单次查询耗时从原来的 50ms 涨到了 5 秒以上。接口超时后前端自动重试重试又超时再次重试。原本只需要一条慢查询结果在同一时间点涌进来几十条一模一样的深分页查询。每条查询都在做大量的回表和排序CPU 和磁盘 I/O 瞬间被打满其他正常的业务查询也跟着变慢最终引发雪崩。所以判断一个分页方案的好坏不能只看它能不能返回正确结果还得看它在高并发场景下的资源消耗。如果一条查询消耗的资源相当于普通查询的几百倍那它迟早会成为系统的短板。2.2 排序字段无索引时的 filesort 放大效应绑定到排序字段上LIMIT OFFSET 还有一个非常容易被忽视的坑。假设有一张订单表orders数据量 300 万行查询语句是这样SELECT id, order_no, user_id, amount FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 10 OFFSET 200000;如果create_time上没索引MySQL 只能先把所有status 1的行查出来全部丢进 sort buffer 排序再跳过 20 万行取 10 行。即使create_time上有索引但由于查询条件是status 1优化器可能认为通过索引扫描status再回表效率更高排序还是得走 filesort。这种场景下真正的瓶颈已经不在 OFFSET 本身而在于 filesort。OFFSET 越大排序结果集越大需要排序的数据量呈近似线性增长最终导致响应时间不可控。有时候你会发现把 OFFSET 从 1000 调到 100000查询耗时的增长甚至超过线性比例这就是因为触发了临时文件归并排序从纯内存计算降级成了磁盘 I/O。2.3 联合索引设计与 WHERE 条件的匹配陷阱很多开发者都知道分页慢要走索引优化但设计联合索引时容易掉进另一个坑索引字段顺序和 WHERE 条件的匹配关系没理清。举个例子一个常见需求是按用户查他的操作记录按时间倒序分页SELECT id, user_id, action, created_at FROM user_logs WHERE user_id 1001 ORDER BY created_at DESC LIMIT 10 OFFSET 0;这种情况建(user_id, created_at)联合索引是标准答案。因为user_id能快速定位到该用户的记录created_at在联合索引中天然有序ORDER BY 可以直接用索引顺序输出连排序都省了只需要扫描目标用户那一小段索引区间即可。但同样这个表如果加一个筛选条件比如查男性用户的操作记录且性别字段存在users表里需要 JOINSELECT l.id, l.user_id, l.action, l.created_at FROM user_logs l JOIN users u ON l.user_id u.id WHERE u.gender 1 ORDER BY l.created_at DESC LIMIT 10 OFFSET 5000;这时候联合索引就派不上用场了。优化器往往会先找到所有gender 1的用户再去user_logs里找他们的记录做 JOIN最后排序。排序结果集是所有男性用户的所有日志而不只是一个用户的日志规模完全不同。这时候 OFFSET 5000 意味着 MySQL 要排序的记录数可能是几十万条慢也就不奇怪了。所以设计索引之前一定要想清楚最核心的查询模式是什么是单用户查自己的记录还是跨用户聚合查询。两种模式的索引策略截然不同。3. 实战方案从延迟关联到游标分页3.1 延迟关联减少无效回表的有效手段延迟关联Deferred Join的核心思路是先用覆盖索引快速定位到需要返回的主键 ID再用主键 ID 关联回原表获取完整行数据。这样回表的次数就从OFFSET LIMIT次降到了 LIMIT 次。拿上面的文章表举例原查询是SELECT id, title, content, author, create_time FROM articles ORDER BY create_time DESC LIMIT 10 OFFSET 5000;改写为延迟关联SELECT a.id, a.title, a.content, a.author, a.create_time FROM articles a INNER JOIN ( SELECT id FROM articles ORDER BY create_time DESC LIMIT 10 OFFSET 5000 ) tmp ON a.id tmp.id;子查询内部只查id和create_time两个字段而(create_time, id)刚好可以通过联合索引或者二级索引直接覆盖不需要回表。MySQL 在索引上完成排序后直接数到偏移位置取出 10 个主键 ID再用这 10 个 ID 去原表回表。整个过程中无效回表被完全消除。我实际测试过一张 800 万行的表OFFSET 200000 LIMIT 20的情况下原查询耗时约 3.8 秒改成延迟关联后降到 0.4 秒左右提升了将近 10 倍。如果再把外部查询改成先查主键再批量回表也就是分成两步在应用层拼接性能表现还会更好。不过延迟关联也有前提内层子查询的排序字段必须有索引支撑。如果ORDER BY字段不能走索引内层子查询照样要 filesort延迟关联带来的提升就会大打折扣。所以在设计阶段就把排序字段纳入索引比事后再想优化方案要省力得多。3.2 游标方案适合稳定排序规则的场景如果业务场景能接受翻页时数据不实时变化这种约束那游标分页是解决深分页问题的最优雅方案。游标分页的核心思想是客户端不再传入 OFFSET而是传上一页最后一条记录的排序值作为查询条件MySQL 直接从该值之后的位置开始扫描。假设上一页最后一条记录的create_time是2024-06-01 12:00:00ID 是102400下一页查询就是SELECT id, title, content, author, create_time FROM articles WHERE (create_time 2024-06-01 12:00:00) OR (create_time 2024-06-01 12:00:00 AND id 102400) ORDER BY create_time DESC, id DESC LIMIT 10;这里为什么要带上id 102400这个条件因为create_time字段本身可能重复如果只按时间判断会出现把上一页最后一条数据重复取出来的情况。用(create_time, id)二元组作为游标就能保证每条数据的排序位置是唯一确定的。这种方案的性能优势非常明显理论上无论翻到多深MySQL 都只需要沿着索引往后扫描 10 条记录扫描量永远是常量级别。实测 800 万行数据翻到第 10 万页和翻到第 2 页耗时几乎没有差别。不过游标分页有它的适用边界。它不支持跳转到任意指定页的功能用户想直接输入页码跳到第 500 页游标方案就束手无策了。所以在设计接口时产品形态就很关键如果是信息流、动态列表等无限加载场景游标分页是完美的如果是传统后台管理系统的页码跳转还是要另想办法。3.3 用主键范围代替 OFFSET还有一种常用于后台列表的思路是主键游标。如果业务表中存在自增主键并且查询条件里通常带有某些过滤维度可以用WHERE id 上一页最大ID来切分数据。比如用户操作日志表按 ID 倒序排列分页SELECT id, user_id, action, created_at FROM user_logs WHERE user_id 1001 AND id 678901 ORDER BY id DESC LIMIT 20;由于主键本身就是聚簇索引id 678901加上user_id 1001的过滤条件能快速定位到目标位置MySQL 沿着索引往后扫 20 行即可整个查询走索引扫描复杂度极低。这种方案的局限也很明显它要求数据按主键顺序天然和业务排序规则一致。如果经常按create_time倒序但create_time和主键递增顺序不一致比如通过批量导入的历史数据那id 上页ID就不是按时间倒序的语义了结果会错。因此使用之前一定要确认排序逻辑和主键单调性的关系。3.4 分页方案对比与选型参考我在实际项目中用过的分页方案可以整理成一张对比表方便你在选型时做参考方案性能表现支持跳页适用场景实现复杂度LIMIT OFFSETOFFSET 越大越慢支持数据量小、翻页浅低延迟关联大幅减少回表仍有 OFFSET 扫描支持中等数据量索引不覆盖查询列中游标分页恒定 O(LIMIT)不支持信息流、无限滚动中主键范围分页恒定 O(LIMIT)不支持自增主键、单调排序场景低禁用深分页接口层拦截限制页码后台管理可接受约束低我个人的选型习惯是前台用户可见的列表优先游标分页后台管理系统如果产品不接受只能上下页就用延迟关联加最大页码限制比如限制最大查询到第 200 页超出后提示用户缩小筛选范围。4. 常见问题排查与避坑经验4.1 怎么用 EXPLAIN 定位分页慢的根因遇到分页慢第一步不是看代码而是先看执行计划。拿到慢查询语句后在 SQL 前面加个 EXPLAIN主要看这几个字段type如果出现ALL说明是全表扫描大概率是索引缺失或没建好key实际使用的索引如果为 NULL说明没有可用的索引rows预估扫描行数这个值如果远超 OFFSET LIMIT说明过滤条件没有有效利用索引Extra如果出现Using filesort说明排序没走索引出现Using temporary说明用了临时表出现Using index condition说明走了索引下推这些信息能帮你快速判断瓶颈在哪一步。用 EXPLAIN 分析上面的深分页语句时我经常会看到类似这样的输出id select_type table type key rows Extra 1 SIMPLE articles index create_time 800000 Using index这里的type index表示扫描了整个索引rows 800000说明 MySQL 预期要扫 80 万行索引记录。这已经足够说明问题即使ORDER BY create_time走的是索引但为了跳过 OFFSETMySQL 依然要遍历大量索引记录。另外别忘了看optimizer tracing。在某些复杂场景下EXPLAIN 给出的预估行数不一定准确开启 optimizer trace 可以查看优化器具体的决策过程包括它为什么选择某个索引、临时表大小预估等。虽然输出比较冗长但在极端疑难问题上很能帮上忙。4.2 翻页出来的数据重复或者缺失是什么原因这是一个经常出现在分页优化之后的问题尤其是从 LIMIT OFFSET 改成游标分页后更容易踩中。核心原因在于排序字段的不稳定性。LIMIT OFFSET 方案下如果两行数据的排序字段值完全相同MySQL 不保证它们的返回顺序是固定的。比如ORDER BY create_time DESC而表里有多条记录的create_time是同一秒那第一页和第二页的分界线就可能不一样造成数据重复或遗漏。解决办法就是给排序补一个绝对唯一的二级排序条件最稳妥的就是加上主键ORDER BY create_time DESC, id DESC这样每条记录的顺序就被唯一锁定了。我在游标分页案例里反复强调(create_time, id)二元组原因正是这个。另一个容易踩的坑是在分页查询中使用不稳定的计算列排序比如ORDER BY RAND()这种排序每次执行结果都不同分页没有任何意义只适合做小规模抽样。4.3 连接池被深分页拖垮时快速止血线上如果已经出现深分页导致的故障先别想着立刻改代码优化索引第一步应该是止血。最有效的临时手段是在数据库网关或者代理层拦掉过大的 OFFSET。比如限制单次查询OFFSET 不得超过 50000超过的请求直接返回参数错误。这样慢查询从源头就被切断了数据库压力会迅速降下来。同时可以降级接口返回策略比如列表接口默认只返回前 100 页数据后端接口加一个开关控制是否允许深翻页。等数据库压力降下来之后再冷静分析慢 SQL决定是加索引还是改游标分页。另外提一句如果用的是 MySQL 8.0可以关注一下EXPLAIN ANALYZE它不仅能看执行计划还能给出每一步实际执行的时间和行数比 EXPLAIN 的估算值更准确。排查深分页问题时这个工具能帮你更精确地确认瓶颈位置。4.4 索引优化中最容易被忽视的三个细节细节一LIMIT的值不影响扫描量OFFSET才影响。所以建索引时不用担心每页条数不同核心要关注的是查询条件和排序字段能否被索引覆盖。细节二联合索引的字段顺序必须和查询的等值条件、排序条件匹配。比如WHERE user_id ? ORDER BY create_time DESC索引应该是(user_id, create_time)而不是(create_time, user_id)。如果你建反了优化器只能先按create_time排序再把user_id作为过滤条件排序索引的优势就完全丧失了。细节三覆盖索引不是万能的。如果查询需要返回的字段特别多强行把所有字段都塞进索引里会导致索引体积膨胀写入性能下降而且查询时还是要回表获取未覆盖的大字段。更合理的做法是只让索引覆盖排序字段和过滤条件数据字段靠主键回表获取配合延迟关联使用。5. 从业务层面规避深分页的设计思路5.1 产品形态上的合理约束坦白说深分页问题很多时候不是技术问题而是产品设计问题。用户真的会翻到第 500 页去看数据吗绝大多数场景下不会。如果运营后台做了个表格每页 10 条允许翻到第 10000 页那翻到这么深的地方基本是为了导出或者批量操作而不是真正一条条看数据。对这种需求合理的做法有两种。一种是在产品上改成加载更多模式用游标分页另一种是限制最大翻页深度比如只允许查看前 200 页并提供按时间筛选的条件让用户通过缩小范围来精确定位数据。很多开发在接到列表要支持分页的需求时默认就写LIMIT OFFSET没想过数据量级增长之后会怎样。如果一开始就约定超过 50 万行的表不允许无条件深翻页很多线上故障根本不会发生。5.2 数据归档与冷热分离另一个思路是从数据存储入手。把历史数据定期归档到单独的表或者单独的库列表查询只查热数据表热数据量维持在可控范围内深分页问题自然就消失了。比如订单表线上只保留最近三个月的数据每个月把超过三个月的订单迁移到历史表。这样即使业务方还是用LIMIT OFFSET分页由于表行数只有几十万OFFSET 到几千也还是能在几十毫秒内返回。归档带来的额外收益是主表变小索引缓存命中率提高写入性能也会跟着改善。这种方案最适合那些数据本身有明确生命周期的业务。迁移过程可以用定时任务在低峰期执行注意在归档时保持索引重建和统计信息更新避免归档后查询计划出现明显变化。5.3 缓存与预计算作为辅助如果列表数据对实时性要求不高还可以考虑在缓存层解决。比如每天定时把深分页的前几页结果提前算好放到 Redis 里用户访问直接走缓存完全不查数据库。不过这种方案我在实际项目中用得比较谨慎。列表数据一旦涉及用户维度比如我的订单我关注的商品每个人的列表都不一样预计算缓存就基本不可行。它只适合全体用户共用的列表比如排行榜、分类页商品列表并且数据变化频率较低的场景。对于用户维度的深分页用游标分页配合数据库索引已经能解决绝大部分问题不必过度设计。最后分享一个我自己的排查习惯我每接手一个新的列表接口第一件事就是看它的 SQL 写法再查一下表的数据量和索引结构。如果发现是LIMIT OFFSET且表行数预期会超过几十万我会直接和产品确认分页行为能改游标就改游标不能改就在代码层加保护逻辑比如限制 OFFSET 上限。有一次排查线上慢查询现象很典型某个后台报表接口白天偶发超时用户一刷新就正常。当时所有人的第一反应都是加缓存但加了缓存之后依然偶发慢请求。后来把慢日志捞出来才发现是某个运营用户把筛选条件选得特别宽导致 OFFSET 一下子跳到了 80 万而且查询里还有一个不做任何过滤的 GROUP BY。这种组合拳打在 2000 万行的表上缓存根本拦不住因为每次都是穿透查询。最终根治方案是把报表数据改为每晚预聚合列表接口改成游标分页问题才彻底消失。所以排查分页慢别只看 OFFSET 有多大还要看整个 SQL 的过滤能力、排序方式、聚合操作是不是叠加在了一起。任何一个环节被放大的时候都会让 OFFSET 的代价成倍增加。搞懂了这些原理之后再去看各种优化方案思路就会清晰很多也知道哪些方案只是治标哪些才是治本。
