High Performance MySQL [Ch. 6 : Optimiser les performances des requêtes]

Pourquoi une requête est lente

Une requête est une suite de tâches : analyse, planification, exécution, envoi des résultats. L'exécution domine, car c'est là que le serveur demande les lignes au moteur de stockage, puis les trie, les regroupe et les filtre. Accélérer une requête, c'est supprimer des tâches, les exécuter moins souvent ou les rendre moins coûteuses, et le levier le plus puissant est presque toujours la quantité de données touchées.

L'application en demande-t-elle trop ?

• Plus de lignes qu'elle n'en affiche. MySQL construit le résultat complet, même si le client ne lit que les dix premières lignes. Utilisez LIMIT.
• Toutes les colonnes. SELECT * traîne des lignes entières à travers les jointures et exclut les index couvrants. Nommez les colonnes que vous utilisez.
• Les mêmes données, encore et encore. Récupérer le nom d'un utilisateur, puis son e-mail, puis son avatar en trois requêtes, c'est trois allers-retours pour une seule ligne.
• Les requêtes N+1. Les ORM qui chargent les associations à la demande transforment « lister les projets et leurs bugs » en une requête plus une par projet. Chargez l'association d'emblée avec un seul WHERE project_id IN (...).

Le serveur en examine-t-il trop ?

Trois chiffres décrivent le coût d'une requête, et tous trois figurent dans le slow query log : le temps de réponse, les lignes examinées et les lignes renvoyées. Le temps de réponse est le plus bruité. Il inclut l'attente des verrous et des E/S, et varie selon tout ce que fait le serveur par ailleurs. Le rapport entre lignes examinées et lignes renvoyées est plus parlant : 1:1 est idéal, les jointures et les agrégats l'augmentent légitimement, et un rapport de l'ordre du millier signifie que le serveur lit des lignes uniquement pour les jeter.

Le meilleur indicateur est le type d'accès, la colonne type de EXPLAIN :

La colonne Extra indique où le filtrage a lieu. Using index signifie qu'un index couvrant a répondu à la requête sans toucher aux lignes. Using index condition signifie que le moteur a filtré sur les colonnes de l'index avant de lire les lignes. Using where signifie que les lignes ont été lues puis filtrées par le serveur, ce qui est la manière coûteuse.

Quand le type d'accès est mauvais, la solution est un index que la requête peut utiliser, une requête réécrite pour utiliser un index existant, ou un changement de schéma comme une table de synthèse.

Restructurer les requêtes

L'objectif : la même réponse pour moins de travail, ou parfois une réponse légèrement différente, acceptable et bien moins coûteuse.

Une requête complexe ou plusieurs requêtes simples ? L'ancienne règle consistait à minimiser les allers-retours. MySQL se connecte et répond vite aux petites requêtes, et les réseaux au sein d'un data center sont rapides : découper le travail est donc souvent acceptable et plus facile à maintenir. Tranchez au cas par cas.

Découpez les grosses écritures. Un seul DELETE de millions d'anciennes lignes garde ses verrous pendant toute son exécution, gonfle l'undo log et fait prendre du retard aux réplicas. Supprimez plutôt par lots, avec une pause entre chacun si nécessaire :

-- une seule instruction, une transaction énorme
DELETE FROM bugs WHERE created_at < NOW() - INTERVAL 1 YEAR;

-- répéter jusqu'à ce qu'elle affecte 0 ligne
DELETE FROM bugs WHERE created_at < NOW() - INTERVAL 1 YEAR LIMIT 5000;

Décomposez les jointures. Exécuter une requête par table et faire la jointure dans l'application peut aider quand les résultats peuvent être mis en cache (l'id d'un tag change rarement), quand des requêtes plus courtes réduisent la contention de verrous, ou quand une liste IN (...) d'ids triés lit les lignes de façon plus séquentielle qu'une boucle imbriquée. C'est un outil, pas un réflexe par défaut.

De l'arbre syntaxique au plan

