一、引言

在处理大量数据时，分页（Pagination） 是常用的技术手段。然而，使用 LIMIT 和 OFFSET 进行分页在数据量很大时可能会导致性能问题。相反，使用 游标（Cursor） 或 基于游标的分页（Cursor-based Pagination） 可以提高查询效率。

二、为什么尽量使用游标而非分页？

1. 分页的性能问题

• 高偏移量性能低下：使用 LIMIT OFFSET 进行分页，当 OFFSET 值很大时，数据库需要扫描大量数据才能跳过指定的记录数，这会导致查询性能显著下降。

• 全表扫描风险：在没有合适索引的情况下，分页查询可能导致全表扫描，增加了 I/O 开销。

2. 游标（基于游标的分页）的优势

• 更高效的查询：基于游标的分页利用索引，直接定位数据的位置，不需要跳过大量记录，查询性能更高。

• 数据一致性：在数据频繁变化的情况下，游标分页能更好地保持数据的一致性，避免数据遗漏或重复。

• 避免重复和遗漏：基于唯一标识（如 id）的游标分页，能够精确地定位数据，避免分页过程中出现的数据重复或遗漏问题。

三、游标分页的原理

游标分页，也称为 基于键集的分页（Keyset Pagination），通过使用上一次查询结果的唯一标识（如自增 id、时间戳等），在下一次查询时作为游标进行定位，获取后续的数据。

• 基本思想：使用 WHERE 子句筛选出比上次最后一条记录大的数据，配合 LIMIT 获取下一页数据。

• 优点：利用索引查找，性能与数据量无关，查询速度更快。

四、优化后的示例（非存储过程）

1. 数据表结构

假设有一个 users 表：

2. 传统分页的缺点

传统分页查询（第 N 页，每页 15 条）：

SELECT id, name, email FROM users
ORDER BY id ASC
LIMIT 15 OFFSET (N - 1) * 15;

• 问题：当 N 很大时，OFFSET 也很大，数据库需要扫描和跳过大量记录，性能下降。

3. 使用游标分页的优化查询

步骤：

1. 获取初始游标值：首次查询时，无需游标，或设置为 0。

2. 查询数据：使用游标值作为条件，获取下一页的数据。

3. 更新游标值：将本次查询结果的最后一条记录的 id，作为下一次查询的游标。

示例：

• 首次查询（初始游标为 0）：

  SELECT id, name, email FROM users
  WHERE id > 0
  ORDER BY id ASC
  LIMIT 15;
  

• 后续查询（假设上次最后一条记录的 id 为 15）：

  SELECT id, name, email FROM users
  WHERE id > 15
  ORDER BY id ASC
  LIMIT 15;
  

• 更新游标值：

  • 在应用程序中，将本次查询结果的最后一个 id 保存为新的游标值，用于下次查询。

4. 示例代码（Java 实现）

假设使用 JDBC 进行数据库操作：

public List<User> getUsersAfterId(int lastId, int pageSize) throws SQLException {
    String sql = "SELECT id, name, email FROM users WHERE id > ? ORDER BY id ASC LIMIT ?";
    List<User> userList = new ArrayList<>();

    try (Connection conn = dataSource.getConnection();
         PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setInt(1, lastId);
        pstmt.setInt(2, pageSize);

        try (ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setEmail(rs.getString("email"));
                userList.add(user);
            }
        }
    }
    return userList;
}

使用方法：

• 首次查询（lastId = 0）：

  List<User> usersPage1 = getUsersAfterId(0, 15);
  

• 后续查询（使用上次的最后一个 id）：

  int lastId = usersPage1.get(usersPage1.size() - 1).getId();
  List<User> usersPage2 = getUsersAfterId(lastId, 15);
  

5. 前端参数的传递

• 首次请求：/api/users?lastId=0

• 响应数据：

  {
      "data": [/* 用户数据数组 */],
      "lastId": 15
  }
  

• 下一次请求：使用上次返回的 lastId

  /api/users?lastId=15

五、游标分页的注意事项

1. 必须有连续且有序的唯一标识符：如自增的 id、唯一的时间戳等。

2. 适用于固定排序的情况：游标分页通常基于某个字段的排序（如 id ASC），在排序方式固定的情况下效果最佳。

3. 无法直接跳转到任意页：游标分页更适合“上一页”“下一页”的翻页方式，而不适合直接跳转到指定页码。

4. 数据的一致性：在数据可能被插入或删除的情况下，游标分页能更好地保持数据的一致性，避免传统分页可能出现的记录重复或遗漏。

六、游标分页与 B+ 树索引的关系

• 利用索引：游标分页使用了索引字段（如 id）进行筛选和排序，数据库可以利用 B+ 树索引高效地定位和读取数据。

• 避免全表扫描：相比高偏移量的 LIMIT OFFSET，游标分页的查询条件可以使数据库直接跳转到指定位置，避免扫描不必要的行。

七、总结

• 性能优势：在数据量较大的情况下，游标分页比传统的 LIMIT OFFSET 分页性能更高。

• 实现简单：无需使用存储过程，可以在应用程序层面实现，代码简单易懂。

• 适用场景：适用于需要高效处理大量数据且只需“上一页”“下一页”导航的场景。

• 优先选择游标分页：在可能的情况下，应尽量使用游标分页来替代传统的分页方式，以获得更好的性能和数据一致性。

• 结合实际场景：根据具体的业务需求和数据规模，选择合适的分页策略，确保系统的性能和稳定性。

参考资料

• MySQL官方文档：优化查询
• 高性能MySQL（第3版）
• Efficient Pagination Using MySQL