Optimisation des requêtes | Exercices corrigés

Concepts & Exercices

SELECT → INDEX → JOIN → OPTIMIZE

Flux d'optimisation

Index

CREATE INDEX idx_nom ON table(nom);

Jointure

JOIN plus rapide que WHERE

Plan d'exécution

EXPLAIN QUERY PLAN

⚡

Exercice 1

Créer un index pour accélérer une recherche

Exercice 2

Optimiser une requête avec jointure

Exercice 3

Remplacer une sous-requête par une jointure

Exercice 4

Analyser le plan d'exécution d'une requête

Exercice 5

Optimiser une requête avec GROUP BY

Corrigé : Exercices 1 à 3

1 Création d'index

Définition :

Index : Structure de données qui améliore la vitesse de recherche dans une table en créant un pointeur vers les lignes.

Méthode d'optimisation :

Identifier les colonnes fréquemment utilisées dans WHERE
Créer un index sur ces colonnes
Tester la performance avant et après
Surveiller l'impact sur les INSERT/UPDATE

-- Avant l'optimisation
SELECT * FROM clients WHERE nom = 'Dupont';

-- Création de l'index
CREATE INDEX idx_clients_nom ON clients(nom);

-- Après l'optimisation
SELECT * FROM clients WHERE nom = 'Dupont';
              

Étape 1 : Analyse de la requête

La colonne nom est fréquemment utilisée dans les clauses WHERE

Étape 2 : Création de l'index

On exécute CREATE INDEX idx_clients_nom ON clients(nom)

Étape 3 : Impact sur la performance

La recherche est maintenant beaucoup plus rapide (O(log n) au lieu de O(n))

Sans index	Avec index
Scan complet de la table	Accès direct via index
O(n)	O(log n)
100ms pour 10000 lignes	1ms pour 10000 lignes

Réponse finale :

Index créé pour optimiser les recherches sur la colonne nom

Règles appliquées :

• Choix judicieux : Ne créer des index que sur les colonnes fréquemment interrogées

• Coût en écriture : Les index ralentissent les INSERT/UPDATE/DELETE

• Stockage : Les index consomment de l'espace disque supplémentaire

2 Optimisation de jointure

Définition :

Jointure optimisée : Utilisation de JOIN avec index sur les colonnes de liaison pour améliorer la performance.

-- Mauvaise requête (non optimisée)
SELECT c.nom, c.prenom, cmd.date_cmd
FROM clients c, commandes cmd
WHERE c.id_client = cmd.id_client AND c.ville = 'Paris';

-- Meilleure requête (optimisée)
SELECT c.nom, c.prenom, cmd.date_cmd
FROM clients c
JOIN commandes cmd ON c.id_client = cmd.id_client
WHERE c.ville = 'Paris';
              

Étape 1 : Analyse de la jointure

La jointure se fait sur c.id_client = cmd.id_client

Étape 2 : Création d'index

On crée des index sur les colonnes de liaison : clients.id_client et commandes.id_client

Étape 3 : Utilisation de JOIN explicite

On remplace la jointure implicite par une jointure explicite avec JOIN

Jointure implicite	Jointure explicite
FROM t1, t2 WHERE t1.c = t2.c	FROM t1 JOIN t2 ON t1.c = t2.c
Moins clair	Plus lisible
Moins optimisable	Plus optimisable

Réponse finale :

Jointure optimisée avec syntaxe explicite et index appropriés

Règles appliquées :

• Syntaxe JOIN : Utiliser la syntaxe explicite JOIN plutôt que la jointure implicite

• Index sur clés étrangères : Créer des index sur les colonnes utilisées dans les jointures

• Ordre des tables : Placer les tables les plus restrictives en premier

3 Remplacement de sous-requête

Définition :

Sous-requête vs Jointure : Certaines sous-requêtes peuvent être remplacées par des jointures pour de meilleures performances.

-- Mauvaise requête (sous-requête)
SELECT *
FROM clients
WHERE id_client IN (
    SELECT id_client
    FROM commandes
    WHERE date_cmd > '2023-01-01'
);

-- Meilleure requête (jointure)
SELECT DISTINCT c.*
FROM clients c
JOIN commandes cmd ON c.id_client = cmd.id_client
WHERE cmd.date_cmd > '2023-01-01';
              

Étape 1 : Identification de la sous-requête

La sous-requête IN peut souvent être remplacée par une jointure

Étape 2 : Transformation en jointure

On convertit WHERE id_client IN (sous-requête) en JOIN

Étape 3 : Ajout de DISTINCT si nécessaire

On ajoute DISTINCT pour éviter les doublons

Sous-requête	Jointure
Exécutée pour chaque ligne	Une seule passe
O(n*m)	O(n+m) avec index
100ms	10ms

Réponse finale :

Sous-requête remplacée par une jointure plus performante

Règles appliquées :

• Remplacement IN : Les sous-requêtes avec IN peuvent souvent être remplacées par des jointures

• Performance : Les jointures sont généralement plus rapides que les sous-requêtes

