系统查询慢性能优化

一.代码查询进行异步查询和批处理

1.使用异步批量查询，再同步阻塞拿结果数据，线程使用类代码如下

package com.dst.common.config.lock;

import com.dstcar.common.utils.spring.SpringUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;

import java.util.concurrent.Callable;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.Future;

/**
 * 
 * @author: lwp
 * @date: 2024/1/9 11:02
 */
public class AsyncExector {
    private static final Logger LOGGER = LoggerFactory.getLogger(AsyncExector.class);
    private final ThreadPoolTaskExecutor threadPoolTaskExecutor;
    private final CountDownLatch latch;

    public AsyncExector(int n){
        this.latch = new CountDownLatch(n);
        this.threadPoolTaskExecutor = SpringUtil.getBean(ThreadPoolTaskExecutor.class);
    }

    public <T> Future<T> run(Callable<T> task,final T defaultValue){
        return threadPoolTaskExecutor.submit(()->{
            T res = null;
            try {
               res = task.call();
            } catch (Exception e) {
                LOGGER.error("调用失败",e);
            }finally {
                latch.countDown();
            }
            return res == null?defaultValue:res;
        });
    }

    public void await() {
        try {
            this.latch.await();
        } catch (InterruptedException e) {
            LOGGER.error("等待失败",e);
        }
    }
}

2.for循环里面进行批处理查询，先查询出生成Map

        AsyncExector exector = new AsyncExector(12);
        // 服务组织
        //Map<String, OrgVirtualQueryServiceImpl.OrgVirtualExpand> orgVirtualExpandMap = orgVirtualQueryService.getOrgVirtualExpandByCodes(new ArrayList<>(serveCityCodeList));
        Future<Map<String, OrgVirtualQueryServiceImpl.OrgVirtualExpand>> orgVirtualExpandMap = exector.run(()-> orgVirtualQueryService.getOrgVirtualExpandByCodes(new ArrayList<>(serveCityCodeList)),new HashMap<>());

        //客户信息
        //Map<String,String> clienteleNameListMap = customerInfoService.getMapByClienteleIds(clienteleIds);
        Future<Map<String, String>> clienteleNameListMap = exector.run(()-> customerInfoService.getMapByClienteleIds(clienteleIds),new HashMap<>());

        //车辆属性
        List<String> attrKeys = Collections.singletonList(CenterStatusConst.PRODUCT_BULLETIN_NUMBER);
        //Map<String, CarProductDTO> carProductMap = getItem(skuCodes, attrKeys);
        Future<Map<String, CarProductDTO>> carProductMap = exector.run(()-> getItem(skuCodes, attrKeys),new HashMap<>());

        //车辆标签
        /*Map<Integer,String> carLabelStrMap =
                operatingVehicleLabelService.getCarLabelNameListMap(OperatingCarLableListQuery.
                builder().carIds(new ArrayList<>(carIds)).jobNum(query.getJobNum()).build());*/
        Future<Map<Integer,String>> carLabelStrMap = exector.run(()-> operatingVehicleLabelService.getCarLabelNameListMap(OperatingCarLableListQuery.
                builder().carIds(new ArrayList<>(carIds)).jobNum(query.getJobNum()).build()),new HashMap<>());

        // 场站
        //Map<Integer, SimpleModel> stationMap = stationService.getStationMapByStationIds(stationIds);
        Future<Map<Integer, SimpleModel>> stationMap = exector.run(()-> stationService.getStationMapByStationIds(stationIds),new HashMap<>());

        //金融服务
        //Map<String,String> financialServiceInfoListMap = financialService.getFinancialServicesMapByIds(new ArrayList<>(financialServices));
        Future<Map<String,String>> financialServiceInfoListMap = exector.run(()-> financialService.getFinancialServicesMapByIds(new ArrayList<>(financialServices)),new HashMap<>());

        // 获取车辆属性,vinCode:[attrId:AttrValueName]
        //Map<String, Map<String, String>> mapveh = getVehicleAttrMap(vinCodes);
        Future<Map<String, Map<String, String>>> mapveh = exector.run(()-> getVehicleAttrMap(vinCodes),new HashMap<>());

        // 获取属性id
        //Map<String, String> mapAttr = dictService.getDictMapByType(VehicleInventoryConst.VEHICLE_ATTR_ID);
        Future<Map<String, String>> mapAttr = exector.run(()-> dictService.getDictMapByType(VehicleInventoryConst.VEHICLE_ATTR_ID),new HashMap<>());

        //商品编码
        //Map<String,String> goodsCodeMap = productServiceImpl.getGoodsCodeListByRealSkuCodes(realSkuCodes);
        Future<Map<String,String>> goodsCodeMap = exector.run(()->  productServiceImpl.getGoodsCodeListByRealSkuCodes(realSkuCodes),new HashMap<>());

        // 新库位状态
        List<String> garageStateCodes=
                list.stream().map(OperatingNewCarViewPageListDTO::getGarageStateCode2nd).collect(toList());
        //Map<String,String> garageStateConfigMap=
        //        garageStateConfigService.getGarageStateFullNameAndCodeMap(garageStateCodes);
        Future<Map<String, String>> garageStateConfigMap = exector.run(()-> garageStateConfigService.getGarageStateFullNameAndCodeMap(garageStateCodes),new HashMap<>());

        //库存分类名称
        //Map<String, String> vehicleStateNamesMap = carInventoryService.getInventoryStateByVinCodes(vinCodes);
        Future<Map<String, String>> vehicleStateNamesMap = exector.run(()-> carInventoryService.getInventoryStateByVinCodes(vinCodes),new HashMap<>());

        //曾用车牌
        //Map<String, List<CarChangeRecord>> carChangeMap = basicCarService.getListByVinCodes(vinCodes);
        Future<Map<String, List<CarChangeRecord>>> carChangeMap = exector.run(()-> basicCarService.getListByVinCodes(vinCodes),new HashMap<>());
        exector.await();

