MySQL深分页优化实战：从OFFSET慢查询到延迟关联与书签分页

每次聊到 MySQL 性能优化深分页几乎是必被点名的那个题。后端面试、日常巡检、线上故障排查撞上它的概率实在太高了。我也记不清有多少次一边看着慢查询日志里LIMIT 100000, 20这种 SQL一边跟同事叹气又是深分页。这篇文章我不打算给你背答案式的三行方案列表而是把我自己在排查、优化、面试回答中真实用到的思路完整讲一遍深分页到底慢在哪、为什么网上说的那几种方案能生效、它们的边界在哪里、以及面试官追问时你应该怎么接住。无论你是准备面试还是正在被线上慢接口折磨照着这个思路去理解基本不会跑偏。1. 一次慢查询揪出的元凶OFFSET 10万的订单列表先还原一个真实场景。某个管理后台的订单列表接口上线第二天业务方就反馈翻到第 200 页之后页面要转好几秒。当时的表大概 30 万行结构类似这样CREATE TABLE orders ( id bigint unsigned NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL, user_id bigint unsigned NOT NULL, status tinyint NOT NULL DEFAULT 0, total_amount decimal(10,2) NOT NULL, create_time datetime NOT NULL, pay_time datetime DEFAULT NULL, PRIMARY KEY (id), KEY idx_status (status), KEY idx_create_time (create_time) ) ENGINEInnoDB;列表页的 SQL 长这样SELECT * FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 100000, 20;初看这条 SQL你可能会觉得有索引啊status 建了索引create_time 也建了索引怎么会慢。我当时也是这么想的直到把EXPLAIN拉出来看了一眼。EXPLAIN的结果里key确实命中了idx_status但rows显示要扫描十万多行Extra那一栏还挂着一个Using filesort。三个信息拼在一起问题就已经写在脸上了MySQL 在 offset 前把所有匹配 status1 的行都翻了一遍排序排完然后才丢掉前 10 万行只留最后 20 行给你。这里要纠正一个直觉误区。很多新手以为LIMIT 100000, 20的意思是从第 100000 行往后取 20 行好像 MySQL 能直接跳到那个位置。实际上 InnoDB 根本没有跳到偏移量的能力它只能沿着索引扫描一行一行数数到第 100000 行以后才开始取数。前面那 10 万行每一行都要经过判断、排序最后被无情丢弃。这就是深分页问题最核心的语义它不是取数据的问题而是白干大量脏活的问题。页数越深白干的活越多而且这个代价是线性增长的。接口慢到不可用时往往就是 offset 已经大到百亿级别数据库每执行一次查询都要做几十万次无效操作。深分页没有一个绝对严格的多少算深标准。在 30 万行的表里offset 到 10 万就已经很吃力在千万级表里可能 offset 到 1 万就不行了。判断标准不是页数而是扫描行数与最终返回行数的比例这个比例一旦超过几百上千你就该警惕了。2. OFFSET 越大越慢的底层逻辑扫描、回表、排序三笔账为什么 offset 大就会慢表面上是扫描行数多但往深了说是三笔成本叠加。我建议面试时也按照这个层次去讲因为每一层都对应了一条优化思路。2.1 第一笔账无用扫描的行数MySQL 执行WHERE status 1 ORDER BY create_time DESC LIMIT 100000, 20优化器选择了idx_status来过滤 status1 的行。它从索引的第一个匹配项开始沿着索引链表逐个扫描数到第 100000 条之后才开始收集最终要返回的 20 条。前面那 100000 条记录全部是无效遍历。这只是索引扫描的部分。如果你做过性能测试会发现在 30 万行的表上跑这条 SQL耗时通常在 1 秒以上而单纯扫描 10 万个索引项其实没那么慢。真正吃掉大头的是第二笔账。2.2 第二笔账回表的随机 I/OSELECT *要返回订单的全部字段但idx_status索引上只有status和主键id。每扫到一个索引项InnoDB 都要拿着id去聚簇索引里取完整数据行这个动作在 MySQL 里叫回表。回表是随机 I/O 的大户。更关键的是深分页场景下前面那 10 万行的回表结果全是白干的——你费劲把完整行取出来排序后又被丢掉了。等于 10 万次随机读最后只换来 20 条有效数据。理解这一点就能明白为什么延迟关联这种先查 id 的优化能起效它把回表次数从 10 万次降到了 20 次。2.3 第三笔账排序与临时文件为什么ORDER BY create_time没走idx_create_time因为status 1的过滤条件和create_time排序字段干脆不在同一个索引里。MySQL 要么选 status 过滤再排序要么选 create_time 排序再过滤两条路它都得权衡。在这个查询里优化器选了前者于是create_time的排序就只能在内存中的 sort buffer 里完成。当排序数据量超过 sort buffer 容量时MySQL 会把中间结果写到磁盘临时文件里做多路归并排序。这个动作的代价比内存排序高得多而且同样无法避免10 万行订单数据参与排序只为了最后取 20 条完全是杀鸡用牛刀。2.4 把三笔账合起来算一下粗略估算假设平均每行订单数据 1KB 左右10 万次回表读取就是 100MB 量级的随机 I/O再加上排序临时文件的读写。单条查询产生这么大体量的读操作在用户并发稍微上来一点的时候数据库的 CPU 和 IO 很容易被打满。这里需要区分一个情况如果ORDER BY字段和WHERE条件能共用一个联合索引MySQL 可以边扫边取省掉 filesort如果查询字段刚好全部在索引里也能省掉回表。但现实中SELECT *和自由排序的组合经常让索引帮不上忙。所以要理解深分页关键不是背结论而是能说清楚这三笔账分别是哪来的每一种优化方案到底省掉了哪一笔。3. 方案一延迟关联同样的 OFFSET少走十万次回表延迟关联是我在工作中用得最多的深分页解法也是面试时最稳妥、最不容易被追问倒的回答。它的思路很直白把取出完整行再过滤改成先取出主键 id再用主键回表取完整行。因为 id 在索引里取 id 的过程根本不需要回表。改造后的 SQL 长这样SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 100000, 20 ) tmp ON o.id tmp.id ORDER BY o.create_time DESC;内层子查询只查id在idx_status索引上扫描时索引页已经带上了主键值InnoDB 不需要回表。扫描 10 万个索引项的成本远低于回表 10 万行。外层拿到 20 个 id 后通过主键关联去聚簇索引里取完整行回表的次数从10 万变成了精确的 20 次。我在一个实际项目里测过同样 30 万行的表同样的分页位置改造前单条查询耗时 1.8 秒改造后降到 80 毫秒左右。数量级上的改善是实打实的。要提醒几个细节内层子查询的ORDER BY create_time能否走索引取决于有没有合适的联合索引。如果在orders表上建一个(status, create_time)联合索引那么WHERE status 1 ORDER BY create_time可以完全在索引内部完成连 filesort 都省了。InnoDB 的辅助索引叶子节点会自动带主键 id所以内层SELECT id不需要额外把 id 加进联合索引也能实现覆盖。外层别丢了ORDER BY o.create_time DESC。因为子查询虽然排好序了但外层做 JOIN 之后MySQL 不保证输出的行序必须显式再排一次。延迟关联不是银弹。当 offset 到了百万量级时即使只扫索引10 万、百万次的索引扫描本身也是成本。它能消灭回表但消灭不了无效扫描。4. 方案二书签分页把跳着找改成接着找如果说延迟关联治标那书签分页就是治本——它直接从根上绕开了 OFFSET。书签分页在英文资料里叫 Keyset Pagination核心思想是不再告诉 MySQL我要从第 10 万行后面取 20 行而是告诉它我要从上一条记录之后继续往下取 20 行。查询条件是连续的而不是跳跃的。先看代码。假设上一页最后一条订单的create_time是2025-01-15 23:59:59id是10086下一页就这么查SELECT * FROM orders WHERE status 1 AND (create_time, id) (2025-01-15 23:59:59, 10086) ORDER BY create_time DESC, id DESC LIMIT 20;这里用了 MySQL 的行构造器语法(create_time, id) (2025-01-15 23:59:59, 10086)等价于先按 create_time 比如果 create_time 相同再按 id 比。这是一种很省事的写法它的好处是只要上一页最后一条记录的唯一键确定下一页就一定能从对的位置继续既不重也不漏。为什么这个方案快因为每次查询都通过WHERE条件直接定位到目标附近的索引位置然后只扫描 20 行。整个操作的成本是恒定的不随页数增加而增加。无论你在第 1 页还是第 10 万页耗时都差不多。但书签分页有一个硬伤不能跳页。用户只能点击上一页 / 下一页没法直接输入跳到第 5000 页。很多后台管理系统都有跳页需求这时候书签分页就没法用了。所以在落地前要先跟产品确认你的分页到底是信息流式的持续加载还是传统翻页导航。书签分页的另一个坑藏在排序字段上。如果只按create_time排序而同一秒内创建了大量订单那么下一页的WHERE create_time 2025-01-15 23:59:59会直接把同一秒剩下的记录全部漏掉。解决办法就是我上面写的把id作为第二排序键同时把(create_time, id)做成组合书签保证排序键唯一。这里还要补一个索引建议这种查询最好建(status, create_time, id)联合索引让过滤、排序、唯一性判断都走同一个索引效率和稳定性能兼得。5. 方案三覆盖索引与范围切割从另一个维度省成本延迟关联和书签分页是面试回答里的主流但我会建议你再补充两个思路因为它们更能显示你对原理的理解深度。5.1 覆盖索引让 SELECT 不再回头拿数据覆盖索引是指查询所需要的所有列都能在某个索引内部找到InnoDB 就不需要回表。前面延迟关联的例子里内层SELECT id本质上就是利用了覆盖索引。如果你想更彻底一点可以对固定字段的列表查询做联合索引覆盖ALTER TABLE orders ADD INDEX idx_status_time_id_amount (status, create_time, id, total_amount);这样如果列表只需要显示这些字段查询可以完全在索引里完成不但没有回表甚至扫描成本也会低很多。但现实世界里SELECT *的需求太常见了一张宽表动辄几十个字段覆盖索引很难做到。所以这个方案更像是特定场景下的极限优化不是通用答案。5.2 范围切割从业务上减少单次查询的数据集既然深分页的本质是在一个超大集合上做深偏移那如果我们能把超大集合切成小集合问题不就消失了吗经典做法有几种时间维度切割订单列表按月份分表或分区用户翻到 3 月再看 3 月那个分区的数据。业务维度切割只看我的订单而不是所有人的订单where 里带 user_id天然把范围缩小到几十条。状态维度切割按订单状态分成多个 Tab每 Tab 只查询当前状态的数据。这些方案的本质不是优化 SQL而是优化数据边界。你在面试或实际设计时如果能意识到深分页问题不只是 SQL 写法问题还是数据模型问题往往比单纯背方案更能让面试官眼前一亮。5.3 三种主流方案怎么选一张表讲清楚方案核心思路适用场景优点缺点与注意点延迟关联先查主键再回表任意需要深翻页的场景改动最小保留跳页能力、兼容原 SQLoffset 极大时仍要扫大量索引项书签分页用上一页末尾的值做条件上下页式列表、信息流、移动端耗时恒定、最优索引利用不能跳页排序字段必须是唯一的覆盖索引查询全部落在索引内字段固定的窄表场景零回表、性能最稳宽表无法实现全局覆盖范围切割缩小参与排序的数据集有自然维度的业务从根源规避深分页依赖业务模型不是通用 SQL 优化6. 面试应答从背方案到讲清成本决策这个题目在面试里最常见但它考察的从来不是你记没记住三个方案而是你能不能建立成本模型再基于业务限制选型。我记得有一次面试官就是在我说完延迟关联后追问了一句你已经知道延迟关联能解决为什么线上很多团队还是会碰到深分页这个问题如果只背过答案很容易卡壳。我后来把回答组织成了这样一个思路你可以参考6.1 先回答为什么慢——这是地基我会先抛结论深分页慢不是取数据慢而是取之前白干活。然后分三层讲成本扫描成本LIMIT 100000, 20要遍历前面 10 万行回表成本SELECT *每行都回表前面 10 万次回表全部浪费排序成本ORDER BY字段和索引匹配不上时还要 filesort甚至落盘。这三层讲完面试官基本能确认你是真的理解 InnoDB 执行过程而不是背了概念。6.2 再讲方案——每一层成本都有对应的解法针对回表浪费 → 延迟关联先查主键再回表针对扫描浪费 → 书签分页用条件继续代替偏移针对排序浪费 → 建联合索引让排序走索引如果都不行 → 考虑从数据模型上范围切割。讲方案时不要只是报名字要主动说出这个方案在什么场景下不适用。比如书签分页没法跳页管理后台用不了延迟关联在超大 offset 下还是会有索引扫描压力。主动暴露边界条件比你只讲优点要可信得多。6.3 面试官可能追问的三个边界没有 ORDER BY 也会深分页吗会。只要LIMIT 100000, 20没有ORDER BY时 MySQL 同样要扫到第 10 万行只是没有排序成本而已。主键不连续怎么办业务删除导致 id 断层时WHERE id ? LIMIT 20可能每页不足 20 条此时需要结合其他稳定字段定位或接受补偿逻辑方案上要提前考虑。数据量到了千万级offset到 50 万时还有什么办法吗单表已经不适合做深翻页了一般会考虑按时间/业务分片或者对搜索能力依赖较高的场景引入搜索引擎体系但那是另一整个话题。6.4 加分项结合真实业务讲选型如果面试官让你给一个最佳实践我会这么说分页需求分两种。第一种是传统后台翻页必须支持跳页用延迟关联最稳SQL 改动小对现有接口兼容最好。第二种是 App 信息流或订单列表用户只会一直往下划这时候书签分页的体验和性能都更好但需要产品层面接受没有跳页。把技术方案和产品需求挂钩是面试里很容易拉开差距的一点。写在最后MySQL 深分页这个问题我在不同场合讲了不下几十次发现大家最后真正卡住的往往不是不知道方案而是没建立成本分析的思维方式。你把回表、扫描、排序这笔账算明白了面试的时候自然能说出个所以然线上遇到慢查询也会下意识去 EXPLAIN 看扫描行数而不是急着堆 Redis 缓存。最后分享一个我亲测比较有效的技巧遇到分页慢的 SQL先别急着改代码把EXPLAIN的结果截图存下来改完一种方案再对比扫描行数和耗时。数据是最有说服力的面试时你能抛出这样一组前后对比比背十种方案都有用。