1 碎片是怎么产生的及查看

在MySQL中.经常update,delete的表就会产生碎片.怎么去确定表中有没有碎片呢?

select  TABLE_SCHEMA,
       TABLE_NAME TABLE_NAME,
                  CONCAT(ROUND(data_length / (1024 * 1024), 2),'M') data_length,
                  CONCAT(ROUND(index_length / (1024 * 1024), 2),'M') index_length,
                  CONCAT(ROUND(ROUND(data_length + index_length) / (1024 * 1024),2),'M') total_size,
                  CONCAT(ROUND(data_free / (1024 * 1024), 2),'M') datafree,
                  engine
FROM INFORMATION_SCHEMA.TABLES
where table_schema='xxx' and table_name='xxx';
其中data_free显示表中空洞的空间.有可能是表碎片,也有可能是未利用到的空间

2 碎片整理的方法

碎片整理有三种方法:

1 alter table xxx engine=innodb; 结合analyze table xx;使用
2 optimize table xxx;
3 将表中数据select出来再插入新表

注意:执行以上操作的时候要避开业务高峰期.不要经常去整理碎片.周期半年左右就可以

3 data_free介绍

information_schema.tables中的data_free这一列.并不能真实的反应空间碎片
要真正对比表碎片的大小.需要用上边语句中的total_size和磁盘上表文件的差值
然后和data_free比较.就可以真实的得出表空间碎片的大小了.

4 扩展介绍

在日常的运维过程中.我们需要实时注意数据库内大表的变化.以下是获取数据库中大表的语句.

SELECT TABLE_SCHEMA,
       TABLE_NAME TABLE_NAME,
                  CONCAT(ROUND(data_length / (1024 * 1024), 2),'M') data_length,
                  CONCAT(ROUND(index_length / (1024 * 1024), 2),'M') index_length,
                  CONCAT(ROUND(ROUND(data_length + index_length) / (1024 * 1024),2),'M') total_size,
                  CONCAT(ROUND(data_free / (1024 * 1024), 2),'M') datafree,
                  engine
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' ,
                           'performance_schema',
                           'sys',
                           'mysql')
ORDER BY (data_length + index_length ) DESC LIMIT 20;