MyBatis查询Oracle百万级数据慢如何优化缩短查询时间?

数据库 潘老师 2年前 (2021-11-18) 5451 ℃ (2) 扫码查看

MyBatis一次性从数据库查询出百万级数据不仅非常慢,而且对服务器的CPU和内存也是极大的考验,因此在不考虑CPU和内存的情况下,要把百万级数据的查询时间优化到秒级甚至毫秒级是非常有必要的,那么该如何去具体优化来缩短查询时间呢?

最近有个需求和顺丰对接,要求我这里提供接口数据,一个在百万级别,一个在十万级别,然后接口内部去做匹配,匹配耗时较短,关键耗时在查询百万级数据然后加载到内存的时间,因此潘老师针对这种情况做了下测试,然后针对实际情况进行优化。

前期准备

首先创建一个测试表,然后出插入了300W条数据,用于查询测试。

第1步:测试原始方式

使用最原始的mybatis的查询sql进行查询,这里先什么条件都不加,直接查全部数据,具体的mapper.xml如下:

<select id="selectAll"
            resultType="com.sf.hlcs.sps.mapper.work.entity.SortplanTestEntity">
        select
            <include refid="Base_Column_List"></include>
        from
        t_sps_sortplan_test
</select>

测试结果就是等了几分钟都没出结果,然后就直接放弃了,因此如果不做任何优化直接一最传统的方式去查询,肯定是行不通的。

第2步:初步优化方式

初步优化方式就是mybatis给我们查询select标签 提供一个fetchSize属性,要知道通过JDBC取数据时,默认是10条数据取一次,即fetchSize为10(根据oracle的文档,默认的fetchSize是10),如果增大这个数字可以减少客户端与Oracle的往返,减少响应时间,网上有建议这个数值不要超过100,要不然对中间件内存消耗大,但是极端情况我还是将值先直接设置为了5W测试了下:

<select id="selectAll" fetchSize="50000"
            resultType="com.sf.hlcs.sps.mapper.work.entity.SortplanTestEntity">
        select
            <include refid="Base_Column_List"></include>
        from
        t_sps_sortplan_test
</select>

测试查询后结果显然理想了很多,只耗时1分多钟,但仍然无法满足实际需求。
MyBatis查询Oracle百万级数据慢如何优化缩短查询时间?
可以多测试几次。调节fetchSize值,找到更优的值,毕竟5W对CPU和内存都瞬间拉爆,如下图:
MyBatis查询Oracle百万级数据慢如何优化缩短查询时间?

第3步:优化SQL

SQL优化其实比较复杂,不具体展开,我们这里因为是无条件查询,所以优化点就在查询的字段数量,一定要尽量减少查询的字段数,只查必要的字段数,可以极大的减少数据代大小(300W数据大小估计有几个G了),这样就极大地缩短了Oracle和服务器的数据传输时间,以及CPU去将查询结果转为实体对象的时间。上面的SQL是查询所有字段,估计有10多个,现在我这里只需要查5个字段,优化sql如下:

<select id="selectAll" fetchSize="50000"
            resultType="com.sf.hlcs.sps.mapper.work.entity.SortplanTestEntity">
        select
            TIME_LIMIT,
            DES_CODE,CONTAINER_DES_CODE,
            CONTAINER_TIME_LIMIT,SOP
        from
        t_sps_sortplan_test
</select>
提示:如果你的SQL很复杂,可以查看SQL执行计划去具体优化

测试结果只要0.3分钟不到,也就是18秒左右。相比之前1分多钟,效率又提高了几倍。但是,还不能满足实际需求,继续优化,如果你的SQL比较复杂,查询条件比较多,务必要多优化SQL。
MyBatis查询Oracle百万级数据慢如何优化缩短查询时间?

第4步:多线程查询

如果SQL也优化到了极致,那么在接下来就可以考虑多线程并发去查询数据库了,可以用多线程分页查,或者根据某个字段分类去查(不好保证每隔线程查询的记录数均衡),总之找到最加性能的多线程查询方式,多线程分页查比较通用,每个线程查询的记录数量也比较均衡,实现起来也会复杂一些,我这里先采用多线程按照某些字段查询的方式区实现了下(因为我这里测试的数据在该字段分布上比较均衡),具体实现如下:
1)新建Callable线程类:

public class SelectDataCallable  implements Callable<List<SortplanTestEntity>> {

    private String llcsSysCode;
    private String lineCode;
    private SortplanTestService service;

    private Integer subId;

    public SelectDataCallable(Integer subId, SortplanTestService service) {

        this.subId = subId;
        this.service = service;
    }

    @Override
    public List<SortplanTestEntity> call() throws Exception {

        return service.selectBySubID(subId);
    }
}

2)SQL分条件查询

<select id="selectAll" fetchSize="50000"
            resultType="com.sf.hlcs.sps.mapper.work.entity.SortplanTestEntity">
        select
            TIME_LIMIT,
            DES_CODE,CONTAINER_DES_CODE,
            CONTAINER_TIME_LIMIT,SOP
        from
        t_sps_sortplan_test
        where sub_id=#{subId}
</select>

3)创建线程池提交查询任务

@Autowired
private SortplanTestService sortplanTestService;
// 4核处理器可以开8线程
private static ExecutorService executorService = Executors.newFixedThreadPool(8);
public void query() throws ExecutionException, InterruptedException {
        System.out.println("正在查询...");
        long t1 = System.currentTimeMillis();
        List<Future<List<SortplanTestEntity>>> futures = new ArrayList<>();
        SelectDataCallable c = null;
        // 8个线程拉满
        for (int subId=1;subId<=8;subId++){
            c = new SelectDataCallable(subId,sortplanTestService);
            // 提交线程任务
            Future<List<SortplanTestEntity>> f = executorService.submit(c);
            futures.add(f);
        }

        List<SortplanTestEntity> list = new ArrayList<>();
        //获取线程返回的查询结果
        for(Future<List<SortplanTestEntity>> f:futures){
            list.addAll(f.get());
        }

        long t2 = System.currentTimeMillis();
        System.out.println(list.size());
        System.out.println("耗时:"+((t2-t1)/1000)+"s");
}

经测试查询完300W数据并映射成对象,需要6s多(这个图忘了截了),由于这个结果是走了VPN,如果直接走内网,应该能达到5s以内,并且线程数也可以根据自己的服务器配置以及实际需求来决定,总之300W数据优化到5s内应该问题不大,能否优化到1s以内就不太清楚了,100W数据可能达到1秒左右,甚至1s以内,但我这毕竟是笔记本电脑配置相比服务器也弱很多,服务器性能越强,也越能缩短查询是时间,以后如果有新方案,继续补充。


版权声明:本站文章,如无说明,均为本站原创,转载请注明文章来源。如有侵权,请联系博主删除。
本文链接:https://www.panziye.com/java/database/3968.html
喜欢 (11)
请潘老师喝杯Coffee吧!】
分享 (0)
用户头像
发表我的评论
取消评论
表情 贴图 签到 代码

Hi,您需要填写昵称和邮箱!

  • 昵称【必填】
  • 邮箱【必填】
  • 网址【可选】

(2) 个小伙伴在畅所欲言
  1. 用户头像
                  List<SortplanTestEntity> list = new ArrayList<>();        //获取线程返回的查询结果        for(Future<List<SortplanTestEntity>> f:futures){            list.addAll(f.get());        }这一步等于还是把300W数据一下子塞到了内存, 会不会很容易OOM呀
    仙人 2023-02-14 15:43 回复
  2. 用户头像
    都这个干了,还是慢
    Clausene 2022-07-06 16:47 回复