一、code
/**
* 提煤单未使用明细列表查询
* @param
* @return
* @author ly
* @date 2020/4/26 20:35
* @since cloud2.0
*/
private ResponseBean getUnUsePage(Integer biId, String search, int page, int rows, Boolean isColliery, Integer customerId, String startDate, String endDate,
String startNo, String endNo, String collieryName, String forwardBiName) {
long millis1 = System.currentTimeMillis();
QueryWrapper<BuStock> wrapper = new QueryWrapper();
if (isColliery) { // 如果是煤矿
List<Integer> customerStockIds = buStockService.getCustomerStockIds(biId);
if (customerId != -1) {
wrapper.eq("belong_bi_id", customerId);
}
if (!customerStockIds.isEmpty()) {
wrapper.in("id", customerStockIds);
} else {
return new ResponseBean(CommonResultCode.SUCCESS.getCode(),"提煤单未使用列表分页查询 "+CommonResultCode.SUCCESS.getMsg(), null);
}
} else {
wrapper.eq("belong_bi_id", biId); // 不是煤矿, 则查询属于我的库存
}
if (!StringUtils.isEmpty(startNo) && !StringUtils.isEmpty(endNo)) {
wrapper.le("start_no", startNo);
wrapper.ge("end_no", endNo);
}
if (!StringUtils.isEmpty(collieryName)) {
wrapper.like("colliery_name", collieryName);
}
if (!StringUtils.isEmpty(forwardBiName)) {
wrapper.like("forward_bi_name", forwardBiName);
}
wrapper.gt("current_stock_num", 0);
wrapper.orderByDesc("create_date");
wrapper.eq("is_cloud", 1);
wrapper.eq("stock_type", 0);
//判断不是运单
wrapper.eq("is_waybill", 0);
// if (!StringUtils.isEmpty(startDate) && !StringUtils.isEmpty(endDate)) { // 创建时间段筛选
// wrapper.between("create_date", startDate, endDate);
// } else {
// Calendar now = Calendar.getInstance();
// wrapper.ge("create_date", now.get(Calendar.YEAR)+"-01-01 00:00:00");
// }
/* 筛选 */
if(!StringUtils.isEmpty(search)) {
// 通过煤票号, 拉运地址, 煤种名称筛选
wrapper.and(qw -> qw.like("start_no", search).or().like("delivery_address", search).or().like("product_name", search).or().like("end_no", search));
}
Page<BuStock> records = new Page(page, rows);
// 获取提煤单库存
buStockService.getUnUsedPage(records, wrapper);
long millis2 = System.currentTimeMillis();
for (BuStock buStock : records.getRecords()) {
BuContract buContract = buContractMapper.getPurchaseContractByDisId(buStock.getDisgroupId());
if (buContract != null && buContract.getCustomerBiId().equals(biId.longValue()) && !buContract.getBelongBiId().equals(biId.longValue())) {
buStock.setPurchaseContractName(buContract.getName());
} else {
buStock.setPurchaseContractName("");
}
}
BuVoucherUseSituation buVoucherUseSituation = new BuVoucherUseSituation();
buVoucherUseSituation.setRecords(records);
Long sum = buStockService.getUnUsedPageSum(wrapper);
if (!StringUtils.isEmpty(sum)) {
buVoucherUseSituation.setCount(sum);
} else {
buVoucherUseSituation.setCount(0L);
}
// for(BuStock buStock: records.getRecords()) {
// if (StringUtils.isEmpty(buStock.getDeliveryAddress())) {
// buStock.setDeliveryAddress("暂无");
// }
//// if (StringUtils.isEmpty(buStock.getCollieryBiId())) {
//// buStock.setCollieryName("暂无");
//// } else {
//// buStock.setCollieryName(infoService.getById(buStock.getCollieryBiId()).getName());
//// }
//// buStock.setShopCustomerName(infoService.getById(buStock.getBelongBiId()).getName());
//// buStock.setAffBiName(infoService.getById(buStock.getCreateBiid()).getName());
// buStock.setForwardBiName(infoService.getById(buVoucherDisGroupService.getRecordByStockId(biId, buStock.getId(), 1).getDistributeBiId()).getName());
//
// }
long millis3 = System.currentTimeMillis();
System.out.println("时间1==" + millis1);
System.out.println("时间2==" + millis2);
System.out.println("时间3==" + millis3);
System.out.println("时间21==" + (millis2-millis1));
System.out.println("时间32==" + (millis3-millis2));
System.out.println("时间31==" + (millis3-millis1));
// response
return new ResponseBean(CommonResultCode.SUCCESS.getCode(),"提煤单未使用列表分页查询 "+CommonResultCode.SUCCESS.getMsg(),buVoucherUseSituation);
}
时间1==1619139659946
时间2==1619139676599
时间3==1619139685356
时间21==16653
时间32==8757
时间31==25410
二、优化代码
2.1、优化在循环中发SQL
for (BuStock buStock : records.getRecords()) {
BuContract buContract = buContractMapper.getPurchaseContractByDisId(buStock.getDisgroupId());
if (buContract != null && buContract.getCustomerBiId().equals(biId.longValue()) && !buContract.getBelongBiId().equals(biId.longValue())) {
buStock.setPurchaseContractName(buContract.getName());
} else {
buStock.setPurchaseContractName("");
}
}
优化后代码
List<Integer> disIds = records.getRecords().stream().map(item -> {
return item.getDisgroupId();
}).collect(Collectors.toList());
List<BuContractVO> contractLists = this.buContractMapper.getContractListByDisIds(disIds);
for (BuStock buStock : records.getRecords()) {
for (BuContractVO bcVO : contractLists) {
if (bcVO.getCustomerBiId().equals(biId.longValue()) && !bcVO.getBelongBiId().equals(biId.longValue())) {
buStock.setPurchaseContractName(bcVO.getName());
} else {
buStock.setPurchaseContractName("");
}
}
}
二、SQL
select * from
(
select distinct bs.id,
bs.belong_bi_id,
bs.start_no,
bs.end_no,
bs.stock_num,
bs.current_stock_num,
bs.product_id,
bs.product_name,
bs.colliery_bi_id,
bs.colliery_code,
bs.stock_type,
bs.orjginal_start_no,
bs.cancel_time,
bs.cancel_reason,
bs.cancel_user_bi_id,
bs.product_price,
ifnull(bs.last_customer,'暂无') as last_customer,
ifnull(bs.delivery_address, '暂无') as delivery_address,
bs.version,
bs.del_flag,
bs.create_by,
bs.create_date,
bs.update_by,
bs.update_time,
bs.remarks,
bs.address_id,
bs.stock_state,
bs.create_type,
bs.yx_stock_id,
bs.create_biid,
bs.source,
bs.is_cloud,
bs.destination,
bs.is_waybill,
bs.last_customer_bi_id,
bs.is_message_send ,
ifnull(bi.name,'暂无') as colliery_name,
ifnull(bi1.name,'暂无') as shop_customer_name,
ifnull(bi2.name,'暂无') as aff_bi_name,
b.forward_bi_name,
b.disgroupId as disgroupId
from bu_stock bs
left join
base_info bi
on
bi.id=bs.colliery_bi_id
left join
base_info bi1
on
bi1.id=bs.belong_bi_id
left join
base_info bi2
on
bi2.id=bs.create_biid
inner join
(
select bvdg.stock_id,
ifnull(bi.name,'暂无') as forward_bi_name,
bvdg.id as disgroupId
from bu_voucher_dis_group bvdg
left join base_info bi
on bi.id=bvdg.distribute_bi_id) b
on b.stock_id=bs.id ) t
WHERE (
belong_bi_id = 602
AND current_stock_num > 0
AND is_cloud = 1
AND stock_type = 0
AND is_waybill = 0
)
ORDER BY create_date DESC LIMIT 0,10
二、使用 Profile 分析
SHOW PROFILES
使用 Profile 分析.png
三、诊断SQL
SHOW PROFILE cpu, block io FOR QUERY 127
image.png
四、EXPLAIN分析
EXPLAIN select * from ( select distinct bs.id,
bs.belong_bi_id,
bs.start_no,
bs.end_no,
bs.stock_num,
bs.current_stock_num,
bs.product_id,
bs.product_name,
bs.colliery_bi_id,
bs.colliery_code,
bs.stock_type,
bs.orjginal_start_no,
bs.cancel_time,
bs.cancel_reason,
bs.cancel_user_bi_id,
bs.product_price,
ifnull(bs.last_customer,'暂无') as last_customer,
ifnull(bs.delivery_address, '暂无') as delivery_address,
bs.version,
bs.del_flag,
bs.create_by,
bs.create_date,
bs.update_by,
bs.update_time,
bs.remarks,
bs.address_id,
bs.stock_state,
bs.create_type,
bs.yx_stock_id,
bs.create_biid,
bs.source,
bs.is_cloud,
bs.destination,
bs.is_waybill,
bs.last_customer_bi_id,
bs.is_message_send ,
ifnull(bi.name,'暂无') as colliery_name,
ifnull(bi1.name,'暂无') as shop_customer_name,
ifnull(bi2.name,'暂无') as aff_bi_name,
b.forward_bi_name,
b.disgroupId as disgroupId
from bu_stock bs
left join
base_info bi
on
bi.id=bs.colliery_bi_id
left join
base_info bi1
on
bi1.id=bs.belong_bi_id
left join
base_info bi2
on
bi2.id=bs.create_biid
inner join
(
select bvdg.stock_id,
ifnull(bi.name,'暂无') as forward_bi_name,
bvdg.id as disgroupId
from bu_voucher_dis_group bvdg
left join base_info bi
on bi.id=bvdg.distribute_bi_id) b
on b.stock_id=bs.id ) t
WHERE (
belong_bi_id = 602
AND current_stock_num > 0
AND is_cloud = 1
AND stock_type = 0
AND is_waybill = 0
)
ORDER BY create_date DESC LIMIT 0,10
EXPLAIN分析.png
- 中招 Using filesort
- 中招 Using temporary
五、大SQL分成几个SQL执行
/**
* 提煤单未使用明细列表查询
*
* @param
* @return
* @author ly
* @date 2020/4/26 20:35
* @since cloud2.0
*/
private ResponseBean getUnUsePage(Integer biId, String search, int page, int rows, Boolean isColliery, Integer customerId, String startDate, String endDate,
String startNo, String endNo, String collieryName, String forwardBiName) {
QueryWrapper<BuStock> wrapper = new QueryWrapper();
if (isColliery) { // 如果是煤矿
List<Integer> customerStockIds = buStockService.getCustomerStockIds(biId);
if (customerId != -1) {
wrapper.eq("belong_bi_id", customerId);
}
if (!customerStockIds.isEmpty()) {
wrapper.in("id", customerStockIds);
} else {
return new ResponseBean(CommonResultCode.SUCCESS.getCode(), "提煤单未使用列表分页查询 " + CommonResultCode.SUCCESS.getMsg(), null);
}
} else {
wrapper.eq("belong_bi_id", biId); // 不是煤矿, 则查询属于我的库存
}
if (!StringUtils.isEmpty(startNo) && !StringUtils.isEmpty(endNo)) {
wrapper.le("start_no", startNo);
wrapper.ge("end_no", endNo);
}
if (!StringUtils.isEmpty(collieryName)) {
wrapper.like("colliery_name", collieryName);
}
if (!StringUtils.isEmpty(forwardBiName)) {
wrapper.like("forward_bi_name", forwardBiName);
}
wrapper.gt("current_stock_num", 0);
wrapper.orderByDesc("create_date");
wrapper.eq("is_cloud", 1);
wrapper.eq("stock_type", 0);
//判断不是运单
wrapper.eq("is_waybill", 0);
// if (!StringUtils.isEmpty(startDate) && !StringUtils.isEmpty(endDate)) { // 创建时间段筛选
// wrapper.between("create_date", startDate, endDate);
// } else {
// Calendar now = Calendar.getInstance();
// wrapper.ge("create_date", now.get(Calendar.YEAR)+"-01-01 00:00:00");
// }
/* 筛选 */
if (!StringUtils.isEmpty(search)) {
// 通过煤票号, 拉运地址, 煤种名称筛选
wrapper.and(qw -> qw.like("start_no", search).or().like("delivery_address", search).or().like("product_name", search).or().like("end_no", search));
}
Page<BuStock> records = new Page(page, rows);
// 获取提煤单库存
this.buStockService.page(records, wrapper);
// 煤矿名称
Set<Integer> collieryIdSet = records.getRecords().stream().map(item -> {
return item.getCollieryBiId();
}).collect(Collectors.toSet());
// 创建企业biid
Set<Integer> createIdSet = records.getRecords().stream().map(item -> {
return item.getCreateBiid();
}).collect(Collectors.toSet());
// 购煤客户
Set<Integer> beLongIds = records.getRecords().stream().map(item -> {
return item.getBelongBiId();
}).collect(Collectors.toSet());
// ids
Set<Integer> ids = records.getRecords().stream().map(item -> {
return item.getId();
}).collect(Collectors.toSet());
List<BuContractVO> buContractVOS = new ArrayList<>();
// 煤矿名称
CompletableFuture<List<Info>> collieryInfoFuture = CompletableFuture.supplyAsync(() -> {
return this.infoService.listByIds(collieryIdSet);
}, executor);
// 创建企业biid
CompletableFuture<List<Info>> createInfoFuture = CompletableFuture.supplyAsync(() -> {
return this.infoService.listByIds(createIdSet);
}, executor);
// 购煤客户
CompletableFuture<List<Info>> beLongInfoFuture = CompletableFuture.supplyAsync(() -> {
return this.infoService.listByIds(beLongIds);
}, executor);
// 转发记录
CompletableFuture<List<BVDGVO>> gvdgInfoFuture = CompletableFuture.supplyAsync(() -> {
return this.buVoucherDisGroupMapper.getBuVoucherDisGroupByStockIds(ids);
}, executor).whenCompleteAsync((res, ex) -> {
if (null == ex) {
List<Integer> disIds = res.stream().map(item -> {
return item.getDisgroupId();
}).collect(Collectors.toList());
// 合同
List<BuContractVO> buContractVOList = this.buContractMapper.getContractListByDisIds(disIds);
buContractVOS.clear();
buContractVOS.addAll(buContractVOList);
}
}, executor);
// 全部库存
CompletableFuture<List<BuStock>> stockListFuture = CompletableFuture.supplyAsync(() -> {
return this.buStockService.list(wrapper);
}, executor);
try {
CompletableFuture<Void> allOf = CompletableFuture.allOf(collieryInfoFuture, createInfoFuture, beLongInfoFuture, gvdgInfoFuture, stockListFuture);
allOf.get();
List<Info> collieryInfo = collieryInfoFuture.get();
List<Info> createInfo = createInfoFuture.get();
List<Info> beLongInfo = beLongInfoFuture.get();
List<BVDGVO> bvdgvos = gvdgInfoFuture.get();
List<BuStock> buStockList = stockListFuture.get();
// 赋值-煤矿名称
for (Info info : collieryInfo) {
for (BuStock stock : records.getRecords()) {
if (info.getId().equals(stock.getCollieryBiId())) {
stock.setCollieryName(info.getName());
}
}
}
// 赋值-提煤单所有人
for (Info info : createInfo) {
for (BuStock stock : records.getRecords()) {
if (info.getId().equals(stock.getCreateBiid())) {
stock.setAffBiName(info.getName());
}
}
}
// 赋值-购煤客户
for (Info info : beLongInfo) {
for (BuStock stock : records.getRecords()) {
if (info.getId().equals(stock.getBelongBiId())) {
stock.setShopCustomerName(info.getName());
}
}
}
// 赋值-采购合同id
for (BVDGVO bvdg : bvdgvos) {
for (BuStock stock : records.getRecords()) {
if (bvdg.getStockId().equals(stock.getId())) {
stock.setForwardBiName(bvdg.getForwardBiName());
stock.setDisgroupId(bvdg.getDisgroupId());
}
}
}
// 赋值-合同名称
for (BuStock buStock : records.getRecords()) {
for (BuContractVO bcVO : buContractVOS) {
if (bcVO.getCustomerBiId().equals(biId.longValue()) && !bcVO.getBelongBiId().equals(biId.longValue())) {
buStock.setPurchaseContractName(bcVO.getName());
} else {
buStock.setPurchaseContractName("");
}
}
}
BuVoucherUseSituation buVoucherUseSituation = new BuVoucherUseSituation();
buVoucherUseSituation.setRecords(records);
// 未使用库存
Long sum = 0L;
for (BuStock stock : buStockList) {
sum += stock.getCurrentStockNum();
}
if (!StringUtils.isEmpty(sum)) {
buVoucherUseSituation.setCount(sum);
} else {
buVoucherUseSituation.setCount(0L);
}
return new ResponseBean(CommonResultCode.SUCCESS.getCode(), "提煤单未使用列表分页查询 " + CommonResultCode.SUCCESS.getMsg(), buVoucherUseSituation);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
网友评论