企业官网建设流程全解析

你有没有遇到过这种一条 SQL 从快到慢的过程？一个管理后台的订单列表，上线初期翻到第 10 页、第 20 页，响应时间都稳稳压在几百毫秒以内。结果数据量涨到几百万行，翻到第 200 页时接口直接卡了几秒，再往后就开始超时。DBA 抓出慢查询日志一看，就是一句看起来不能再普通的SELECT * FROM orders ORDER BY id DESC LIMIT 80000, 20。这是 MySQL 分页里最典型也最容易踩的坑——LIMIT offset, size这种写法，在小数据量时毫无感知，数据一旦上来，offset 越大，查询就越慢。这篇文章就是我排查一个接口性能问题时的完整复盘，从执行原理到优化方案，再到不同场景的选型思路，一次讲清楚。

本文适合天天写业务 SQL 的后端开发、正在被报表接口折腾的运维同学，以及所有在 MySQL 上做过大量列表页的人。无论你是刚接触分页的新人，还是已经知道“深分页慢”但说不清原理的老手，都能在这里拿到一套可以直接用的排查路径和优化方案。下面先从一个最基本的执行流程开始拆。

1. 分页慢的根源：LIMIT offset, size 在 MySQL 里到底做了什么

1.1 一条分页查询的完整执行流程

先明确一个基础问题：SQL 里写LIMIT 80000, 20，MySQL 是怎么执行的？很多人误以为数据库能直接“跳过前 80000 行”，然后从第 80001 行开始取 20 行。但真实情况是，MySQL 并不具备这种“跳行”能力。

标准执行流程是：数据库从满足 WHERE 条件的第一条记录开始，沿着索引或者堆表逐条扫描，每扫到一条就计数，一直数到offset行之后，再继续读取size行，最后把前面offset条记录丢弃，只返回最后那size条。也就是说，LIMIT 80000, 20实际需要访问大约 80020 行数据，只不过前面 80000 行“看完就扔”，不进入结果集。

不理解这个流程，后面所有优化方案都很难真正吃透。你可以把它想象成在一本几千页的书里找内容：如果必须从第 1 页开始逐页翻，翻到第 500 页才能看到目标页，那这个动作的耗时就和页码大小成正比。MySQL 的offset就相当于这个页码，区别在于书的每一页都很薄，而数据库里的每一行可能是几百字节甚至几 KB 的完整记录。

1.2 offset 越大，扫描和回表的行数线性增长

继续上面的执行流程，不难得出一个结论：LIMIT offset, size的扫描行数大致等于offset + size。比如：

• LIMIT 0, 20：扫描约 20 行；
• LIMIT 10000, 20：扫描约 10020 行；
• LIMIT 1000000, 20：扫描约 1000020 行。

返回的数据量没有变化，都是 20 行，但数据库付出的工作量完全不是一个量级。更关键的是，如果查询走了二级索引，并且需要回表读取完整行记录，那前面offset + size行里的大部分行都要做一次“索引定位 + 主键回表”。这个回表动作才是深分页性能恶化的核心元凶之一。

我在实际排查中见过很典型的案例：一个订单表用(user_id, create_time)作为二级索引，业务 SQL 是SELECT * FROM order_info WHERE user_id = ? ORDER BY create_time DESC LIMIT 200000, 20。从索引角度看，MySQL 能很快定位到满足user_id条件的首行，但为了拿到第 200000 行，它必须沿着这个二级索引连续扫 200020 条索引记录。扫描索引本身还能接受，真正可怕的是每一条记录都要回表拿整行数据，而回表对应的主键在物理页上是分散的，结果就是产生了大量的随机 I/O。

1.3 加上 ORDER BY 之后，问题往往更严重

如果分页查询里只有LIMIT，没有ORDER BY，MySQL 还可以按索引自然顺序扫描。但业务上几乎所有分页都会带ORDER BY，比如按创建时间倒序、按下单金额降序。一旦排序字段上缺少合适的索引，MySQL 就得先把所有满足 WHERE 条件的行读出来，做一次文件排序（filesort），排完序后再截取offset到offset + size的部分。

这个过程里有两个致命点：第一，参与排序的行数不会因为 LIMIT 而减少，排序代价和满足条件的数据总量直接相关；第二，当排序数据量超过sort_buffer_size时，MySQL 会把中间结果写到磁盘临时文件，产生额外的磁盘 I/O。两种因素叠加，深分页的慢就会从“扫描慢”演变成“排序也慢”，响应时间比单纯的大 offset 更容易失控。

