企业官网建设流程全解析

1. 先搞清楚 LIMIT OFFSET 慢在哪

1.1 一条分页查询到底怎么执行

看到“LIMIT OFFSET”这个组合，做过 MySQL 分页开发的应该都不陌生。简单查询分页确实方便，但数据量上来之后，翻到越靠后的页，接口就越慢，甚至直接卡死。这个问题的根子不在 SQL 写得复杂，而在 MySQL 执行 OFFSET 的方式——它不是“跳过”，而是“把前面所有行都读一遍再丢掉”。

拿一条最常见的 SQL 举例：

SELECT * FROM order_record WHERE status = 1 ORDER BY create_time DESC LIMIT 100000, 20;

这条语句想取第 100001 页的 20 条数据。MySQL 的实际执行过程是：

1. 从存储引擎读取满足status = 1条件的前 100020 行。
2. 丢掉前 100000 行。
3. 把最后剩下的 20 行返回给客户端。

所以 OFFSET 越大，要读取和丢弃的行数就越多。这里有一个容易误解的点：LIMIT OFFSET 不是“定位到第 100000 行再读 20 行”，而是“从第一行开始，一行一行数到第 100020 行”。这种执行方式和翻阅一本纸质账本很像：账本没有页码，只有连续行号，你想看第 10 万行之后的内容，就得从第一行开始往后数，数到 10 万行之后才算数完。

这就解释了为什么数据量小的时候完全没感觉。表里一共几千行，OFFSET 就算填到 1000，MySQL 也就读一千多行，毫秒级完成。但表里一旦有几百万行、几千万行，OFFSET 填到几十万，MySQL 就要实打实扫描几十万行。这个增长是线性的，数据量越大，OFFSET 越大，性能就越难看。

1.2 深分页的扫描成本是线性增长的

假设每页 20 条数据，翻到第 1000 页时，OFFSET = 19980，MySQL 需要读取约 20000 行；翻到第 100000 页时，OFFSET = 1999980，MySQL 需要读取约 200 万行。从 2 万行到 200 万行，读取量差了 100 倍。

那为什么扫描行数增加会这么致命？因为每一行都不是“白读”的：

• 如果是全表扫描，每一行都要做 WHERE 条件判断。
• 如果走二级索引，每一行都要按主键回表，去聚簇索引里拿完整数据。
• 如果排序字段没有索引，还要把符合条件的数据先放进排序缓冲区，甚至落盘到临时文件。

这些操作都会消耗 CPU、内存和磁盘 IO。数据全在 InnoDB 的 buffer pool 里还好一点，一旦 buffer pool 命中率下降，或者需要从磁盘读数据页，性能就是断崖式下跌。

很多人会把慢日志里的 SQL 拿出来，发现索引都加了，执行计划看起来也没问题，但还是慢。原因就在这里：索引可以让你按顺序扫描，但不能帮你“跳过 OFFSET 前面的行”。只要 MySQL 需要读取 OFFEST + LIMIT 这么多行，深分页的代价就无法回避。

2. 深挖几个让 OFFSET 雪上加霜的因素

2.1 回表：二级索引的隐藏代价

大多数分页查询都有排序字段，比如ORDER BY create_time。为了排序，我们一般会给create_time建二级索引。看起来索引命中了，但如果你查询的是SELECT *，MySQL 在二级索引里只能拿到索引列和主键，拿不到其他列。

InnoDB 的二级索引叶子节点存的是索引字段 + 主键。要取完整行，必须拿主键再去聚簇索引里查一次，这个过程就叫回表。深分页时，回表次数会非常夸张。

举个例子：

SELECT * FROM order_record WHERE status = 1 ORDER BY create_time DESC LIMIT 200000, 20;

假设执行计划走了(status, create_time)这个联合索引，MySQL 会沿着二级索引树的顺序，逐条扫描满足条件的记录。每扫到一条，都拿主键回聚簇索引取一次完整行。扫 20 万条，就要回表 20 万次。

