标签归档：性能

分页的秘密：OFFSET 性能问题与游标分页

发表回复

在我们日常使用的网站或应用中，无论是浏览电商商品列表、滚动社交媒体动态，还是搜索引擎上一页一页查找结果，分页无处不在。它看似简单，一页接着一页展示数据，但在背后，却隐藏着不少技术的「秘密」。

分页处理得好，用户只会觉得流畅自然；但如果处理不好，页面加载迟缓、数据重复、甚至直接超时，崩溃，都会让用户体验大打折扣。而在应用架构过程中，分页更是一个绕不开的话题，尤其当涉及到海量数据 时，分页的实现方式会直接影响到系统的性能和效率。

OFFSET 性能问题 就是分页中最常见的「瓶颈」。它的核心问题在于，当数据规模变大时，传统分页方式的查询速度会急剧下降，甚至拖垮整个数据库。幸运的是，我们有解决方案：游标分页。

那么，为什么 OFFSET 性能会变差？游标分页又是如何解决这些问题的？今天，我们从分页开始，聊一下分页逻辑。

1. 分页是什么

分页是一个很常见的逻辑，也是大部分程序员入门的时候首先会掌握的一个通用的实现逻辑。

分页是一种将大量数据分成多个小部分（页面）进行逐步加载和显示的技术方法。它是一种数据分割和展示的策略，常用于需要显示大量数据的场景，既能提升用户体验，又能改善系统性能。

分页通常通过将数据按照固定的条目数分隔成多个页面，用户可以通过分页导航（如“上一页”、“下一页”、“跳转到第 N 页”等）浏览数据的不同部分。

2. 分页的作用

分页的主要作用包括以下几点：

  1. 提升用户体验：

    • 避免让用户一次性加载和浏览大量数据，从而减少信息过载。
    • 通过分页导航（如页码按钮、上一页/下一页），让用户能够快速定位到感兴趣的数据。

  2. 优化页面性能：

    • 限制页面加载的数据量，减少服务器和浏览器的资源消耗。
    • 减少前端页面渲染的压力，提高页面加载速度和响应速度。

  3. 降低后端和数据库压力：

    • 分页可以限制一次性查询的数据量，避免对数据库产生过高的查询负载。
    • 避免将所有数据发送到前端，减少网络的传输压力。

  4. 便于数据管理：

    • 在管理系统中，分页能够让管理员方便地查看、筛选和操作特定范围内的数据。

3. 分页的实现方式

分页的实现方式常见的是两种，传统分页和游标分页，根据应用场景和需求，选择合适的方案可以有效提升系统性能和用户体验。

3.1 OFFSET 分页（传统分页）

传统分页，也称为基于 OFFSET 的分页，是最常见的一种分页方式。其核心思想是通过页码和偏移量（OFFSET）来定位查询结果的起始记录，并限定每次查询的记录数量（LIMIT）。这种方式通常与 SQL 的 LIMIT 和 OFFSET 关键字结合使用。

传统分页的主要逻辑是根据用户请求的页码计算出需要跳过的记录数（OFFSET = (page – 1) * pageSize），然后查询从偏移量开始的指定数量的记录。

原理

OFFSET 分页是最常见也是最简单的分页方式。它通过指定查询的起始位置和每页记录数，从数据库中获取相应的数据。例如，在 SQL 中可以通过LIMIT 和OFFSET 实现：

SELECT * 
FROM table_name
ORDER BY id
LIMIT 10 OFFSET 20;
  • LIMIT 10：表示每页显示 10 条记录。
  • OFFSET 20：表示跳过前 20 条记录（即从第 21 条开始）。

优点

  1. 实现简单

    • 逻辑清晰直观，基于LIMIT 和OFFSET 的 SQL 查询几乎所有数据库都支持。
    • 开发和维护成本低，适合快速实现分页功能。

  2. 支持随机跳页

     SELECT * 
FROMusers
ORDERBYidASC
LIMIT10OFFSET990;
    • 用户可以通过指定页码直接跳转到任意页，而无需逐页加载。例如，直接查询第 100 页的数据：

  3. 适用范围广

    • 适合小规模或中等规模的数据分页场景，尤其是在数据集较小且性能要求不高时。

缺点

  1. 性能问题

     SELECT * 
