Introduction :

Le reconstruction ou l’organisation des index proprement dite n’existe pas en MySQL. Cette action de maintenance des données se réalise par un rebuild ou une réorganisation complète de la table avec la commande MySQL « OPTIMIZE TABLE ».
Optimiser une table en MySQL pour les deux moteurs de stockage les plus utilisés (MyISAM ou INNODB), consiste à défragmenter le stockage physique des données et des index associés d’une table, c’est-à-dire récupérer l’espace disque inutilisé laissé par les enregistrements supprimés ou par des mises à jour des données.

Quel est l’intérêt de l’optimize : 

L’optimisation d’une table permet de :

• Trier les données et les pages d’index,
• Réduire l’espace de stockage. Pour les tables InnoDB, la récupération d’espace disque est possible uniquement si le stockage est configuré par table (un tablespace ou un ibdata par table). Voir le lien suivant pour plus d’informations sur les avantages et les inconvénients de ce choix de paramétrage : http://dev.mysql.com/doc/refman/5.7/en/innodb-multiple-tablespaces.html
• Mettre à jour les statistiques sur les index,
• Réduire et augmenter l’efficacité des i/o,
• Générer des bons plans d’exécution et démineur ainsi les temps de réponse des requêtes.

Quel est l’impact de l’opération « OPTIMIZE » sur la disponibilité des données :

La commande MySQL à utiliser pour optimiser une table de type MyISAM ou InnoDB est la suivante « OPTIMIZE TABLE nom_table ». Pour les tables de type InnoDB, il s’agit d’un alias de la commande « ALTER TABLE nom_table ENGINE=InnoDB », donc pour ce type de table on peut utiliser l’une ou l’autre des 2 commandes.

Avant la version MySQL 5.7.4, les opérations de type « alter table » sont très coûteuses et le moteur MySQL utilise l’algorithme « COPY », c’est à dire que l’alter de la table passe par la création d’une nouvelle copie vide de la table en question (table temporaire), les données étant ensuite copiées ligne par ligne de l’ancienne vers la nouvelle table avec une mise à jour des index, et une fois la copie de toutes les lignes terminée, l’ancienne table est dropée et la nouvelle est renommée avec le nom d’origine. Et pendant la reconstruction de la table, les requêtes simultanées de type DML (DELETE, INSERT, UPDATE) ne sont pas autorisées. Donc, ce type d’opérations a un impact important sur la disponibilité des données selon la taille de la table, sur l’espace disque (création de la table temporaire dans le même espace de stockage de la table d’origine), et I/O important pour copier la table et construire les index.
Depuis la version 5.7.4, le moteur MySQL utilise l’algorithme »INPLACE » ou « Online DDL ». L’opération de rebuild de la table passe toujours par une copie temporaire pendant la reconstruction, mais elle ne bloque pas la mise à jour de la table à l’inverse des versions précédentes et l’opération est très rapide. L’utilisation de l’espace temporaire par défaut « tmpdir » pour ce type d’opérations est prévu à partir de la version 5.7.11.

La fréquence de l’opération « OPTIMIZE » :

L’opération de rebuild des tables doit être faite au moins une fois par semaine et après chaque mise à jour importante des données, car, sans cette opération, les données et les index restent fragmentés et les statistiques ne sont pas mises à jour. A partir de la version MySQL 5.6, la mise à jour des statistiques est faite automatiquement si le taux de mise à jour des données est supérieur à 10%. Voir le lien suivant pour plus d’information sur la mise à jour automatique des statistiques : http://dev.mysql.com/doc/refman/5.7/en/innodb-statistics-estimation.html.
Mais il y a aussi la possibilité de procéder au rebuild des tables fragmentées uniquement. Pour identifier la fragmentation des données des tables, il faut interroger la table système « INFORMATION_SCHEMA.TABLES » avec la requête suivante :
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, DATA_LENGTH/1024/1024 as DATA_SIZE_MO, INDEX_LENGTH/1024/1024  as INDEX_SIZE_MO, DATA_FREE/1024/1024 as DATA_FREE_MO, (DATA_FREE/1024/1024/ (DATA_LENGTH/1024/1024+INDEX_LENGTH/1024/1024)) as TAUX_FRAG
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN (‘mysql’,’information_schema’,’performance_schema’)
AND ENGINE IN (‘INNODB’, ‘MyISAM’)
AND TABLE_TYPE = ‘BASE TABLE’
— AND DATA_FREE > 0;

Comment optimiser toutes les tables :

Pour générer un script qui permet d’optimiser toutes les tables de toutes les bases utilisateurs, il suffit d’utiliser la requête suivante :
mysql>  mysql -u root -p’*********’ -B mysql -e « SELECT CONCAT(‘OPTIMIZE TABLE ‘,’\`’,TABLE_SCHEMA,’\`’,’.’,’\`’,TABLE_NAME,’\`’,’;’) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN (‘mysql’,’information_schema’,’performance_schema’)  AND ENGINE IN (‘INNODB’, ‘MyISAM’) AND TABLE_TYPE = ‘BASE TABLE’  » > /tmp/rebuild_tables.sql
Puis exécuter le script :
mysql> mysql -u root -p ‘********’ -B mysql < /tmp/rebuild_tables.sql
Mais il y a aussi la possibilité de planifier la tâche dans la crontab de l’OS ou d’utiliser le scheduler interne (EVENT) de MySQL.

Exemple d’un Event :

USE mysql;
DROP EVENT IF EXISTS event_exec_rebuild_table;
DELIMITER |
— Create event exec rebuild table
CREATE EVENT event_exec_rebuild_table
ON SCHEDULE EVERY 1 WEEK
STARTS ‘2017-01-01 02:00:00’
COMMENT  ‘rebuild des tables une fois par semaine’
DO
BEGIN
ALTER TABLE `test`.`test1` Engine=InnoDB;
ALTER TABLE `test`.`test2` Engine=InnoDB;
…….
END;
|
DELIMITER;
Pour plus d’information sur l’utilisation du scheduler interne de  MySQL : http://dev.mysql.com/doc/refman/5.7/en/create-event.html
J’espère que cet article vous sera utile.
Cordialement.