MyISAM vs InnoDB :une comparaison

Présentation

Choisir le bon moteur de stockage est une décision stratégique cruciale qui a un impact sur le développement futur. En fonction de votre cas d'utilisation, choisissez entre deux principaux moteurs de stockage pour MySQL :MyISAM ou InnoDB .

Dans cet article, vous apprendrez les principales différences entre les moteurs de stockage MyISAM et InnoDB, comment vérifier quel moteur de stockage vous utilisez et comment le convertir.

Prérequis

Accès au terminal/ligne de commande
Une base de données MySQL fonctionnelle
phpMyAdmin installé

Que sont MyISAM et InnoDB

MyISAM et InnoDB sont des moteurs de stockage MySQL. Les moteurs de stockage sont des composants du système de gestion de base de données utilisés pour manipuler les données de la base de données.

MonISAM

MyISAM signifie Indexed Sequential Access Method. C'était le moteur de stockage par défaut de MySQL jusqu'en décembre 2009. Avec la sortie de MySQL 5.5, MyISAM a été remplacé par InnoDB.

MyISAM est basé sur un algorithme ISAM qui affiche rapidement des informations provenant de grands ensembles de données. Il a une faible empreinte de données et convient mieux aux entrepôts de données et aux applications Web.

InnoDB

InnoDB est le moteur de stockage par défaut de MySQL depuis la sortie de MySQL 5.5. Il convient mieux aux grandes bases de données contenant des données relationnelles.

InnoDB se concentre sur une fiabilité et des performances élevées, ce qui le rend idéal pour les systèmes de gestion de contenu. L'une des utilisations les plus connues d'InnoDB est le logiciel MediaWiki qui alimente Wikipedia.

Différences principales entre MyISAM et InnoDB

Examinons les principales différences entre MyISAM et InnoDB.

Fonctionnalités	MonISAM	InnoDB
Type	Non transactionnel	Transactionnel
Verrouillage	Verrouillage de table	Verrouillage au niveau de la ligne
Clés étrangères	Non	Oui
Table, index et stockage de données	Trois fichiers distincts (.frm, .myd et .myi)	Tablespace
Conçu pour	Vitesse	Performances
ACIDE	Non	Oui

Type de moteur de stockage

Il existe deux types de moteurs de stockage, selon la méthode de restauration :

Non transactionnel – les options d'écriture doivent être annulées manuellement.
Transactionnel – les options d'écriture sont annulées automatiquement si elles ne se terminent pas.

Résumé : MyISAM est un moteur de stockage non transactionnel, tandis qu'InnoDB est un moteur de stockage de type transactionnel.

Verrouillage

Le verrouillage dans MySQL est une option qui empêche deux utilisateurs ou plus de modifier des données en même temps. L'utilisateur ne peut pas modifier les données lorsque l'option de verrouillage est activée. Cette fonctionnalité préserve la validité de toutes les données.

MonISAM utilise le verrouillage de table comme méthode de verrouillage par défaut. Il permet à une seule session de modifier des tables à la fois. Les tables sont toujours verrouillées dans le même ordre. La méthode de verrouillage de table convient mieux aux bases de données en lecture seule qui ne nécessitent pas beaucoup de mémoire.

Exemple de verrouillage de table :

Requêtes	Colonne 1	Colonne 2	Colonne 3
Requête 1 (mise à jour)	Écrire	Données	Données
Requête 2 (attendre)	Données	Données	Données
Requête 3 (attendre)	Données	Données	Données

InnoDB utilise le verrouillage au niveau de la ligne comme méthode de verrouillage par défaut. Il prend en charge plusieurs sessions sur des lignes sélectionnées en verrouillant uniquement les lignes dans le processus de modification. Le verrouillage de ligne est plus adapté aux bases de données qui ont plusieurs utilisateurs. L'inconvénient est qu'il nécessite beaucoup de mémoire et prend plus de temps pour interroger et modifier les données.

Exemple de verrouillage au niveau de la ligne :

Requêtes	Colonne 1	Colonne 2	Colonne 3
Requête 1 (mise à jour)	Écrire	Données	Données
Requête 2 (sélectionner)	Lecture	Données	Lecture
Requête 3 (mise à jour)	Données	Écrire	Données

Résumé : MyISAM utilise le verrouillage de table, tandis qu'InnoDB utilise le verrouillage au niveau des lignes comme méthode par défaut.

Clés étrangères

Une clé étrangère est une colonne (ou une collection de colonnes) dans une table qui est liée à une colonne (ou une collection de colonnes) dans une autre table. Il empêche les actions qui détruisent le lien entre les tables et la possibilité d'insérer des données invalides.

Résumé : MyISAM ne prend pas en charge l'option de clé étrangère, contrairement à InnoDB.

Table, index et stockage de données

Les deux moteurs de stockage diffèrent en fonction de la manière dont ils stockent les fichiers.

MonISAM stocke les tables, l'index et les données dans trois fichiers distincts :

.frm – Le format de table contenant la structure de table ou la définition de table.
.myi – Le fichier d'index avec des index pour chaque table.
.myd – Le fichier de données qui contient les données de chaque table.

InnoDB stocke la structure de la table dans le .frm fichier et a un tablespace où il stocke les index et les données.

Résumé : MyISAM stocke les données dans trois fichiers distincts, tandis qu'InnoDB stocke les données dans un seul fichier.

Assistance ACID

