# sql-blog-day2024-05-26

# 场景：

今天写了一个数据量大的列表查询业务代码，需求是查询一个 3 千多数据的表还要根据这个表的字段去查询另一个表对应的总数量。一对多关系的，然后在数据查询出来处理数据的时候使用 map 去处理数据结果是每条数据都要进行查询计算总数极大的影响都了性能问题，下面记录解决方式

# 代码：

# coontroller

	/**
	* 分页查询矿山图层管理数据
	* @param mineLayerDataDto
	* @return
	*/
	@Anonymous
	@PostMapping("/getAll")
	public TableDataInfo dataAll(@RequestBody MineLayerDataDto mineLayerDataDto)
	{
	return ksJbxxhzService.dataAll(mineLayerDataDto);
	}

# dto 类

	@Data
	@NoArgsConstructor
	public class KsTcCountDto {
	private String xkzh;
	private Long count;
	}

# mapper

	/**
	* 查询挂载数量
	* @param xkzhList
	* @return
	*/
	@Select("<script>" +
	"SELECT xkzh, COUNT(*) as count FROM ks_tc " +
	"WHERE xkzh IN " +
	"<foreach item='item' index='index' collection='list' open='(' separator=',' close=')'>" +
	"#{item}" +
	"</foreach> " +
	"GROUP BY xkzh" +
	"</script>")
	List<KsTcCountDto> selectCountByXkzhBatch(@Param("list") List<String> xkzhList);

# service

	/**
	* 分页查询矿山图层管理数据
	* @param mineLayerDataDto
	* @return
	*/
	TableDataInfo dataAll(MineLayerDataDto mineLayerDataDto);

# serviceImpl

	/**
	* 分页查询矿山图层管理数据
	* @param mineLayerDataDto
	* @return
	*/
	@Override
	public TableDataInfo dataAll(MineLayerDataDto mineLayerDataDto)
	{
	TableDataInfo tableDataInfo = new TableDataInfo();
	Page<KsJbxxhz> page = new Page<>(mineLayerDataDto.getPageNo(), mineLayerDataDto.getPageSize());
	Page<KsJbxxhz> result = baseMapper.selectPage(page, Wrappers.<KsJbxxhz>lambdaQuery()
	.like(!StringUtil.isNullOrEmpty(mineLayerDataDto.getKsmc()), KsJbxxhz::getKsmc, mineLayerDataDto.getKsmc())
	.eq(mineLayerDataDto.getKsfl() != null, KsJbxxhz::getKsfl, mineLayerDataDto.getKsfl())
	.eq(mineLayerDataDto.getKqlx() != null, KsJbxxhz::getKqlx, mineLayerDataDto.getKqlx())
	.like(!StringUtil.isNullOrEmpty(mineLayerDataDto.getSzxzqmc()), KsJbxxhz::getSzxzqmc, mineLayerDataDto.getSzxzqmc())
	.like(!StringUtil.isNullOrEmpty(mineLayerDataDto.getXkzh()), KsJbxxhz::getXkzh, mineLayerDataDto.getXkzh())
	.ge(!StringUtil.isNullOrEmpty(mineLayerDataDto.getYxqq()), KsJbxxhz::getYxqq, DateUtil.parse(mineLayerDataDto.getYxqq()))
	.le(!StringUtil.isNullOrEmpty(mineLayerDataDto.getYxqz()), KsJbxxhz::getYxqz, DateUtil.parse(mineLayerDataDto.getYxqz())));
	List<KsJbxxhz> records = result.getRecords();
	// 判断是否查询已挂载数据
	if (mineLayerDataDto.getGstate() != null && mineLayerDataDto.getGstate().equals(KsflEnum.MOUNTED.getCode().toString()))
	{
	String data = ksTcService.list().stream().map(r -> r.getXkzh()).collect(Collectors.joining(","));
	records = records.stream().filter(r -> data.contains(r.getXkzh())).collect(Collectors.toList());
	}
	//todo 优化代码，增加请求速度 (finish)
	// Step 1: 从记录中提取所有不同的 Xkzh 值
	List<String> xkzhList = records.stream()
	.map(KsJbxxhz::getXkzh)
	.distinct()
	.collect(Collectors.toList());
	// Step 2: 执行批处理查询以获取计数
	Map<String, Long> xkzhCountMap = this.countByXkzhBatch(xkzhList);
	// Step 3: 设置每条记录的 Qishu 值
	records.forEach(v -> v.setQishu(xkzhCountMap.getOrDefault(v.getXkzh(), 0L)));
	int total = records.size();
	tableDataInfo.setCode(HttpStatus.SUCCESS);
	tableDataInfo.setMsg("success");
	tableDataInfo.setRows(records);
	tableDataInfo.setTotal(total);
	return tableDataInfo;
	}

	/**
	* 根据许可证号集合查询总数并返回
	* @param xkzhList
	* @return
	*/
	public Map<String, Long> countByXkzhBatch(List<String> xkzhList) {
	return baseMapper.selectCountByXkzhBatch(xkzhList).stream()
	.collect(Collectors.toMap(KsTcCountDto::getXkzh, KsTcCountDto::getCount));
	}