FROMusers
ORDERBYidASC
LIMIT10OFFSET100000;

    在这种情况下，数据库需要先扫描 100,000 条记录后，才能返回第 100,001 条到第 100,010 条记录。扫描的记录越多，查询耗时越长。

    • 当数据量很大时，OFFSET 会导致查询性能下降，因为数据库需要扫描并跳过OFFSET 指定的记录，即使这些记录不会返回。
      例如：

  2. 数据一致性问题

    • 重复记录：如果在第一页和第二页之间插入了一条新记录，第二页可能会重复显示第一页的最后一条记录。
    • 记录丢失：如果在分页过程中删除了某些记录，可能会导致某些记录被跳过。
    • 如果在分页过程中数据发生变化（如插入或删除记录），可能会导致分页结果出现重复记录或跳过记录的情况。例如：

  3. 不适合实时更新的场景

    • 当数据集频繁增删时，传统分页难以保证结果的准确性。

  4. 消耗资源

    • 每次分页查询都需要数据库执行完整的排序和偏移操作，对资源消耗较大，尤其在大数据集或深分页（偏移量很大）时问题更加明显。这种我们一般称之为深分页

适用场景

适合小规模数据分页，或者数据更新不频繁的场景，如展示固定的商品列表或博客文章。

3.2 Keyset 分页（游标分页）

Keyset Pagination，也称为基于键的分页或游标分页，是一种高效的分页技术，用于解决传统分页方法（基于 OFFSET 和 LIMIT）在处理大数据集时的性能瓶颈问题。相较于传统分页，Keyset Pagination 不依赖页码或偏移量，而是通过上一页的最后一条记录的标识符（通常是主键或唯一索引）来标记分页的起始点，从而实现更高效、更稳定的分页。

原理

游标分页是一种基于游标的分页方式，通过使用上一页的最后一条记录的标识（如主键或时间戳）来确定下一页的数据，而不是依赖 OFFSET。

示例查询：

SELECT * 
FROM table_name
WHERE id > 100
ORDER BY id
LIMIT 10;
  • id > 100：表示从上一页最后一条记录的主键（id=100）之后开始查询。
  • LIMIT 10：每次获取 10 条记录。

优点

  • 性能优越：避免了 OFFSET 扫描的性能问题，查询直接从指定游标位置开始。
  • 数据一致性：即使数据在分页过程中发生变化，也能保证数据不会重复或丢失。

缺点

  • 跳页困难：无法直接跳转到第 N 页，需要依赖前置页的上下文。
  • 依赖排序字段：通常需要全局唯一且连续的排序字段（如主键或时间戳）。

适用场景

适合处理海量数据或数据频繁更新的场景，如社交媒体动态流、消息列表、AIGC 的推荐图片流等。

聊完了常见的两种分页，再聊一下 OFFSET 为什么会慢。

4. OFFSET 为什么会慢

以 MySQL 为例。

LIMIT ... OFFSET ... 是一种常用的分页查询方式，但随着OFFSET 值的增大，这种方式会带来严重的性能问题。其核心原因在于MySQL 的查询执行机制 和数据的存储与读取方式。

在执行LIMIT ... OFFSET ... 查询时，MySQL 的行为是扫描并跳过 OFFSET 指定的记录，即使这些记录不会返回到客户端，但是数据库仍然需要从磁盘读取记录，排序……

这不是执行问题，而是 OFFSET 设计方式：

…the rows are first sorted according to the <order by clause> and then limited by dropping the number of rows specified in the <result offset clause> from the beginning…

SQL:2016, Part 2, §4.15.3 Derived tables

翻译过来：……记录会首先根据 ORDER BY 子句 进行排序，然后通过丢弃从开头开始的 OFFSET 子句指定数量的行来限制结果……

4.1 OFFSET 执行过程

比如下面的例子：

SELECT * 
FROM t1 
ORDER BY id ASC 
LIMIT 1000000, 20;

其执行过程如下：

  1. 全表扫描或索引扫描：

    • MySQL 根据ORDER BY id 对记录进行排序。即使只需要第 1000001 条到第 1000020 条记录，也必须先按查询条件读出前 100 万条记录。
    • 如果有索引（如主键索引id），MySQL 会利用索引扫描；如果没有索引，则会进行全表扫描。

  2. 跳过 OFFSET 记录：

    • MySQL 遍历查询结果集，并逐条丢弃前 100 万条记录（OFFSET 1000000）。
    • 这种「丢弃」并不是直接跳过，而是逐行读取，然后丢弃，直到到达第 1000001 条记录。

  3. 读取目标记录：

    • 到达第 1000001 条记录后，MySQL 开始读取接下来的 20 条数据（LIMIT 20），作为最终结果返回。

4.2 OFFSET 性能问题的根本原因

（1）扫描和跳过造成资源浪费

即使客户端只需要一小部分数据（例如 20 条），MySQL 在执行查询时，仍然需要扫描和处理大量的记录（前 100 万条）。这会带来以下问题：

  • 耗费磁盘 I/O：
    MySQL 需要从磁盘读取未返回的记录，即使这些记录最终会被丢弃。
  • 浪费内存和 CPU：
    MySQL 扫描的所有记录会被加载到内存中，排序和过滤操作会消耗 CPU 资源。对于深分页（OFFSET 值很大）的查询，这种浪费会随着页码的增加而成倍增长。

