MySQL磁盘碎片清理MySQL磁盘碎片清理为什么会产生磁盘碎片？那是因为某一个表如果经常插入数据和删除数据，必然会产

MySQL磁盘碎片清理

磁盘碎片产生的原因

为什么会产生磁盘碎片？那是因为某一个表如果经常插入数据和删除数据，必然会产生很多未使用的空白空间，这些空白空间就是不连续的碎片，这样久而久之，这个表就会占用很大空间，但实际上表里面的记录数却很少，这样不但会浪费空间，并且查询速度也更慢。

查看除系统表外磁盘碎片占用空间[date_free]大于0的所有表信息

-- 查看除系统表外 data_free 值大于0 的所有表的信息

SELECT
    concat(TABLE_SCHEMA,'.',TABLE_NAME)                                         AS database_name,
    concat(TRUNCATE(SUM(DATA_LENGTH+DATA_FREE+INDEX_LENGTH)/1024/1024,2),' MB') AS total_size,
    concat(TRUNCATE(SUM(DATA_LENGTH)/1024/1024,2),' MB')                        AS data_size,
    concat(TRUNCATE(SUM(DATA_FREE)/1024/1024,2),' MB')                          AS data_free,
    concat(TRUNCATE(SUM(INDEX_LENGTH)/1024/1024,2),'MB')                        AS index_size,
    ENGINE
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema',
                           'mysql')
    AND data_free > 0
GROUP BY
    TABLE_NAME,
    TABLE_SCHEMA,
    ENGINE
ORDER BY
    data_free DESC;

清理磁盘碎片的两种方式

OPTIMIZE TABLE 表名;

-- OPTIMIZE TABLE只对MyISAM、BDB和InnoDB表起作用
OPTIMIZE TABLE tb_task_log;

执行结果会出现 “Table does not support optimize, doing recreate + analyze instead”，这个其实无妨，实际上磁盘碎片已经被清除掉了。

ALTER TABLE 表名 ENGINE = Innodb;（注意：只有InnoDB引擎的表可以这么做）

ALTER TABLE tb_operational_log engine=InnoDB;

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