JdbcTemplate 两种分页查询方法 分页效率优化

JdbcTemplate jdbcTemplate = getJdbcTemplate();

int currPage = 150000;
int pageSize = 10;
int startRow = (currPage-1)*pageSize;
StringBuffer countSql = new StringBuffer("select count(*) ");
StringBuffer resSql = new StringBuffer("select * ");
StringBuffer tjSql = new StringBuffer("from test_jdbc_batch");


resSql.append(tjSql);
countSql.append(tjSql);
int totalCnt = jdbcTemplate.queryForObject(countSql.toString(),Integer.class);

StopWatch stopWatch = new StopWatch();
stopWatch.start("方式一：query(final String sql, final ResultSetExtractor rse)");
List> rsList = new ArrayList<>();
jdbcTemplate.query(resSql.toString(), rs -> {
    int currentRow = 0;
    int columnCount = rs.getMetaData().getColumnCount();
    while (rs.next() && (currentRow < (startRow + pageSize))) {
        if (currentRow >= startRow) {
            Map tempRow = new HashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                tempRow.put(rs.getMetaData().getColumnName(i), rs.getObject(i));
            }
            rsList.add(tempRow);
        }
        currentRow = currentRow + 1;
    }
    return rsList;
});
PageUtils page1 = new PageUtils(rsList,totalCnt,pageSize,currPage);
stopWatch.stop();

起始行数:

int startRow = (curPage-1)*pageSize;

总页数

int totalPage = (totalCnt +pageSize - 1) / pageSize;

或者

int totalPage = (int)Math.ceil((double)totalCount / pageSize);

stopWatch.start("方式二：limit startRow,pageSize");
List> rsListByLimit = jdbcTemplate.queryForList(resSql.append(" limit ").append(startRow).append(",").append(pageSize).toString());
PageUtils page2 = new PageUtils(rsListByLimit,totalCnt,pageSize,currPage);
stopWatch.stop();

System.out.println(stopWatch.prettyPrint());

• 以上两种方式，数据量少时，执行效率差不多，使用200w数据测试，从150w取10条，方式一用了8s，方式二用了3s。
• limit方式效率可能更高一些，也和where和order by相关条件有关，是否有索引有关。
• 当数据量比较小时（100万以内），无论你翻到哪一页，性能都是很快的。
• 两种方式原理差不多,就是查询m+n条记录，去掉前m条，返回后n条,那么m越大，查询性能就越低，因为MySQL需要扫描全部m+n条记录。

后续会对分页做进一步优化！！！！