DELIMITER $$
USE `db`$$
DROP PROCEDURE IF EXISTS `optimize_table`$$
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 ;
mysql表碎片清理
最新推荐文章于 2024-06-23 17:44:30 发布