Optimisation de Bases de Données : Index, Plans de Requêtes, Partitionnement & Cache

Une base de données n'est rapide que dans la mesure où on l'utilisé correctement. Ce guide couvre les techniques essentielles pour extraire la performance maximale de toute base de données — de la conception d'index au pooling de connexions.

Stratégies d'Indexation

Les index sont le levier de performance le plus impactant. Un index manquant transformé une requête de 2 ms en un scan complet de table de 20 secondes.

Index B-Tree (le défaut)

Les B-trees sont l'index de référence. Ils maintiennent des données triées et supportent l'égalité, les requêtes par plage et la correspondance par préfixe.

-- Recherche par égalité
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = 'alice@example.com';  -- index seek : O(log n)

-- Requête par plage
CREATE INDEX idx_orders_date ON orders(created_at);
SELECT * FROM orders WHERE created_at > '2026-01-01';  -- index range scan

Fonctionnement interne des B-trees :

                    ┌───────────────┐
                    │  [50 | 100]   │     ← racine (2 clés, 3 pointeurs)
                    └──┬────┬────┬──┘
                       │    │    │
              ┌────────┘    │    └────────┐
              ▼             ▼             ▼
        ┌──────────┐ ┌──────────┐ ┌──────────┐
        │ [10|30]  │ │ [60|80]  │ │[120|150] │  ← nœuds internes
        └─┬──┬──┬──┘ └─┬──┬──┬──┘ └─┬──┬──┬──┘
          │  │  │       │  │  │       │  │  │
          ▼  ▼  ▼       ▼  ▼  ▼       ▼  ▼  ▼
        [pages feuilles avec pointeurs vers les lignes]

Les pages font typiquement 8–16 Ko. Un B-tree à 3 niveaux peut indexer des milliards de lignes avec seulement 3 lectures disque par lookup.

Index Composites (Multi-Colonnes)

L'ordre des colonnes est crucial. La règle du préfixe gauche s'applique :

CREATE INDEX idx_orders_multi ON orders(customer_id, status, created_at);

-- ✅ Utilise l'index (préfixe gauche)
SELECT * FROM orders WHERE customer_id = 42;
SELECT * FROM orders WHERE customer_id = 42 AND status = 'shipped';
SELECT * FROM orders WHERE customer_id = 42 AND status = 'shipped' AND created_at > '2026-01-01';

-- ❌ Ne peut pas utiliser l'index (saute customer_id)
SELECT * FROM orders WHERE status = 'shipped';
SELECT * FROM orders WHERE created_at > '2026-01-01';

Règles pour l'ordre des colonnes :

1. Colonnes d'égalité en premier — colonnes utilisées avec =
2. Colonne de plage en dernier — la colonne utilisée avec >, <, BETWEEN
3. Haute sélectivité d'abord — colonnes qui filtrent le plus de lignes

Index Couvrants

Un index qui contient toutes les colonnes nécessaires à une requête — la base ne touché jamais la table.

-- Si votre requête est :
SELECT customer_id, status, total FROM orders WHERE customer_id = 42 AND status = 'shipped';

-- Un index couvrant :
CREATE INDEX idx_orders_covering ON orders(customer_id, status) INCLUDE (total);
-- INCLUDE de PostgreSQL évite d'alourdir les clés du B-tree tout en couvrant la requête

C'est un index-only scan — drastiquement plus rapide pour les charges en lecture.

Index Hash

Lookups en O(1) pour correspondance exacte, mais pas de requêtes par plage, pas d'ordonnancement, pas de correspondance par préfixe.

-- Index hash PostgreSQL
CREATE INDEX idx_sessions_token ON sessions USING hash(token);
-- Utile uniquement pour : WHERE token = 'abc123'

Cas d'usage : tokens de session, lookups par UUID, patterns de correspondance exacte uniquement.

Index GIN & GiST (PostgreSQL)

Index Partiels

N'indexer que les lignes qui comptent :

-- N'indexer que les commandes actives (80% des requêtes, 5% des lignes)
CREATE INDEX idx_active_orders ON orders(customer_id, created_at)
WHERE status != 'archived';

Index plus petit → lookups plus rapides, moins de stockage, écritures plus rapides.

Anti-Patterns d'Indexation

Lire les Plans de Requêtes

Si vous savez lire une sortie EXPLAIN, vous pouvez corriger 90 % des requêtes lentes.

EXPLAIN PostgreSQL

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, c.name, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
  AND o.created_at > '2026-01-01';