我自己踩过一个很典型的坑：某个报表接口按create_time倒序分页，但表里根本没给create_time建索引。刚开始数据量只有几十万，勉强能跑；等数据量到了千万级，排序直接开始写临时文件，接口稳定超时。那时我才意识到，分页优化的第一步不是换 SQL 写法，而是先保证排序字段有索引。

2. 深分页变慢的本质：MySQL 为什么不能直接跳到 offset

2.1 没有“跳行”能力，只有“逐条累计”

前面说的执行流程已经隐含了问题的本质。MySQL 的 InnoDB 存储引擎用 B+ 树组织数据，B+ 树能快速定位某个键值，但无法根据“这是第几行”来直接定位。因为行号是一种逻辑概念，不是物理存储位置。

举个例子，你要找“第 100000 行”，数据库并不知道这行数据长什么样、主键是多少。它必须从第一条记录开始一路数过去，期间还要判断每一行是否满足 WHERE 条件。只有累计到第 100000 行时，才能确认“哦，这就是那一行”。这种逐条累计的行为，让offset天然背负着 O(offset) 的扫描成本。

所以，想让分页快起来，最根本的思路就是把“按行号跳转”改成“按条件定位”。后者是 B+ 树最擅长的事情：我知道上一页最后一行的主键是 12345，那直接WHERE id < 12345 ORDER BY id DESC LIMIT 20，MySQL 可以从主键 12345 这个位置开始反向扫描，最多只要读 20 条即可。这个区别，就是后面所有优化方案的理论基础。

2.2 排序字段不唯一，还会引发重复数据和漏数据

深分页带来的问题不只是慢，还有一个很容易被忽略的坑：页码不稳定。

假设一个评论列表用ORDER BY create_time DESC分页，而同一毫秒内有多条评论。MySQL 在排序时如果create_time相同，记录的先后顺序是不确定的，不同页之间可能互相穿插。用户在翻页时会发现：上一页最后一条记录，下一页第一条记录还是同一条，或者更糟，某条记录在上一页出现过、这一页又出现。用户感知就是“数据重复了”。

这也是后来社区一致推荐“游标分页”的原因之一。游标分页不依赖页码和行号，而是记住上一页最后一条记录的位置，用(create_time, id)这种组合条件去定位。主键 id 在表里是唯一的，把它和排序字段组合起来，就能保证全局顺序唯一，从根源上消除重复和遗漏。

2.3 随机 I/O 与缓冲池污染，拖垮的不止一条 SQL

深分页的另一个隐性成本，在数据库实例层面。当一条LIMIT 500000, 20的查询在线上执行时，它会触发大量的主键回表。前面 50 万条回表记录分布在不同数据页上，每一页数据都会被加载到 InnoDB 的缓冲池中，挤占原本用于热点数据的缓存空间。

这种“大量扫描 + 大量回表”的行为，会让缓冲池发生严重的 LRU 污染。哪怕这条查询最后只返回 20 行，它访问过的数据页可能多达几十万个，这些页把真正的热数据挤出去，之后其他高频查询的命中率也会跟着下降。这就是为什么深分页问题常常表现为“某条慢 SQL 出现后，整个实例的响应都变差了”。

我自己在维护一个调用量较高的电商后台时遇到过类似情况：一个运营人员闲着没事，把一个统计列表翻到了第 500 页，结果那两分钟里，其他接口的 P99 延迟直接翻倍。排查到最后，锅绝大部分都扣在了这条深分页 SQL 上。

3. 实测对比：延迟关联、游标分页、覆盖索引，谁最靠谱

3.1 延迟关联：先查主键，再回表，只回目标行

延迟关联（deferred join）是深分页优化里最经典、改动成本最低的方案。核心思路是把“大 offset 的扫描”限制在索引树上，回表只发生在最后那size行上。

看 SQL 更直观：

-- 原始写法，深分页时回表 100020 行 SELECT * FROM orders ORDER BY id DESC LIMIT 100000, 20; -- 延迟关联写法 SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY id DESC LIMIT 100000, 20 ) tmp ON o.id = tmp.id ORDER BY tmp.id DESC;

内层子查询只查主键id，因为主键是聚簇索引，扫描的是索引页，不需要回表。等到确定了目标 20 个 id，再由外层查询通过主键回表取完整记录。这样回表次数从 100020 次降到了 20 次，性能差距非常明显。