3.再进行从内存拿数据处理和赋值

for (OperatingNewCarViewPageListDTO c : list) {
            //城市大区
            OrgVirtualQueryServiceImpl.OrgVirtualExpand orgVirtualExpand = orgVirtualExpandMap.get().get(c.getServeCityCode());
            if (orgVirtualExpand != null) {
                c.setCityName(orgVirtualExpand.getName());
                c.setRegionName(orgVirtualExpand.getPName());
            }
            //车辆标签
            c.setCarLabelName(carLabelStrMap.get().get(c.getCarId()));
            //客户名称
            c.setClienteleName(clienteleNameListMap.get().get(c.getClienteleId()));
            //入库时间
            c.setStockTimeName(DateUtil.convert2String(DateUtil.getDateByTime(c.getStockTime()),DateUtil.TIMEFORMAT));
            c.setCreateTimeName(DateUtil.convert2String(DateUtil.getDateByTime(c.getCreateTime()),DateUtil.TIMEFORMAT));
            //公告号
            CarProductDTO item = carProductMap.get().get(c.getSkuCode());
            if (item != null) {
                c.setNoticeNo(item.getProductAttributeValue(CenterStatusConst.PRODUCT_BULLETIN_NUMBER));
            }
            //场站
            SimpleModel stationInfo = stationMap.get().get(c.getStationId());
            if (stationInfo != null) {
                c.setStationName(stationInfo.getName());
            }
            // 金融服务
            String financialServicesName = convertFinancialServiceIdsName(financialServiceInfoListMap.get(),c.getFinancialServices());
            c.setFinancialServicesName(financialServicesName);

            c.setMaintainLabelName(VehicleMaintainLabelEnum.getMultiName(c.getMaintainLabel()));
            // 处理车辆属性
            Map<String, String> mapVehicleAttrManage = mapveh.get().get(c.getVinCode());
            converVehilcAttr(mapVehicleAttrManage, c, mapAttr.get());
            //商品编码
            c.setGoodsCode(goodsCodeMap.get().get(c.getRealSkuCode()));
            // 可售类型
            c.setSalableProductsName(CenterStatusConst.convertSalableProductsName(c.getSalableProducts()));
            // 库位状态的名称
            c.setGarageStateFullName(garageStateConfigMap.get().get(c.getGarageStateCode2nd()));
            // 库存分类名称
            c.setInventoryStateNames(vehicleStateNamesMap.get().get(c.getVinCode()));
            //曾用车牌
            List<CarChangeRecord> carChangeRecords = carChangeMap.get().get(c.getVinCode());
            if (CollectionUtils.isNotEmpty(carChangeRecords)) {
                c.setUsedPlateNumber(org.springframework.util.StringUtils.collectionToCommaDelimitedString(carChangeRecords.stream().map(CarChangeRecord::getPrePlateNumber).collect(Collectors.toList())));
            } else {
                c.setUsedPlateNumber("-");
            }
        }

二、查询SQL语句只返回需要使用的字段，这里只使用车架号，只返回车架号列表，舍弃其他字段返回

List<String> vinCodesStr = operatingNewCarService.getOperatingNewCarListNew(query);
if (CollectionUtils.isNotEmpty(vinCodesStr)) {
    query.setVinCodes(vinCodesStr);
}

三、增加查询表条件的字段索引

EXPLAIN SELECT distinct o.id,o.car_id,b.plate_number,b.vin_code,o.city_id,o.serve_city_code,o.car_new_old,o.car_purpose,o.salable_products,
        o.garage_state_code_1st,o.garage_state_code_2nd,o.use_type,o.use_purpose
        FROM dst_vehicle.o_operating_vehicle o
        INNER JOIN dst_vehicle.o_basic_car b ON b.id = o.car_id
        left join dst_vehicle.o_operating_vehicle_label t on b.id = t.car_id ;

//增加索引
CREATE INDEX serve_city_code_idx USING BTREE ON dst_vehicle.o_operating_vehicle (serve_city_code);

?四、优化效果

1.优化前：1.87s 优化后：339ms? 比优化前快了6倍