这是学习笔记的第 1852篇文章今天接到一位开发同学的数据操作需求,需求看似很简单,需要执行下面的SQL语句:,我来为大家科普一下关于mysql清理数据避免空间碎片的办法?下面希望有你要的答案,我们一起来看看吧!

mysql清理数据避免空间碎片的办法(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秒,亿级数据的切换,整体来说效果还是很明显的,也推荐大家在工作中根据适合的场景来应用。

,