示例代码
分页插件配置
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的统计可以通过自己想法进行优化,避免不必要的性能开支
「人生在世,留句话给我吧」