insert into select 迁移数据,愚蠢的行为

尘世中-迷途小书童

于 2024-06-03 10:24:29 发布

阅读量649

点赞数 5

文章标签： java mysql

本文链接：https://blog.csdn.net/LINgZone2/article/details/139407854

版权

在高并发环境下，使用`insert into select`进行数据迁移时，全表扫描可能导致长时间锁表，引发支付流水丢失问题。在测试环境中未暴露此问题，因缺少模拟真实业务场景。解决方案是为where条件创建索引，避免全表扫描。

摘要由CSDN通过智能技术生成

事情的起因

公司的交易量比较大，使用的数据库是mysql，每天的增量差不多在百万左右，公司并没有分库分表，所以想维持这个表的性能只能考虑做数据迁移。

同事李某接到了这个任务，于是他想出了这两个方案

先通过程序查询出来，然后插入历史表，再删除原表
使用insert into select让数据库IO来完成所有操作

第一个方案使用的时候发现一次性全部加载，系统直接就OOM了，但是分批次做就过多io和时间长，于是选用了第二种方案，测试的时候没有任何问题，开开心心上线，然后被开除。

到底发生了啥，我们复盘一下

先来看第一个方案，先看伪代码

// 1、查询对应需要迁移的数据
List<Object> list = selectData();

// 2、将数据插入历史表
insertData(list);

// 3、删除原表数据
deleteByIds(ids);

我们可以从这段代码中看到，OOM的原因很简单，我们直接将数据全部加载内存，内存不爆才怪。

再来看看第二个方案，到底发生了啥。

为了维持表的性能，同时保留有效数据，经过商量定了一个量，保留10天的数据，差不多要在表里面保留1kw的数据。所以同事就做了一个时间筛选的操作，直接insert into select ... dateTime < (Ten days ago)，爽极了，直接就避免了要去分页查询数据，这样就不存在OOM啦。还简化了很多的代码操作，减少了网