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的统计可以通过自己想法进行优化,避免不必要的性能开支