在处理海量数据时，分页查询是一个非常常见的需求。然而，当数据量达到百万级甚至千万级时，传统的 LIMIT OFFSET 分页方式会变得越来越慢，尤其是在访问后面的页码时。

• 第 1 页：很快
• 第 100 页：有点慢
• 第 10000 页：非常慢
• 第 100 万页：几乎无法响应

今天，我们来探讨如何通过**游标分页（Keyset Pagination）**技术，让深分页查询变得飞快。

问题背景

LIMIT OFFSET 的性能问题

-- 查询第 1000000 页，每页 10 条
SELECT id, name, amount, create_time
FROM orders
ORDER BY create_time DESC, id DESC
LIMIT 10 OFFSET 9999990;

执行过程分析：

┌─────────────────────────────────────────────────────────────┐
│  LIMIT OFFSET 执行过程：                                     │
│                                                             │
│  1. MySQL 需要扫描前 10000000 条记录                        │
│  2. 然后跳过前 9999990 条记录                               │
│  3. 只返回最后 10 条记录                                    │
│                                                             │
│  问题：                                                      │
│  - OFFSET 越大，需要扫描的数据越多                            │
│  - 即使只需要 10 条数据，也要扫描百万条                      │
│  - 大量数据被读取后丢弃，浪费资源                            │
│  - 无法使用覆盖索引优化                                      │
└─────────────────────────────────────────────────────────────┘

性能对比

测试场景：orders 表 1000 万条数据

第 1 页 (OFFSET 0)：    0.01s
第 100 页 (OFFSET 990)： 0.05s
第 1000 页 (OFFSET 9990)：0.5s
第 10000 页 (OFFSET 99990)：5s
第 100000 页 (OFFSET 999990)：50s+

执行计划分析

EXPLAIN
SELECT id, name, amount, create_time
FROM orders
ORDER BY create_time DESC, id DESC
LIMIT 10 OFFSET 9999990;

结果：

+----+-------------+--------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra          |
+----+-------------+--------+------+---------------+------+---------+------+---------+----------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 9999990 | Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+---------+----------------+

问题：

• type = ALL：全表扫描
• Extra = Using filesort：需要额外排序
• rows = 9999990：扫描近 1000 万行

游标分页原理

什么是游标分页？

┌─────────────────────────────────────────────────────────────┐
│  游标分页原理：                                               │
│                                                             │
│  1. 每次查询都记录当前页最后一条记录的"标记"                   │
│  2. 下一页查询时，根据这个标记来定位起始位置                    │
│  3. 不需要跳过大量数据，直接定位到目标位置                     │
│                                                             │
│  标记选择：                                                   │
│  - 自增主键 ID                                               │
│  - 时间戳 + ID（处理时间相同的情况）                           │
│  - 其他有序且唯一的列组合                                      │
│                                                             │
│  特点：                                                     │
│  ✓ 每次查询都是 O(1)，与页码无关                              │
│  ✓ 可以使用索引快速定位                                       │
│  ✓ 内存友好，不需要加载大量数据                                │
│  ✗ 不支持跳页（只能顺序翻页）                                  │
│  ✗ 需要维护游标状态                                           │
└─────────────────────────────────────────────────────────────┘

游标分页 SQL

-- 第一页：获取最新的 10 条记录
SELECT id, name, amount, create_time
FROM orders
ORDER BY create_time DESC, id DESC
LIMIT 10;

-- 第二页：以上一页最后一条记录为标记
-- 假设上一页最后一条记录：create_time='2024-01-15 10:30:00', id=10000
SELECT id, name, amount, create_time
FROM orders
WHERE create_time < '2024-01-15 10:30:00'
   OR (create_time = '2024-01-15 10:30:00' AND id < 10000)
ORDER BY create_time DESC, id DESC
LIMIT 10;

执行计划对比

EXPLAIN
SELECT id, name, amount, create_time
FROM orders
WHERE create_time < '2024-01-15 10:30:00'
   OR (create_time = '2024-01-15 10:30:00' AND id < 10000)
ORDER BY create_time DESC, id DESC
LIMIT 10;

结果：

+----+-------------+--------+-------+-----------------------+------------------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys         | key              | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+-----------------------+------------------+---------+------+------+-------------+
|  1 | SIMPLE      | orders | range | idx_create_time_id    | idx_create_time_id| 13      | NULL |   10 | Using where |
+----+-------------+--------+-------+-----------------------+------------------+---------+------+------+-------------+

优化效果：

• type = range：范围扫描
• key = idx_create_time_id：使用了复合索引
• rows = 10：只扫描 10 行

核心代码实现

1. 游标分页查询服务

@Service
@Slf4j
public class OrderCursorService {

    @Autowired
    private OrderRepository orderRepository;

