企业官网建设流程全解析

做后端开发的兄弟应该都见过这种场面：一张几千万行的订单表，业务方要求前端支持“第10000页”的翻页，一页20条。最开始我天真地以为分页能有什么性能问题，直到某次线上慢查询告警把我半夜叫醒，才发现MySQL分页性能优化这件事，本质上是和LIMIT offset这种“先取再多扔”的机制在做斗争。这篇内容我打算把从问题定位、方案选型、索引设计到参数调优的完整链路写清楚，适合正在被深分页慢查询折磨的读者，也适合刚接触MySQL想建立正确分页认知的新手。

1. 先搞清楚LIMIT到底慢在哪：一次查询的三笔账

1.1 “先取后扔”的取数逻辑

很多人在写SELECT * FROM orders ORDER BY create_time DESC LIMIT 100000, 20时，默认认为MySQL只查了20条。实际上它的执行逻辑是：从头开始扫描，读完前100000条全部扔掉，再取接下来的20条返回。

这意味着每次深翻页，数据库都在白白读取并丢弃海量数据。LIMIT offset, row_count这个语法，本质是把“排除”的工作交给了数据库。offset越大，代价越高，而且是线性增长，不是指数级，但足够让你在页数深了之后直接卡死。

生活化类比：你要从一本1000页的书里找指定位置的内容，页码式分页相当于每次都要从头翻到目标页，翻得越深越累。键集分页则相当于在书页之间夹了一张书签，翻到书签位置直接读下一页就行。

1.2 深分页慢的三重代价：扫描、回表、排序

深分页慢从来不是单一原因，通常可以拆成三笔账：

第一笔是扫描行数。MySQL读取的是offset + limit行，举个例子，LIMIT 1000000, 20实际要读取1000020行，哪怕最终只返回20行。这是最直观的代价。

第二笔是回表。如果查询走了二级索引，比如你建了create_time索引，MySQL先在索引里定位符合条件的记录，拿到主键值，再拿着主键去聚簇索引里取整行数据。深分页场景下，需要回表的行数有可能是几十万甚至上百万，每次回表都是一次随机I/O，磁盘响应时间远大于顺序读。

第三笔是排序。如果ORDER BY字段不能利用索引，MySQL会把候选集放进排序缓冲区（sort buffer），数据量超过缓冲区大小时，就会在临时文件中进行外部排序。这个过程的CPU和I/O开销会被深分页行数不断放大。

1.3 亲手复现一次慢查询的完整过程

纸上谈兵没有用，我习惯第一时间打开慢查询日志确认问题。操作方法很简单：

-- 开启慢查询日志，阈值设为1秒 SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; SHOW VARIABLES LIKE 'slow_query_log%'; SHOW VARIABLES LIKE 'long_query_time';

然后随便造一张百万行测试表，执行一次深分页查询，再用EXPLAIN看执行计划：

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

如果你的执行计划里type是ALL，或者Extra里出现Using filesort，基本可以断定这就是一次灾难级查询。看到Using filesort别慌，它不等于“这个查询一定会慢”，但配合深分页时，慢是大概率事件。

2. 分页场景分类：先看清自家业务到底属于哪一类

2.1 三种常见分页模式

不是所有分页都该用同一种优化手段。我把业务里最常见的分页模型分成三类，它们的差异非常明显：

传统页码式分页：最常见的管理后台列表，带上一页、下一页、跳转到第N页的交互。用户在意的往往是“我能从第1页翻到第20页就差不多了”，但产品经理总喜欢留一个“跳到9999页”的入口。

信息流式分页：App里的订单流水、内容Feed流、消息列表，用户上滑加载更多，不存在页码概念，只需要“后一页”数据。

极速翻页式分页：搜索页、筛选结果页，用户翻几页看到数据够了就走，不需要精确总页数和深翻页支持。

三种模式对性能的容忍度完全不同。第一种最痛苦，因为页码跳转意味着必须支持深分页；第二种是键集分页的天然主场；第三种只要限制最大翻页深度，性能压力就迎刃而解。

