Mysql : les principales différences entre MyISAM et InnoDB


troll
On se pose souvent la question sur l'utilisation de MyISAM ou d'InnoDB pour une base de données MySQL. Cet article décrit rapidement les principaux avantages et inconvénients de ces deux moteurs de stockage.

1. MyISAM

Les avantages :

  • très rapide pour les requêtes de type SELECT ou INSERT
  • il supporte les index fulltext : permet d'effectuer des recherches sur des mots en se basant sur un index spécifique, accélérant ainsi les recherches
  • il gère les conflits d'accès (ou lock) : permet de verrouiller une table pour des opérations bien précises
  • très facile à administrer : possibilité de recopier directement les fichiers d'un serveur vers un autre, support des tables compressées ...

Les inconvénients :

  • il ne supporte pas les transactions
  • il ne supporte pas les clés étrangères

Note : en plus du fichier .frm, chaque table est représentée par un fichier de données .myd (MYisamData) et un fichier d'index .myi (MYisamIndex).

2. InnoDB

Les avantages :

  • il supporte ACID : permet d'assurer que chaque enregistrement sera complètement réussi, ou complètement échoué, ainsi les risques d'erreurs sont impossibles, même en cas de panne
  • il gère les transactions (instructions sql BEGIN, COMMIT, ROLLBACK ...)
  • il supporte les clés étrangères et les intégrités référentielles
  • il possède un système de récupération automatique en cas de crash

Les inconvénients :

  • il ne permet pas les index fulltext
  • son administration est un peu plus complexe (gestion de tablespace, paramètres supplémentaires dans le my.cnf ...)
  • le moteur de stockage est plus lent que d'autres et gourmand en ressources mémoires et en espace disque

Note : chaque table est représentée par un fichier .frm.

3. Changement du moteur de stockage

On gardera à l'esprit que le choix du moteur n'est jamais définitif. Il peut être changé dynamiquement avec la commande ALTER TABLE comme ceci :

ALTER TABLE maTable1 ENGINE=INNODB;
ALTER TABLE maTable2 ENGINE=MYISAM;

Ce système permet notamment d'utiliser plusieurs types de stockage pour différentes tables d'une même base de données. Cela permettra dans certains cas d'obtenir les meilleurs performances et avantages.

4. Conclusion

Pour conclure, on optera pour InnoDB principalement lorsque l'on utilisera un système d'information qui n'admet pas les erreurs ou qui doit utiliser des clés étrangères ou des intégrités référentielles.

MyISAM restera quant à lui le meilleur choix dans le cas où l'on fait principalement des requêtes de lecture ou d'insertion.


23 Commentaires pour "Mysql : les principales différences entre MyISAM et InnoDB"

