Performance et optimisation SQL

🎯 Objectif : À la fin de ce chapitre, tu sauras diagnostiquer et résoudre les problèmes de performance SQL — index, EXPLAIN, requêtes lentes et bonnes pratiques de production. ⏱️ Durée estimée : 50 minutes | Niveau : Avancé

Pourquoi les performances SQL comptent

Une requête qui met 50ms en dev peut prendre 30 secondes en prod avec 10 millions de lignes. La différence entre une app fluide et une app inutilisable, c’est souvent une requête mal optimisée.

Les trois leviers principaux :

• Les index → accélérer les lectures sans toucher au code
• EXPLAIN ANALYZE → comprendre ce que fait vraiment le moteur SQL
• L’écriture des requêtes → éviter les anti-patterns qui tuent les perfs

💡 Ce chapitre utilise PostgreSQL pour les exemples, mais les concepts s’appliquent à MySQL, SQL Server et tout SGBD relationnel.

Les index : accélérer les recherches

Comment fonctionne un index

Sans index, le moteur SQL parcourt toutes les lignes de la table pour trouver celles qui correspondent (Seq Scan). Avec un index, il utilise une structure arborescente (B-tree) pour aller directement aux bonnes lignes.

-- Créer un index simple
CREATE INDEX idx_servers_status ON servers(status);

-- Index composite (plusieurs colonnes)
CREATE INDEX idx_servers_env_status ON servers(environment, status);

