mysql delete表数据后,如何解决空间大小不变的问题?

版权声明:工作和生活的点点滴滴都应该记录下来! https://blog.csdn.net/u011350541/article/details/78181782

背景:
因为每天都有数个手机数据的定时任务,且每天收集的数据中存在重复数据的问题,数据库的某张表空间越来越大。整个数据库实例的空间是30GB,一张表就占了22GB,导致数据的查询越来越慢,因此决定删除重复数据。
实施:
删重复数据采用的方法是直接delete where,总数据量200W+行,删完后大概还有70W行,搞完直接回家睡大觉
突然:
才删了过了3天,突然收到告警,说数据库的表空间使用已经超过90%,急需扩容或优化。当时就蒙了,不是才删掉100W行的数据吗?为什么这么快就有告警了
排查:
跑到目标机器数据库目录下ls看了下表大小,果然依然是22GB,还往上涨了点,接着select一下数据行数,确实只有70W行,为什么会这样?百度后才知道:

  1. delete from table_name where 条件删除数据后,数据表占用的空间大小不会变。
  2. 不跟条件直接delete的时候。如:delete from table_name清除了数据,同时数据表的空间也会变为0。

这是因为删除操作后在数据文件中留下碎片所致。DELETE只是将数据标识位删除,并没有整理数据文件,当插入新数据后,会再次使用这些被置为删除标识的记录空间

这个时候我们该使用 OPTIMIZE TABLE 指令对表进行优化了。
命令语法:OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] …
最简单的:optimize table phpernote_article;

在OPTIMIZE TABLE运行过程中,MySQL会锁定表。因此,这个操作一定要在网站访问量较少的时间段进行。

但是我的这种情况并不适用optimize,因为执行这一操作时需要先备份表再整理碎片,而我剩下的空间并不能完成表的备份,因此无法完成optimize。

解决:
最后的解决方案是先dump表,再source进去,这样也不会有碎片产生,同时不需要更多的表空间进行备份。

最后介绍两个命令:
1.DELETE
 ・DML语言
 ・可以回退
 ・可以有条件的删除
DELETE FROM 表名
  WHERE 条件
2.TRUNCATE TABLE(删除表内所有数据仅保存表结构,且不会有碎片残留)
 ・DDL语言
 ・无法回退
 ・默认所有的表内容都删除
 ・删除速度比delete快。
TRUNCATE TABLE 表名

展开阅读全文

没有更多推荐了,返回首页