MyISAM、INNODB类型表碎片优化

MyISAM表类型采用 OPTIMIZE TABLE table_name SQL语句清理碎片。

InnoDB 使用的 Clustered Index,索引和数据绑定在一起，重排序是不现实的，所以不支持 MyISAM 式的 OPTIMIZE，而是绑定到了ALTER TABLE 命令上面。

可以通过执行以下语句来整理碎片,提高索引速度: ALTER TABLE table_name ENGINE = Innodb;  

这其实是一个NULL操作,表面上看什么也不做,实际上重新整理碎片了.

当执行优化操作时,实际执行的是一个空的 ALTER 命令,但是这个命令也会起到优化的作用,它会重建整个表,删掉未使用的空白空间。

如果针对INNODB的表做 OPTIMIZE TABLE 的操作，系统将返回：Table does not support optimize, doing recreate + analyze instead

什么是mysql碎片? 

简单的说,删除数据必然会在数据文件中造成不连续的空白空间,而当插入数据时,这些空白空间则会被利用起来.于是造成了数据的存储位置不连续,以及物理存储顺序与理论上的排序顺序不同,这种是数据碎片。

实际上数据碎片分为两种,一种是单行数据碎片,另一种是多行数据碎片.前者的意思就是一行数据,被分成N个片段,存储在N个位置.后者的就是多行数据并未按照逻辑上的顺序排列.当有大量的删除和插入操作时,必然会产生很多未使用的空白空间,这些空间就是多出来的额外空间.索引也是文件数据,所以也会产生索引碎片,理由同上,大概就是顺序紊乱的问题.

如何对碎片优化？

Engine 不同,OPTIMIZE 的操作也不一样的,MyISAM 因为索引和数据是分开的,所以 OPTIMIZE 可以整理数据文件,并重排索引. 

OPTIMIZE 操作会暂时锁住表,而且数据量越大,耗费的时间也越长,它毕竟不是简单查询操作.所以把 Optimize 命令放在程序中是不妥当的,不管设置的命中率多低,当访问量增大的时候,整体命中率也会上升,这样肯定会对程序的运行效率造成很大影响。

比较好的方式就是做个脚本Script,定期检查mysql中 `information_schema`.`TABLES`字段,查看 DATA_FREE 字段,大于0话,就表示有碎片.脚本多长时间运行一次,可以根据实际情况来定,比如每周跑一次.