Introduction aux moteurs de stockage MySQL

MySQL est probablement le système de gestion de bases de données relationnelles (SGBDR) le plus connu. Développé en tant que logiciel libre et open source, il était à l’origine soutenu par la société MYSQL AB, mais appartient maintenant à Oracle. Dans MySQL, le « moteur de stockage » utilisé pour une table détermine comment les données sont traitées. Il existe plusieurs moteurs de stockage, mais les plus utilisés sont InnoDB et MyISAM. Dans cet article, nous voyons quelles sont leurs caractéristiques distinctives et les principales différences entre eux.

Dans ce tutoriel, vous allez apprendre :

Qu’est-ce qu’un moteur de stockage ?
Comment vérifier quels moteurs de stockage sont disponibles
Les principales différences entre MyISAM et InnoDB
Comment vérifier quel moteur est utilisé par une table
Comment définir et modifier le moteur de stockage utilisé par une table

Configuration logicielle requise et conventions utilisées

Qu’est-ce qu’un moteur de stockage ?

Avant d’aborder les fonctionnalités et les différences entre les deux principaux moteurs de stockage MySQL, nous devons définir ce qu’est un moteur de stockage. Les moteurs de stockage, également connus sous le nom de « gestionnaires de tables », sont essentiellement les parties de base de données qui interprètent et gèrent les opérations liées aux requêtes SQL pour les tables de base de données. Dans les versions récentes de MySQL, les moteurs de stockage peuvent être organisés et gérés à l’aide d’une architecture « pluggable ». Il existe une variété de moteurs de stockage, mais les deux plus fréquemment utilisés sont InnoDB et MyISAM.

Vérification des moteurs de stockage disponibles

Pour obtenir une liste des moteurs de stockage disponibles dans la base de données que nous utilisons, tout ce que nous avons à faire est d’émettre une simple requête SQL, donc la première chose que nous devons faire est d’ouvrir une invite interactive MySQL et de nous connecter à l’aide d’un utilisateur de la base de données et de son mot de passe :

mysql -u <username> -p<password>

Si la connexion réussit, l’invite passera à mysql>. Ici, nous pouvons exécuter notre requête SQL pour visualiser les moteurs de stockage disponibles :

mysql> SHOW ENGINES;

Une fois la requête exécutée, nous devrions obtenir un résultat similaire à ce qui suit :

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

Dans le tableau ci-dessus, généré à la suite de la requête, nous pouvons facilement voir quels moteurs de stockage sont pris en charge, en examinant la valeur de la colonne Support dans chaque ligne. La valeur « OUI » signifie que le moteur de stockage est disponible, « NON » dans le cas contraire. La valeur « DEFAULT » dans la même colonne, en revanche, indique que le moteur correspondant, dans ce cas InnoDB, est le moteur par défaut utilisé par le serveur.

Les valeurs présentes dans les colonnes « Transactions » et « Points de sauvegarde » indiquent si un moteur de stockage prend en charge, respectivement, les transactions et les annulations ou non. Comme nous pouvons le voir en jetant un coup d’œil au tableau, seul le moteur InnoDB le fait.

Les informations sur les moteurs de stockage existent dans la table « ENGINES » de la base de données « INFORMATION_SCHEMA », nous pouvons donc également émettre des requêtes standard « SELECT » pour obtenir les données dont nous avons besoin :

mysql> SELECT * FROM INFORMATION_SCHEMA.ENGINES

Nous obtiendrions le même résultat que nous avons vu ci-dessus.

InnoDB contre MyISAM

Voyons quelles sont les principales fonctionnalités et différences entre les deux moteurs de stockage les plus utilisés : InnoDB et MyISAM.

InnoDB

Comme nous l’avons déjà dit, InnoDB est le moteur de stockage par défaut depuis MySQL 5.5. Certaines des principales caractéristiques de ce moteur de stockage sont les suivantes :

Prise en charge des transactions avec validation et restauration
Verrouillage au niveau des rangées
Prise en charge des clés étrangères, avec mise à jour et suppression en cascade

Transactions avec annulations et validations

La prise en charge des transactions offre un moyen sûr d’exécuter plusieurs requêtes, tout en préservant la cohérence des données. Lorsque plusieurs opérations qui modifient des données sont exécutées et que nous voulons nous assurer qu’elles ne sont efficaces que si toutes réussissent et qu’aucune erreur ne se produit, nous voulons utiliser des transactions. La façon typique de procéder est de démarrer une transaction et d’effectuer les requêtes : si une erreur se produit, un rollback est effectué, sinon les modifications sont validées.

Serrures au niveau des rangées

Lors de l’utilisation d’InnoDB, le verrouillage des données se produit au niveau des lignes, de sorte que la quantité de données verrouillées pendant une transaction est limitée. Il existe deux types de serrures avec InnoDB :

Serrure partagée
Serrure exclusive

Un verrou partagé permet à la transaction qui en est le propriétaire de lire la ligne, tandis qu’un verrou exclusif permet à la transaction d’effectuer des opérations qui modifient la ligne, afin de mettre à jour ou de supprimer des données.

Lorsqu’une transaction obtient un verrou partagé sur une ligne et qu’une autre transaction nécessite le même type de verrou, celui-ci est accordé immédiatement. Cependant, si la deuxième transaction demande un verrou exclusif sur la même ligne, elle devra attendre.

