Dépannage des requêtes lentes MySQL : un guide étape par étape

Le dépannage des requêtes lentes MySQL commence par une règle inconfortable : ne pas deviner à partir du texte de la requête seul. Une requête qui semble moche peut être inoffensive car elle s'exécute une fois par jour. Une requête qui semble simple peut ruiner la base de données car elle s'exécute des milliers de fois par minute, analyse trop de lignes ou attend derrière des verrous.

Le workflow utile est ennuyeux de la meilleure façon. Capturez les vraies requêtes lentes, regroupez-les par coût, inspectez le plan d'exécution, modifiez une chose et mesurez à nouveau. Cela vous évite d'ajouter des index aléatoires, de modifier aveuglément les paramètres globaux ou de blâmer MySQL alors que l'application envoie un modèle de requête évitable.

Je commence généralement par trois questions :

Quelle requête nuit aux utilisateurs, et pas seulement qui semble suspecte ?
Le temps est-il passé à lire des lignes, à trier, à attendre des verrous ou à attendre l'application ?
Puis-je prouver la correction avec EXPLAIN, le timing et de nouvelles données du journal des requêtes lentes ?

Commencez par le journal des requêtes lentes

Le journal des requêtes lentes MySQL enregistre les instructions qui dépassent le seuil configuré. Selon le manuel MySQL, le journal est désactivé par défaut, long_query_time est par défaut à 10 secondes, et une instruction doit normalement s'exécuter au moins aussi longtemps et examiner au moins min_examined_row_limit lignes avant d'être enregistrée. Si log_queries_not_using_indexes est activé, MySQL peut également enregistrer les instructions qui n'utilisent pas d'index pour les recherches de lignes. Cette option est utile pendant le diagnostic, mais elle peut produire beaucoup de bruit sur les systèmes très sollicités.

Une configuration de départ pratique ressemble à ceci :

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_output = FILE

Sur de nombreux systèmes de production, 1 seconde est un premier passage raisonnable. Pour une API sensible à la latence, vous pouvez temporairement l'abaisser à 0,5 ou 0,2. Faites-le avec un plan et une surveillance de l'espace disque. Une base de données à fort trafic peut écrire une quantité surprenante de données de journal lent une fois le seuil abaissé.

Vous pouvez vérifier les paramètres actifs à partir d'une session MySQL :

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_output';
SHOW VARIABLES LIKE 'min_examined_row_limit';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

Pour une enquête temporaire, vous pouvez activer le journal sans modifier le fichier de configuration :

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

N'oubliez pas que les modifications SET GLOBAL peuvent ne pas survivre à un redémarrage à moins que vous ne les persistiez également via votre processus de configuration normal. Sur MySQL 8, certaines équipes utilisent SET PERSIST, mais je préfère toujours valider le paramètre souhaité dans la gestion de configuration afin que le prochain opérateur puisse le voir.

Si vous activez log_queries_not_using_indexes, envisagez également de définir log_throttle_queries_not_using_indexes afin qu'un point de terminaison bruyant n'inonde pas le journal. MySQL prend en charge cette limitation précisément parce que la journalisation sans index peut croître rapidement.

Regroupez le journal avant de lire les requêtes individuelles

Les journaux de requêtes lentes bruts sont répétitifs. Vous pouvez voir la même requête des centaines de fois avec des identifiants différents. Lire le fichier du début à la fin fait perdre du temps et donne aux requêtes rares et effrayantes plus d'importance qu'aux requêtes courantes et coûteuses.

Commencez par mysqldumpslow, qui est fourni avec les installations MySQL dans de nombreux environnements :

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

Cela demande les dix premiers modèles triés par temps de requête. Les indicateurs exacts varient selon la version et la plateforme, alors vérifiez mysqldumpslow --help si votre commande se comporte différemment. Les tris utiles incluent le temps total, le temps moyen, le temps de verrouillage et le nombre de lignes examinées.

Pour les enquêtes en production, pt-query-digest de Percona Toolkit est souvent meilleur car il fournit un regroupement plus riche et des détails de type centile. L'outil n'est pas magique ; il vous évite simplement de faire des calculs à la main. L'important est de classer par impact. Une requête qui prend huit secondes une fois par nuit peut être moins urgente qu'une requête qui prend 120 millisecondes mais s'exécute 600 fois par seconde.

Lors de la lecture de la sortie groupée, recherchez des modèles :

Temps total élevé : probablement visible par l'utilisateur ou gourmand en ressources.
Nombre élevé : souvent une boucle d'application ou un cache manquant.
Lignes examinées élevées avec peu de lignes envoyées : généralement un problème d'indexation ou de filtrage.
Temps de verrouillage élevé : éventuellement une transaction, un conflit d'écriture, un verrou de métadonnées ou un problème de DDL.

Ne supposez pas qu'un Rows_examined élevé est toujours mauvais. Les requêtes de rapport et les tâches par lots analysent parfois intentionnellement. La question est de savoir si l'analyse correspond à la tâche et si elle se produit au bon moment.

