MySQL删除数据空间没有释放-碎片

一、现象描述

我们在做数据库运维的时候，经常会发现数据库批量删除数据之后，磁盘空间并没有立即释放或者说没有丝毫变化的场景。接下来我们就针对INNODB和MyISAM两款存储引擎分析一下。

INNODB存储引擎官网介绍：https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html

MySIAM存储引擎官网介绍：https://dev.mysql.com/doc/refman/5.7/en/myisam-storage-engine.html

二、原因分析

2.1 若用的delete from table_name 删除的表的全部数据，myisam会立刻释放磁盘空间，innodb不会释放磁盘空间。

2.2 若用的delete from table_name where 这种带条件的删除表部分数据，不管innodb还是myisam都不会释放磁盘空间。

2.3 drop table table_name 和truncate talbe table_name，都会立刻释放磁盘空间。

2.4 若希望 delete 删除操作后释放磁盘空间：

（1）针对MySIAM引擎可以执行delete后执行optimize table table_name 操作，optimize只对myisam、innodb、bdb引擎表生效，innodb执行的时候可能会提示“Table does not support optimize, doing recreate + analyze instead”。

MySIAM引擎碎片整理原理：https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html OPTIMIZE TABLE它会重组表数据和索引的物理页，对于减少所占空间和在访问表时优化IO有效果。OPTIMIZE 操作会暂时锁住表，而且数据量越大，耗费的时间也越长。

（2）针对INNODB引擎可以执行alter table table_name engine=innodb释放磁盘空间，期间都会锁表，建议业务低谷或者线下操作。

INNODB引擎碎片整理原理：https://dev.mysql.com/doc/refman/5.7/en/alter-table.html ALTER TABLE这其实是一个NULL操作，表面上看什么也不做，实际执行的是一个空的 ALTER 命令，但是这个命令也会起到优化的作用，它会重建整个表，删掉未使用的空白空间，起到碎片整理的作用。

特别注意：在进行ALTER碎片整理时，建议留出整理表的两倍空间，例如：待碎片整理表是100G，那么建议留出200G的空间，用于碎片整理，防止表重建过程中，将磁盘打满，影响业务。

2.5 注意事项

（1）DELETE 删除操作后虽然未释放磁盘空间，但是下次再插入数据的时候，仍然可以使用这部分空间，不用担心未释放就不会再重复使用此磁盘空间了。重启不会释放磁盘空间。

（2）INNODB执行数据的修改操作，例如删除一行数据时，表面看到是数据库返回删除成功底层上数据只是标记删除，并没有从索引和数据文件中真实删除，所以占据的空间也没有释放。innodb 后台的purge_thread 定期清理已经删除的索引和文件。但是并不会回收这些空间，而是后面有新数据来填充空洞。

（3）如果A表删除数据没有通过4的方式进行空间释放，那么A表的空洞只能由A表新的insert数据填充，B表无法占用A表的空洞空间。原因是A表的表空间物理文件没有变小，所占的实际物理空间没变！！！

三、INNODB碎片产生原因详解

InnoDB表的数据存储在页（page）中，每个页可以存放多条记录。这些记录以树形结构组织，这颗树称为B+树索引。表中数据和辅助索引都是使用B+树结构。维护表中所有数据的这颗B+树索引称为聚簇索引，通过主键来组织的。聚簇索引的叶子节点包含行中所有字段的值，辅助索引的叶子节点包含索引列和主键列。

在InnoDB中，删除一些行，这些行只是被标记为“已删除”，而不是真的从索引中物理删除了，因而空间也没有真的被释放回收。InnoDB的Purge线程会异步的来清理这些没用的索引键和行。但是依然没有把这些释放出来的空间还给操作系统重新使用，因而会导致页面中存在很多空洞。如果表结构中包含动态长度字段，那么这些空洞甚至可能不能被InnoDB重新用来存新的行，因为空间空间长度不足。关于这个你可以参考博客Overview of fragmented MySQL InnoDB tables。

另外，删除数据就会导致页（page）中出现空白空间，大量随机的DELETE操作，必然会在数据文件中造成不连续的空白空间。而当插入数据时,这些空白空间则会被利用起来。于是造成了数据的存储位置不连续。物理存储顺序与逻辑上的排序顺序不同,这种就是数据碎片。

对于大量的UPDATE，也会产生文件碎片化 ， Innodb的最小物理存储分配单位是页(page)，而UPDATE也可能导致页分裂（page split），频繁的页分裂，页会变得稀疏，并且被不规则的填充，所以最终数据会有碎片。

作者：UStarGao
链接：https://www.starcto.com/mysql/127.html
来源：STARCTO
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

UCloud新用户专属注册连接

UCloud CDN超值特惠专场

UCloud全球云主机（UHost/VPS）大促页面

UCloud快杰云主机大促页面