MYSQL:空间碎片的清理方案（该方案基于独立表空间）

MySQL清除表空间碎片（对于Innodb 引擎的 共享空间：Innodb 共享空间自己试过就是清理不掉,所以对于Innodb 共享空间我的解决方法是把Innodb 修改成独立表空间）

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

碎片产生的原因 （1）表的存储会出现碎片化，每当删除了一行内容 该段空间就会变为空白、被留空，而在一段时间内的大量删除操作，会使这种留空的空间变得比存储列表内容所使用的空间更大；

（2）当执行插入操作时，MySQL会尝试使用空白空间，但如果某个空白空间一直没有被大小合适的数据占用，仍然无法将其彻底占用，就形成了碎片；

（3）当MySQL对数据进行扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写入的区域中处于峰值位置的部分；

例如：

一个表有1万行，每行10字节，会占用10万字节存储空间，执行删除操作，只留一行，实际内容只剩下10字节，但MySQL在读取时，仍看做是10万字节的表进行处理，所以，碎片越多，就会越来越影响查询性能。

查看表碎片大小

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

mysql> SHOW TABLE STATUS LIKE '表名';

结果中’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;

清除表碎片

（1）MyISAM表

mysql> optimize table 表名

（2）InnoDB表

mysql> alter table 表名 engine=InnoDB

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

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

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

转载：  http://blog.csdn.net/eagle89/article/details/78017505

//shell脚本如下:

mysql_user=root
mysql_pass=xxxx
time_log=/opt/database/time
databases=/opt/database/databases
mysql -u$mysql_user -p$mysql_pass -e "show databases" | grep -v "Database" > $databases
sed -i "s/information_schema//" $databases
sed -i "s/mysql//" $databases
sed -i "s/test//" $databases
sed -i "s/performance_schema//" $databases
databases1=$(cat /opt/database/databases)
for i in $databases1
do
echo "database $i staring"
tables=$(mysql $i -u$mysql_user -p$mysql_pass -e "show tables" | grep -v "Tables" > /opt/database/$i)
tablelist=$(cat /opt/database/$i)
echo "optimize database $i starting" >> $time_log
echo "$i start at $(date +[%Y/%m/%d/%H/%M/%S])" >> $time_log
for list in $tablelist
do
echo $list
mysql $i -u$mysql_user -p$mysql_pass -e "alter table $list engine=InnoDB"
done
echo "$i end as $(date +[%Y/%m/%d/%H/%M/%S])" >> $time_log
echo >> $time_log
done