SQL Avancé : optimisation des bases de données

Tu maîtrises l'INNER JOIN et le LEFT JOIN de base. Ce chapitre attaque les jointures telles qu'on les rencontre vraiment en production : celles qui répondent à des questions plus fines que « croise A et B ». Trouver ce qui manque, réconcilier deux référentiels incohérents, ou récupérer les N derniers éléments par groupe — autant de besoins réels qui demandent des motifs de jointure plus élaborés.

Le premier motif à maîtriser est l'anti-jointure : trouver les lignes d'une table qui n'ont aucune correspondance dans une autre (clients sans commande, produits jamais vendus). On peut l'écrire avec LEFT JOIN ... WHERE droite.id IS NULL, mais la variante NOT EXISTS est souvent plus rapide et surtout plus sûre. Car il y a un piège redoutable : NOT IN sur une sous-requête renvoie zéro ligne dès qu'un NULL s'y glisse, à cause de la logique à trois valeurs de SQL (une comparaison avec NULL vaut « inconnu », jamais « vrai »). Retiens la règle : pour une anti-jointure, préfère toujours NOT EXISTS.

Deux autres outils complètent ta boîte. Le FULL OUTER JOIN garde toutes les lignes des deux tables, avec des NULL là où il n'y a pas de correspondance : idéal pour réconcilier deux sources de données et repérer ce qui existe d'un côté mais pas de l'autre. Et la jointure LATERAL (en PostgreSQL) permet une sous-requête corrélée performante : pour chaque ligne de la table principale, elle exécute une sous-requête qui peut référencer cette ligne. C'est la solution élégante au classique « les 3 dernières commandes de chaque client », un besoin que les jointures ordinaires peinent à exprimer proprement.

Anti-jointure : trouver ce qui manque

-- Clients sans aucune commande (anti-jointure)
SELECT c.id, c.nom
FROM clients c
LEFT JOIN commandes o ON o.client_id = c.id
WHERE o.id IS NULL;

-- Variante avec NOT EXISTS (souvent plus rapide)
SELECT c.id, c.nom
FROM clients c
WHERE NOT EXISTS (
  SELECT 1 FROM commandes o WHERE o.client_id = c.id
);

FULL OUTER JOIN

Garde toutes les lignes des deux côtés, NULL où il n'y a pas de correspondance. Utile pour réconcilier deux référentiels.

SELECT COALESCE(a.id, b.id) AS id, a.donnee_source, b.donnee_cible
FROM source a
FULL OUTER JOIN cible b ON a.id = b.id;

LATERAL : sous-requête corrélée performante (Postgres)

-- Pour chaque client, les 3 dernières commandes
SELECT c.id, c.nom, d.id AS commande_id, d.total
FROM clients c
LEFT JOIN LATERAL (
  SELECT id, total
  FROM commandes
  WHERE client_id = c.id
  ORDER BY cree_le DESC
  LIMIT 3
) d ON true;

À mesure que tes requêtes se complexifient, elles deviennent illisibles : des sous-requêtes imbriquées sur trois niveaux que personne, toi compris·e, ne comprend six mois plus tard. Les CTE (Common Table Expressions), introduites par le mot-clé WITH, résolvent élégamment ce problème. Une CTE nomme un résultat intermédiaire que tu peux ensuite réutiliser dans la requête principale, comme une variable temporaire. Tu lis la requête de haut en bas, étape nommée par étape nommée, au lieu de dérouler mentalement des parenthèses imbriquées.

Au-delà de la lisibilité, les CTE débloquent une capacité qu'aucune sous-requête classique n'offre : la récursion. Avec WITH RECURSIVE, une requête peut se référer à elle-même pour parcourir une structure arborescente ou un graphe. C'est la solution au problème classique des hiérarchies stockées en base : une table de catégories où chaque ligne pointe vers sa catégorie parente, un organigramme, un fil de commentaires imbriqués. La requête part d'un cas de base (les racines), puis remonte les enfants niveau par niveau jusqu'à épuisement, comme le montre l'exemple des catégories ci-dessous.