单次回表可能只要几十微秒，但乘以 20 万次，累积起来就是好几秒。而且回表访问的聚簇索引页往往是离散的，不一定连续，buffer pool 的命中率会下降，严重时会产生大量随机 IO。MySQL 有 MRR 优化，可以把主键排序后批量回表，但触发条件有限，不能指望它解决所有问题。

所以“走了索引还慢”的第一个隐藏原因，就是回表次数太多。索引只是让筛选和排序更快，并没有让回表变免费。

2.2 排序：不是所有 ORDER BY 都走索引

深分页另一个容易被忽略的因素是排序。如果ORDER BY的字段和 WHERE 条件无法匹配到同一个索引，MySQL 就要把所有满足条件的数据先取出来，排序后再截取 LIMIT 范围。

排序逻辑大致是这样：

1. 把满足 WHERE 条件的行的排序列和主键放入sort_buffer。
2. 如果sort_buffer装不下，就使用磁盘临时文件做归并排序。
3. 排序完成后，跳过 OFFSET 前面的行，取 LIMIT 行。

如果磁盘上有大量数据需要排序，性能会非常差。这时候 Explain 的 Extra 列里会出现Using filesort，看到这个标志就要警惕。

举一个很典型的错误：

SELECT * FROM order_record WHERE status = 1 ORDER BY create_time DESC LIMIT 100000, 20;

索引只建了(status)，那么 WHERE 很快，但 ORDER BY create_time 用不上索引，必须 filesort。正确做法是把索引设计成(status, create_time, id)，让 WHERE 和 ORDER BY 都能吃到同一个索引。

如果 ORDER BY 还带了一个唯一性字段，比如ORDER BY create_time DESC, id DESC，那索引要设计成(status, create_time, id)，顺序和方向都要匹配。很多 SQL 慢，就是索引字段的顺序和 ORDER BY 条件不一致导致的。

2.3 查询列越宽，深分页越痛苦

还有一类慢，不是慢在扫描行数，而是慢在“数据搬运量”。分页列表页经常被要求展示很多字段，有些字段本身不大，但如果表里塞了TEXT、VARCHAR(2000)这种宽列，情况就不同了。

深分页时，MySQL 每次回表都要把整行数据取出来，包括那些大字段。虽然最终只返回 20 行，但前面的 10 万行、20 万行都已经被完整读取过一轮。行越宽，内存和 CPU 消耗越高，网络传输的数据也越大。

我之前在一张订单表里加了一个remark VARCHAR(2000)，列表页不显示这个字段，但 SQL 是SELECT *。数据量到 200 万行之后，翻到第 2 万页，接口耗时从 300ms 涨到了 1.4s。后来把 SQL 改成只查列表需要的字段，耗时直接降了一大截。

所以排查深分页慢时，先看一眼SELECT后面到底查了哪些列。如果是一大盘不相干的字段，赶紧改掉。覆盖索引能救回表，但救不了无意义的宽列传输。

3. 常见优化方案：不是只有改 LIMIT

3.1 延迟关联：先取主键再回表

延迟关联的核心思路很简单：不要在主查询里直接做深分页回表，而是先用覆盖索引把需要的主键查出来，再通过主键去原表取完整数据。

SQL 可以这样写：

SELECT t.* FROM order_record t INNER JOIN ( SELECT id FROM order_record WHERE status = 1 ORDER BY create_time DESC, id DESC LIMIT 200000, 20 ) tmp ON t.id = tmp.id ORDER BY t.create_time DESC, t.id DESC;

这条 SQL 的关键在于内层子查询。子查询里只查id，排序字段是create_time, id，正好和联合索引(status, create_time, id)匹配。InnoDB 二级索引天然包含主键id，所以子查询可以直接走索引覆盖扫描，不需要回表。

整个过程是：

1. 子查询在索引上扫描 200020 条记录，但只拿id，不回表。
2. LIMIT 之后只剩 20 个id。
3. 外层再通过 20 个主键回原表，拿完整行。

这样回表次数从 20 万次降到了 20 次，效果立竿见影。

实际操作中有个很容易踩的坑：外层忘记加ORDER BY。因为 INNER JOIN 的结果顺序是不保证的，如果你只靠内层子查询排好序，外层查询结果可能乱序。必须要在外层也写一遍排序条件，才能保持分页顺序稳定。

