Del Levin‘s blog

我的member_user里面有352599条数据，gp_project里面有1211974条数据，请问该如何优化这个sql的查询效率

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);  
  
// 提取查询结果里面的supplierID的set集合  
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);  
    }  
}