企业官网建设流程全解析

1. 从一次深夜告警说起：深分页到底踩了什么坑

接到线上告警的时候，已经有连续几分钟的慢查询堆积了。看监控平台拉出来的SQL，问题集中在一条分页查询上，大概长这样：

SELECT * FROM orders WHERE user_id = 123456 ORDER BY create_time DESC LIMIT 100000, 20;

单看这条SQL本身，过滤条件有索引，排序字段也建了索引，理论上不应该是慢查询。可实际在MySQL里跑一下，动不动就是几百毫秒到一秒多。业务方反馈说，用户只是往下多翻了几页，页面就开始转圈，到了后面几十页，几乎打不开。

这个场景几乎每个做后端开发的人都碰到过：MySQL分页查询，翻页越深越慢。同样是LIMIT，LIMIT 20 OFFSET 0快得飞起，LIMIT 20 OFFSET 100000就开始卡顿。问题到底出在哪？有人说是回表，有人说是扫描行数太多，也有人直接归结为“MySQL天生就不擅长深分页”。这些说法都对，但都只是一部分。今天把这条SQL从执行计划到物理I/O层面掰开揉碎，彻底讲清楚LIMIT OFFSET慢的根本原因、排查思路，以及我在实际项目中用过的几种优化方案。

需要先说明的是，这篇文章讨论的是MySQL InnoDB存储引擎下的场景，大部分逻辑在MySQL 5.7和8.0版本中表现一致，8.0的优化器在某些场景下有自动优化，但深分页的痛点本质没有改变。

2. 执行计划里的真相：不是查询慢，是“翻垃圾”翻得慢

2.1 先看rows字段：优化器说的是扫描行数，不是返回行数

排查这类问题，第一步永远是看执行计划。用EXPLAIN跑一下这条SQL，关键信息集中在type、key、rows这三个字段上。

EXPLAIN SELECT * FROM orders WHERE user_id = 123456 ORDER BY create_time DESC LIMIT 100000, 20;

以常见的索引结构来看（联合索引(user_id, create_time)），执行计划的type可能是ref或者range，key是联合索引，而rows会显示一个很大的数字，比如在十万到百万这个量级。

这个rows值暴露了问题的第一层真相：MySQL需要先读取并跳过前100000行，然后才开始返回结果。LIMIT 100000, 20的含义是取第100001到第100020条，意味着优化器无论如何都要先定位到第100001条的位置，这个定位过程需要从满足user_id = 123456条件的第一条记录开始，逐条往下数。优化器给出来的rows是预估的需要扫描的行数，而不是实际返回的行数。

打个比方：你要在一本厚厚的书里找第1001页的第三行文字，如果你知道页码，可以翻目录然后直接翻到那一页。但如果这本书没有页码，你必须从第一页开始一页一页翻过去，翻到1001页之前的每一页都要过目，然后扔掉。MySQL这个场景下就是后者——索引结构能帮你快速定位到user_id = 123456对应的第一条数据，但从第一条到第100000条之间，没有任何“页码”可以直接跳过去。

2.2 回表：深分页的放大效应

如果只是扫描索引，其实也不算太慢。真正的放大效应来自回表。

查看执行计划时，当Extra列出现Using index condition或者没有出现Using index，就说明查询需要回表。什么是回表？简单说，二级索引里只存了索引列的值和主键值，查询需要的其他列（比如订单金额、商品快照、状态等）都在聚簇索引（主键索引）的叶子节点上。MySQL通过二级索引找到主键后，还要用主键去聚簇索引里再查一次，才能拿到完整的行数据。

深分页场景下，回表的代价被极度放大：

• 前100000条数据，每条都要走一次回表，哪怕它们最后都被丢弃
• 回表是随机I/O，聚簇索引的叶子节点按主键排列，但查询出的主键顺序和物理存储顺序不一致，导致数据页被随机读取，而不是顺序读取
• 如果这些数据页不在缓冲池里，每次回表都是一次磁盘读取

我在线上排查时见过极端情况：一张表只有几十万行，数据量不算大，但深分页查询的耗时超过一秒，原因就是从第90000条开始翻页时，坏查询要回表十万次，而数据页在磁盘和缓冲池之间反复换入换出，I/O等待时间直接把查询拖垮。

2.3 排序状态：Extra列里的Using filesort

再来看排序。ORDER BY create_time DESC配合(user_id, create_time)联合索引，理论上可以直接利用索引顺序完成排序，执行计划里不会出现Using filesort。

