MySQL : MyISAM et InnoDB
A ses débuts, MySQL utilisait le moteur de stockage MyISAM.
C’est la raison pour laquelle on retrouve beaucoup d’exemples de création de tables sur Internet avec l’instruction engine=MyISAM (ce qui, au passage, peut faire planter pas mal de créations de bases/tables).
Aujourd’hui, le moteur de stockage par défaut de MySQL est InnoDB.
MyISAM n’est plus activement développé, à l’inverse d’InnoDB. Il est donc recommandé de convertir les tables MyISAM au format InnoDB, afin de bénéficier des dernières optimisations de performance du nouveau moteur.
Le moteur InnoDB
InnoDB est un moteur de stockage inclus d’origine dans toutes les distributions fournies par MySQL AB. Son principal avantage par rapport aux autres moteurs de stockage de MySQL est qu’il permet des transactions ACID (Atomiques, Cohérentes, Isolées et Durables), ainsi que la gestion des clés étrangères (avec vérification de la cohérence).
Votre site est trop lent ?
Un WordPress lent, c'est des conversions perdues. J'audite, j'optimise, je documente — et je vous explique ce que j'ai fait et pourquoi. Pas de jargon inutile, que du concret.
Demandez un audit de performance →Toutes les bases de données sont stockées au même endroit. Par défaut dans le fichier ibdata1 qui, sous les systèmes de type unix, se trouve généralement dans /var/lib/mysql. Il est également possible d’utiliser plusieurs fichiers ou même d’utiliser directement une ou plusieurs partitions sur le disque en mode RAW.
Ce moteur de base de données utilise aussi deux fichiers de logs, d’habitude ib_logfile0 et ib_logfile1. Les fichiers de définitions de table .frm sont également dans un dossier au nom de la base comme pour MyISAM.
Depuis sa version 5.5, MySQL utilise InnoDB comme moteur par défaut.
Comment connaitre le format actuel de vos tables ?
Pour faire un petit état des lieux du format actuel des tables de votre base de données, il suffit de vous identifier sur le serveur SQL et de lancer cette requête :
SHOW TABLE STATUS FROM `database`;Langage du code : JavaScript (javascript)Il vous suffit de remplacer database par le nom de votre base de données et de regarder la valeur de la colonne Engine.
Convertir les tables MyISAM au format InnoDB
Pour convertir des tables MyISAM au format InnoDB, il suffit de lancer une requête SQL va grandement nous simplifier la vie:
SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')
FROM information_schema.tables
WHERE 1=1
AND engine = 'MyISAM'
AND table_schema NOT IN ('information_schema', 'mysql', 'performance_schema');Langage du code : JavaScript (javascript)Le résultat de cette requête est une liste bien formatée de requêtes à lancer pour que toutes nos tables soient converties au format InnoDB.
Voici le résultat de cette requête sur le serveur :
+-----------------------------------------------------------+
| CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') |
+-----------------------------------------------------------+
| ALTER TABLE blog.blog_wc_comments_subscription ENGINE=InnoDB;
| ALTER TABLE blog.blog_wc_phrases ENGINE=InnoDB;
| ALTER TABLE blog.blog_wc_users_voted ENGINE=InnoDB;
+-----------------------------------------------------------+Langage du code : JavaScript (javascript)Toutes les tables des bases de données qui sont au format MyISAM au format InnoDB sont listées, à l’exception des tables utilisées dans la gestion de MySQL (‘information_schema’, ‘mysql’, ‘performance_schema’) qui doivent rester en MyISAM.
Etape de Conversion
Il vous suffit ensuite de lancer cette liste de requêtes ALTER TABLE sous MySQL, Adminer ou PHPMyAdmin pour convertir vos tables. Lancez la liste de commandes que vous venez de trouver :
ALTER TABLE blog.blog_wc_comments_subscription ENGINE=InnoDB;
ALTER TABLE blog.blog_wc_phrases ENGINE=InnoDB;
ALTER TABLE blog.blog_wc_users_voted ENGINE=InnoDB;Et voilà, vos tables MyISAM sont maintenant devenues des tables InnoDB.
Retours sur InnoDB
J’ai passé l’intégralité de mes tables en InnoDB et voici mes observations.
Le site est plus réactif et semble être beaucoup moins sensibles aux erreurs de connexion à la base de données aux heures de pointe.
Plus de crash de tables, donc plus besoin de stopper le serveur SQL, réparer et relancer le serveur.
Il est plus aisé d’optimiser InnoDB, pleins de tutoriels sur le net en parlent.
Il sera plus simple de migrer un jour vers un autre serveur de base de données comme MariaDB, qui sera totalement opérationnel avec InnoDB.
Votre base de données ralentit tout ?
Tables wp_options surchargées, autoload incontrôlé, requêtes non indexées — une base WordPress mal entretenue finit toujours par plomber les temps de réponse. Je l'audite, je la nettoie, je l'optimise.
Diagnostiquons votre base de données →
Bonjour,
après avoir lancé la requête SQL, je n’ai pas vu de modifications, quand je suis sur phpmyadmin dans la colonne type il y a encore écrit myisam.
Bonjour Mathieu,
Effectivement, il manquait un petit paragraphe explicatif dans l’article… je viens de le rajouter, ainsi qu’un exemple, pour que tout soit plus clair. Merci!
Désolé mais je ne comprends pas bien si les tables doivent s’afficher après la conversion avec le tag myisam ou innodb. Chez moi c’est myisam qui reste affiché en face de chaque table et innodb en bas pour la somme.
Bonjour dd,
Une fois que les commandes ALTER sont lancées, les tables sont converties au format InnoDB. Il faut que l’utilisateur qui lance ces commandes ait les droits MySQL de modifier la structure des tables (droits ALTER notamment).
Ensuite, il sufft de relancer
.
Les tables seront alors en InnoDB.