Mysql删除数据后磁盘空间未释放的解决办法【转】

转自

Mysql删除数据后，磁盘空间未释放的解决办法 - 今日头条(TouTiao.com)
http://toutiao.com/a6303087712678412546/?tt_from=mobile_qq&utm_campaign=client_share&app=news_article&utm_source=mobile_qq&iid=4710631352&utm_medium=toutiao_ios

 

起因：

公司的服务器上添加了硬盘监控，收到报警后，确认是mysql的文件占用空间比较大。于是，确认是哪个表占空间比较大后，删除了部分数据（注：数据库数据为线下分析使用，非线上数据，可以删除），但服务器硬盘空间并没有释放掉，报警仍旧存在。

原因及解决办法：

使用delete删除的时候，mysql并没有把数据文件删除，而是将数据文件的标识位删除，没有整理文件，因此不会彻底释放空间。被删除的数据将会被保存在一个链接清单中，当有新数据写入的时候，mysql会利用这些已删除的空间再写入。即，删除操作会带来一些数据碎片，正是这些碎片在占用硬盘空间。（BTW：看官方文档上好像是innodb引擎的可以利用操作系统来帮忙回收这些碎片，MyISam的表没有办法自己回收，这里待定，后续再看下）

官方推荐使用 OPTIMIZE TABLE命令来优化表，该命令会重新利用未使用的空间，并整理数据文件的碎片。

语法如下：

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

注：该命令将会整理表数据和相关的索引数据的物理存储空间，用来减少占用的磁盘空间，并提高访问表时候的IO性能。但是，具体对表产生的影响是依赖于表使用的存储引擎的。该命令对视图无效。

该命令目前只对MyISAM、InnoDB，ARCHIVE的表起作用，其余引擎的不起作用，不过可以设置，后面会讲到~~

具体的使用例子：

1、查看表占用的空间大小：

大约占用了51G的空间。

2、使用optimize命令

使用的时间比较长，需要耐心等待。

3、查看优化之后的空间占用大小。

占用空间15G左右，减少了大部分。

可见优化效果很好~

BTW：

查看表占用硬盘空间大小的SQL语句如下：（默认用M做展示单位）

SELECT TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH)/1048576, TABLE_ROWS FROM information_schema.tables WHERE TABLE_SCHEMA='dbname' AND TABLE_NAME='tablename'; 

对于InnoDB引擎的mysql， optimize 命令，将会被映射到alter table上，具体可以参见官方文档。

补充:

1、如何使optimize 支持其他引擎？

默认情况下，optimize不支持其他存储引擎，但是可以在启动mysqld的时候，使用 --skip-new 参数，这种情况下，optimize命令，将会被映射到alter table命令上，实现上述的功能。

2、该物理删除还是逻辑删除？

生产环境下，尽量不要用物理删除，一旦物理删除了，意味着数据恢复就会很麻烦。建议逻辑删除，数据仍存储在DB里。如果数据量很大的时候，可以考虑使用分库分表。但，这个仍旧是需要根据业务场景来。

3、optimize执行时会将表锁住，所以不要在高峰期使用。也不要经常使用，每月一次就足够了