Une subtilité de performance mérite d'être connue. Historiquement, PostgreSQL matérialisait toujours les CTE — il les calculait une fois, stockait le résultat, ce qui pouvait bloquer certaines optimisations. Depuis PostgreSQL 12, les CTE non récursives sont inlinées par défaut, c'est-à-dire fusionnées dans la requête pour que le planificateur les optimise globalement. Si tu as besoin de forcer l'ancien comportement (par exemple pour calculer un résultat coûteux une seule fois), tu peux écrire WITH x AS MATERIALIZED (...). Un détail, mais qui explique parfois des écarts de performance surprenants entre versions.

CTE simple

WITH top_clients AS (
  SELECT client_id, SUM(total) AS ca
  FROM commandes
  WHERE cree_le >= '2026-01-01'
  GROUP BY client_id
  ORDER BY ca DESC
  LIMIT 10
)
SELECT c.nom, t.ca
FROM top_clients t
JOIN clients c ON c.id = t.client_id;

CTE récursive : parcourir une arborescence

-- Catégories en arborescence : parent_id pointe vers la catégorie parent
WITH RECURSIVE arbre AS (
  -- Cas de base : les racines (sans parent)
  SELECT id, parent_id, nom, 1 AS niveau
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- Récursion : on prend les enfants des nœuds déjà trouvés
  SELECT c.id, c.parent_id, c.nom, a.niveau + 1
  FROM categories c
  JOIN arbre a ON c.parent_id = a.id
)
SELECT * FROM arbre ORDER BY niveau, nom;

Les fonctions de fenêtrage sont probablement la fonctionnalité SQL la plus sous-utilisée par les débutant·es, et pourtant l'une des plus puissantes. Leur idée : effectuer un calcul d'agrégat (un classement, un cumul, une moyenne glissante) sans écraser les lignes. Là où un GROUP BY réduit dix lignes à une, une fonction de fenêtrage garde tes dix lignes et ajoute une colonne calculée sur chacune. La clause OVER (...) définit la « fenêtre » de lignes sur laquelle le calcul s'applique, avec éventuellement un PARTITION BY pour regrouper et un ORDER BY pour ordonner.

Le premier usage est le classement, avec trois fonctions à distinguer soigneusement. ROW_NUMBER attribue un numéro séquentiel unique (1, 2, 3, 4). RANK donne le même rang aux ex-æquo mais saute ensuite (1, 2, 2, 4). DENSE_RANK donne le même rang aux ex-æquo sans sauter (1, 2, 2, 3). Le choix dépend de ce que tu veux exprimer : « le 3ᵉ meilleur » n'a pas le même sens selon que tu comptes les ex-æquo ou non. Combinées à PARTITION BY, elles répondent à des questions du type « la commande la plus chère par client ».

Les autres usages sont tout aussi précieux pour l'analyse. LAG et LEAD donnent accès à la valeur de la ligne précédente ou suivante, ce qui permet de calculer une variation (le CA de ce mois moins celui du mois d'avant) en une seule requête. Et en combinant une fonction d'agrégat avec un ORDER BY et un cadre de lignes (ROWS BETWEEN ...), on obtient des cumuls et des moyennes glissantes — le CA cumulé depuis le début de l'année, la moyenne sur sept jours. Ces calculs, cauchemardesques à faire côté application, deviennent triviaux et rapides quand on laisse la base les exécuter.

ROW_NUMBER, RANK, DENSE_RANK

-- Classement des commandes par client
SELECT
  client_id,
  id,
  total,
  ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY total DESC) AS rang
FROM commandes;

• ROW_NUMBER : numéro séquentiel unique (1, 2, 3, 4...)
• RANK : ex-aequo ont le même rang, sauts ensuite (1, 2, 2, 4)
• DENSE_RANK : ex-aequo ont le même rang, sans sauts (1, 2, 2, 3)

