MySQL|程序员应知应会之MySQL数据库的碎片整理

MySQL|程序员应知应会之MySQL数据库的碎片整理

文章图片


大家都知道Windows磁盘工具里有一个磁盘碎片整理 , 与之类似的是 , MySQL数据库里也会产生碎片 , 也需要整理 。

MySQL碎片就是指数据文件中一些不连续的空白空间 , 因为大小的原因 。 这些空白空间无法被全部利用上 , 久而久之 , 这些空白空间越来越多 , 越来越碎 , 从而就会引起物理存储和逻辑存储的位置顺序不一致的问题 , 导致了碎片的产生 。
一、数据库碎片的产生 。
MySQL在执行delete或者update操作时 , 就有可能产生碎片 。
在执行delete操作的时候 , 存储中会产生由于delete操作而留下的空白空间 , 而当有新数据插入时 , MySQL会尝试使用这些空白空间 , 但是数据又不可能完全一致 , 正好把空白覆盖满 , 于是会就有空白空间出现 。 尤其是在大量delete操作的时候 , 会出现大量的空白空间 。
在执行update的时候 , 例如在可变长度的字段 , 比如说varchar或lob , 更新的数据可能和原来的长度不一样 , 这样也会产生碎片 。 比如说原始长度是varchar(100) , 但我们更新了大量长度为50的数据 , 这样的话 , 就有50的空间成为了空白 。 对于MySQL的innodb存储引擎来说 , 表存储数据的单位是页 , 而update操作会造成页分裂 , 分裂以后存储变得不连续、不规则 , 从而产生碎片 。
二、碎片造成的问题
碎片会造成很多问题例如说空间浪费和读写性能下降 。 由于存在大量碎片 , 数据从连续规则的存储方式变为随机分散的存储方式 , 会增加磁盘IO的负担 , 于是读写性能会下降 。
三、MySQL中如何处理碎片
在MySQL中 , 可以利用
show table status like ‘%table_name%’;
语句来查看表状态 。

其中data_length是表数据大小 , index_length是表索引大小 , data_free就是碎片大小 , 当data_free大于0时 , 意味着表中有碎片 。
optimize table table_name;
语句可以清理碎片 , 但并不是所有的存储引擎都支持(程序员应知应会之MySQL的存储引擎?) , 该语句适用于InnoDB、MyISAM 和 ARCHIVE 表 。
此操作会锁表 , 时间长短依据表数据量的大小 。
另外我们也可以通过重建表的存储引擎来重组数据和索引的存储 , 从而达到清理碎片的目的 。
语句如下:
【MySQL|程序员应知应会之MySQL数据库的碎片整理】alter table table_name engine = innodb;