2.2 不同模式下的代价差异与优化方向

我整理过一张对比表，供你对照自己业务的形态：

很多团队在没做场景分类之前，就直接套某种优化方案，结果发现“延迟关联”改了之后，前端页码对不上了。这不是方案的问题，是你没告诉产品经理“你这种交互模式本来就不适合深翻页”。

3. 三套经过实战验证的优化方案（含完整SQL与步骤）

3.1 方案一：延迟关联 + 覆盖索引约束

延迟关联的核心思想是：让最耗时的大offset排序只发生在索引上，而不是发生在全表数据上。先把主键ID查出来，再用ID回原表取完整数据，这样深分页部分扫描的是紧凑的索引结构，回表数量被压制到“当前页”那么少。

具体SQL我习惯这么写：

-- 第一步：子查询只查ID，利用覆盖索引，不回表 SELECT t0.* FROM orders t0 JOIN ( SELECT id FROM orders WHERE status = 1 ORDER BY create_time DESC LIMIT 200000, 20 ) tmp ON t0.id = tmp.id ORDER BY tmp.create_time DESC;

这里有个关键细节：SQL_MODE可能包含ONLY_FULL_GROUP_BY等约束，但SELECT t0.*本身不违规；我在子查询里只选id，因为id是主键，二级索引(create_time, status)里如果覆盖了id，子查询就不需要回表。如果你的查询只需要少数几个字段，可以直接把那些字段也放进联合索引里，实现完全覆盖。

改完之后，MySQL的执行顺序变成了：子查询在索引上完成排序和offset过滤（仍然要读20万行索引，但索引体积小、顺序读快），拿到20个主键ID后，外层JOIN只回表20行。实测下来，这个方案能让200000深度分页的耗时从秒级降到百毫秒级。

3.2 方案二：键集分页（游标分页）——彻底告别offset

键集分页的哲学是“记住上一次的位置”，而不是“计算要从哪里开始跳过”。它要求你保留上一页最后一条记录的排序字段值，然后用WHERE条件直接定位下一批数据。

假如你的排序字段是create_time，并且create_time允许重复，那就要搭配唯一字段id做tie-breaker。判断“下一批”的条件必须同时处理两个字段：

-- 假设上一页最后一条记录的 create_time = '2024-06-01 10:00:00', id = 12345 SELECT * FROM orders WHERE status = 1 AND ( create_time < '2024-06-01 10:00:00' OR (create_time = '2024-06-01 10:00:00' AND id < 12345) ) ORDER BY create_time DESC, id DESC LIMIT 20;

这段SQL里(create_time < ? OR (create_time = ? AND id < ?))的组合形态，完全可以在联合索引(status, create_time, id)上高效命中，MySQL会把这个条件转换成索引区间扫描。每一页只扫描“上一页之后”的行，不再从头数起。

这也是我推荐信息流场景用键集分页的原因：无论你翻到多深，单页性能恒定，不会因为用户刷了1万条而变慢。代价是：你没法跳转到随机页码，因为“某个页码对应哪一批数据”这个概念已经不存在了。前端只能做“加载更多”，不能做页码跳转。

提示：键集分页的排序字段务必带上唯一字段一起去比较。否则当create_time出现大量相同值时，会出现重复数据或漏数据，这是新手最容易踩的坑。

3.3 方案三：物化分页——临时表/中间层思路

有些业务场景躲不开“页码跳转 + 深度翻页 + 复杂筛选”，比如后台报表导出、筛选结果集很大的运营后台。这种情况下，与其每次翻页都重算整个候选集，不如把候选集“物化”一次，后续翻页只从物化结果里取切片。

核心操作是先筛选出所有满足条件的ID，存入临时表，再按偏移量分页：

