12 mars 2019

Comment accélérer vos requêtes MySQL de 300x

Quelques conseils simples pour une base de données SQL rapide et performante.

Print Friendly, PDF & Email

Avoir une base de données lente peut signifier dans la vraie vie avoir des logiciels tout aussi lents, des temps d'attente élevés et des temps d'arrêt associés qui peuvent avoir un impact à la fois sur l'ambiance de vos journées qui deviennent frustrantes en raison de logiciels non réactifs, et une expérience utilisateur définitivement inadéquate.

Sur le vrai métier des cas connus on pense à un centre d'appels dans lequel avec 500 employés par jour sur un quart de 8 heures on récupère 2 minutes d'attente pour chaque quart de chaque opérateur, une bonne optimisation de la base de données nous permettra de réduire temps d'arrêt excessifs et de récupérer 1000 minutes homme par jour, soit 5000 par semaine, 20000 240 par mois et 4000 80 par an soit 20 heures totales d'attente à vide, pour des économies de coûts et une productivité accrue d'environ XNUMX XNUMX euros par an à un coût moyen de XNUMX euros de l'heure.

Ceci est juste un exemple et un petit tutoriel qui veut ouvrir les yeux sur la façon d'optimiser les requêtes MySQL en prenant par exemple le SGBD MySQL, mais la théorie rapportée ici est la base de tous les SGBD modernes tels que PostgreSQL, Oracle, SQL Server de Microsoft et essentiellement tous les SGBD qui utilisent la syntaxe SQL standard ANSI.

Avant de pouvoir optimiser les requêtes lentes, vous devez les trouver.

MySQL a un journal de requête lente (ou requête lente) intégré. Pour l'utiliser, ouvrez le fichier mon.cnf et définissez la variable slow_query_log sur « Activé ». Impôt long_query_time sur le nombre de secondes qu'une requête doit prendre pour être considérée comme lente, par exemple 0.2. Définissez slow_query_log_file sur le chemin où vous souhaitez enregistrer le fichier. Exécutez ensuite votre code et toutes les requêtes au-dessus du seuil spécifié seront ajoutées à ce fichier.

Une fois que vous savez ce que sont les requêtes lentes et problématiques, vous pouvez commencer à explorer ce qui les ralentit. Un outil offert par MySQL est le mot-clé EXPLIQUE . Fonctionne avec des instructions SÉLECTIONNER, SUPPRIMER, INSÉRER, REMPLACER et METTRE À JOUR . Il est simplement utilisé pour analyser et expliquer la requête comme ceci :

EXPLIQUE SELECT image.id, image.titre
De image
en stock JOIN album ON image.id_album = album.id
 album.user_id = 1;

Le résultat que vous obtiendrez est une explication de la façon dont les données sont accessibles. Une ligne s'affiche pour chaque table impliquée dans la requête :

Les parties importantes auxquelles il faut prêter une attention particulière sont le nom de la table, la clé utilisée et le nombre de lignes analysées lors de l'exécution de la requête.

Il numérise essentiellement 2.000.000 20.000 40 d'images, puis numérise XNUMX XNUMX albums pour chaque image. Cela signifie qu'il scanne en fait XNUMX milliards de lignes pour la table d'album. Cependant, il est possible de rendre ce processus beaucoup plus efficace.

Utilisez des index.

Les performances peuvent être considérablement améliorées en utilisant des index. Considérez les données comme des noms dans un carnet d'adresses. Vous pouvez parcourir toutes les pages ou vous pouvez faire glisser la carte de lettre appropriée pour localiser rapidement le nom dont vous avez besoin.

Utilisez des index pour éviter des passages inutiles dans les tables. Par exemple, vous pouvez ajouter un index sur image.album_id de cette façon:

ALTER TABLE image ADD INDEX(id_album);

Désormais, si vous exécutez la requête, le processus n'implique plus l'analyse de la liste complète des images. Tout d'abord, tous les albums sont scannés pour trouver ceux qui appartiennent à l'utilisateur. Par la suite, les images sont rapidement localisées à l'aide de la colonne index_id indexée. Cela réduit le nombre de lignes numérisées à 200.000 317. La requête est également environ XNUMX fois plus rapide que l'original.

Vous pouvez vous assurer que les deux tables utilisent une clé en ajoutant l'index suivant :

ALTER TABLE album ADD INDEX(user_id);

Cette fois, la table de l'album n'est pas numérisée dans son intégralité, mais les bons albums sont rapidement trouvés grâce à la clé user_id . Lorsque ces 100 albums sont numérisés, les images associées sont localisées à l'aide de la touche id_album . Chaque table utilise une clé pour des performances optimales, rendant la requête 380 fois plus rapide que l'originale.

Cela ne signifie pas que vous devez ajouter des index partout, car chaque index ralentit l'écriture dans la base de données. Gain sur la lecture mais perte sur les écritures dans la base de données. Il suffit donc d'ajouter des index qui augmentent réellement les performances de lecture.

Utilisez EXPLAIN pour confirmer et supprimer tous les index qui ne sont pas utilisés dans les requêtes.

Effectuez un profilage et une analyse ultérieure des requêtes pour découvrir les problèmes d'application potentiels.

Au lieu de cela, il peut arriver d'avoir une base de données avec des requêtes légères et efficaces, mais néanmoins la charge du serveur monte en flèche et en particulier la charge du processus du serveur de base de données.

