mybatis-plus分页插件之count优化

示例代码

分页插件配置

package com.example.demo.conf;

import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
@MapperScan(basePackages = "com.example.demo.mapper")
public class MybatisPlusConfig {

    //配置分页插件注入容器
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return interceptor;
    }
}

分页代码示例

分页查询control

@PostMapping("/selectPage")
public IPage<SplitExperimentType> selectAll(){
    Page page = new Page();
    page.setCurrent(1L);
    page.setSize(2L);
    IPage<SplitExperimentType> list = service.selectAll(page);
    return list;
}

分页查询service

@Override
public IPage<SplitExperimentType> selectAll(Page page) {
    return baseMapper.selectAll(page);
}

分页查询mapper

IPage<SplitExperimentType> selectAll(IPage page);

分页查询mapper.xml

<select id="selectAll" resultType="com.example.demo.model.SplitExperimentType">
    select * from split_experiment_type
</select>

优化前分页sql执行流程

1,先执行count统计,统计sql如下,规则时方法sql结果作为子查询,在外套一层select count(*) from (select * from split_experiment_type);

2, count > 0 ,在执行方法映射 sql

第一步:select count(*) from (select * from split_experiment_type);

第二步:select * from split_experiment_type limit xxx

性能分析

select count(*) from (select * from split_experiment_type)

这个sql肯定有问题,select * from split_experiment_type 会扫描全列,即使用明确列替换 * 也是会扫描在统计count是不需要的列;

期望是在count统计数,最优解是只统计符合条件的2级索引,或者主键索引,只统计索引列,性能刚刚的

但是统计count是sql是自动生成的,有什么办法不使用mybatis-plus的分页插件自动生成的count的sql呢,当然可以,下面我们看下mybatis-plus的分页插件部分源码

package com.baomidou.mybatisplus.extension.plugins.inner;


public class PaginationInnerInterceptor implements InnerInterceptor {
   
    public boolean willDoQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
        //获取page对象
        IPage<?> page = (IPage)ParameterUtils.findPage(parameter).orElse((Object)null);
        
        if (page != null && page.getSize() >= 0L && page.searchCount()) {
            //根据page对象的里的countId来查询是否配置了自定义count的方法,countId就是自定义统计sql的mapper里的方法名
            MappedStatement countMs = this.buildCountMappedStatement(ms, page.countId());
            BoundSql countSql;
            //如根据countId能获取MappedStatement,即存在自定义的方法执行,就执行自定义的mapper方法来统计count
            if (countMs != null) {
                
                countSql = countMs.getBoundSql(parameter);
            } 
            //如根据countId不能获取MappedStatement,就自己组装count统计sql
            else {
                countMs = this.buildAutoCountMappedStatement(ms);
                String countSqlStr = this.autoCountSql(page.optimizeCountSql(), boundSql.getSql());
                MPBoundSql mpBoundSql = PluginUtils.mpBoundSql(boundSql);
                countSql = new BoundSql(countMs.getConfiguration(), countSqlStr, mpBoundSql.parameterMappings(), parameter);
                PluginUtils.setAdditionalParameter(countSql, mpBoundSql.additionalParameters());
            }

            CacheKey cacheKey = executor.createCacheKey(countMs, parameter, rowBounds, countSql);
            List<Object> result = executor.query(countMs, parameter, rowBounds, resultHandler, cacheKey, countSql);
            long total = 0L;
            if (CollectionUtils.isNotEmpty(result)) {
                Object o = result.get(0);
                if (o != null) {
                    total = Long.parseLong(o.toString());
                }
            }

            page.setTotal(total);
            return this.continuePage(page);
        } else {
            return true;
        }
    }

  ......
   
}

所以,可以看出如果我们在page对象中配置的countId(mapper里自定义统计count的方法名),并且存在mapper,存在countId对象值得统计方法(返回值是Long),并且在xml中进行了实现,就会走自定义的统计方法

优化后分页查询代码

controller的查询方法不变

service里

@Override
public IPage<SplitExperimentType> selectAll(Page page) {
    //指定自定义统计sql的方法名
    page.setCountId("selectAll_COUNT");
    return baseMapper.selectAll(page);
}

mapper里新增一个selectAll_COUNT的方法

IPage<SplitExperimentType> selectAll(IPage page);
Long selectAll_COUNT(IPage page);

mapper.xml新增一个selectAll_COUNT方法的实现

<select id="selectAll" resultType="com.example.demo.model.SplitExperimentType">
        select * from split_experiment_type
</select>

<select id="selectAll_COUNT" resultType="java.lang.Long">
        SELECT COUNT(1) FROM split_experiment_type
</select>

优化后分页查询sql执行流程

第一步:SELECT COUNT(1) FROM split_experiment_type

第二步:select * from split_experiment_type limit xxx

count的统计可以通过自己想法进行优化,避免不必要的性能开支

Comments: 6

「人生在世,留句话给我吧」

提交评论