MySQL DELETE 操作后没有释放磁盘空间

最近遇到一个问题，项目对应的的数据库频繁报警，报警内容为数据库磁盘空间达到85%。后来经过查看发现数据库中有20多个表数据量特别大，每张表大约3亿数据，共70亿左右数据（存储的内容为坐标记录整体来说不太重要），后续经过排查发现程序在记录的时候有逻辑漏洞导致有很多重复数据，后来经过沟通决定通过脚本来删除重复数据。经过漫长的删除。。。。。。原以为占用的磁盘空间就可以下降，但是万万没有想到磁盘空间一点没有降低，还是频频报警。我很不理解，难道执行delete脚本不释放磁盘空间吗？后续经过查询发现还真不释放。

当 MySQL 数据库服务删除部分数据后，有些情况下这些数据占用的存储空间会释放掉，有些情况这些存储空间则不会释放。以下是对这种情况的简单说明：

一、删除数据不释放内存

delete from table_name

原因：

使用delete删除的时候，MySQL并没有把数据文件删除，只会将已经删除的数据标记为删除，因此并不会彻底的释放空间。这些被删除的数据会被保存在一个链接清单中，当有新数据写入的时候，MySQL会利用这些已删除的空间再写入。删除操作会带来一些数据碎片，正是这些碎片在占用硬盘空间。这些被标记为删除的记录，就是数据空洞。不仅浪费空间，还影响查询效率。

影响：

1. mysql底层是以数据页为单位来存储和读取数据的，每次向磁盘读一次数据就是读一个数据页，每访问一个数据页就对应一次IO操作，磁盘IO访问速度是很慢的。
2. 如果一个表上存在大量的数据空洞，原本只需要一个数据页就保存的数据，由于被很多空洞占用了空间。不得不增加其它数据页来保存数据，相应的mysql在查询相同数据的时候，就不得不增加磁盘IO操作，从而影响查询速度。
3. 不仅删除会造成数据空洞，插入和更新同样会造成数据空洞。因此一个表在经过大量频繁的增删改后，难免会产生数据空洞，影响查询效率。在生产环境中直接表现为原本查询很快的表变的越来越慢。

为什么这么设计

1、这样的设计是因为：如果在磁盘上移除之后，很多其它的记录需要在磁盘上重新排列，这会消耗大量的性能。(例如：一个大表，存在索引，删除了其中一行，那么整个索引结构就会发生变化，随之而来的改变索引结构，必将带来磁盘IO)。

二、删除数据释放内存

drop table table_name;
truncate table table_name;

optimize table命令可以将表中数据重新排列，使得查询效率更加高效。此外这个命令还可以释放表中的碎片空间，从而节省磁盘空间。删除表操作和清空数据表操作都会释放空间。

三、优化表空间

1、第一种方法

optimize table table_name ;

OPTIMIZE 适用于InnoDB和MyISAM存储引擎。 使用OPTIMIZE TABLE来重新利用未使用的空间，并重新整理数据文件的碎片。

好处：减少表数据与表索引的物理空间，降低访问表时的IO。整理之前，取数据需要跨越很多碎片空间，这时需要时间的，整理后，想要的数据都放在一起了，直接拿就拿到了，效率提高。

2、第二种方法

alter table tab_name  ENGINE = 'InnoDB';

注意：该操作执行的时候会把该表格先写入一个tmp临时表，所以磁盘剩余空间必须大于表空间，否则会执行失败。

3、第三种方法（生产环境不建议使用）

先将整个表的结构和数据导出来，删除整个表，再创建表，最后将旧表数据重新导入新表中。

总结：

1.不要经常进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可。
2.OPTIMIZE TABLE只对MyISAM，BDB和InnoDB表起作用，尤其是MyISAM表的作用最为明显。此外，并不是所有表都需要进行碎片整理，一般只需要对包含上述可变长度的文本数据类型的表进行整理即可。
3.在OPTIMIZE TABLE运行过程中，5.7.4以前的版本MySQL会锁定表。