之前写的代码如下 (OUT)：性能极差

	@Override
	public TableDataInfo dataAll(MineLayerDataDto mineLayerDataDto)
	{
	TableDataInfo tableDataInfo = new TableDataInfo();
	Page<KsJbxxhz> page = new Page<>(mineLayerDataDto.getPageNo(), mineLayerDataDto.getPageSize());
	Page<KsJbxxhz> result = baseMapper.selectPage(page, Wrappers.<KsJbxxhz>lambdaQuery()
	.like(!StringUtil.isNullOrEmpty(mineLayerDataDto.getKsmc()), KsJbxxhz::getKsmc, mineLayerDataDto.getKsmc())
	.eq(mineLayerDataDto.getKsfl() != null, KsJbxxhz::getKsfl, mineLayerDataDto.getKsfl())
	.eq(mineLayerDataDto.getKqlx() != null, KsJbxxhz::getKqlx, mineLayerDataDto.getKqlx())
	.like(!StringUtil.isNullOrEmpty(mineLayerDataDto.getSzxzqmc()), KsJbxxhz::getSzxzqmc, mineLayerDataDto.getSzxzqmc())
	.like(!StringUtil.isNullOrEmpty(mineLayerDataDto.getXkzh()), KsJbxxhz::getXkzh, mineLayerDataDto.getXkzh())
	.ge(!StringUtil.isNullOrEmpty(mineLayerDataDto.getYxqq()), KsJbxxhz::getYxqq, DateUtil.parse(mineLayerDataDto.getYxqq()))
	.le(!StringUtil.isNullOrEmpty(mineLayerDataDto.getYxqz()), KsJbxxhz::getYxqz, DateUtil.parse(mineLayerDataDto.getYxqz())));
	List<KsJbxxhz> records = result.getRecords();
	// 判断是否查询已挂载数据
	if (mineLayerDataDto.getGstate() != null && mineLayerDataDto.getGstate().equals(KsflEnum.MOUNTED.getCode().toString()))
	{
	String data = ksTcService.list().stream().map(r -> r.getXkzh()).collect(Collectors.joining(","));
	records = records.stream().filter(r -> data.contains(r.getXkzh())).map(v -> {
	v.setQishu(ksTcService.count(Wrappers.<KsTc>lambdaQuery().eq(KsTc::getXkzh, v.getXkzh())));
	return v;
	}).collect(Collectors.toList());
	}
	int total = records.size();
	tableDataInfo.setCode(HttpStatus.SUCCESS);
	tableDataInfo.setMsg("success");
	tableDataInfo.setRows(records);
	tableDataInfo.setTotal(total);
	return tableDataInfo;
	}

计算机学科 blog - sql