ACIDE fait référence aux propriétés de transaction de la base de données :atomicité , cohérence , isolement , et durabilité . Il garantit que la transaction se termine en cas d'erreur ou de défaillance du système.

Reportez-vous à notre article ACID vs. Base pour en savoir plus sur les modèles de transaction de base de données.

Résumé : MyISAM ne prend pas en charge ACID, tandis qu'InnoDB est entièrement conforme à ACID.

Comment vérifier si vous utilisez MyISAM ou InnoDB

L'utilisation du bon moteur de stockage est essentielle pour la manipulation des données. Un mauvais moteur de stockage peut entraîner des erreurs d'interrogation et réduire la vitesse et les performances. Par conséquent, il est crucial de vérifier si MyISAM ou InnoDB sont définis comme moteur de stockage par défaut.

Remarque : Le réglage des performances de MySQL nécessite l'évaluation de nombreux facteurs. Nous vous recommandons d'utiliser InnoDB au lieu de MyISAM pour de meilleures performances.

Vérifier via la ligne de commande

Recherchez des informations sur le moteur de stockage par défaut en suivant les étapes ci-dessous.

1. Ouvrez le terminal et connectez-vous au shell MySQL. Ensuite, affichez une liste de bases de données :

SHOW DATABASES;

2. Localisez la base de données préférée dans la liste et sélectionnez-la :

USE database_name;

Le terminal affiche un message de confirmation du changement de base de données.

3. Ensuite, utilisez le SHOW CREATE TABLE commande pour afficher des informations sur la table et le moteur de stockage :

SHOW CREATE TABLE database_name.table_name;

Dans l'exemple ci-dessous, la sortie répertorie InnoDB comme moteur de stockage par défaut.

Remarque : Remplacez database_name et table_name avec le nom de votre base de données et de votre table.

Vérifier avec phpMyAdmin

Il existe deux façons de vérifier le moteur de stockage par défaut dans phpMyAdmin :

À partir d'une liste de tableaux.
En exécutant une requête.

À partir d'une liste de tableaux

Vous pouvez utiliser une liste de tables pour savoir quelles tables utilisent MyISAM ou InnoDB comme moteur de stockage par défaut.

1. Ouvrez phpMyAdmin et sélectionnez la base de données préférée dans la liste.

2. Dans la liste des tableaux , localisez le Type colonne pour voir les types de moteurs de stockage. Dans notre exemple, les Clients table utilise MyISAM comme moteur de stockage par défaut.

Exécuter une requête

Une autre façon d'afficher un moteur de stockage par défaut consiste à exécuter une requête.

1. Connectez-vous à phpMyAdmin et sélectionnez la base de données préférée dans une liste de bases de données.

2. Sélectionnez le SQL onglet pour accéder aux options de requête.

3. Entrez la commande suivante pour afficher toutes les tables utilisant MyISAM comme moteur de stockage :

SELECT TABLE_NAME, ENGINE FROM information_schema. TABLES WHERE TABLE_SCHEMA = 'database_name' and ENGINE = 'myISAM'

4. Cliquez sur Aller pour exécuter une requête.

Remarque : Remplacez database_name avec le nom de votre base de données.

La sortie affiche une liste de toutes les tables contenant le moteur de stockage MyISAM.

Vous pouvez utiliser la même commande pour rechercher des bases de données à l'aide du moteur de stockage InnoDB. Modifiez la requête en remplaçant ENGINE = 'myISAM' avec ENGINE = 'InnoDB' .

Comment convertir MyISAM et InnoDB

Des problèmes peuvent survenir si vous utilisez InnoDB dans des processus quotidiens, alors qu'une table plus ancienne utilise MyISAM. C'est pourquoi il est essentiel de savoir convertir des tableaux.

Convertir le moteur de stockage via le terminal

Exécutez ALTER TABLE commande dans le shell MySQL pour convertir le moteur de stockage de MyISAM en InnoDB et vice versa.

Pour convertir InnoDB en MyISAM, exécutez :

ALTER TABLE database_name.table_name ENGINE=MyISAM;

Pour convertir MyISAM en InnoDB, exécutez :

ALTER TABLE database_name.table_name ENGINE=InnoDB;

Le terminal imprime un message de confirmation.

Convertir le moteur de stockage à l'aide de phpMyAdmin

Il existe deux manières de convertir le moteur de stockage dans phpMyAdmin :

Utilisation des opérations de table menu.
Exécution d'une requête.

Utilisation du menu des opérations

1. Sélectionnez la base de données préférée dans un menu de base de données.

2. Choisissez une table pour laquelle vous souhaitez modifier le moteur de stockage et sélectionnez Plus menu déroulant.

3. Cliquez sur Opérations pour accéder au menu.

4. Localisez le moteur de stockage réglage dans le tableau options et cliquez sur le moteur de stockage pour charger le menu déroulant contenant divers moteurs de stockage. Sélectionnez InnoDB dans la liste et cliquez sur GO pour enregistrer la modification.

Exécuter une requête

1. Accédez au SQL centre de commande pour la base de données préférée.

2. Exécutez ALTER TABLE commande dans le shell MySQL pour convertir le moteur de stockage.

Pour convertir en MyISAM, exécutez :

ALTER TABLE table_name ENGINE=MyISAM;

Pour convertir en InnoDB, exécutez :

ALTER TABLE.table_name ENGINE=InnoDB;

3. Cliquez sur ALLER bouton pour exécuter la requête.

Vous avez converti avec succès le moteur de stockage.