FAQ MySQLConsultez toutes les FAQ

Nombre d'auteurs : 16, nombre de questions : 155, dernière mise à jour : 22 avril 2014  Ajouter une question

 

Cette FAQ a été conçue à partir des questions fréquemment posées sur le forum MySQL de Developpez.com. Elle ne prétend pas à être exhaustive et peut contenir des erreurs occasionnelles. Si vous relevez une coquille, n'hésitez pas à nous le faire savoir.

Pour participer à cette FAQ, veuillez envoyer vos réponses sur le forum.


SommaireSyntaxe SQL et structure des donnéesMoteurs de stockage (4)
précédent sommaire suivant
 

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 :

Code sql : Sélectionner tout
CREATE TABLE test ( id integer not null primary key ) ENGINE=innodb ;

Mis à jour le 16 juin 2004 vanquish

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.

InnoDB stocke les données de toutes les tables et de tous les index dans un unique Tablespace. Ce tablespace peut être composé de 1 à 255 fichiers.
L'ensemble de ces fichiers est considéré comme un tout unique ; les données d'une même table sont réparties à travers les différents fichiers. Il en ressort que la taille d'une table n'est plus limitée par la taille maximum qu'impose le système d'exploitation à un fichier.
Ainsi, même sur une partition de type FAT32, une table InnoDB pourra dépasser 2Go.

InnoDB offre des statistiques très détaillées, ce qui permet une bonne optimisation des différents paramètres. Il permet également une sauvegarde à chaud et cohérente de votre base, sans avoir à verrouiller les tables pendant toute la durée du dump.
En effet, si vous utilisez le paramètre --single-transaction, mysqldump effectuera la sauvegarde de toute la base au sein d'une unique transaction.

Tous ces avantages font qu'InnoDB, d'abord considéré comme un simple plug-in, est désormais intégré à la version standard de MySQL.

Mis à jour le 16 juin 2004 vanquish

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.

Mis à jour le 16 juin 2004 vanquish

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

Code sql : Sélectionner tout
ALTER TABLE MA_TABLE TYPE=INNODB ;
Il faut cependant veiller à ce que le tablespace soit suffisamment grand pour contenir le résultat. Sur de grosses tables, l'opération peut prendre un temps non négligeable.

Attention, il ne faut pas tenter de convertir les tables système, c'est-à-dire les table de la base mysql (user, host etc.) qui doivent rester au format MyISAM.

Mis à jour le 16 juin 2004 vanquish

Proposer une nouvelle réponse sur la FAQ

Ce n'est pas l'endroit pour poser des questions, allez plutôt sur le forum de la rubrique pour ça


Réponse à la question

Liens sous la question
précédent sommaire suivant
 

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © 2017 Developpez Developpez LLC. Tous droits réservés Developpez LLC. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents et images sans l'autorisation expresse de Developpez LLC. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

 
Contacter le responsable de la rubrique MySQL