Il peut ne pas s'agir d'un problème de base de données, ou plutôt pas simplement d'un problème de base de données, mais d'un niveau d'application tel que, par exemple, un script PHP qui appelle par erreur cycliquement une certaine requête ou qui exécute une requête incorrecte sans utiliser les clauses adéquates qui peuvent permettre une vitesse d'exécution de la requête elle-même.

Un exemple classique est la symptomatologie suivante : la base de données est toujours la même, elle a toujours été bonne pendant des mois ou des années, il n'y a pas de pics et de volumes d'accès et de visites différents des standards mais d'un coup la charge du serveur et MySQL commence à se développer d'une manière apparemment non motivée.

Est-ce la faute de la base de données ou une erreur a-t-elle été commise du côté de l'application ?

Pour analyser cette éventualité par l'ingénieur système ou enquêter sur tout problème dérivant d'une implémentation correcte de l'application par le développeur, vous pouvez utiliser des outils de profilage des performances tels que New Relic ou utiliser des outils tels que Boîte à outils Percona que nous avons couvert dans l'article spécifique dédié sur Boîte à outils Percona

Mettez à jour vers la dernière version de MySQL ou revenez à une version précédente.

Autant que cela puisse paraître (et c'est en effet) l'œuf de Christophe Colomb, il convient de rappeler que mettre à niveau vers la dernière version de MySQL (ou fourches associées) cela pourrait automatiquement améliorer les temps d'exécution des requêtes et réduire la charge de votre SGBD.

Nous avons des témoignages directs de nos clients (au moins 4 en 2021) qui ont eu ce type de problème, c'est-à-dire des requêtes extrêmement lentes qui sont devenues rapides (de 10 secondes à 0,2 pour ainsi dire) simplement en passant de Percona Server 5.6 à Percona Server 5.7.

Évidemment, le même concept s'applique à un saut de version et à partir de MySQL 5.7 vers MySQL 8.0. Les benchmarks trouvés en ligne donnent une compréhension détaillée des avantages possibles et potentiels qui peuvent être obtenus.

C'est sûrement une voie à suivre juste avant de commencer à vous concentrer sur le profilage et l'optimisation.

On comprend combien d'un point de vue académique et pour les puristes cela peut sembler une abomination de laisser des requêtes lentes mal conçues qui s'exécutent rapidement, mais il faut aussi évaluer la chose du point de vue entrepreneurial et d'un réel pragmatisme.

En fait, il est souvent nécessaire de résoudre un problème en quelques heures au maximum et avec des coûts minimes. Essayer cette voie ne fera pas de nous des ceintures noires SQL Standard ANSI XNUMXe dan, mais les entrepreneurs ont toujours apprécié les solutions rapides, abordables et réalisables.

Souvent comme on dit "L'important c'est que ça marche".

Requêtes lentes et MySQL lent sur WordPress (ou autre CMS)

Lorsque le problème susmentionné apparaît sur des CMS Open Source tels que WordPress, vous n'avez souvent même pas l'occasion de réaliser ce qui s'est réellement passé du côté de l'application pour que le site avec base de données et rapide accrocheur avant, devienne un pachyderme 10 minutes plus tard.

Peut-être que l'utilisateur hébergé par nos services vient de penser à mettre à jour les deux ou trois derniers plugins WordPress qui viennent de sortir sans trop se poser de questions car il n'est pas technicien et parce qu'il l'a toujours fait sans aucun problème.

Cependant, il arrive plus fréquemment qu'on ne l'imagine qu'un mauvais plugin, mal écrit avec une mauvaise logique métier peut entraîner de sérieux dommages et impacter drastiquement les performances de la base de données, comme un plugin qui commence à écrire du junk sur une table WordPress partagée comme la table wp_options.

Pour avoir un exemple réel de ce dont nous parlons je vous invite à lire ce cas d'un de nos clients

Seule la compréhension de ce qui se passe du côté de l'application, en effet, peut permettre d'appréhender les problèmes de charge CPU qui ne sont en aucun cas imputables à la conception de la base de données.

Vous rencontrez toujours des problèmes avec la vitesse de MySQL ?

Si après avoir lu notre article et suivi nos conseils et que vous avez également une base de données d'entreprise ou une application qui est lente et ne vous satisfait pas, veuillez nous contacter pour évaluer les marges d'amélioration de votre base de données et profiter du service d'optimisation MySQL afin de d'avoir une base de données rapide et performante.

Vous avez des doutes ? Vous ne savez pas par où commencer ? Contactez-nous


Nous avons toutes les réponses à vos questions pour vous aider à faire le bon choix.

Discute avec nous

Discutez directement avec notre support avant-vente.

0256569681

Contactez-nous par téléphone pendant les heures de bureau 9h30 - 19h30

Contactez-nous en ligne

Ouvrez une demande directement dans l'espace contact.

INFORMATIONS

ManagedServer.it est le premier fournisseur italien de solutions d'hébergement hautes performances. Notre modèle d'abonnement est abordable et prévisible, afin que les clients puissent accéder à nos technologies d'hébergement fiables, à nos serveurs dédiés et au cloud. ManagedServer.it offre également d'excellents services d'assistance et de conseil sur l'hébergement des principaux CMS Open Source tels que WordPress, WooCommerce, Drupal, Prestashop, Magento.

haut