MySQL : maintenance des tables et des index MyISAM et InnoDB

MySQL : maintenance des tables et des index MyISAM et InnoDBCette 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;

MySQL  / ALTER TABLE Défragmentation Formateur MariaDB Formateur MySQL InnoDB MariaDB MyIsam MySQL Procédure stockée Réindexation