但换个场景：如果排序字段不是索引的一部分，或者排序方向与索引顺序不一致，MySQL就需要把满足条件的行都读出来，放到内存或磁盘临时文件中排序。Using filesort一旦出现，深分页的代价会更高，因为排序操作本身需要额外的CPU和内存开销。

有个常见的误区：认为ORDER BY加上索引就一定没问题。实际上，索引对排序的帮助是有前提的——WHERE条件的等值列必须是索引最左前缀，排序字段必须紧随其后且顺序一致。一旦WHERE条件的列不在索引的最左前缀位置，或者排序字段与索引顺序不一致（比如索引是升序、查询要降序），优化器就会放弃索引排序，走Using filesort。

我在一个实际项目中就碰到过：表上有联合索引(category_id, status, create_time)，但查询条件是WHERE status = 1 ORDER BY create_time DESC，status不在最左前缀，导致整个排序掉到了filesort路径。加上深分页的offset，查询从几十毫秒飙升到几百毫秒。

3. OFFSE T深翻页的三重代价：扫描、回表、排序的叠加效应

3.1 代价模型拆解：一次深分页查询到底干了多少活

把深分页查询拆解开来，一次LIMIT 100000, 20实际上干了三件事：

1. 从索引中定位到第一个满足WHERE条件的行，沿着索引链表向后扫描
2. 扫描过程中收集前100000行的主键，逐条回表拿到完整行数据
3. 对这100000行进行排序（如果需要），然后丢弃前100000行，返回最后20行

这个过程中，真正返回给客户端的有用数据只有20行，但MySQL为此付出的工作量是全量扫描+全量回表+可能全量排序。这个效率模型本质上就是把大部分资源浪费在了“数数”上。

画一个简单的对比：

• LIMIT 0, 20：扫描20行，回表20行，排序范围20行
• LIMIT 100000, 20：扫描100020行，回表100020行，排序范围可能是100020行，返回20行

工作量随offset增加呈线性增长，查询耗时自然也是线性增长。这就是为什么翻到第10页、第20页还能忍，翻到第100页、第500页直接不可用。

3.2 数据分布对深分页耗时的实际影响

同样的LIMIT 100000, 20，在不同数据分布下表现会差很多。这是容易被忽略的一点。

如果满足user_id = 123456条件的总行数只有150000行，那么limit 100000的偏移量虽然深，但最多扫描150000行，耗时有上限。但如果这个用户有500万行订单记录，扫描量就变成500万行，耗时不可控。

另外一个容易踩坑的细节：user_id条件过滤出的数据量，和表的总体数据量相比可能就是很小的一部分。执行计划里rows字段对这部分数据量的估算如果偏差很大（常见于统计信息过期），优化器可能选错索引。比如MySQL预估用户123456只有几千行订单，选择了user_id索引；但实际有几十万行，导致查询从快速定位变成大批量扫描。

我建议在排查这类问题时，先单独跑一下：

SELECT COUNT(*) FROM orders WHERE user_id = 123456;

确认这个用户的数据量级，再根据实际量级判断到底是优化器选错索引，还是深分页本身的代价。

3.3 为什么不建议把OFFSET直接改大

有一种粗暴的优化思路是：把LIMIT 100000, 20改成LIMIT 99980, 40，期望通过多返回一些数据减少翻页次数。这个思路乍一看有效，实则是掩耳盗铃——MySQL的执行方式没有变化，它依然要扫描99980行再丢弃99980行。

还有人在业务层面做“预加载”，比如一次性查出前100页的数据缓存到内存。这种方案对小数据量可行，但数据量一大就失效。MySQL本质上是个存储引擎，不是搜索引擎，把它当成内存数据库来用，迟早会出问题。

还有个容易忽略的问题：深分页查询如果并发上来，放大效应更明显。一个深分页查询消耗的IO次数可能是浅分页的几千倍，几个类似查询同时打过来，数据库的IO能力直接被打满，整个实例的查询延迟都会受影响。我在排查一次数据库抖动时发现，罪魁祸首就是某个后台任务在循环翻页，每一页都是ORDER BY id LIMIT 500000, 100级别的深分页，直接把实例的读IO拖到了瓶颈。

4. 优化方案对照：延迟关联、游标翻页与业务降级

4.1 方案一：延迟关联，先走覆盖索引拿到主键再回表

