历史数据迁移到表中增加查询效率的思路及思路

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

想法

1.先备份你的数据（记住这一点非常重要，非常重要！）

2、删除原表15天前的所有数据

3、每天定时运行任务，备份15天前的数据并删除（每天晚上运行，保持数据量在百万级以内，以降低难度）

问题

1、需要在不停止当前业务的情况下进行数据迁移。

2、同时删除故障表中不包含当前时间15天内数据的数据。

3、由于数据量在千万级，以上操作时必须考虑性能，不能影响其他业务的正常使用。

实现方式1.备份数据并删除原表废弃数据

其实我这里备份数据的时候遇到了很多问题，因为数据库是阿里云的，我无法直接备份阿里云上的数据（其实在云上备份更快），而且没有服务器可言，只能通过其他工具备份。

1.首先，使用备份数据。 如果直接在这里导出的话会很慢。 我已经尝试了几次，但没有成功。

2.使用的工具，数据传输，比导出SQL效率高，但是导入的数据会少很多。 我这里测试了好几次，发现每次的数据都不一样。

然后用之前一直用的，直接右键表（或数据库），选择复制表，不到半小时就备份了2000万条数据。 虽然可能还是很慢，但是相比上面，还是好很多了，至少它导出的数据是正确的。

数据备份后，需要删除原表中的数据。 上面提到原表中只保留15天内的数据，但是15天之前的数据量太大（超过1000万条），所以不要使用from table where time < xxx

这种方式直接删除，因为语句是逐行执行的，所以会每行生成一条log日志，效率极低，尤其是数据量较大时，可能会导致死锁。

另一种方法是先备份15天内的数据，使用表，然后导入刚刚备份的数据。 它和数据的区别在于，它是物理直接删除和逻辑删除。 我们都知道，删除之后，并不会直接在物理空间中删除。 MySQL会定期开启一个线程来删除数据。 这种方式适合当前业务终止的情况，因为需要直接删除表，然后重新导入数据。 这不适合当前的需求。

有两种方法。 一是每天按照时间写SQL删除数据，因为每天的数据量在50万以内，索引执行时间也不是很长。 另一种是写一个程序，循环几天，然后删除。 是的，不管怎样，主要思想就是把大数据变小，分而治之。 因为时间有限，我们就直接写SQL，根据时间删除。

2.计划任务（备份数据、删除旧数据）

这里我们使用的是公司打包的一个定时项目，每天晚上12点运行。 说一下这里遇到的问题。

因为我之前只保留了15天内的数据，所以只查询了15天之前的数据，然后直接读入程序中，直接oom，主要是因为在这个过程中，会不断的创建其他对象，解决方案如下:

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

底层仍然调用jdbc。 如果不设置，默认是查询并返回全部50万条数据，方便oom。 这里设置为每次查询从jdbc结果集中获取1000条数据，然后查看批量数据。

获取数据后，备份到历史表中。 这里同样的问题。 如果直接插入50万条数据，会报OOM。 原因是解析SQL时会产生大对象。

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

可以看到，申请内存时，堆内存溢出了。

改进方法，批量添加：

//查询数据
        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);
            }
        }

文档：

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

删除和添加的实现原理是一样的，都是批处理。 当数据量很大时，批处理比直接处理效率高得多。

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

|这就是本文的内容。 我是孟辰。 您可以微信搜索“梦辰建筑笔记”公众号。 保证内容丰富！！！ 欢迎大家与我交流。 |

|------------------------------------------------ ----------|–|