如何优雅实现高性能分页

2025-04-16 本文已影响0人 code_搬运工

一、延迟关联分页
延迟关联分页是一种优化数据库查询性能的技术，特别适用于大数据量下的分页查询。它的核心思想是先通过子查询获取分页所需的主键，再用这些主键关联获取完整数据，避免了大偏移量时的性能问题。
实现方式

原生SQL实现

@Repository
public class UserRepository {
    
    @PersistenceContext
    private EntityManager entityManager;
    
    public Page<User> findUsersWithDelayJoin(int page, int size) {
        // 1. 先查询分页的ID
        String idQuerySql = "SELECT u.id FROM User u ORDER BY u.createTime DESC";
        Query idQuery = entityManager.createNativeQuery(idQuerySql);
        idQuery.setFirstResult((page - 1) * size);
        idQuery.setMaxResults(size);
        List<Long> ids = idQuery.getResultList();
        
        if (ids.isEmpty()) {
            return new PageImpl<>(Collections.emptyList(), PageRequest.of(page - 1, size), 0);
        }
        
        // 2. 根据ID查询完整数据
        String dataQuerySql = "SELECT u FROM User u WHERE u.id IN :ids ORDER BY u.createTime DESC";
        List<User> users = entityManager.createQuery(dataQuerySql, User.class)
                                      .setParameter("ids", ids)
                                      .getResultList();
        
        // 3. 查询总数
        String countSql = "SELECT COUNT(u) FROM User u";
        long total = entityManager.createQuery(countSql, Long.class)
                                .getSingleResult();
        
        return new PageImpl<>(users, PageRequest.of(page - 1, size), total);
    }
}

MyBatis实现

@Mapper
public interface UserMapper {
    
    @Select("SELECT id FROM user ORDER BY create_time DESC LIMIT #{offset}, #{size}")
    List<Long> selectUserIds(@Param("offset") int offset, @Param("size") int size);
    
    @Select("SELECT * FROM user WHERE id IN " +
            "<foreach item='id' collection='ids' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach> " +
            "ORDER BY create_time DESC")
    List<User> selectUsersByIds(@Param("ids") List<Long> ids);
    
    @Select("SELECT COUNT(*) FROM user")
    long countUsers();
}

@Service
public class UserService {
    
    @Autowired
    private UserMapper userMapper;
    
    public Page<User> getUsersWithDelayJoin(int page, int size) {
        int offset = (page - 1) * size;
        
        // 1. 先查询分页的ID
        List<Long> ids = userMapper.selectUserIds(offset, size);
        
        if (ids.isEmpty()) {
            return new PageImpl<>(Collections.emptyList(), PageRequest.of(page, size), 0);
        }
        
        // 2. 根据ID查询完整数据
        List<User> users = userMapper.selectUsersByIds(ids);
        
        // 3. 查询总数
        long total = userMapper.countUsers();
        
        return new PageImpl<>(users, PageRequest.of(page, size), total);
    }
}

性能对比
延迟关联分页相比传统分页的优势：

大偏移量时性能更好：传统分页 LIMIT 100000, 20 需要扫描100020行；延迟关联先获取20个ID再关联，只扫描20行完整数据。
减少数据传输量：第一次查询只返回ID，不返回所有字段。
缓存友好：ID查询结果更容易被缓存。

注意事项

排序一致性：确保两次查询使用相同的排序条件，否则可能出现数据不一致。
ID查询优化：确保ID查询使用了适当的索引。
事务一致性：如果数据在两次查询间发生变化，可能导致结果不一致，必要时加锁或使用事务。
小数据量：对于小数据量，传统分页可能更简单高效。
这种实现方式特别适合在数据量大、翻页深的场景下使用，可以显著提高分页查询性能。

二、游标分页实现
游标分页是一种更高效的分页方式，特别适合无限滚动或社交媒体动态流等场景。相比传统的偏移量分页，它避免了偏移量大时的性能问题

游标分页原理

使用一个唯一且有序的字段（通常是ID或创建时间）作为游标
客户端传递"上一页最后一条记录的游标值"来获取下一页
不需要知道总页数或总记录数

基于MyBatis的实现

@Mapper
public interface PostMapper {
    
    @Select("SELECT * FROM post ORDER BY created_at DESC LIMIT #{size}")
    List<Post> findFirstPage(@Param("size") int size);
    
    @Select("SELECT * FROM post WHERE created_at < #{cursor} ORDER BY created_at DESC LIMIT #{size}")
    List<Post> findByCursor(@Param("cursor") LocalDateTime cursor, @Param("size") int size);
}

@Service
public class PostService {
    
    @Autowired
    private PostMapper postMapper;
    
    public CursorPage<Post> getPosts(LocalDateTime cursor, int size) {
        List<Post> posts = cursor == null 
            ? postMapper.findFirstPage(size)
            : postMapper.findByCursor(cursor, size);
        
        LocalDateTime nextCursor = posts.isEmpty() ? null : posts.get(posts.size() - 1).getCreatedAt();
        boolean hasNext = posts.size() == size;
        
        return new CursorPage<>(posts, size, nextCursor, hasNext);
    }
}

