MySQL 深度分页性能优化与原理分析

为什么传统分页在高并发下会变慢？

在 MySQL 数据库中，LIMIT offset, size 是最常用的分页语法。要理解它的性能痛点，我们需要从引擎的执行过程来看。假设有如下 SQL：

SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20;

Mariadb/MySQL 数据库在执行此查询时，步骤如下：

1. 回表查询：引擎并不直接从聚集索引返回最终结果，而是先通过覆盖索引（如果有主键索引）获取符合条件的行主键值。
2. 丢弃前 N 行：根据排序要求扫描并定位到第 offset + size 行的位置，然后将前面的 offset 行全部废弃（即扔掉前 10000 条）。
3. 取出后 M 行：取出剩余的 size 条记录，并通过主键 ID 去聚簇索引中查询完整的数据行（Full Row Lookup）。

核心问题在于： 无论是否需要显示前 10000 条数据，数据库都必须逐一处理并扫描这 10000 条记录。如果业务需要翻到第 10 万页（LIMIT 100000, 20），数据库就要扫描 100,020 行，这种“全扫描前缀”的操作在高并发场景下会导致严重的性能损耗。

优化方案一：基于当前页记录的“游标法” (Seek Method)

如果产品需求允许只展示“上一页”和“下一页”的功能，而不需要展示具体的“第 5 页、第 6 页”等页码跳转，那么可以采用最高效的Seek 方法。

实现逻辑

假设每页 20 条，当前页（第 10 页）数据的最大 ID 为 9527，最小 ID 为 9500。

• 上一页逻辑：以当前页第一条记录的 ID 为边界向上查。
• 下一页逻辑：以当前页最后一条记录的 ID 为边界向下查。

操作步骤

第一：处理“上一页”查询

由于是按 ID 降序排列，上一页的数据 ID 应大于当前页的最大 ID。SQL 如下：

SELECT * FROM message WHERE id > 9527 ORDER BY id ASC LIMIT 20;

注意：这里使用 ORDER BY id ASC 是为了配合 id > 9527 获取紧邻的大于 ID 的记录，且按升序排列更符合用户直觉。

第二：处理“下一页”查询

下一页的数据 ID 应小于当前页的最小 ID。SQL 如下：

SELECT * FROM message WHERE id < 9500 ORDER BY id DESC LIMIT 20;

优缺点分析

• 优点：无论翻多少页，每次查询永远只扫描 limit 指定的行数（如 20 行），性能恒定且极佳。
• 缺点：无法直接跳转到“第 X 页”。虽然可以通过计算累积偏移量来实现跳转，但会失去常数时间复杂度的优势。

优化方案二：延迟关联 (Deferred Join) 优化深度分页

在实际业务中，产品经理通常希望支持 `<上一页 1 2 3... 下一页>` 的分页体验。要实现任意页码的快速跳转且避免全扫描前缀，我们可以利用覆盖索引的优势。

核心思路

既然“回表”查询完整数据很慢，那我们能不能先在覆盖索引（通常是主键索引）里完成“跳过 N 行”的动作，拿到需要的 ID 列表后，再拿着 ID 列表去回表查询完整信息？

操作演示

我们要跳至第 8 页（共 20 条/页），起始偏移量为 (8-1)*20 = 140。传统的写法是 LIMIT 140, 20，这会扫描 160 行。

1. 第一步：仅获取该页的主键 ID

利用 WHERE 限制查找范围，进一步减少需扫描的主键行数（这是上文“clue”法的延伸）：

-- 假设我们知道大概的 ID 范围，例如要在 id > 9000 的范围内找第 8 页的前 20 条
SELECT id FROM message WHERE id > 9000 ORDER BY id DESC LIMIT 140, 20;

说明：即使使用了 offset，这里的消耗也仅限于内存中的主键比较和少量磁盘读取，远低于回表的 IO 开销。若追求极致，可直接算出目标 ID 范围再进行精确筛选。

2. 第二步：根据 ID 列表回表查询完整字段

将上一步查出的 20 个 ID 拼接到子查询中：

SELECT * FROM message m
INNER JOIN (
    SELECT id FROM message
    ORDER BY id DESC
    LIMIT 140, 20
) t ON m.id = t.id;

或者更常见的写法是利用覆盖索引：

SELECT * FROM table_name
WHERE id IN (
    SELECT id FROM table_name ORDER BY id DESC LIMIT 140, 20
);

原理拆解

在这个执行计划中，子查询部分虽然也需要做 OFFSET 跳跃，但它是在主键索引树上进行的快速导航。当子查询确定了 20 个 ID 后，外层查询通过 JOIN 进行回表，这 20 次回表是非常快的随机 IO。这就避免了因为 OFFSET=140 而导致的逐行过滤浪费。

进阶技巧：基于相对位移的动态 Offset

如果依然觉得固定 LIMIT m, n 的 m 太大，可以结合第一种方法的思路：

假设当前在第 10 页（ID 范围 9527 ~ 9500），如果要跳到第 8 页：

-- 计算两页之间的间隔（例如隔了 2 页）
SELECT * FROM message WHERE id > 9527 ORDER BY id ASC LIMIT (2*20), 20;

这种方法的本质是缩小了 OFFSET 的值。通过记录锚点 ID 来计算相对位移，使得 OFFSET 不再是从第一页开始累加，从而保证扫描行数始终在一个较小的可控范围内。

总结

MySQL 的性能优化是一个不断权衡的过程。对于无限长列表流式加载（如朋友圈、新闻列表），强烈建议采用基于 ID 的 Seek 方法；对于传统后台管理或搜索结果页，推荐使用延迟关联（子查询）方式优化 LIMIT OFFSET 带来的性能衰减。

注意事项：

• 在使用 ASC/DESC 混合查询时，务必注意排序方向，否则可能导致数据显示顺序错乱。
• 上述优化建立在有合适索引（如主键索引）的前提下。如果没有索引，任何分页优化都收效甚微。

以上就是 MySQL 深度分页性能优化的详细内容，更多关于数据库性能调优的资料请关注本站其它相关文章！