优化MySQL myisam、innodb表碎片

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

 

InnoDB 使用的 Clustered Index,索引和数据绑定在一起,重排序是不现实的.所以不支持 MyISAM 式的 OPTIMIZE,而是绑定到了ALTER TABLE 命令上面.可以通过执行以下语句来整理碎片,提高索引速度:

ALTER TABLE table_name ENGINE = Innodb;

这其实是一个NULL操作,表面上看什么也不做,实际上重新整理碎片了.当执行优化操作时,实际执行的是一个空的 ALTER 命令,但是这个命令也会起到优化的作用,它会重建整个表,删掉未使用的空白空间.

 

什么是mysql碎片?怎样知道表的碎片有多大呢?

 

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

 

数据碎片分为两种,一种是单行数据碎片,另一种是多行数据碎片.

 

前者的意思就是一行数据,被分成N个片段,存储在N个位置.后者的就是多行数据并未按照逻辑上的顺序排列.

 

当有大量的删除和插入操作时,必然会产生很多未使用的空白空间,这些空间就是多出来的额外空间.索引也是文件数据,所以也会产生索引碎片,理由同上,大概就是顺序紊乱的问题.

 

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

 

OPTIMIZE 操作会暂时锁住表,而且数据量越大,耗费的时间也越长,它毕竟不是简单查询操作.所以把 Optimize 命令放在程序中是不妥当的,不管设置的命中率多低,当访问量增大的时候,整体命中率也会上升,这样肯定会对程序的运行效率造成很大影响.比较好的方式就是做个shell,定期检查mysql中 `information_schema`.`TABLES`字段,查看 DATA_FREE 字段,大于0话,就表示有碎片.脚本多长时间运行一次,可以根据实际情况来定,比如每周跑一次.

 

CHECK TABLE 或 REPAIR TABLE,检查或维护 MyISAM 表
OPTIMIZE TABLE,优化 MyISAM 表
ANALYZE TABLE,分析 MyISAM 表