mysql千万数据迁移

mysql千万数据迁移

最近有个需求，项目中故障表每天以50w条的行数新增，现已有2千万数据，还在不断增加，导致查询效率极其低(索引解决不了)，为此，和同事讨论，表中只保留最近15天的数据，其他历史数据迁移到备份表中，增加查询效率。

思路

1.先备份数据(切记很重要，很重要!)
2.删除原表中15天之前的所有数据
3.每天定时跑任务，去备份15天之前的数据，并删除掉(每天晚上跑，数据量保持在百万级别内，降低难度)

问题

1.需要不停止当前业务的情况下，迁移数据。
2. 还要删除故障表不包含在当前时间15天内的数据。
3. 因为数据在千万级别，所以在以上操作的时候要考虑性能,不要影响到其他 业务的正常使用

实现

1.备份数据,删除原表废弃数据

这里其实在备份数据的时候，遇到了很多问题，因为数据库是阿里云的，然后我不能直接上阿里云上备份数据(其实在云上备份，更快捷),然后也没有服务器可言，只能通过其他工具进行备份。

1，先是使用navicat进行备份数据，这里直接导出的话，很慢，我也尝试了几次，都不行
2.使用navicat中的工具，数据传输，虽然效率比导出sql好些，但是他会少导入很多数据，这里我测试了几次，发现每次数据都不一样。

然后使用之前一直再用的sqlyog，直接右键表(或者数据库),选择将表复制，然后2千万条数据，不到半个小时就备份好了，虽然可能还是很慢，但相对上面来说，还是好了很多，最起码它导出的数据是正确的

数据备份好了，就要删除原表中的数据了，上文也提到了，原表中只保留15天以内的数据，但15天之前的数据量过于庞大(1千万以上),所以不要使用 delete from table where time < xxx
这样直接删除，因为delete语句是一行一行执行，它会对每一行生成log日志，效率极低，特别是在数据量很大的情况下，可能会导致死锁。

还有一直办法是先备份好备份好15天内的数据， 使用truncate table，然后再将刚刚备份的数据导入进去,它和delete的区别是，truncate是物理直接删除，delete是逻辑删除，我们都知道delete删除之后不会直接在物理空间中直接删掉，mysql会定期开一个线程，去删除数据。这种方法适合当前业务终止的情况下，因为你需要将表相当于直接删除掉，然后在重新往里导入数据,这里的话不适合现在的需求。
有两种方法，一种是写sql，根据时间，每天删除数据，因为每天的数据量在50w条以内，索引执行时间也不是很长，还有一种是写程序，循环天数，去删除，都可以，反正主要的思想就是把大变小，分而治止，这里因为时间关系，就直接写sql，根据时间删除。

2.定时任务(去备份数据,以及删除旧数据)

这里使用公司封装的定时项目,每天晚上12点跑，这里说一下遇到的问题吧，
因为我之前只保留了15天以内的数据，所以我只查询，15天之前的数据，然后直接读取到程序中，直接oom，主要是在这个过程中，还会有其他的对象在不断被创建，解决方法如下:

<select id="getData"  fetchSize="1000">
        SELECT
            xx
        FROM
        	xx
    </select>

mybatis底层还是调用jdbc，如果不设置fatchSize，默认是将50w数据全部查询，返回，容易oom，这里设置fatchSize，设置每次查询从jdbc结果集中拿1000条数据，之后再查一下批数据。

获取到数据之后，备份到历史表中了，这里一样的问题，如果50w数据直接插入，一样会报oom，原因是mybatis在解析sql的时候会生成大对象，

java.lang.OutOfMemoryError: Java heap space
 
	at java.base/java.util.Arrays.copyOf(Arrays.java:3746)

可以看到,Arrays在申请内存的时候,导致堆内存溢出

改进方法,分批新增:

//查询数据
        List<FaultRealDto> faultRealDtoList = faultRealMapper.getfaultRealData();

        //新增数据
        int index = faultRealDtoList.size() / 10000 + 1;

        for (int i = 0; i < index; i++) {
            //stream流表达式，skip表示跳过前i*10000条记录，limit表示读取当前流的前10000条记录
            List<FaultRealDto> list = faultRealDtoList.stream().skip(i * 10000).limit(10000).collect(Collectors.toList());
            if (!list.contains(list)) {
                faultRealMapper.insertFaultRealHistory(list);
            }
        }

mapper文件:

INSERT INTO xx (
       xx
        )
        VALUES
        <foreach collection ="list" item="item" index= "index" separator =",">
            (
            #{item.xx}
            )
        </foreach >
    </insert>

删除和新增的实现原理一样，都是分批处理，在大数据量下，分批处理比直接处理效率要好的多。

ok，这里整个过程就完成了。

|本期文章就到这里了，我是梦辰，可以微信搜一搜「 梦辰的架构笔记 」公众号,保证是干货!!!欢迎大家和我交流。|
|----------------------------------------------------------|–|