mysql平滑访问_MySQL平滑删除数据的小技巧

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

今天接到一位开发同学的数据操作需求，需求看似很简单，需要执行下面的SQL语句：

delete from test_track_log where log_time < '2019-1-7 00:00:00';

看需求描述是因为查询统计较差，希望删除一些历史数据。

带着疑问我看下了表结构：

CREATE TABLE `test_track_log` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',

`uid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '用户ID',

...

`log_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录时间',

PRIMARY KEY (`id`),

KEY `idx_uid_fsm_log` (`uid`,`fsm_id`,`log_time`)

) ENGINE=InnoDB AUTO_INCREMENT=125082604DEFAULT CHARSET=utf8 COMMENT='记录测试账号的任务轨迹'

看自增列的情况，这个表的数据量有近1亿条记录了，暂且不说数据量带来的额外影响，单说这个需求，你会发现这是一个陨石坑。

简单验证了下，数据量确实在亿级别。

select count(id) from  tgp_db.tgp_track_log

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

| 125082603 |

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

1 row in set (1 min 26.63 sec)

如果老老实实执行了，估计我下午就不用干别的了。

显然这个需求是一个模糊需求，业务方希望清理数据，但是实现方式缺不合理。

如果我们使用truncate的操作，这样看来目前是比较合适的。

同时在做数据清理的时候，势必要考虑备份数据，而和业务方确认，数据可以不用备份，但是从数据库层面来说，是需要的。

在操作前进行细致的沟通，发现业务方还是会希望参考近些天来的数据，尤其是当天的数据，所以这个操作还是需要谨慎。

这里有两个坑：

第一是业务方再三确认不需要备份，但是如果删除了数据之后，发生了意料之外的故障，需要恢复数据，而DBA没法恢复，那么这个锅我们背不住。

第二是业务方再三确认删除的逻辑是正确的，但是他们不负责数据操作的性能问题，我们如果不去审核而为了执行而执行，那么造成性能故障之后，很容易造成需求的分歧。

所以这件事情的本质很简单，清理数据，对业务影响最小，保留指定范围的数据。

这种情况下单纯的DML语句是搞不定了，我们需要想一些办法，这里有一个技巧，也是我非常喜欢MySQL的一个亮点特性，即MySQL可以很轻松的把一个库的表迁移到另外一个数据库，这种操作的代价就好像把一个文件从文件夹1拷贝到文件夹2。

一个初版的实现如下：

create table test_db.test_track_log_tmp like test_db.test_track_log;

alter table test_db.test_track_log rename to test_db_arch.test_track_log;

alter table test_db.test_track_log_tmp rename to test_db.test_track_log;

这种操作看起来很简单，但是也存在一些问题，一个是在切换的过程中，如果写入数据是会丢失数据的，即数据已经入库，这里通过rename丢失数据。

第二个是这个操作不够简洁。怎么改进呢，我们可以把rename的操作玩得更溜。

mysql> create table test_db_arch.test_track_log like test.test_track_log;

mysql> RENAME TABLE test.test_track_log    TO test_db_arch.test_track_log_bak,

test_db_arch.test_track_log    TO test.test_track_log,

test_db_arch.test_track_log_bak TO test_db_arch.test_track_log;

Query OK, 0 rows affected (0.02 sec)

整个过程持续0.02秒，亿级数据的切换，整体来说效果还是很明显的，也推荐大家在工作中根据适合的场景来应用。