Optimisation des index MySQL : requêtes lentes

Bien utilisé, un index MySQL transforme une requête qui prend plusieurs secondes en une requête de quelques millisecondes ; mal utilisé, il ne sert à rien et peut même ralentir vos écritures. Sur les serveurs de jeu en particulier, les tables de sessions, de logs, d'inventaire et de classements atteignent vite des millions de lignes. À ce stade, il faut mesurer le goulot d'étranglement plutôt que de le deviner. Dans ce guide, nous verrons comment diagnostiquer les requêtes lentes avec EXPLAIN, décider quelles colonnes indexer et éviter les erreurs les plus fréquentes.

Pourquoi les index fonctionnent

Dans une table sans index, MySQL parcourt toute la table du début à la fin pour trouver les lignes recherchées (un full table scan). Plus la table grandit, plus cela ralentit de façon linéaire. Un index est, dans la plupart des cas, une structure en B-tree : il garde triées les valeurs de la colonne indexée et permet à MySQL de sauter directement au bloc concerné grâce à une logique proche de la recherche binaire, même parmi des millions de lignes.

Cela a un coût : chaque INSERT, UPDATE et DELETE doit aussi mettre à jour l'index. Un index accélère donc les lectures tout en ralentissant un peu les écritures et en consommant de l'espace disque. C'est pourquoi l'objectif n'est pas « tout indexer » mais d'indexer les bonnes colonnes.

Trouver la requête lente : le slow query log

Il faut d'abord savoir quelle requête est réellement lente. Le slow query log de MySQL enregistre chaque requête dépassant un seuil que vous définissez. Vous pouvez l'activer rapidement par session :

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;        -- requêtes de plus de 1 seconde
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Après un certain temps de fonctionnement, résumez les requêtes les plus fréquentes et les plus lentes avec mysqldumpslow :

mysqldumpslow -s t /var/log/mysql/slow.log | head -20

L'objectif est de trouver une requête concrète à optimiser. Travaillez avec du trafic réel, pas avec des suppositions.

Diagnostic avec EXPLAIN

Une fois que vous avez une requête lente, utilisez EXPLAIN pour voir comment MySQL l'exécute. Exemple de requête de serveur de jeu :

EXPLAIN SELECT * FROM player_logs
WHERE account_id = 4815 AND action = 'login'
ORDER BY created_at DESC
LIMIT 20;

Dans la sortie, concentrez-vous sur ces colonnes :

• type : la méthode d'accès. ALL est mauvais (parcours complet), ref et range sont bons, const/eq_ref sont les meilleurs.
• key : l'index réellement utilisé par MySQL. NULL signifie qu'aucun index n'a été utilisé.
• rows : le nombre estimé de lignes que MySQL prévoit de parcourir. Plus c'est bas, mieux c'est.
• Extra : si vous voyez Using filesort ou Using temporary, un travail supplémentaire est fait pour le tri ou le regroupement.

Pour un plan plus détaillé, EXPLAIN ANALYZE (MySQL 8.0.18+) exécute réellement la requête et affiche les durées mesurées — vous voyez le coût réel plutôt qu'une estimation.

Concevoir le bon index

La requête ci-dessus filtre par account_id et action et trie par created_at. L'index idéal est un index composite qui reflète exactement cet ordre :

CREATE INDEX idx_logs_acc_action_time
  ON player_logs (account_id, action, created_at);

Le concept clé ici est la règle du préfixe le plus à gauche (leftmost prefix). Si un index composite est (a, b, c), MySQL peut l'utiliser pour des recherches sur a, a+b ou a+b+c, mais pas pour b ou c seuls. C'est pourquoi l'ordre des colonnes compte :

• Les filtres d'égalité (=) viennent en premier (ici account_id, action).
• Puis viennent la colonne de plage (range) et de ORDER BY (created_at).

Grâce à cet ordre, MySQL gère à la fois le filtrage et le tri via l'index, et Using filesort disparaît.

Erreurs fréquentes

• Envelopper une colonne dans une fonction : écrire WHERE DATE(created_at) = '2026-06-27' empêche l'utilisation de l'index. Utilisez plutôt une plage : WHERE created_at >= '2026-06-27' AND created_at < '2026-06-28'.
• Joker en tête : LIKE '%abc' désactive l'index, alors que LIKE 'abc%' peut l'utiliser.
• Incompatibilité de type : comparer une colonne numérique à une chaîne comme WHERE id = '123' peut déclencher une conversion implicite et un parcours complet.
• Sur-indexation : indexer seules des colonnes rarement interrogées ou peu sélectives (par exemple un drapeau à valeurs 0/1) n'apporte généralement rien.
• SELECT * inutile : si vous ne sélectionnez que les colonnes nécessaires, parfois l'index lui-même satisfait toute la requête (un covering index) et la table n'est jamais touchée.

Vérifier et maintenir

Après avoir ajouté un index, relancez le même EXPLAIN : type doit passer de ALL à ref/range, rows doit chuter fortement et filesort doit disparaître. Pour garder les statistiques à jour, exécutez de temps en temps ANALYZE TABLE player_logs;. Vous pouvez repérer et supprimer les index inutilisés via la vue sys.schema_unused_indexes afin de réduire la charge d'écriture.

Rappelez-vous : l'optimisation des index n'est pas une tâche ponctuelle mais une boucle continue — mesurer, indexer, vérifier, recommencer.

Questions fréquentes

Combien d'index puis-je ajouter à une table ?

Bien que la limite technique soit élevée, la limite pratique dépend de votre charge de travail. Sur les tables à écritures intensives, gardez peu d'index et bien ciblés ; sur les tables de reporting à lectures intensives, davantage d'index peut être raisonnable. N'oubliez pas que chaque index a un coût d'écriture et de stockage.

Des index séparés ou un index composite, lequel est meilleur ?

Si une requête filtre plusieurs colonnes ensemble, un seul index composite correctement ordonné est presque toujours meilleur que des index mono-colonne séparés. Dans la plupart des cas, MySQL utilise un seul index par requête de la façon la plus efficace.

La clé primaire est-elle déjà un index ?

Oui. Dans InnoDB, la clé primaire est aussi l'index clusterisé (clustered index), et les données de ligne sont physiquement stockées dans son ordre. C'est pourquoi choisir une clé primaire courte, séquentielle et immuable (comme un auto-increment) est important pour les performances.

Arrêtez de deviner quand votre base ralentit. Serveur de jeu ou application web, peu importe — mesurons ensemble vos requêtes lentes et construisons la bonne stratégie d'index. Contactez-moi et accélérons votre base de données.