InnoDB : le meilleur moteur de stockage pour MySQL ?

Lorsqu’un site vo­lu­mi­neux fonc­tionne lentement, le problème vient souvent d’une base de données non optimisée. Une place pour chaque chose, chaque chose à sa place : une maxime qui vaut aussi pour le numérique. Si le contenu se développe en per­ma­nence, il faut recourir à un système de gestion de base de données adapté, ou SGBD. Un SGBD bien connu, MySQL, permet de tra­vail­ler, de classer et de stocker à long terme et en toute sim­pli­cité de grandes quantités de données.

InnoDB joue un rôle très important pour stocker des données dans MySQL. InnoDB est un sous-système de stockage qui s’est distingué, ces dernières années, pour ses per­for­mances et sa fiabilité su­pé­rieures à ses con­cur­rents. Dans cet article, nous vous pré­sen­tons InnoDB, ses in­con­vé­nients et ses dif­fé­rences avec le moteur de stockage MyISAM.

À l’origine un simple moteur de stockage, InnoDB a fini par devenir un véritable moteur de stockage po­ly­va­lent pour MySQL. Au vu de sa grande fiabilité et de ses per­for­mances élevées, MySQL en a fait son moteur de stockage par défaut à partir de sa version 5.6.

En tant que système de gestion de base de données, MySQL a pour but d’en­re­gis­trer, d’indexer et d’in­ter­ro­ger des données dans une table. Lorsqu’on a besoin de lire et d’inscrire de petites quantités de données en grand nombre, comme pour un site Internet ou une ap­pli­ca­tion, ou bien de modifier des données avec des tran­sac­tions sûres, MySQL apporte une solution adaptée. Grâce à InnoDB, ce logiciel bénéficie du moteur de stockage adapté, qui se comporte comme un module et peut s’intégrer de manière flexible. Cependant, c’est toujours MySQL qui précise comment les données d’une base de données doivent être sau­ve­gar­dées.

La plupart des moteurs de stockage en­re­gistrent les données sur un support de données ou, pour un accès plus rapide, dans une mémoire centrale. InnoDB permet d’exécuter des tran­sac­tions de données isolées les unes des autres : c’est uni­que­ment lorsqu’une tran­sac­tion est terminée qu’elle est inscrite sur le support de données cor­res­pon­dant. Cette procédure évite tout accès incomplet dans une base de données existante.

Chaque table InnoDB organise les données sur le support de données. L’accès ralentit un peu, mais devient nettement plus sûr, car chaque table est optimisée à l’aide d’une clé primaire. La sélection des données est re­la­ti­ve­ment plus rapide, tandis que l’ajout et l’ac­tua­li­sa­tion prennent plus de temps. C’est la raison pour laquelle on re­com­mande le moteur de stockage InnoDB pour MySQL es­sen­tiel­le­ment pour les grosses bases de données et, notamment, lorsque de nom­breuses données sont liées les unes aux autres.

Comme évoqué, avec InnoDB, les données d’une tran­sac­tion sont reliées les unes aux autres. Lorsque des données sont sup­pri­mées, InnoDb supprime au­to­ma­ti­que­ment toutes les données associées. Il est d’autant plus simple pour l’uti­li­sa­teur de maintenir l’intégrité ré­fé­ren­tielle de la base de données. Cette intégrité ré­fé­ren­tielle doit être im­pé­ra­ti­ve­ment définie au préalable, sans quoi elle ne sera pas au­to­ma­ti­que­ment préservée. On peut ainsi ver­rouil­ler, par exemple, l’accès en écriture à des paquets de données.

La structure des tables d’InnoDB est sau­ve­gar­dée dans des fichiers frm, les données ex­ploi­tées et les index dans un espace de tables relié à la base de données, qui peut être réparti dans un ou plusieurs fichiers. L’intégrité ré­fé­ren­tielle s’impose ici aussi : la con­fi­gu­ra­tion de l’espace des tables peut être répartie dans plusieurs ré­per­toires, mais elle doit être spécifiée au début du travail et ne peut pas être modifiée ul­té­rieu­re­ment. Tra­vail­ler sur une table InnoDB s’apparente à tra­vail­ler sur la partition d’un disque dur, où les mo­di­fi­ca­tions ul­té­rieures peuvent entraîner une perte de données.

