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分多钟,但仍然无法满足实际需求。
可以多测试几次。调节fetchSize
值,找到更优的值,毕竟5W对CPU和内存都瞬间拉爆,如下图:
第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>
测试结果只要0.3分钟不到,也就是18秒左右。相比之前1分多钟,效率又提高了几倍。但是,还不能满足实际需求,继续优化,如果你的SQL比较复杂,查询条件比较多,务必要多优化SQL。

第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以内,但我这毕竟是笔记本电脑配置相比服务器也弱很多,服务器性能越强,也越能缩短查询是时间,以后如果有新方案,继续补充。