Si la première transaction détient un verrou exclusif sur la ligne, la seconde devra attendre que ledit verrou soit libéré pour obtenir un verrou partagé ou exclusif.

Prise en charge des clés étrangères

Les clés étrangères sont une caractéristique très importante, car elles peuvent être utilisées pour renforcer l’intégrité des données en fonction de la relation logique entre les tables. Imaginons que nous ayons trois tables dans notre base de données (supposons qu’elle s’appelle « testdb ») : une table d’utilisateurs contenant les utilisateurs existants, une table de tâches où toutes les tâches disponibles sont enregistrées, et une table user_job utilisée pour représenter la relation de plusieurs à plusieurs qui existe entre les utilisateurs et les tâches (un utilisateur peut avoir plusieurs tâches, et plusieurs tâches peuvent être associées au même utilisateur).

La table user_job est ce qu’on appelle une table de jointure ou d’association, car son seul but est de représenter les associations utilisateurs-emplois. La table comporte deux colonnes, l’une appelée user_id et l’autre ID de tâche. Deux contraintes de clé étrangère existent dans la table pour appliquer les règles suivantes : une valeur dans la colonne user_id ne peut faire référence qu’à une valeur dans la colonne id de la table user, et une valeur dans la colonne job_id doit faire référence à une valeur existante dans la colonne id de la table job.

Cela renforcerait l’intégrité, puisque seuls les identifiants des utilisateurs et des tâches existants seraient autorisés à exister dans la table d’association. La suppression d’un utilisateur ou d’un travail impliqué dans une ou plusieurs associations dans la table user_job n’est pas non plus autorisée, sauf si une règle CASCADE DELETE est définie pour la clé étrangère correspondante. Dans ce cas, lorsqu’un utilisateur ou une tâche est supprimé, les relations dans lesquelles il est impliqué sont également supprimées.

MonISAM

MyISAM était le moteur de stockage MySQL par défaut, mais a été remplacé par InnoDB. Lorsque ce moteur est utilisé, les verrouillages de données se produisent au niveau de la table, donc plus de données sont verrouillées lorsqu’une opération est effectuée. Contrairement à InnoDB, MyISAM ne prend pas en charge l’annulation des transactions et les commits, de sorte que les annulations doivent être effectuées manuellement. Une autre grande différence entre MyISAM et InnoDB est que le premier ne prend pas en charge les clés étrangères. MyISAM est plus simple, et pourrait avoir un avantage (discutable) sur les opérations de lecture intensive sur des ensembles de données limités. Lorsque MyISAM est utilisé sur une table, un indicateur est défini, qui indique si cette table a besoin d’être réparée, après, par exemple, un arrêt brusque. La réparation de la table pourrait ensuite être effectuée à l’aide des outils appropriés.

Vérification du moteur de stockage utilisé par une table spécifique

Comment savoir quel moteur de stockage est utilisé pour une table spécifique ? Tout ce que nous avons à faire est d’émettre une simple requête. Par exemple, pour savoir quel moteur de stockage est utilisé pour la table d’utilisateurs que nous avons mentionnée dans l’exemple précédent, nous exécuterions :

mysql> SHOW TABLE STATUS WHERE name = 'user' \G;

Notez que dans la requête ci-dessus, nous avons utilisé \G, afin de faire en sorte que le résultat de la requête soit affiché verticalement, afin d’optimiser l’espace. Une fois la requête exécutée, nous obtenons le résultat suivant :

*************************** 1. row ***************************
           Name: user
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-12-27 09:38:16
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

Dans ce cas, en regardant la valeur stockée dans la colonne « Moteur », nous pouvons clairement voir que le moteur « InnoDB » est utilisé pour la table. Une autre façon d’obtenir les mêmes informations est d’interroger le INFORMATION_SCHEMA. Tableau TABLES directement :

mysql> SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'user' AND TABLE_SCHEMA = 'testdb';

La requête ci-dessus ne renvoie que le moteur utilisé par la table :

+--------+
| ENGINE |
+--------+
| InnoDB |
+--------+

Si nous modifions légèrement la requête, nous pouvons obtenir une liste de tous les noms de tables de la base de données et du moteur utilisé par celles-ci :

mysql> SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'testdb';

Définition et modification du moteur de stockage utilisé par une table

Si nous voulons définir un moteur de stockage spécifique pour une table, nous pouvons le spécifier au moment de la création. Par exemple, supposons que nous créions la table des tâches et que, pour une raison quelconque, nous souhaitions utiliser le moteur de stockage MyISAM pour celle-ci. Nous émettrions la requête SQL suivante :

mysql> CREATE TABLE testdb.job ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL ) ENGINE = MyISAM;

Si, au lieu de cela, nous voulons modifier le moteur de stockage utilisé pour une table déjà existante, nous avons simplement besoin d’utiliser une instruction ALTER SQL. Supposons que nous voulions changer le moteur de stockage utilisé pour la table « job » que nous avons créée dans l’exemple précédent en InnoDB ; Nous courrions :

mysql> ALTER TABLE testdb.job ENGINE = InnoDB;

Conclusions

Dans ce tutoriel, nous avons appris ce qu’est un moteur de stockage de base de données, et nous avons vu les principales caractéristiques des deux moteurs MySQL les plus utilisés : InnoDB et MyISAM. Nous avons vu comment vérifier quels moteurs sont disponibles, quel moteur est utilisé pour une table et comment définir et modifier un moteur de table à l’aide de requêtes SQL.