mysql表碎片清理表空间收缩应用

碎片产生的原因:

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

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

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

查看碎片的SQL: table_schema db, table_name, data_free, engine依次表示 数据库名称 表名称 碎片所占字节空间  表引擎名称
   select table_schema db, table_name, data_free, engine from information_schema.tables where table_name ='student'
清理碎片并将表空间收缩的SQL: OPTIMIZE TABLE 库名.表名
  OPTIMIZE TABLE 库.student;
应用场景:
如果您已经删除了表的一大部分，或者如果您已经对含有可变长度行的表（含有VARCHAR, BLOB或TEXT列的表）进行了很多更改，则应使用OPTIMIZE TABLE优化表。
清除碎片的优点:
  降低访问表时的IO,提高mysql性能,释放表空间降低磁盘空间使用率
注意
1.MySQL官方建议不要经常(每小时或每天)进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可(我们现在是每月凌晨4点清理mysql所有实例下的表碎片)。
2.在OPTIMIZE TABLE运行过程中，MySQL会锁定表。因此，这个操作一定要在网站访问量较少的时间段进行。
3.清理student的105万条数据, OPTIMIZE TABLE 库.student;本地测试需要37秒。
现ipcacloud库已有event_optimize_table事件每月凌晨4点调用optimize_table()存储过程, 清理表碎片(当碎片空间占数据空间 > 0.30的这些表的碎片都会被清理).
/*
此定时任务 事件每月凌晨4点清理mysql实例下表碎片
*/
DROP EVENT IF EXISTS `event_optimize_table`;
DELIMITER ;;
CREATE EVENT `event_optimize_table` ON SCHEDULE EVERY 1 MONTH STARTS '2017-12-15 04:00:00' ON COMPLETION PRESERVE ENABLE DO CALL optimize_table()
;;
DELIMITER ;
/*
清理mysql下所有实例的表碎片(当碎片字节空间占 数据字节与索引字节空间 之和大于0.30时, 这些表的碎片都需要清理,使用游标遍历清理) 定时任务事件 每月凌晨4点调用此清理表碎片的任务
table_schema是数据库名 OPTIMIZE TABLE 库.article;
*/
DROP PROCEDURE IF EXISTS `optimize_table`;
DELIMITER ;;
CREATE  PROCEDURE `optimize_table`()
BEGIN
DECLARE tableSchema VARCHAR(100);
DECLARE tableName VARCHAR(100);
DECLARE stopFlag INT DEFAULT 0;
-- 大于30%碎片率的清理
DECLARE rs CURSOR FOR SELECT table_schema,table_name FROM information_schema.tables WHERE ((data_free/1024)/((data_length+index_length+data_free)/1024)) > 0.30;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag = 1;
OPEN rs;
WHILE stopFlag <> 1 DO
FETCH NEXT FROM rs INTO tableSchema,tableName;
IF stopFlag<>1 THEN 
-- SET @table_optimize = CONCAT('ALTER TABLE `',tableName,'` ENGINE = INNODB');
SET @table_optimize = CONCAT('OPTIMIZE TABLE `',table_schema,'`.`',tableName,'`');
PREPARE sql_optimize FROM @table_optimize;    
EXECUTE sql_optimize;
END IF;
END WHILE;
CLOSE rs;
    END
;;
DELIMITER ;