实际测试时，我在一个约 800 万行的订单表上跑过：LIMIT 500000, 20的原始查询大约在 2.3 秒左右；改成延迟关联后，同样的条件能压到 0.1 秒左右。不同机器、不同数据分布下具体数字会有差异，但量级上的差距基本是几十倍起步。

不过要提醒一点：延迟关联不是银弹。如果排序字段没有索引，内层子查询本身也要走 filesort，那问题只是从外层移到了内层。所以用这个方案前，必须确认ORDER BY字段上有合适的索引，最理想的索引形式是(排序字段, id)或者(过滤字段, 排序字段, id)。

3.2 游标分页：彻底告别大 offset，每页扫描量恒定

游标分页也叫“书签分页”，它的做法是：在 SQL 中不写LIMIT offset, size，而是利用上一页最后一条记录的某个有序字段，作为下一页的定位条件。

最简单的按主键倒序：

SELECT * FROM orders WHERE id < 12345 ORDER BY id DESC LIMIT 20;

这个 SQL 里没有 offset，MySQL 从主键 12345 的位置直接开始反向扫描，每翻一页都只要读 20 条左右，扫描成本恒定。无论用户翻到第 1 页还是第 10000 页，性能都不会出现劣化。

如果业务需要按create_time排序，只用单个字段还是会遇到重复数据问题。正确的游标写法是“排序字段 + 主键”双条件：

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

这样做的原理很简单：复合条件能唯一定位到“上一页最后一条记录”的位置，无论create_time有多少重复，id都能兜底，保证下一页一定从这条记录之后开始。对应的索引要建(create_time, id)。

游标分页唯一的弱点是：不支持跳页。你不能直接告诉后端“给我第 5 页的数据”，因为后端只知道上一页的最后位置。这个限制在 C 端信息流和移动端“下拉加载更多”场景下完全不是问题，但在传统管理后台“用户手动输入页码”的场景就需要再权衡。

3.3 覆盖索引：让查询在索引页内就完成

有时业务限定只能用LIMIT offset, size，又不方便改造游标，这时候“覆盖索引”可以缓解问题。覆盖索引指的是查询需要的所有字段都包含在同一个索引里，MySQL 扫描索引时就能直接拿到数据，不需要回表。

举个例子，一个文章列表页只展示id, title, create_time：

SELECT id, title, create_time FROM article WHERE category_id = 10 ORDER BY id DESC LIMIT 100000, 20;

如果有一个联合索引(category_id, id, title, create_time)，MySQL 可以直接在二级索引上完成过滤、排序和取值，全程不回表。深分页的主要成本就从“回表 + 随机 I/O”降为了“顺序扫描二级索引”，性能提升明显。

但覆盖索引的代价也很现实：索引本身会占用更多存储空间，写入时维护成本更高。实际运维时，我不会为了“所有分页查询”都去建超宽联合索引，只会针对“高频且字段固定”的查询做定点优化。字段一多、一杂，覆盖索引反而变成负担。

3.4 三种方案对比

如果你的项目被迫保留传统 offset 分页，我会优先推荐“延迟关联 + 覆盖索引”组合：内层子查询在覆盖索引上完成，外层只回表最后几条。这样在支持跳页的前提下，能做到尽量接近游标分页的性能。

4. 不同业务场景下的分页方案选型

4.1 后台管理端：保留 offset，但要加约束

后台管理系统的特点一般是数据量大、查询条件多、使用者是内部员工。这类系统往往要求能跳页，而且产品上经常会给出“共 150000 条，当前第 3772 页”这种信息，直接换游标分页几乎不可能。

我的建议是在保留LIMIT offset, size的前提下，做三件事：

第一，给所有排序字段补索引。后台列表最常见的排序字段是创建时间，给create_time加索引能避免 filesort。如果要同时按多个字段排序，就建联合索引。

第二，限制最大偏移量。很多管理后台莫名其妙的性能问题，都源于有人把列表翻到了几千页之后。可以在代码层设置阈值，比如offset > 50000时直接提示“数据过深，请缩小筛选范围”，从业务侧避免深分页。

第三，强制用户加筛选条件。一个报表查询，如果不限定时间范围就能全表翻页，MySQL 迟早会被拖垮。我在实际项目中做过一次改造：订单列表必须选择起止时间，且时间跨度不能超过 31 天。加了这一个限制，最大扫描行数直接降了一个数量级。

4.2 C 端信息流与移动端：无脑优先游标分页