Reproduisez une requête en toute sécurité

Choisissez un modèle de requête et obtenez un échantillon réel avec des paramètres. Si le journal lent a normalisé les littéraux, trouvez la requête d'origine dans les journaux d'application, les traces APM ou l'entrée brute du journal lent.

Avant de l'exécuter manuellement, vérifiez le rayon d'impact. Un SELECT lent sur un réplica est généralement sûr. Un UPDATE lent en production n'est pas quelque chose à exécuter à la légère. Pour les requêtes d'écriture, inspectez d'abord le plan et le modèle de transaction, ou testez sur une copie de staging avec des données réalistes.

Une note de travail utile pour chaque requête ressemble à ceci :

Point de terminaison : GET /customers/123/orders
Modèle de requête : commandes par client et statut, les plus récentes en premier
Observé : 1,8 s en moyenne, 420 000 lignes examinées, 20 lignes envoyées
Taille de la table : 12 millions de lignes
Taille de résultat attendue : une page de commandes
Suspicion : index composite manquant pour customer_id, status, created_at

Cette note maintient le travail lié à un chemin utilisateur réel au lieu d'un extrait SQL aléatoire.

Utilisez `EXPLAIN`, puis lisez-le comme un opérateur

Exécutez EXPLAIN sur la requête lente :

EXPLAIN
SELECT id, customer_id, status, created_at, total
FROM orders
WHERE customer_id = 123
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Pour MySQL 8, EXPLAIN ANALYZE peut exécuter la requête et afficher les informations de synchronisation réelles. Utilisez-le avec précaution sur les requêtes coûteuses car il exécute réellement l'instruction. Pour les requêtes SELECT simples dans un environnement contrôlé, cela peut être très utile.

Les colonnes que je vérifie en premier sont type, possible_keys, key, rows, filtered et Extra.

type vous indique le modèle d'accès. const, eq_ref, ref et range sont généralement de bons signes. index signifie que MySQL analyse un index, ce qui peut encore être trop de travail. ALL signifie une analyse complète de table. Une analyse complète n'est pas automatiquement erronée sur une petite table, mais elle est suspecte sur une table chaude avec des millions de lignes.

key montre l'index choisi par MySQL. Si possible_keys répertorie un index prometteur mais que key est différent, l'optimiseur peut penser que l'autre index est moins cher. Cela peut arriver en raison d'une faible sélectivité, de statistiques obsolètes ou d'un index qui ne correspond pas au filtre et au tri ensemble.

rows est une estimation, pas une promesse. Si l'estimation est complètement erronée, exécutez ANALYZE TABLE pendant une fenêtre de maintenance appropriée ou examinez si la distribution des données est asymétrique.

Extra raconte souvent l'histoire. Using filesort signifie que MySQL a besoin d'une étape de tri séparée ; cela ne signifie pas nécessairement un tri sur disque, mais cela vaut la peine d'être vérifié lorsque l'ensemble de résultats est volumineux. Using temporary apparaît souvent avec des regroupements, des requêtes distinctes ou des tris complexes. Using index peut être bon car la requête est satisfaite à partir de l'index sans lire les lignes de la table.

Corrigez les index en gardant à l'esprit la forme complète de la requête

La correction la plus courante pour une requête lente n'est pas "ajouter un index à la colonne dans la clause WHERE". La meilleure règle est : construisez un index qui correspond à la façon dont la requête filtre, joint, trie et limite les lignes.

Pour la requête de commandes ci-dessus, un index à colonne unique sur customer_id pourrait aider, mais il peut encore laisser MySQL trier de nombreuses lignes pour ce client. Un index composite est souvent plus utile :

CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at);

Si la requête trie par les plus récentes en premier, MySQL peut souvent analyser l'index dans l'ordre inverse. Dans MySQL 8, vous pouvez également définir des index descendants lorsque cela correspond à un modèle plus large :

CREATE INDEX idx_orders_customer_status_created_desc
ON orders (customer_id, status, created_at DESC);

L'ordre des colonnes est important. Placez les filtres d'égalité en premier, puis les colonnes de plage ou de tri lorsque cela correspond à la requête. Par exemple, avec WHERE customer_id = ? AND status = ? ORDER BY created_at DESC LIMIT 20, customer_id, status, created_at est généralement plus utile que created_at, customer_id, status.

N'ajoutez pas tous les index qui semblent utiles. Les index accélèrent les lectures mais ralentissent les écritures et consomment du stockage. Si la table reçoit des insertions ou des mises à jour lourdes, un nouvel index composite a un coût réel. Vérifiez d'abord les index existants :

SHOW INDEX FROM orders;

Parfois, la bonne réponse est de remplacer deux index faibles par un meilleur index composite, pas de garder les trois.

Réécrivez les requêtes qui bloquent l'utilisation de l'index

Certaines requêtes lentes sont lentes car elles cachent des valeurs indexées derrière des fonctions ou des modèles que MySQL ne peut pas utiliser efficacement.

Cette version est courante et pénible :

