MySQL : maintenance des tables et des index MyISAM et InnoDB
Cette procédure stockée a pour objet de « défragmenter » les tables MyISAM et InnoDB à condition que ces dernières soient stockées en mode innodb_file_per_table=1. Son intérêt est aussi de réindexer les index associés aux tables. Vous pouvez appeler cette procédure par un EVENT.
/* Création de la procédure stockée*/ USE mysql; DROP PROCEDURE IF EXISTS maintenance; DELIMITER $$ CREATE PROCEDURE maintenance() BEGIN DECLARE done INT DEFAULT FALSE; DECLARE val_table VARCHAR(255); DECLARE val_schema VARCHAR(255); DECLARE val_engine VARCHAR(255); DECLARE val_sql VARCHAR(255); DECLARE cur_tables CURSOR FOR SELECT table_schema, table_name, engine FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') and engine IN ('MyISAM','InnoDB'); DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur_tables; boucle: LOOP FETCH cur_tables INTO val_schema,val_table,val_engine; IF done THEN LEAVE boucle; END IF; SET @a=val_schema; SET @b=val_table; SET @c=val_engine; IF @c='MyISAM' THEN /* SET @s='REPAIR TABLE ?.?'; */ SET @s=CONCAT('REPAIR TABLE `',@a,'`.`',@b,'`'); ELSEIF @c='InnoDB' THEN /* SET @s='ALTER TABLE ?.? ENGINE=InnoDB'; */ SET @s=CONCAT('ALTER TABLE `',@a,'`.`',@b,'` ENGINE=InnoDB'); END IF; PREPARE stmt FROM @s; EXECUTE stmt /* USING @a,@b */; DEALLOCATE PREPARE stmt; /* SET @s='OPTIMIZE TABLE ?.?'; */ SET @s=CONCAT('OPTIMIZE TABLE `',@a,'`.`',@b,'`'); PREPARE stmt FROM @s; EXECUTE stmt /* USING @a,@b */; DEALLOCATE PREPARE stmt; END LOOP; CLOSE cur_tables; END $$ DELIMITER ; /* Utilisation de la procédure stockée */ use mysql; CALL maintenance;