App 和 H5 里的信息流、评论列表、动态列表，用户的操作习惯是“上拉加载更多”，几乎没有“跳转第 3 页”的需求。这类场景和游标分页是天作之合。

实现时通常会在接口里传两个参数：last_id和last_sort_value。后端拿到上一页最后一条记录的排序字段和主键，拼成WHERE (sort_field < ? OR (sort_field = ? AND id < ?))这样的条件，配合LIMIT size取下一页。

要注意的是，很多产品会在这里偷懒，只传一个last_id，但排序字段是create_time，结果导致数据重复或漏数据。正确做法是“排序字段 + 主键”组合定位，两者缺一不可。另外，游标分页对数据变更也是敏感的：如果上一页最后一条记录在用户下拉前被删除了，下一页定位条件仍然能正常工作，因为条件判断的是“位置”而不是“某条具体记录是否存在”，这一点比单纯记 id 更稳。

4.3 需要跳页的复杂场景：用缓存和业务规约兜底

有些系统确实既需要快速响应，又需要跳页，典型如开放平台 API 分页接口、运营后台的批量列表。MySQL 在深分页上的劣势是天然的，我的经验是不要硬抗，而是从架构和业务上找突破口。

一种常见做法是热点页码缓存。用户最常访问的是前 20 页、前 50 页，这些页面的结果可以缓存到 Redis 里，直接把深分页挡在数据库之外。超过缓存范围后，要么明确提示“仅支持查看前 N 页”，要么引导用户通过搜索、筛选来缩小数据范围。

另一种做法是“时间分片 + 按偏移分页”。比如一个交易流水表，用户希望查到半年前的历史数据，那就不应该用一个大 offset 去扫全表，而是让用户先选月份，SQL 变成WHERE month = '2024-01' ORDER BY id LIMIT 1000, 20。把一个大偏移拆成多个小偏移，每个小偏移对应的数据量都可控。

说到底，任何分页方案的选型，都要先回到产品需求：“用户真的需要翻到那么深吗？”大多数时候，深度翻页只是因为没有筛选项或搜索功能，而不是真的有这种使用场景。

5. 定位与排查：如何确认一条 SQL 确实死于深分页

5.1 从慢查询日志里捞出元凶

排查分页性能问题的第一步，永远是先定位到具体 SQL，而不是凭感觉猜测。MySQL 的慢查询日志是标准工具，可以在线开启：

SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1;

long_query_time表示超过 1 秒的查询会被记录。线上环境建议设置成 1 秒，性能敏感场景甚至可以设到 0.5 秒。开启后，再过一段时间查看慢查询日志文件，就能看到所有执行时间超标的分页 SQL。

如果日志量很大，可以用mysqldumpslow工具做聚合，把执行次数多、总耗时高的 SQL 排到最前面。我在一次线上问题排查中，就是靠 mysqldumpslow 发现某条分页 SQL 一小时里被执行了 3000 多次，平均执行时间 3.8 秒，从而锁定了问题源头。

5.2 用 EXPLAIN 判断执行计划的真实状态

找到慢 SQL 后，下一步就是对它执行EXPLAIN，看执行计划。

关注几个核心字段：

• type：全表扫描是ALL，走索引是range或ref。如果看到ALL，说明连最基本的索引都没用上，先解决这里。
• key：实际使用的索引是哪一条，是否和预期一致。
• rows：优化器预估扫描行数。对于LIMIT 100000, 20的查询，rows通常会显示一个非常大的数字，和实际offset + size接近。
• Extra：如果出现Using filesort，说明排序字段没有走索引；出现Using temporary，说明可能用了临时表。

MySQL 8.0.18 之后还提供了EXPLAIN ANALYZE，会真的把 SQL 执行一遍，并输出实际扫描行数和每步耗时，比传统 EXPLAIN 更直观。遇到深分页问题时，我非常推荐用这个工具验证自己的猜想。

EXPLAIN ANALYZE SELECT * FROM orders ORDER BY id DESC LIMIT 100000, 20;

输出结果中，actual rows=100020 ... actual time=...这类信息会把深分页的真实代价直接摆到明面上。

5.3 一个真实案例：从 6 秒到 0.2 秒的排查过程

之前有个报表接口，查询某段时间内的订单流水，分页大小 20，响应时间从 800ms 一路涨到 6 秒。我按照上面的思路排查了一遍过程如下：

