关于后端:如何安全迁移大表过期数据到备份表

前言

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，能够设计为两个工作来保障程序性。

总结

以上是对于平安迁徙数据的一些思考和实际，欢送大家评论。