定义task类
public class ChargeCallableTask implements Callable{
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 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 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 getChargeStatistics = ChargeCallableTask.getFuture(threadPool,"getChargeStatistics",chaChargeVo);
Future getTotalChargeStatistics = ChargeCallableTask.getFuture(threadPool,"getTotalChargeStatistics",chaChargeVo);
totalElements = (long)getTotalChargeStatistics.get();
ListchargeStatistics = getChargeStatistics.get();
}
public List 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