    public PageResult<Order> queryByCursor(CursorRequest request) {
        long start = System.currentTimeMillis();
        
        List<Order> orders;
        
        if (request.isFirstPage()) {
            orders = orderRepository.findTopOrders(request.getPageSize());
        } else {
            orders = orderRepository.findOrdersByCursor(
                    request.getCursorCreateTime(),
                    request.getCursorId(),
                    request.getPageSize()
            );
        }
        
        long duration = System.currentTimeMillis() - start;
        
        // 构建下一页游标
        Cursor nextCursor = buildNextCursor(orders, request.getPageSize());
        
        return PageResult.<Order>builder()
                .data(orders)
                .hasNext(nextCursor != null)
                .nextCursor(nextCursor)
                .durationMs(duration)
                .build();
    }

    private Cursor buildNextCursor(List<Order> orders, int pageSize) {
        if (orders == null || orders.size() < pageSize) {
            return null;
        }
        
        Order lastOrder = orders.get(orders.size() - 1);
        return Cursor.builder()
                .createTime(lastOrder.getCreateTime())
                .id(lastOrder.getId())
                .build();
    }
}

2. 游标请求与响应对象

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class CursorRequest {

    private LocalDateTime cursorCreateTime;
    private Long cursorId;
    private int pageSize;

    public boolean isFirstPage() {
        return cursorCreateTime == null && cursorId == null;
    }
}

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Cursor {
    private LocalDateTime createTime;
    private Long id;

    public String encode() {
        String json = String.format("{\"t\":\"%s\",\"i\":%d}", 
                createTime.toString(), id);
        return Base64.getUrlEncoder().encodeToString(json.getBytes(StandardCharsets.UTF_8));
    }

    public static Cursor decode(String encoded) {
        byte[] bytes = Base64.getUrlDecoder().decode(encoded);
        String json = new String(bytes, StandardCharsets.UTF_8);
        // 使用 Jackson 或 Gson 解析
        // 这里简化处理
        try {
            JsonNode node = new ObjectMapper().readTree(json);
            return Cursor.builder()
                    .createTime(LocalDateTime.parse(node.get("t").asText()))
                    .id(node.get("i").asLong())
                    .build();
        } catch (Exception e) {
            return null;
        }
    }
}

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class PageResult<T> {
    private List<T> data;
    private boolean hasNext;
    private Cursor nextCursor;
    private long durationMs;
}

3. Repository 层

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query(value = """
        SELECT id, order_id, amount, create_time, status
        FROM orders
        ORDER BY create_time DESC, id DESC
        LIMIT :pageSize
        """, nativeQuery = true)
    List<Order> findTopOrders(@Param("pageSize") int pageSize);

    @Query(value = """
        SELECT id, order_id, amount, create_time, status
        FROM orders
        WHERE (create_time < :cursorTime) 
           OR (create_time = :cursorTime AND id < :cursorId)
        ORDER BY create_time DESC, id DESC
        LIMIT :pageSize
        """, nativeQuery = true)
    List<Order> findOrdersByCursor(
            @Param("cursorTime") LocalDateTime cursorTime,
            @Param("cursorId") Long cursorId,
            @Param("pageSize") int pageSize);
}

4. 索引配置

@Component
@Slf4j
public class IndexConfig {

    @Autowired
    private DataSource dataSource;

    @PostConstruct
    public void initIndexes() {
        try (Connection conn = dataSource.getConnection()) {
            createIndexIfNotExists(conn,
                "CREATE INDEX idx_orders_create_time_id " +
                "ON orders (create_time DESC, id DESC)");
        } catch (SQLException e) {
            log.error("Failed to create indexes", e);
        }
    }

    private void createIndexIfNotExists(Connection conn, String sql) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            stmt.execute(sql);
        } catch (SQLException e) {
            if (!e.getMessage().contains("Duplicate key")) {
                throw e;
            }
        }
    }
}

5. Controller 层

@RestController
@RequestMapping("/api/orders")
@Slf4j
public class OrderController {

    @Autowired
    private OrderCursorService orderCursorService;

    @GetMapping("/cursor")
    public ResponseEntity<PageResult<Order>> getOrdersByCursor(
            @RequestParam(required = false) String cursor,
            @RequestParam(defaultValue = "10") int pageSize) {
        
        CursorRequest request;
        
        if (cursor == null || cursor.isEmpty()) {
            request = CursorRequest.builder()
                    .pageSize(pageSize)
                    .build();
        } else {
            Cursor decodedCursor = Cursor.decode(cursor);
            request = CursorRequest.builder()
                    .cursorCreateTime(decodedCursor.getCreateTime())
                    .cursorId(decodedCursor.getId())
                    .pageSize(pageSize)
                    .build();
        }
        
        PageResult<Order> result = orderCursorService.queryByCursor(request);
        return ResponseEntity.ok(result);
    }
}

性能对比测试

测试环境

数据库：MySQL 8.0
数据量：1000 万条 orders 记录
索引：idx_orders_create_time_id (create_time DESC, id DESC)

