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();//解析数据放入内存dtoListphaseData(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());}}

    @Overridepublic List<AudienceCustomerDTO> esQueryCustomer(AudienceCustomerReqDTO reqDTO) {//第一次进来 查询时间范围内的最小audidif(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_customerwhere 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 asclimit #{param.nums}</select>