游标分页的优势

性能更好：避免了OFFSET带来的性能问题，特别是大数据量时
实时性更好：新增或删除记录不会影响分页结果的一致性
无限滚动友好：非常适合无限滚动的UI设计
无跳跃问题：不会出现传统分页跳转到某页时数据变化的问题

注意事项

游标字段选择：应选择唯一且有序的字段（如自增ID、创建时间戳）
排序方向：确保查询排序与游标方向一致
多字段排序：如需多字段排序，游标应包含所有这些字段
安全性：游标值不应暴露敏感信息，必要时可加密
游标分页是实现高效、稳定分页的优选方案，特别适合现代Web和移动应用中的动态内容加载场景。

三、覆盖索引分页
覆盖索引分页是一种高效的分页优化技术，它通过创建包含查询所需全部字段的索引，使查询可以完全通过索引完成，避免回表操作，从而大幅提升分页性能。

覆盖索引分页原理

覆盖索引：索引包含查询需要的所有字段，查询只需扫描索引而无需访问数据表
分页优化：结合覆盖索引和延迟关联技术，先通过索引获取分页主键，再关联获取完整数据

实现步骤

数据库准备
首先确保有合适的覆盖索引，例如对于用户表：

-- 创建包含常用查询字段的覆盖索引
CREATE INDEX idx_user_covering ON users(id, username, email, create_time);

Spring Boot + MyBatis 实现

@Mapper
public interface UserMapper {
    
    // 覆盖索引查询
    @Select({
        "SELECT id, username, email FROM users",
        "ORDER BY create_time DESC",
        "LIMIT #{offset}, #{size}"
    })
    List<Map<String, Object>> selectCoveringIndex(@Param("offset") int offset, 
                                                @Param("size") int size);
    
    // 总数查询
    @Select("SELECT COUNT(*) FROM users")
    long countUsers();
    
    // 完整实体查询
    @Select({
        "<script>",
        "SELECT * FROM users WHERE id IN",
        "<foreach item='id' collection='ids' open='(' separator=',' close=')'>",
        "#{id}",
        "</foreach>",
        "ORDER BY create_time DESC",
        "</script>"
    })
    List<User> selectByIds(@Param("ids") List<Long> ids);
}

@Service
public class UserService {
    
    @Autowired
    private UserMapper userMapper;
    
    public Page<User> getUsersWithCoveringIndex(int page, int size) {
        int offset = page * size;
        
        // 1. 使用覆盖索引获取分页数据
        List<Map<String, Object>> coveringData = userMapper.selectCoveringIndex(offset, size);
        
        if (coveringData.isEmpty()) {
            long total = userMapper.countUsers();
            return new PageImpl<>(Collections.emptyList(), 
                                PageRequest.of(page, size), 
                                total);
        }
        
        // 2. 提取ID列表
        List<Long> ids = coveringData.stream()
                                   .map(map -> (Long) map.get("id"))
                                   .collect(Collectors.toList());
        
        // 3. 根据ID获取完整实体
        List<User> users = userMapper.selectByIds(ids);
        
        // 4. 获取总数
        long total = userMapper.countUsers();
        
        return new PageImpl<>(users, 
                            PageRequest.of(page, size), 
                            total);
    }
}

优化说明
索引设计：

确保索引包含查询所需的所有字段
排序字段必须包含在索引中
示例：CREATE INDEX idx_covering ON table(col1, col2, col3)

查询优化：
第一阶段查询只从索引获取数据（Using index）
第二阶段通过主键批量获取完整数据

性能对比：

传统分页：需要访问表数据+排序+跳过OFFSET
覆盖索引分页：仅访问索引+按主键精确查找

适用场景

需要显示部分字段的列表页
大数据量下的分页查询
排序字段固定的分页需求
读多写少的业务场景

注意事项

索引维护成本：覆盖索引会占用更多存储空间，并影响写入性能
字段变更：查询字段变化时需要调整索引
索引选择：不是所有字段都适合包含在覆盖索引中
查询计划：定期检查EXPLAIN结果，确保查询使用了正确的索引

高级优化（组合策略）

public Page<User> getOptimizedUsers(int page, int size, String sortField) {
    // 1. 使用覆盖索引获取ID和排序字段
    Page<Object[]> idPage = userRepository.findIdsAndSortField(
        PageRequest.of(page, size, Sort.by(sortField).descending())
    );
    
    // 2. 延迟关联获取完整数据
    List<User> users = userRepository.findByIdIn(
        extractIds(idPage.getContent()),
        Sort.by(sortField).descending()
    );
    
    return new PageImpl<>(users, idPage.getPageable(), idPage.getTotalElements());
}

这种组合策略可以应对更复杂的分页需求，同时保持高性能。

如何优雅实现高性能分页

游标分页原理

游标分页的优势

高级优化（组合策略）

猜你喜欢

热点阅读