MyBatis分页助手使用探测式分页解决大表统计总数慢的问题

1. 自定义PageHelper

使用装饰着模式自定义PageHelper，继承并重写getCountSql方法，对自动生成的统计SQL进行改造。

package com.lavender.component.mybatis.interceptor;

import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.github.pagehelper.autoconfigure.PageHelperProperties;
import com.google.common.collect.Lists;
import lombok.NoArgsConstructor;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.session.RowBounds;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Objects;

/**
 * 探测式步长分页助手
 * @Author Autumn、
 * @Date 2020年09月16日 22:58:07
 * @Description: 探测式分页助手，解决因为count而导致的大表查询速度缓慢问题
 */
@Component
@NoArgsConstructor
public class DetectPageHelper extends PageHelper {

    /** 默认步长 */
    private static final int DEFAULT_STEP = 10;
    /** 步长 */
    private static final ThreadLocal<Integer> LOCAL_STEP = new ThreadLocal<>();
    /** 当前页码 */
    private static final ThreadLocal<Integer> LOCAL_CURRENT = new ThreadLocal<>();
    /** 数据库类型 */
    private static String helperDialect;

    @Autowired
    public DetectPageHelper(PageHelperProperties properties) {
        DetectPageHelper.helperDialect = properties.getHelperDialect();
    }

    /**
     * 开始步长分页
     * @param pageNum  页码
     * @param pageSize 每页显示数量
     */
    public static <E> Page<E> startPage(int pageNum, int pageSize) {
        return DetectPageHelper.startPage(pageNum, pageSize, DEFAULT_STEP);
    }

    /**
     * 开始步长分页
     * @param pageNum  页码
     * @param pageSize 每页显示数量
     * @param step step 步长
     */
    public static <E> Page<E> startPage(int pageNum, int pageSize, int step) {
        LOCAL_STEP.set(step);
        System.out.println(Thread.currentThread().getId());
        return PageHelper.startPage(pageNum, pageSize);
    }

    /**
     * 构建分页实例
     * @param list 数据列表
     * @param <E> 泛型
     * @return 分页实例
     */
    public static <E> PageInfo<E> buildPageInfoInstance(List<E> list) {
        return DetectPageHelper.buildPageInfoInstance(list, DEFAULT_STEP);
    }

    /**
     * 构建分页实例
     * @param list 数据列表
     * @param navigatePages 分页条分页总数
     * @param <E> 泛型
     * @return 分页实例
     */
    public static <E> PageInfo<E> buildPageInfoInstance(List<E> list, int navigatePages) {
        PageInfo<E> pageInfo = new PageInfo<>(list);
        // 获取当前页码
        int currentPage = LOCAL_CURRENT.get();
        LOCAL_CURRENT.remove();
        // 根据步长计算之后还有多少页
        int totalPage = currentPage + (int) Math.ceil(pageInfo.getTotal() / pageInfo.getPageSize()) - 1;
        // 计算分页条起始页和结束页
        int begin = currentPage - (navigatePages / 2) > 1 ? currentPage - (navigatePages / 2) : 1;
        int end = totalPage >= currentPage + (navigatePages - currentPage + begin - 1)
                ? currentPage + (navigatePages - currentPage + begin - 1) : totalPage;
        if (end == totalPage) {
            begin = end - navigatePages + 1 >= 1 ? end - navigatePages + 1 : 1;
        }
        List<Integer> pageNumbers = Lists.newArrayList();
        for (int number = begin; number <= end; number++) {
            pageNumbers.add(number);
        }
        int[] pageNumbersArray = new int[pageNumbers.size()];
        for (int number = 0; number < pageNumbers.size(); number++) {
            pageNumbersArray[number] = pageNumbers.get(number);
        }
        // 因为这边的总数量数并不是真是的总数量数，所以分页的许多参数都变得不可用
        pageInfo.setNavigatepageNums(pageNumbersArray);
        pageInfo.setPageNum(currentPage);
        pageInfo.setSize(totalPage);
        pageInfo.setIsFirstPage(currentPage == 1);
        pageInfo.setIsLastPage(currentPage == totalPage || totalPage == 0);
        pageInfo.setHasPreviousPage(currentPage > 1);
        pageInfo.setHasNextPage(currentPage < totalPage);
        return pageInfo;
    }

    @Override
    public String getCountSql(MappedStatement ms, BoundSql boundSql, Object parameterObject, RowBounds rowBounds, CacheKey countKey) {
        String countSql = super.getCountSql(ms, boundSql, parameterObject, rowBounds, countKey);
        Integer step = LOCAL_STEP.get();
        System.out.println(Thread.currentThread().getId());
        if (Objects.nonNull(step)) {
            LOCAL_STEP.remove();
            Page localPage = DetectPageHelper.LOCAL_PAGE.get();
            LOCAL_CURRENT.set(localPage.getPageNum());
            // 计算偏移量
            int offset = (localPage.getPageNum() - 1) * localPage.getPageSize();
            // 计算探测长度
            int rows = step * localPage.getPageSize();
            countSql = customizationCountSql(countSql, offset, rows);
        }
        return countSql;
    }

    private static String customizationCountSql(String countSql, int offset, int rows) {
        switch (helperDialect) {
            // Oracle...
            // MySQL...
            default:
                return String.format(
                        "SELECT COUNT(0) FROM ( %s LIMIT %d, %d ) T",
                        countSql.replace("count(0)", "1"), offset, rows
                );
        }
    }

}

2. 修改分页助手的 dialect配置

在配置文件中指定分页助手使用的PageHelper为我们自定义的PageHelper。

pagehelper.dialect=com.lavender.component.mybatis.interceptor.DetectPageHelper

3.在代码中使用自定义分页助手进行分页查询

package com.lavender.web.controller;

import com.github.pagehelper.PageInfo;
import com.lavender.component.mybatis.interceptor.DetectPageHelper;
import com.lavender.entity.po.User;
import com.lavender.mappers.UserMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

/**
 * 测试接口
 * @Author Autumn、
 * @Date 2020年09月15日 23:34:38
 * @Description:
 */
@Slf4j
@RestController
@RequestMapping("/test")
public class TestController {

    @Autowired
    private UserMapper userMapper;

    /**
     * MyBatis10页探测
     */
    @GetMapping("/func1/{pageNumber}/{pageSize}")
    public PageInfo<User> func1(@PathVariable int pageNumber, @PathVariable int pageSize) {
        DetectPageHelper.startPage(pageNumber, pageSize);
        return DetectPageHelper.buildPageInfoInstance(userMapper.selectAll());
    }

}