延迟关联（Deferred Join）是我在实际场景中最常用、效果也最明显的优化方式。核心思路是：先让MySQL只走覆盖索引，不要回表，等确认了最终要返回的20条主键后，再去回表拿完整数据。

改写后的SQL长这样：

SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE user_id = 123456 ORDER BY create_time DESC LIMIT 100000, 20 ) t ON o.id = t.id;

子查询里的SELECT id只用了联合索引(user_id, create_time)，索引已经包含了id（主键），所以这部分是全索引扫描，不需要回表。扫描100000条索引记录的开销远小于扫描100000条完整行记录（索引记录更小，数据页更少，I/O次数大幅下降）。拿到这20个主键后，再通过主键回表，只有20次回表操作。

实测效果：还是前面那个订单表的案例，改写后查询耗时从900多毫秒降到80毫秒左右，效果非常明显。

这个方案适合的场景：需要保留原有排序和分页语义，并且不能轻易改变接口返回格式。它是深分页优化中改动最小、风险最低的方案。

需要特别注意的是：延迟关联对排序字段有依赖。ORDER BY create_time DESC必须能在索引内完成排序，否则子查询里仍然会出现filesort。如果排序字段不在索引中，或者WHERE条件破坏最左前缀，延迟关联的效果会大打折扣。

4.2 方案二：游标式翻页，放弃OFFSET

延迟关联能解决的只是“减少回表次数”，但MySQL仍然需要扫描并丢弃100000条索引记录。如果数据量大到一定程度，连索引扫描本身都很耗时。这时候只能从业务层面换思路：放弃OFFSET翻页，改用“基于游标”的翻页方式。

游标式翻页的核心思想是：每次查询不是告诉MySQL“跳过前面多少条”，而是告诉它“从哪一条之后开始取”。

比如第一页查询拿到最后一条记录的create_time和id，第二页查询写成：

SELECT * FROM orders WHERE user_id = 123456 AND (create_time < '2024-01-15 12:00:00' OR (create_time = '2024-01-15 12:00:00' AND id < 123456)) ORDER BY create_time DESC, id DESC LIMIT 20;

这里的条件是“取比上一次最后一条更早的数据”，利用(user_id, create_time, id)索引直接定位到游标位置，跳过中间所有行。这种翻页方式的时间复杂度与页数无关，翻到第100000页和翻到第2页耗时基本一致。

实际项目中我遇到过需要导数据后台批量拉取全量订单的场景。之前用offset翻页，每翻一页慢一点，翻到后面几乎跑不动。改成游标式翻页之后，无论翻多少页，单页查询耗时始终稳定在50毫秒以内，整批数据拉取的总耗时从快两小时缩短到十几分钟。

游标式翻页有一个比较明显的限制：用户不能随意跳页，只能一页一页往下翻。对大多数ToB系统、内容流、操作日志这类场景来说，这个限制可以接受。但对电商、搜索结果这类需要支持用户直接跳到第50页的业务，游标式翻页就不太适用。

4.3 方案三：业务降级，限制最大翻页深度

有些场景里，业务确实需要随机跳页，但用户几乎不会跳到30页之后。比如一个交易后台的订单列表，常规使用集中在最近几十页。与其让数据库硬扛深分页，不如在业务层直接限制最大翻页深度。

具体做法有很多：

• 最大offset限制：比如只允许翻到第100页，超出就返回提示“数据过多，请使用筛选条件”
• 默认加条件缩小范围：比如默认只看最近3个月的订单，缩小数据集后再翻页
• 跳页时使用简版接口：只返回核心字段，减少回表列数，用延迟关联优化
• 对后台的大量数据导出，不考虑翻页，直接走游标拉取或者异步任务生成文件

业务降级方案有时候比任何SQL优化都管用，因为深分页本质上是个不合理需求——真的有用户会一页一页翻到第500页吗？大部分情况下是没人在翻，而是某些爬虫或后台任务在翻。

我在一个后台系统里把最大翻页深度限制为100页（每页20条，总共2000条可见数据），同时补充了“时间范围筛选”功能。从那以后，这类慢查询就再也没有出现过，因为再深的翻页请求直接被拦截了。

4.4 方案四：用覆盖索引兜底，把回表彻底干掉

如果说延迟关联是“减少回表次数”，那么覆盖索引就是“彻底不回表”。如果查询需要的所有列都在索引里，执行计划的Extra列会显示Using index，MySQL直接只扫描索引就返回结果，不需要回表。

比如这个查询：

