MySQL删除表中大量数据后,表空间大小不变的解决方案。

01-14 11:23 字数 1011 阅读 809 已编辑

有一个表有5000万+的数据,其中很多数据没有用了,想着通过删除无用的数据来达到节省表空间的目的,删了几百万条后发现,表空间大小并不会改变。

原因

我的删除命令如下

delete from tb where condition = 'a' order by id limit 1000;

循环执行这个命令删除无用数据。

删除之后查看一下表状态

show table status from db_name like 'tb';

结果如下

table staus

可以看到Data_free的值很大,这个字段包含了那些被我们delete命令“删除”的记录占用的空间大小。

delete命令并不会把记录删除,他只会把记录头信息里的delete_mask标识改为1,这些被“删除”的记录之所以不立即从磁盘上移除,是因为移除它们之后把其他的记录在磁盘上重新排列需要性能消耗,所以只是打一个删除标记而已,所有“被删除掉”的记录都会组成一个所谓的垃圾链表,在这个链表中的记录占用的空间称之为所谓的可重用空间,之后如果有新记录插入到表中的话,可能把这些被删除的记录占用的存储空间覆盖掉。

比如一个页里有ID为 1,2,3,4,5,6,7,8,9,10 的几条记录,我删除掉了 ID为 2368 的记录,如果我后续选择insert into tb id 为 2368 的记录,这些位置还会被复用。

但是我的ID是递增的,这样岂不是这些记录占用的位置永远都不会消失?那我删除了个啥?删除了个寂寞!怎么把这些记录的位置清理掉呢?

解决

为了把表中的空隙去掉,这时就可以采用重新建一个与表 A 结构相同的表 B,然后按照主键 ID 递增的顺序,把数据依次插入到 B 表中。

由于是顺序插入,自然 B 表的空隙不存在,数据页的利用率也更高。之后用表 B 代替表 A,好像起到了收缩表 A 空间的作用

通过搜索找到三种方案。

1. optimize table tb;

只是此命令会锁表,想通过此命令优化表结构,只能在访问量小的时候,或者停机的时候的使用。

2.alter table tb engine=innodb;

这个命令就比较厉害了,相当于新建临时表、把旧表插到临时表里、交换表名、删除旧表,并且不锁表还不影响原表的增删改,比较推荐,当然在操作的时候,还是推荐在业务低峰期的时候。以免发生意外。

3.dump把数据导出,然后再把数据导入。

没测试。

0人点赞>
关注 收藏 改进 举报
0 条评论
排序方式 时间 投票
快来抢占一楼吧
请登录后发表评论
站长 @ 十七度
文章
368
粉丝
23
喜欢
181
收藏
29
排名 : 1
访问 : 97.19万
私信