SELECT *
FROM orders
WHERE YEAR(created_at) = 2026;

Si created_at est indexé, l'envelopper dans YEAR() peut empêcher une recherche de plage normale. Écrivez le prédicat comme une plage à la place :

SELECT id, customer_id, status, created_at, total
FROM orders
WHERE created_at >= '2026-01-01'
  AND created_at <  '2027-01-01';

La même idée s'applique aux recherches avec caractère générique en tête :

WHERE email LIKE '%@example.com'

Un index B-tree normal ne peut pas sauter au milieu d'une chaîne. Si les recherches de suffixe sont importantes, vous pouvez avoir besoin d'une colonne générée, d'un champ normalisé séparé ou d'un système de recherche conçu pour ce cas d'utilisation.

Surveillez également SELECT *. Cela semble inoffensif pendant le développement, mais cela peut forcer MySQL à lire des lignes de table alors qu'une projection plus petite pourrait utiliser un index couvrant. Cela envoie également des données inutiles sur le réseau.

Vérifiez les verrous lorsque le plan de requête semble correct

Une requête peut avoir un plan décent et être toujours lente car elle attend. Le Lock_time du journal lent peut vous orienter dans cette direction, mais il n'explique pas tous les types d'attente. Si les utilisateurs signalent des pauses aléatoires, vérifiez les sessions actives :

SHOW PROCESSLIST;

Sur MySQL 8, les vues du schéma Performance et sys peuvent donner de meilleurs détails, selon la configuration du serveur. Pour un aperçu rapide, je vérifie souvent les transactions de longue durée et les instructions bloquées avant de modifier les index.

Un exemple réel : une requête UPDATE orders SET status = ? WHERE id = ? devrait être rapide. Si elle apparaît dans le journal lent avec une recherche par clé primaire, le problème peut être une transaction qui a laissé la ligne verrouillée tout en effectuant un travail non lié. La correction n'est pas un autre index. La correction consiste à raccourcir la transaction et à déplacer les appels externes lents en dehors de celle-ci.

Les verrous de métadonnées peuvent créer un piège similaire. Une migration exécutant ALTER TABLE peut attendre une ancienne transaction, tandis que de nouvelles requêtes s'accumulent derrière le DDL en attente. Le journal des requêtes lentes montrera des symptômes, mais la cause profonde est le comportement de déploiement.

Ajustez les paramètres du serveur uniquement après le travail sur les requêtes

La configuration compte, mais il est facile d'en abuser comme première réponse. Si une requête analyse cinq millions de lignes pour en renvoyer dix, l'augmentation de la mémoire peut seulement rendre le mauvais plan moins douloureux.

Pour les systèmes lourds InnoDB, innodb_buffer_pool_size est le premier paramètre à examiner. Sur un serveur MySQL dédié, il est souvent défini sur une grande partie de la mémoire, mais la bonne valeur dépend de ce qui s'exécute d'autre sur l'hôte, de la taille de l'ensemble de données et de la charge de travail. Ne copiez pas aveuglément un pourcentage à partir d'un article de blog.

Vérifiez également si la base de données attend sur le disque. Si l'ensemble de travail ne tient pas en mémoire, ou si le stockage est saturé, même les requêtes bien indexées peuvent caler. Associez l'examen des requêtes aux métriques de l'hôte : CPU, latence du disque, IOPS, pression mémoire et nombre de connexions.

Les pools de connexions peuvent aggraver l'apparence des requêtes lentes. Si un point de terminaison déclenche trop d'instructions lentes, le pool se remplit, les requêtes non liées attendent des connexions et toute l'application semble cassée. Dans ce cas, corriger la requête reste la tâche principale, mais les limites du pool et les délais d'attente déterminent la grâce avec laquelle le système échoue.

Prouvez la correction

Après avoir ajouté un index ou réécrit une requête, exécutez à nouveau EXPLAIN. Vous voulez voir moins de lignes estimées, une meilleure clé choisie et moins d'étapes supplémentaires coûteuses. Testez ensuite la requête réelle avec des paramètres réalistes.

Ne vous arrêtez pas à une exécution rapide. Un cache chaud peut cacher des problèmes. Essayez des cas courants, volumineux et délicats :

Un client avec de nombreuses commandes.
Un client sans commandes correspondantes.
Une plage de dates qui couvre une période chargée.
Une valeur de statut qui correspond à la plupart des lignes.

Surveillez ensuite le journal lent après le déploiement. Le meilleur résultat n'est pas "la requête semblait meilleure en staging". Le meilleur résultat est que le modèle de requête disparaît des principaux contrevenants, la pression CPU ou E/S diminue et le chemin utilisateur est plus rapide.

Le dépannage des requêtes lentes MySQL est principalement une collecte de preuves disciplinée. Activez le journal avec des seuils raisonnables, regroupez les modèles coûteux, inspectez le plan, corrigez la forme de la requête et validez avec des données fraîches. Cette habitude empêche à la fois la sous-correction et la sur-correction, ce qui est exactement ce que vous voulez lorsque la base de données est déjà sous pression.