SQL : optimiser ses requêtes pour des performances maximales

Une base de données relationnelle peut stocker des millions, voire des milliards de lignes. Sans des requêtes optimisées, le temps de réponse devient vite insupportable. L’optimisation des requêtes SQL est une compétence clé pour tout développeur, administrateur ou data scientist. Elle repose sur trois piliers : la compréhension du plan d’exécution, la création d’index adaptés, et l’écriture de requêtes efficaces. L’organisme de formation ISOSET consacre des modules complets à cette discipline, trop souvent négligée.

1. Comprendre le plan d’exécution – la carte du routier

Avant d’optimiser, il faut savoir comment le SGBD exécute la requête. Le plan d’exécution décrit l’algorithme choisi (scan de table, index seek, jointure par hachage) et les coûts associés. Les commandes essentielles sont EXPLAIN (PostgreSQL, MySQL, SQLite) et EXPLAIN ANALYZE (exécution réelle avec temps).

sql

-- PostgreSQL : analyser une requête
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM commande WHERE client_id = 12345;

Le résultat mentionne des termes comme Seq Scan (parcours séquentiel, coûteux sur les grandes tables), Index Scan (rapide si un index est utilisé) ou Hash Join (algorithme de jointure). L’objectif est de passer de Seq Scan à Index Scan. Les formations ISOSET apprennent à lire ces plans et à identifier les goulots d’étranglement.

2. Les index – l’arme principale

Un index (généralement un arbre B+) permet de trouver rapidement les lignes correspondant à une valeur. Sans index, une recherche dans 10 millions de lignes prend plusieurs secondes ; avec un index bien choisi, elle devient quasi instantanée.

2.1 Types d’index courants

B‑tree – pour les égalités (=) et les comparaisons de plages (<, >, BETWEEN).
Hash – uniquement pour les égalités, plus rapide mais limité.
GIN – pour la recherche plein‑texte ou les colonnes JSON.
Partial index – sur un sous‑ensemble de lignes (WHERE actif = true).

2.2 Créer et utiliser les index intelligemment

sql

-- Index simple sur `client_id`
CREATE INDEX idx_commande_client ON commande(client_id);

-- Index composite sur (client_id, date_commande)
CREATE INDEX idx_cli_date ON commande(client_id, date_commande);

-- Index partiel pour les commandes non livrées
CREATE INDEX idx_commande_non_livree ON commande(date_commande) WHERE statut != 'livré';

Règles d’or : indexez les colonnes utilisées dans WHERE, JOIN et ORDER BY. L’ordre dans un index composite compte : placez d’abord la colonne la plus sélective. Évitez la sur‑indexation (trop d’index ralentissent les écritures). Vérifiez avec EXPLAIN que l’index est employé. ISOSET réalise des audits d’indexation pour ses clients, détectant les index inutiles et ceux qui manquent.

3. Éviter les scans de tables inutiles

Même avec un index, une requête mal écrite peut provoquer un Seq Scan. Voici quelques erreurs fréquentes :

Fonction sur une colonne indexée – évitez WHERE YEAR(date) = 2025 ; préférez WHERE date BETWEEN '2025-01-01' AND '2025-12-31'.
LIKE avec joker au début – LIKE '%texte%' interdit l’index ; LIKE 'texte%' peut encore l’utiliser.
Négations (!=, NOT IN, NOT EXISTS) rendent souvent l’index inutilisable.
Type incompatible – comparer un VARCHAR avec un nombre peut casser l’index ; uniformisez les types.

Les ateliers ISOSET montrent comment détecter ces problèmes via EXPLAIN et les corriger par réécriture ou ajustement de types.

4. Optimiser les jointures

Les jointures mal conçues sont une source classique de lenteur. Trois algorithmes principaux existent :

Nested Loop – parcourt la table externe et cherche dans la table interne (bon pour petits jeux de données ou index sur la table interne).
Hash Join – construit une table de hachage en mémoire (efficace pour grands volumes sans index).
Merge Join – trie les deux tables puis les parcourt (nécessite un tri préalable).

Conseils : préférez INNER JOIN aux sous‑requêtes corrélées (souvent plus lentes). Joignez la table la plus sélective en premier. Indexez les colonnes de jointure des deux côtés si possible. Utilisez EXPLAIN pour voir l’algorithme choisi. ISOSET consacre une session entière aux stratégies de jointure avancées, avec des exercices sur des bases de plusieurs millions de lignes.

5. Réécriture des sous‑requêtes et des CTE

Une sous‑requête corrélée s’exécute une fois par ligne de la requête externe, ce qui peut être désastreux. Préférez une jointure ou utilisez une CTE (Common Table Expression) avec WITH pour éviter les répétitions.

sql

-- Sous‑requête corrélée lente
SELECT nom FROM client c
WHERE (SELECT COUNT(*) FROM commande cmd WHERE cmd.id_client = c.id_client) > 5;

