企业官网建设流程全解析

如果你维护过任何一个有点体量的业务系统，大概率见过下面这类查询：

select order_no, user_id, amount from t_order order by gmt_create desc limit 10 offset 1000000;

深分页慢是个老生常谈的问题，但真正愿意把原因讲到根上、再把优化方案落地的人不算多。明明只取 10 条，凭什么要跑好几秒？索引好像也加了，为什么还是慢？更麻烦的是，一旦业务量上来，这种 SQL 往往会成为第一号慢查询，拖垮页面不说，数据库连接池都可能被占满。

这篇内容把“limit 10 offset 1000000 为什么很慢”拆开揉碎：先讲慢的底层逻辑，再给复现和定位方法，最后给能直接照着改的优化方案。适合正在排查慢查询的后端开发者、DBA，也适合刚被分页问题困扰的新手。

1. 慢的根源：offset 那一步到底在做什么

1.1 一条分页 SQL 的真实执行顺序

很多人把limit 10 offset 1000000理解成“从第 1000000 条开始取 10 条”，把 offset 当成一个起点标记。数据库没这么聪明。对它来说，这条 SQL 要分好几步走：

1. 找到满足 where 条件的所有行；
2. 按 order by 字段对全部结果排序；
3. 跳过最前面的 offset 行；
4. 取接下来的 limit 行返回。

换句话说，offset 不是“直接跳到第 1000001 行”，而是“把前 1000000 行全部数完再丢掉”。limit 10的意思也不是“只处理 10 行”，而是“最终返回 10 行”。这个差异，是理解整个问题的钥匙。

为了完成排序和条件判断，数据库需要读取的原始数据量，远比你最后看到的 10 行大得多。如果没有索引支撑，那只能是全表扫描：逐行检查条件、逐行送进排序过程。有索引时会好一些，但如果 order by 字段用不上索引，依然要把候选行全部捞出来排序。所以limit 10 offset 1000000真正的耗时，不取决于返回多少行，而取决于“前面被扫描和跳过的那 100 万行”。

1.2 逻辑分页不等于物理定位

还有一层很容易被忽略的误区：分页是“逻辑概念”，数据库里并不存在“第 100000 页”这样一个物理文件位置。页面上的“下一页”对应的是 SQL 里的 offset，但数据库索引和数据文件里，并没有“页号—offset”的映射关系。

你可以把它类比成一家没有目录的仓库：你想取第 100001 个箱子，只能从门口开始，一个箱子一个箱子数过去，数到目标位置。limit 10 offset 1000000的意思是数过前 100 万个箱子，再抱走后面 10 个。仓库里的箱子放得再整齐，也不能让你“瞬移”到第 100 万个箱子附近。索引能帮你更快判断某个箱子是否满足条件，但“跳过 100 万个箱子”这个动作本身，一点都省不掉。

这也是为什么翻到第 10 页很顺滑，翻到第 10000 页开始卡顿的核心原因。深分页的慢，不是某一条 SQL 写废了，而是数据库必须为“跳过”付出真实成本。offset 越大，必须跳过的行数越多。

2. 为什么偏移量越大越慢

2.1 全量扫描与无效读取

慢的本质，是大量无效工作。数据库读取并处理了前 1000000 行，最后却只把其中 10 行返回给用户。那些被读取、被排序、被跳过然后丢弃的行，全部是纯消耗。

如果是全表扫描，成本会随 offset 线性增长：offset 到 100 万时，至少 100 万行要参与进来。更隐蔽的是，行数上来之后，排序的临时数据可能溢出内存，从内存操作退化成磁盘交换，性能会出现断崖式下跌。这也就是为什么有时候 offset 从 1 万涨到 100 万，耗时不是涨 10 倍，而是涨几十倍。

换个角度想：数据库的 B+ 树索引确实能快速定位到某个具体键值，但分页查询没有提供“起点键”，只有“跳过 N 行”的指令。数据库只能从索引的一端开始，一个节点一个节点地遍历，数够 offset 再动手。索引可以帮你缩短单次查找的时间，却无法帮你跳过“数 100 万行”这个过程。

2.2 select * 的回表放大效应

如果查询只取主键，而且排序字段和过滤字段都在联合索引里，数据库完全可以在索引内部完成计算。但业务 SQL 通常写的是select *，按主键回表取完整行。深分页时，“索引层跳过”加上“回表读行”，会把随机 I/O 放大到非常恐怖的程度。

举个例子说明完整过程。假设联合索引包含(status, gmt_create, id)，查询是：

select * from t_order where status = 'paid' order by gmt_create desc limit 10 offset 1000000;

数据库会沿着索引找到所有满足status='paid'的索引项，一路扫描并跳过前 100 万个索引项。每定位到一件需要返回的行，还要根据主键回到聚簇索引读取整行。结果虽然只有 10 行，但前面 100 万个索引项的扫描、以及与聚簇索引的交互，都是实打实的工作量。