3.2 主键范围分页：最适合“下一页”场景

如果业务只需要“上一页 / 下一页”，不需要跳转到任意页，那最优方案是用排序键做范围过滤，而不是用 OFFSET。

如果你是按主键排序分页，记录上一页最后一条数据的id，下一请求直接查：

SELECT * FROM order_record WHERE id > :last_id ORDER BY id LIMIT 20;

这条 SQL 会利用主键索引做 range 扫描。每次查询只需要读取约 20 行，不管当前是第 1 页还是第 100 万页，耗时都是稳定的。

但要注意，主键范围分页只在ORDER BY id时成立。如果业务需要按create_time排序，直接用id > :last_id判断是不行的，因为id顺序和create_time顺序不一定完全一致。这时候要用排序字段本身作为游标，见下面这一节。

主键范围分页在后台批量处理场景特别有用。比如一个数据导出任务，不能一次性查 100 万条数据，那就用WHERE id > :last_id ORDER BY id LIMIT 5000循环分批拉，每次拉 5000 条，直到没有数据为止。这种方式对数据库非常友好，不会因为数据量增加而变慢。

3.3 复合游标分页：多排序字段的稳定翻页

现实中的列表排序往往不是单一字段，而是ORDER BY create_time DESC, id DESC。此时第一页最后一条记录的排序值是(create_time, id)，下一页的条件就是“严格小于这个游标的所有数据”。

SQL 可以写成：

SELECT * FROM order_record WHERE create_time < :last_create_time OR (create_time = :last_create_time AND id < :last_id) ORDER BY create_time DESC, id DESC LIMIT 20;

这个写法的核心是保证排序稳定。create_time可能有很多重复值，如果只用create_time < :last_create_time过滤，会漏掉与上一页最后一条记录create_time相同但id更小的数据，导致漏数据或重复数据。所以要加上id作为唯一性兜底。

只要表上有(create_time, id)的联合索引，这种写法就能走索引 range 扫描。每次查询扫描的行数接近 20 行，和 OFFSET 无关。代价是业务前端不能随意跳页，只能一页一页往下翻，但换来的是极其稳定的性能。

如果前端非要支持跳页，可以考虑限制最大翻页深度。比如最多允许翻 100 页，超过之后提示用户改用搜索条件。别指望数据库能高效地跳过百万行，这是物理限制。

3.4 覆盖索引和业务层兜底

延迟关联能减少回表，覆盖索引则可以彻底消除回表。如果一个查询的所有字段都在索引里，MySQL 执行时可以直接从索引返回结果，Extra 列会显示Using index。

比如列表页只需要id、order_no、create_time、status这四个字段，可以设计一个联合索引覆盖它们：

KEY idx_status_create_time (status, create_time, id, order_no)

查询写：

SELECT id, order_no, create_time, status FROM order_record WHERE status = 1 ORDER BY create_time DESC, id DESC LIMIT 200000, 20;

这时候 MySQL 只需要扫描二级索引，连聚簇索引都不用回。虽然还是要扫描 200020 条索引记录，但二级索引比聚簇索引小很多，扫描成本低不少。

不过覆盖索引不是越多越好。索引本身要占用磁盘空间，写入时还要维护索引结构，增加写放大。如果你为了覆盖一个不常用的查询硬塞字段，很可能得不偿失。最合理的做法是改 SQL，让列表页字段尽量精简，然后对核心高频查询做覆盖索引。

业务层兜底也很重要。产品设计上可以限制最大分页深度，或者用“加载更多”替代传统数字分页。用户真正翻到 100 页以后的场景其实很少，大部分高深分页压力来自爬虫和异常调用。有些系统甚至直接在 Nginx 或者接口层做了限制：超过 100 页一律拒绝。这种粗暴但有效的方案，应该优先于堆硬件和熬 SQL。

4. 一个模拟项目X的实测：Explain 和耗时对比

4.1 准备一张有点真实的订单表

为了把上面的理论落到地上，我自己搭了一个完全模拟的订单表。表结构如下：