-- 第一步：把符合条件的ID集合物化到临时表 CREATE TEMPORARY TABLE tmp_page_ids ( id BIGINT NOT NULL, page_rank INT NOT NULL, PRIMARY KEY (id), INDEX idx_page_rank (page_rank) ) ENGINE = InnoDB; INSERT INTO tmp_page_ids (id, page_rank) SELECT id, ROW_NUMBER() OVER (ORDER BY create_time DESC) AS rn FROM orders WHERE status = 1; -- 第二步：从物化表里取某一页 SELECT o.* FROM tmp_page_ids t JOIN orders o ON o.id = t.id WHERE t.page_rank BETWEEN 400001 AND 400020 ORDER BY t.page_rank;

临时表在连接断开后自动销毁，所以这种方案通常应用在同一个数据库会话内，或者配合真正意义上的中间表（比如先预计算一批ID放到独立表里）使用。物化分页的额外好处是：如果候选集非常大，你还可以给它加索引、做分区，甚至并行处理。

缺点是物化本身有成本，不适合每次查询都重算；而且数据在物化期间如果发生增删，页码会漂移。我的经验是：物化适合“重筛选、翻深页、可接受结果集略微滞后”的导出型场景，不太适合在线高并发列表。

4. 排序字段与分页性能的关系：选错排序，优化全是白费

4.1 ORDER BY 索引字段与非索引字段的对比

分页优化最容易被忽略的就是排序字段的索引设计。ORDER BY如果没有索引支撑，MySQL需要把符合条件的行全部放到sort buffer里做filesort。深分页场景下，filesort的数据量可能是几十万甚至几百万行，这个排序时间会被进一步放大。

对比一下两种写法：

-- 推荐：排序字段在联合索引里 SELECT * FROM orders WHERE status = 1 ORDER BY create_time DESC, id DESC LIMIT 200000, 20; -- 不推荐：排序字段没有索引支撑 SELECT * FROM orders WHERE status = 1 ORDER BY amount DESC LIMIT 200000, 20;

第二种写法即使加了(status, create_time)索引也帮不上忙，因为要按amount排序。如果这个场景真的必须存在，我会选择把amount纳入联合索引，例如(status, amount, id)，让排序直接走索引。

4.2 多字段排序的索引设计技巧

分页排序经常不止一个字段，比如“按创建时间倒序，再按ID倒序”。这时联合索引的设计原则是：等值条件字段放前面，排序字段放后面，并且尽量保持排序方向一致。

• 等值条件：WHERE status = 1，status放在索引最前面。
• 排序字段：ORDER BY create_time DESC, id DESC，create_time和id依次排在status后面。
• MySQL 8.0支持降序索引，可以创建INDEX idx_status_create_id (status, create_time DESC, id DESC)，让排序完全匹配索引方向。

这里有个反直觉的坑：如果你在子查询里写了ORDER BY create_time DESC LIMIT 200000, 20，而联合索引是(status, create_time ASC, id ASC)，MySQL 5.7及之前版本可能无法完全利用索引完成反向排序，会引入额外的filesort。所以建索引前，建议用EXPLAIN确认Extra里是否有Using filesort。

提示：索引不是越多越好。为了一个低频分页查询加上几个大联合索引，会让INSERT和UPDATE付出写放大代价。我通常会对查询频率和写入频率做个权衡，写多读少的表宁可接受适度慢查询，也不盲目堆索引。

5. 参数配置与查询计划观测：用数据说话，别靠猜

5.1 EXPLAIN必看字段与观测技巧

优化完成后，必须用EXPLAIN验证，而不是只看“感觉快没快”。我重点看这几个字段：

一个常见误区是只看rows忽略了Extra。我遇到过一条语句走了索引，rows也不大，但Extra里有Using filesort，整体查询依然慢。所以每次优化完，我会把执行计划连同真实耗时一起记录在案：

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

用SQL_NO_CACHE避免查询缓存的影响，多次执行取稳定值。优化前记录一次基线，优化后再记录一次，对比才有说服力。

5.2 影响排序和分页的几个关键参数

除了SQL本身，有几个参数值得调。我先把常见参数和推荐方向列出来：

