MySQL : maintenance des tables et des index MyISAM et InnoDB
10 mai 2014
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;