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;