mybatis-plus分頁(yè)插件之count優(yōu)化

示例代碼
分頁(yè)插件配置
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 {

    //配置分頁(yè)插件注入容器
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return interceptor;
    }
}
分頁(yè)代碼示例
分頁(yè)查詢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;
    }
分頁(yè)查詢service

    @Override
    public IPage<SplitExperimentType> selectAll(Page page) {
        return baseMapper.selectAll(page);
    }
分頁(yè)查詢mapper

IPage<SplitExperimentType> selectAll(IPage page);
分頁(yè)查詢mapper.xml

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


優(yōu)化前分頁(yè)sql執(zhí)行流程
1，先執(zhí)行count統(tǒng)計(jì)，統(tǒng)計(jì)sql如下，規(guī)則時(shí)方法sql結(jié)果作為子查詢，在外套一層select count(*) from (select * from split_experiment_type);

2, count > 0 ，在執(zhí)行方法映射 sql

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

第二步：select * from split_experiment_type limit xxx


性能分析
select count(*) from (select * from split_experiment_type)

這個(gè)sql肯定有問(wèn)題，select * from split_experiment_type 會(huì)掃描全列，即使用明確列替換 * 也是會(huì)掃描在統(tǒng)計(jì)count是不需要的列；

期望是在count統(tǒng)計(jì)數(shù)，最優(yōu)解是只統(tǒng)計(jì)符合條件的2級(jí)索引，或者主鍵索引，只統(tǒng)計(jì)索引列，性能剛剛的

但是統(tǒng)計(jì)count是sql是自動(dòng)生成的，有什么辦法不使用mybatis-plus的分頁(yè)插件自動(dòng)生成的count的sql呢，當(dāng)然可以，下面我們看下mybatis-plus的分頁(yè)插件部分源碼

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對(duì)象
        IPage<?> page = (IPage)ParameterUtils.findPage(parameter).orElse((Object)null);
        
        if (page != null && page.getSize() >= 0L && page.searchCount()) {
            //根據(jù)page對(duì)象的里的countId來(lái)查詢是否配置了自定義count的方法，countId就是自定義統(tǒng)計(jì)sql的mapper里的方法名
            MappedStatement countMs = this.buildCountMappedStatement(ms, page.countId());
            BoundSql countSql;
            //如根據(jù)countId能獲取MappedStatement，即存在自定義的方法執(zhí)行，就執(zhí)行自定義的mapper方法來(lái)統(tǒng)計(jì)count
            if (countMs != null) {
                
                countSql = countMs.getBoundSql(parameter);
            }
            //如根據(jù)countId不能獲取MappedStatement，就自己組裝count統(tǒng)計(jì)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;
        }
    }

  ......
   
}



所以，可以看出如果我們?cè)趐age對(duì)象中配置的countId（mapper里自定義統(tǒng)計(jì)count的方法名），并且存在mapper，存在countId對(duì)象值得統(tǒng)計(jì)方法（返回值是Long），并且在xml中進(jìn)行了實(shí)現(xiàn)，就會(huì)走自定義的統(tǒng)計(jì)方法

優(yōu)化后分頁(yè)查詢代碼
controller的查詢方法不變



service里

 @Override
    public IPage<SplitExperimentType> selectAll(Page page) {
        //指定自定義統(tǒng)計(jì)sql的方法名
        page.setCountId("selectAll_COUNT");
        return baseMapper.selectAll(page);
    }


mapper里新增一個(gè)selectAll_COUNT的方法

IPage<SplitExperimentType> selectAll(IPage page);
Long selectAll_COUNT(IPage page);
mapper.xml新增一個(gè)selectAll_COUNT方法的實(shí)現(xiàn)

<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>
優(yōu)化后分頁(yè)查詢sql執(zhí)行流程
第一步：SELECT COUNT(1) FROM split_experiment_type

第二步：select * from split_experiment_type limit xxx
count的統(tǒng)計(jì)可以通過(guò)自己想法進(jìn)行優(yōu)化，避免不必要的性能開(kāi)支

特此記錄，希望能幫到大家



作者： IT學(xué)習(xí)道場(chǎng)


歡迎關(guān)注微信公眾號(hào) ： IT學(xué)習(xí)道場(chǎng)