数据库优化之清理数据库碎片

一、如何查看MySQL 的表空间是否有碎片
1、show status可以查看MySQL服务器运行状态值。而show table status from db_name可以查询db_name 数据库里所有表的信息，是否有索引碎片等。
这个命令中 Data_free 字段，如果该字段不为 0，则产生了数据碎片。

2、产生碎片的原因
经常进行 delete 操作
经常进行 delete 操作，产生空白空间，久而久之就产生了碎片；
update 更新
update 更新可变长度的字段(例如 varchar 类型)，将长的字符串更新成短的。之前存储的内容长，后来存储是短的，即使后来插入新数据，那么有一些空白区域还是没能有效利用的。
由于碎片空间是不连续的，导致这些空间不能充分被利用；由于碎片的存在，导致数据库的磁盘 I/O 操作变成离散随机读写，加重了磁盘 I/O 的负担。

二、MySQL 的表空间如何清理碎片？
1、show variables like ‘innodb_file_per_table’;执行查看是否为ON
打开该参数，创建表则会分2个文件，.frm存放元数据，.ibd存放表数据(表初始大小是98304KB)；
关掉该参数off之后，创建表，只会生成.frm元数据文件，数据会存放到系统表空间，这样会不利于后期管理，系统表空间会逐渐膨胀，导致影响性能。
2、定期执行 OPTIMIZE TABLE tableName;
optimize table 表名；
对于InnoDB的表，OPTIMIZE TABLE 的工作原理如下
对于InnoDB表， OPTIMIZE TABLE映射到ALTER TABLE … FORCE（或者这样翻译：在InnoDB表中等价 ALTER TABLE … FORCE），它重建表以更新索引统计信息并释放聚簇索引中未使用的空间。
3、定期执行 ALTER TABLE tableName ENGINE = InnoDB;
方法一：ALTER TABLE tablename ENGINE=InnoDB；(重建表存储引擎，重新组织数据)
或：ALTER TABLE tbl_name FORCE;
方法二：进行一次数据的导入导出
使用 mysqldump 将表转储到文本文件，删除表，然后从转储文件重新加载
注意：optimize、ALTER TABLE是高危操作，会锁表，影响业务，建议在低峰期操作。