Optimisation MySQL : Techniques Essentielles pour des Performances Accrues

MySQL gère très bien les grands volumes de données, mais en général, plus les tables d'une base de données sont petites, plus les requêtes exécutées dessus sont rapides. Par conséquent, lors de la création de tables, pour de meilleures performances, nous pouvons définir la largeur des colonnes aussi petite que possible.

Par exemple, lors de la définition du champ de code postal, si nous le définissons comme CHAR(255), nous inutilement augmentons la taille de la base de données. Même utiliser le type VARCHAR serait superflu, car CHAR(6) suffit amplement. De même, lorsque c'est possible, nous devrions utiliser MEDIUMINT plutôt que BIGINT pour définir les champs entiers.

Une autre méthode d'optimisation consiste, lorsque c'est possible, à définir les colonnes comme NOT NULL. Ainsi, lors de l'exécution de requêtes ultérieures, la base de données n'a pas à comparer les valeurs NULL.

Pour certains champs textuels, tels que "région" ou "genre", nous pouvons les définir comme type ENUM. Dans MySQL, le type ENUM est traité comme des données numériques, qui sont traitées beaucoup plus rapidement que les types texte. Cela nous permet d'améliorer les performances de la base de données.

2. Utiliser les JOINTURES plutôt que les sous-requêtes

MySQL supporte les sous-requêtes SQL à partir de la version 4.1. Cette technique permet de créer une requête SELECT qui génère un résultat à colonne unique, puis d'utiliser ce résultat comme condition de filtrage dans une autre requête. Par exemple, pour supprimer les clients de la table d'informations clients n'ayant aucune commande, nous pouvons utiliser une sous-requête pour d'abord extraire tous les ID clients ayant passé des commandes dans la table des ventes, puis transmettre ce résultat à la requête principale, comme suit :

DELETE FROM utilisateurs
WHERE ID_Utilisateur NOT IN (SELECT ID_Utilisateur FROM commandes)

L'utilisation des sous-requêtes permet d'effectuer en une seule fois des opérations SQL qui logiquement nécessiteraient plusieurs étapes, tout en évitant les transactions ou les verrous de table. Cependant, dans certains cas, les sous-requêtes peuvent être remplacées par des jointures (JOIN) plus efficaces. Par exemple, pour récupérer tous les utilisateurs n'ayant aucune commande, nous pouvons utiliser la requête suivante :

SELECT * FROM utilisateurs
WHERE ID_Utilisateur NOT IN (SELECT ID_Utilisateur FROM commandes)

Si nous utilisons une jointure (JOIN) pour effectuer cette requête, la vitesse sera beaucoup plus rapide, surtout si l'ID_Utilisateur est indexé dans la table commandes. La requête serait alors :

SELECT * FROM utilisateurs
LEFT JOIN commandes ON utilisateurs.ID_Utilisateur = commandes.ID_Utilisateur
WHERE commandes.ID_Utilisateur IS NULL

Les jointures (JOIN) sont plus efficaces car MySQL n'a pas besoin de créer de tables temporaires en mémoire pour effectuer cette requête logiquement nécessitant deux étapes.

3. Utiliser les UNION au lieu des tables temporaires manuelles

MySQL supporte les requêtes UNION à partir de la version 4.0. Cela permet de combiner deux ou plusieurs requêtes SELECT nécessitant l'utilisation de tables temporaires en une seule requête. La table temporaire est automatiquement supprimée lorsque la session de requête client se termine, garantissant une base de données organisée et efficace. Pour créer une requête avec UNION, il suffit de connecter plusieurs instructions SELECT avec le mot-clé UNION, en veillant à ce que toutes les instructions SELECT aient le même nombre de champs. L'exemple suivant illustre une requête utilisant UNION :

SELECT Nom, Telephone FROM clients UNION
SELECT Nom, Date_Naissance FROM auteurs UNION
SELECT Nom, Fournisseur FROM produits

4. Les trnasactions

