JdbcTemplate中如何进行分页查询的优化？

JdbcTemplate提供了分页查询的支持,但默认实现存在性能问题,需要进行优化。

默认分页查询会执行:

SELECT * FROM table LIMIT ? OFFSET ?  

这会导致查询全表然后丢弃OFFSET之前的记录,当OFFSET很大时,消耗资源严重。

优化步骤如下:
1、 自定义RowMapper实现,只返回需要的列:

public class UserRowMapper implements RowMapper<User> {
    public User mapRow(ResultSet rs, int rowNum) throws SQLException {
        User user = new User();
        user.setId(rs.getInt("id"));
        user.setName(rs.getString("name"));
        return user;
    }
}

• 只需要id和name列,过滤其他列

2、 创建分页查询SQL,使用主键限制记录范围:

SELECT id, name FROM table WHERE id >= ? AND id < ?  

• 使用id主键限制查询范围,这可以有效避免全表扫描

3、 计算主键范围:

int start = (pageNum - 1) * pageSize;   // 开始id
int end = start + pageSize;             // 结束id

4、 执行分页查询,设置主键范围参数:

List<User> users = jdbcTemplate.query(sql, new UserRowMapper(), start, end);

• 将start和end作为参数限制主键范围

5、 查询结果即为一页的数据
一个优化示例:

String sql = "SELECT id, name FROM user WHERE id >= ? AND id < ?";

int start = (pageNum - 1) * pageSize;  
int end = start + pageSize;  

List<User> users = jdbcTemplate.query(sql, new UserRowMapper(), start, end); 

相比默认实现,优化后的分页查询:

1. 避免全表扫描, seulement在主键范围内查询
2. 只查询需要的列,提高效率
3. 支持任意高的分页,而不会存在性能问题