MySQL 8 : CTE, fonctions fenêtre et EXPLAIN ANALYZE

Les CTE récursives remplacent les hacks à base de variables

Avant MySQL 8.0, parcourir une hiérarchie (arbre de catégories, organigramme) obligeait à bricoler avec des variables de session ou des procédures stockées. Les Common Table Expressions récursives simplifient ça nativement :

WITH RECURSIVE hierarchie AS (
  SELECT id, nom, parent_id, 1 AS niveau
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.nom, c.parent_id, h.niveau + 1
  FROM categories c
  JOIN hierarchie h ON c.parent_id = h.id
)
SELECT * FROM hierarchie ORDER BY niveau;

Attention à la variable cte_max_recursion_depth (1000 par défaut) qui protège contre les boucles infinies en cas de données corrompues.

Fonctions fenêtre : arrêter les sous-requêtes corrélées

Calculer un classement, une moyenne mobile ou un cumul par groupe ne nécessite plus de sous-requête par ligne :

SELECT client_id, montant,
       SUM(montant) OVER (PARTITION BY client_id ORDER BY date_commande) AS total_cumule,
       RANK() OVER (PARTITION BY client_id ORDER BY montant DESC) AS rang
FROM commandes;

C'est souvent plus rapide qu'une sous-requête corrélée équivalente, car MySQL n'exécute qu'un seul balayage des données par fenêtre.

JSON : indexer ce qui compte

Stocker du JSON dans une colonne JSON est pratique, mais interroger un champ imbriqué sans index scanne toute la table. La solution : une colonne générée virtuelle indexée.

ALTER TABLE evenements
  ADD COLUMN type_evt VARCHAR(50)
  GENERATED ALWAYS AS (payload->>'$.type') STORED,
  ADD INDEX idx_type_evt (type_evt);

EXPLAIN ANALYZE, pas juste EXPLAIN

EXPLAIN seul donne un plan théorique basé sur des statistiques parfois obsolètes. EXPLAIN ANALYZE exécute réellement la requête et affiche le temps passé à chaque étape :

EXPLAIN ANALYZE
SELECT * FROM commandes WHERE client_id = 42 ORDER BY date_commande DESC;

Si le plan indique un Filesort coûteux alors qu'un index existe sur client_id, pensez à un index composite (client_id, date_commande) pour éviter le tri en mémoire.

Index invisibles pour tester sans risque

Avant de supprimer un index suspecté inutile en production, rendez-le invisible plutôt que de le droper :

ALTER TABLE commandes ALTER INDEX idx_ancien INVISIBLE;

L'optimiseur l'ignore, mais l'index reste maintenu et réversible en une commande si un problème de performance apparaît.

À retenir

MySQL 8.4 est la version LTS actuelle ; privilégiez-la pour les nouveaux projets.
Les CTE et fonctions fenêtre remplacent la majorité des anciens contournements SQL.
Toujours valider un plan avec EXPLAIN ANALYZE avant d'ajouter un index à l'aveugle.