mysql碎片空洞问题

碎片空洞问题
数据文件空洞的问题，主要关注innodb引擎，innodb在执行数据的修改操作，例如删除一行数据时，表面上看到的是数据库返回删除成功，但在底层并非如此，实际上这行数据在物理上只是被标记为删除，并不会从索引和数据文件中真实删除，所以，它所占据的空间也没有真正释放，在innodb后台有个purge thread，会定期去清理这些已经删除的缩影和文件，即便如此，innodb也不会回收这些已经删除的空间给操作系统，这就会在数据文件中存在很多空洞，这些空洞很可能会一直存在，并且越来越大。

 

（1）查看某个表的碎片大小

结果中’Data_free’列的值就是碎片大小。

（2）列出所有已经产生碎片的表

mysql> select table_schema db, table_name, data_free, engine     from information_schema.tables where table_schema not in ('information_schema', 'mysql')  and data_free > 0;

 

清除表碎片：

• MyISAM表

mysql> optimize table 表名

• InnoDB表

mysql> alter table 表名 engine=InnoDB

 

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

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

建议：

清除碎片操作会暂时锁表，数据量越大，耗费的时间越长，可以做个脚本，定期在访问低谷时间执行，例如每周三凌晨，检查DATA_FREE字段，大于自己认为的警戒值的话，就清理一次。