Optimisation des performances de MySQL avec des index et des explications –

Les techniques pour améliorer la performance des applications peuvent provenir de différents endroits, mais normalement la première chose que nous observons – le goulot d'étranglement le plus courant – est la base de données. Peut-il être amélioré? Comment pouvons-nous mesurer et comprendre ce qui est nécessaire et ce qui peut être amélioré?

Un outil très simple mais très utile est le profilage de la requête. L'activation du profilage est un moyen simple d'obtenir une estimation plus précise du temps d'exécution d'une requête. C'est un processus en deux étapes. Premièrement, nous devons activer le profilage. Ensuite, nous appelons afficher les profils pour obtenir le temps d'exécution de la requête.

Imaginons que nous ayons l'insertion suivante dans notre base de données (supposons que l'utilisateur 1 et la galerie 1 sont déjà créés):

 INSERT INTO `homestead`.`images` (` id`, `gallery_id`,` nom_fichier_original ', `nom_fichier`,` description`) VALEURS
(1, 1, 'me.jpg', 'me.jpg', 'Une photo de moi marchant dans la rue'),
(2, 1, 'dog.jpg', 'dog.jpg', 'Une photo de mon chien dans la rue'),
(3, 1, 'cat.jpg', 'cat.jpg', 'Une photo de mon chat qui marche dans la rue'),
(4, 1, 'purr.jpg', 'purr.jpg', 'Une photo de mon chat ronronnant');

Évidemment, cette quantité de données ne causera aucun problème, mais utilisons-la pour faire un profil simple. Considérons la requête suivante:

 SELECT * FROM `homestead`.`images` AS i
O i i.description COMME '% street%';

Cette requête est un bon exemple de celle qui peut devenir problématique dans le futur si nous recevons beaucoup d'entrées de photos.

Pour obtenir un temps d'exécution précis sur cette requête, nous utiliserons le SQL suivant:

 définir le profilage = 1;
SELECT * FROM `homestead`.`images` AS i
O i i.description COMME '% street%';
montrer les profils;

Le résultat ressemblerait à ceci:

Comme on peut le voir, les montrent les profils; non seulement pour la requête d'origine mais aussi pour toutes les autres requêtes qui sont faites. De cette façon, nous pouvons établir un profil précis de nos requêtes.

Mais comment pouvons-nous réellement les améliorer?

Nous pouvons compter sur notre connaissance de SQL et improviser, ou nous pouvons compter sur la commande MySQL explain et améliorer les performances de nos requêtes basées sur des informations réelles.

Explain est utilisé pour obtenir un plan d'exécution de requête, ou comment MySQL exécutera notre requête. Il fonctionne avec SELECT SUPPRIMER INSERT REPLACE et UPDATE instructions, et affiche des informations de l'optimiseur sur le plan d'exécution de l'instruction. La documentation officielle fait un très bon travail décrivant comment expliquer peut nous aider:

Avec l'aide d'EXPLAIN, vous pouvez voir où vous devriez ajouter des index aux tables afin que le instruction s'exécute plus rapidement en utilisant des index pour rechercher des lignes. Vous pouvez également utiliser EXPLAIN pour vérifier si l'optimiseur joint les tables dans un ordre optimal.

Pour illustrer l'utilisation de explain nous utiliserons la requête faite par notre UserManager.php pour trouver un utilisateur par email:

 SELECT * FROM `homestead`.`users` O email email = 'claudio.ribeiro@examplemail.com';

Pour utiliser la commande explain il suffit de la précéder avant de sélectionner des requêtes de type:

 EXPLAIN SELECT * FROM `homestead`.`users` OERE email = 'claudio.ribeiro@examplemail.com' ;

Ceci est le résultat (défilement vers la droite pour voir tous):

Ces résultats ne sont pas faciles à comprendre à première vue, alors regardons chacun d'entre eux de plus près:

• id : il s'agit simplement de l'identificateur séquentiel pour chacune des requêtes dans SELECT.

• select_type : le type de requête SELECT. Ce champ peut prendre un certain nombre de valeurs différentes, donc nous allons nous concentrer sur les plus importantes:

  • SIMPLE : une requête simple sans sous-requêtes ni unions
  • PRIMARY : le select est dans la requête la plus externe dans une jointure
  • DERIVED : le select est une partie d'une sous-requête dans un from
  • SUBQUERY : le premier select dans une sous-requête
  • UNION : le select est le second ou

  La liste complète des valeurs qui peuvent apparaître dans un champ select_type peut être trouvée ici ici .

• table : le tableau auquel il est fait référence par la ligne.

• type : ce champ est la manière dont MySQL rejoint les tables utilisées. C'est probablement le champ le plus important dans la sortie d'explication. Il peut indiquer des index manquants et peut également montrer comment la requête doit être réécrite. Les valeurs possibles pour ce champ sont les suivantes (ordonnées du meilleur type au pire):

  • système : la table a zéro ou une ligne
  • const : la table n'a qu'une correspondance rangée qui est indexée. C'est le type de jointure le plus rapide
  • eq_ref : toutes les parties de l'index sont utilisées par la jointure et l'index est soit PRIMARY_KEY soit UNIQUE NOT NULL.
  • ref : toutes les lignes correspondantes d'une colonne d'index sont lus pour chaque combinaison de lignes de la table précédente. Ce type de jointure apparaît normalement pour les colonnes indexées par rapport aux opérateurs = ou
  • fulltext : la jointure utilise l'index FULLTEXT de la table
  • ref_or_null : ceci est Identique à ref, mais contient également des lignes avec une valeur NULL de la colonne.
  • index_merge : la jointure utilise une liste d'index pour produire le jeu de résultats. La colonne KEY du explique contiendra les clés utilisées
  • unique_subquery : une sous-requête IN renvoie un seul résultat de la table et utilise la clé primaire.
  • range ]: un index est utilisé pour rechercher des lignes correspondantes dans une plage spécifique
  • index : l'ensemble de l'arborescence d'index est analysé pour trouver les lignes correspondantes
  • all : la table entière est analysée lignes pour la jointure. C'est le pire type de jointure et indique souvent l'absence d'index appropriés sur la table

• possible_keys : montre les clés qui peuvent être utilisées par MySQL pour trouver les lignes de la table. Ces clés peuvent être utilisées ou non dans la pratique.

• keys : indique l'index réel utilisé par MySQL. MySQL recherche toujours une clé optimale pouvant être utilisée pour la requête. Tout en joignant plusieurs tables, il peut trouver d'autres clés qui ne sont pas listées dans possible_keys mais qui sont plus optimales.

• key_len : indique la longueur de l'index que l'optimiseur de requête a choisi d'utiliser.

• ref : Affiche les colonnes ou les constantes qui sont comparées à l'index nommé dans la colonne clé.

• rows : liste le nombre d'enregistrements examinés pour produire la sortie. C'est un indicateur très important. moins il y a d'enregistrements examinés, mieux c'est.

• Extra : contient des informations supplémentaires. Des valeurs telles que L'utilisation de filesort ou L'utilisation temporaire dans cette colonne peut indiquer une requête gênante.

La documentation complète sur le format de sortie expliquer peut être trouvée sur la page officielle MySQL .

Pour en revenir à notre requête simple: c'est un SIMPLE type de select avec un type const de jointure. C'est le meilleur cas de requête que nous puissions avoir. Mais que se passe-t-il lorsque nous avons besoin de requêtes plus grandes et plus complexes?

Pour en revenir à notre schéma d'application, nous pourrions vouloir obtenir toutes les images de la galerie. Nous pourrions également vouloir avoir seulement des photos qui contiennent le mot "chat" dans la description. C'est certainement un cas que nous pourrions trouver sur les exigences du projet. Jetons un coup d'oeil à la requête:

 SELECT gal.name, gal.description, img.filename, img.description FROM utilisateurs `homestead`.` utilisateurs 'AS
LEFT JOIN `homestead`.`galleries` AS gal ON utilisateurs.id = gal.user_id
LEFT JOIN `homestead`.`images` AS img sur img.gallery_id = gal.id
O im img.description LIKE '% dog%';

Dans ce cas plus complexe, nous devrions avoir plus d'informations à analyser sur notre expliquer :

 EXPLAIN SELECT gal.name, gal.description, img.filename, img.description FROM `homestead` .` utilisateurs "AS"
LEFT JOIN `homestead`.`galleries` AS gal ON utilisateurs.id = gal.user_id
LEFT JOIN `homestead`.`images` AS img sur img.gallery_id = gal.id
O im img.description LIKE '% dog%';

Cela donne les résultats suivants (défilement vers la droite pour voir toutes les cellules):

Regardons de plus près et voyons ce que nous pouvons améliorer dans notre requête

Comme nous l'avons vu précédemment, les colonnes principales que nous devrions regarder en premier sont la colonne et les colonnes . L'objectif devrait avoir une meilleure valeur dans la colonne et réduire autant que possible sur la colonne .

Notre résultat sur la première requête est index ce qui n'est pas un bon résultat du tout. Cela signifie que nous pouvons probablement l'améliorer.

En regardant notre requête, il y a deux façons de l'aborder. Tout d'abord, la table Users n'est pas utilisée. Nous développons la requête pour nous assurer que nous ciblons les utilisateurs, ou nous devons supprimer complètement la partie users de la requête. Cela ne fait qu'ajouter de la complexité et de la durée à nos performances globales.

 SELECT gal.name, gal.description, img.filename, img.description FROM `homestead`.`galleries` AS gal
LEFT JOIN `homestead`.`images` AS img sur img.gallery_id = gal.id
O im img.description LIKE '% dog%';

Alors maintenant nous avons exactement le même résultat. Jetons un coup d'œil à expliquer :

Il nous reste un TOUS sur le type. Alors que TOUS pourrait être le pire type de joint possible, il y a aussi des moments où c'est la seule option. Selon nos exigences, nous voulons toutes les images de la galerie, donc nous devons parcourir l'ensemble de la table des galeries. Alors que les index sont vraiment bons quand vous essayez de trouver des informations particulières sur une table, ils ne peuvent pas nous aider quand nous avons besoin de toute l'information. Quand nous avons un cas comme celui-ci, nous devons recourir à une méthode différente, comme la mise en cache.

Une dernière amélioration que nous pouvons faire, puisque nous traitons un LIKE est d'ajouter un FULLTEXT index à notre champ de description. De cette façon, nous pourrions changer le LIKE en un match () et améliorer les performances. Plus d'informations sur les index de texte intégral peuvent être trouvés ici .

Il y a aussi deux cas très intéressants que nous devons examiner: la plus récente et connexe fonctionnalité dans notre application. Ceux-ci s'appliquent aux galeries et touchent quelques cas d'angle dont nous devrions être conscients:

 EXPLAIN SELECT * FROM `homestead`.`galleries` AS gal
LEFT JOIN `homestead`.`users` COMME ON u.id = gal.user_id
O u u.id = 1
ORDER BY gal.created_at DESC
LIMITE 5;

Ce qui précède est pour les galeries liées.

 EXPLAIN SELECT * FROM `homestead`.`galleries` AS gal
ORDER BY gal.created_at DESC
LIMITE 5;

Ce qui précède est pour les nouvelles galeries

A première vue, ces requêtes devraient être très rapides car elles utilisent LIMIT . Et c'est le cas sur la plupart des requêtes utilisant LIMIT . Malheureusement pour nous et notre application, ces requêtes utilisent également ORDER BY . Parce que nous devons ordonner tous les résultats avant de limiter la requête, nous perdons les avantages d'utiliser LIMIT .

Puisque nous savons ORDER BY pourrait être difficile, appliquons notre fidèle

Et,

Comme nous pouvons le voir, nous avons le pire cas de type de jointure: ALL pour nos deux requêtes.

Historiquement, l'implémentation de ORDER BY de MySQL, en particulier avec LIMIT est souvent à l'origine des problèmes de performance de MySQL. Cette combinaison est également utilisée dans la plupart des applications interactives avec de grands ensembles de données. Les fonctionnalités comme les utilisateurs récemment enregistrés et les balises supérieures utilisent normalement cette combinaison.

Comme il s'agit d'un problème courant, il existe également une petite liste de solutions communes que nous devons appliquer pour résoudre les problèmes de performances. index . Dans notre cas, created_at est un excellent candidat, puisque c'est le domaine que nous passons commande. De cette façon, nous avons tous les deux ORDER BY et LIMIT exécutés sans scanner et trier le jeu de résultats complet

Voici certaines des mesures que nous devrions prendre lorsque nous avons à la fois LIMIT et ORDER BY afin de minimiser les problèmes de performance.

Conclusion

Comme nous pouvons le voir, explique peut être très utile pour repérer les problèmes dans nos requêtes dès le début. Il y a beaucoup de problèmes que nous remarquons seulement lorsque nos applications sont en production et que de grandes quantités de données ou beaucoup de visiteurs entrent dans la base de données. Si ces choses peuvent être repérées dès le début en utilisant expliquer il y a beaucoup moins de place pour les problèmes de performance dans le futur.

Notre application a tous les index dont elle a besoin, mais nous savons maintenant peut toujours recourir à expliquer et aux index chaque fois que nous devons vérifier les améliorations de performance

Cláudio Ribeiro est un développeur de logiciels, voyageur et écrivain de Lisbonne. Quand il ne développe pas quelque chose de cool chez Fixeads il est probablement en train de faire du sac à dos quelque part dans le monde ou de jouer avec un cadre obscur