dernière modification le 10 juillet 2020
Dans ce chapitre, nous allons parler des moteurs de stockage MySQL.
Un moteur de stockage est un module logiciel qu’un système de gestion de base de données utilise pour créer, lire, mettre à jour les données d’une base de données. Il existe deux types de moteurs de stockage dans MySQL : transactionnel et non transactionnel.
Pour MySQL 5.5 et les versions ultérieures, le moteur de stockage par défaut est InnoDB.
Le moteur de stockage par défaut pour MySQL avant la version5.5 était MyISAM. Choisir le bon moteur de stockage est une décision stratégique importante, qui aura un impact sur le développement futur. Dans ce tutoriel, nous utiliserons les moteurs de stockage MyISAM, InnoDB, Memory et CSV. Si vous êtes nouveau dans le monde de MySQL et que vous étudiez le système de gestion de base de données MySQL, ce n’est pas un problème majeur. Si vous planifiez une base de données de production, les choses deviennent plus compliquées.
Liste des moteurs de stockage
Moteurs de stockage supportés par MySQL :
- InnoDB
- MyISAM
- Memory
- CSV
- Merge
- Archive
- Federated
- Blackhole
- Example
InnoDB est le moteur de stockage le plus largement utilisé avec le support des transactions. C’est un moteur de stockage conforme à la norme ACID. Il prend en charge le verrouillage au niveau des rangs, la récupération en cas de crash et le contrôle de la concurrence multi-version. C’est le seul moteur qui fournit une contrainte d’intégrité référentielle de clé étrangère. Oracle recommande d’utiliser InnoDB pour les tables, sauf pour des cas d’utilisation spécialisés.
MyISAM est le moteur de stockage original. C’est un moteur de stockage rapide.Il ne supporte pas les transactions. MyISAM fournit un nivellement des tables. Il est surtout utilisé dans le Web et l’entreposage de données.
Le moteur de stockage en mémoire crée des tables en mémoire. C’est le moteur le plus rapide. Il fournit un verrouillage au niveau des tables. Il ne supporte pas les transactions.Le moteur de stockage en mémoire est idéal pour créer des tables temporaires ou des consultations rapides.Les données sont perdues lorsque la base de données est redémarrée.
CSV stocke les données dans des fichiers CSV. Il offre une grande flexibilité car les données dans ce format sont facilement intégrées dans d’autres applications.
Fusion opère sur des tables MyISAM sous-jacentes. Les tables de fusion permettent de gérer plus facilement de grands volumes de données. Elles regroupent logiquement une série de tables MyISAM identiques, et les référencent comme un seul objet.Bon pour les environnements d’entreposage de données.
Le moteur de stockage d’archives est optimisé pour une insertion à grande vitesse. Il compresse les données au fur et à mesure de leur insertion. Il ne prend pas en charge les transactions. Il est idéal pour stocker et récupérer de grandes quantités de données historiques et archivées rarement référencées.
Le moteur de stockage Blackhole accepte mais ne stocke pas les données. Les récupérations renvoient toujours un ensemble vide. Cette fonctionnalité peut être utilisée dans la conception de bases de données distribuées où les données sont automatiquement répliquées, mais pas stockées localement. Ce moteur de stockage peut être utilisé pour effectuer des tests de performance ou d’autres tests.
Le moteur de stockage fédéré offre la possibilité de séparer les serveurs MySQL pour créer une base de données logique à partir de plusieurs serveurs physiques.Les requêtes sur le serveur local sont automatiquement exécutées sur les tables distantes (fédérées). Aucune donnée n’est stockée sur les tables locales.Il est bon pour les environnements distribués.
mysql> SHOW ENGINES\G*************************** 1. row *************************** Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keysTransactions: YES XA: YES Savepoints: YES*************************** 2. row *************************** Engine: CSV Support: YES Comment: CSV storage engineTransactions: NO XA: NO Savepoints: NO...
La commande SHOW ENGINES montre tous les moteurs disponibles que le serveur supporte.
Choisir le bon moteur
Aucun moteur de stockage n’est idéal pour toutes les circonstances. Certains sont plus performants dans certaines conditions et moins performants dans d’autres situations. Il existe des compromis qui doivent être pris en compte. Une solution plus sûre nécessite plus de ressources ; elle peut être plus lente, prendre plus de temps CPU et d’espace disque. MySQL est très flexible dans la mesure où il fournit plusieurs moteurs de stockage différents. Certains d’entre eux, comme le moteur Archive, sont créés pour être utilisés dans des situations spécifiques.
Dans certains cas, la réponse est claire. Dès lors que nous traitons avec certains systèmes de paiement, nous sommes obligés d’utiliser la solution la plus sûre. Nous ne pouvons pas nous permettre de perdre des données aussi sensibles. InnoDB est la solution à retenir. Si nous voulons une recherche en texte intégral, nous pouvons choisir soit MyISAM soit InnoDB. Seul InnoDB supporte la contrainte d’intégrité référentielle de clé étrangère et si nous prévoyons d’utiliser cette contrainte, alors le choix est clair.
Spécifier et modifier les moteurs de stockage
Le moteur de stockage est spécifié au moment de la création de la table.
mysql> CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name VARCHAR(50), -> Cost INTEGER) ENGINE='MyISAM';
Le mot clé ENGINE spécifie le moteur de stockage utilisépour cette table particulière.
Si nous ne spécifions pas explicitement le moteur de stockage, alors le moteur de stockage par défaut est utilisé. Avant MySQL 5.5, le moteur de stockage par défaut était MyISAM. Pour MySQL 5.5 et les versions ultérieures, le moteur de stockage par défaut est InnoDB.
Il est possible de migrer vers un moteur de stockage différent. Notez que la migration d’une grande table peut prendre beaucoup de temps. De plus, nous pourrions rencontrer des problèmes lors de la migration des tables. Certaines fonctionnalités pourraient ne pas être prises en charge dans les deux tables.
mysql> SELECT ENGINE FROM information_schema.TABLES -> WHERE TABLE_SCHEMA='mydb' -> AND TABLE_NAME='Cars';+--------+| ENGINE |+--------+| InnoDB |+--------+1 row in set (0,05 sec)
Cette instruction SQL permet de connaître le moteur de stockage utilisé pour une table Cars dans la base de données mydb. On pourrait aussi utiliser l’instruction SQL SELECT CREATE TABLE Cars. La information_schema est une table qui stocke des informations techniques sur nos tables.
mysql> ALTER TABLE Cars ENGINE='MyISAM';
Cette instruction SQL change le moteur de stockage de la table Cars en MyISAM.
mysql> SELECT ENGINE FROM information_schema.TABLES -> WHERE TABLE_SCHEMA='mydb' -> AND TABLE_NAME='Cars';+--------+| ENGINE |+--------+| MyISAM |+--------+1 row in set (0,00 sec)
Maintenant le moteur de stockage de la table est MyISAM.
Dans cette partie du tutoriel MySQL, nous avons abordé les moteurs de stockage.