Résumé : MySQL dispose de plusieurs moteurs de stockage, chacun avec ses propres avantages et inconvénients. Vous pouvez choisir le meilleur à utiliser : MyISAM, InnoDB, MERGE, MEMORY(HEAP), BDB(BerkeleyDB), EXAMPLE, FEDERATED, ARCHIVE, CSV, BLACKHOLE.

MySQL dispose de plusieurs moteurs de stockage, chacun avec ses propres avantages et inconvénients. Vous pouvez choisir le meilleur à utiliser :

MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE.

MySQL prend en charge plusieurs moteurs de stockage comme gestionnaires pour différents types de tables. Les moteurs de stockage MySQL comprennent des moteurs qui gèrent les tables transactionnelles et des moteurs qui gèrent les tables non transactionnelles :

• MyISAM gère les tables non transactionnelles. Il offre un stockage et une récupération à grande vitesse, ainsi que des capacités de recherche en texte intégral. MyISAM est pris en charge dans toutes les configurations MySQL et est le moteur de stockage par défaut, sauf si vous configurez MySQL pour utiliser un autre moteur par défaut.

• Le moteur de stockage MEMORY fournit des tables "en mémoire". Le moteur de stockage MERGE permet de traiter une collection de tables MyISAM traitées de manière identique comme une seule table. Comme MyISAM, les moteurs de stockage MEMORY et MERGE gèrent les tables non transactionnelles, et les deux moteurs sont inclus par défaut dans MySQL.

  Remarque : le moteur de stockage MEMORY était anciennement appelé moteur HEAP.

• Les moteurs de stockage InnoDB et BDB fournissent des tables transactionnelles. BDB est inclus dans les distributions binaires MySQL-Max publiées pour les systèmes d'exploitation qui le prennent en charge. InnoDB est également inclus par défaut dans toutes les distributions binaires MySQL 5.1. Vous pouvez configurer MySQL pour activer ou désactiver l'un ou l'autre moteur selon votre préférence.

• Le moteur de stockage EXAMPLE est un moteur « stub » qui ne fait rien. Vous pouvez créer des tables avec ce moteur, mais aucune donnée n'y est stockée ni récupérée. Le but de ce moteur est de servir d'exemple dans le code source de MySQL, en démontrant comment commencer à écrire un nouveau moteur de stockage. De même, son intérêt principal est pour les développeurs.

• NDB Cluster est le moteur de stockage utilisé par MySQL Cluster pour implémenter des tables partitionnées sur plusieurs ordinateurs. Il est fourni dans les distributions binaires MySQL-Max 5.1. Ce moteur de stockage n'est actuellement pris en charge que sur Linux, Solaris et Mac OS X. Dans les futures distributions MySQL, nous prévoyons d'ajouter la prise en charge de ce moteur sur d'autres plateformes, y compris Windows.

• Le moteur de stockage ARCHIVE est utilisé pour stocker de grandes quantités de données sans index, avec une très faible empreinte de stockage.

• Le moteur de stockage CSV stocke les données dans des fichiers texte au format séparé par des virgules.

• Le moteur de stockage BLACKHOLE accepte mais ne stocke pas les données, et la récupération renvoie toujours un ensemble vide.

• Le moteur de stockage FEDERATED stocke les données dans une base de données distante. Dans MySQL 5.1, il ne fonctionne qu'avec MySQL, en utilisant l'API client C de MySQL. Dans les futures distributions, nous prévoyons de le faire se connecter à d'autres sources de données en utilisant d'autres pilotes ou méthodes de connexion client.

Les plus couramment utilisés sont MyISAM et InnoDB

Comment choisir entre les moteurs de stockage MySQL MyISAM et InnoDB ?

Bien que les moteurs de stockage dans MySQL ne soient pas seulement MyISAM et InnoDB, ces deux-là sont les plus couramment utilisés. Certains webmestres n'ont peut-être pas prêté attention aux moteurs de stockage de MySQL. En fait, le moteur de stockage est aussi un point important dans la conception d'une base de données. Alors, quel moteur de stockage un système de blog devrait-il utiliser ?

Ci-dessous, nous examinons les différences entre les deux moteurs de stockage.

