Module 6 - Optimisation des Requêtes

1. Le Problème : La Lenteur Tue les Applications

Écrire une requête qui fonctionne est la première étape. Écrire une requête qui reste rapide même quand vos tables contiennent des millions de lignes est une compétence professionnelle essentielle. Une requête lente peut paralyser une application entière.

Analogie : Chercher une information dans un livre

❌ Sans optimisation

Vous lisez le livre page par page, du début à la fin, pour trouver ce que vous cherchez. C'est un Full Table Scan.

✅ Avec optimisation

Vous utilisez l'index à la fin du livre pour aller directement à la bonne page. C'est beaucoup plus rapide.

⚠️ Impact des requêtes lentes

Temps de réponse de l'application qui explose
Serveur surchargé et risque de crash
Expérience utilisateur dégradée
Coûts d'infrastructure qui augmentent

2. Les Index : L'Annuaire de votre Base de Données

Un index est une structure de données spéciale, séparée de la table, qui contient une copie triée d'une ou plusieurs colonnes avec des "pointeurs" vers les lignes correspondantes de la table.

Types d'index

Type d'Index	Description	Usage
Index simple	Index sur une seule colonne	Recherches sur une colonne spécifique
Index composite	Index sur plusieurs colonnes	Recherches combinant plusieurs critères
Index unique	Garantit l'unicité des valeurs	Clés primaires, emails, numéros uniques
Index full-text	Pour la recherche textuelle	Recherche dans du contenu texte long

Créer un index

-- Créer un index simple sur la colonne 'email'
CREATE INDEX idx_utilisateurs_email ON utilisateurs(email);

-- Créer un index composite sur ville et nom
CREATE INDEX idx_clients_ville_nom ON Clients(ville, nom);

-- Créer un index unique
CREATE UNIQUE INDEX idx_unique_email ON utilisateurs(email);

Le Coût des Index

Les index ne sont pas gratuits.

✅ Avantage

Ils accélèrent considérablement les opérations de lecture (SELECT, JOIN).

❌ Inconvénient

Ils ralentissent légèrement les opérations d'écriture (INSERT, UPDATE, DELETE), car la base de données doit mettre à jour à la fois la table ET l'index.

💡 Règle d'or