所以，深分页时select *和select id的差距会被放大。只选一列时，能更好地利用索引覆盖；一旦带上所有字段，回表次数就会逼近被扫描的行数，磁盘随机读的代价立刻显现。

2.3 排序是容易被忽略的隐藏大头

“limit 10 offset 1000000”通常伴随 order by，排序这个大头，很多人会忽略。

如果排序字段没有索引，数据库会把全部候选行读入排序缓冲区。内存放不下时，中间结果会落到临时文件，形成外部排序。100 万行级别的排序，足够让内存和磁盘来回倒腾好几轮。如果在执行计划里看到Using filesort，说明排序已经是一个独立的高成本阶段。

反过来，如果 order by 字段能命中索引，数据库可以按索引顺序一路扫描，省去显式排序。但注意：它依然要跳过 offset 对应的索引项。索引能降低排序成本，不能消灭 offset 跳跃成本。排序成本和扫描成本叠加在一起，就是深分页慢的最后真相。

3. 实际上手：复现、执行计划与定位

3.1 用实验数据复现慢查询

纸上谈兵没用，先做一个能复现的实验。拿本地数据库建一张订单表，字段不用多：id主键、order_no字符串、user_id、status、gmt_create时间、remark备注。插入大约 500 万行数据，可以用存储过程循环插入，或者用脚本批量生成。

有个细节值得注意：gmt_create最好和id一样单调递增，模拟真实流水。否则数据乱序，排序路径反而会失真。

验证时分别跑这几种查询：

select * from t_order order by gmt_create desc limit 10 offset 0; select * from t_order order by gmt_create desc limit 10 offset 1000; select * from t_order order by gmt_create desc limit 10 offset 100000; select * from t_order order by gmt_create desc limit 10 offset 1000000;

我自己的实验里，趋势大致如下（数值随机器配置变化，但方向不变）：

这个趋势是所有关系型数据库共同的：offset 越大越慢，不是机器差，也不是参数没调好，而是查询方式本身决定了成本。

3.2 执行计划是定位问题的第一工具

为什么慢，不能靠猜，要看执行计划。对上面的查询执行：

explain select * from t_order order by gmt_create desc limit 10 offset 1000000;

重点看四类信息：

• type：如果是ALL，说明是全表扫描；range或index会好一些。
• key：实际用到的索引是什么。如果显示 NULL，说明索引完全没帮上忙。
• rows：预估扫描行数。如果接近 500 万，说明几乎全表都被翻了一遍。
• Extra：出现Using filesort代表有排序临时文件，出现Using temporary说明还用到了临时表，两者都是高成本信号。

一个典型的慢查询执行计划大概长这样：

type : ALL key : NULL rows : 5000000 Extra : Using filesort; Using temporary

看到这个结果，基本可以确认：这条 SQL 把 500 万行全部取出来，排了个序，再丢掉 4999990 行。慢就是必然的。

另外，线上环境建议把慢查询日志打开，阈值先设成 1 秒。抓到的每一条深分页 SQL，都值得专门过一遍执行计划再决定怎么改。

4. 三个能落地的优化方案

4.1 延迟关联：让索引先把活干完

第一种改动，叫延迟关联，也叫覆盖子查询。核心思路是：先在索引上完成分页计算，只查出需要的主键，再用主键回表取完整数据。

原 SQL：

select * from t_order order by gmt_create desc limit 10 offset 1000000;

改成：

select t.* from t_order t inner join ( select id from t_order order by gmt_create desc limit 10 offset 1000000 ) tmp on t.id = tmp.id order by t.gmt_create desc;

为什么有效？子查询只需要访问索引，索引里已经包含了id和gmt_create，不需要回表，也就避免了 100 万次随机 I/O。拿到 10 个主键之后，主表再按主键读取 10 行，代价非常小。

注意：改完仍然要看执行计划，确认子查询真的走的是覆盖索引，而不是重新全表扫描。这个方案适合 offset 还没大到完全无法接受、但又已经明显变慢的场景，改动成本低，效果立竿见影。

4.2 键集分页：彻底告别 offset

要从根上解决问题，推荐键集分页，也叫 seek 方法。思路很简单：不用 offset 指定起点，直接用“上一页最后一条”的排序字段作为下一页的条件。

第一页正常查：

select id, gmt_create from t_order where status = 'paid' order by gmt_create desc, id desc limit 10;

拿到最后一行的gmt_create和id，假设是('2024-06-01 12:00:00', 999999)。下一页查询写成：

select id, gmt_create from t_order where status = 'paid' and (gmt_create, id) < ('2024-06-01 12:00:00', 999999) order by gmt_create desc, id desc limit 10;

