一、分页查询的痛点

上周，一位做数据分析的朋友向我抱怨：他们的系统在处理大量数据时，分页查询变得越来越慢。"我们有一张包含 1000 万条记录的用户表，"他说，"当翻到第 100 页时，响应时间超过了 5 秒，用户体验极差。"

我帮他检查了一下代码，发现他们使用的是传统的 LIMIT OFFSET 分页方式：

SELECT * FROM user ORDER BY id DESC LIMIT 10 OFFSET 1000000;

这种查询方式在数据量较大时，会有严重的性能问题。因为 MySQL 需要先跳过 OFFSET 指定的行数，然后再返回 LIMIT 指定的行数。当 OFFSET 达到 100 万时，MySQL 需要扫描 1000010 行数据，然后只返回 10 行，效率非常低。

二、传统分页方案的局限性

为了实现分页查询，我们通常会使用以下方案：

1. LIMIT OFFSET 分页

SELECT * FROM table ORDER BY id DESC LIMIT 10 OFFSET 1000000;

这种方案的问题：

• 性能差：随着 OFFSET 的增大，查询时间线性增长
• 数据不一致：在分页过程中，如果有数据插入或删除，可能会导致重复或遗漏数据
• 无法跳页：用户无法直接跳转到指定页码，只能一页一页翻
• 内存消耗：当 OFFSET 较大时，MySQL 需要扫描大量数据，消耗内存

2. 基于 ID 的分页

SELECT * FROM table WHERE id > 1000000 ORDER BY id ASC LIMIT 10;

这种方案的问题：

• 只能基于自增 ID：如果表的主键不是自增的，或者排序字段不是 ID，这种方案不适用
• 无法支持复杂排序：如果需要按多个字段排序，这种方案难以实现
• 数据删除问题：如果有数据被删除，可能会导致跳号

3. 全量缓存

将所有数据加载到内存中，然后在内存中进行分页。

这种方案的问题：

• 内存消耗大：对于大量数据，内存消耗巨大
• 数据同步：需要处理数据的实时同步问题
• 初始化时间长：首次加载数据需要较长时间

三、终极方案：基于游标+时间戳的深度优化

今天，我要和大家分享一个在实战中验证过的解决方案：基于游标+时间戳的分页查询。

这套方案的核心思想是：

1. 使用游标：通过记录上一页的最后一条记录的标记（游标），来定位下一页的起始位置
2. 时间戳排序：使用时间戳作为排序字段，确保数据的一致性
3. 索引优化：为排序字段创建索引，提高查询速度
4. 防重复处理：处理数据插入和删除导致的重复或遗漏问题

四、方案详解

1. 数据库设计

（1）用户表设计

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `name` varchar(255) NOT NULL COMMENT '用户名',
  `email` varchar(255) NOT NULL COMMENT '邮箱',
  `phone` varchar(20) NOT NULL COMMENT '手机号',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_create_time` (`create_time`),
  KEY `idx_name` (`name`),
  KEY `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

关键配置：

• KEY idx_create_time (create_time)：为创建时间字段创建索引，用于排序和分页

2. SpringBoot实现

（1）分页请求参数类

@Data
public class PageRequest {
    private int pageSize = 10;
    private String cursor;
    private String sortField = "create_time";
    private String sortOrder = "desc";
}

（2）分页响应结果类

@Data
public class PageResult<T> {
    private List<T> data;
    private boolean hasMore;
    private String nextCursor;
    private long total;
}

（3）游标工具类

@Component
public class CursorUtils {
    
    /**
     * 生成游标
     */
    public String generateCursor(Object... values) {
        try {
            String data = String.join(",", Arrays.stream(values).map(String::valueOf).toArray(String[]::new));
            return Base64.getEncoder().encodeToString(data.getBytes(StandardCharsets.UTF_8));
        } catch (Exception e) {
            throw new RuntimeException("生成游标失败", e);
        }
    }
    
    /**
     * 解析游标
     */
    public List<String> parseCursor(String cursor) {
        try {
            byte[] decoded = Base64.getDecoder().decode(cursor);
            String data = new String(decoded, StandardCharsets.UTF_8);
            return Arrays.asList(data.split(","));
        } catch (Exception e) {
            throw new RuntimeException("解析游标失败", e);
        }
    }
}

（4）分页服务类

@Service
@Slf4j
public class PaginationService {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @Autowired
    private CursorUtils cursorUtils;
    
