Les moteurs de stockage de MySQL
Sommaire
- 1- Objectifs
- 2- Présentation
- 3- La commande
SHOW ENGINES; - 4- InnoDB
- 4.1- Présentation d'InnoDB
- 4.2- Exemple d'utilisation
- 4.3- Avantages et inconvénients
- 4.4- Avantages
- 4.5- Inconvénients
- 5- MyISAM
- 5.1- Présentation d'MyISAM
- 5.2- Exemple d'utilisation
- 5.3- Avantages et inconvénients
- 5.4- Avantages
- 5.5- Inconvénients
- 6- La différence clé entre MyISAM et INNODB est la suivante
- 7- Les autres moteurs MySQL
- 7.1- MEMORY
- 7.2- BLACKHOLE
- 7.3- CSV
- 7.4- ARCHIVE
- 7.5- MERGE (aka MRG_MyISAM)
- 7.5.1- Sommaire du cours MySQL
Les moteurs de stockage de MySQL
-
Objectifs
- Connaitre les moteurs de stockage de MySQL
- Différencier les deux moteurs de stockage de MySQL MyISAM et INNODB
-
Présentation
- On appelle moteur de stockage (ou MySQL storage engines en anglais) l’ensemble des algorithmes utilisés par un SGBDR pour stocker les informations et y accéder au moyen d’une requête SQL.
- La plupart des SGBDR proposent un moteur unique, créé pour être le plus efficace possible dans tous les cas. MySQL et ses forks (dont les plus connus sont MariaDB et Drizzle) se démarquent de leurs homologues en proposant à l’administrateur de la base de choisir pour chaque table de sa base quel moteur il désire utiliser.
- Source de l’image : https://hevodata.com/learn/sql-vs-mysql/
- Le schéma ci-dessus répertorie de nombreux exemples de moteurs voici la listes des principaux :
- InnoDB
- MyISAM
- NDBCluster
- Memory
- et plusieurs autres
- MySQL propose de nombreux moteurs pour gérer les tables. Les deux principaux moteurs sont MyISAM, et InnoDB.
- MyISAM est le moteur historique de MySQL, il est d’ailleurs utilisé par défaut.
-
La commande
SHOW ENGINES; - Voyons déjà quels sont les moteurs que MySQL met à notre disposition, ceci au moyen de la commande
SHOW ENGINES;. -
InnoDB
-
Présentation d’InnoDB
- InnoDB est un moteur assez performant faisant partie de la famille des moteurs transactionnels.
- Il ne gère pas les index en FULLTEXT contrairement à MyISAM.
- InnoDB est un moteur relationnel. Il s’assure que les relations entre les données de plusieurs tables sont cohérentes et que si l’on modifie certaines données, que ces changements soient répercutés aux tables liées.
- InnoDB gère le verrouillage des données au niveau de la ligne, contrairement à MyISAM qui les gère au niveau de la table. Ainsi, si vous écrivez des données sur la première ligne de la table, seule celle-ci sera bloquée et toutes les autres seront disponibles en écriture et lecture.
- InnoDB est plus lent que MyISAM, de par sa nature transactionnelle et relationnelle, même si les récentes modifications qu’il a subi pour le passage à la version 5.5 l’ont rendu beaucoup plus performant qu’avant, et ses performances sont désormais très proches de celles de MyISAM. Cependant, la sécurité qu’il apporte quant à la validité des données dans la base en fait un moteur de choix lorsqu’il s’agit de stocker des données relationnelles ou nécessitant des transactions.
-
Exemple d’utilisation
-
Avantages et inconvénients
-
Avantages
- Gestion des relations
- Gestion des transactions
- Verrouillage à la ligne et non à la table
-
Inconvénients
- Plus lent que MyISAM
- Occupe plus de place sur le disque dur
- Occupe plus de place en mémoire vive
-
MyISAM
-
Présentation d’MyISAM
- MyISAM est disponible dans toutes les versions de MySQL à partir de la 3.23.0.
- Pour les versions antérieures à la 5.5, c’est le moteur par défaut de MySQL.
- Il s’agit d’un moteur non transactionnel assez rapide en écriture et très rapide en lecture. Ceci vient en grande partie du fait qu’il ne gère pas les relations ni les transactions et évite donc des contrôles gourmands en ressources mais perd en sûreté ce qu’il gagne en vitesse.
- Il gère l’indexation des contenus, accélérant les recherches, et il est le seul moteur de MySQL permettant de créer des index FULLTEXT sur les champs de type TEXT, rendant les recherches beaucoup plus efficaces qu’avec
LIKE %. -
Exemple d’utilisation
-
Avantages et inconvénients
-
Avantages
- Très rapide en lecture
- Extrêmement rapide pour les opérations COUNT() sur une table entière
- Les index FULLTEXT pour la recherche sur des textes
-
Inconvénients
- Pas de gestion des relations
- Pas de gestion des transactions
- Bloque une table entière lors d’opérations d’insertions, suppressions ou mise à jour des données
-
La différence clé entre MyISAM et INNODB est la suivante
- Les index fulltext est un type d’index particulier qui sert à indexer du contenu texte et ainsi pouvoir faire des recherches à base de mots clés.
-
Les autres moteurs MySQL
- À côté de MyISAM et InnoDB, on trouve toute une série d’autres moteurs qui ont des applications bien spécifiques. Voici une liste des différents moteurs MySQL :
-
MEMORY
- stocke les données non pas dans des fichiers sur le disque dur du serveur MySQL, mais dans la mémoire RAM. Cela permet un gain très important de performance, mais en contrepartie il n’y a pas de persistance des données (quand on arrête le serveur MySQL, on perd toutes les données contenues dans les tables MEMORY. Ce moteur peut être utilisé pour stocker les utilisateurs connectés à un site internet par exemple (ces informations sont par nature éphémères, donc on a pas besoin de persistance).
-
BLACKHOLE
- est comme son nom l’indique un trou noir. C’est une sorte de puits sans fond, rien de tout ce que vous écrirez dans une table blackhole ne sera stocké. À première vue cela n’a aucun intérêt, mais on peut s’en servir pour faire des tests (benchmarks…)
-
CSV
- enregistre les données dans des fichiers CSV (fichier texte avec chaque colonne séparée par un point virgule (;), lisible dans un tableur comme Excel.
-
ARCHIVE
- est spécialement conçu pour enregistrer des données qui ne changent pas, et pour optimiser le stockage en compressant les données de la table. On ne peut faire que des INSERT et des SELECT sur une table archive, pas d’UPDATE. Ce moteur est en général utilisé pour stocker des logs (journaux).
-
MERGE (aka MRG_MyISAM)
- est très proche de MyISAM, sauf qu’il permet de stocker une table dans plusieurs fichiers différents. Cela permet d’améliorer les performances sur de très grosses tables, et de compresser les données anciennes (sur lesquels il n’y aura plus d’écriture) avec myisampack.
.
CREATE TABLE Apprenant(
cin BIGINT NOT NULL PRIMARY KEY auto_increment,
username CHAR(100) UNIQUE NOT NULL,
password CHAR(40) NOT NULL,nom CHAR(100) NOT NULL,
prenom CHAR(100) NOT NULL,
adresse CHAR(200) NOT NULL,
email CHAR(200) NOT NULL,
gouv_id BIGINT NOT NULL REFERENCES gouvernorat (gouv_id) ON UPDATE CASCADE ON DELETE CASCADE
)ENGINE=InnoDB;
CREATE TABLE gouvernorat(
gouv_id BIGINT NOT NULL PRIMARY KEY auto_increment,
gouv_intitule CHAR(20) NOT NULL
)ENGINE=InnoDB;CREATE TABLE Apprenant(
cin BIGINT NOT NULL PRIMARY KEY auto_increment,
username CHAR(100) UNIQUE NOT NULL,
password CHAR(40) NOT NULL,nom CHAR(100) NOT NULL,
prenom CHAR(100) NOT NULL,
adresse CHAR(200) NOT NULL,
email CHAR(200) NOT NULL,
gouv_id BIGINT NOT NULL REFERENCES gouvernorat (gouv_id) ON UPDATE CASCADE ON DELETE CASCADE
)ENGINE=MyIsam;
CREATE TABLE gouvernorat(
gouv_id BIGINT NOT NULL PRIMARY KEY auto_increment,
gouv_intitule CHAR(20) NOT NULL
)ENGINE=MyIsam;| MyISAM | InnoDB |
|---|---|
| ne supporte pas les transactions | Supporte les transactions |
| convient aux petits projets. | est la meilleure option lorsque vous utilisez une base de données plus grande car elle prend en charge les transactions et le volume |
| La vitesse de performance de MyISAM est bien supérieure à celle de InnoDB. | |
| MyISAM et InnoDB ont sensiblement la même performance en lecture, par contre en écriture, InnoDB est plus lent que MyISAM. De plus, les données stockées avec InnoDB occupent plus de place sur le disque que MyISAM. | |
| Une fois qu’une table est supprimée, elle peut être rétablie. | Une fois qu’une table est supprimée, elle ne peut pas être rétablie. |
| le champ AUTO_INCREMENT fait partie de l’index. | |
| ne support pas ACID (Atomicity, Consistency, Isolation et Durability) | prend en charge la propriété ACID (Atomicity, Consistency, Isolation et Durability) |
| ne prend pas en charge les contraintes d’intégrité référentielle FOREIGN-KEY | le prend en charge les contraintes d’intégrité référentielle FOREIGN-KEY |
| il n’y a aucune possibilité de verrouillage au niveau des lignes, d’intégrité relationnelle il y’a un verrouillage au niveau de la table | Le verrouillage au niveau des lignes est possible et il y’a un verrouillage au niveau de la table |
| supporte l’index FULLTEXT | ne supporte pas l’index FULLTEXT |
| l’insertion et la mise à jour sont beaucoup plus lentes que InnoDB | l’insertion et la mise à jour sont beaucoup plus rapides que MyISAM |