Le système le plus connu s’appuyant sur InnoDB est mediawiki, un logiciel qui, entre autre, constitue la base du site Internet Wikipédia.

En principe, InnoDB est plus récent et plus moderne que les autres moteurs de stockage MySQL. Ce moteur est aussi plus complexe et demande un en­vi­ron­ne­ment de base de données nettement plus puissant. Cependant, comme les ap­pli­ca­tions Web de­vien­nent de plus en plus complexes, les ex­ploi­tants se voient de plus en plus re­com­man­der les pos­si­bi­li­tés (tran­sac­tions, clés étran­gères) que propose MySQL avec InnoDB : tous les processus d’un site Internet reposent sur la lecture des contenus d’une base de données. Certes, avec InnoDB, le temps de réaction peut ralentir (notamment lorsque les entrées sont ac­tua­li­sées ou lorsque l’on ajoute de nouveaux articles). Pourtant, le critère décisif, à savoir l’affichage des nouveaux contenus côté uti­li­sa­teur, est nettement plus rapide.

Les avantages et les in­con­vé­nients d’InnoDB s’équi­librent lorsqu’il est utilisé pour un site Internet commun et moderne. Dans le cas d’une boutique en ligne, cette solution atteint vite ses limites : le système de commande implique de nombreux accès en écriture. Le système de ver­rouil­lage protégé en écriture d’InnoDB n’est donc pas pertinent. À l’inverse, dans ce contexte, on utilisera plutôt des tables qui gèrent mieux les accès en écriture, comme MyISAM.

Le point le plus important, et de loin, quand MySQL utilise InnoDB : toute mo­di­fi­ca­tion erronée à l’intérieur de la base de données peut entraîner le plantage complet du site. Avant chaque mo­di­fi­ca­tion, il faut donc faire une sau­ve­garde de la base de données existante. InnoDB utilise un protocole de tran­sac­tion spé­ci­fi­que­ment conçu qui, en cas d’erreur, lance au­to­ma­ti­que­ment la res­tau­ra­tion.

Depuis sa version 5, MySQL supporte plus de dix moteurs de stockage dif­fé­rents, dont le plus connu est MyISAM. Ce moteur en­re­gistre les données de chaque table dans deux fichiers dif­fé­rents, un pour les fichiers, un pour l’index. ISAM signifie Indexed Sequential Access Method (méthode d’accès sé­quen­tielle indexée) : il est im­pos­sible que plusieurs uti­li­sa­teurs ou ap­pli­ca­tions écrivent dans une table en même temps. L’en­re­gis­tre­ment sé­quen­tiel autorise une seule ap­pli­ca­tion à accéder en écriture. Cependant, cette même table peut être lue par plusieurs uti­li­sa­teurs en même temps. Lorsqu’il faut faire lire une grande quantité de petits paquets de données ra­pi­de­ment et à plusieurs ap­pli­ca­tions en même temps, MyISAM est im­bat­table.

InnoDB respecte la norme ACID (Atomicity Con­sis­tency Isolation Durability). Toutes les tran­sac­tions s’ef­fec­tuent isolées les unes des autres, mais il est possible, si né­ces­saire, que plusieurs ap­pli­ca­tions ins­cri­vent des données en même temps dans une même table. Lors de l’in­ter­ro­ga­tion des données, la tran­sac­tion SELECT, InnoDB est clai­re­ment supérieur ; en revanche, pour l’accès en écriture à une base de données, les tran­sac­tions INSERT et UPDATE, MyISAM est lé­gè­re­ment plus rapide. InnoDB compense largement sa vitesse moindre par son protocole de tran­sac­tion. MyISAM ne dispose d’aucune res­tau­ra­tion au­to­ma­tique en cas d’erreur : il faut donc toujours faire un back-up avant de modifier la base de données.