MySQL深度分页优化说白了就是跟LIMIT offset, size这种写法死磕。我接手过一套订单管理后台数据量过百万之后页面翻到几十页就得等三秒起步前端同事直接在列表页画了个转圈的小菊花。当时我第一反应是换服务器结果冷静下来一查根本不是硬件的事而是这条 SQL 本身的执行逻辑有大问题。所谓深度分页就是 offset 特别大的分页比如LIMIT 1000000, 20。它慢不是因为查 20 条数据慢而是 MySQL 为了找到这 20 条得先把前面 100 万条记录翻一遍。这篇文章不堆概念直接把从踩坑、定位、优化到落地的完整过程捋一遍适合正在做后端接口、数据库维护或者全栈开发的读者参考。1. 深度分页为什么这么慢从一次接口超时说起1.1 事故现场还原当时那个订单列表页功能看起来很简单按创建时间倒序一页 20 条支持搜索框传订单状态和用户 ID。订单表的数据量大概 500 万行跑到第 8000 条记录左右也就是第 400 页时接口响应时间从几十毫秒直接飙到 4 秒多。最初排查方向完全跑偏以为是带宽或者服务器磁盘 IO 出问题了重启了 MySQL、清了缓存最后才发现罪魁祸首就是分页 SQL 的写法本身。EXPLAIN一看rows那列直接超过 100 万Extra里还挂着Using filesort。当时第一反应是 create_time 字段没索引但加上索引之后发现该慢还是慢。这就是深度分页的迷惑性看起来索引也有了SQL 也该走的都走了但它就是快不起来因为问题不在“有没有索引”而在offset这种写法要付出的固定代价。1.2 慢在哪一步LIMIT offset 的真实执行链很多人以为LIMIT 1000000, 20是直接从第 100 万行开始读实际上 MySQL 是从第 1 行开始扫扫到第 1000020 行然后把前 100 万行全部丢掉只留下最后 20 行。这个过程如果用生活类比就像在火车站排队取票你前面排了 1000 个人但你只想叫第 1001 个人出来服务员仍然得从第 1 个人开始挨个报数数到第 1000 才知道谁是下一个。在 InnoDB 存储引擎里这一步的真实开销来自三块第一扫描。如果查询条件和排序能命中二级索引MySQL 会从索引的起始位置开始按顺序扫叶子节点每扫一条记录都要判断是不是满足过滤条件直到累计扫够offset size条才停。第二回表。二级索引里一般只存索引字段和主键不是完整行数据。如果你SELECT *每扫到一个索引项还得根据主键再到聚簇索引里取一整行这是随机 IO百万次随机回表的代价极其夸张。第三排序。如果排序字段没被索引覆盖MySQL 会先把满足 WHERE 条件的所有记录扔进临时文件做 filesort排完序再执行 LIMIT。这个排序的数据量不是 100 万行而是全部符合过滤条件的行数在深度分页场景下基本就是全表排序。所以一条深度分页 SQL 的耗时基本上是“索引扫描 全量排序 海量回表”三者叠加的结果。越往后面翻页offset 越大这三项成本就越大而且不是线性增长接近开关式恶化。1.3 批量查询场景下的连锁反应更麻烦的是这种慢 SQL 不是单条接口的问题。后台管理系统一打开就是列表页紧接着可能有多个管理员同时查询、导出、刷新。每一条深度分页查询都在占用数据库连接和 IO连接池很快被打满其他完全正常的查询也会在后面排队。我当时观察数据库状态Threads_running一度飙到几十CPU使用率超过 90%整个实例响应能力大幅下降。所以深度分页优化的价值不只是把一条 SQL 从 3 秒优化到 30 毫秒而是把数据库整体的可用连接数、CPU、IO 这些资源释放出来。明白了这一点后面做方案选型的时候就会更倾向于选择“从根源上减少扫描量”的做法而不是表面上的缓存兜底。2. 快速诊断怎么确认分页是否需要优化2.1 打开慢查询日志观察真实执行怀疑有深度分页问题第一步不是改 SQL而是先把慢查询日志打开拿到真实执行数据。MySQL 的慢查询日志配置很直接在配置文件里加上这几行slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1long_query_time设成 1表示超过 1 秒的查询都会被记下来。log_queries_not_using_indexes会把没走索引的查询也写进日志这个开关在排查分页问题时特别有用能帮你发现那些你以为走了索引、实际全表扫的隐藏问题。重启 MySQL 之后让线上流量跑个十来分钟然后直接看日志文件里耗时最长的几条 SQL。如果出现的都是类似LIMIT 100000, 20、LIMIT 500000, 20这种语句那基本可以确诊深度分页问题了。2.2 EXPLAIN 看执行计划盯住这几列拿到慢 SQL 之后立刻用EXPLAIN分析执行计划。不需要看所有列抓住四个关键点就够了type这一列看它到底是ref、range、index还是all。最差的是all代表全表扫描index表示全索引扫描也不一定是好事ref或者range说明查询条件能有效利用索引。key这一列确认实际用的索引是不是自己预期的那一个。很多时候你给表建了索引MySQL 优化器觉得全表扫更快直接把你索引晾一边了。rows这一列预估扫描行数。如果这个数字接近几十万甚至上百万而实际返回只有 20 条那基本可以断定慢在这里。Extra这一列重点看有没有Using filesort和Using temporary。Using filesort意味着排序没走索引这在深度分页里几乎是致命的因为它把排序量扩大到了所有符合条件的记录。举一个实际排查的例子EXPLAIN SELECT * FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 500000, 20;执行计划里很可能出现type: refkey: idx_statusrows: 900000Extra: Using filesort。这说明 MySQL 先通过 status 索引过滤出 90 万条记录然后全部扔进临时排序再从排序结果里丢掉前 50 万条。这比直接全表扫还难顶。2.3 数据量与 offset 的现实分水岭并不是所有分页都需要优化。数据量只有几千行offset 几千那优化不优化差别不大。但从我的经验看有两个数字特别值得注意第一表的数据量超过 100 万行后分页查询的成本开始明显上升。第二offset 超过 10000 后接口耗时会有跳变尤其是在排序字段没有索引的情况下非常明显。我常用的判断标准是这样如果查询的EXPLAIN rows超过 1 万同时接口响应时间超过 500 毫秒就要认真考虑优化。不要等到线上慢查询告警才动手提前把这些深度分页 SQL 捞出来治理成本低得多。3. 落地方案五条路我推荐你先走这条3.1 书签法用上一条记录的位置翻页书签法又叫游标法、seek method是目前解决深度分页问题最推荐的手段。核心思想很简单不再告诉数据库“跳过前面多少条”而是告诉数据库“从上一次取到的最后一条记录的位置开始往后取 N 条”。举例来说假设上一页最后一条记录的 create_time 是2024-06-01 12:00:00id 是100023那么下一页的查询写成SELECT * FROM orders WHERE create_time 2024-06-01 12:00:00 OR (create_time 2024-06-01 12:00:00 AND id 100023) ORDER BY create_time DESC, id DESC LIMIT 20;这里的核心变化是把OFFSET换成了范围条件。InnoDB 的 B 树索引天然支持等值和范围查找create_time ?直接就能定位到上一次位置的附近然后顺序往后取 20 条。扫描的行数只跟返回条数有关跟翻到第几页完全无关。这就是它快的根本原因。但有一个前提必须满足排序条件必须包含上一页的位置信息。如果只按 create_time 排序而表里有大量相同 create_time 的记录单纯用create_time ?会漏数据。所以通常要把主键作为排序和位置的第二条件避免排序不稳定。这一点我在后面的避坑清单里会专门展开。书签法的代价是它只支持“顺着往前翻”或者“顺着往后翻”不支持直接跳转到第 N 页。如果你的产品有“点击页码跳转”的刚性需求书签法就不太合适。但是对于信息流、日志列表、订单列表这类常见场景无脑用书签法基本不会翻车。3.2 延迟关联先查索引再取全行延迟关联的思路是先只查主键拿到主键之后再去关联原表查完整数据避免在二级索引扫描阶段就把大量无用行数据回表载入。比如原来的 SQL 是这样SELECT * FROM orders WHERE user_id 9527 ORDER BY create_time DESC LIMIT 100000, 20;改成延迟关联SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE user_id 9527 ORDER BY create_time DESC LIMIT 100000, 20 ) t ON o.id t.id ORDER BY o.create_time DESC;子查询里只查 id用到的都是索引列回表成本基本为零可以走覆盖索引。拿到这 20 个 id 之后再回原表查完整行只回表 20 次。不过有一点得说清楚延迟关联只解决了“回表次数过多”的问题并没有解决offset本身扫描了大量索引项的问题。也就是说它治标不治本。如果表特别大offset 特别深延迟关联依然慢。所以我的习惯是把它当作书签法的辅助手段两个方案经常配合使用。3.3 覆盖索引设计给 ORDER BY 安排一条专用通道深度分页慢的另一个重要原因是 filesort。避免 filesort 的最好办法是让排序字段正好是索引的一部分这样索引本身就是排好序的MySQL 直接顺序扫就行。针对订单表一个典型的设计是建组合索引ALTER TABLE orders ADD INDEX idx_status_createtime_id (status, create_time, id);这个索引在 WHERE status 1同时 ORDER BY create_time DESC, id DESC 的场景下能发挥作用。查询条件里的等值字段 status 放在最前面排序字段 create_time 和 id 跟在后面MySQL 直接按索引顺序读取连排序都省了。建完索引后再跑一次 EXPLAINExtra列里如果出现Using index condition或者Using index说明索引作用发挥得不错。如果Using filesort消失了那深度分页的核心成本就少了一半。但索引也不是建得越多越好。每个索引都会占用磁盘空间写入时要同步更新索引多了写性能会明显下降。所以组合索引设计要谨慎尽量覆盖线上真实最高频的查询路径而不是把所有字段都塞进索引里。3.4 子查询限定范围把 offset 变成 id 边界如果你不想改造翻页方式仍然想保留page参数可以考虑用子查询先把 id 范围圈出来再查完整数据。思路是先把深度 offset 的问题转化成“id 大于多少”的问题。比如用户要跳转到很后面的页面可以先把主键范围算出来SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 100000, 20 ) t ON o.id t.id;这个写法和延迟关联很像但这里强调的点是子查询里的 LIMIT 不会回表只扫描索引项整体成本比直接SELECT *低很多。如果查询条件比较复杂比如多个过滤字段子查询的优化空间更大。不过要注意这种方案在 offset 非常深的情况下仍然需要扫描大量索引项它不是银弹。更适合那种产品非要保留页码跳转、又不想彻底改成游标式的场景。有点像是“在现有约束下尽量把代价压缩”。3.5 快照缓存治标但有时很管用的办法还有一种思路是彻底绕开数据库分页第一次查询时把符合条件的 id 列表全部查出来存到 Redis 里之后每次翻页都从缓存里切片返回数据库只承担第一次查询的压力。这个方案在“数据量可控、过滤条件固定、翻页频繁”的场景下很管用。比如某个报表后台每天凌晨跑一次数据快照把当天符合条件的订单 id 列表写入缓存用户白天随便翻页都很快。但这个方案有两个明显限制。一是数据总量不能太大几百万个 id 存到 Redis 里就得几十 MB 内存数据量再大内存吃不消。二是数据实时性差只要有新数据进来或者数据被修改缓存就会不准需要额外做刷新机制。我的看法是快照缓存作为兜底方案可以但别当成第一选择。它的局限性太多真正能一劳永逸的还是书签法配合合理的索引设计。4. 实战复盘订单列表从 3 秒优化到 20 毫秒4.1 准备模拟数据500 万行订单表为了完整还原优化过程我模拟了一张订单表字段尽量贴合实际业务结构如下CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL, order_no VARCHAR(32) NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL DEFAULT 0, create_time DATETIME NOT NULL, PRIMARY KEY (id), KEY idx_status_createtime_id (status, create_time, id), KEY idx_create_time (create_time) ) ENGINEInnoDB;然后写了一个存储过程灌入 500 万行模拟数据。注意不能逐条插入那样太慢了我用的是单条循环但每 1000 条提交一次的方式整个过程跑了几分钟。DELIMITER // CREATE PROCEDURE init_orders() BEGIN DECLARE i INT DEFAULT 1; DECLARE batch_size INT DEFAULT 1000; WHILE i 5000000 DO INSERT INTO orders (user_id, order_no, amount, status, create_time) VALUES (FLOOR(RAND() * 100000), CONCAT(O, i), ROUND(RAND() * 1000, 2), FLOOR(RAND() * 3), NOW() - INTERVAL FLOOR(RAND() * 365 * 24 * 3600) SECOND); IF MOD(i, batch_size) 0 THEN COMMIT; END IF; SET i i 1; END WHILE; COMMIT; END// DELIMITER ;插入完成后表里有 500 万行数据覆盖了 status 0、1、2 三种状态create_time 分布在一年范围内这样测试结果更接近真实业务。4.2 优化前一个 SQL 让数据库 CPU 打到 100%优化前列表接口的原始 SQL 是这样的SELECT * FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 1000000, 20;这条 SQL 的耗时我实测了几次稳定在 2.8 秒左右。EXPLAIN 显示rows大约 150 万Extra里有Using filesort。执行期间数据库 CPU 一度冲到 90% 以上连接数明显上升。这个结果完全符合前面讲的执行链先按 status 过滤出大量记录全部排序再丢掉前 100 万条最后回表 20 条。而实际上用户只想看第 100 万行后面的 20 条数据库白干了 99.99% 的活。4.3 组合优化索引重建 书签法落地优化动作分成两步。第一步确认索引覆盖第二步改成游标式 SQL。索引方面保留(status, create_time, id)组合索引让 WHERE 和 ORDER BY 都能直接走索引路径。然后书签 SQL 长这样SELECT * FROM orders WHERE status 1 AND (create_time 2024-05-01 12:00:00 OR (create_time 2024-05-01 12:00:00 AND id 500000)) ORDER BY create_time DESC, id DESC LIMIT 20;优化后的 SQLEXPLAIN 的rows直接降到 20 左右Using filesort消失接口响应时间从 2.8 秒降到 20 毫秒附近。数据库 CPU 也恢复到正常水平。这里有个细节值得注意order by 后面加了id DESC游标条件里也加了(create_time ? AND id ?)这一句。如果不加create_time 相同的记录在翻页时会出现重复或遗漏这是书签法最常见的坑后面我会详细说。4.4 接口层改造从页码翻页到游标翻页SQL 改完之后接口参数也得跟着改。原来前端传的是page50001size20现在改成把上一次最后一条记录的位置传回来。大概的接口设计是这样的客户端请求GET /api/orders?status1cursor2024-05-01 12:00:00,500000size20服务端解析 cursor 里的两个值create_time 和 id拼到查询条件里。返回结果时取当前页最后一条记录的 create_time 和 id拼成新的 cursor 返回给前端{ data: [...], nextCursor: 2024-04-30 11:58:30,499990, hasMore: true }前端下一页直接带上nextCursor请求即可。如果担心用户在翻页过程中看到新插入的数据还可以把刷新策略设计成“列表只允许往下翻不自动刷新”或者加上版本号之类的控制字段。这部分属于产品策略范畴但技术实现上并不复杂。5. 避坑清单深度分页的高频翻车现场5.1 排序字段重复书签位置必须加主键兜底书签法最容易出错的地方就是排序字段不唯一。假设 create_time 上有很多记录完全相同上一页最后一条的 create_time 是2024-05-01 12:00:00下一页如果只写WHERE create_time 2024-05-01 12:00:00就会把这些时间相同的记录全部漏掉。正确写法是用排序字段加主键组成联合游标WHERE create_time ? OR (create_time ? AND id ?) ORDER BY create_time DESC, id DESC这个条件的意思是先找比上次时间更早的记录如果时间相同则找主键比上次主键更小的记录。因为主键全局唯一联合条件能精确定位到上一次的位置不多不漏。我当时第一次落地书签法就栽在这里。数据量小的时候没感觉数据量大之后发现翻页过程中少了几条记录排查了半天才发现是没加主键兜底。后来只要涉及书签法我都是默认带上主键条件不管排序字段有没有重复的可能。5.2 WHERE 条件漂移导致翻页数据错乱还有一次线上问题书签法上线之后用户筛选完某个状态后往下翻页翻到第二页数据开始错乱。排查到最后发现是接口层漏传了 status 过滤条件。书签位置是带有上下文语义的不是说上一个游标在哪个位置换一种过滤条件还能接着用。游标设计必须和查询条件严格绑定。每次查询都要带上完整的 WHERE 条件游标只是额外限制位置不能替代过滤条件。如果查询条件变化了游标必须重置前端翻到一半切换筛选条件接口要做一次全量重查而不是沿用旧游标。5.3 数据删除造成的跳页现象原生的LIMIT offset, size在数据不断删除的场景下会跳页因为删除记录后行数重新排列offset 就不是原来的含义了。书签法天然规避了这个问题因为它是基于位置而不是编号删掉某条记录不影响后续游标的正确性。但这个方案会带来另一个体验问题如果用户在第 10 页停留后台删掉了一些记录用户继续翻下一页时可能感觉“跳过了”一些条目。但这其实是数据量变化导致的正常现象对列表类的产品影响不大。如果需要绝对稳定可以在产品上提示“数据已更新列表已重置”或者干脆不做即时删除用逻辑删除字段替代物理删除。5.4 索引没走对force index 要不要用书签 SQL 写完不是万事大吉一定要用 EXPLAIN 验证实际执行计划。我就遇到过明明建了组合索引MySQL 优化器却因为统计信息过期选了其他索引导致范围扫描退化成全索引扫描的情况。如果确认索引设计没问题只是优化器选择错误可以先ANALYZE TABLE刷新统计信息。实在不行可以用FORCE INDEX强制指定索引但不建议默认长期用。因为FORCE INDEX是硬编码一旦后续索引结构调整这个写法可能反噬性能。我的习惯是先刷新统计信息配合慢查询观察一段时间如果仍然选错索引再考虑FORCE INDEX并且做好注释说明原因。5.5 别忘了 COUNT(*) 也是深度分页的坑分页接口的另一个隐藏坑是总条数查询。如果列表页要显示共 250 万条记录、共 125000 页那每次翻页还得多跑一次SELECT COUNT(*) FROM orders WHERE status 1。这个查询在大表上同样不便宜尤其过滤条件比较复杂的时候耗时可能比重建后的分页 SQL 还高。我的处理思路是看产品需求是否真的需要精确总数。如果只需要一个大致的量级可以用定时任务统计后缓存起来或者用SHOW TABLE STATUS之类的估算值。如果确实需要精确计数可以考虑单独维护一张计数器表每次状态变更时原子更新。分页查询本身优化得再好被 COUNT 拖后腿就亏了。——在实际项目里深度分页优化的第一步永远是先看清楚查询计划搞清楚慢在“扫描”“排序”还是“回表”再针对性处理。我个人处理完这条订单列表之后又在日志类、流水类接口上复用同一套方案每次都能把接口耗时压到一个可接受的范围。书签法加组合索引这一套组合拳已经是我处理这类问题的默认选择了也希望这篇文章能帮你在自己的系统里少踩几个坑。
