企业官网建设流程全解析

hello 我是逆境

一张订单表有几千万条数据，同样查询 20 条记录，前几页很快，越往后却越慢。

原因往往在于：返回的数据虽然只有 20 条，但数据库为了找到这 20 条，可能已经处理了上百万条记录。

本文以 MySQL 的 InnoDB 引擎为例，讲清楚深分页为什么慢、常见优化方式，以及面试时应该如何回答。

先从一条常见的分页 SQL 看起。

假设订单表orders包含订单主键id、订单状态status、创建时间created_at、金额amount等字段。

查询已支付订单，按创建时间和主键升序排列：

SELECTid,amount,created_atFROMordersWHEREstatus=1ORDERBYcreated_atASC,idASCLIMIT1000000,20;

LIMIT 1000000, 20的含义是：

跳过前 100 万条符合条件的记录，再返回 20 条。

在可以沿索引顺序读取、且符合条件的记录足够多时，可以理解为：依次取得前1000020个符合条件的条目，跳过前1000000个，返回最后20个。MySQL 的 LIMIT/OFFSET 执行器也体现了这种读取并跳过的方式。:chatgpt-content-reference{index=“0”}

如果还需要过滤更多数据或者额外排序，实际工作量可能更大。

这就是深分页的核心问题：OFFSET 越大，为了跳过前面的记录，数据库做的无用工作就越多。

有人可能会问：有索引，为什么不能直接跳到第 100 万条？

因为普通 B+ 树索引擅长的是按键值定位，例如查找id > 500的起点；它并没有为任意查询结果维护一个“第几条”的位置目录。

因此，“找到某个 ID”和“跳过某个数量的记录”，是两回事。

优化的第一步，是让过滤和排序用上合适的索引。

针对前面的查询，可以考虑建立联合索引：

CREATEINDEXidx_status_time_idONorders(status,created_at,id);

这个顺序对应查询的处理需求：

• 通过status = 1限定订单状态。
• 在状态相同的范围内，按照created_at、id读取。
• 索引顺序与ORDER BY一致，有机会避免额外排序。

优化器是否实际选择这条索引，还需要看执行计划。:chatgpt-content-reference{index=“1”}

这里额外按照id排序，是因为多笔订单可能具有相同的创建时间。加入唯一主键后，可以让排序结果确定；只按时间排序，相同时间的记录之间没有确定顺序。:chatgpt-content-reference{index=“2”}

不过，加索引之后，前面 100 万条记录仍然需要被跳过。

索引可以降低过滤和排序成本，但不能自动消除 OFFSET 的扫描成本。

另外，列表只查询需要展示的字段，避免无意义的SELECT *。但少查字段不代表一定不用回表：本例中的amount不在联合索引里，仍需要读取对应的数据行。

如果必须按页码查询，可以使用“覆盖索引 + 延迟关联”。

先理解两个概念：

• 回表：先从二级索引找到主键，再通过主键到聚簇索引读取需要的数据。InnoDB 的二级索引记录包含主键值。:chatgpt-content-reference{index=“3”}
• 覆盖索引：查询需要的列都能从同一条索引中取得，不必为了补齐其他列再读取数据行。:chatgpt-content-reference{index=“4”}

如果原查询沿着非覆盖的二级索引读取，大量候选记录可能带来大量回表。最终虽然只返回 20 条，前面的读取工作却已经发生了。

根据这个原理，可以推导出一种优化方式：

先利用覆盖索引找到这一页的 20 个 ID，再通过主键读取这 20 条订单的详情。

SQL 如下：

SELECTo.id,o.amount,o.created_atFROMordersASoJOIN(-- 先在索引中完成分页，取出这一页的主键和排序字段SELECTid,created_atFROMordersWHEREstatus=1ORDERBYcreated_atASC,idASCLIMIT1000000,20)ASpageONo.id=page.id-- 外层也要明确排序ORDERBYpage.created_atASC,page.idASC;

这就是延迟关联。

内层查询需要的status、created_at、id都在索引中，可以先确定这一页的 ID；外层再读取对应订单的金额等字段。

在上述执行计划成立时，优化点是：把读取详情的工作推迟到选好这一页之后，让最终这 20 条记录再去读取详情。

但它有一个重要限制：

内层仍然存在LIMIT 1000000, 20，仍需遍历大量索引条目。

所以，面试时不能说“延迟关联把扫描量从 100 万条降到了 20 条”。

准确的说法是：

延迟关联减少了无用的回表工作，OFFSET 带来的扫描成本仍然存在。

如果原查询本来就被索引覆盖，额外增加一次关联，通常也没有这个收益。

如果业务允许连续翻页，游标分页可以进一步减少扫描。

普通分页记录的是“跳过多少条”，游标分页记录的是“上次查到了哪里”。

例如，业务本来就按主键id升序排序，上一页最后一条记录的 ID 是500：

