Optimisation des requêtes MySQL : guide pratique

1. Commencer par identifier les requêtes lentes

N'optimisez pas au hasard. Activez le slow query log et laissez-le vous montrer ce qui coûte réellement cher en production, plutôt que ce qui paraît suspect dans le code.

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5; -- seconds
SET GLOBAL log_output = 'TABLE';  -- or FILE

Une fois les entrées collectées, agrégez-les au lieu de les lire une par une — une poignée de formes de requêtes concentre généralement l'essentiel de la charge. mysqldumpslow est fourni avec MySQL ; pt-query-digest de Percona offre une ventilation plus détaillée par modèle de requête normalisé. Si le Performance Schema est activé, sys.statement_analysis donne la même vue sans fichier de log :

SELECT * FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 20;

Classez par temps total (fréquence × latence), et pas seulement par la requête la plus lente — une requête de 500 ms exécutée une fois par jour pèse moins qu'une requête de 20 ms exécutée dix mille fois par minute.

2. Lire le plan d'exécution

Une fois la requête à corriger identifiée, lancez EXPLAIN dessus avant de modifier quoi que ce soit :

EXPLAIN SELECT * FROM orders
WHERE customer_id = 42 AND status = 'pending';

Quatre colonnes comptent plus que les autres :

• type — la méthode d'accès. ALL signifie un parcours complet de la table (full table scan) ; range, ref et eq_ref indiquent qu'un index est utilisé, du moins sélectif au plus sélectif, en gros.
• key — l'index réellement retenu. S'il vaut NULL alors que vous attendiez un index, l'optimiseur a jugé un parcours complet moins coûteux, souvent à cause de statistiques obsolètes ou de l'un des cas décrits en section 4.
• rows — le nombre estimé de lignes que MySQL s'attend à examiner. Comparez-le au nombre de lignes réellement renvoyées : un écart important signale un index manquant ou inefficace.
• Extra — surveillez Using filesort (une passe de tri supplémentaire) et Using temporary (une table temporaire pour le regroupement ou le tri), deux opérations qui deviennent coûteuses sur de gros volumes de résultats.

À partir de MySQL 8.0.18, EXPLAIN ANALYZE exécute réellement la requête et indique les temps et nombres de lignes réels à chaque étape, ce qui est plus fiable que les estimations de l'optimiseur :

EXPLAIN ANALYZE SELECT * FROM orders
WHERE customer_id = 42 AND status = 'pending';

3. Bien indexer les tables InnoDB

Les tables InnoDB sont stockées sous forme d'index cluster sur la clé primaire — les lignes sont physiquement ordonnées selon celle-ci. Chaque index secondaire stocke la valeur de la clé primaire à côté des colonnes indexées : une recherche via un index secondaire qui n'est pas couvrant implique donc une seconde lecture dans l'index cluster pour récupérer le reste de la ligne.

Index composites et ordre des colonnes

Pour un index multicolonne, MySQL ne peut utiliser qu'un préfixe gauche des colonnes. Un index sur (customer_id, status, created_at) sert les requêtes qui filtrent sur customer_id seul, ou sur customer_id et status ensemble, mais pas une requête qui filtre uniquement sur status. Placez d'abord les colonnes filtrées par égalité, puis en dernier la colonne utilisée pour le tri ou le filtre par plage.

Index couvrants

Si un index contient toutes les colonnes dont la requête a besoin, MySQL peut y répondre à partir de l'index seul — vous verrez Using index dans la colonne Extra d'EXPLAIN, et la lecture de la ligne est entièrement évitée :

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

Attention toutefois à ne pas sur-indexer : chaque index alourdit les écritures et le stockage, et l'optimiseur doit évaluer (et parfois écarter à tort) davantage d'options à l'exécution.

4. Les requêtes qui neutralisent les index

Voici les causes récurrentes du fameux « pourquoi mon index n'est-il pas utilisé ? » :

• Fonctions appliquées aux colonnes indexées. WHERE YEAR(created_at) = 2024 ne peut pas utiliser un index sur created_at, car MySQL doit évaluer la fonction pour chaque ligne. Réécrivez la condition sous forme de plage : WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'.
• Conversion de type implicite. Comparer une colonne de type chaîne à un nombre sans guillemets (ou l'inverse) oblige MySQL à convertir chaque valeur avant la comparaison, ce qui désactive silencieusement l'index.
• Jokers en début de motif. LIKE '%foo' ne peut pas utiliser un index B-tree classique, car l'index est trié de gauche à droite. LIKE 'foo%', si. Pour une véritable recherche textuelle, utilisez plutôt un index FULLTEXT.
• Pagination avec un OFFSET élevé. LIMIT 20 OFFSET 100000 oblige MySQL à parcourir puis écarter 100 000 lignes avant d'en renvoyer une seule. Passez à la pagination par clé (keyset pagination) : WHERE id > :last_id ORDER BY id LIMIT 20.
• OR sur des colonnes différentes. WHERE customer_id = 42 OR status = 'pending' ne peut souvent pas exploiter efficacement un seul index. Découpez la requête en UNION de deux requêtes indexées, ou restructurez la condition.

5. Entretenir les statistiques et le schéma

L'optimiseur choisit un plan à partir d'estimations de cardinalité. Après des modifications de données importantes, rafraîchissez-les :

ANALYZE TABLE orders;

À vérifier aussi lors d'une revue de la modélisation des données : des types de données surdimensionnés gaspillent de l'espace d'index et ralentissent chaque parcours qui les touche. Une clé primaire BIGINT dont vous n'aurez jamais besoin, ou une colonne TEXT qui pourrait être un VARCHAR borné, alourdit chaque index de la table et le rend plus lent à lire depuis le disque et à garder en cache dans le buffer pool.

6. Surveiller et itérer

L'optimisation des requêtes n'est pas une opération ponctuelle. Les données grossissent, les modes d'accès évoluent, et une requête qui tenait à 10 000 lignes peut s'effondrer à 10 millions. Intégrez le slow query log ou sys.statement_analysis à vos revues régulières, et relancez EXPLAIN après chaque migration de schéma — une nouvelle colonne ou un index modifié peut changer silencieusement un plan qui était rapide.