Nested Loop  (cost=0.85..124.50 rows=15 width=52) (actual time=0.05..0.30 rows=12 loops=1)
  Buffers: shared hit=48
  ->  Index Scan using idx_orders_status_date on orders o  (cost=0.43..62.15 rows=15 width=20)
        Index Cond: ((status = 'pending') AND (created_at > '2026-01-01'))
        Buffers: shared hit=12
  ->  Index Scan using customers_pkey on customers c  (cost=0.42..4.15 rows=1 width=36)
        Index Cond: (id = o.customer_id)
        Buffers: shared hit=36
Planning Time: 0.15 ms
Execution Time: 0.35 ms

Points clés à surveiller

EXPLAIN MySQL

EXPLAIN FORMAT=TREE
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';

Colonnes clés : type (ALL = scan complet, ref = index, const = lookup PK), key (quel index), rows (estimé), Extra (Using filesort, Using temporary = drapeaux rouges).

Partitionnement

Diviser les grandes tables en morceaux plus petits et gérables.

Partitionnement Horizontal (Sharding)

Chaque partition contient un sous-ensemble de lignes.

-- Partitionnement déclaratif PostgreSQL
CREATE TABLE events (
    id         BIGSERIAL,
    created_at TIMESTAMPTZ NOT NULL,
    event_type TEXT,
    payload    JSONB
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_q1 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');

CREATE TABLE events_2026_q2 PARTITION OF events
    FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');

-- Les requêtes routent automatiquement vers la bonne partition
SELECT * FROM events WHERE created_at = '2026-02-15';  -- ne scanne que events_2026_q1

Stratégies de partitionnement

Quand partitionner

• La table dépasse des centaines de millions de lignes
• Les requêtes filtrent toujours sur la clé de partition (le partition pruning fonctionne)
• Vous devez supprimer d'anciennes données à moindre coût (drop partition au lieu de DELETE)
• Les opérations de maintenance (VACUUM, REINDEX) sur la table complète sont trop lentes

Quand NE PAS partitionner

• La table a moins de ~10M lignes (un index suffit)
• Les requêtes ne filtrent pas sur la clé de partition (toutes les partitions scannées = pire que sans partitionnement)
• Vous devez interroger toutes les partitions fréquemment

Pooling de Connexions

Ouvrir une connexion à la base de données est coûteux (handshake TCP, TLS, authentification, création de processus dans PostgreSQL). Le pooling de connexions réutilise les connexions existantes.

PgBouncer (PostgreSQL)

; pgbouncer.ini
[databases]
mydb = host=127.0.0.1 dbname=mydb

[pgbouncer]
listen_port = 6432
pool_mode = transaction      ; libère la connexion après chaque transaction
max_client_conn = 1000       ; les clients peuvent ouvrir 1000 connexions
default_pool_size = 20       ; seulement 20 connexions PostgreSQL réelles

Pooling au niveau applicatif

La plupart des ORMs et drivers supportent le pooling :

# Pool de connexions SQLAlchemy
engine = create_engine(
    "postgresql://user:pass@localhost/db",
    pool_size=20,          # connexions en régime permanent
    max_overflow=10,       # connexions burst au-dessus de pool_size
    pool_timeout=30,       # attente avant erreur
    pool_recycle=1800,     # recycler les connexions après 30 min
)

Piège serverless

Lambda/Cloud Functions peuvent créer des milliers de connexions. Solutions :

• RDS Proxy (AWS) — pooler de connexions managé pour RDS/Aurora
• PgBouncer sur ECS — pooler auto-hébergé
• Neon / Supabase — pooling de connexions intégré
• Prisma Data Proxy — pooling applicatif pour le serverless

Stratégies de Cache

Cache-Aside (Lazy Loading)

def get_user(user_id):
    # 1. Vérifier le cache
    cached = redis.get(f"user:{user_id}")
    if cached:
        return json.loads(cached)

    # 2. Cache miss → interroger la base
    user = db.query("SELECT * FROM users WHERE id = %s", user_id)

    # 3. Remplir le cache
    redis.setex(f"user:{user_id}", 3600, json.dumps(user))  # TTL 1 heure
    return user

Avantages : Ne cache que ce qui est effectivement lu. Une panne de cache ne casse pas l'app. Inconvénients : La première requête est toujours un miss. Données obsolètes jusqu'à expiration du TTL.

Write-Through

def update_user(user_id, data):
    # 1. Écrire en base
    db.execute("UPDATE users SET ... WHERE id = %s", user_id)
    # 2. Mettre à jour le cache immédiatement
    redis.setex(f"user:{user_id}", 3600, json.dumps(data))

Avantages : Le cache est toujours à jour. Inconvénients : Écritures plus lentes (deux écritures). Le cache peut contenir des données jamais lues.

Write-Behind (Write-Back)

Écrire d'abord dans le cache, écrire en base de manière asynchrone plus tard. Performance d'écriture maximale mais risqué de perte de données si le cache crashe avant le flush.

Patterns d'Invalidation de Cache

« Il n'y a que deux choses difficiles en informatique : l'invalidation de cache et nommer les choses. » — Phil Karlton

Techniques d'Optimisation de Requêtes

Éviter SELECT *

-- ❌ Récupère toutes les colonnes, ne peut pas utiliser d'index couvrant
SELECT * FROM orders WHERE customer_id = 42;

-- ✅ Ne récupérer que les colonnes nécessaires
SELECT id, total, status FROM orders WHERE customer_id = 42;

Utiliser EXISTS au lieu de COUNT pour les vérifications d'existence

-- ❌ Compte toutes les lignes correspondantes
SELECT COUNT(*) > 0 FROM orders WHERE customer_id = 42 AND status = 'pending';

-- ✅ S'arrête au premier résultat
SELECT EXISTS(SELECT 1 FROM orders WHERE customer_id = 42 AND status = 'pending');

Opérations par lots

-- ❌ N+1 requêtes
for user_id in user_ids:
    db.query("SELECT * FROM users WHERE id = %s", user_id)

-- ✅ Une seule requête
db.query("SELECT * FROM users WHERE id = ANY(%s)", user_ids)

Utiliser les CTEs pour la lisibilité, les sous-requêtes pour la performance

Dans PostgreSQL < 12, les CTEs sont des barrières d'optimisation — le planificateur ne peut pas pousser les prédicats dedans. Dans PostgreSQL 12+, les CTEs simples sont inlinées. Mais vérifiez toujours le plan de requête.

Pagination : keyset vs OFFSET

-- ❌ OFFSET : la base lit et jette N lignes
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 10000;  -- lit 10 020 lignes

-- ✅ Keyset : se positionne directement
SELECT * FROM orders WHERE id > 10000 ORDER BY id LIMIT 20;  -- lit 20 lignes

Topologies de Réplication

Primaire-Réplica (Read Replicas)

     Écritures         Lectures
       │          ┌────────────┐
       ▼          │            ▼
  ┌─────────┐    │    ┌──────────────┐
  │Primaire │────┘    │  Réplica 1   │
  │         │────┐    └──────────────┘
  └─────────┘    │    ┌──────────────┐
                 └───►│  Réplica 2   │
                      └──────────────┘

Réplication synchrone : Le primaire attend l'ACK de la réplique → zéro perte de données, latence plus élevée. Réplication asynchrone : Le primaire n'attend pas → latence plus faible, perte de données possible en cas de failover.

Multi-Primaire

Les deux nœuds acceptent les écritures. Nécessite une résolution de conflits (last-writer-wins, CRDTs, merge applicatif).

Utilisé par : Galera Cluster (MySQL), BDR (PostgreSQL), CockroachDB, DynamoDB Global Tables.

Vacuum & Maintenance (PostgreSQL)

Le MVCC de PostgreSQL crée des tuples morts lors des UPDATE/DELETE. VACUUM récupère l'espace.

-- Vérifier le bloat
SELECT schemaname, tablename, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Réglage de l'autovacuum

-- Pour une table à forte écriture, rendre l'autovacuum plus agressif
ALTER TABLE events SET (
    autovacuum_vacuum_scale_factor = 0.01,    -- déclencher à 1% de tuples morts (défaut 20%)
    autovacuum_vacuum_cost_delay = 2          -- exécuter plus vite (défaut 20ms)
);

pg_stat_statements — trouver les requêtes lentes

-- Top 10 requêtes par temps total
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Checklist de Performance

Ressources

• Use The Index, Luke — Le guide définitif de l'indexation SQL
• PostgreSQL EXPLAIN Visualizer — Collez votre sortie EXPLAIN pour une analyse visuelle
• pgMustard — Analyse de plans de requêtes assistée par IA
• The Art of PostgreSQL — Techniques PostgreSQL avancées
• [High Performance MySQL](�0� — 4e édition (2022)
• Database Reliability Engineering — Le livré DRE d'O'Reilly

:::