一、mysql 表碎片是如何产生的
简单的说,删除数据必然会在数据文件中造成不连续的空白空间,而当插入数据时,这些空白空间则会被利用起来,于是造成了数据的存储位置不连续。以及物理存储顺序与理论上的排序顺序不同,这种是数据碎片。实际上数据碎片分为两种,一种是单行数据碎片,另一种是多行数据碎片。前者的意思就是一行数据,被分成N个片段,存储在N个位置。后者的就是多行数据并未按照逻辑上的顺序排列。当有大量的删除和插入操作时,必然会产生很多未使用的空白空间,这些空间就是多出来的额外空间。索引也是文件数据,所以也会产生索引碎片,理由同上,大概就是顺序紊乱的问题。
二、mysql 表碎片清理
查看某库里面表状态(可以看是否存在碎片,查出来的数据,如果Data_free不为0,就说明此表有碎片)
mysql> show table status from ileiming\G;
1、Myisam类型表清理方法
mysql> OPTIMIZE TABLE table_name;
2、InnoDB类型表清理方法
mysql> ALTER TABLE table_name ENGINE=InnoDB;
以上是常规的命令清理碎片的方式。但本人在多年以前,因为公司需求,要把一张非常大的表清理数据,保存最近3个月的(这个表的数据最久已经保存到3年以前,大概清理的数据占整个表的80%),当时经过测试,直接了delete命令删除,效率非常低,于是我新建一张和老表数据结构一样的空表,然后select老表最近3个月的数据直接insert into到新表,因为保存的数据比删除的数据少很多,这样效率也提升了。现在想想,这样操作,同样也起到了清理碎片的作用,而且比直接删除,然后在运行表碎片清理命令效率更高,更直接。