SpringBoot高性能同步MySQL数据

背景

随着业务的增长，原有的数据库压力逐渐增大，放任不管必然会影响到业务的响应速度，严重甚至会出现数据库死锁，OOM，导致业务瘫痪，所以某些表需要做数据迁移至新数据库，减轻原数据的压力。

数据的迁移方案

1、可使用工具(navicat)进行数据迁移
2、通过程序(Java代码)的方式进行迁移
本文说的是第二种方式

数据迁移需要考虑的问题

1、需要以分页的方式进行，不可一次查询大量的数据,很有
2、查询的字段需要有索引，最好是主键ID
3、插入的方式使用批处理，切勿逐条插入。
4、记录同步过程，如果经常同步，可把记录写库

迁移数据的主要步骤

1、创建craete_time索引

# 创建creat_time索引 
ALTER TABLE xxx ADD INDEX idx_ctime (create_time);

2、创建必要的Entity

@Data
public class DataLimitQuery {
    /**
     * 下次分页Id
     */
    private Long nextPageId;
    /**
     * 限制数据开始
     */
    private Integer limitStart;
    /**
     * 限制数据结束
     */
    private Integer limitEnd;
    /**
     * Id值范围(左)
     */
    private Long idRangeMid;
    /**
     * Id值范围(右)
     */
    private Long idRangeMax;
	/**
     * 开始时间
     */
    private String startTime;
    /**
     * 结束时间
     */
    private String endTime;
}

3、编写对应的Mapper接口

    /**
     * 根据条件查询列表
     * @param dataLimitQuery
     * @return
     */
    List<xxx> queryListLimit(DataLimitQuery dataLimitQuery);
	
	/**
     * 批量插入
     * @param entities
     */
    void insertBatch(@Param("entities") List<xxxEntity> entities);

4、编写Mapper.xml

<select id="queryListLimit" resultType="com.xxx.xxx.xxx.xxx.entity.xxx">
    SELECT * FROM
    `xxx
    <where>
        <if test="nextPageId != null">
            AND id > #{nextPageId}
        </if>
        <if test="idRangeMid != null and idRangeMax != null">
            AND id BETWEEN #{idRangeMid} AND #{idRangeMax}
        </if>
        <if test=" startTime != null">
            and create_time <![CDATA[>=]]> #{startTime}
        </if>
        <if test=" endTime != null">
            and create_time <![CDATA[<=]]> #{endTime}
        </if>
    </where>
    ORDER BY id ASC
    <choose>
        <when test="limitStart != null and limitEnd != null">
            LIMIT #{limitStart},#{limitEnd}
        </when>
        <when test="limitEnd != null">
            LIMIT #{limitEnd}
        </when>
        <otherwise>
            LIMIT 0,3000
        </otherwise>
    </choose>
</select>

<insert id="insertBatch">
    INSERT INTO `xxx表` (`id`, `xxx字段`, ...)
    VALUES
    <foreach collection="entities" item="item" index="index" separator=",">
        (#{item.id}, #{item.xxx字段}, ...)
    </foreach>
</insert>

5、业务逻辑代码

CompletableFuture.runAsync(()->{
    DataLimitQuery dataLimitQuery = new DataLimitQuery();
    dataLimitQuery.setStartTime("2024-07-31 03:10:08");
    dataLimitQuery.setEndTime("2024-08-01 19:01:08");
    // 同步xxx表
    List<xxx> originList = new ArrayList<>();
    log.info("开始同步xxx数据");
    long l = System.currentTimeMillis();
    int i = 0;
    do{
        i++;
        if (!CollectionUtils.isEmpty(originList)){
            dataLimitQuery.setNextPageId(originList.get(originList.size()-1).getId());
        }
        originList = xxxMapper.queryListLimit(dataLimitQuery);
        if (CollectionUtils.isEmpty(originList)){
            break;
        }
        log.info("迁移xxx数据,size:{},nextPageId:{}", originList.size(), originList.get(originList.size()-1).getId());
        try {
            xxxMapper.insertBatch(originList);
        } catch (DuplicateKeyException e) {
		    // 注意：这里发生异常会直接跳出循环，解决的方案：1、异常批次入库，另外处理；2、再try catch做简约处理
            log.warn("xxx进行迁移时,主键发生了冲突,批次:{},查询条件:{}", i, dataLimitQuery);
            // 当主键冲突时，进行检查逐条插入
            for (xxxEntity xxx对象 : originList) {
                int exist = xxxMapper.existById(xxx对象.getId());
                if (exist == 0) {
                    xxxMapper.insert(xxx对象);
                }
            }
        } catch (Exception e) {
            log.error("同步xxx数据发生了异常,查询条件:{}", dataLimitQuery, e);
        }
    } while (!CollectionUtils.isEmpty(originList));
    log.info("结算同步xxx数据,共耗时:{}毫秒", System.currentTimeMillis()-l);
});

总结

1、添加(查询|删除SQL)需要的索引
2、创建必要的Entity
3、编写对应的Mapper接口
4、编写对应的Mapper.xml文件
5、编写数据迁移逻辑