MySQL调优--12--分批插入/更新数据 ---案例2

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

分批插入案例

MySQL调优–11–分批插入/更新数据 —案例

1.思路分析

1. 第一次进来 查询时间范围内的最小索引最小值
2. 每次根据唯一索引大于上次查出来的最小值，查一批数据
3. 更新最小值，循环查询

2.表结构

3.业务代码

public class AudienceCustomerReqDTO {

    private Long audid;
    private Integer nums;
    private Date startTime;
    private Date endTime;
    private Long minId;
    private Long maxId;
    private Integer needCreateTime;
    private Integer needUpdateTime;
}

if(!complementFlag){
            List<String> rowList = new ArrayList<>(); //单个文件集合
            AudienceCustomerReqDTO reqDTO = new AudienceCustomerReqDTO();
            reqDTO.setNums(3000);
            reqDTO.setStartTime(startDate);
            reqDTO.setEndTime(endDate);
            //循环查询数据
            while (true){
                ApiResponse<List<AudienceCustomerDTO>> response = audienceFeign.esQueryCustomer(reqDTO);
                ApiResponse.checkApiResponse(response);
                if (response == null || CollectionUtils.isEmpty(response.getData())) {
                    //结束前检查兜底写入文件
                    if (rowList.size() > 0) {
                        writeFile(file, rowList);
                        rowList.clear();
                    }
                    break;
                }
                List<AudienceCustomerDTO> dtoList = response.getData();
                //解析数据放入内存dtoList
                phaseData(dtoList,rowList);

                //10万一批写入文件
                if (rowList.size() >= 100000) {
                    writeFile(file, rowList);
                    file_num += rowList.size();
                    rowList.clear();
                }
                //查询下一批数据
                AudienceCustomerDTO lastElement = dtoList.get(dtoList.size() - 1);
                reqDTO.setAudid(lastElement.getAudid());
            }

        }

    @Override
    public List<AudienceCustomerDTO> esQueryCustomer(AudienceCustomerReqDTO reqDTO) {
        //第一次进来 查询时间范围内的最小audid
        if(reqDTO.getAudid()==null){
            AudMinMaxIdDTO minMaxId= audienceCustomerMapper.getMinMaxIdBetweenUpdateTime(reqDTO);
            if(minMaxId==null){
                log.info("该时间范围没有更新数据，request: {}",JSONObject.toJSONString(reqDTO));
                return null;
            }
            reqDTO.setAudid(minMaxId.getMinId()-1);
        }
        List<AudienceCustomerDTO> dtoList = audienceCustomerMapper.selectSingleAudienceTable(reqDTO);
        return dtoList;
    }

4.sql

    <select id="getMinMaxIdBetweenUpdateTime" resultType="dto.response.AudMinMaxIdDTO">
        select MIN(audid) as minId,MAX(audid) as maxId from t_audience_customer
        <where>
            <if test="param.startTime != null">
                and update_time <![CDATA[>=]]> #{param.startTime}
            </if>
            <if test="param.endTime != null">
                and update_time <![CDATA[<=]]> #{param.endTime}
            </if>
        </where>
    </select>

    <select id="selectSingleAudienceTable" resultType="dto.AudienceCustomerDTO">
        select
        <include refid="baseColumns"/>
        from t_audience_customer
        where audid > #{param.audid}
        <if test="param.startTime != null">
            and update_time <![CDATA[>=]]> #{param.startTime}
        </if>
        <if test="param.endTime != null">
            and update_time <![CDATA[<=]]> #{param.endTime}
        </if>
        order by audid asc
        limit #{param.nums}
    </select>