这部分最容易翻车的就是sort_buffer_size。它跟连接数直接相乘计算内存占用：假设你设置8MB，连接池有200个连接，光排序缓冲就能吃1.6GB内存。更合理的方式是保持一个中等值，然后优先确保innodb_buffer_pool_size足够容纳热点索引页，因为分页性能很多时候卡在磁盘I/O，而不是卡在排序本身。

查看当前参数值的方法：

SHOW VARIABLES LIKE 'sort_buffer_size'; SHOW VARIABLES LIKE 'tmp_table_size'; SHOW VARIABLES LIKE 'max_heap_table_size'; SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

注意，tmp_table_size和max_heap_table_size中较小的那个控制着实际临时表内存上限，如果这两个值一个16MB一个1GB，真正起约束的是16MB。

6. 一个2000万行订单的完整优化案例与避坑清单

6.1 优化前：真实业务里的慢查询长什么样

前阵子接手过一个订单列表接口，表里2000多万行数据，字段包括id、order_no、user_id、create_time、status、amount等。原始SQL长这样：

SELECT * FROM orders WHERE status = 1 ORDER BY create_time DESC LIMIT 400000, 20;

线上执行耗时稳定在2.1秒左右。EXPLAIN的结果是type = ALL，rows估算超过400万，Extra里带有Using filesort。这是一次标准的“全表扫描 + 深分页 + 排序”三重奏，业务方还要求前端支持跳到第20000页，继续硬扛下去，慢查询日志每天都是一大片红色告警。

6.2 优化后：两套方案的实际效果对比

我先加了一个联合索引(status, create_time, id)，然后分别测试了延迟关联和键集分页的效果。

延迟关联版本：

SELECT t0.* FROM orders t0 JOIN ( SELECT id FROM orders WHERE status = 1 ORDER BY create_time DESC, id DESC LIMIT 400000, 20 ) tmp ON t0.id = tmp.id;

键集分页版本（以上一页最后一个create_time和id作为入参）：

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

实测对比结果如下：

延迟关联的耗时依然随offset增长，但增速比原始SQL平缓很多；键集分页则完全不受offset影响。业务方最终接受了“首页+上一页+下一页+加载更多”的交互改造，放弃任意页码跳转，接口P99耗时稳定在10毫秒以内。

6.3 我踩过的坑和避坑清单

这次排查和优化踩了不少坑，我总结成清单，你直接照着对照检查就行：

• 不要盲目迷信“加个索引就完事”。索引解决了排序问题，但LIMIT 400000, 20的深offset依然要扫描几十万行索引，延迟还是存在，只是从特别慢变成了没那么慢。

• 键集分页的排序字段必须唯一或组合唯一。只要create_time有重复值，就必须带上id一起比较，否则下一页数据会重复或缺失。

• 不要一上来就SELECT *。如果你只需要order_no和amount，就让查询走覆盖索引，JOIN回表都省了。我在优化案例里就是因为业务字段只需要几个，直接把联合索引设计成全覆盖，速度又提升了一截。

• 不要忽略count(*)的代价。很多分页接口为了显示“总页数”，每次都执行一次SELECT COUNT(*) FROM orders WHERE status = 1，即使你优化了分页SQL，这条count语句也会成为新的超时点。数据量大时，建议用缓存或近似值代替精确总页数。

• 数据发生变化时，页码会漂移。键集分页对这个问题是天然免疫的，因为它定位的是“当前位置之后的数据”；但传统页码式的延迟关联，在前几页有记录被删除时，后面所有页的数据都会整体前移，这需要跟产品沟通，做好预期管理。

• 大范围导出不要走在线分页接口。我在案例里发现很多“分页查询”其实是后台导出任务，一次导出要拉十几万条数据。这种场景请走异步导出任务，分批拉取写文件，不要用网页接口扛。

我自己现在看到新项目还在用LIMIT offset做深分页，都会先问一句“这个页数到底要翻多深”。如果只是常规几十页，老老实实用传统分页加索引就好；如果业务要求无限翻页，尽早说服产品改成加载更多的交互。MySQL分页性能优化，优化的不只是SQL，还优化了产品形态和团队对数据库代价的认知。