此帖子用于抛砖引玉
我的member_user里面有352599条数据，gp_project里面有1211974条数据，
SELECT gp.*, mu.linkmanName , mu.linkmanPhone, mu.legalPersonName, mu.legalPersonPhone, mu.address, mu.registerArea FROM gp_project gp LEFT JOIN member_user mu ON mu.supplierId = gp.supplier_id WHERE DATE(gp.publicity_time) >= '2024-04-03' AND DATE(gp.publicity_time) <= '2024-04-07' and mu.deleted = 0
针对这样的数据，我的数据表里面数据太多，导致了查询需要512秒的时间，而且这仅仅是两个表的关联，之后还需要关联四五个表组成一条查询语句，如果仅在mysql里面查，未免有些太为难服务器了。
因为平常批量数据用的sql里面的in语法比较多，所以尝试了一下用关联字段in数据的情况，突然发现，这样还挺快，主要是因为关联字段是索引，所以比较快，通过单表查出数据，然后将关键字段提取出来，in到另一个表里面快速查询，最后拼接字段，来实现数据的查询结果。
另外因为涉及到多个循环，如果仅仅遵循 循环的外小内大原则，这样未免要写多个循环，降低了程序的运行效率多个O(n^2)的程序跑的，这样未免太致命了。改用map的key和value对照关系这样通过一个循环+map的方式降低时间复杂度为O(1) ，这样就会快很多
其实我在网上找了很多办法都挺无力的，排除掉物理升级和缓存这两个（因为没钱和数据每天都在更新），索引优化，分页查询，改数据类型，这些能做到的都是微不足道。还希望有大佬可以给出一些新的思路和想法，万分感谢，本贴的丑陋代码仅作抛砖引玉。
下面是操作案例（取自部分的项目代码）
mybaits部分（单个案例）
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xyjq.mapper.db1.LoginMemberUserDao">
<!--根据供应商id判断是否为注册用户 -->
<select id="isRegisterSupplier" resultType="com.xyjq.entity.db1.LoginMemberUserEntity">
SELECT supplierId,createTime FROM `login_member_user` WHERE supplierId in
<foreach collection="supplierIdList" item="supplierId" open="(" separator="," close=")">
#{supplierId}
</foreach>
</select>
</mapper>
业务层部分
List<GpProjectEntity> list = baseDao.queryList(params);
Set<Integer> supplierIdList = list.stream()
.map(GpProjectEntity::getSupplierId)
.filter(Objects::nonNull)
.collect(Collectors.toSet());
SimpleDateFormat inputFormat = new SimpleDateFormat("yyyyMMddHHmmss");
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
// 以下遍历中防止嵌套for循环O(n^2)，使用map对象存储降低时间复杂度为O(1)
Map<String, MemberUserEntity> memberUserInfoMap = new HashMap<>();
memberUserDao.getMemberUserInfo(supplierIdList).forEach(memberUser -> memberUserInfoMap.put(memberUser.getSupplierId().toString(), memberUser));
Map<String, SupplierBussinessInformationEntity> supplierBusinessInfoMap = new HashMap<>();
supplierBussinessInformationDao.getSupplierBusinessInfo(supplierIdList).forEach(supplierBusinessInfo -> supplierBusinessInfoMap.put(supplierBusinessInfo.getSupplierId().toString(), supplierBusinessInfo));
Map<String, LoginMemberUserEntity> loginMemberUserMap = new HashMap<>();
loginMemberUserDao.isRegisterSupplier(supplierIdList).forEach(loginMemberUser -> loginMemberUserMap.put(loginMemberUser.getSupplierId().toString(), loginMemberUser));
Map<String, String> financingNeedsMap = new HashMap<>();
// 根据搜索结果，如果key一样就拼接value
financingNeedsDao.getCompFinancingNeeds(supplierIdList).forEach(financingNeeds -> {
String key = financingNeeds.getSupplierId();
String value = FinancingStatus.getDescriptionByCode(financingNeeds.getFinancingProgress()) + "(" + financingNeeds.getCreateTime() + ")";
financingNeedsMap.merge(key, value, (oldValue, newValue) -> oldValue + ", " + newValue);
});
for (GpProjectEntity project : list) {
if (project.getAgName() != null && !project.getAgName().isEmpty()) {
project.setProjectNameMergeAgBid(project.getAgName());
} else if (project.getJatTpProName() != null && !project.getJatTpProName().isEmpty()) {
project.setProjectNameMergeAgBid(project.getJatTpProName());
}
if (project.getAgTotalMoney() != null) {
project.setProjectMoneyMergeAgBid(project.getAgTotalMoney());
} else {
project.setProjectMoneyMergeAgBid(project.getWinPriceRevised());
}
// 设置项目的日期
if (project.getAgSignDate() != null) {
Date date = null;
try {
date = inputFormat.parse(project.getAgSignDate());
} catch (ParseException e) {
throw new RuntimeException(e);
}
project.setProjectDateMergeAgBid(dateFormat.format(date));
} else {
project.setProjectDateMergeAgBid( dateFormat.format(project.getJatWinTime()) );
}
String supplierId = project.getSupplierId().toString();
// 设置供应商信息
MemberUserEntity memberUser = memberUserInfoMap.get(supplierId);
if (memberUser != null) {
project.setProjectLinkManName(memberUser.getLinkmanName());
project.setProjectLinkManPhone(memberUser.getLinkmanPhone());
project.setProjectLegalPersonName(memberUser.getLegalPersonName());
project.setProjectLegalPersonPhone(memberUser.getLegaPersonPhone());
project.setProjectAddress(memberUser.getAddress());
project.setProjectRegisterArea(memberUser.getRegisterArea());
}
// 设置工商信息省市区
SupplierBussinessInformationEntity supplierBussinessInformation = supplierBusinessInfoMap.get(supplierId);
if (supplierBussinessInformation != null) {
project.setProjectProvince(supplierBussinessInformation.getProvince());
project.setProjectCity(supplierBussinessInformation.getCity());
project.setProjectDistrict(supplierBussinessInformation.getDistrict());
}
// 设置是否是注册用户
LoginMemberUserEntity loginMemberUser = loginMemberUserMap.get(supplierId);
if (loginMemberUser != null) {
project.setProjectIsRegisterSupplier("已注册（" + dateFormat.format(loginMemberUser.getCreateTime()) + ")");
}
// 设置融资历史记录
String financingNeeds = financingNeedsMap.get(supplierId);
if (financingNeeds != null) {
project.setProjectFinancingNeeds(financingNeeds);
}
}
发表于 2024-4-10 14:19
|
发表于 2024-4-11 09:33
学习了！