（2）无法直接利用索引跳过记录

即使排序字段有索引（如主键索引id），MySQL 仍然需要逐条扫描记录，跳过 OFFSET 指定的记录。原因是：

  • 索引扫描的局限性： MySQL 的索引只能用来快速定位起始记录（例如id > 1000000 的情况），但在 OFFSET 查询中，MySQL 并不知道目标记录的具体位置，只能通过逐条遍历的方式来跳过。
  • 无指针跳转机制： MySQL 的存储引擎（如 InnoDB）在处理 OFFSET 查询时，不会直接跳过指定数量的记录，而是逐行读取和计数，直到到达目标记录。

（3）排序带来的额外开销

在使用ORDER BY 的情况下，MySQL 必须先对所有数据进行排序，然后再从中挑选目标记录：

  • 如果排序字段没有索引，MySQL 会将数据加载到内存或临时表中，并在内存中完成排序（可能会涉及磁盘写入）。
  • 如果排序字段有索引，MySQL 会利用索引加速排序，但仍需遍历和丢弃 OFFSET 指定的记录，资源浪费依然存在。

（4）深分页数据量巨大

当OFFSET 值较小时，MySQL 需要跳过的记录量较少，性能影响不明显。但随着OFFSET 值的增大，MySQL 需要扫描和丢弃的记录数呈线性增长，最终导致性能急剧下降。

4.3 OFFSET 性能问题的典型场景

（1）数据量庞大时的深分页

当表中的数据量达到百万级别时，深分页（如OFFSET 1000000）会导致查询性能显著下降。原因是 MySQL 在扫描前 100 万条记录时，消耗了大量的磁盘 I/O 和 CPU 资源。

（2）查询结果动态变化

分页查询的同时，数据可能在不断更新（如新增或删除记录）。这种情况下：

  • MySQL 仍然会按照 OFFSET 值从头扫描，导致性能下降。
  • 数据的插入或删除可能导致分页结果重复或遗漏。

（3）排序字段没有索引

如果ORDER BY 的字段没有索引，MySQL 需要对全表数据进行排序，并将排序结果存储在临时表中。排序操作会进一步加剧性能问题。

4.4 如何解决 OFFSET 性能问题？

  1. 使用游标分页（Keyset Pagination）
    通过记录上一页的最后一条记录的唯一标识符（如主键id）来定位下一页的起点，避免扫描和跳过无关记录：
   SELECT * 
   FROM t1 
   WHERE id > #{last_id} 
   ORDER BY id ASC 
   LIMIT 20;
  • 优势：直接定位目标记录，性能与OFFSET 无关。
  • 适用场景：连续分页（如滑动加载）。

  1. 限制深分页范围
    限制用户只能跳转到前后一段范围内的页码，避免深分页。

  2. 子查询优化
    使用子查询提取主键范围，然后通过主键关联查询：

   SELECT * 
   FROM t1 
   JOIN (
       SELECT id 
       FROM t1 
       ORDER BY id ASC 
       LIMIT 1000000, 20
   ) x USING (id);
  • 优势：减少排序和回表操作的开销。
  1. 合理设计索引
    对常用的查询字段和排序字段添加索引，最大化利用 MySQL 的索引能力。

除以上的 4 种以外，还可以考虑倒序分页，延迟关联、分区表优化或业务逻辑分流等方案。

OFFSET 的性能问题，归根结底是因为 MySQL 的查询执行机制无法直接跳过指定数量的记录，只能通过逐条扫描和丢弃的方式实现。这种机制在深分页时会导致严重的资源浪费。通过优化查询方式（如游标分页或子查询），可以显著减少无关记录的扫描量，从而提高查询性能。

5. 小结

分页是日常开发中非常常见的功能，但在数据量上来后，分页可能成为隐藏的性能杀手。传统的 OFFSET 分页尽管实现简单，但却无法避免扫描和跳过大量无用记录的性能瓶颈，尤其在处理海量数据时。这种情况下，优化分页逻辑显得尤为重要。

通过引入游标分页、子查询优化、分区表设计等技术手段，并结合业务逻辑上的调整，几乎可以解决大部分分页场景的性能问题。在实际开发中，应根据业务特点和数据规模选择合适的优化方案，实现性能和用户体验的平衡。

分页的优化，不仅是一项技术能力，更是对业务场景理解的体现。希望通过本文的分析和总结，能帮助开发者更好地应对深分页的挑战，写出高效、稳健的分页逻辑！

以上。