Bien que nous puissions utiliser des sous-requêtes, des jointures et des UNION pour créer diverses requêtes, toutes les opérations de base de données ne peuvent pas être accomplies avec une ou quelques instructions SQL. Plus souvent, une série d'instructions est nécessaire pour effectuer un travail. Cependant, dans ce cas, si une instruction de ce bloc échoue, l'ensemble des opérations du bloc devient incertain. Par exemple, pour insérer des données dans deux tables associées, il se peut que la première table soit mise à jour avec succès, puis que la base de données rencontre un problème, empêchant l'achèvement de l'opération sur la deuxième table. Cela pourrait entraîner une incohérence des données, voire une corruption des données de la base de données. Pour éviter cela, il faut utiliser des transactions : soit toutes les instructions du bloc réussissent, soit toutes échouent. En d'autres termes, cela permet de maintenir la cohérence et l'intégrité des données de la base de données. Une transaction commence par le mot-clé BEGIN et se termine par le mot-clé COMMIT. Si une opération SQL entre ces deux échoue, la commande ROLLBACK peut restaurer la base de données à son état avant BEGIN.

BEGIN;
INSERT INTO ventes SET ID_Client = 14;
UPDATE stock SET Quantite = 11 WHERE article = 'livre';
COMMIT;

Une autre fonction importante des transactions est qu' lorsque plusieurs utilisateurs utilisent simultanément la même source de données, elles peuvent utiliser le verrouillage de la base de données pour fournir un accès sécurisé aux utilisateurs, garantissant que leurs opérations ne sont pas perturbées par d'autres utilisateurs.

5. Le veroruillage de tables

Bien que les transactions soient une excellente méthode pour maintenir l'intégrité de la base de données, leur caractère exclusif peut parfois affecter les performances, notamment dans les grands systèmes d'application. Puisque la base de données est verrouillée pendant l'exécution d'une transaction, les autres requêtes des utilisateurs ne peuvent que temporairement attendre que la transaction se termine. Si un système de base de données n'est utilisé que par quelques utilisateurs, l'impact des transactions ne sera pas trop important ; mais si des milliers d'utilisateurs accèdent simultanément à un système de base de données, comme sur un site de commerce électronique, cela peut entraîner des délais de réponse importants.

Dans certains cas, nous pouvons obtenir de meilleures performances en verrouillant les tables. L'exemple suivant utilise le verrouillage de tables pour accomplir la fonction de transaction de l'exemple précédent :

LOCK TABLE stock WRITE
SELECT Quantite FROM stock WHERE Article = 'livre';
-- ...
UPDATE stock SET Quantite = 11 WHERE Article = 'livre';
UNLOCK TABLES

Ici, nous utilisons une instruction SELECT pour récupérer les données initiales, effectuons quelques calculs, puis utilisons l'instruction UPDATE pour mettre à jour la table avec la nouvelle valeur. L'instruction LOCK TABLE contenant le mot-clé WRITE garantit qu'aucune autre opération d'insertion, de mise à jour ou de suppression ne sera effectuée sur la table stock jusqu'à ce que la commande UNLOCK TABLES soit exécutée.

6. L'utilisation des clés étrangères

Le verrouillage de tables peut maintenir l'intégrité des données, mais il ne garantit pas l'association des données. C'est là que nous pouvons utiliser les clés étrangères.

Par exemple, une clé étrangère peut garantir que chaque enregistrement de vente fait référence à un client existant. Ici, la clé étrangère peut mapper l'ID_Client de la table clients vers l'ID_Client de la table ventes, garantissant qu'aucun enregistrement sans ID_Client valide ne sera mis à jour ou inséré dans la table ventes.

CREATE TABLE clients (
    ID_Client INT NOT NULL,
    PRIMARY KEY (ID_Client)
) TYPE=INNODB;

CREATE TABLE ventes (
    ID_Vente INT NOT NULL,
    ID_Client INT NOT NULL,
    PRIMARY KEY (ID_Client, ID_Vente),
    FOREIGN KEY (ID_Client) REFERENCES clients(ID_Client) ON DELETE CASCADE
) TYPE=INNODB;