测试结果

内存使用对比

场景：查询第 100000 页

LIMIT OFFSET：
- 需要扫描：1000000 行
- 需要排序：1000000 行
- 内存占用：高

游标分页：
- 需要扫描：10 行
- 需要排序：0 行（索引有序）
- 内存占用：极低

进阶优化策略

1. 时间范围限定

对于非常大的表，即使使用游标分页，也应该限定时间范围：

@Query(value = """
    SELECT id, order_id, amount, create_time, status
    FROM orders
    WHERE create_time >= :startTime
      AND (create_time < :cursorTime 
         OR (create_time = :cursorTime AND id < :cursorId))
    ORDER BY create_time DESC, id DESC
    LIMIT :pageSize
    """, nativeQuery = true)
List<Order> findOrdersByCursorWithTimeRange(
        @Param("startTime") LocalDateTime startTime,
        @Param("cursorTime") LocalDateTime cursorTime,
        @Param("cursorId") Long cursorId,
        @Param("pageSize") int pageSize);

2. 并行查询优化

对于需要导出大量数据的场景，可以使用并行游标：

public List<Order> exportAllOrders(LocalDateTime startTime, LocalDateTime endTime) {
    List<CompletableFuture<List<Order>>> futures = new ArrayList<>();
    
    // 根据时间分片，并行查询
    LocalDateTime currentTime = startTime;
    while (currentTime.isBefore(endTime)) {
        LocalDateTime sliceEnd = currentTime.plusDays(1);
        futures.add(CompletableFuture.supplyAsync(() -> 
            orderRepository.findOrdersByTimeRange(currentTime, sliceEnd)));
        currentTime = sliceEnd;
    }
    
    return futures.stream()
            .map(CompletableFuture::join)
            .flatMap(List::stream)
            .sorted(Comparator.comparing(Order::getCreateTime).reversed())
            .collect(Collectors.toList());
}

3. 分布式游标分页

在分库分表场景下，可以使用全局唯一 ID 作为游标：

public PageResult<Order> queryShardedOrders(CursorRequest request) {
    List<Order> orders = new ArrayList<>();
    
    // 查询所有分片
    for (String shard : shardList) {
        List<Order> shardOrders = shardRepository.queryByCursor(request, shard);
        orders.addAll(shardOrders);
    }
    
    // 全局排序
    orders.sort(Comparator.comparing(Order::getCreateTime).reversed());
    
    // 分页
    List<Order> page = orders.stream()
            .limit(request.getPageSize())
            .collect(Collectors.toList());
    
    return PageResult.<Order>builder()
            .data(page)
            .hasNext(orders.size() > request.getPageSize())
            .build();
}

生产环境配置

application.yml

server:
  port: 8080

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/example_db?useSSL=false&serverTimezone=Asia/Shanghai
    username: root
    password: root

cursor:
  default-page-size: 10
  max-page-size: 100
  time-range-required: true

logging:
  level:
    com.example.cursor: DEBUG

配置说明

常见问题

Q: 游标分页支持跳页吗？

A: 不支持。游标分页是顺序分页，只能一页一页向后翻。如果需要跳页功能，可以考虑：

• 使用 LIMIT OFFSET（适合数据量不大的场景）
• 提供搜索功能，让用户通过条件定位到特定位置

Q: 如果数据被删除或更新怎么办？

A: 由于游标分页依赖于记录的标记值，如果记录被删除：

• 如果删除的是当前页的记录，可能会导致数据跳跃
• 如果删除的是之前页的记录，不影响当前查询

解决方案：

• 使用软删除
• 在标记中加入版本号
• 使用时间戳范围来减少影响

Q: 如何处理并发写入？

A: 游标分页天然支持并发写入，新插入的数据会出现在第一页，不会影响已有的分页结果。

Q: 游标如何序列化传递给前端？

A: 推荐使用 Base64 编码：

// 编码
String encodedCursor = cursor.encode();

// 解码
Cursor cursor = Cursor.decode(encodedCursor);

总结

通过本文的优化方案，我们可以实现：

1. 深分页性能提升 5000x+：从 50s 降至 0.01s
2. 内存友好：只加载需要的数据，不浪费资源
3. 索引友好：可以使用覆盖索引优化
4. 扩展性强：支持分库分表场景

关键设计：

• 游标标记：使用 create_time + id 作为唯一标记
• 范围查询：使用 < 和 = 操作替代 OFFSET
• 复合索引：(create_time DESC, id DESC)

在实际项目中，建议根据业务需求选择合适的分页方式：

• 数据量小（< 100 万）：可以使用 LIMIT OFFSET
• 数据量大（> 100 万）：强烈推荐游标分页
• 需要跳页功能：考虑其他方案（搜索、时间范围筛选等）

源码获取

本公众号文章已同步发布至小程序博客板块，需要源码请关注小程序博客。
公众号：服务端技术精选
小程序码：