SELECT id, user_id, order_no, create_time FROM orders WHERE user_id = 123456 ORDER BY create_time DESC LIMIT 100000, 20;

如果在(user_id, create_time, order_no)上建一个联合索引，那么这条查询的所有列（id、user_id、create_time、order_no）都在索引里，可以完全避免回表。

这个方案看着很美好，但实际项目里建覆盖索引要非常克制。原因有二：

1. 索引占用额外空间，尤其是表行数大、查询列多时。一个覆盖索引的体积可能比数据本身的体积还大
2. 写入开销增加，每次INSERT和UPDATE都需要维护更多索引

通常我会在一个高频查询场景（比如订单列表的列表页接口）里使用覆盖索引，因为它列数固定、查询模式固定。而像详情页那种需要查很多列的，就不要去碰覆盖索引了，老老实实用延迟关联。

4.5 表结构层面的补充思路：分批翻页用主键范围

还有一种少有人提但很实用的思路：用主键范围替代offset做批量翻页。这个方法适合后台任务、数据同步、导出全量数据这类场景。

假设要遍历orders表满足某些条件的所有数据，不用LIMIT x OFFSET y，而是记住上一次取到的最大主键，然后：

SELECT * FROM orders WHERE id > 上次最大id AND 其他条件 ORDER BY id LIMIT 1000;

这种翻页方式利用主键索引天然的有序性，每次查询都能快速跳到上一批数据的末尾，不需要扫描和丢弃任何中间数据。因为主键索引本身就是聚簇索引，所以连回表都省了，查询性能和翻页深度完全无关。

这种方案的适用场景很明确：不需要用户交互式翻页，而是程序后台循环读取。我常用于数据迁移、日志归档、报表统计这类任务型场景。跟游标式翻页类似，它们的共同特点都是放弃“任意跳页”能力，换取稳定的性能。

5. 实战排查记录：一次深分页慢查询的完整复盘

5.1 现场信息收集

有一次排查线上慢查询，现象是订单管理后台的列表页从第30页开始明显卡顿，到第50页直接超时。我先收集了三类信息：

第一，慢查询日志里的具体SQL和耗时分布。这里能看到完整SQL文本、扫描行数、耗时等关键信息。第二，表的索引结构。第三，满足过滤条件的数据总量。

通过慢查询日志发现，问题SQL的扫描行数在逐渐增大，从几千涨到几十万。这符合深分页线性增长的预期。

5.2 定位核心瓶颈

查看表结构后发现，表上有单列索引idx_user_id(user_id)，而查询用了ORDER BY create_time DESC。执行计划里出现了两个问题：一是排序字段不在索引里，使用了filesort；二是查询列比较多，需要大量回表。

这一步的排查结论很清晰：用户数据量本身只有几万行，但每翻一页都要扫描全部用户订单+全量排序+回表。虽然数据量不大，但线上并发较高，多个管理后台用户同时翻页，数据库的整体负载就被拉高了。

5.3 三步优化实施

我在那个实例上做了三步优化：

第一步，把单列索引改成联合索引(user_id, create_time)，解决filesort问题，让排序走索引完成。

第二步，把列表页查询改成延迟关联写法，先查主键再回表，减少无效回表次数。

第三步，在业务层加上翻页深度限制，超过50页强制要求用户添加时间筛选条件。

优化后的执行计划，Extra列不再出现Using filesort，查询耗时从500毫秒级以上降到了100毫秒以内。翻了很深的页（比如第49页），耗时依然稳定。

这个案例其实很有代表性。它说明深分页慢并不一定只是OFFSET本身的错，很多时候是排序+回表+扫描多重问题叠在一起。单独优化其中一环效果有限，排查时要把整条SQL的每个环节都过一遍。

5.4 排查中有用的工具与指令

顺手整理几个排查分页慢查询时常用的指令：

• EXPLAIN：必看，注意type、key、rows、Extra四个字段
• EXPLAIN ANALYZE：MySQL 8.0自带，可以给出实际执行时间和具体步骤耗时
• SHOW INDEX FROM 表名：查看表上索引情况，确认是否有多余索引或缺失的联合索引
• SHOW TABLE STATUS LIKE '表名'：查看表数据量估算
• 慢查询日志：确认具体哪些分页SQL是慢查询，统计出现频率

建议在排查深分页问题时，不要一上来就查“怎么优化分页”，而是先搞清楚查询走了哪个索引、排序走了哪条路径、扫描了多少行。这些信息都比优化技巧本身重要。

