Modèles d'Optimisation SQL

Transformez les requêtes lentes en opérations ultra-rapides grâce à l'optimisation systématique, l'indexation appropriée et l'analyse des plans de requête.

Quand utiliser cette compétence

Déboguer les requêtes à exécution lente
Concevoir des schémas de base de données performants
Optimiser les temps de réponse des applications
Réduire la charge et les coûts de base de données
Améliorer la scalabilité pour les ensembles de données croissants
Analyser les plans de requête EXPLAIN
Implémenter des index efficaces
Résoudre les problèmes de requêtes N+1

Concepts fondamentaux

1. Plans d'exécution de requête (EXPLAIN)

Comprendre la sortie EXPLAIN est fondamental pour l'optimisation.

EXPLAIN PostgreSQL :

-- Explain basique
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

-- Avec statistiques d'exécution réelles
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'user@example.com';

-- Sortie détaillée avec plus d'informations
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.*, o.order_total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > NOW() - INTERVAL '30 days';

Métriques clés à surveiller :

Seq Scan : Balayage complet de table (généralement lent pour les grandes tables)
Index Scan : Utilisation d'index (bon)
Index Only Scan : Utilisation d'index sans accès à la table (excellent)
Nested Loop : Méthode de jointure (acceptable pour petits ensembles)
Hash Join : Méthode de jointure (bon pour ensembles plus grands)
Merge Join : Méthode de jointure (bon pour données triées)
Cost : Coût estimé de la requête (plus bas est mieux)
Rows : Nombre estimé de lignes retournées
Actual Time : Temps d'exécution réel

2. Stratégies d'indexation

Les index sont l'outil d'optimisation le plus puissant.

Types d'index :

B-Tree : Par défaut, bon pour requêtes d'égalité et plages
Hash : Seulement pour comparaisons d'égalité (=)
GIN : Recherche full-text, requêtes sur tableaux, JSONB
GiST : Données géométriques, recherche full-text
BRIN : Block Range INdex pour très grandes tables avec corrélation

-- Index B-Tree standard
CREATE INDEX idx_users_email ON users(email);