第一步，从慢查询日志里捞出了 SQL：SELECT * FROM order_detail WHERE pay_time BETWEEN ? AND ? ORDER BY pay_time DESC LIMIT 200000, 20。

第二步，EXPLAIN一看，type=ALL，rows=2300000，Extra=Using filesort。原因很清楚：pay_time上没有索引，每查一次就要全表扫描加文件排序。

第三步，给pay_time建立了单列索引。执行计划从ALL变成了range，filesort 消失，响应时间降到大约 1.8 秒。但这还不够。

第四步，执行计划显示rows=1800000，说明数据库仍然要扫描大量行。这时候我把 SQL 改成延迟关联：

SELECT o.* FROM order_detail o INNER JOIN ( SELECT id FROM order_detail WHERE pay_time BETWEEN ? AND ? ORDER BY pay_time DESC LIMIT 200000, 20 ) tmp ON o.id = tmp.id ORDER BY tmp.pay_time DESC;

同时把索引升级为(pay_time, id)联合索引，让内层子查询可以完全走覆盖索引。最终响应时间稳定在 0.2 秒左右，整个排查改造过程用时不到一小时。

这个案例说明，深分页优化往往不是一步到位，而是“先消除全表扫描，再消除回表放大”。如果跳过第一步直接改 SQL，索引没建对，延迟关联也很难落地。

6. 常见问题速查表与避坑经验

6.1 分页问题速查表

6.2 避坑清单，都是从线上事故里总结出来的

第一，不要用ORDER BY RAND()做随机分页。每次执行都会对所有行排序，数据量一到百万级就是灾难。真要随机取数据，先取一个随机 id 范围再查询，代价小得多。

第二，不要在排序字段上套函数。ORDER BY DATE(create_time)这种写法会让索引彻底失效，因为 MySQL 无法用 create_time 索引直接满足 DATE 函数的排序结果。正确的做法是查询条件用create_time >= ? AND create_time < ?，排序直接用create_time。

第三，尽量避免SELECT *。深分页查询每多取一个不需要的字段，回表压力就多一分。把查询字段裁剪到业务真正需要的列，优化效果立竿见影。

第四，批量任务不要用大 offset 分页循环。比如定时任务需要分批处理 100 万条数据，有人习惯写LIMIT 0, 1000、LIMIT 1000, 1000……这种写法越到后面越慢。正确做法是用主键游标：

-- 每批取 1000 条，记录这批的最大 id SELECT * FROM target_table WHERE id > :last_max_id ORDER BY id LIMIT 1000;

每条 SQL 都从上次结束的位置继续，扫描行数恒定为 1000 左右。

第五，关注事务隔离级别下的快照行为。分页查询如果放在一个长事务里，又涉及大量读取，会让 undo log 膨胀，间接拖慢整个实例。事务尽量短，不在事务里做深分页遍历。

第六，避免不必要的重复排序。有时候外层查询和内层子查询各写了一次 ORDER BY，但外层的结果顺序已经和内层一致，完全可以不重复排。这也是很多人写延迟关联时容易漏掉的细节。

第七，新接口开发时提前定好分页规范。我在团队里的约定是：面向用户的列表，默认游标分页；管理端列表，默认 offset 分页但必须限制最大偏移和强制筛选条件。宁可开发时多花半小时改造接口，也不要在线上数据量涨上来之后靠补索引续命。

写在最后：分页方案不只是 SQL 问题

我自己经历过的项目，凡是分页出过事故的，几乎都有一个共同点：初期把LIMIT offset, size当成了唯一的列表实现方式，没有考虑数据增长后的行为。现在我的默认策略很简单：新写的面向用户列表接口一律使用游标分页，后台管理列表保留 offset，但一定加上页数上限和筛选条件。

如果一个列表既要求跳深页、又要查询快，我会先怀疑需求本身是不是没被拆解清楚——比如“翻到 500 万条后面的数据”真的有用户需要吗？能不能通过搜索、筛选项或导出任务来解决？至少在 MySQL 这一层，深分页的代价是实打实的扫描和回表，与其在 SQL 上冒险，不如早点在交互和架构上做取舍。

最后再分享一个小技巧：如果接口暂时只能接受 offset 分页，又担心深分页把数据库拖垮，可以在应用层加一行限制，offset超过 50000 时直接返回“数据过深，请缩小查询范围”。虽然看起来有点粗暴，但效果非常稳，一个阈值就能避免绝大多数性能事故。等业务真的需要更深的数据时，再来考虑游标和其他高级方案，才是性价比最高的路径。