MySQL数据清理有技巧，这么破

jeanron100

发布于 2019-12-02 13:21:39

1.3K0

发布于 2019-12-02 13:21:39

很多同学总是抱怨说自己的工作没有技术难度，没有含金量。我这里想提到一点就是精细化管理，如果你能够把自己管理的环境像打磨一件作品一样，知道它的业务特点和瓶颈，知道它的性能细节，也知道如何进行后续的改进和优化，那么你的管理工作就上升了一个层次。

比如一个对数据表做清理的操作，可能看起来就是做些delete操作，有什么好的办法和技巧呢。

这个环境是一个线上业务，因为各种原因，表中的数据臃肿不堪，设计上没有做冷热分离，导致大表的数据有100多G,数据量都是亿级别，和业务同学确认的情况，对数据使用拆分为了在线数据和历史数据，历史数据会不断的推入大数据中进行后续计算，而线上数据需要保留一定时间范围的数据即可。

所以这个需求不是简单的数据删除，而且需要保留一定的时间范围的数据。

当然这个过程中也会有大量的问题和细节需要确认，其中一个关键的问题是：

是否有线上维护窗口，以便集中进行数据清理。

业务同学也比较配合，不过还是略带犹豫，他们反馈说维护倒是可以，不过时间不能太长，而且只能在凌晨的一个时间点。

我这人一来懒，而来年龄也大了，不想起那么早执行这种维护管理工作，一种实现策略就是写个脚本定时执行，这种方案基本可行，唯一的难点就是我们对于执行过程的进度完全不可控，这些表的数据量都很大，看似简单的清理工作不光产生大量的日志，而且如果一旦阻塞业务是完全无法评估影响时长的，而且从心理上考虑，那天要维护，比如5:00，我势必得在那个时间范围内醒来，然后观察进度，但是除此之外一旦有问题，我可做的事情就很少了。

所以我的目标是：在正常时间进行数据清理工作，而且对于业务无感知。

本着这个目标，我就得更加细致和认真一些，比如表test需要保留多长时间的数据，存在哪些时间字段，我们的清理和补录是基于哪些时间字段，我整理了下表：

可以看到整个清理的工作量还是比较大的。

怎么样能够实现业务无感知呢，我的理解是要明确业务边界，比如一个表test1,它里面的数据是按照时间写入的，比如保留15天的数据，那么前面15天的数据是否和业务流程是紧密耦合的，还是只依赖于当天的数据，和业务同学沟通确认的情况比较理想，业务侧只会关注当天的数据，而后续的计算任务会在另外一个时段进行，也就意味着我们的数据清理和数据补录工作可以改造为一种异步模式。

我们需要构造另外一个影子库，在这个影子库下面创建同名的表，然后通过rename的方式进行切换，为了保证这个过程的完整性和连续性，整个rename的过程应该是一气呵成的。

创建一个影子表：

create table test_db_arch.test like test_db.test;

原表和影子表切换：

 RENAME TABLE test_db.test TO test_db_arch.test_bak,
                  test_db_arch.test TO test.test,
                  test_db_arch.test_bak TO test_db_arch.test;

这个操作其实就跟对一个文件进行重命名一样，代价是很低的。

这里是关键，但是本质上不是难点，整个操作在毫秒级即可完成。

难点1：数据补录切忌一刀切

假设表结构为：

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `levelId` int(11) NOT NULL,
  `clientTime` int(11) unsigned NOT NULL,
  `serverTime` int(11) unsigned NOT NULL,
  `ip` int(11) unsigned NOT NULL,
  `clientStartTime` int(11) unsigned NOT NULL,
  `networkState` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `userId_clientTime` (`userId`,`clientTime`)
) ENGINE=InnoDB AUTO_INCREMENT=xxxx DEFAULT CHARSET=utf8;
比如要补录3天内的数据，我可以写成如下的时间范围：
insert into  test_db.test
select * from    test_db_arch.test where serverTime between UNIX_TIMESTAMP('2019-11-26') and UNIX_TIMESTAMP('2019-11-29');

其实仔细看这种实现是有问题的，因为between的方式是包含和等于的关系，比如id between 1 and 2,那么可以等价于 1<=id<=2

而且一次性包含的时间范围太大，其实对于数据写入的性能压力会很大。

所以我们可以把整个语句拆分为多条基于时间维度的语句。

难点2：如何优雅的处理自增列

仔细看如下的语句，结合表结构，你会发现还是有问题。

insert into  test_db.test
select * from    test_db_arch.test where serverTime between UNIX_TIMESTAMP('2019-11-26 00:00:00') and UNIX_TIMESTAMP('2019-11-29 23:59:59');

这种模式下通过insert的方式补录数据是很可能出错的，很可能会抛出主键冲突的问题，这个问题的潜在风险就是id自增列会被重置为一个较大的值，比如之前的id自增列值为30000000，结果通过rename方式重置为1，在insert的时候会把id重新调整为30000001这样的值，实在有些浪费，所以这种模式折中的处理就是我们指定列名进行数据写入,比如下面的方式：

insert into     test_db.test(`userId`,`levelId`,`clientTime`,`serverTime`,`ip`,`clientStartTime`,`networkState`)
select `userId`,`levelId`,`clientTime`,`serverTime`,`ip`,`clientStartTime`,`networkState` from    test_db_arch.testwhere serverTime between UNIX_TIMESTAMP('2019-11-14 00:00:00') and UNIX_TIMESTAMP('2019-11-14 23:59:59');

难点3：没有索引的数据补录操作怎么破

如果我们解决了前面两个难题，对于常见问题场景应该是很容易了。这里有第3中场景比较纠结，这个表有100G数据，但是要删除的条件字段没有索引，如果加索引，这个代价太高，线上业务操作很可能产生资源的剧烈消耗，如果不加索引，执行的耗时会很长，而且很难评估。

这里有个小诀窍是我们不添加索引，但是还能够有索引的速度。

我们的数据表有自增列，那么自增列一定是按照时间的维度自增的，我们可以使用二分法来进行定位。

比如这个表的最大id值为335686576，我们可以把这个id分成几份。查看锁对应的时间范围。

select id,FROM_UNIXTIME(serverTime, "%Y-%m-%d %H:%i:%S") from user_game_levels_log where id in (335686576/2,335686576/4,335686576/8);

+-----------+------------------------------------------------+

| id | FROM_UNIXTIME(serverTime, "%Y-%m-%d %H:%i:%S") |

+-----------+------------------------------------------------+

| 41960822 | 2018-01-06 00:02:35 |

| 83921644 | 2018-02-20 11:23:10 |

| 167843288 | 2018-07-24 23:39:35 |

+-----------+------------------------------------------------+

然后不断的缩小范围

select count(*) from test_db_arch.testwhere serverTime between UNIX_TIMESTAMP('2019-11-14 00:00:00') and UNIX_TIMESTAMP('2019-11-14 23:59:59') and id between 310000000 and 310500000;

+----------+

| count(*) |

+----------+

| 0 |

+----------+

1 row in set (0.11 sec)

通过几次尝试就可以很快定位到，需要一点技巧和耐心。

而整个的数据写入根据测试，基本都是4秒内就可以完成数据写入。

执行清理之后的效果如何呢，可以看到清理的数据效果还是很不错的。