-- Index composite (l'ordre importe !)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Index partiel (indexe un sous-ensemble de lignes)
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';

-- Index sur expression
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- Index couvrant (inclut colonnes supplémentaires)
CREATE INDEX idx_users_email_covering ON users(email)
INCLUDE (name, created_at);

-- Index full-text search
CREATE INDEX idx_posts_search ON posts
USING GIN(to_tsvector('english', title || ' ' || body));

-- Index JSONB
CREATE INDEX idx_metadata ON events USING GIN(metadata);

3. Modèles d'optimisation de requête

Éviter SELECT * :

-- Mauvais : Récupère des colonnes inutiles
SELECT * FROM users WHERE id = 123;

-- Bon : Récupérer seulement ce dont vous avez besoin
SELECT id, email, name FROM users WHERE id = 123;

Utiliser la clause WHERE efficacement :

-- Mauvais : Fonction empêche l'utilisation d'index
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- Bon : Créer un index fonctionnel ou utiliser correspondance exacte
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Puis :
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- Ou stocker les données normalisées
SELECT * FROM users WHERE email = 'user@example.com';

Optimiser les JOINs :

-- Mauvais : Produit cartésien puis filtrage
SELECT u.name, o.total
FROM users u, orders o
WHERE u.id = o.user_id AND u.created_at > '2024-01-01';

-- Bon : Filtrer avant la jointure
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01';

-- Mieux : Filtrer les deux tables
SELECT u.name, o.total
FROM (SELECT * FROM users WHERE created_at > '2024-01-01') u
JOIN orders o ON u.id = o.user_id;

Modèles d'optimisation

Modèle 1 : Éliminer les requêtes N+1

Problème : Anti-modèle N+1 Query

# Mauvais : Exécute N+1 requêtes
users = db.query("SELECT * FROM users LIMIT 10")
for user in users:
    orders = db.query("SELECT * FROM orders WHERE user_id = ?", user.id)
    # Traiter les commandes

Solution : Utiliser les JOINs ou le chargement par batch

-- Solution 1 : JOIN
SELECT
    u.id, u.name,
    o.id as order_id, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id IN (1, 2, 3, 4, 5);

-- Solution 2 : Requête par batch
SELECT * FROM orders
WHERE user_id IN (1, 2, 3, 4, 5);

# Bon : Requête unique avec JOIN ou chargement par batch
# Utilisant JOIN
results = db.query("""
    SELECT u.id, u.name, o.id as order_id, o.total
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE u.id IN (1, 2, 3, 4, 5)
""")

# Ou chargement par batch
users = db.query("SELECT * FROM users LIMIT 10")
user_ids = [u.id for u in users]
orders = db.query(
    "SELECT * FROM orders WHERE user_id IN (?)",
    user_ids
)
# Grouper les commandes par user_id
orders_by_user = {}
for order in orders:
    orders_by_user.setdefault(order.user_id, []).append(order)

Modèle 2 : Optimiser la pagination

Mauvais : OFFSET sur grandes tables

-- Lent pour de grands décalages
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;  -- Très lent !

Bon : Pagination basée sur curseur

-- Beaucoup plus rapide : Utiliser curseur (dernier ID vu)
SELECT * FROM users
WHERE created_at < '2024-01-15 10:30:00'  -- Dernier curseur
ORDER BY created_at DESC
LIMIT 20;

-- Avec tri composite
SELECT * FROM users
WHERE (created_at, id) < ('2024-01-15 10:30:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Nécessite un index
CREATE INDEX idx_users_cursor ON users(created_at DESC, id DESC);

Modèle 3 : Agréger efficacement

Optimiser les requêtes COUNT :

-- Mauvais : Compte toutes les lignes
SELECT COUNT(*) FROM orders;  -- Lent sur grandes tables

-- Bon : Utiliser estimations pour comptages approximatifs
SELECT reltuples::bigint AS estimate
FROM pg_class
WHERE relname = 'orders';

-- Bon : Filtrer avant de compter
SELECT COUNT(*) FROM orders
WHERE created_at > NOW() - INTERVAL '7 days';

-- Mieux : Utiliser index-only scan
CREATE INDEX idx_orders_created ON orders(created_at);
SELECT COUNT(*) FROM orders
WHERE created_at > NOW() - INTERVAL '7 days';

Optimiser GROUP BY :

-- Mauvais : Grouper puis filtrer
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 10;

-- Mieux : Filtrer d'abord, puis grouper (si possible)
SELECT user_id, COUNT(*) as order_count
FROM orders
WHERE status = 'completed'
GROUP BY user_id
HAVING COUNT(*) > 10;

-- Mieux : Utiliser index couvrant
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

Modèle 4 : Optimisation de sous-requête

Transformer les sous-requêtes corrélées :

-- Mauvais : Sous-requête corrélée (s'exécute pour chaque ligne)
SELECT u.name, u.email,
    (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count
FROM users u;

-- Bon : JOIN avec agrégation
SELECT u.name, u.email, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name, u.email;

-- Mieux : Utiliser window functions
SELECT DISTINCT ON (u.id)
    u.name, u.email,
    COUNT(o.id) OVER (PARTITION BY u.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

Utiliser les CTEs pour la clarté :

-- Utilisant Common Table Expressions
WITH recent_users AS (
    SELECT id, name, email
    FROM users
    WHERE created_at > NOW() - INTERVAL '30 days'
),
user_order_counts AS (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    WHERE created_at > NOW() - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT ru.name, ru.email, COALESCE(uoc.order_count, 0) as orders
FROM recent_users ru
LEFT JOIN user_order_counts uoc ON ru.id = uoc.user_id;

Modèle 5 : Opérations par batch

INSERT par batch :

-- Mauvais : Plusieurs insertions individuelles
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');
INSERT INTO users (name, email) VALUES ('Carol', 'carol@example.com');

-- Bon : Insertion par batch
INSERT INTO users (name, email) VALUES
    ('Alice', 'alice@example.com'),
    ('Bob', 'bob@example.com'),
    ('Carol', 'carol@example.com');

-- Mieux : Utiliser COPY pour insertions en masse (PostgreSQL)
COPY users (name, email) FROM '/tmp/users.csv' CSV HEADER;

UPDATE par batch :

-- Mauvais : UPDATE en boucle
UPDATE users SET status = 'active' WHERE id = 1;
UPDATE users SET status = 'active' WHERE id = 2;
-- ... répéter pour plusieurs IDs

-- Bon : UPDATE unique avec clause IN
UPDATE users
SET status = 'active'
WHERE id IN (1, 2, 3, 4, 5, ...);

-- Mieux : Utiliser table temporaire pour gros batches
CREATE TEMP TABLE temp_user_updates (id INT, new_status VARCHAR);
INSERT INTO temp_user_updates VALUES (1, 'active'), (2, 'active'), ...;

UPDATE users u
SET status = t.new_status
FROM temp_user_updates t
WHERE u.id = t.id;

Techniques avancées

Vues matérialisées

Pré-calculer les requêtes coûteuses.

-- Créer une vue matérialisée
CREATE MATERIALIZED VIEW user_order_summary AS
SELECT
    u.id,
    u.name,
    COUNT(o.id) as total_orders,
    SUM(o.total) as total_spent,
    MAX(o.created_at) as last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Ajouter index à la vue matérialisée
CREATE INDEX idx_user_summary_spent ON user_order_summary(total_spent DESC);

-- Rafraîchir la vue matérialisée
REFRESH MATERIALIZED VIEW user_order_summary;

-- Rafraîchissement concurrent (PostgreSQL)
REFRESH MATERIALIZED VIEW CONCURRENTLY user_order_summary;

-- Requête sur vue matérialisée (très rapide)
SELECT * FROM user_order_summary
WHERE total_spent > 1000
ORDER BY total_spent DESC;

Partitionnement

Diviser les grandes tables pour de meilleures performances.

-- Partitionnement par plage de dates (PostgreSQL)
CREATE TABLE orders (
    id SERIAL,
    user_id INT,
    total DECIMAL,
    created_at TIMESTAMP
) PARTITION BY RANGE (created_at);

-- Créer les partitions
CREATE TABLE orders_2024_q1 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE orders_2024_q2 PARTITION OF orders
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- Les requêtes utilisent automatiquement la partition appropriée
SELECT * FROM orders
WHERE created_at BETWEEN '2024-02-01' AND '2024-02-28';
-- Scanne seulement la partition orders_2024_q1

Hints de requête et optimisation

-- Forcer l'utilisation d'index (MySQL)
SELECT * FROM users
USE INDEX (idx_users_email)
WHERE email = 'user@example.com';

-- Requête parallèle (PostgreSQL)
SET max_parallel_workers_per_gather = 4;
SELECT * FROM large_table WHERE condition;

-- Join hints (PostgreSQL)
SET enable_nestloop = OFF;  -- Forcer hash ou merge join

Meilleures pratiques

Indexer sélectivement : Trop d'index ralentissent les écritures
Surveiller les performances des requêtes : Utiliser slow query logs
Maintenir les statistiques à jour : Exécuter ANALYZE régulièrement
Utiliser les types de données appropriés : Types plus petits = meilleures performances
Normaliser réfléchie : Équilibrer normalisation vs performance
Cacher les données fréquemment accédées : Utiliser cache au niveau application
Connection Pooling : Réutiliser les connexions à la base de données
Maintenance régulière : VACUUM, ANALYZE, reconstruire indexes

-- Mettre à jour les statistiques
ANALYZE users;
ANALYZE VERBOSE orders;

-- Vacuum (PostgreSQL)
VACUUM ANALYZE users;
VACUUM FULL users;  -- Récupérer l'espace (verrouille la table)

-- Réindexer
REINDEX INDEX idx_users_email;
REINDEX TABLE users;

Pièges courants

Sur-indexation : Chaque index ralentit INSERT/UPDATE/DELETE
Indexes inutilisés : Gaspillent de l'espace et ralentissent les écritures
Indexes manquants : Requêtes lentes, balayages complets de table
Conversion de type implicite : Empêche l'utilisation d'index
Conditions OR : Impossible d'utiliser les indexes efficacement
LIKE avec wildcard de tête : LIKE '%abc' ne peut pas utiliser d'index
Fonction dans WHERE : Empêche l'utilisation d'index sauf si index fonctionnel existe

Surveiller les requêtes

-- Trouver les requêtes lentes (PostgreSQL)
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- Trouver les indexes manquants (PostgreSQL)
SELECT
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    seq_tup_read / seq_scan AS avg_seq_tup_read
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 10;

-- Trouver les indexes inutilisés (PostgreSQL)
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

sql-optimization-patterns