这里用元组比较，两个条件一起限定。为什么快？因为查询不再“数着行跳”，而是直接通过索引定位到上一页最后位置，然后继续往下取。每条查询只处理 limit 需要的行，成本从 O(n) 降到接近 O(log n)。

前提是排序字段必须唯一。如果只用gmt_create，遇到同一时间戳的数据会漏行。把id加进 order by 和 where 条件，就能保证排序顺序稳定、不重不漏。

这个方案最大的限制，是不能任意跳页。但冷静想想，业务里“下一页”场景远远多于“跳到第 10 万页”，大部分列表都能接受这种改造。

4.3 跳页需求怎么办

有些管理后台必须支持跳页，比如管理员去第 1000 页改一条数据。面对这种情况，可以分三层处理。

第一层，限制最大翻页深度。产品上最常见也最实用的做法，是允许翻到一定深度（比如 5000 页），超过之后强制要求使用筛选条件或搜索关键词。没有人真的会一条条翻到第 10 万页，这个限制通常不会伤害真实需求。

第二层，把深分页查询迁移到分析型系统。如果管理人员确实需要查极深页的数据，说明你面对的已经不是“列表查询”，而是“检索或分析”。这种场景应该由专门的系统承载，而不是逼着业务库硬扛。

第三层，缓存结果集。浅页的 id 列表可以缓存，深页访问频率很低，直接提示用户缩小范围。缓存要控制好失效策略，避免数据更新后翻页错乱。

4.4 方案对比与选型

选型时先问需求：能不能只做下一页？能，就上键集分页。不能，就结合延迟关联和深度限制一起用。纯粹靠加索引解决深分页，基本是死路。

5. 配套索引和参数：别做无效调优

5.1 联合索引设计要点

键集分页要发挥效果，索引必须覆盖 where 条件和排序字段。常见的建法是这样：

alter table t_order add index idx_status_create(status, gmt_create, id);

顺序有讲究：等值条件字段放最前面，排序字段随后，最后加一个唯一字段用于稳定排序。如果查询里还有其他筛选条件，比如user_id、order_type，要把过滤性更好的字段尽量放到左边。

注意最左前缀原则。如果 order by 是gmt_create, id，而索引建成了(id, status, gmt_create)，排序还是用不上索引，filesort照样出现。索引设计必须跟着执行计划走，不要想当然。

5.2 参数调优要克制

说到sort_buffer_size、tmp_table_size、max_heap_table_size，很多人的第一反应是调大内存。这些参数确实有用，但只是给系统多一点缓冲。如果算法本身就是“全量排序 + 100 万行扫描”，调大内存最多让它晚一点爆，不能根治。

更值得关注的是innodb_buffer_pool_size这类缓存参数。缓存命中率越高，深分页回表时的磁盘 I/O 越少，效果会更明显。另外，连接池要设置查询超时，不然一条深分页 SQL 就能把整个服务的连接占满。

慢查询日志、执行计划采样、rows_examined监控，才是长期维护的抓手。参数可以调，但别把参数当成救命稻草。

6. 深分页避坑清单与实操心得

6.1 我踩过的几个坑

第一，只测第一页。本地跑limit 10 offset 0很流畅，上线后用户一直翻页，才发现越翻越卡。压测必须覆盖深页，不能只看浅页面。

第二，加了索引不验证执行计划。加完索引后，如果 order by 字段顺序和索引列不一致，照样会filesort。每次改完，都要看一眼执行计划确认效果。

第三，深分页还坚持select *。深分页场景下，这几乎是第一杀手。能只查主键就先查主键，业务必须全字段时，配合延迟关联。

第四，忽略前面的count(*)。很多列表慢的不是分页本身，而是计算总页数的count(*)。大表上统计行数同样是一场全表扫描，最好单独做计数缓存。

第五，接口参数从 pageNum 改成 cursor 时，只改后端不改前端。前端页码组件要适配，否则联调阶段会出各种“明明传了 cursor 但还是带 offset”的问题。

6.2 我的一点实操体会

我在一个报表类项目里见过类似的问题：用户反馈翻到几百页之后页面几乎白屏。查慢查询日志，全是limit 20 offset 600000。当时没有急着调参数，先用执行计划确认扫描行数接近全表，再用键集分页重写了接口。上线后最深页面的响应时间，从 6 秒降到了 30 毫秒左右。

后来产品侧把“必须支持跳到任意页码”的需求砍掉了，改成“搜索 + 筛选 + 有限翻页”。整个列表页再没出过性能问题。如果你正被深分页折磨，我建议你先问业务一个问题：真的有人会翻到第 100000 页吗？多数时候，答案是没有。

一旦卸下“任意跳页”这个包袱，性能优化会轻松很多。