Créez des index sur les colonnes que vous utilisez fréquemment dans vos clauses WHERE et ON (les clés étrangères sont d'excellents candidats).

Quand créer un index ?

Sur les clés étrangères utilisées dans les JOIN
Sur les colonnes fréquemment utilisées dans WHERE
Sur les colonnes utilisées pour trier (ORDER BY)
Sur les colonnes uniques (email, numéro de sécurité sociale, etc.)

Quand NE PAS créer d'index ?

Sur des petites tables (moins de 1000 lignes)
Sur des colonnes rarement utilisées dans les recherches
Sur des tables avec beaucoup d'écritures et peu de lectures
Sur des colonnes avec peu de valeurs distinctes (ex: booléen)

3. EXPLAIN : Lire dans les Pensées du SGBD

Comment savoir si votre requête utilise un index ? Comment savoir si elle est efficace ? En demandant au SGBD son plan d'exécution.

Analogie

C'est comme demander à votre GPS de vous montrer l'itinéraire détaillé avant de commencer le trajet. Vous pouvez voir s'il prévoit de prendre l'autoroute (un index) ou de passer par des petites routes de campagne (un full scan).

La commande EXPLAIN (ou EXPLAIN ANALYZE sur PostgreSQL) est l'outil de débogage le plus important pour un développeur SQL.

EXPLAIN SELECT * FROM utilisateurs WHERE email = 'alice@example.com';

-- Version détaillée avec temps d'exécution réel (PostgreSQL)
EXPLAIN ANALYZE SELECT * FROM utilisateurs WHERE email = 'alice@example.com';

Interpréter le résultat d'EXPLAIN

Colonne	Signification	Valeurs importantes
type	Type d'accès à la table	ALL = Full scan (⚠️ mauvais) index, ref, range = Index utilisé (✅ bon)
key	Index utilisé	Nom de l'index ou NULL si aucun
rows	Nombre de lignes examinées	Plus c'est bas, mieux c'est
Extra	Informations supplémentaires	Using index = très bon Using filesort = attention performance

⚠️ Signaux d'alerte dans EXPLAIN

type: ALL → Full table scan
rows élevé → Trop de lignes examinées
Using temporary → Table temporaire créée
Using filesort → Tri coûteux

4. Bonnes Pratiques pour des Requêtes Efficaces

1. SELECT ce dont vous avez besoin, pas *

❌ Mauvais

SELECT * FROM Clients;

Force la base à récupérer toutes les colonnes, même inutiles.

✅ Bon

SELECT nom, email FROM Clients;

Ne récupère que les colonnes nécessaires.

2. Attention aux fonctions dans WHERE

Appliquer une fonction sur une colonne dans une clause WHERE empêche souvent l'utilisation d'un index sur cette colonne.

❌ Non "sargable" (pas optimisable)

WHERE YEAR(date_commande) = 2024;

L'index sur date_commande ne peut pas être utilisé.

✅ "Sargable" (optimisable)

WHERE date_commande >= '2024-01-01'
  AND date_commande < '2025-01-01';

L'index peut être utilisé efficacement.

3. JOIN vs sous-requête IN

❌ Moins optimisé

SELECT * FROM articles
WHERE auteur_id IN (
    SELECT id FROM auteurs
    WHERE pays = 'France'
);

✅ Mieux optimisé

SELECT a.*
FROM articles AS a
INNER JOIN auteurs AS au
    ON a.auteur_id = au.id
WHERE au.pays = 'France';

4. Limitez les résultats avec LIMIT

-- Récupérer seulement les 10 premiers résultats
SELECT * FROM Commandes
ORDER BY date_commande DESC
LIMIT 10;

5. Utilisez EXISTS au lieu de COUNT pour tester l'existence

❌ Moins efficace

SELECT COUNT(*) FROM Commandes
WHERE client_id = 5;

Compte toutes les lignes.

✅ Plus efficace

SELECT EXISTS(
    SELECT 1 FROM Commandes
    WHERE client_id = 5
);

S'arrête dès la première ligne trouvée.

5. Les Transactions : Garantir la Cohérence

Une transaction est un groupe d'une ou plusieurs opérations SQL qui doivent être traitées comme une seule unité de travail atomique.

Analogie : Le Virement Bancaire

Un virement de 100€ du compte A au compte B est composé de deux opérations :

UPDATE comptes SET solde = solde - 100 WHERE id = 'A';
UPDATE comptes SET solde = solde + 100 WHERE id = 'B';

Que se passe-t-il si le système plante après l'opération 1 mais avant l'opération 2 ? L'argent a disparu ! Une transaction garantit que soit les deux opérations réussissent, soit aucune n'est appliquée.

Syntaxe des transactions

START TRANSACTION;

UPDATE comptes SET solde = solde - 100 WHERE id = 'A';
UPDATE comptes SET solde = solde + 100 WHERE id = 'B';

-- Si tout s'est bien passé, on valide les changements
COMMIT;

-- Si une erreur s'est produite, on peut annuler tous les changements
-- ROLLBACK;

Les propriétés ACID

Les transactions garantissent quatre propriétés fondamentales, connues sous l'acronyme ACID :

A - Atomicité

Tout ou rien. Soit toutes les opérations de la transaction réussissent, soit aucune n'est appliquée.

C - Cohérence

La base de données passe d'un état cohérent à un autre état cohérent. Les contraintes sont respectées.

I - Isolation

Les transactions concurrentes ne s'interfèrent pas. Chaque transaction s'exécute comme si elle était seule.

D - Durabilité

Une fois validée (COMMIT), la transaction est permanente, même en cas de panne système.

Exemple pratique avec la boutique en ligne

START TRANSACTION;

-- 1. Diminuer le stock du produit
UPDATE Produits
SET stock = stock - 2
WHERE id = 5;

-- 2. Créer la commande
INSERT INTO Commandes (client_id, date_commande, montant_total)
VALUES (3, NOW(), 91.98);

-- 3. Ajouter les détails de la commande
INSERT INTO Details_Commande (commande_id, produit_id, quantite)
VALUES (LAST_INSERT_ID(), 5, 2);

-- Si tout est OK, valider
COMMIT;

-- En cas d'erreur, annuler tout
-- ROLLBACK;

⚠️ Attention aux transactions longues

Les transactions trop longues peuvent bloquer d'autres utilisateurs et dégrader les performances. Gardez vos transactions courtes et précises.

6. Checklist d'Optimisation

✅ Checklist avant la mise en production

☑️ Les clés étrangères ont-elles des index ?
☑️ Les colonnes utilisées dans WHERE sont-elles indexées ?
☑️ Utilisez-vous SELECT avec des colonnes spécifiques (pas *) ?
☑️ Avez-vous testé vos requêtes avec EXPLAIN ?
☑️ Évitez-vous les fonctions dans les clauses WHERE ?
☑️ Les jointures sont-elles préférées aux sous-requêtes IN ?
☑️ Utilisez-vous LIMIT pour paginer les résultats ?
☑️ Les transactions sont-elles courtes et précises ?
☑️ Avez-vous testé avec un volume de données réaliste ?

7. À Vous de Jouer : Exercices Pratiques

📝 Exercice 1

Créez un index approprié pour accélérer cette requête :

SELECT * FROM Commandes WHERE client_id = 5;

Question : Sur quelle colonne faut-il créer l'index ?

📝 Exercice 2

Utilisez EXPLAIN pour analyser cette requête et identifiez les problèmes potentiels :

EXPLAIN SELECT * FROM Clients WHERE ville = 'Paris';

Question : Utilise-t-elle un index ou fait-elle un full scan ?

📝 Exercice 3

Réécrivez cette requête pour la rendre "sargable" :

SELECT * FROM Commandes
WHERE MONTH(date_commande) = 3;

Indice : Utilisez une comparaison de plage de dates au lieu d'une fonction.

SQL et Bases de Données Relationnelles

Optimisation des Requêtes - Rendre vos Requêtes Plus Rapides