Mybatis PageHelper 分页查询优化

一、应用场景：

MYSQL数据库，使用 PageHelper工具类进行分页查找，在表数据量很大的情况下，分页查询的速度会明显下降。

二、问题描述：

1. 原查询代码

// 通过PageHelper.startPage 方法 先获取记录总数，然后根据总数、页号、每页大小 查询得到分页数据
Page<User> page = PageHelper.startPage(pageNo, pageSize);
List<User> listDTO = accountFundRecordMapper.getUser(queryParam);

DataPage<User> dataPage = null;
if (null != page && null != listDTO) {
    dataPage = new DataPage<>(listDTO, page.getTotal(), pageNo, pageSize);
} else {
    dataPage = new DataPage<>(new ArrayList<>(), 0, pageNo, pageSize);
}

2. 分页查询执行步骤

2.1 获取记录总数

通过组装 getUser()方法对应SQL（即：select name from user），得到统计记录数的SQL，获取记录总数，组装后的SQL如下：

select count(0) from (select id, name, age, addr from user) tmp_count

2.2 分页查询

根据页号、每页大小 查询得到分页数据。 SQL如下：

select id, name, age, addr from user LIMIT ? 

2.3 执行步骤分析

对于2.1 获取记录总数而言，原查询SQL存在两种情况：
1.查询SQL为单表查询,但要查询的字段很多，在数据量大的情况下，可按照步骤 3.1 自定义COUNT SQL语句 进行优化
2.查询SQL为多表查询，在数据量大的情况下，可按照步骤 3.2 自定义分页SQL 语句 进行优化

三、优化方案

3.1 自定义COUNT SQL语句

PageHelper 在执行时，会调用拦截器PageInterceptor中的代码，判断是否存在后缀为_COUNT的自定义COUNT SQL语句：


因此，只需要在查询mapper中手写一个方法名后缀为_COUNT的语句，那么拦截器就会执行自定义的查询SQL, 而不会像2.1 获取记录总数那样在原有的查询SQL外面套一层count语句。
自定义统计表记录的SQL如下：
新增方法名为getUser_COUNT的查询SQL：

select count(1) from user

3.2 自定义分页SQL 语句

1.不再使用PageHelper.startPage()做分页查询，手写分页查询，传入 pageStart 和 pageEnd：

// 根据pageNo、pageSize计算 pageStart和 pageEnd
private void getQueryParam(MapObject queryParam) {
        int pageNo1 = Integer.parseInt((String)queryParam.get("pageNo"));
        int pageSize1 = Integer.parseInt((String)queryParam.get("pageSize"));

        if(pageNo1 == 1) {
            queryParam.put("pageStart", 0);
            queryParam.put("pageEnd", pageSize1);
        } else {
            queryParam.put("pageStart", pageSize1 * (pageNo1 -1));
            queryParam.put("pageEnd", pageSize1);
        }
}

2.查询数据代码如下：

// 获取数据
List<AccountInfoPageDTO> list = gfssClientMapper.getUser(queryParam);

int total = 0;
if (CollectionUtils.isNotEmpty(list)) {
    total = list.get(0).getTotal();
}

analyzeAccountTradeType(list, false);

DataPage<AccountInfoPageDTO> dataPage;
if (null != list) {
    dataPage = new DataPage<>(list, total, pageNo, pageSize);
} else {
    dataPage = new DataPage<>(new ArrayList<>(), 0, pageNo, pageSize);
}

3.getUser(Map map)的SQL如下：
先分页查询主键的记录，并统计主键的数量，即可得到需要查询的数据和记录总数；然后再查询表user的其它字段数据，这样即可提高查询性能。