    /**
     * 基于游标+时间戳的分页查询
     */
    public PageResult<User> queryUsers(PageRequest request) {
        long startTime = System.currentTimeMillis();
        
        // 构建查询条件
        StringBuilder sql = new StringBuilder("SELECT * FROM user WHERE 1=1");
        List<Object> params = new ArrayList<>();
        
        // 解析游标
        if (request.getCursor() != null) {
            List<String> cursorValues = cursorUtils.parseCursor(request.getCursor());
            if (cursorValues.size() >= 2) {
                String lastCreateTime = cursorValues.get(0);
                long lastId = Long.parseLong(cursorValues.get(1));
                
                if ("desc".equals(request.getSortOrder())) {
                    sql.append(" AND (create_time < ? OR (create_time = ? AND id < ?))");
                    params.add(lastCreateTime);
                    params.add(lastCreateTime);
                    params.add(lastId);
                } else {
                    sql.append(" AND (create_time > ? OR (create_time = ? AND id > ?))");
                    params.add(lastCreateTime);
                    params.add(lastCreateTime);
                    params.add(lastId);
                }
            }
        }
        
        // 排序
        sql.append(" ORDER BY ").append(request.getSortField());
        if ("desc".equals(request.getSortOrder())) {
            sql.append(" DESC");
        } else {
            sql.append(" ASC");
        }
        sql.append(", id ").append("desc".equals(request.getSortOrder()) ? "DESC" : "ASC");
        
        // 限制返回数量（多查询一条，用于判断是否有下一页）
        sql.append(" LIMIT ?");
        params.add(request.getPageSize() + 1);
        
        // 执行查询
        List<User> users = jdbcTemplate.query(sql.toString(), params.toArray(), (rs, rowNum) -> {
            User user = new User();
            user.setId(rs.getLong("id"));
            user.setName(rs.getString("name"));
            user.setEmail(rs.getString("email"));
            user.setPhone(rs.getString("phone"));
            user.setCreateTime(rs.getTimestamp("create_time").toLocalDateTime());
            user.setUpdateTime(rs.getTimestamp("update_time").toLocalDateTime());
            return user;
        });
        
        // 处理结果
        boolean hasMore = false;
        String nextCursor = null;
        List<User> resultData = users;
        
        if (users.size() > request.getPageSize()) {
            hasMore = true;
            resultData = users.subList(0, request.getPageSize());
            User lastUser = resultData.get(resultData.size() - 1);
            nextCursor = cursorUtils.generateCursor(lastUser.getCreateTime().toString(), lastUser.getId());
        }
        
        // 计算总耗时
        long endTime = System.currentTimeMillis();
        log.info("分页查询耗时：{}ms", endTime - startTime);
        
        // 构建响应
        PageResult<User> result = new PageResult<>();
        result.setData(resultData);
        result.setHasMore(hasMore);
        result.setNextCursor(nextCursor);
        result.setTotal(countUsers());
        
        return result;
    }
    
    /**
     * 统计用户总数
     */
    private long countUsers() {
        String sql = "SELECT COUNT(*) FROM user";
        return jdbcTemplate.queryForObject(sql, Long.class);
    }
}

（5）控制器类

@RestController
@RequestMapping("/api/users")
@Slf4j
public class UserController {
    
    @Autowired
    private PaginationService paginationService;
    
    /**
     * 分页查询用户
     */
    @GetMapping
    public ResponseEntity<PageResult<User>> getUsers(PageRequest request) {
        try {
            PageResult<User> result = paginationService.queryUsers(request);
            return ResponseEntity.ok(result);
        } catch (Exception e) {
            log.error("查询用户失败", e);
            return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).build();
        }
    }
}

3. 性能优化

（1）索引优化

为排序字段创建索引，确保查询时能够使用索引：

CREATE INDEX idx_create_time ON user(create_time);

（2）批量处理

对于大量数据的查询，可以使用批量处理，减少数据库交互次数：

public List<User> batchQueryUsers(List<Long> ids) {
    String sql = "SELECT * FROM user WHERE id IN (" + String.join(",", Collections.nCopies(ids.size(), "?")) + ")";
    return jdbcTemplate.query(sql, ids.toArray(), (rs, rowNum) -> {
        User user = new User();
        user.setId(rs.getLong("id"));
        user.setName(rs.getString("name"));
        user.setEmail(rs.getString("email"));
        user.setPhone(rs.getString("phone"));
        user.setCreateTime(rs.getTimestamp("create_time").toLocalDateTime());
        user.setUpdateTime(rs.getTimestamp("update_time").toLocalDateTime());
        return user;
    });
}

（3）缓存优化

使用 Redis 缓存热点数据，减少数据库查询：

@Service
public class UserService {
    
    @Autowired
    private RedisTemplate<String, Object> redisTemplate;
    
    @Autowired
    private PaginationService paginationService;
    
