FAQ MySQLConsultez toutes les FAQ

Lorsque MySQL veut créer, supprimer ou lire un enregistrement dans une table, il délègue ce travail au sous-système appelé moteur de stockage.
Sous MySQL 5.0, ce concept prend toute sa signification car les moteurs sont connectables (pluggable) et déconnectables à loisir du coeur du SGBD. La version 5.1 simplifie quant à elle la création de moteurs de stockage personnalisés qui seront connectables au serveur à la volée.

Par défaut, MySQL utilise le moteur MyIsam, mais il existe plusieurs autres systèmes : InnoDB, BDB, Heap, Archive ou encore Federated.
Hormis MyISAM, le seul moteur très exploité reste InnoDB. Les autres sont obsolètes, en cours de développement ou réservés à des usages très précis (comme des tables temporaires stockées en mémoire).

On spécifie le moteur de la table au moment de sa création en terminant son instruction CREATE par la chaine ENGINE=NomDuMoteur (anciennement TYPE=NomDuType).
Par exemple :

CREATE TABLE test ( id integer not null primary key ) ENGINE=innodb ;

InnoDB permet à MySQL de répondre aux spécifications dites ACID (Atomicité, Cohérence, Isolation, Durabilité). Se reporter à l'article de SqlPro : http://sqlpro.developpez.com/cours/s...ndements/#L4.5.

Pour répondre à ces critères :

• InnoDB implémente un système de transactions avec un verrouillage au niveau enregistrement et, comme Oracle, un système de multi-versionning des enregistrements. Ce dernier point permet de faire des SELECT, sans avoir à verrouiller quoi que ce soit.
  
  InnoDB permet tous les types de transactions imposés par SQL 92, soit : READ UNCOMMITED, READ COMMITED, REPEATABLE READ, SERIALIZABLE.
  

• Est utilisé un système de triple-écriture et un système de journaux qui rend les bases InnoDB très robustes. La reprise sur crash s'effectue en quelques minutes (voire en quelques secondes).
  

• InnoDB propose aussi de définir des contraintes d'intégrité référentielles des données grâce à l'utilisation de clés étrangères (foreign key). Ce système permet des mises à jour et des suppressions en cascade.
  

A proprement parler, le moteur de stockage InnoDB n'est pas plus lent que MyISAM. Sur certaines opérations simples, il serait même plus rapide. InnoDB permet donc de conserver la principale force de MySQL : sa grande vélocité.

Cependant, l'utilisation de clés étrangères oblige la création et la consultation d'index, ce qui n'est pas sans impact sur les performances.
Seul l'accès aux méta-données (instruction du type DESCRIBE TABLE_X) est plus lent, ce qui ne pose pas de réels problèmes dans une utilisation courante.

Les inconvénients sont ailleurs.

Avec le système InnoDB, toutes les données de toutes les tables de toutes les bases sont stockées au sein d'un tablespace commun. Il en résulte qu'il est beaucoup plus compliqué de déplacer une base d'un serveur à un autre.
Il faut obligatoirement sauvegarder la base à l'aide de mysqldump, puis remonter cette sauvegarde sur le serveur de destination. Pour supprimer, une base, il faut obligatoirement passer par l'instruction drop database,car si on se contente de supprimer le dossier portant le nom de la base, on ne supprimera que le descriptif des tables, mais pas les données.
Mais même en utilisant drop database, si l'espace correspondant aux données est bien libéré et réutilisable pour de nouvelles données, la taille du ou des fichiers constituant le tablespace ne sera pas réduite pour autant. L'espace disque n'est donc pas immédiatement récupérable.
Le seul moyen pour diminuer la taille du tablespace est de faire un dump de l'ensemble des bases, de supprimer les bases puis le tablespace, et enfin de restaurer une partie de la sauvegarde au sein d'un nouveau tablespace plus petit.

Depuis la version 4.1.1, InnoDB permet de créer un tablespace différent pour chaque table - ce qui le rapproche du mode de fonctionnement de MyIsam. Cela tend donc à réduire le problème, mais sans le résoudre totalement. En effet, les différentes tables sont liées par des transactions communes. Ces tablespaces ne sont donc pas totalement indépendants les uns des autres.
Cependant, la copie ou la suppression d'une base devient possible, si aucune transaction n'est en cours ou en attente dans les journaux.

InnoDB comporte quelques autres limitations qui ont peu de chance de vous poser problème, mais qu'il est préférable de connaître :

• InnoDB ne tient pas à jour le compte des enregistrements d'une table. Un SELECT COUNT(*) FROM TABLE_X, sans clause WHERE sera donc plus lent sur InnoDB qui devra parcourir l'ensemble de l'index primaire pour calculer la valeur.
  

• InnoDB a une gestion légèrement différente des valeurs auto-incrémentés. Il n'est pas possible d'initialiser le compteur autrement qu'en créant un enregistrement fictif. Un champ auto-incrémenté doit obligatoirement être indexé et cet index ne doit contenir que ce champ.
  

• Hors champ TEXT et BLOB, un enregistrement ne peut pas dépasser la moitié de la taille d'une page, soit 8Ko (puisque par défaut les pages font 16Ko). Ce n'est pas un réel problème puisque même avec 50 champs de type integer et 50 champs de type CHAR(120), vous restez sous cette limite.
  

• Il n'est pas possible d'utiliser l'instruction INSERT DELAYED.
  

• Les bases InnoDB sont nettement plus grosses que leur équivalent MyIsam. A nombre d'enregistrements égal, comptez au moins 25% de volume en plus.
  

• Dernier inconvénient, le paramétrage du fichier my.ini (ou my.cnf) est un peu plus compliqué si on désire utiliser InnoDB de façon optimale.
  

Oui. Il suffit d'utiliser une instruction du type :

ALTER TABLE MA_TABLE TYPE=INNODB ;