Optimisation des requêtes MySQL : un guide pratique

Les requêtes MySQL lentes sont rarement mystérieuses une fois que vous regardez le plan d'exécution. La partie difficile n'est pas de savoir que les index sont importants. La partie difficile est de prouver quelle requête est lente, de comprendre pourquoi MySQL a choisi un plan, et de modifier la requête ou l'index sans aggraver les écritures, le stockage ou d'autres requêtes.

Commencez par des preuves. Utilisez le journal des requêtes lentes, le Performance Schema, les traces d'application ou un outil de surveillance comme PMM pour trouver les requêtes qui nuisent réellement aux utilisateurs. Utilisez ensuite EXPLAIN et, lorsque c'est sûr, EXPLAIN ANALYZE pour voir ce que MySQL fait.

Comprendre les performances des requêtes

Les causes courantes incluent :

• Index manquants ou inefficaces : Sans index appropriés, MySQL doit effectuer des analyses de table complètes, ce qui est très inefficace pour les grandes tables.
• SQL mal écrit : Filtres non sargables, SELECT * inutile, jointures croisées accidentelles et conditions de jointure inefficaces peuvent tous dégrader les performances.
• Grands ensembles de données : Plus de données signifie plus de pages à lire, trier, grouper et mettre en cache.
• Matériel et configuration : Une configuration de serveur sous-optimale ou des ressources matérielles insuffisantes peuvent également jouer un rôle, bien que ce guide se concentre sur l'optimisation au niveau des requêtes.

La puissance de EXPLAIN

EXPLAIN est le premier outil à utiliser lorsque vous voulez comprendre comment MySQL planifie une requête. Pour un simple EXPLAIN SELECT, MySQL montre le plan choisi par l'optimiseur sans renvoyer le jeu de résultats. EXPLAIN ANALYZE exécute la requête et rapporte les temps réels, donc utilisez-le avec précaution sur les systèmes de production.

Comment utiliser EXPLAIN

Pour une requête de lecture, préfixez avec EXPLAIN :

EXPLAIN SELECT * FROM users WHERE username = 'john_doe';

Interpréter la sortie de EXPLAIN

La sortie de EXPLAIN est une table avec plusieurs colonnes importantes :

