Java分页查询提速案例优化

Java分页查询提速案例优化

传统分页问题分析

// 传统分页 - 性能低效
@Repository
public class UserRepository {
    // 问题1: COUNT查询和主查询分开执行
    public Page<User> findByPage(int page, int size) {
        // 先count
        Long total = jdbcTemplate.queryForObject(
            "SELECT COUNT(*) FROM user", Long.class);
        // 再分页查询
        List<User> list = jdbcTemplate.query(
            "SELECT * FROM user LIMIT ? OFFSET ?",
            new Object[]{size, page * size},
            new UserRowMapper()
        );
        return new Page<>(list, total, page, size);
    }
}

优化方案一：延迟COUNT

@Service
public class UserService {
    @Autowired
    private UserRepository userRepository;
    // 优化1: 延迟计算总记录数
    public Page<User> findByPageOptimized(int page, int size) {
        // 仅第一页需要总记录数
        if (page == 0) {
            return userRepository.findByPageWithCount(page, size);
        }
        // 非第一页：先获取数据
        List<User> list = userRepository.findByPageNoCount(page, size);
        // 根据数据判断是否需要总记录数
        if (list.size() < size) {
            // 最后一页，计算总记录数
            Long total = userRepository.countAll();
            return new Page<>(list, total, page, size);
        }
        // 非最后一页，使用估算或上一次的总记录数
        return new Page<>(list, null, page, size);
    }
}

优化方案二：覆盖索引查询

@Repository
public class UserRepository {
    // 优化2: 使用覆盖索引
    public List<User> findByPageWithCoveringIndex(int lastId, int size) {
        String sql = """
            SELECT id, name, email, created_at 
            FROM user 
            WHERE id > ? 
            ORDER BY id 
            LIMIT ?
            """;
        return jdbcTemplate.query(
            sql,
            new Object[]{lastId, size},
            new UserRowMapper()
        );
    }
    // 配合使用
    public Page<User> findByPageWithCursor(int lastId, int size) {
        List<User> list = findByPageWithCoveringIndex(lastId, size);
        // 获取最后一个ID作为下一页的游标
        Integer nextCursor = list.isEmpty() ? null : list.get(list.size() - 1).getId();
        return new Page<>(list, size, nextCursor);
    }
}

优化方案三：SQL_CALC_FOUND_ROWS

@Repository
public class UserRepository {
    // 优化3: 使用SQL_CALC_FOUND_ROWS
    public Page<User> findByPageWithCalcRows(int page, int size) {
        // 一次查询获取总记录数和数据
        String sql = """
            SELECT SQL_CALC_FOUND_ROWS id, name, email, created_at 
            FROM user 
            WHERE status = 1 
            ORDER BY created_at DESC 
            LIMIT ? OFFSET ?
            """;
        List<User> list = jdbcTemplate.query(
            sql,
            new Object[]{size, page * size},
            new UserRowMapper()
        );
        // 获取总记录数
        Long total = jdbcTemplate.queryForObject(
            "SELECT FOUND_ROWS()", Long.class);
        return new Page<>(list, total, page, size);
    }
}

优化方案四：分页缓存

@Service
public class UserService {
    @Autowired
    private CacheManager cacheManager;
    // 优化4: 缓存总记录数
    @Cacheable(value = "userCount", unless = "#result == null")
    public Long getUserCount() {
        return userRepository.countAll();
    }
    public Page<User> findByPageWithCache(int page, int size) {
        // 缓存总记录数，减少COUNT查询
        Long total = getUserCount();
        List<User> list = userRepository.findByPage(page, size);
        return new Page<>(list, total, page, size);
    }
    // 定期更新缓存
    @Scheduled(fixedDelay = 60000) // 1分钟更新一次
    public void refreshUserCount() {
        cacheManager.getCache("userCount").clear();
        getUserCount();
    }
}

优化方案五：性能对比测试

@Component
public class PaginationBenchmark {
    @Autowired
    private UserService userService;
    public void benchmark() {
        int page = 0;
        int size = 20;
        int iterations = 100;
        // 测试传统分页
        long start1 = System.currentTimeMillis();
        for (int i = 0; i < iterations; i++) {
            userService.findByPageTraditional(page, size);
        }
        long end1 = System.currentTimeMillis();
        System.out.println("传统分页耗时: " + (end1 - start1) + "ms");
        // 测试优化后的分页
        long start2 = System.currentTimeMillis();
        for (int i = 0; i < iterations; i++) {
            userService.findByPageOptimized(page, size);
        }
        long end2 = System.currentTimeMillis();
        System.out.println("优化分页耗时: " + (end2 - start2) + "ms");
        // 输出性能提升比例
        double improvement = ((double)(end1 - start1) - (end2 - start2)) / (end1 - start1) * 100;
        System.out.println("性能提升: " + String.format("%.2f", improvement) + "%");
    }
}

数据库索引优化

-- 创建复合索引
CREATE INDEX idx_user_status_created ON user(status, created_at, id);
-- 覆盖索引
CREATE INDEX idx_user_cover ON user(id, name, email, created_at);
-- 分区表（大表优化）
ALTER TABLE user PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

完整优化方案选择

@Component
public class PaginationStrategy {
    public Page<?> findByPage(int page, int size, PaginationStrategy strategy) {
        switch (strategy) {
            case CURSOR:
                return findByCursor(page, size);
            case COVERING_INDEX:
                return findByCoveringIndex(page, size);
            case SQL_CALC_ROWS:
                return findBySqlCalcRows(page, size);
            case CACHED:
                return findByCached(page, size);
            default:
                return findByTraditional(page, size);
        }
    }
    // 结合多种优化方案
    public Page<User> findByComprehensiveOptimization(int page, int size) {
        // 1. 小数据量使用传统分页
        if (page * size < 10000) {
            return findByTraditional(page, size);
        }
        // 2. 大数据量使用游标分页
        if (size <= 50) {
            return findByCursor(page, size);
        }
        // 3. 使用覆盖索引+延迟COUNT
        return findByCoveringIndexWithLazyCount(page, size);
    }
}
enum PaginationStrategy {
    TRADITIONAL,
    CURSOR,
    COVERING_INDEX,
    SQL_CALC_ROWS,
    CACHED
}

根据实际业务场景选择合适的优化方案,建议组合使用以达到最佳效果。