LAG et LEAD : valeur précédente, suivante

-- Évolution du CA mois par mois
SELECT
  mois,
  ca,
  LAG(ca) OVER (ORDER BY mois) AS ca_mois_precedent,
  ca - LAG(ca) OVER (ORDER BY mois) AS variation
FROM ca_mensuel;

Cumul et moyenne mobile

-- Cumul du CA depuis le début de l'année
SELECT
  jour,
  ca_jour,
  SUM(ca_jour) OVER (ORDER BY jour) AS ca_cumule
FROM ca_journalier;

-- Moyenne glissante sur 7 jours
SELECT
  jour,
  ca_jour,
  AVG(ca_jour) OVER (
    ORDER BY jour
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moyenne_7j
FROM ca_journalier;

Le GROUP BY de base, tu le connais : regrouper des lignes et calculer un agrégat par groupe. Ce chapitre montre comment aller plus loin pour construire de vrais rapports d'analyse, du genre de ceux qu'on attend dans un tableau de bord. Deux besoins reviennent : produire des sous-totaux à plusieurs niveaux, et calculer des agrégats conditionnels côte à côte.

Pour les sous-totaux, SQL propose des extensions du GROUP BY. ROLLUP (region, produit) calcule en une seule passe le CA par région et par produit, plus le sous-total par région, plus le total général — exactement la structure d'un rapport hiérarchique. CUBE va encore plus loin en produisant toutes les combinaisons possibles des dimensions. Là où il faudrait autrefois plusieurs requêtes réunies par UNION, une seule ligne de code fait le travail, et le moteur l'optimise mieux qu'un empilement manuel.

Pour les agrégats conditionnels, PostgreSQL offre la clause FILTER, d'une élégance remarquable. Écrire COUNT(*) FILTER (WHERE statut = 'PAYEE') à côté de COUNT(*) FILTER (WHERE statut = 'ANNULEE') te donne, dans une seule requête et par groupe, le nombre de commandes payées et annulées côte à côte. C'est l'équivalent moderne et lisible du vieil idiome SUM(CASE WHEN ... THEN ... END), plus verbeux et plus facile à rater. Ces trois outils — ROLLUP, CUBE et FILTER — transforment SQL en un véritable moteur de reporting, sans passer par un outil externe.

GROUPING SETS, ROLLUP, CUBE

-- Sous-totaux par région ET par produit ET total général, en une passe
SELECT region, produit, SUM(ca) AS ca
FROM ventes
GROUP BY ROLLUP (region, produit);

-- Toutes les combinaisons (region, produit, region+produit, total)
SELECT region, produit, SUM(ca) AS ca
FROM ventes
GROUP BY CUBE (region, produit);

Agrégats conditionnels avec FILTER (Postgres)

SELECT
  region,
  COUNT(*) FILTER (WHERE statut = 'PAYEE') AS payees,
  COUNT(*) FILTER (WHERE statut = 'ANNULEE') AS annulees,
  SUM(total) FILTER (WHERE statut = 'PAYEE') AS ca_paye
FROM commandes
GROUP BY region;

S'il fallait ne retenir qu'un levier d'optimisation, ce serait l'index. Un bon index peut faire passer une requête de trente secondes à trois millisecondes — un facteur dix mille. Le principe, tu le connais : un index est une structure de données annexe, triée, qui permet au moteur de trouver directement les lignes recherchées au lieu de parcourir toute la table. Mais bien indexer ne se résume pas à « mettre un index sur les colonnes filtrées » ; c'est un art qui demande de comprendre les types d'index et leur fonctionnement.

Le type par défaut, le B-tree, couvre l'écrasante majorité des cas : égalité, plages, tri. Deux variantes méritent une attention particulière. L'index composé, sur plusieurs colonnes, obéit à une règle capitale : celle du préfixe gauche. Un index sur (client_id, cree_le) accélère un filtre sur client_id seul, ou sur les deux, mais pas sur cree_le seul — car l'index est trié d'abord par la première colonne. L'ordre des colonnes dans un index composé n'est donc jamais arbitraire. L'index partiel, lui, ne couvre qu'un sous-ensemble de lignes (WHERE statut = 'EN_ATTENTE') : minuscule et ultra-rapide quand tu cherches justement cette petite fraction. PostgreSQL ajoute des types spécialisés comme GIN (pour le JSON, les tableaux, la recherche plein-texte) et BRIN (pour les très grosses tables naturellement triées, comme des logs).

Mais l'index a un coût qu'il ne faut jamais oublier, et c'est le contrepoint essentiel de ce chapitre : chaque index doit être mis à jour à chaque écriture sur la colonne concernée. Sur une table qui reçoit beaucoup d'INSERT et d'UPDATE, dix index, c'est potentiellement dix fois plus lent en écriture. Ajouter un index n'accélère donc pas toujours : un index inutile est du coût pur, et si une requête ramène de toute façon une grande partie de la table, le moteur préférera l'ignorer. La discipline à adopter : ne crée un index que lorsque tu as la preuve qu'il sert, mesurée avec EXPLAIN ANALYZE — l'outil du chapitre suivant.

Les types d'index utiles

• B-tree : défaut, pour égalité et plages (=, <, BETWEEN, ORDER BY)
• Index composé : sur plusieurs colonnes, respecte l'ordre du WHERE
• Index partiel : ne couvre qu'une partie des lignes (ex : WHERE actif = true)
• GIN (Postgres) : pour jsonb, tableaux, recherche plein-texte
• BRIN (Postgres) : pour grosses tables triées (ex : logs par date)

Index composé : ordre des colonnes

-- Cas typique : on filtre par client et on trie par date
CREATE INDEX idx_cmd_client_date
  ON commandes (client_id, cree_le DESC);

-- Cet index accélère :
SELECT * FROM commandes WHERE client_id = 42 ORDER BY cree_le DESC;
-- ET aussi : WHERE client_id = 42 (le préfixe gauche suffit)

-- Mais PAS : WHERE cree_le > '2026-01-01' tout seul
-- (la colonne client_id manque en préfixe)

Index partiel

-- Si 99% des commandes sont 'PAYEE', un index global est inutile.
-- Mais on cherche souvent les 1% qui restent :
CREATE INDEX idx_cmd_pending
  ON commandes (cree_le)
  WHERE statut = 'EN_ATTENTE';

-- Index minuscule, ultra rapide sur les requêtes filtrées par statut.

Face à une requête lente, la pire réaction est d'ajouter des index au hasard en espérant que ça aille mieux. La bonne démarche commence par comprendre ce que fait réellement le moteur, et pour ça il existe un outil : EXPLAIN. Savoir lire un plan d'exécution est une compétence qui vaut de l'or, parce qu'elle transforme l'optimisation d'une superstition en une démarche méthodique : tu vois exactement où le temps se passe, et donc quoi corriger.

Distingue bien deux variantes. EXPLAIN requête affiche le plan estimé sans exécuter la requête — utile mais basé sur des prévisions. EXPLAIN ANALYZE requête exécute réellement la requête et te donne les temps mesurés, ce qui est bien plus fiable. Ajoute l'option BUFFERS pour voir en prime les lectures disque. Attention à un piège : puisque EXPLAIN ANALYZE exécute vraiment, sur un UPDATE ou un DELETE il modifie tes données — encadre-le alors d'un BEGIN; ... ROLLBACK; pour tester sans laisser de trace.

Que chercher dans un plan ? Le signal d'alarme numéro un est un Seq Scan sur une grosse table alors que le filtre ne ramène que quelques lignes : c'est presque toujours un index manquant. À l'inverse, un Index Scan est bon signe. Nuance importante : sur une petite table, un Seq Scan peut être volontairement choisi par le planificateur car parcourir tout est moins coûteux que passer par l'index — ce n'est un défaut que sur les grosses tables. Surveille aussi l'écart entre les lignes estimées et réelles : un gros décalage signale des statistiques périmées, à rafraîchir avec ANALYZE table;. Le scénario ci-dessous te fait dérouler exactement cette enquête sur une requête devenue lente en production.

EXPLAIN vs EXPLAIN ANALYZE

• EXPLAIN requête : plan estimé, sans exécution
• EXPLAIN ANALYZE requête : plan + exécution réelle + timings
• EXPLAIN (ANALYZE, BUFFERS) requête : ajoute le détail des pages lues

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM commandes
WHERE client_id = 42 AND cree_le > NOW() - INTERVAL '30 days';

Ce qu'il faut repérer

• Seq Scan sur grosse table : index manquant, presque toujours à corriger
• Index Scan : bon signe (le moteur utilise un index)
• Bitmap Heap Scan : convenable pour des filtres qui ramènent beaucoup de lignes
• Écart entre rows estimé et rows réel : statistiques à rafraîchir avec ANALYZE table;
• BUFFERS : shared read = lectures disque (lent). shared hit = cache (rapide).

Tant qu'une seule personne écrit dans la base, tout est simple. Le problème surgit quand plusieurs sessions écrivent en même temps : des anomalies subtiles peuvent apparaître, des bugs qui ne se manifestent qu'en production sous charge et qu'aucun test unitaire n'attrape. Comprendre comment la base gère la concurrence, c'est se prémunir contre cette catégorie de bugs invisibles — parmi les plus difficiles à diagnostiquer qui soient.

Tout repose sur les quatre garanties ACID qu'offre une transaction. L'atomicité (tout passe ou rien), la cohérence (les contraintes restent respectées), l'isolation (les transactions concurrentes ne se marchent pas dessus) et la durabilité (un commit survit à un crash). C'est l'isolation qui est la plus subtile, car elle se règle par niveaux. READ COMMITTED (défaut PostgreSQL) garantit que tu ne lis que des données validées. REPEATABLE READ (défaut MySQL) garantit en plus que deux lectures identiques dans une transaction renvoient le même résultat. SERIALIZABLE, le plus strict, fait comme si les transactions s'exécutaient les unes après les autres.

Pourquoi ces niveaux comptent-ils ? Parce qu'un niveau trop permissif laisse passer des anomalies de concurrence. L'exemple du write skew est parlant : deux médecins vérifient chacun de leur côté qu'ils ne sont pas le seul de garde, concluent que oui, et se déclarent tous deux absents — chaque lecture était correcte à son instant, mais le résultat final viole la règle métier. Pour éviter ça, on monte le niveau d'isolation (SERIALIZABLE) ou on prend un verrou explicite avec SELECT ... FOR UPDATE, qui bloque la ligne jusqu'à la fin de la transaction. Savoir quand recourir à ces protections — typiquement sur les soldes de comptes, les stocks, les réservations — fait la différence entre une application qui tient la charge et une qui corrompt silencieusement ses données.

ACID : les 4 garanties

• Atomicité : tout passe ou rien
• Cohérence : la base reste valide (contraintes respectées)
• Isolation : les transactions concurrentes ne se voient pas l'une l'autre
• Durabilité : un commit survit à un crash

Les niveaux d'isolation

• READ COMMITTED (défaut Postgres) : tu lis toujours des données committées
• REPEATABLE READ (défaut MySQL) : deux lectures successives renvoient le même résultat dans la transaction
• SERIALIZABLE : comme si les transactions s'exécutaient en série, sans concurrence

Verrouillage explicite : SELECT FOR UPDATE

BEGIN;
  -- Verrouille la ligne jusqu'à COMMIT/ROLLBACK
  SELECT solde FROM comptes WHERE id = 1 FOR UPDATE;
  -- Calcul...
  UPDATE comptes SET solde = solde - 100 WHERE id = 1;
COMMIT;