6. 细节与经验拾遗：几个容易踩坑的操作

6.1 建立联合索引时的字段顺序

针对WHERE user_id = ? ORDER BY create_time DESC LIMIT ?这种查询，联合索引应该是(user_id, create_time)，而不是(create_time, user_id)。

索引匹配遵循最左前缀原则：查询条件是等值匹配user_id，排序字段是create_time。把等值条件的字段放前面，排序字段放后面，才能同时满足“快速定位”和“索引排序”两个需求。如果顺序反过来，MySQL能用create_time做排序，但要先扫描所有满足create_time范围的数据再过滤user_id，效果完全不同。

另外，要注意排序方向的匹配。MySQL 8.0支持降序索引，如果业务上对某个字段频繁倒序查询，可以在建索引时显式声明DESC，否则MySQL在一定程度上需要反向扫描索引，性能表现会略差一些。

6.2 统计信息过期导致执行计划偏差

MySQL优化器在决定使用哪个索引时，依赖表的统计信息。如果表数据发生了大量更新删除，而统计信息没有及时更新，优化器可能对rows的预估严重偏差，选错索引。

这个问题在深分页场景下特别致命：本来该走user_id索引快速定位，结果优化器觉得user_id的区分度不高，选择了别的索引；或者反过来，选择了看似合理的索引，但实际扫描量巨大。

遇到这种情况，可以通过ANALYZE TABLE命令更新统计信息，或者调整相关索引的基数（cardinality）。但是要记住，这只是一个临时措施。定期维护统计信息，关注表的数据变更量，才是长期方案。

6.3 尽量减少回表的列数

延迟关联已经能大幅减少回表次数，但如果业务上能少查一些列，效果会更好。比如订单列表页很多时候只需要显示订单号、金额、状态、时间，没必要把商品快照、退款详情这些大字段也查出来。

把大字段拆分到单独的扩展表，或者在查询中明确列出需要的列，而不是用SELECT *，对回表开销和网络传输都有正面影响。这里我的经验是：列表接口尽量用精简字段，详情接口再查完整数据。这个习惯对很多数据库性能问题都有帮助，不只限于分页场景。

6.4 分页排序不一致问题

深分页还有一个容易被忽略的隐患：排序不稳定。当ORDER BY create_time遇到同一秒内有多条记录时，create_time相同，排序结果不确定，翻页时同一行数据可能出现在不同页，导致用户看到数据重复或遗漏。

解决方式很简单：排序条件里加一个唯一字段兜底，最常用的是主键。

SELECT * FROM orders WHERE user_id = 123456 ORDER BY create_time DESC, id DESC LIMIT 20;

这样排序结果是完全确定的，翻页时不会出现数据错乱。配合游标式翻页时，这种写法会直接影响游标条件的准确性，务必加上主键兜底。

7. 选型总结与个人体会

回顾整个排查链路，MySQL分页慢查询的根因其实可以归纳成一句话：深分页让MySQL做了大量无用的扫描和回表，而OFFSET本身就是罪魁祸首。任何优化方案的本质，都是想办法减少这几部分无用功——要么减少回表次数（延迟关联、覆盖索引），要么彻底跳过无用扫描（游标式翻页、主键范围翻页），要么限制用户翻页深度（业务降级）。

在我个人经验里，遇到深分页慢的问题，我会按下面的顺序决策：

• 如果数据量不大（十万行级别以内），先查执行计划，加联合索引，再试试延迟关联，大概率就能解决问题
• 如果数据量大（百万行以上），且业务对随机跳页没有强需求，优先改成游标式翻页
• 如果业务必须支持随机跳页，那就限制最大翻页深度，并指导用户用筛选条件缩小数据范围
• 后台批量任务类场景，别用OFFSET，直接用主键范围翻页或者游标式翻页

还有一点想特别提一下：不要一上来就想着用其他数据库或者搜索引擎替换MySQL解决深分页问题。很多场景下，MySQL其实是被用错了方式。只要明确了业务场景，选对翻页方案，MySQL撑住千万级数据量的分页查询是完全够用的。

最后分享一个小技巧：排查这类问题时，把LIMIT的数字缩小到1，比如先跑一下这条SQL看能不能快速返回。如果缩到1还是很慢，说明问题出在排序或回表上；如果缩到1变快了，就说明扫描行数太多是主要瓶颈。这个二分定位法帮我节省了很多时间，你可以一试。