-- Index unique (garantit l'unicité + accélère)
CREATE UNIQUE INDEX idx_servers_hostname ON servers(hostname);

-- Index partiel (seulement certaines lignes)
CREATE INDEX idx_servers_running ON servers(hostname)
WHERE status = 'running';

🔥 L’ordre des colonnes dans un index composite compte. (environment, status) accélère les recherches par environment seul ou environment + status, mais pas par status seul.

Quand créer un index

Règle simple : indexe les colonnes qui apparaissent dans WHERE, JOIN, ORDER BY et GROUP BY de tes requêtes fréquentes.

-- Cette requête bénéficie d'un index sur (environment, status)
SELECT hostname, ip_address
FROM servers
WHERE environment = 'production' AND status = 'running';

-- Cette requête bénéficie d'un index sur server_id (FK)
SELECT s.hostname, svc.name
FROM servers s
JOIN services svc ON s.server_id = svc.server_id;

-- Vérifier les index existants (PostgreSQL)
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'servers';

Le coût des index

Les index ne sont pas gratuits. Chaque INSERT, UPDATE ou DELETE doit aussi mettre à jour les index. Trop d’index sur une table à forte écriture ralentit les modifications.

⚠️ Règle d’équilibre : une table typique a entre 3 et 8 index. Au-delà, audite et supprime les index inutilisés.

-- Trouver les index inutilisés (PostgreSQL)
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Supprimer un index inutile
DROP INDEX IF EXISTS idx_servers_old_column;

EXPLAIN ANALYZE : radiographie des requêtes

Lire un plan d’exécution

EXPLAIN ANALYZE exécute la requête et montre exactement ce que fait le moteur SQL — type de scan, coûts, temps réel.

EXPLAIN ANALYZE
SELECT s.hostname, s.environment, COUNT(svc.service_id) AS nb_services
FROM servers s
LEFT JOIN services svc ON s.server_id = svc.server_id
WHERE s.status = 'running'
GROUP BY s.hostname, s.environment;

Le résultat ressemble à ça :

HashAggregate  (cost=45.20..47.20 rows=200 width=64) (actual time=0.85..0.92 rows=15 loops=1)
  ->  Hash Left Join  (cost=12.50..40.20 rows=500 width=56) (actual time=0.35..0.65 rows=45 loops=1)
        Hash Cond: (s.server_id = svc.server_id)
        ->  Seq Scan on servers s  (cost=0.00..22.50 rows=200 width=48) (actual time=0.01..0.15 rows=200 loops=1)
              Filter: (status = 'running')
              Rows Removed by Filter: 800
        ->  Hash  (cost=10.00..10.00 rows=200 width=8) (actual time=0.18..0.18 rows=200 loops=1)
              ->  Seq Scan on services svc  (cost=0.00..10.00 rows=200 width=8) (actual time=0.01..0.10 rows=200 loops=1)
Planning Time: 0.15 ms
Execution Time: 1.05 ms

🎯 Ce qu’il faut regarder en priorité :

• Seq Scan sur une grosse table → probablement un index manquant
• Rows Removed by Filter élevé → la requête lit beaucoup de lignes pour en garder peu
• Actual time vs cost → le temps réel versus l’estimation du planificateur

Seq Scan vs Index Scan

-- AVANT index : Seq Scan (parcourt toute la table)
EXPLAIN ANALYZE
SELECT * FROM servers WHERE hostname = 'web-01';
-- Seq Scan on servers  (actual time=0.01..2.50 rows=1 loops=1)
--   Rows Removed by Filter: 9999

-- Créer l'index
CREATE INDEX idx_servers_hostname ON servers(hostname);

-- APRÈS index : Index Scan (va directement à la ligne)
EXPLAIN ANALYZE
SELECT * FROM servers WHERE hostname = 'web-01';
-- Index Scan using idx_servers_hostname  (actual time=0.02..0.03 rows=1 loops=1)

💡 Un Index Scan sur 10 000 lignes est typiquement 100x plus rapide qu’un Seq Scan. Sur 10 millions de lignes, c’est la différence entre 5ms et 30 secondes.

Optimiser les requêtes courantes

Éviter le SELECT *

-- ❌ Mauvais : ramène toutes les colonnes
SELECT * FROM servers WHERE environment = 'production';

-- ✅ Bon : seulement les colonnes nécessaires
SELECT hostname, ip_address, status FROM servers WHERE environment = 'production';

Le SELECT * force le moteur à lire toutes les colonnes du disque, même celles dont tu n’as pas besoin. Sur des tables larges (30+ colonnes), l’impact est significatif.

Pagination efficace

-- ❌ Mauvais avec OFFSET élevé (doit parcourir toutes les lignes précédentes)
SELECT * FROM logs ORDER BY created_at DESC LIMIT 20 OFFSET 100000;

-- ✅ Bon : pagination par curseur (keyset pagination)
SELECT * FROM logs
WHERE created_at < '2026-03-29 10:00:00'
ORDER BY created_at DESC
LIMIT 20;

⚠️ OFFSET 100000 force le moteur à lire et jeter 100 000 lignes avant de retourner les 20 suivantes. La pagination par curseur reste rapide quelle que soit la page.

Sous-requêtes vs JOIN

-- ❌ Sous-requête corrélée (exécutée pour chaque ligne)
SELECT hostname,
  (SELECT COUNT(*) FROM services WHERE server_id = s.server_id) AS nb_services
FROM servers s;

-- ✅ JOIN avec agrégation (une seule passe)
SELECT s.hostname, COUNT(svc.service_id) AS nb_services
FROM servers s
LEFT JOIN services svc ON s.server_id = svc.server_id
GROUP BY s.hostname;

Utiliser EXISTS au lieu de IN

-- ❌ IN avec sous-requête (peut être lent sur de gros jeux)
SELECT * FROM servers
WHERE server_id IN (SELECT server_id FROM alerts WHERE severity = 'critical');

-- ✅ EXISTS (s'arrête dès qu'il trouve une correspondance)
SELECT * FROM servers s
WHERE EXISTS (
  SELECT 1 FROM alerts a
  WHERE a.server_id = s.server_id AND a.severity = 'critical'
);

Cas entreprise : dashboard de monitoring lent

Contexte : un dashboard affiche les serveurs avec leurs services et dernières alertes. La page met 8 secondes à charger avec 50 000 serveurs.

Diagnostic :

-- La requête problématique
EXPLAIN ANALYZE
SELECT s.hostname, s.environment, s.status,
       COUNT(DISTINCT svc.service_id) AS nb_services,
       COUNT(DISTINCT a.alert_id) AS nb_alerts
FROM servers s
LEFT JOIN services svc ON s.server_id = svc.server_id
LEFT JOIN alerts a ON s.server_id = a.server_id AND a.resolved_at IS NULL
WHERE s.environment = 'production'
GROUP BY s.server_id, s.hostname, s.environment, s.status
ORDER BY s.hostname
LIMIT 50;

Résultat : trois Seq Scan, 8.2 secondes.

Solution en 3 étapes :

-- 1. Index sur le filtre principal
CREATE INDEX idx_servers_env ON servers(environment);

-- 2. Index sur les FK utilisées dans les JOIN
CREATE INDEX idx_services_server ON services(server_id);
CREATE INDEX idx_alerts_server_resolved ON alerts(server_id, resolved_at);

-- 3. Résultat : 3 Index Scan, 45ms
-- Gain : x180 plus rapide

🎯 Trois index bien placés ont transformé une requête inutilisable en une requête instantanée. C’est la réalité de l’optimisation SQL : 80% des gains viennent des index.

Les pièges classiques

⚠️ Fonctions sur colonnes indexées — WHERE LOWER(hostname) = 'web-01' ne peut pas utiliser un index sur hostname. Crée un index fonctionnel : CREATE INDEX idx_lower_hostname ON servers(LOWER(hostname)).

⚠️ OR dans les WHERE — WHERE status = 'running' OR environment = 'production' ne peut pas utiliser un index composite. Préfère un UNION de deux requêtes indexées.

⚠️ LIKE avec wildcard au début — WHERE hostname LIKE '%web%' force un Seq Scan. Seul LIKE 'web%' peut utiliser un index B-tree.

⚠️ N+1 queries — Charger 100 serveurs puis faire 100 requêtes pour leurs services. Toujours préférer un JOIN ou une requête avec IN.

⚠️ Pas de VACUUM/ANALYZE — En PostgreSQL, ANALYZE met à jour les statistiques que le planificateur utilise. Sans stats à jour, il choisit de mauvais plans. Active autovacuum et ne le désactive jamais.

Résumé

L’optimisation SQL se résume à trois réflexes :

• Indexer intelligemment → colonnes des WHERE, JOIN, ORDER BY. Pas trop, pas trop peu
• Lire le plan d’exécution → EXPLAIN ANALYZE révèle la vérité. Seq Scan sur une grosse table = alerte rouge
• Écrire des requêtes propres → pas de SELECT *, pagination par curseur, EXISTS plutôt que IN, JOINs plutôt que sous-requêtes corrélées

🎯 La performance SQL n’est pas de la magie — c’est de la méthodologie. Mesure, comprends le plan, ajoute l’index qui manque, remesure. Répète.

➡️ Félicitations ! Tu as terminé la série Apprendre SQL. Tu maîtrises maintenant les requêtes, les jointures, l’agrégation, les fonctions avancées, l’administration et l’optimisation.