• id : Le numéro de séquence du SELECT dans la requête. Les nombres plus élevés sont généralement exécutés en premier.
• select_type : Le type de SELECT (par exemple, SIMPLE, PRIMARY, SUBQUERY, DERIVED).
• table : La table accédée.
• partitions : Les partitions utilisées (si le partitionnement est activé).
• type : Le type de jointure. C'est l'une des colonnes les plus utiles. Visez const, eq_ref, ref ou range lorsque la forme de la requête le permet. Méfiez-vous de index et surtout de ALL sur les grandes tables.
• possible_keys : Montre les index que MySQL pourrait utiliser.
• key : L'index que MySQL a effectivement choisi d'utiliser.
• key_len : La longueur de la partie de l'index que MySQL prévoit d'utiliser. Plus court n'est pas automatiquement mieux ; cela dépend de la sélectivité et de la requête.
• ref : La colonne ou constante comparée à l'index (key).
• rows : Une estimation du nombre de lignes que MySQL prévoit d'examiner.
• filtered : Le pourcentage de lignes filtrées par la condition de la table.
• Extra : Contient des informations supplémentaires sur la façon dont MySQL résout la requête. Les valeurs clés à surveiller incluent :
  • Using where : Indique que MySQL applique une condition lors du traitement des lignes. C'est courant et pas toujours mauvais.
  • Using index : Signifie que la requête est couverte par un index (toutes les colonnes requises sont dans l'index), ce qui est bon.
  • Using temporary : MySQL doit créer une table temporaire, souvent pour les opérations GROUP BY ou ORDER BY. Cela peut être lent.
  • Using filesort : MySQL doit effectuer un tri externe (n'utilisant pas d'index pour l'ordre). C'est souvent un signe de clause ORDER BY inefficace.

Identifier les goulots d'étranglement avec EXPLAIN

Regardons quelques scénarios courants et comment EXPLAIN aide à identifier les problèmes :

Scénario 1 : Analyse complète de table

Considérez une requête comme :

SELECT * FROM orders WHERE order_date = '2023-10-26';

Si la colonne order_date n'est pas indexée, EXPLAIN pourrait montrer :

+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 1000000 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+

Problème : type: ALL indique une analyse complète de table. rows: 1000000 montre que MySQL doit examiner chaque ligne de la table orders. key: NULL signifie qu'aucun index n'a été utilisé.

Solution : Ajoutez un index sur la colonne order_date :

CREATE INDEX idx_order_date ON orders (order_date);

Après avoir ajouté l'index, réexécutez EXPLAIN. Vous devriez voir un type d'accès plus sélectif comme ref ou range, et le nombre estimé de lignes devrait diminuer si le filtre de date est sélectif.

Scénario 2 : ORDER BY ou GROUP BY inefficace

SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id ORDER BY customer_id;

Si customer_id n'est pas indexé, EXPLAIN pourrait montrer :

+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------+
| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows   | Extra                            |
+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------+
|  1 | SIMPLE      | orders | index | NULL          | NULL | NULL    | NULL | 100000 | Using temporary; Using filesort |
+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------+

Problème : Using temporary et Using filesort indiquent que MySQL effectue des opérations coûteuses pour trier et grouper les données. Cela est souvent dû au fait qu'aucun index ne peut satisfaire efficacement à la fois les exigences de regroupement et de tri.

Solution : Pour cette requête spécifique, un index sur (customer_id) peut permettre à MySQL de parcourir les lignes dans l'ordre de regroupement. Si la requête réelle filtre d'abord par date, statut ou locataire, un index composite peut être meilleur, comme (tenant_id, status, customer_id).

CREATE INDEX idx_customer_id ON orders (customer_id);

Scénario 3 : Utilisation inutile de SELECT *

Lorsque vous sélectionnez toutes les colonnes (*) mais que vous n'en avez besoin que de quelques-unes, vous transférez plus de données et pouvez empêcher un index couvrant d'être utile. Cela est particulièrement notable sur les tables larges avec des colonnes JSON, des blobs de texte ou de nombreux champs nullables.

-- Supposons un index sur 'status'
SELECT * FROM tasks WHERE status = 'pending';

EXPLAIN pourrait montrer Using where mais si la requête nécessite des colonnes qui ne sont pas dans l'index utilisé pour le filtrage, elle devra toujours accéder aux données de la table.

Solution : Spécifiez uniquement les colonnes dont vous avez besoin :

SELECT task_id, description FROM tasks WHERE status = 'pending';

Si vous interrogez fréquemment cette forme exacte, envisagez un index couvrant qui inclut la colonne de filtre et les colonnes retournées :

CREATE INDEX idx_tasks_status_id_description
  ON tasks (status, task_id, description);

Ne créez pas d'index couvrants pour chaque requête. Ils accélèrent les lectures au prix du stockage et de la surcharge d'écriture.

Réécrire les requêtes lentes

Au-delà de l'indexation, la façon dont vous structurez le SQL peut modifier la quantité de travail que MySQL doit effectuer.

Évitez les sous-requêtes corrélées

Les sous-requêtes corrélées peuvent s'exécuter une fois pour chaque ligne traitée par la requête externe. MySQL peut optimiser certaines d'entre elles, mais si EXPLAIN montre des recherches dépendantes répétées, une jointure ou une table dérivée est souvent plus claire et plus rapide.

Souvent inefficace :

SELECT o.order_id, o.order_date
FROM orders o
WHERE o.customer_id IN (
    SELECT c.customer_id
    FROM customers c
    WHERE c.country = 'USA'
);

Souvent mieux comme une jointure :

SELECT o.order_id, o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.country = 'USA';

Utilisez EXPLAIN sur les deux versions. La jointure n'est pas automatiquement plus rapide dans tous les schémas, mais elle est plus facile pour de nombreuses équipes à raisonner et à indexer.

Optimisez les clauses LIKE

Les caractères génériques en tête (%) dans les clauses LIKE empêchent généralement un index B-tree normal d'être utilisé pour une recherche par plage.

Inefficace :

SELECT * FROM products WHERE product_name LIKE '%widget';

Mieux (si possible) :

SELECT * FROM products WHERE product_name LIKE 'widget%';

Si vous avez besoin d'une correspondance de type "contient", envisagez les index full-text MySQL pour une recherche textuelle appropriée, les approches n-gram pour des langues spécifiques, ou un moteur de recherche lorsque la pertinence et la correspondance flexible sont importantes.

Utilisez UNION ALL au lieu de UNION lorsque c'est possible

UNION supprime les lignes en double, ce qui nécessite une étape supplémentaire de tri et de déduplication. Si vous savez qu'il n'y a pas de doublons ou que vous n'avez pas besoin de les supprimer, UNION ALL est plus rapide.

Lent :

SELECT name FROM table1
UNION
SELECT name FROM table2;

Rapide :

SELECT name FROM table1
UNION ALL
SELECT name FROM table2;

Autres conseils d'optimisation

• Gardez les statistiques à jour : Assurez-vous que les statistiques des tables sont à jour afin que l'optimiseur de requêtes puisse prendre des décisions éclairées. Cela est souvent géré automatiquement mais peut être mis à jour manuellement avec ANALYZE TABLE.
• Configuration du serveur : L'optimisation des requêtes ne compensera pas un petit pool de buffers InnoDB ou des disques surchargés. Dans MySQL 8.0, l'ancien cache de requêtes est supprimé, donc ne planifiez pas de nouvelles optimisations autour de query_cache_size.
• Surveillance régulière : Utilisez des outils comme MySQL Enterprise Monitor, Percona Monitoring and Management (PMM), ou les vues intégrées du Performance Schema pour suivre les requêtes lentes et identifier les tendances.

Un workflow pratique d'optimisation

Pour les systèmes de production, optimisez à partir de la requête lente vers l'extérieur :

1. Capturez le SQL exact, les valeurs liées, les nombres de lignes et le timing.
2. Exécutez EXPLAIN FORMAT=TREE ou EXPLAIN FORMAT=JSON si votre version de MySQL le supporte.
3. Vérifiez si l'index sélectionné correspond au modèle de filtre et de jointure.
4. Testez une réécriture de requête ou un changement d'index sur des données réalistes.
5. Comparez les lignes examinées, les tables temporaires, le comportement de tri et la latence murale.

Cela vous évite d'ajouter des index parce qu'une requête "semble lente". Les index ont un coût. Chaque insertion, mise à jour et suppression doit les maintenir. Une table avec dix index qui se chevauchent peut devenir globalement plus lente même si une requête de lecture s'améliore.

Pour une requête courante d'application multi-locataire, l'ordre des index importe souvent plus que le nombre de colonnes indexées :

SELECT id, created_at, total
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
  AND created_at >= '2025-01-01'
ORDER BY created_at DESC
LIMIT 50;

Un index utile pourrait être :

CREATE INDEX idx_orders_tenant_status_created
  ON orders (tenant_id, status, created_at DESC);

Cet index commence par des filtres d'égalité, puis prend en charge la plage de dates et le tri. Si vous mettez created_at en premier, MySQL peut analyser de nombreux locataires avant de trouver le bon. Si vous omettez status, la requête peut toujours fonctionner mais examiner de nombreuses lignes supplémentaires.

Surveillez les filtres non sargables

Une condition est sargable lorsque MySQL peut utiliser un index pour rechercher des lignes correspondantes. Envelopper une colonne indexée dans une fonction brise souvent cela :

-- Plus difficile d'utiliser un index sur created_at
SELECT * FROM orders
WHERE DATE(created_at) = '2025-01-15';

Réécrivez-la comme une plage :

SELECT *
FROM orders
WHERE created_at >= '2025-01-15'
  AND created_at <  '2025-01-16';

La deuxième version permet à MySQL de rechercher dans un index sur created_at. La même idée s'applique à LOWER(email), aux opérations mathématiques sur les colonnes numériques et aux conversions de type implicites. Si la colonne est indexée, gardez le côté colonne de la comparaison propre lorsque vous le pouvez.

Soyez prudent avec la pagination

La pagination par décalage devient coûteuse sur les pages profondes :

SELECT id, title
FROM posts
WHERE status = 'published'
ORDER BY published_at DESC
LIMIT 20 OFFSET 200000;

MySQL doit toujours parcourir les lignes précédentes avant de retourner la page demandée. Pour les flux, les journaux d'audit et les tables d'administration, la pagination par clé est généralement meilleure :

SELECT id, title, published_at
FROM posts
WHERE status = 'published'
  AND (published_at, id) < ('2025-05-01 12:00:00', 987654)
ORDER BY published_at DESC, id DESC
LIMIT 20;

Associez-la à un index tel que (status, published_at, id). Cela modifie un peu le comportement du produit car les utilisateurs se déplacent via un curseur plutôt que de sauter à la page 10 000, mais cela peut transformer une requête pénible en une requête prévisible.

Validez avec des données réelles

Les petites bases de données de staging mentent. Une requête instantanée sur 20 000 lignes peut être horrible sur 200 millions de lignes, surtout lorsque la distribution des données est asymétrique. Testez avec un volume et une cardinalité similaires à la production lorsque c'est possible. Si vous ne pouvez pas copier les données de production, générez au moins des données avec des tailles de locataires, une distribution de statuts et des plages de dates similaires.

Une dernière habitude utile : conservez l'ancien plan et le nouveau plan dans le ticket. Vous voudrez savoir pourquoi un index existe à l'avenir.

La meilleure habitude d'optimisation MySQL est de faire en sorte que chaque changement gagne sa place. Capturez la requête lente, inspectez le plan, modifiez une requête ou un index, puis comparez la latence et les lignes examinées. Un plan EXPLAIN propre est utile, mais le vrai gain est une latence de production plus faible sans créer de nouvelle pression d'écriture ou de gonflement du stockage.