mybatis多线程查询数据库

定义task类

public class ChargeCallableTask implements Callable<Object>{

private  String method;

private Object parameter;

public ChargeCallableTask(String method,Object parameter){

this.method=method;

this.parameter=parameter;

}

@Override

public Object call() throws Exception{

ChargeServiceImpl  chargeServiceImpl  =(ChargeServiceImpl)SpringContextUtil.getBean(chargeService);

List<ChaChargeVo> chargeStatistics = Collections.Empty_LIST;

Long totalElements = null;

if("getChargeStatistics".equals(method)){

return chargeStatistics=chargeServiceImpl.getChargeStatistics((ChaChargeVo)parameter);

}else if("getTotalChargeStatistics".equals(method)){

return chargeServiceImpl.getTotalChargeStatistics((ChaChargeVo)parameter);

}

return "";

}

public static Future<Object> getFuture(ExecutorService threadPool,String method,Object parameter) throws Exception{

return threadPool.submit(new ChargeCallableTask(method,parameter));

}

}

chargeServiceImpl实现类中

public Result getChargeStatistics (ChaChargeVo chaChargeVo,HttpHeaders httpHeaders ){

ExecutorService threadPool=Executors.newFixedThreadPool(3);

Future<Object> getChargeStatistics = ChargeCallableTask.getFuture(threadPool,"getChargeStatistics",chaChargeVo);

Future<Object> getTotalChargeStatistics = ChargeCallableTask.getFuture(threadPool,"getTotalChargeStatistics",chaChargeVo);

totalElements = (long)getTotalChargeStatistics.get();

List<ChaChargeVo>chargeStatistics = getChargeStatistics.get();

}

public  List<ChaChargeVo>  getChargeStatistics(ChaChargeVo chaChargeVo){

return  chaChargeMapper.getChargeStatistics(chaChargeVo);

}

public  long getTotalChargeStatistics(ChaChargeVo chaChargeVo){

return  chaChargeMapper.getTotalChargeStatistics(chaChargeVo);

}

查询费用汇总和明细时使用1条sql查询在数据库阻塞锁表，所以拆成4条小sql,先按分页和查询条件查询出满足的customerCd再通过customerCd多线程4次异步调用查询数据和未结算金额及结算金额和总条数 在查询group by和order by加index where条件为常量  时间类型datetiime改为11位int类型做排序时候会加快0.5-1s速度。15s多线程后为11s,去身份证号关联2张表后为5s 

查询满足条件的10个customer_cd耗时2.8s左右，按中文名首字母升序排序并需要按customer_cd分组并加索引分页10条

改造etl抽取数据生成表时按中文名首字母排序的主键key为int类型自增，直接查询distinct10个customer_cd耗时100ms排序走主键

group by会排序 distinct不会排序能走索引情况下比group by快，distinct聚合函数时不走索引会filesort

查询数据时身份证号需要关联2张表时用explain查看使用Using join buffer (Block Nested Loop) type=all查询3张表type=ref

将身份证号加到主表中有80万数据 去掉身份证号后group by上加index type=range extra=using index condition;using where