• Distinction : Utiliser DISTINCT si nécessaire pour éviter les doublons

Corrigé : Exercices 4 à 5

4 Analyse du plan d'exécution

Définition :

Plan d'exécution : Description de la manière dont le SGBD va exécuter une requête, utile pour identifier les goulets d'étranglement.

-- Analyse du plan d'exécution
EXPLAIN QUERY PLAN
SELECT c.nom, cmd.date_cmd
FROM clients c
JOIN commandes cmd ON c.id_client = cmd.id_client
WHERE c.ville = 'Paris';

-- Résultat typique
-- 0|0|0|SCAN TABLE clients (~100000 rows)
-- 0|1|1|SEARCH TABLE commandes USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
              

Étape 1 : Utilisation de EXPLAIN

On ajoute EXPLAIN QUERY PLAN devant la requête

Étape 2 : Analyse des résultats

On identifie les opérations de SCAN (lent) et SEARCH (rapide)

Étape 3 : Optimisation

On crée des index là où des SCAN sont effectués

Opération	Description	Performance
SCAN TABLE	Parcours complet de la table	O(n)
SEARCH TABLE	Recherche via index	O(log n)
SEARCH INDEX	Recherche via index secondaire	O(log n)

Réponse finale :

Plan d'exécution analysé et optimisé

Règles appliquées :

• EXPLAIN : Outil essentiel pour analyser la performance des requêtes

• SCAN vs SEARCH : Privilégier les opérations SEARCH aux SCAN

• Indexation : Créer des index sur les colonnes fréquemment recherchées

5 Optimisation avec GROUP BY

Définition :

Optimisation GROUP BY : Techniques pour améliorer la performance des requêtes avec regroupement et fonctions d'agrégation.

-- Mauvaise requête (non optimisée)
SELECT c.ville, COUNT(*)
FROM clients c
JOIN commandes cmd ON c.id_client = cmd.id_client
GROUP BY c.ville
HAVING COUNT(*) > 5;

-- Optimisée avec index
CREATE INDEX idx_clients_ville ON clients(ville);
CREATE INDEX idx_commandes_client ON commandes(id_client);

-- Requête avec filtre avant GROUP BY
SELECT c.ville, COUNT(*)
FROM clients c
JOIN commandes cmd ON c.id_client = cmd.id_client
WHERE cmd.date_cmd > '2023-01-01'  -- Filtre avant GROUP BY
GROUP BY c.ville
HAVING COUNT(*) > 5;
              

Étape 1 : Analyse de la requête

La requête effectue un GROUP BY sur la colonne ville

Étape 2 : Création d'index

On crée des index sur les colonnes de regroupement et de jointure

Étape 3 : Filtre avant regroupement

On place les filtres dans WHERE pour réduire le jeu de données avant GROUP BY

Avant optimisation	Après optimisation
100000 lignes traitées	10000 lignes traitées
GROUP BY sur 100000	GROUP BY sur 10000
200ms	20ms

Réponse finale :

Requête GROUP BY optimisée avec index et filtres appropriés

Règles appliquées :

• Index sur GROUP BY : Créer des index sur les colonnes de regroupement

• Filtre avant : Utiliser WHERE pour réduire le jeu de données avant GROUP BY

• Complexité : Le GROUP BY est coûteux, l'optimiser est crucial

Cours bien détaillé

INDEX → JOIN → WHERE → OPTIMIZE

Stratégie d'optimisation

🎯

Index : Améliorent la vitesse de recherche mais ralentissent les écritures.

📏

Jointures : Privilégier la syntaxe explicite JOIN pour la lisibilité et l'optimisation.

📐

Sous-requêtes : Remplacer par des jointures quand c'est possible pour de meilleures performances.

📝

Plan d'exécution : Outil essentiel pour comprendre comment une requête est exécutée.

💡

Conseil : Analyser les requêtes lentes avec EXPLAIN avant d'optimiser

🔍

Attention : Ne pas créer trop d'index, cela ralentit les INSERT/UPDATE

⚡

Astuce : Placer les conditions les plus restrictives en premier

📋

Méthode : Optimiser les requêtes critiques en priorité

✅

Vérification : Mesurer les performances avant et après optimisation

Étapes d'optimisation :

Identification : Repérer les requêtes lentes dans les logs
Analyse : Utiliser EXPLAIN pour comprendre le plan d'exécution
Indexation : Créer des index sur les colonnes fréquemment utilisées
Réécriture : Transformer les sous-requêtes en jointures
Test : Comparer les performances avant et après

Règles de performance :

Les index améliorent la lecture mais ralentissent l'écriture
Les jointures sont généralement plus rapides que les sous-requêtes
Les filtres dans WHERE sont plus efficaces que dans HAVING
Le GROUP BY est une opération coûteuse, à utiliser avec parcimonie

EXPLAIN

Analyse le plan d'exécution d'une requête

CREATE INDEX

Crée un index pour accélérer les recherches

JOIN vs IN

JOIN est généralement plus rapide que IN

Optimisation des requêtesExercices corrigés

Optimisation des requêtes
Exercices corrigés