Optimisation SQL avancée : de la requête lente à la requête instantanée

Index composites, plans d’exécution, anti-patterns de jointures et transactions : la méthode pour diagnostiquer et corriger les requêtes qui ralentissent une application.

Mesurer avant d’optimiser : le plan d’exécution d’abord

Une requête lente sans plan d’exécution est une devinette. `EXPLAIN` donne la stratégie estimée, `EXPLAIN ANALYZE` les coûts réels. Relevez le `type` d’accès (ALL vs ref vs range), le nombre de lignes estimé, les fichiers temporaires et les tris. Un écart entre estimation et réalité indique souvent des statistiques obsolètes : `ANALYZE TABLE` les rafraîchit.

EXPLAIN ANALYZE
SELECT qs.id, qs.score
FROM quiz_session qs
WHERE qs.tenant_id = 42
  AND qs.taken_at >= '2026-01-01'
ORDER BY qs.score DESC
LIMIT 20;

Concevoir des index composites efficaces

L’ordre des colonnes d’un index composite suit les prédicats : égalité d’abord, plage ensuite, tri en dernier. Un index qui couvre toutes les colonnes de la requête évite les retours à la table. Chaque index coûte en écriture : supprimez ceux qui ne servent qu’à une seule requête rare, et vérifiez l’usage réel avec les statistiques du moteur.

-- Bon ordre : égalité (tenant_id), plage (taken_at), tri (score)
CREATE INDEX idx_qs_tenant_taken_score
    ON quiz_session (tenant_id, taken_at, score);

-- Requête couverte : aucun retour à la table
SELECT score FROM quiz_session
WHERE tenant_id = 42 AND taken_at >= '2026-01-01'
ORDER BY score DESC;

Anti-patterns de jointures et de sous-requêtes

Une jointure qui multiplie les lignes (fan-out) fausse les agrégats : `COUNT(*)` compte les lignes jointes, pas les entités. Les sous-requêtes corrélées exécutées par ligne sont souvent remplaçables par des fenêtres ou des jointures dérivées. Vérifiez toujours que les colonnes de jointure sont indexées des deux côtés et que le type de jointure correspond à l’intention métier.

-- Fan-out : une session a plusieurs réponses
SELECT s.id, COUNT(*) AS answer_count
FROM quiz_session s
JOIN quiz_answer a ON a.session_id = s.id
GROUP BY s.id;

-- Alternative sans doublon : sous-requête dérivée
SELECT s.id,
       (SELECT COUNT(*) FROM quiz_answer a WHERE a.session_id = s.id) AS answer_count
FROM quiz_session s;

Fonctions sur colonnes : l’assassin silencieux des index

Appliquer une fonction à une colonne dans le WHERE (`LOWER(email) = ?`, `DATE(created_at) = ?`) empêche l’utilisation de l’index : le moteur doit évaluer la fonction sur chaque ligne. Contournez par une colonne générée et indexée, un index fonctionnel (MySQL 8.0.13+), ou en réécrivant le prédicat sur une plage. La règle : garder la colonne nue dans les prédicats.

-- À éviter : fonction sur la colonne
SELECT * FROM users WHERE LOWER(email) = 'x@example.com';

-- MySQL 8 : index fonctionnel
CREATE INDEX idx_users_lower_email ON users ((LOWER(email)));

-- ou plage explicite sur un timestamp
SELECT * FROM quiz_session
WHERE taken_at >= '2026-08-01' AND taken_at < '2026-08-02';

Prédicats SARGeables et cardinalité

Un prédicat est SARGeable (Search ARGument Able) quand le moteur peut l’exploiter via l’index : comparaisons directes, `IN`, `BETWEEN`, préfixe `LIKE 'abc%'`. Les `LIKE '%abc'`, `!=` et `OR` non indexé cassent souvent cette propriété. La cardinalité guide aussi le choix : un index sur une colonne quasi constante (statut à 99 % « actif ») ne sera pas utilisé, et c’est normal.

-- SARGeable : préfixe utilisable
WHERE slug LIKE 'symfony-%';

-- Non SARGeable : suffixe → scan complet
WHERE slug LIKE '%-optimization';

Transactions, verrous et deadlocks

Les deadlocks ne sont pas des bugs aléatoires : ce sont des interblocages de verrous. On les réduit en verrouillant les ressources dans un ordre global constant, en gardant les transactions courtes et en évitant de lire puis écrire la même ligne sans besoin. Les niveaux d’isolation ont un coût : SERIALIZABLE verrouille les plages, READ COMMITTED suffit souvent. Un deadlock doit être retenté, pas ignoré.

// Ordre constant : toujours verrouiller les sessions AVANT les résultats
$conn->executeQuery('SELECT id FROM quiz_session WHERE id = ? FOR UPDATE', [$sessionId]);
$conn->executeQuery('INSERT INTO quiz_result (session_id, score) VALUES (?, ?)', [$sessionId, $score]);

// En cas de deadlock (SQLSTATE 40001) : retry avec backoff

Fenêtres et agrégats : remplacer les auto-jointures

Les fonctions de fenêtrage calculent des valeurs par groupe sans perdre les lignes de détail : classements, deltas entre lignes, totaux cumulés. Elles remplacent les auto-jointures coûteuses et les sous-requêtes corrélées. Le `OVER (PARTITION BY ... ORDER BY ...)` définit le cadre ; vérifiez que la partition correspond à l’unité métier réelle (utilisateur, session, tenant).

SELECT
    user_id,
    taken_at,
    score,
    AVG(score) OVER (PARTITION BY user_id ORDER BY taken_at
                     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_avg
FROM quiz_result
ORDER BY user_id, taken_at;

Modélisation : normalisation, types et contraintes

Un schéma propre évite des optimisations douloureuses : types numériques pour les nombres (jamais de VARCHAR pour les scores), contraintes d’intégrité (FK, CHECK, NOT NULL) qui garantissent des données exploitables, et un partitionnement réservé aux très grandes tables. Les colonnes JSON sont pratiques mais s’interrogent mal : structurez ce qui est réellement recherché, gardez JSON pour ce qui est opaque.

CREATE TABLE quiz_session (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    score DECIMAL(5,2) NOT NULL,
    taken_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (id),
    CONSTRAINT fk_session_tenant FOREIGN KEY (tenant_id) REFERENCES tenant (id),
    CONSTRAINT fk_session_user FOREIGN KEY (user_id) REFERENCES user (id),
    INDEX idx_session_tenant_taken (tenant_id, taken_at)
) ENGINE=InnoDB;