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 表。

此操作会锁表，时间长短依据表数据量的大小。

另外我们也可以通过重建表的存储引擎来重组数据和索引的存储，从而达到清理碎片的目的。

语句如下：

alter table table_name engine = innodb;