这是学习笔记的第 1852篇文章今天接到一位开发同学的数据操作需求，需求看似很简单，需要执行下面的SQL语句：，我来为大家科普一下关于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=125082604 DEFAULT 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秒，亿级数据的切换，整体来说效果还是很明显的，也推荐大家在工作中根据适合的场景来应用。
,
