MySQL 深度分页如何优化？

2026年01月04日

面试考察点

面试官提出这个问题，通常希望考察以下几个层面的能力：

问题诊断能力：你是否能清晰地说出深度分页（LIMIT 偏移量过大）导致性能问题的根本原因，而不仅仅是背诵优化方案。面试官不仅仅是想知道“怎么做”，更是想知道你为什么需要这么做。
解决方案的广度和深度：你是否掌握多种不同场景和粒度的优化手段，并能评估其优劣。这反映了你的技术储备和解决复杂问题的思维广度。
技术原理结合实践的能力：优化的方案是否基于对数据库索引（尤其是 B+Tree）、查询执行过程（如“回表”）等核心原理的理解。能否将原理落地为具体的 SQL 写法。
架构思维：在极端或海量数据场景下，是否能跳出单纯的 SQL 优化，从系统架构或数据存储层面提出根本性的解决思路。
权衡与取舍意识：任何优化方案都有其代价和限制。你是否能说明每种方案的适用场景、前提条件和潜在缺点，这体现了你的工程实践经验。

核心答案

深度分页的典型慢查询形式为 SELECT * FROM table ORDER BY id LIMIT 1000000, 20，其性能瓶颈在于，MySQL 需要先读取并丢弃前 1000000 条记录，然后才返回接下来的 20 条。优化的核心思想是：尽可能地利用索引，减少或避免数据库扫描和丢弃大量无用数据的过程。

主要的优化方案有：

利用覆盖索引优化：如果查询字段能被索引完全覆盖，可先通过索引快速定位。
使用子查询 / JOIN 延迟关联：先用索引覆盖查询快速拿到主键 ID，再用这些 ID 关联回原表获取完整数据。
使用游标分页（连续翻页）：记录上一页最后一条记录的唯一标识（如自增 ID、时间戳），下次查询直接从这个标识之后开始。
业务与架构层优化：禁止过深的页码跳转，或使用更专业的搜索引擎（如 Elasticsearch）来承载海量数据的分页查询。

深度解析

原理/机制

以 SELECT * FROM t ORDER BY key LIMIT 1000000, 10 为例，假设 key 字段有索引。

未优化时的执行流程：服务器会通过 key 索引定位到第一条符合条件的记录，然后顺着叶子节点的链表向后扫描 1000000 + 10 条记录。注意，虽然使用了索引排序，避免了 filesort，但 InnoDB 引擎为了获取 SELECT * 中的其他列数据，需要根据主键 ID 进行大量的“回表”操作。扫描和回表这 100 多万次 I/O 就是性能杀手。
覆盖索引的威力：如果查询只需 SELECT id, key，且 (key, id) 构成了一个覆盖索引，那么整个查询可以在索引树上完成，无需回表，速度会快很多。这为“延迟关联”方案提供了基础。

代码示例与实践

方案一：覆盖索引 + 延迟关联 这是最通用和推荐的内核级优化方法。

-- 原始慢查询
SELECT * FROM `user` ORDER BY create_time DESC LIMIT 1000000, 20;

-- 优化后：先通过覆盖索引快速定位到目标主键
SELECT * FROM `user` AS u
INNER JOIN (
    SELECT id FROM `user`
    ORDER BY create_time DESC
    LIMIT 1000000, 20
) AS tmp ON u.id = tmp.id; -- 再用主键高效地关联回原表获取所有列

执行过程解析：

子查询 SELECT id FROM user ... 利用了 (create_time, id) 索引（或主键索引），它仅扫描索引叶子节点，快速找到第 1000000 到 1000020 条记录的 ID。这个过程比原查询快得多，因为不需要回表。
外层查询用这 20 个 ID 去关联原表。由于 ID 是主键，关联效率极高（近似 20 次主键查询），最终只进行了最少且必要的回表操作。

方案二：游标分页（连续翻页） 适用于“无限下拉”或“下一页”这类连续翻页场景，不适合直接跳转到任意页码。

-- 第一页
SELECT * FROM `user` WHERE `deleted` = 0 ORDER BY id DESC LIMIT 20;
-- 假设返回的最后一条记录的 id 是 10020

-- 获取“下一页”时，利用上一页的最后一条ID
SELECT * FROM `user`
WHERE `deleted` = 0 AND id < 10020 -- 关键：直接定位到开始位置
ORDER BY id DESC
LIMIT 20;

优点：无论翻到第几页，查询速度都恒定且极快。限制：

必须有一个唯一且有序的字段（如自增主键、时间戳）。
数据不能有缺失或动态排序变化（如 ORDER BY score，score 可能变化）。
需要客户端维护“上一页最后一条记录”的状态。

对比分析与最佳实践

方案	原理	优点	缺点/限制	适用场景
延迟关联	利用覆盖索引减少回表	通用性强，对业务逻辑侵入小	偏移量极大时，子查询扫描索引本身仍有成本	大多数需要深度随机跳页的场景
游标分页	记录位置，直接定位	性能最优，O(1) 复杂度	无法随机跳页；要求数据有序稳定	无限下拉、连续翻页（如APP信息流）
业务限定	禁止过深查询	简单有效	牺牲了部分产品功能	配合“仅展示前N页”使用
搜索引擎	将查询压力转移	海量数据分页性能卓越	引入额外系统复杂度，有数据同步延迟	电商商品搜索、日志查询等海量数据场景

最佳实践与常见误区：

首选延迟关联：在大多数需要支持跳页的 Web 后端服务中，这是平衡了性能与功能性的最佳选择。
避免 SELECT \*：务必按需取字段，为使用覆盖索引创造条件。
索引是根本： ORDER BY 和 WHERE 条件必须有效利用索引，否则任何分页优化都大打折扣。
前端配合：与产品协商，限制可跳转的页码深度（如只展示前 100 页），这是成本最低的优化。
误区： “把 LIMIT M, N 改成 LIMIT N OFFSET M 能优化”。这只是语法不同，执行逻辑完全一样，没有性能区别。

总结

深度分页优化的本质是减少不必要的磁盘扫描和回表操作，核心路径是 覆盖索引 -> 延迟关联 ，对于连续翻页场景则优先采用 游标分页 ，并在业务和架构层面根据数据规模做相应的妥协或升级。

发布于2025年12月23日|11:01:26