Notez le paramètre "ON DELETE CASCADE". Ce paramètre garantit que lorsque l'enregistrement d'un client dans la table clients est supprimé, tous les enregistrements associés dans la table ventes sont automatiquement supprimés. Si vous souhaitez utiliser des clés étrangères dans MySQL, n'oubliez pas de définir le type de table comme InnoDB (type de table transactionnel) lors de sa création. Ce n'est pas le type de table par défaut dans MySQL. La méthode de définition consiste à ajouter TYPE=INNODB dans l'instruction CREATE TABLE, comme indiqué dans l'exemple.

7. L'utilisation des index

Les index sont une méthode courante pour améliorer les performances de la base de données. Ils permettent au serveur de base de données de récupérer des lignes spécifiques beaucoup plus rapidement qu' sans index, surtout lorsque les instructions de requête contiennent des commandes comme MAX(), MIN() et ORDER BY, l'amélioration de performance étant encore plus évidente.

Sur quels champs faut-il créer des index ?

En général, les index doivent être créés sur les champs qui seront utilisés pour des jointures (JOIN), des conditions WHERE et des tris ORDER BY. Il faut éviter de créer des index sur des champs contenant un grand nombre de valeurs dupliquées. Pour un champ de type ENUM, il est très probable qu'il y ait un grand nombre de valeurs dupliquées.

Par exemple, pour le champ "province" dans la table clients, créer un index sur ce champ n'apportera pas d'avantage ; au contraire, cela pourrait réduire les performances de la base de données. Nous pouvons créer des index appropriés lors de la création de la table, ou utiliser ALTER TABLE ou CREATE INDEX pour les créer ultérieurement. De plus, MySQL supporte les index et recherche en texte intégral à partir de la version 3.23.23. Les index en texte intégral sont de type FULLTEXT dans MySQL, mais ne peuvent être utilisés que sur des tables de type MyISAM. Pour une grande base de données, charger des données dans une table sans index FULLTEXT, puis créer l'index avec ALTER TABLE ou CREATE INDEX, est très rapide. Cependant, charger des données dans une table déjà dotée d'un index FULLTEXT est un processus très lent.

8. Optimisation des instructions de requête

Dans la grande majorité des cas, l'utilisation d'index peut accélérer les requêtes. Cependant, si les instructions SQL sont mal utilisées, les index ne pourront pas jouer leur rôle.

Voici plusieurs aspects auxquels il faut faire attention :

  • En premier lieu, il est préférable d'effectuer des opérations de comparaison entre des champs de même type. Dans les versions antérieures à MySQL 3.23, c'était même une condition nécessaire. Par exemple, on ne peut pas comparer un champ INT indexé avec un champ BIGINT ; mais comme cas particulier, lorsque les champs de type CHAR et VARCHAR ont la même taille, ils peuvent être comparés.
  • En second lieu, évitez d'utiliser des fonctions sur des champs indexés. Par exemple, lors de l'utilisation de la fonction YEAR() sur un champ de type DATE, l'index ne pourra pas jouer son rôle. Ainsi, bien que les deux requêtes suivantes retournent le même résultat, la seconde est beaucoup plus rapide que la première.
  • En troisième lieu, lors de la recherche de champs de type caractère, nous utilisons parfois le mot-clé LIKE avec des caractères génériques. Cette méthode, bien que simple, se fait au détriment des performances du système. Par exemple, la requête suivante comparera chaque enregistrement de la table :
SELECT * FROM livres
WHERE nom LIKE "MySQL%"

Mais si nous utilisons la requête suivante, le résultat est identique, mais la vitesse est beaucoup plus rapide :

SELECT * FROM livres
WHERE nom >= "MySQL" AND nom < "MySQM"

Enfin, il faut éviter de laisser MySQL effectuer des conversions de type automatiques dans les requêtes, car ce processus peut également rendre les index inefficaces.

Étiquettes: MySQL Optimisation jointures index transactions

Publié le 3 juillet à 08h12

Étiquettes Populaires

©2026 L'Atelier Monstre