SELECTid,amountFROMordersWHEREid>500-- 上一页实际返回的最后一个 IDORDERBYidASCLIMIT20;

数据库可以根据id定位起点，再向后读取，无须从头跳过此前所有记录。

主键不需要连续。如果500后面是503，就从503开始读取。

但是，不能把 OFFSET 直接当成 ID。删除记录、筛选条件等，都可能让记录位置与 ID 失去对应关系。

回到前面的订单查询，我们按created_at、id排序，就需要同时保存这两个值。

假设上一页最后一条订单是：

created_at = 2026-09-01 10:00:00.000000 id = 12345

下一页可以这样查询：

SELECTid,amount,created_atFROMordersWHEREstatus=1AND(-- 时间更晚，排在上一页最后一条之后created_at>'2026-09-01 10:00:00.000000'OR(-- 时间相同，继续比较主键created_at='2026-09-01 10:00:00.000000'ANDid>12345))ORDERBYcreated_atASC,idASCLIMIT20;

这个条件就是把“排在上一条之后”翻译成 SQL：

先比较时间，时间相同再比较 ID。

如果只写created_at > 上次时间，与上一页最后一条时间相同、但尚未展示的订单就会被漏掉。

示例假设时间字段非空。保存游标时，应保留时间精度，并保持相同的筛选条件。如果改成两个字段都降序排列，下一页的两个比较符也相应改成<。

在索引能够支持范围定位的情况下，读取工作通常接近当前一页所需的数据量。不过，仍存在索引定位、过滤和读取详情的成本，不能说“任何情况下都只扫描 20 条”。

游标分页还有两个限制：

• 不能仅凭页码直接定位。用户要求跳到第 5000 页时，我们并不知道这一页的起点。
• 不会自动固定数据快照。并发增删，或者排序、筛选字段发生变化时，多次请求看到的数据集仍可能变化。

实际选择方案时，要先看业务如何翻页。

对于很深的随机跳页，可以先引导用户缩小时间范围、增加筛选条件，或者限制可翻页的深度。

如果确实需要高效随机跳转，再考虑预计算页锚点或固定结果集，但这些方案会增加维护和一致性成本。

分页接口还有一个容易忽略的瓶颈：查询总条数。

很多分页接口实际执行了两条 SQL：一条查当前页，一条查总数。

SELECTCOUNT(*)FROMordersWHEREstatus=1;

即使当前页已经查得很快，统计总数仍可能很慢。InnoDB 不会直接保存一个对所有事务都适用的精确行数，COUNT(*)需要统计当前事务可见的记录。:chatgpt-content-reference{index=“5”}

如果业务只需要知道“还有没有下一页”，可以查询pageSize + 1条。

例如，每页展示 20 条，就查询 21 条：

• 查到 21 条，返回前 20 条，并设置hasNext = true。
• 不足 21 条，则没有下一页。
• 使用游标分页时，下次游标取实际返回的第 20 条，不能取用于判断的第 21 条，否则会漏掉它。

如果确实需要总数，可以根据允许的数据延迟考虑缓存或异步统计。要求实时、精确，就需要承担相应查询成本。

优化是否有效，最终要通过执行计划和实际耗时判断。

先用EXPLAIN查看：

• key：实际选择了哪条索引。
• type：采用什么访问方式。index可能是全索引扫描，不能直接理解成“很快”。
• rows：预估需要检查的行数，不是实际测量值。
• Extra：Using index表示覆盖索引；Using index condition表示索引条件下推，两者不同。:chatgpt-content-reference{index=“6”}

Using filesort表示需要额外排序，但不一定使用磁盘，也可能在内存中完成。:chatgpt-content-reference{index=“7”}

还可以使用EXPLAIN ANALYZE查看实际执行信息，包括耗时、返回行数和循环次数。注意，它会真正执行 SQL。:chatgpt-content-reference{index=“8”}

验证时，应关注读取阶段处理了多少记录、是否减少了大量详情读取，以及整体耗时是否改善。只看到最终返回 20 条，并不能证明优化成功。

总结：

大数据量分页主要关注深分页问题。LIMIT offset, size的 offset 很大时，数据库仍需要处理并跳过前面的记录；如果还有大量回表或额外排序，成本会进一步增加。

我会先用 EXPLAIN 检查执行计划，根据过滤条件和排序条件设计联合索引，并且只查询需要的字段。如果必须支持按页码跳转，可以先用覆盖索引查出这一页的 ID，再关联查询详情，减少无用的回表，但它仍有 OFFSET 扫描成本。

如果业务允许连续翻页，我会使用游标分页，保存上一页最后一条记录的排序值，下一次从该位置做范围查询。排序字段不唯一时，需要加主键保证顺序确定。最后还要检查 COUNT 总数查询，并结合实际执行信息验证收益。