-- Version plus rapide avec GROUP BY
SELECT c.nom
FROM client c
JOIN commande cmd ON c.id_client = cmd.id_client
GROUP BY c.id_client, c.nom
HAVING COUNT(*) > 5;

Les CTE peuvent être matérialisées ou non selon les SGBD ; sur PostgreSQL, ajoutez MATERIALIZED pour forcer la matérialisation si nécessaire. Les formations ISOSET initient également aux bonnes pratiques d’écriture des sous‑requêtes pour les jeunes développeurs.

6. Utiliser les vues matérialisées pour les agrégations complexes

Si vous exécutez souvent des requêtes d’agrégation (sommes, moyennes, group by) sur des données qui changent peu, une vue matérialisée pré‑calcule le résultat. Le rafraîchissement peut être planifié (par exemple toutes les nuits).

sql

-- PostgreSQL
CREATE MATERIALIZED VIEW stats_ventes_produit AS
SELECT produit_id, SUM(montant) AS total, COUNT(*) AS nb
FROM commande
GROUP BY produit_id;

-- Rafraîchir périodiquement
REFRESH MATERIALIZED VIEW stats_ventes_produit;

Les vues matérialisées transforment des requêtes lourdes en simples SELECT sur la vue. ISOSET recommande cette technique pour les tableaux de bord décisionnels.

7. Partitionnement pour les très grandes tables

Quand une table atteint plusieurs centaines de millions de lignes, même les index peuvent être insuffisants. Le partitionnement divise la table en sous‑tables plus petites (par date, par région, etc.). Les requêtes qui filtrent sur la clé de partition ne parcourent qu’une seule sous‑table.

sql

-- Partitionnement par plage de dates (PostgreSQL)
CREATE TABLE commande (
    id_cmd SERIAL,
    date_cmd DATE NOT NULL,
    montant DECIMAL(10,2)
) PARTITION BY RANGE (date_cmd);

CREATE TABLE commande_2025 PARTITION OF commande
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

Le partitionnement améliore aussi la maintenance (suppression rapide d’une partition entière). Les retours d’expérience partagés par ISOSET montrent des gains de performance spectaculaires après mise en place de partitions.

8. Bonnes pratiques générales

Évitez SELECT * – ne lisez que les colonnes nécessaires pour réduire les transferts.
Utilisez LIMIT en développement – pour tester vos requêtes sans impact sur la base.
Préférez EXISTS à COUNT(*) > 0 – EXISTS s’arrête dès qu’une ligne correspond.
Surveillez les requêtes lentes – activez le slow_query_log (MySQL) ou log_min_duration_statement (PostgreSQL).
Mettez à jour les statistiques – ANALYZE (PostgreSQL) ou OPTIMIZE TABLE (MySQL) pour aider l’optimiseur.

9. Exemple d’optimisation complète

Scénario : recherche des commandes d’un client sur une période, avec tri par date.

sql

-- Version initiale (lente)
SELECT * FROM commande WHERE client_id = 1234 AND EXTRACT(YEAR FROM date_cmd) = 2025 ORDER BY date_cmd;

Problèmes : SELECT *, fonction sur date_cmd (casse l’index), pas d’index sur (client_id, date_cmd).

Optimisation :

sql

-- Créer l’index composite adapté
CREATE INDEX idx_cli_date ON commande(client_id, date_cmd);

-- Réécrire la requête
SELECT id_cmd, date_cmd, montant FROM commande 
WHERE client_id = 1234 AND date_cmd >= '2025-01-01' AND date_cmd < '2026-01-01'
ORDER BY date_cmd;

Gain potentiel : passage de plusieurs secondes à quelques millisecondes sur 10 millions de lignes. Ce type d’exercice pratique est au cœur des ateliers ISOSET.

Se former à l’optimisation SQL avec ISOSET

Maîtriser les plans d’exécution, les index, la réécriture de requêtes et le partitionnement est indispensable pour tout professionnel manipulant des bases volumineuses. ISOSET propose des formations complètes :

Optimisation SQL – niveau 1 (2 jours) – lecture de plans, index B‑tree, réécriture des requêtes courantes.
Optimisation SQL – niveau 2 (2 jours) – index avancés (partiels, GIN), partitionnement, vues matérialisées, jointures complexes.
Atelier sur mesure – audit de vos requêtes les plus lentes et correction guidée.

Les formateurs ISOSET sont des experts en bases de données (PostgreSQL, MySQL, Oracle) forts de nombreuses optimisations en conditions réelles. Chaque stagiaire repart avec des méthodes systématiques pour diagnostiquer et corriger les problèmes de performance, ainsi qu’un accès à des exercices corrigés. Les témoignages d’anciens apprenants confirment des gains de temps d’exécution divisés par 10 ou 100 après la formation.