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云平台推荐
随便看看
- 2021-03-20Docker应用程序数据管理与持久化
- 2021-01-27数据库宕机以后恢复的过程?如何保证事务的ACID特性?
- 2021-07-10MySQL基于Percona XtraBackup 实现全备&增量备份与恢复
- 2021-09-09开源Wazuh安全平台容器化部署
- 2021-09-27MongoDB数据导入导出工具详解