• Premièrement, InnoDB prend en charge les transactions, tandis que MyISAM ne le fait pas. C'est très important. Une transaction est une méthode de traitement avancée. Par exemple, dans une série d'insertions, de suppressions et de modifications, si une erreur se produit, elle peut être annulée et restaurée, mais MyISAM ne peut pas le faire.
• Deuxièmement, MyISAM convient aux applications axées sur les requêtes et les insertions, tandis qu'InnoDB convient aux applications avec des modifications fréquentes et des exigences de sécurité plus élevées.
• Troisièmement, InnoDB prend en charge les clés étrangères, tandis que MyISAM ne le fait pas.
• Quatrièmement, le moteur de stockage par défaut des versions de MySQL antérieures à 5.1 est MyISAM, et pour les versions postérieures à 5.1, c'est InnoDB.
• Cinquièmement, InnoDB ne prend pas en charge les index de type FULLTEXT.
• Sixièmement, InnoDB ne sauvegarde pas le nombre de lignes d'une table. Par exemple, lors de l'exécution de select count(*) from table, InnoDB doit parcourir toute la table pour calculer combien de lignes il y a, mais MyISAM peut simplement lire le nombre de lignes sauvegardé. Notez que lorsque l'instruction count(*) inclut une condition WHERE, MyISAM doit également parcourir toute la table.
• Septièmement, pour les champs auto-incrémentés, InnoDB doit contenir un index qui inclut uniquement ce champ, mais dans les tables MyISAM, un index composite peut être créé avec d'autres champs.
• Huitièmement, lors du vidage de toute la table, InnoDB supprime ligne par ligne, ce qui est très inefficace. MyISAM reconstruira plutôt la table.
• Neuvièmement, InnoDB prend en charge le verrouillage au niveau des lignes (dans certains cas, il verrouille encore toute la table, commeupdate table set a=1 where user like '%lee%'

Sur la base des neuf différences ci-dessus et des caractéristiques des blogs personnels, il est recommandé que les systèmes de blogs personnels utilisent MyISAM, car dans un blog les principales opérations sont la lecture et l'écriture, et il y a peu d'opérations en chaîne. Par conséquent, choisir le moteur MyISAM rend l'efficacité d'ouverture des pages de votre blog supérieure à celle des blogs utilisant le moteur InnoDB. Bien sûr, ce n'est qu'une suggestion personnelle ; la plupart des blogs devraient encore choisir attentivement en fonction des circonstances réelles.

Quelques remarques sur le choix entre MyISAM et InnoDB :

MYISAM et InnoDB sont deux moteurs de stockage fournis par la base de données MySQL. Chacun a ses propres avantages et inconvénients. InnoDB prend en charge des fonctionnalités avancées de base de données relationnelle telles que les transactions et le verrouillage au niveau des lignes, que MyISAM ne prend pas en charge. MyISAM offre de meilleures performances et occupe moins d'espace de stockage. Par conséquent, le choix du moteur de stockage dépend de l'application spécifique.

Si votre application nécessite absolument des transactions, vous devez sans aucun doute choisir le moteur InnoDB. Notez toutefois que le verrouillage au niveau des lignes d'InnoDB est conditionnel. Lorsque la condition WHERE n'utilise pas de clé primaire, la table entière sera tout de même verrouillée. Par exemple, une instruction DELETE telle que DELETE FROM mytable.

Si votre application a des exigences élevées en matière de performances de requête, vous devriez utiliser MyISAM. MyISAM sépare les index des données, et ses index sont compressés, ce qui permet une meilleure utilisation de la mémoire. Par conséquent, ses performances de requête sont nettement meilleures que celles d'InnoDB. Les index compressés permettent également d'économiser un peu d'espace disque. MyISAM prend en charge l'indexation en texte intégral, ce qui peut grandement optimiser l'efficacité des requêtes LIKE.

Certains disent que MyISAM ne peut être utilisé que pour de petites applications, mais ce n'est en réalité qu'un préjugé. Si le volume de données est relativement important, cela devrait être résolu en faisant évoluer l'architecture, par exemple par le partitionnement de tables et le sharding de base de données, plutôt qu'en se reposant uniquement sur le moteur de stockage.

Quelques autres remarques :

De nos jours, on choisit généralement InnoDB. La raison principale est que MyISAM utilise un verrouillage au niveau de la table, ce qui entraîne des problèmes de sérialisation lecture-écriture et des conflits de verrous de table, avec pour conséquence une faible efficacité de concurrence. MyISAM n'est généralement pas choisi pour les applications intensives en lecture-écriture.

Concernant le moteur de stockage par défaut de la base de données MySQL :

MyISAM et InnoDB sont deux moteurs de stockage de MySQL. S'il s'agit d'une installation par défaut, ce devrait être InnoDB. Vous pouvez trouver default-storage-engine=INNODB dans le fichier my.ini. Bien sûr, vous pouvez spécifier le moteur de stockage correspondant lors de la création d'une table. Vous pouvez voir les informations pertinentes en exécutant show create table xx.

Comparaison d'InnoDB et MyISAM dans MySQL

MyISAM：

Chaque table MyISAM est stockée sur disque sous forme de trois fichiers. Le nom du premier fichier commence par le nom de la table, et l'extension indique le type de fichier. Le fichier .frm stocke la définition de la table. Le fichier de données a l'extension .MYD (MYData).

Les tables MyISAM peuvent être compressées et elles prennent en charge la recherche en texte intégral. Elles ne prennent pas en charge les transactions ni les clés étrangères. Si une transaction est annulée, cela provoquera une annulation incomplète et un manque d'atomicité. Lors de l'exécution d'UPDATE, un verrou de table est utilisé, donc la concurrence est relativement faible. Si vous exécutez un grand nombre d'instructions SELECT, MyISAM est un meilleur choix.

MyISAM sépare les index des données, et les index sont compressés, ce qui améliore considérablement l'utilisation de la mémoire. Davantage d'index peuvent être chargés. InnoDB, en revanche, lie étroitement les index et les données sans compression, ce qui rend InnoDB nettement plus volumineux que MyISAM.

MyISAM met en cache les index en mémoire, et non les données. InnoDB met en cache les données en mémoire. Relativement parlant, plus la mémoire du serveur est grande, plus l'avantage qu'InnoDB peut exploiter est important.

Avantages :L'interrogation des données est relativement rapide, adaptée à un grand nombre d'instructions SELECT, et prend en charge l'indexation en texte intégral.

Inconvénients :Ne prend pas en charge les transactions, ne prend pas en charge les clés étrangères, a une faible concurrence et ne convient pas à un grand nombre d'instructions UPDATE.

InnoDB：

Ce type est sécurisé pour les transactions. Il présente les mêmes caractéristiques que le type BDB, et tous deux prennent également en charge les clés étrangères. Les tables InnoDB sont très rapides. Elles offrent des fonctionnalités encore plus riches que BDB, donc si vous avez besoin d'un moteur de stockage sécurisé pour les transactions, il est recommandé de l'utiliser. Le verrouillage au niveau des lignes est utilisé lors des UPDATE, donc la concurrence est relativement élevée. Si vos données impliquent un grand nombre d'opérations INSERT ou UPDATE, vous devez utiliser des tables InnoDB pour des raisons de performance.

Avantages :Prend en charge les transactions, prend en charge les clés étrangères, a une concurrence élevée et convient à un grand nombre d'instructions UPDATE.

Inconvénients :L'interrogation des données est relativement rapide, mais ne convient pas à un grand nombre d'instructions SELECT.

Pour les tables InnoDB qui prennent en charge les transactions, la principale raison affectant la vitesse est que le paramètre par défaut AUTOCOMMIT est activé, et que le programme n'appelle pas explicitement BEGIN pour démarrer une transaction, ce qui fait que chaque ligne insérée est automatiquement validée, ce qui affecte gravement la vitesse. Vous pouvez appeler BEGIN avant d'exécuter du SQL, et plusieurs instructions SQL forment une seule transaction (même si autocommit est activé), ce qui améliorera grandement les performances.

Les différences fondamentales sont :Le type MyISAM ne prend pas en charge les traitements avancés tels que le traitement des transactions, tandis que le type InnoDB le prend en charge.

Les tables de type MyISAM mettent l'accent sur les performances, et leur vitesse d'exécution est plus rapide que celle des tables de type InnoDB, mais elles ne fournissent pas de prise en charge des transactions. InnoDB fournit la prise en charge des transactions et des fonctionnalités de base de données avancées telles que les clés étrangères.

Autres comparaisons :

MyIASM est une nouvelle version de la table ISAM, avec les extensions suivantes :

• Portabilité au niveau binaire.
• Indexation des colonnes NULL.
• Moins de fragmentation pour les lignes de longueur variable que les tables ISAM.
• Prend en charge les fichiers volumineux.
• Meilleure compression des index.
• Meilleure distribution statistique des clés.
• Gestion d'auto_increment meilleure et plus rapide.

Voici quelques différences dans les détails et l'implémentation spécifique :

• 1. InnoDB ne prend pas en charge les index de type FULLTEXT.
• 2. InnoDB ne stocke pas le nombre exact de lignes d'une table. C'est-à-dire que lors de l'exécution de select count(*) from table, InnoDB doit parcourir toute la table pour calculer le nombre de lignes, tandis que MyISAM n'a qu'à lire le nombre de lignes enregistré. Notez que lorsque l'instruction count(*) inclut une condition WHERE, l'opération sur les deux tables est la même.
• 3. Pour les champs AUTO_INCREMENT, InnoDB doit inclure un index contenant uniquement ce champ, mais dans les tables MyISAM, un index composite peut être créé avec d'autres champs.
• 4. Lors de l'exécution de DELETE FROM table, InnoDB ne reconstruit pas la table ; il supprime les lignes une par une.
• 5. L'opération LOAD TABLE FROM MASTER ne fonctionne pas sur InnoDB. La solution consiste à convertir d'abord la table InnoDB en table MyISAM, importer les données, puis la reconvertir en table InnoDB. Toutefois, cela ne s'applique pas aux tables qui utilisent des fonctionnalités InnoDB supplémentaires (comme les clés étrangères).

De plus, le verrouillage au niveau des lignes d'InnoDB n'est pas absolu. Si MySQL ne peut pas déterminer la plage à parcourir lors de l'exécution d'une instruction SQL, les tables InnoDB verrouilleront également toute la table, par exemple : update table set num=1 where name like "%aaa%".

Aucun type de table n'est omnipotent. Ce n'est qu'en choisissant de manière appropriée le type de table en fonction du type d'activité que les avantages de performance de MySQL peuvent être maximisés.

Comparaison des mises à jour entre InnoDB et MyISAM :

L'organisation des données d'InnoDB est un arbre B+ construit en fonction de la clé primaire. Si aucune clé primaire n'est explicitement définie, InnoDB choisira une clé NOT NULL UNIQUE comme clé primaire. S'il n'y en a toujours pas, InnoDB créera une clé primaire de 6 octets. L'index de clé primaire pointe vers une page, et non vers une position de ligne spécifique.

Une clé primaire non auto-incrémentée rend l'insertion très lente, par exemple en utilisant un numéro de téléphone portable ou un numéro de carte d'identité comme clé primaire, alors faites bon usage de AUTO_INCREMENT.

Une grande table n'est pas effrayante ; ce qui est effrayant, c'est COUNT ou un LIMIT avec un offset élevé. Vous pouvez remplacer un grand LIMIT big par LIMIT max_id, xxxxx.

Limit 0 1000 | limit 1001 1000 | limit 2001 1000

Limit 0 1000 | where id>max_id1 limit 1000 | where id>max_id2 limit 1000

Pour InnoDB, partitionner une table selon une certaine colonne en espérant améliorer les performances sur un seul serveur n'a aucun sens.

La vitesse d'insertion et la vitesse de requête sont parfois des contradictions inconciliables.

Il est faux de dire qu'InnoDB n'est pas adapté à COUNT. MyISAM est tout aussi lent. C'est simplement que MyISAM met en cache le nombre de lignes de toute la table, donc compter toute la table est rapide. S'il y a une condition de requête et que ce n'est pas une requête sur clé primaire, il n'y a pas de différence. La raison pour laquelle le COUNT sur clé primaire est lent est qu'InnoDB est organisé par clé primaire. Lorsqu'on compte par clé primaire, les données sont chargées.

Le stockage paginé d'InnoDB facilite la mise en cache de tables entières et les sauvegardes à chaud.

Si une table possède de nombreux index, alors la vitesse de mise à jour d'InnoDB est supérieure à celle de MyISAM. Parce que les index secondaires d'InnoDB sont associés à la clé primaire de la table, qui est une valeur logique, tandis que tous les index de MyISAM sont associés à l'emplacement physique des données. Lors des mises à jour, l'emplacement physique des données peut changer. S'il change, tous les index doivent être mis à jour.

InnoDB ne stocke pas le nombre exact de lignes d'une table. C'est-à-dire que lors de l'exécution de select count(*) from table, InnoDB doit parcourir toute la table pour calculer le nombre de lignes, tandis que MyISAM n'a qu'à lire le nombre de lignes enregistré. Notez que lorsque l'instruction count(*) inclut une condition WHERE, l'opération sur les deux tables est la même.

Adresse d'origine : https://my.oschina.net/junn/blog/183341