mysql空间清理

最近某个数据库空间达到上线，通过业务梳理删掉了很大的空间，结果表空间只少了一点点（估计只减少了索引占的空间）

一. MySQL中如何找出碎片化严重的表

SELECT table_schema, TABLE_NAME, concat(data_free/1024/1024, ‘M’) as data_free
FROM `information_schema`.tables
WHERE data_free > 3 * 1024 * 1024
AND ENGINE = ‘innodb’
ORDER BY data_free DESC

二. 参数 innodb_file_per_table

mysql5.6.6及其后续版本默认开启独立表空间，开启后可独立表空间，才支持单表空间整理回收

否则是共享表空间，只能重建数据库回收空间

修改方法：在配置文件（my.cnf）中设置： innodb_file_per_table

查看配置：show variables like ‘%innodb_file_%’

更多细节见另一篇博文：《mysql的 共享表空间 与 独立表空间》

三. 通过 optimize table xxx 回收

工作原理：它重建表以更新索引统计信息并释放聚簇索引中未使用的空间

使用方式：OPTIMIZE TABLE foo;

注意事项：1.执行期间io和cpu会短暂飚高  2.只适用于独立表空间，即innodb_file_per_table=1 要开启

执行效果：如果有主从同步的话，主库能回收成功，从库回收不了

四. 通过 alter table xxx engine=innodb 回收（推荐）

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

使用方式：alter table xxx engine=innodb

注意事项：1.执行期间io和cpu会短暂飚高  2.只适用于独立表空间，即innodb_file_per_table=1 要开启

执行效果：主库从库空间都成功回收