如何安全迁移大表过期数据到备份表？

前言

Mysql数据库中的表数据经年累月，数据量已经超出了可处理能力，一些快照表，比如订单表、消息表...，定期将三个月前的数据迁移到备份表，减轻快照表的查询开销。

如何做到安全的迁移数据

数据迁移的过程就是把一条数据插入到备份表，并删除表中的数据。插入和删除操作如何保证原子性成为迁移工作中的难点。有两种意外情况：1.插入后未删除 2.插入未成功直接删除。两种情况都不能被项目接受。

基于安全考虑，开发一个迁移功能，分步骤完成迁移工作。

1. 标记可迁移数据
2. 标记过的数据插入备份表
3. 删除原表已备份未删除的数据，先查询备份表中是否存在然后再删除

    @Update("UPDATE user SET deleted = 1 WHERE create_time >= #{beginTime} AND create_time <= #{endTime} ")
    int markData(@Param("beginTime") LocalDateTime beginTime,
                 @Param("endTime") LocalDateTime endTime);

    @Select("SELECT id FROM user WHERE create_time >= #{beginTime} AND create_time <= #{endTime} AND deleted = 1" +
            "ORDER BY create_time ASC " +
            "LIMIT #{pageBegin}, #{pageSize} ")
    List<Long> queryMarkData(@Param("beginTime") LocalDateTime beginTime,
                             @Param("endTime") LocalDateTime endTime,
                             @Param("pageBegin") Integer pageBegin,
                             @Param("pageSize") Integer pageSize);

    // INSERT INTO user_backup SELECT * FROM user 查询条件未命中索引会锁表
    @Insert("INSERT INTO user_backup SELECT * FROM user WHERE id = #{id}")
    Map<Object, Object> saveBackupData(@Param("id") Long id);

    @Select("SELECT count(1) FROM user_backup WHERE id = #{id}")
    int existBackupData(@Param("id") Long id);

    @Update("DELETE user WHERE id = #{id}")
    int deleteBackupData(@Param("id") Long id);

@SpringBootTest
public class BackupService {

    @Resource
    private BackupMapper backupMapper;

    @Test
    public void createBackupTable(){
        // ...
    }
    
    @Test
    public void markData(){
        backupMapper.markData(LocalDateTime.now().minusYears(2), LocalDateTime.now().minusYears(1));
    }
    
    @Test
    public void queryThenSave(){
        List<Long> ids = backupMapper.queryMarkData(
                LocalDateTime.now().minusYears(2), LocalDateTime.now().minusYears(1), 0, Integer.MAX_VALUE);
        for (Long id : ids) {
            backupMapper.saveBackupData(id);
        }
    }

    @Test
    public void queryThenDelete(){
        List<Long> ids = backupMapper.queryMarkData(
                LocalDateTime.now().minusYears(2), LocalDateTime.now().minusYears(1), 0, Integer.MAX_VALUE);
        for (Long id : ids) {
            if (backupMapper.existBackupData(id) > 0) {
                backupMapper.deleteBackupData(id);
            }
        }
    }
}

queryThenSave queryThenDelete 两个操作可以批量执行也可以并发执行，不过queryThenSave 一定要早于 queryThenDelete，可以设计为两个任务来保证顺序性。

总结

以上是对于安全迁移数据的一些思考和实践，欢迎大家评论。