Dans cette étape, vous apprendrez à comparer les performances de différents types de sous-requêtes en MySQL. Comprendre les caractéristiques de performance des sous-requêtes est crucial pour écrire des requêtes SQL efficaces, en particulier lors du traitement de grands ensembles de données.
Les performances d'une sous-requête peuvent être affectées par plusieurs facteurs, notamment :
-
Ajouter plus de données à la table orders :
Pour rendre la comparaison de performance plus réaliste, ajoutons une quantité significative de données à la table orders. Nous insérerons 1000 commandes pour chaque client à l'aide d'une procédure stockée. Exécutez les commandes SQL suivantes dans l'invite MySQL :
DELIMITER //
CREATE PROCEDURE insert_many_orders()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 1000 DO
INSERT INTO orders (customer_id, order_date, total_amount) VALUES (1, CURDATE(), 50.00);
INSERT INTO orders (customer_id, order_date, total_amount) VALUES (2, CURDATE(), 75.00);
INSERT INTO orders (customer_id, order_date, total_amount) VALUES (3, CURDATE(), 100.00);
INSERT INTO orders (customer_id, order_date, total_amount) VALUES (4, CURDATE(), 125.00);
SET i = i + 1;
END WHILE;
END//
DELIMITER ;
CALL insert_many_orders();
DROP PROCEDURE insert_many_orders;
Explication :
- Ce script SQL crée une procédure stockée nommée
insert_many_orders.
- La procédure insère 1000 commandes pour chacun des quatre clients dans la table
orders.
- Après l'insertion des données, la procédure est supprimée.
-
Requête utilisant IN :
Exécutez la requête suivante utilisant IN pour trouver tous les clients qui ont passé au moins une commande :
SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);
-
Analyser le plan d'exécution de la requête à l'aide de EXPLAIN :
Avant d'exécuter la requête, utilisez la commande EXPLAIN pour analyser le plan d'exécution de la requête. Cela vous donnera un aperçu de la manière dont MySQL prévoit d'exécuter la requête et identifiera les goulots d'étranglement potentiels en matière de performance. Exécutez la commande SQL suivante dans l'invite MySQL :
EXPLAIN SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);
La sortie de EXPLAIN vous montrera les tables accédées, les index utilisés (le cas échéant) et l'ordre dans lequel les opérations sont effectuées. Portez une attention particulière à la colonne type, qui indique le type de jointure ou de méthode d'accès utilisée.
-
Requête utilisant EXISTS :
Exécutez la requête suivante utilisant EXISTS pour trouver tous les clients qui ont passé au moins une commande :
SELECT *
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
-
Analyser le plan d'exécution de la requête à l'aide de EXPLAIN :
Utilisez la commande EXPLAIN pour analyser le plan d'exécution de la requête EXISTS. Exécutez la commande SQL suivante dans l'invite MySQL :
EXPLAIN SELECT *
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
Comparez le plan d'exécution avec celui de la requête IN. Y a-t-il des différences dans les tables accédées, les index utilisés ou les méthodes d'accès ?
-
Observations :
En général, EXISTS a tendance à offrir de meilleures performances que IN lorsque la sous-requête renvoie un grand nombre de lignes. En effet, IN doit comparer les valeurs de la requête externe à toutes les valeurs renvoyées par la sous-requête, tandis que EXISTS s'arrête dès qu'il trouve une correspondance. Cependant, les performances réelles peuvent varier en fonction de la requête spécifique, des données et du système de base de données. Vous pouvez utiliser la fonction BENCHMARK() (comme montré dans le document original) pour obtenir une mesure plus précise du temps d'exécution, mais pour ce laboratoire, l'analyse de la sortie EXPLAIN fournit un aperçu suffisant des plans de requête.
-
Nettoyage (facultatif) :
Si vous souhaitez nettoyer la base de données et les tables, vous pouvez exécuter les commandes suivantes dans l'invite MySQL :
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
DROP DATABASE IF EXISTS labdb;
Lorsque vous avez terminé toutes les étapes, vous pouvez quitter le client MySQL en tapant :
exit