Flux des commentaires de cet article Ajouter un commentaire
  •  

    Je vais donner l'impression de troller, mais PostgreSQL est bien meilleur de Mysql.

    Peut-être que tu n'as pas le choix de la base de donnée. Mais si tu as le choix, je te conseille hypra vivement de regarder PostgreSQL (la doc est superbe ce qui ne gache rien).

    > - il supporte ACID

    PostgreSQL le fait et sur plusieurs tables à la fois (lorsqu'il y a des contraintes entre tables).

    > - il supporte les clés étrangères et les intégrités référentielles

    Idem PostgreSQL, mais PostgreSQL c'est mieux. Tu peux faire un backup (alors que ta base de données est utilisée en écriture), toutes les règles sont respectés. Il n'y aura aucune incohérence dans le dump.

    M'enfin, force est de constater que MySQL fait la satisfaction de beaucoup.

    RépondreRépondre
    moi , le 17 décembre 2007 à 21:53
  •  

    InnoDB est plus rapide en écriture, d'après mes tests, lors des applications type "forum". En effet, il gère le "locking" par ligne (row) et non par table, donc on peut plus facilement imaginer bcp de personnes postant sur un même forum.

    Je l'avais installé pour un forum sous MyISAM avant, et la différence était flagrante.

    RépondreRépondre
    fruey , le 17 décembre 2007 à 22:36
  •  

    Tout à fait d'accord avec Fruey, MyIsam n'est pas fait pour l'insertion mais pour la lecture (et dans ce cas pourquoi, p.ex; en php, ne pas utiliser un fichier plat, vu qu'il ne gère pas l'intégrité référentielle !).

    Une de mes applis d'inscription d'élèves ramait sensiblement sous MyIsam (bcp d'insertion et de modif°), InnoDB a résolu le pb.

    SQLite est intéressant aussi.

    RépondreRépondre
    leg , le 18 décembre 2007 à 07:47
  •  

    @moi : je connais également PostgreSQL (je l'ai utiliser à une époque ou MySQL était très peu populaire), mais aujourdh'ui je préfère utiliser MySQL. Sinon, pour les bases MyISAM, on peut faire des lock tables avant les dump afin de garantir les sauvegardes. Le binaire mysqlhotcopy permet d'ailleurs de faire des dumps avec lock de façon très simple.

    @Fruey et @leg : en se qui concerne la rapidité des INSERT, je me suis basé sur les résulats de ce test :
    http://www.magazine-jeux.com/Opt...

    Ou l'on peut voir que MyISAM est plus rapide lors d'une insertion de 100 000 lignes :
    - MyIsam 72sec
    - InnoDB >2min

    Quelque part cela me semble plus logique, car si j'ai bien compris, à chaque INSERT, InnoDB vérifie que tout c'est bien passé afin de garantir l'intégrité des données, MyISAM lui ne fait rien.

    En revanche, j'ai lu ailleurs que InnoDB serait plus performant sur des UPDATE, ce qui peu expliquer les écarts de performances dans certains cas.

    RépondreRépondre
    pti-seb , le 18 décembre 2007 à 13:30
  •  

    @Pti-seb :
    Oui et non sur les INSERT : la question n'est pas la rapidité pour une opération en soi, mais la concurrence de multiples INSERT dans une application du type forum. Si le trafic est important & plusieurs INSERT se font dans une même table (forum_posts, qui contient une ligne pour chaque nouveau billet + une jointure vers une table type forum_categories par ex) alors InnoDB s'avère, d'expérience, plus rapide et plus stable.

    Sur une table qui se met à jour en bloc en une seule fois, alors MyISAM + LOAD DATA INFILE est très très rapide. InnoDB ne s'impose que dans les applications quand il y a bcp d'écritures en simultané vers la même table, en fait...

    RépondreRépondre
    fruey (Let's Have It) , le 18 décembre 2007 à 14:13
  •  

    bonjour ,
    pouvez vous mexpliquer Freuy c koi la gestion par ligne row merci

    RépondreRépondre
    saliphp , le 29 décembre 2008 à 12:54
  •  

    Je suis loin d'être expert donc un peu d'indulgence ^^ mais si je veux faire gérer des clés étrangères et faire un "moteur de recherche" en utilisant les index full text je ne peux pas ?

    Merci

    RépondreRépondre
    milka1664 , le 22 mars 2010 à 19:56
  •  

    @milka1664 : ben il n'y a que MyISAM qui supporte le full text ...

    RépondreRépondre
    pti-seb , le 22 mars 2010 à 20:11
  •  

    @pti-seb : Oui mais pour les clés etrangères + index fulltext

    RépondreRépondre
    milka1664 , le 22 mars 2010 à 20:14
  •  

    windows server offre aussi des possibilités intéressantes - no troll -

    RépondreRépondre
    Deathpool , le 27 octobre 2010 à 15:48
  •  

    dans certain cas, nous avons besoin de
    1."lectures rapide" pour les recherche
    2. "Ecriture rapide", pour les mises à jour et l'ajout des données etc..
    3. ET de respecter les contraintes avec des clé étrangère etc...

    et c'est la que la véritable question se pose, quel moteur choisir?

    RépondreRépondre
    abdeslem , le 11 mars 2012 à 12:27
  •  

    bonjour,
    je suis entrain de développer une application php (symfony2) je veux génerer mes entités avec revers engeneering est ce que je dois utiliser InnoDB ou MyIsam , je suis coincée à ce niveau . et merci d'avance

    RépondreRépondre
    fahdi.jbeli , le 18 juillet 2012 à 11:25
  •  

    j'ai trouvé la solution il faut utilisé InnoDB

    RépondreRépondre
    fahdi.jbeli , le 18 juillet 2012 à 15:23
  •  

    pourrais tu donner plus de détails?

    RépondreRépondre
    abdeslem , le 18 juillet 2012 à 15:41
  •  

    "il possède un système de récupération automatique en cas de crash"

    en fait non... je viens d'avoir un crash et ma base innodb est simplement bonne pour la poubelle, il n'y a pas de procédure de récupération :(

    RépondreRépondre
    greg , le 20 août 2012 à 20:46
  •  

    @greg : il faut relancer le serveur MySQL, celui-ci réparera ta base InnoDB automatiquement. Si ce n'est pas le cas, c'est que tu as surement fait une erreur en terme d'administration de ce dernier.

    RépondreRépondre
    pti-seb , le 21 août 2012 à 09:05
  •  

    Non pas d'erreur de ma part, juste un crash... j'ai relancé, exporté, cherché partout et tout essayé. Je vais ré-écrire mon application pour utiliser des flat files au lieu d'innodb, c'est plus sûr...

    RépondreRépondre
    greg , le 21 août 2012 à 23:51
  •  

    Bonsoir,
    Moi pour ma part, j'étais sous InnoDB pour mon annuaire.
    Le trouvant lent, j'ai décidé de passé ma bdd sous MYISAM. Et là miracle, mon annuaire devient super-rapide !

    Merci à vous pour le code de changement de type !
    Bon courage.

    RépondreRépondre
    Quentin , le 15 novembre 2012 à 22:22
  •  

    @Quentin : sinon tu peux aussi passer certaines tables sous MyISAM et conserver d'autres en InnoDB. Mais c'est vrai que ça deviens compliqué à gérer d'avoir plusieurs moteurs de stockage dans une même base de données.
    En ce qui me concerne, en général dans mes projets j'utilise MyISAM qui se révèle assez souple pour des petits ou moyens projets.

    RépondreRépondre
    Tony , le 10 juillet 2013 à 19:54
  •  

    Bonjour,

    Je me posait une question et je voulais votre avis confirmé pour savoir ce que vous en pensez.

    Serait-il possible (et intéressant) d'avoir une ou plusieurs tables en InnoDB pour nos insertions, UPDATE etc. (pour gérer notre DATA en gros)
    Et d'utiliser une table comme vue matérialisée en MyISAM qui serait alimentée par des TRIGGER sur le InnoBD afin d'effectuer nos SELECT plus rapidement ?

    RépondreRépondre
    Adrien , le 13 janvier 2014 à 13:52
  •  

    @Adrien : C'est exactement la question que je me posais à la lecture de cet article!
    Est-ce qu'un "pro" a une réponse?

    RépondreRépondre
    denis , le 26 mars 2014 à 15:59
  •  

    Salut, je suis entrein de créer une base MySQL InnoDB mais la personne pour quie je la fait voudrait 1seul fichier comme access ou sqlite. Savez-vous comment faire?

    RépondreRépondre
    Bbabagreen , le 12 mai 2015 à 16:49
  •  

    @Bbabagreen : @Bbabagreen : quand la construction de ta db est terminée, fournit lui un dump (structure + data si utile) de la db, nan ?

    RépondreRépondre
    Nolwennig , le 20 mai 2015 à 14:20
 

Ajouter un commentaire

actualité android apache apple astuce astuces bash bilboblog blog boot chrome clavier commande commandes conky date debian Desktop développement elementary exploit faille fedora firefox flash gimp gnome google graphique Graphisme hack hacking Hardware humour intel internet iphone jailbreak Jeux Kde kernel libre Linux log logiciels Logiciels Libres lucid lynx maemo mail maquette metasploit microsoft mobile mockup monitoring mozilla multi-touch musique mysql n900 nautilus nokia noyau openoffice open source password photos php Planet publicité red hat redhat rpm réseau screenshot script serveur serveurs shell sql ssh statistiques sysadmin system Sécurité thème tux-planet tv twitter ubuntu unity vidéo vidéos vlc voyage wallpaper windows wordpress yum