Mybatis-Plus查询大量数据时，慢！

最新推荐文章于 2024-08-02 11:00:48 发布

TheBlindM

最新推荐文章于 2024-08-02 11:00:48 发布

阅读量6.8k

点赞数 3

分类专栏： Java 文章标签： MyBatis 大量数据慢 Mybatis Plus

本文链接：https://blog.csdn.net/qq_40495860/article/details/116164869

版权

Java 专栏收录该内容

24 篇文章 1 订阅

订阅专栏

Mybatis-Plus查询大量数据时很慢！

解决：

1.采用少量多次的方法来处理

        int count= service.count();
        int pageSize=10000;
        int pageNum=(count/pageSize)+1;
        AtomicInteger iStart = new AtomicInteger(0);
        final String limit="limit ";
        Stream<Integer> iterate = Stream.iterate(0, i -> ++i);
        iterate.limit(pageNum).parallel().forEach(integer -> {
            QueryWrapper<Object> objectQueryWrapper = new QueryWrapper<>();
            objectQueryWrapper.last(limit+ iStart.getAndAdd(pageSize) +","+pageSize);
            List list = service.list(objectQueryWrapper);
            list.parallelStream().forEach(v->{
               //......
            });
        
        });

注意并发问题 iStart会被多个线程操作所以用AtomicInteger来操作

2.Limit 优化

都知道limit 查询在很大数据量下越往后偏移速度越慢

优化

select *  from  A表 as a join (  select id  from A表 limit  offet,size ) as b  on b.id=a.id

这sql可以通用啊，那我怎么加入到Mybatis plus里面啊

3.Mybatis plus SQL注入器

3.1自定义全局方法

关于自定义全局方法如何写可以看官方的案例

public class LimitMethod extends AbstractMethod {
    
    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        String id=tableInfo.getKeyColumn();
        String tableName = tableInfo.getTableName();
        String sql = String.format("<script> select * from %s as a join\n" +
                        "      (select %s from %s %s) as b on b.%s=a.%s  </script>", tableName,
                id, tableName,sqlComment(),id,id);
        SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);
        return addSelectMappedStatementForTable(mapperClass,"selectListByLimit",sqlSource,tableInfo);
    }
}

注意 < script >

3.2注册自定义全局方法


@Component
public class MyDefinedSqlInjector extends DefaultSqlInjector {


    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass) {

        List<AbstractMethod> methodList = super.getMethodList(mapperClass);
        methodList.add(new LimitMethod());
        return methodList;
    }
}

3.3自定义Mapper

public interface CommonMapper<T> extends BaseMapper<T> {

    List<T> selectListByLimit(@Param("ew") Wrapper<T> queryWrapper);

}

然后需要用这个的继承它即可

public interface AdviceMapper extends CommonMapper<Advice> {

}

3.4自定义Service

public interface CommonService<T> extends IService<T> {

   List<T> listByLimit(Wrapper<T> queryWrapper);

}

public class CommonServiceImpl<M extends CommonMapper<T>,T> extends ServiceImpl<M,T> implements CommonService<T> {

    @Override
    public List<T> listByLimit(Wrapper<T> queryWrapper) {
        return this.baseMapper.selectListByLimit(queryWrapper);
    }

}

public interface AdviceService extends CommonService<Advice> {

}

public class AdviceServiceImpl extends CommonServiceImpl<AdviceMapper, Advice> implements AdviceService {

}

3.5使用

        int count= service.count();
        int pageSize=10000;
        int pageNum=(count/pageSize)+1;
        AtomicInteger iStart = new AtomicInteger(0);
        final String limit="limit ";
        Stream<Integer> iterate = Stream.iterate(0, i -> ++i);
        iterate.limit(pageNum).parallel().forEach(integer -> {
            QueryWrapper<Object> objectQueryWrapper = new QueryWrapper<>();
            objectQueryWrapper.last(limit+ iStart.getAndAdd(pageSize) +","+pageSize);
            List list = service.list(objectQueryWrapper);
            list.parallelStream().forEach(v->{
               //......
            });
        
        });