SQL avancé : index, plans et transactions

Comprendre l’optimiseur, lire un plan d’exécution et choisir le bon niveau d’isolation : le référentiel SQL de Codara couvre les décisions qui font passer une base de 100 ms à 1 ms.

Indexation : au-delà de l’index simple

Un index composite est un arbre dont chaque niveau suit un ordre de colonnes. Les colonnes d’égalité doivent précéder les colonnes de plage, sinon l’optimiseur ne peut exploiter que le préfixe. Un index couvrant (toutes les colonnes nécessaires) permet un index-only scan et évite les retours à la table. Attention au coût d’écriture : chaque index supplémentaire ralentit les INSERT et UPDATE.

-- Recherche : equality sur tenant_id, range sur created_at
CREATE INDEX idx_quiz_tenant_created
    ON quiz_session (tenant_id, created_at);

-- L'optimiseur peut couvrir : aucune lecture de la table
SELECT id, score
FROM quiz_session
WHERE tenant_id = 42 AND created_at >= '2026-08-01';

Lire et corriger les plans d’exécution

Le plan d’exécution est la traduction de votre requête en opérations : accès indexé, tri, jointures, tables temporaires. Les indicateurs de coût sont `rows` (estimation), le `type` d’accès et la colonne `Extra`. Un `Using filesort` n’est pas un tri sur disque dans tous les moteurs : c’est un tri qui ne peut pas utiliser l’ordre d’un index. `EXPLAIN ANALYZE` mesure les coûts réels et permet de vérifier une correction.

EXPLAIN ANALYZE
SELECT q.category, COUNT(*) AS attempts, AVG(qs.score) AS avg_score
FROM quiz_session qs
JOIN question q ON q.id = qs.question_id
WHERE qs.tenant_id = 42
GROUP BY q.category
ORDER BY attempts DESC;

Transactions, verrous et niveaux d’isolation

Une transaction garantit atomicité et isolation, mais chaque niveau d’isolation a un prix en verrous. READ COMMITTED verrouille les lignes écrites ; REPEATABLE READ ajoute une cohérence d’instantané ; SERIALIZABLE verrouille les plages et peut bloquer des requêtes concurrentes. Les deadlocks se réduisent en verrouillant les ressources dans un ordre global constant et en gardant les transactions courtes.

START TRANSACTION;

-- Verrouille la ligne avant de décider
SELECT id, remaining_slots
FROM exam_session
WHERE id = 10 FOR UPDATE;

UPDATE exam_session SET remaining_slots = remaining_slots - 1 WHERE id = 10;

COMMIT;

Fonctions de fenêtrage et agrégats avancés

Les fonctions de fenêtrage (ROW_NUMBER, RANK, LAG, SUM OVER) calculent des valeurs relatives à un groupe sans perdre les lignes détail, contrairement à GROUP BY. Elles remplacent élégamment les auto-jointures pour les classements, les différences entre lignes consécutives et les totaux cumulés. La partition et l’ordre du OVER déterminent le cadre ; un mauvais cadrage produit des résultats corrects syntaxiquement mais faux sémantiquement.

SELECT
    user_id,
    score,
    ROW_NUMBER() OVER (PARTITION BY theme ORDER BY score DESC) AS rank_in_theme,
    LAG(score) OVER (PARTITION BY user_id ORDER BY taken_at) AS previous_score
FROM quiz_result;