Le parseur transforme le SQL en arbre et rejette les erreurs de syntaxe. Le préprocesseur vérifie ce que la grammaire ne peut pas vérifier : que les tables et les colonnes existent, que les noms ne sont pas ambigus et que l'utilisateur dispose des privilèges nécessaires. Puis l'optimiseur cherche un plan peu coûteux.

L'optimiseur de MySQL est fondé sur les coûts, avec quelques raccourcis fondés sur des règles. Il estime le coût des plans candidats à partir de statistiques (taille des tables et des index, cardinalité des index, distribution des clés) et choisit le moins coûteux. Il peut choisir un plan moins bon pour des raisons prévisibles : les statistiques sont des estimations et les nombres de lignes d'InnoDB sont approximatifs ; son modèle de coût ignore quelles pages sont déjà en cache ; et il ne voit pas le coût du travail qu'il ne contrôle pas, comme les fonctions stockées. Le coût estimé de la dernière requête se trouve dans Last_query_cost.

Ce que l'optimiseur fait pour vous

• Il réordonne les jointures pour partir de la table qui produit le moins de lignes.
• Il transforme les jointures externes en jointures internes quand la clause WHERE rejette de toute façon les lignes NULL.
• Il simplifie les expressions : 5 = 5 AND a > 5 devient a > 5, et les conditions impossibles terminent la requête au plus tôt.
• Il résout d'abord les constantes. Une recherche par clé primaire dans une table devient une constante utilisée par le reste du plan.
• Il propage les égalités à travers les colonnes de jointure, de sorte qu'un filtre sur une table restreint aussi l'autre.
• Il répond à MIN() et MAX() depuis une extrémité de l'index : une seule lecture au début ou à la fin du B-Tree.
• Il trie les listes IN() et y fait une recherche dichotomique au lieu de les parcourir.
• Il s'arrête tôt pour LIMIT, DISTINCT et NOT EXISTS dès que la réponse est connue.
• Il réécrit les sous-requêtes en semi-jointures ou en recherches d'index quand c'est possible.

Comment MySQL exécute les jointures

MySQL traite toute opération multitable, y compris les tables dérivées et UNION, comme une jointure, et son algorithme traditionnel est la boucle imbriquée : pour chaque ligne de la première table, trouver les lignes correspondantes dans la deuxième ; pour chacune d'elles, trouver les correspondances dans la troisième ; émettre une ligne quand toutes les tables ont trouvé une correspondance, puis revenir en arrière. Un LEFT JOIN est la même boucle, qui émet des NULL quand la table interne n'a pas de correspondance ; RIGHT JOIN est réécrit en jointure gauche. Ce modèle ne peut pas exprimer FULL OUTER JOIN, que MySQL ne prend toujours pas en charge. Depuis MySQL 8.0.18, les équijointures sans index utilisable s'exécutent en hash join plutôt qu'en boucle imbriquée.

Comme le coût vaut « lignes de la table externe × recherches par ligne », l'ordre des jointures compte énormément. Joindre 5 000 étudiants à leurs notes coûte 5 000 recherches d'index dans les notes, quelle que soit la façon dont vous écrivez la clause FROM ; l'optimiseur choisit l'ordre qui demande le moins de sondages. STRAIGHT_JOIN impose l'ordre écrit, ce qui est utile pour tester et, parfois, pour passer outre une mauvaise estimation. Avec beaucoup de tables, le nombre d'ordres possibles explose (10 tables en ont déjà 3 628 800) : au-delà de optimizer_search_depth, MySQL passe donc à une recherche gloutonne.

Le plan final n'est pas du bytecode. C'est un arbre d'opérations que l'exécuteur parcourt, et que EXPLAIN FORMAT=TREE affiche directement.

Comment cela apparaît dans votre verdict

Chaque défi est noté sur les lignes examinées par rapport à un plan de référence : c'est ce chapitre résumé en un seul chiffre. Joindre une table dont vous n'avez pas besoin, regrouper des lignes déjà uniques, ou filtrer après un parcours plutôt que pendant une recherche d'index : tout cela se traduit par des lectures handler supplémentaires, et par un CORRECT_BUT_INEFFICIENT.

Questions fréquentes