CREATE TABLE `order_record` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `order_no` varchar(32) NOT NULL, `user_id` bigint unsigned NOT NULL, `status` tinyint NOT NULL DEFAULT '1', `create_time` datetime(3) NOT NULL, `remark` varchar(2000) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_create_time_id` (`create_time`, `id`), KEY `idx_status_create_time` (`status`, `create_time`, `id`) ) ENGINE=InnoDB;

故意加了一个remark大字段，模拟列表页常见的问题：明明用不到，却因为SELECT *被反复读取。数据量插到 200 万行左右，全部是程序生成的模拟数据，没有真实业务数据。

4.2 用 Explain 定位扫描行数

先看最原始的深分页 SQL：

SELECT * FROM order_record ORDER BY create_time DESC LIMIT 200000, 20;

我执行EXPLAIN后的关键信息是：type = index，key = idx_create_time_id，rows = 200020。

这里有个非常容易误判的点：看到走了索引就以为没问题，但type = index表示的是“按索引顺序全索引扫描”，它只是避开了 filesort，并没有减少需要读取的行数。由于是SELECT *，每一行还要按主键回表，所以真实的处理行数接近 200020 行。

再看延迟关联版：

SELECT t.* FROM order_record t INNER JOIN ( SELECT id FROM order_record ORDER BY create_time DESC, id DESC LIMIT 200000, 20 ) tmp ON t.id = tmp.id ORDER BY t.create_time DESC, t.id DESC;

执行计划上，内层派生表走idx_create_time_id，Extra列是Using index，说明内层只扫描索引，不回表。外层则是通过主键eq_ref查 20 行。两个操作叠加，扫描成本完全不在一个量级。

4.3 直接上耗时对比

我统计了不同 OFFSET 下两种 SQL 的耗时，结果表格如下（基于模拟项目X的环境，配置不重要，看趋势）：

注意第一行：OFFSET 很小时，延迟关联反而更慢。因为内层子查询的派生表物化有额外开销。所以不要一上来就把所有分页都改成延迟关联，小分页场景没必要。

随着 OFFSET 变大，原 SQL 的耗时快速增长，延迟关联的增长则平缓得多。原因就是回表次数从 20 万次降到了 20 次。

如果换成主键范围分页：

SELECT * FROM order_record WHERE id > :last_id ORDER BY id LIMIT 20;

不管上一页的id是 100 还是 190 万，耗时都稳定在几毫秒左右。这个方案的限制在前面已经说过了，只适用于按主键排序且允许“下一页”交互的场景。

5. 常见问题与排查实录

5.1 为什么用了延迟关联还是慢？

延迟关联不是万能钥匙。实际排查中经常遇到“我明明改了延迟关联，为什么还是很慢”的情况，常见原因有三个。

第一，内层子查询没有走覆盖索引。如果你在子查询里SELECT *，或者SELECT了一些非索引字段，内层依然要回表，延迟关联的效果就没了。子查询应该只查主键或者覆盖字段。

第二，外层查询又出现了 filesort。延迟关联只负责减少回表，外层如果加了一个和索引不匹配的ORDER BY，MySQL 还是要把结果重新排序。最好是让内层和外层的排序字段保持一致，并且都压到同一个索引上。

第三，OFFSET 条件下的选择不同。极端情况下，优化器可能选择先 JOIN 再排序，而不是先 LIMIT 再 JOIN。遇到这种问题，最有效的排查方式是看执行计划：内层派生表的rows是不是接近OFFSET + LIMIT，Extra里有没有Using index。如果你的 MySQL 支持EXPLAIN ANALYZE，直接看实际耗时和循环次数，比猜要靠谱得多。

5.2 为什么明明走了索引，OFFSET 大了还是慢？

这是深分页领域最常见的困惑。一句话回答就是：走了索引不等于扫描行数少。

EXPLAIN里的type = index只是说 MySQL 选择了一个索引来避免排序或者做全表扫描，不代表快速。OFFSET 是 150 万时，即便走索引，也要扫描 150 万条索引记录。索引扫描比全表扫描快，但绝对不是 O(1)。

还有一种情况是 WHERE 条件选的索引没问题，但 ORDER BY 字段不匹配，导致Extra里出现Using filesort。比如 WHERE 条件能用idx_status_create_time，但 ORDER BY 用了一个不在该索引里的字段。这时候 MySQL 要把大量数据取出来排序，深分页必然慢。

在 MySQL 8.0 里，我强烈建议用EXPLAIN ANALYZE去查看真实执行时间。它不止给你显示执行计划，还会给出每个步骤实际处理的行数和耗时。有一次我发现优化器估算rows = 200000，但实际执行时内部循环了 5 次，总处理行数远超预期，问题一下就定位到了 WHERE 条件的过滤性上。

5.3 COUNT 和分页到底谁拖慢了接口？

很多列表接口是先执行COUNT(*)再执行分页查询。深分页慢的时候，连续两三条慢 SQL 一起出现，很容易误判。

COUNT(*)慢不慢，取决于有没有合适的二级索引。InnoDB 在统计行数时不会扫描聚簇索引，如果表里有更小的二级索引，优化器会优先扫描二级索引。SELECT COUNT(*) FROM order_record WHERE status = 1，如果status有索引，MySQL 会扫这个索引统计行数，而不是去扫 200 万行整表。

但就算扫索引，COUNT 的行数还是会随数据总量增长。200 万行和 2000 万行，COUNT 的代价完全不同。如果你的列表页必须显示“共多少条”，建议单独评估 COUNT 的耗时，最好做计数缓存或者用异步统计。不要把 COUNT 慢的原因算到 OFFSET 头上，这是两条不同的慢路径。

5.4 缓存能解决深分页吗？

缓存可以缓解一部分压力，但不是无脑缓存整个分页结果集。

我见过有人把第 1 到第 1000 页的完整列表都放进了缓存，结果数据更新后缓存全部失效，瞬间回源时把数据库打爆。列表页数据是高度动态的，缓存粒度太粗反而容易出问题。

比较务实的做法是：

• 只缓存前几页热点数据，因为绝大多数用户只看前几页。
• 深分页请求直接走游标模式，不依赖缓存。
• 如果业务必须支持跳页，可以限制最多翻 100 页，超过之后要求用户通过条件筛选缩小数据范围。

深分页的根源是数据库要跳过大量扫描行，这个物理代价不会因为缓存而消失。缓存只是把一部分热点数据的代价提前付掉了，并不能改变 SQL 本身的扫描模型。

6. 最后说点我的实操体会

6.1 我在模拟项目X里踩过的坑

第一次优化延迟关联的时候，我犯过一个特别低级的错误：内层子查询排序排得好好的，外层 JOIN 完没有写ORDER BY。测试时发现第 3 页和第 4 页之间偶尔会重复数据。后来才反应过来，JOIN 之后的结果顺序不由子查询决定，必须外层也排序。

第二次踩坑是产品非要支持跳页，我又不想放弃游标的高性能。最后折中的方案是限流加限制最大页数，超过 100 页返回一个友好提示，引导用户用筛选条件缩小范围。产品同意了，数据库也保住了。

还有一次，我在 ORDER BY 里只写了ORDER BY create_time DESC，没加id兜底。当create_time大量重复时，分页结果里出现了重复数据。排序字段不唯一，分页边界就不可靠，这是比性能更深层的正确性问题。

6.2 选型建议

现在遇到分页慢的问题，我的判断路径很固定：

• 表数据量小，OFFSET 又不深，先别折腾，看看是不是查询字段和索引设计不合理。
• 数据量大且必须支持数字页码，优先做联合索引 + 延迟关联。
• 数据量大且产品只接受“加载更多 / 上一页下一页”，直接上主键范围或复合游标。
• 如果连游标都满足不了，比如必须支持随机跳页，那就要考虑限制翻页深度、冷热数据分离，或者引入更适合大范围搜索的引擎。

最后想提醒一句：优化分页不要只看 SQL，要先确认排序字段唯一性，再看执行计划里真实的扫描行数，最后才谈方案。索引不是加得越多越好，偏移量不是换一种写法就能变魔术。理解了 LIMIT OFFSET 的扫描模型，很多优化方案其实都是水到渠成的事。