Introduction :
L’analyse des tables en MySQL est un traitement de maintenance complémentaire au traitement de rebuild « OPTIMIZE TABLE » qui défragmente et réorganise les données de la table (Voir l’article Rebuild index en MySQL disponible dans ce blog pour plus d’informations). L’analyse des tables « ANALYZE TABLE » permet de mettre à jour les statistiques sur les données de la table et les index.
Quel est l’intérêt de l’Analyze:
Les traitements de rebuild et de l’analyze des tables sont essentiels pour la défragmentation des données et pour la mise à jour des statistiques sur les colonnes et les index afin de permettre à l’optimiseur de choisir les meilleurs plans d’exécution et d’avoir ainsi des meilleurs temps de réponse des requêtes. Il est donc essentiel d’exécuter ces deux traitements au moins une fois par semaine ou plus en cas de forte activité de mise à jour des données de la base. Il est important aussi de faire un Analyze d’une table en cas de modification de sa structure comme l’ajout d’un index par exemple ou en cas de purge ou d’une intégration importante des données.
La mise à jour automatique des statistiques :
Depuis la version 5.6, les statistiques sur les index et les tables sont mises à jour automatiquement avec l’activation du paramètre « innodb_stats_auto_recalc », et cette mise à jour automatique des statistiques intervient une fois que le seuil de mise à jour de 10 % des lignes de la table est atteint.
Sur MySQL, on a la possibilité d’avoir des statistiques en mémoire (supprimées en cas de redémarrage de l’instance) ou persistantes sur disque avec l’activation ou pas du paramètre « innodb_stats_persistent ». Les deux paramètres « innodb_stats_auto_recalc » et « innodb_stats_persistent » sont activés par défaut depuis la version 5.6.6.
En cas d’activation de l’un ou des deux paramètres, la règle s’applique sur toutes les tables et toutes les bases de l’instance, mais on a aussi la possibilité d’activer ou pas une ou les deux options au niveau de la table avec la clause « STATS_PERSISTENT » et « STATS_AUTO_RECALC » lors de la création de la table ou par la modification de sa structure (CREATE/ALTER TABLE).
Les informations sur les statistiques des index et les tables sont stockées dans les deux tables innodb_table_stats et innodb_index_stats de la base système mysql.
Pour consulter la dernière mise à jour des statistiques, il suffit de consulter la colonne « last_update » des deux tables.
Pour plus d’informations sur la description des deux tables et des noms des statistiques, veuillez consulter le lien suivant : https://dev.mysql.com/doc/refman/5.7/en/innodb-persistent-stats.html
La commande Analyze:
La commande à utiliser pour analyser une table est la suivante :
ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name1 [, tbl_name2] ….
Par défaut, la commande est écrite dans le binlog de l’instance et donc répliquée sur les slaves; pour éviter cela, il suffit d’utiliser l’option NO_WRITE_TO_BINLOG ou LOCAL.
La commande « Analyze » supporte aussi les tables partitionnées. Dans ce cas, l’analyze se fait avec la commande ALTER TABLE nom_table » OPTIMIZE PARTITION nom_part1, nom_part2, …
Pour les tables de type MyISAM, il faut utiliser la commande « myisamchk » avec l’option « analyze » : MYISAMCHK –ANALZE nom_table [.MYI]
Pour plus d’informations sur l’utilisation de la commande « myisamchk », veuillez consulter le lien suivant : https://dev.mysql.com/doc/refman/5.7/en/myisamchk.html
Comment scripter l’analyze de toutes les tables InnoDB :
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(‘ANALYZE TABLE ‘,’\`’,TABLE_SCHEMA,’\`’,’.’,’\`’,TABLE_NAME,’\`’,’;’) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN (‘mysql’,’information_schema’,’performance_schema’) AND ENGINE =’INNODB’ AND TABLE_TYPE = ‘BASE TABLE’ » > /tmp/analyze_tables.sql
Puis exécuter le script :
mysql> mysql -u root -p ‘********’ -B mysql < /tmp/analyze_tables.sql
Pour les tables de type MyISAM, la commande myisamchk s’applique directement sur les fichiers physiques des tables. Exemple : myisamchk –analyse /path/to/datadir/*/*.MYI
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_analyze_table;
DELIMITER |
— Create event exec analyze table
CREATE EVENT event_exec_analyze_table
ON SCHEDULE EVERY 1 WEEK
STARTS ‘2017-01-01 02:00:00’
COMMENT ‘mise à jour des statistiques des tables et des index une fois par semaine’
DO
BEGIN
ANALYZE TABLE `test`.`test1` ;
ANALYZE TABLE `test`.`test2` ;
…….
END;
|
DELIMITER;
Pour plus d’informations 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.
