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