如何优雅的实现DML批量操作

这是学习笔记的第 2105 篇文章

昨天处理了一个业务同学的数据需求，简单来说就是对一张大表做一下数据清理，数据量在8千万左右，需要保留近一个月的数据，大概是400万左右。

对于数据的删除处理，尤其是大表的处理，可以借助MySQL特有的一种处理策略，可以参考之前的文章:

MySQL平滑删除数据的小技巧

从处理方式来看，基本就是做了rename,把原来表的数据转置到一个中间库里面，然后补录数据，对于日志型的数据表来说是很有必要的。

但是这种方式涉及几个细节，主要的出发点就是怎么样让这个操作更加可控，我所说的意思是整个处理过程你可以按部就班的操作，该备份备份，而补录补录，而对线上的切换过程都是毫秒级完成，几乎产生不了直接影响，要实现这个看似不大可能的需求，我们就需要设定几个边界：

1）数据类型为流水型业务，不涉及事务处理

2）数据流程不会修改历史数据，仅仅参考近N(可以为N,也可以为当天）的数据

3）操作的时机不是业务高峰期

4）能够接受秒级的数据写入闪断

明确了这些不能够之后，我们来看看怎么来实现这个目标，可以参考如下的流程图：

所以要实现这个目标，我们需要尽可能保证中转表的数据要尽可能完整，而且要保证数据切换能够高效完成。

那么我们处理的思路就是增量迭代，即最后的切换阶段耗时最短，我们可以提前复制需要补录的数据，同时对当天的数据进行增量的补录，然后开始切换。

我们可以参考如下的步骤：

1）首先创建同样结构的表，包括索引,一个表是做备份，一个是作为中转。

mysql> create table cmec_log_arch.log like cmec_log.log;

mysql> create table cmec_log_arch.log_new like cmec_log.log;

2）需要把近一个月的数据表现存放到中转表log_new里面，为了提高效率，我们先保证当天的数据有效。

mysql> insert into cmec_log_arch.log_new select * from cmec_log.log where cdate between '2019-09-18' and '2019-09-18 10:00:00'; --当前时间大于10:00:00，但是间隔不大

我们可以缩小时间间隔，完成增量数据的补录，直到增量数据的补录时长控制在秒级（数据集越小，处理时长越短）

2）然后切换表

mysql> RENAME TABLE cmec_log.log TO cmec_log_arch.log_bak,

cmec_log_arch.log TO cmec_log.log,

cmec_log_arch.log_bak TO cmec_log_arch.log;

Query OK, 0 rows affected (0.18 sec)

整个切换过程是很快的。

4）接着我们进行历史数据的补录，完成了当天数据的补录，我们只需要关注历史数据的范围即可。

为了尽可能降低对岸上环境的应县个，我们需要缩小补录的时间范围，比如按照如下的方式来进行补录：insert into cmec_log.log select * from cmec_log_arch.log_new where cdate between '2019-09-16' and '2019-09-17';

因为结果集相对小一些，处理过程对已有的数据处理线程的效率影响最小，可以避免大结果集导致服务阻塞的情况。

当然关键的部分是整个流程梳理完善后固定下来，我们可以把它转换成一个脚本，这样后续的操作我们只需要输入表名，保留的时间范围即可完成这个看起来略微复杂的需求了。

个人新书 《MySQL DBA工作笔记》