    public PageResult<User> getUsers(PageRequest request) {
        // 生成缓存键
        String cacheKey = "users:page:" + request.getPageSize() + ":" + request.getSortField() + ":" + request.getSortOrder() + ":" + (request.getCursor() != null ? request.getCursor() : "null");
        
        // 尝试从缓存获取
        PageResult<User> cachedResult = (PageResult<User>) redisTemplate.opsForValue().get(cacheKey);
        if (cachedResult != null) {
            return cachedResult;
        }
        
        // 执行查询
        PageResult<User> result = paginationService.queryUsers(request);
        
        // 缓存结果，设置过期时间为10分钟
        redisTemplate.opsForValue().set(cacheKey, result, 10, TimeUnit.MINUTES);
        
        return result;
    }
}

五、性能对比

1. 测试环境

• CPU: Intel Core i7-10700
• 内存: 16GB
• MySQL: 8.0
• 数据量: 1000万条用户数据

2. 测试结果

从测试结果可以看出，基于游标+时间戳的分页查询在深度分页时性能稳定，响应时间始终保持在10ms左右，而传统的LIMIT OFFSET分页在深度分页时响应时间线性增长，第10000页时响应时间达到5000ms。

六、最佳实践

1. 索引设计

• 为排序字段创建索引：确保查询时能够使用索引，提高查询速度
• 复合索引：如果需要按多个字段排序，创建复合索引
• 索引顺序：索引的顺序应与查询的排序顺序一致
• 避免使用函数：在排序字段上避免使用函数，否则无法使用索引

2. 游标设计

• 唯一标识：游标应包含唯一标识，确保数据的一致性
• 排序字段：游标应包含排序字段的值，用于定位下一页的起始位置
• Base64编码：使用Base64编码游标，确保游标可以安全传输
• 错误处理：处理游标解析失败的情况，确保系统的稳定性

3. 分页参数设计

• 页面大小限制：限制页面大小，避免一次查询过多数据
• 默认值：为分页参数设置合理的默认值
• 参数验证：验证分页参数的有效性，避免无效参数
• 排序字段白名单：限制排序字段，避免使用不存在的字段排序

4. 性能优化

• 缓存：使用Redis等缓存，减少数据库查询
• 批量处理：使用批量操作，减少数据库交互次数
• 异步处理：对于复杂查询，使用异步处理，提高系统响应速度
• 监控：监控分页查询的性能，及时发现和解决性能问题

5. 数据一致性

• 防重复处理：处理数据插入和删除导致的重复或遗漏问题
• 时间戳同步：确保时间戳的准确性，避免数据不一致
• 事务处理：对于涉及数据修改的操作，使用事务处理，确保数据一致性

七、方案优势

1. 性能稳定：深度分页时性能稳定，响应时间不受页码影响
2. 数据一致性：基于时间戳和ID的排序，确保数据的一致性
3. 支持复杂排序：支持按多个字段排序，满足不同的业务需求
4. 易于实现：实现简单，代码量少，易于维护
5. 兼容性好：与现有系统兼容性好，易于集成
6. 可扩展性：易于扩展，支持更多的排序字段和查询条件

八、适用场景

1. 大数据量分页：适用于数据量较大的表，如用户表、订单表等
2. 深度分页：适用于需要深度分页的场景，如数据分析、报表等
3. 实时数据：适用于实时数据的分页查询，如日志、监控数据等
4. 移动端应用：适用于移动端应用的分页加载，如新闻、商品列表等
5. API接口：适用于API接口的分页查询，提供一致的响应格式

九、写在最后

分页查询是Web应用中常见的功能，但在处理大量数据时，传统的LIMIT OFFSET分页方式会遇到性能瓶颈。通过使用基于游标+时间戳的分页查询，我们可以在深度分页时保持稳定的性能，提供更好的用户体验。

当然，这套方案也不是银弹，它有一定的局限性：

• 不支持跳页：用户无法直接跳转到指定页码，只能一页一页翻
• 需要排序字段：需要有合适的排序字段，并且该字段需要有索引
• 游标管理：需要管理游标，增加了一定的复杂度

但对于大多数需要深度分页的场景来说，基于游标+时间戳的分页查询方案已经足够满足需求，而且实现简单、性能稳定。

希望这篇文章能给你带来一些启发，帮助你在实际项目中更好地实现分页查询功能，提供更优质的用户体验。

如果你在使用这套方案的过程中有其他经验或困惑，欢迎在评论区留言交流！

服务端技术精选，专注分享后端开发实战经验，让技术落地更简单。

如果你觉得这篇文章有用，欢迎点赞、在看、分享三连！