Table des matières de l'article :
Si vous cherchez à améliorer les performances des bases de données MySQL dans votre logiciel, vous devrez peut-être connaître toutes les différences entre InnoDB et MyISAM, deux types bien connus de moteurs de stockage MySQL. Et si vous allez en choisir un, nous pensons que vous aimeriez savoir ce que chacun d'eux a à offrir et lequel correspond le mieux à votre logiciel.
Chaque aspect technique détermine le succès d'un projet. Avec le besoin croissant d'archiver toutes les données, le choix d'une base de données capable de contrôler avec succès toutes les exigences d'archivage, même à long terme, est de la plus haute importance. Lorsqu'un architecte de solutions se voit confier la responsabilité de choisir une base de données, il compare ces bases de données entre elles et choisit celle qui lui convient le mieux.
Dans ce blog, nous parlerons des deux moteurs de stockage les plus populaires des bases de données MySQL : InnoDB et MyISAM. Nous discuterons de ce qu'ils sont et comprendrons MyISAM vs InnoDB en les comparant à 7 facteurs critiques.
Comprendre ce qu'est MySQL
MySQL est un RDBMS open source populaire (système de gestion de base de données relationnelle)
Il est largement utilisé par les universitaires et les professionnels du monde entier. MySQL est disponible gratuitement sous la licence publique GNU et est également connu comme une version propriétaire premium. Michael Widenius a initialement développé MySQL chez MySQL AB, une société basée en Suède. En 2012, Sun Microsystems a acquis MySQL AB et plus tard Oracle a acquis Sun Microsystems.
MySQL est utilisé pour diverses applications et est basé sur le langage de requête structuré (SQL). Il est également utilisé par certains sites Web populaires, notamment Twitter, Facebook, Mediawiki, YouTube et Flickr, etc.
MySQL a eu de nombreux forks dont les plus populaires sont MariaDB et Percona Server.
Comprendre les principales fonctionnalités de MySQL
- Facilité d'utilisation: MySQL est facile à télécharger, à installer et à utiliser. Vous pouvez simplement accéder aux téléchargements depuis le site officiel et suivre les instructions détaillées fournies dans leur documentation.
- Évolutivité : MySQL a une architecture évolutive et fournit des utilitaires de chargement rapide hautes performances avec différents caches mémoire.
- Compatibilité: MySQL est compatible et peut être installé sur toutes les plates-formes modernes telles que Windows, Linux, Unix.
- Sécurité des données: MySQL possède toutes les fonctionnalités qui garantissent la sécurité de la base de données et n'autorise l'accès qu'aux utilisateurs autorisés.
- Basse coût : c'est gratuit. N'importe qui dans le monde entier peut télécharger gratuitement MySQL pour un usage personnel.
Qu'est-ce que MyISAM ?
MyISAM est l'abréviation de My Indexed Sequential Access Method; MyISAM est le système de stockage par défaut et est souvent utilisé dans les environnements Web, d'entreposage de données et autres environnements d'analyse. Le moteur de stockage MyISAM prend en charge toutes les configurations MySQL.
MyISAM était un moteur de stockage par défaut jusqu'en décembre 2009 ; plus tard, InnoDB a remplacé le moteur de stockage par défaut. MyISAM est basé sur l'algorithme ISAM qui affiche plus rapidement le résultat d'ensembles de données plus volumineux. MyISAM a une empreinte de données minimale et convient donc mieux à l'entreposage de données et aux applications Web.
Qu'est-ce qu'InnoDB ?
InnoDB est un moteur de stockage de transactions sécurisées (conforme ACID) pour MySQL avec des fonctionnalités telles que Commit, Rollback et Crash Recovery pour protéger les données des utilisateurs et fournir une tolérance aux pannes. InnoDB utilise le verrouillage au niveau des lignes et le non-verrouillage cohérent de style Oracle sur les lectures augmente la simultanéité et les performances multi-utilisateurs.
InnoDB stocke les données utilisateur dans des index clusterisés basés sur des clés primaires afin de réduire les E/S pour les requêtes courantes. Pour maintenir l'intégrité des données, InnoDB prend également en charge les contraintes d'intégrité référentielle FOREIGN KEY.
Avec l'introduction de MySQL 5.5, InnoDB est devenu le moteur de stockage par défaut et convient le mieux aux grands ensembles de données contenant des données structurées et relationnelles. InnoDB se concentre davantage sur la fiabilité et les performances, ce qui le rend idéal pour les systèmes de gestion de contenu.
MyISAM contre InnoDB
Maintenant que nous avons une compréhension de base de MyISAM et InnoDB, comparons-les sur divers facteurs :
- MyISAM vs InnoDB : type de moteur de stockage
- MyISAM vs InnoDB : bloquer
- MyISAM vs InnoDB : clés étrangères et intégrité référentielle
- MyISAM vs InnoDB : propriété ACID
- MyISAM vs InnoDB : performances
- MyISAM vs InnoDB : fiabilité
- MyISAM vs InnoDB : mise en cache et indexation
1. MyISAM vs InnoDBstorage : type de moteur
MyISAM il s'agit d'un type de stockage non transactionnel et toutes les options d'écriture doivent être restaurées manuellement (si nécessaire).
InnoDB est un type de stockage de transaction qui annule automatiquement les écritures si elles ne sont pas terminées.
2. Stockage MyISAM vs InnoDB : verrouillage
Le verrouillage est le mécanisme de MySQL qui empêche deux utilisateurs de modifier des lignes en double en même temps en verrouillant la ligne. Les utilisateurs ne peuvent pas modifier la table lorsque le verrouillage est activé.
MyISAM utilise la méthode par défaut de verrouillage de table et autorise une seule session à modifier la table. Cela signifie qu'un seul utilisateur peut modifier le tableau à la fois. Si un autre utilisateur essaie de modifier la table, il recevra un message indiquant qu'elle est verrouillée. La méthode de verrouillage de table est utile pour les bases de données en lecture seule car elle ne nécessite pas beaucoup de mémoire.
InnoDB utilise le verrouillage au niveau de la ligne de la table. Cette méthode prend en charge plusieurs sessions sur la même ligne en verrouillant uniquement les lignes dans le processus d'édition. Le verrouillage de ligne est utile pour les bases de données avec plusieurs utilisateurs.
Le seul inconvénient du verrouillage au niveau des lignes est qu'il consomme beaucoup de mémoire et que l'interrogation et la modification des données prennent du temps.
Lorsqu'une requête est exécutée sur une table MyISAM, toute la table dans laquelle la requête est exécutée sera verrouillée. Cela signifie que les requêtes suivantes ne seront exécutées qu'une fois la requête en cours terminée. Si vous lisez une grande table et/ou qu'il y a des lectures et des écritures fréquentes, cela peut signifier un énorme arriéré de requêtes.
Lorsqu'une requête est exécutée sur une table InnoDB, seules les lignes concernées sont verrouillées, le reste de la table reste disponible pour d'autres opérations. Cela signifie que les requêtes peuvent s'exécuter simultanément sur la même table, tant qu'elles n'utilisent pas la même ligne.
3. Stockage MyISAM vs InnoDB : clés étrangères ou clés étrangères et intégrité référentielle.
Une clé étrangère est une colonne d'une table qui relie des données à une autre table. Empêche les utilisateurs d'ajouter des enregistrements qui rompent le lien entre deux tables.
MyISAM ne prend pas en charge l'option Clé étrangère.
InnoDB prend en charge l'option Clé étrangère.
L'intégrité référentielle garantit que les relations entre les tables restent cohérentes. Ou plus généralement une table a une clé étrangère qui pointe vers une autre table. Lorsqu'une modification est apportée au tableau à puces, les modifications seront également restreintes / liées au tableau de liens.
InnoDB est un SGBD relationnel (RDBMS) et a donc une intégrité référentielle, contrairement à MyISAM. Ainsi, InnoDB prend en charge les clés étrangères et l'intégrité référentielle, y compris les suppressions et les mises à jour en cascade, mais MyISAM ne prend pas en charge les contraintes de clé étrangère.
Ainsi, lorsque vous concevez une base de données MySQL et que vous devez définir des contraintes d'intégrité entre les tables, InnoDB est votre tasse de café. (Bien sûr, vous pouvez avoir le moteur MyISAM pour les tables dont vous n'avez pas besoin pour définir des contraintes d'intégrité référentielle.)
4. Stockage MyISAM vs InnoDB : propriétés ACID
ACID signifie Atomicité, Cohérence, Isolation et Durabilité. MyISAM ne prend pas en charge les propriétés ACID tandis qu'InnoDB prend en charge les propriétés ACID.
Les données d'une table sont gérées à l'aide d'instructions DML (Data Manipulation Language), telles que SELECT, INSERT, UPDATE et DELETE. Une transaction regroupe deux ou plusieurs instructions DML dans une seule unité de travail, de sorte que l'unité entière ou aucune n'est appliquée.
MyISAM ne prend pas en charge les transactions alors qu'InnoDB le fait.
Par conséquent, si une table utilise le moteur MyISAM et que l'opération est abandonnée, l'opération est immédiatement abandonnée et les lignes affectées (ou même les données de chaque ligne) restent affectées, même si l'opération n'est pas terminée. . Si une table utilise le moteur InnoDB et que l'opération est abandonnée, car elle utilise des transactions qui ont une atomicité, toutes les transactions qui ne se sont pas terminées n'auront aucun effet car aucune validation n'est effectuée.
Lorsque vous effectuez une opération dans MyISAM, les modifications sont définies et vous ne pouvez pas annuler les modifications alors que dans InnoDB, vous pouvez annuler ces modifications.
Lorsque vous travaillez sur une application qui utilise beaucoup de transactions, InnoDB serait une meilleure option que MyISAM.
InnoDB fournit également une récupération automatique après un crash du serveur MySQL ou de l'hôte sur lequel le serveur est exécuté.
5. Stockage MyISAM vs InnoDB : performances
InnoDB prend en charge les propriétés transactionnelles, c'est-à-dire la restauration et la validation, et a une vitesse d'écriture plus élevée. Les performances d'InnoDB pour les gros volumes de données sont meilleures que MyISAM.
MyISAM il ne prend pas en charge les propriétés transactionnelles et est plus rapide à lire. Par rapport à InnoDB, les performances pour un volume de données élevé sont inférieures.
6. Stockage MyISAM vs InnoDB : fiabilité
InnoDB il utilise un journal transactionnel pour enregistrer chaque opération et fournit ainsi des opérations fiables. Par conséquent, en cas d'erreur, les données peuvent être récupérées rapidement à l'aide de ces journaux.
MyISAM n'offre pas l'intégrité des données ; les pannes matérielles et les opérations annulées peuvent entraîner une corruption des données.
7. Stockage MyISAM vs InnoDB : mise en cache et indexation
InnoDB ne prend pas en charge l'indexation de texte intégral jusqu'à la version 5.6.4 de MySQL. Bien qu'il s'agisse d'une ancienne version, certaines applications utilisent toujours les mêmes versions ou des versions plus anciennes de MySQL (en particulier les fournisseurs d'hébergement mutualisé). Cependant, ce n'est pas une raison valable pour utiliser MyISAM. Il est préférable de passer à un fournisseur d'hébergement qui prend en charge les versions mises à jour de MySQL comme la nôtre.
De plus, une table MyISAM qui utilise l'indexation FULLTEXT ne peut pas être convertie en table InnoDB.
Comment vérifier si vous utilisez MyISAM ou InnoDB
Si vous ne savez pas quel moteur de stockage est actuellement utilisé dans votre base de données MySQL, vérifions-le et voyons. La première consiste à démarrer MySQL Shell et à exécuter la commande, puis à localiser et à sélectionner la base de données requise avec la commande.SHOW DATABASES;USE database_name;
Ensuite, un message de confirmation s'affichera indiquant que la base de données a été trouvée. Vous pouvez maintenant utiliser la commande SHOW CREATE TABLE pour afficher des informations sur la table et le moteur de stockage. Il ressemble à ceci :
SHOW CREATE TABLE database_name.table_name;
La sortie affichera le moteur de stockage par défaut pour la table demandée.
Il existe un moyen plus simple de le faire si vous utilisez PhpMyAdmin pour MySQL, il suffit de regarder les différentes tables.
Quand est-il préférable d'utiliser MyISAM et quand utiliser InnoDB
Si une table MyISAM a un problème, cela n'affectera que la table. Si des problèmes surviennent avec d'autres tables ou bases de données, ils n'affecteront pas les opérations des autres tables ou bases de données. Par conséquent, MyISAM peut être recommandé pour les serveurs avec plusieurs sites.
Le moteur de stockage InnoDB est recommandé pour les opérations de table fréquentes. Il offre de meilleures performances, de meilleures fonctionnalités et peut également réduire considérablement l'utilisation de la mémoire sur le serveur. Bien sûr, il est généralement préféré à MyISAM. Cependant, il convient de noter que, puisque des problèmes avec les tables InnoDB peuvent entraîner une perte de données dans d'autres tables InnoDB, il est important de s'assurer que vous avez activé les sauvegardes automatiques.
Dépassant une simple logique théorique ou académique pour une utilisation dans un contexte web comme une utilisation dans un hébergement WordPress, ou un ecommerce comme Prestashop ou Magento, il est pratiquement toujours recommandé d'utiliser InnoDB au lieu de MyISAM.
Comment convertir MyISAM en InnoDB
La conversion de MyISAM en InnoDB peut être très utile si, par exemple, vous rencontrez une table MyISAM précédente qui doit être convertie pour correspondre à votre environnement InnoDB actuel.
Comment convertir toutes les tables MyISAM en InnoDB
Si vous souhaitez convertir toutes les tables en une base de données requise, vous pouvez le faire en toute sécurité via phpMyAdmin.
1. Une fois la base de données requise démarrée et sélectionnée, vous pouvez y exécuter la requête suivante, après avoir remplacé nom de la base de données avec le nom réel de la base de données :
SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='MyISAM' AND table_schema = 'database_name';
Par exemple, voici à quoi cela ressemblera avec la base de données sakila :
2. Après avoir soumis cette requête, vous verrez la liste de toutes les tables à convertir en InnoDB.
3. Cliquez sur + Choix au-dessus des résultats, sélectionnez Textes complets et cliquez Allez-y dans le coin inférieur droit de l'écran.
4. Cochez la case Afficher tout et copier toutes les requêtes à l'aide de Copier dans le presse-papier dans la boite Opérations sur les résultats de la requête .
5. Collez les résultats dans l'éditeur de texte et copiez toutes les lignes commençant par ALTER TABLE dans le presse-papiers.
6. Cliquez sur l'onglet SQL au-dessus des résultats et collez les instructions ALTER TABLE dans le champ de texte, puis cliquez à nouveau Aller . Toutes les tables de votre base de données seront converties en InnoDB.
Comment convertir une seule table MyISAM en InnoDB
Si vous souhaitez convertir une seule table de MyISAM en InnoDB, exécutez la commande ALTER TABLE, après avoir remplacé nom de la base de données e nom_table avec les noms réels de la base de données et de la table :
ALTER TABLE database_name.table_name ENGINE=InnoDB;
conclusion
Cet article a répertorié les différences critiques entre InnoDB et MyISAM en les comparant à des facteurs critiques. Il est important de connaître les propriétés de chaque base de données car il devient plus facile de faire un choix. Vous devez être conscient des similitudes qu'ils possèdent et aussi de ce qui les différencie. J'espère qu'après avoir lu cet article, vous aurez une meilleure compréhension d'InnoDB et de MyISAM.
Si, comme nous l'imaginons, vous êtes dans ce blog alors que vous travaillez avec des CMS ou des plates-formes de commerce électronique populaires telles que WordPress / Drupal / Joomla / Prestashop ou Magento, le conseil le plus impartial que nous puissions vous donner pour améliorer les performances de votre site est de convertir tous les tables MyISAM vers InnoDB.
Si vous rencontrez des difficultés ou souhaitez déléguer une opération lourde ou dangereuse à première vue, merci de nous contacter.
