MySQL 8.2 et l'optimisation des requêtes : comment nous avons divisé par 10 nos temps de réponse

MySQL 8.2 et le paradoxe des développeurs : tout le monde l'utilise, peu l'optimisent

En octobre 2023, Oracle a publié MySQL 8.2 Innovation Release, apportant des améliorations significatives sur l'optimiseur de requêtes, le support JSON avancé et les performances InnoDB. Cette sortie intervient dans un contexte révélateur : selon le Stack Overflow Developer Survey 2023, 39 % des développeurs professionnels utilisent MySQL, ce qui en fait le système de gestion de base de données le plus populaire au monde — devant PostgreSQL (49 % en progression) et SQL Server (26 %).

Pourtant, selon New Relic, les requêtes lentes représentent la cause numéro 1 de mauvaise expérience utilisateur dans les applications web, devant les images non optimisées et le JavaScript bloquant. Le fossé entre popularité et maîtrise est immense.

Chez ADRD Consulting, nous avons rencontré ce problème de front sur deux projets : RecruteX (SaaS RH multi-tenant, 50+ entreprises clientes) et QualiTrack (suivi qualité avec audit trails). Voici ce que nous avons fait pour diviser par 10 les temps de réponse critiques.

Diagnostic : EXPLAIN ANALYZE avant toute optimisation

La première règle : ne jamais optimiser à l'aveugle. Avant chaque intervention, nous utilisons EXPLAIN ANALYZE (disponible depuis MySQL 8.0.18) pour obtenir le plan d'exécution réel avec les temps mesurés :

EXPLAIN ANALYZE
SELECT c.nom, c.prenom, COUNT(o.id) as nb_offres
FROM candidats c
LEFT JOIN offres_candidatures oc ON c.id = oc.candidat_id
LEFT JOIN offres o ON oc.offre_id = o.id
WHERE c.entreprise_id = 42
GROUP BY c.id
ORDER BY nb_offres DESC;

Sur RecruteX, cette requête prenait 2 340 ms pour retourner les 847 candidats d'une entreprise cliente. Le plan d'exécution révélait un full table scan sur la table candidats (230 000 lignes) malgré la clause WHERE sur entreprise_id.

Technique 1 : index composites (le gain le plus rapide)

La table candidats avait un index simple sur id mais aucun sur entreprise_id. Pire : la clause GROUP BY c.id forçait un tri supplémentaire.

Solution : index composite couvrant les colonnes dans l'ordre d'utilisation :

CREATE INDEX idx_candidats_entreprise_id
ON candidats (entreprise_id, id, nom, prenom);

Résultat immédiat : la requête tombe à 187 ms — gain de 12,5×. L'index composite permet à MySQL d'utiliser un index-only scan pour les colonnes incluses, sans accéder aux données réelles de la table.

Technique 2 : élimination des requêtes N+1

Sur QualiTrack, la page de liste des audits chargeait les données avec ce pattern :

// Anti-pattern N+1
$audits = $pdo->query("SELECT * FROM audits WHERE statut='en_cours'")->fetchAll();
foreach ($audits as $audit) {
    // Une requête SQL PAR audit !
    $audit['inspecteur'] = $pdo->prepare("SELECT nom FROM users WHERE id=?")->execute([$audit['user_id']]);
}

Avec 150 audits en cours, cela générait 151 requêtes SQL par chargement de page. La solution : une seule requête avec JOIN :

// Pattern optimisé : 1 seule requête
SELECT a.*, u.nom as inspecteur_nom, u.prenom as inspecteur_prenom
FROM audits a
INNER JOIN users u ON a.user_id = u.id
WHERE a.statut = 'en_cours'
ORDER BY a.date_creation DESC;

Résultat : page de liste passée de 3,2 secondes à 0,28 secondes.

Technique 3 : partitionnement pour les tables d'audit trail

QualiTrack conserve un historique complet de toutes les actions (audit trail réglementaire). En 18 mois, la table action_logs avait atteint 8,4 millions de lignes. Les requêtes de reporting devenaient prohibitives.

Solution : partitionnement par RANGE sur la colonne created_at :

ALTER TABLE action_logs
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Les requêtes filtrées par année n'analysent plus qu'une partition. Gain sur le rapport annuel 2023 : de 18 secondes à 1,4 secondes.

Résultats consolidés : avant/après

Les 3 commandes MySQL à connaître absolument

• SHOW PROCESSLIST : voir les requêtes en cours d'exécution et identifier les blocages.
• SHOW STATUS LIKE 'Slow_queries' : nombre de requêtes ayant dépassé le seuil long_query_time.
• SELECT * FROM information_schema.INNODB_METRICS WHERE STATUS='enabled' : métriques InnoDB en temps réel.

Vos applications web souffrent de lenteurs inexpliquées ? Contactez-nous — un audit de performance MySQL peut révéler des gains de ×10 sur vos requêtes critiques en moins d'une journée.