Share
J'ai utilisé phpMyAdmin pendant plus de dix ans. Dans mes premières années avec l'outil, j'avais simplement besoin de quelque chose qui puisse me montrer la structure d'un tableau et me donner rapidement les données qu'il contient. Au fur et à mesure de l'évolution de mes besoins, les outils fournis avec phpMyAdmin ont également évolué, ce qui me permet de revenir comme principal outil MySQL, même avec optimisation..
J'ai eu le plaisir de travailler avec plusieurs bases de données différentes. Chacun a ses inconvénients et chacun ses points forts. Lorsque j'ai le choix, j'ai tendance à revenir à MySQL, malgré le fait que je suis trop bon marché pour acheter MySQL Enterprise. Au lieu de cela, je dois payer avec phpMyAdmin comme principal outil de profilage. Cela fonctionne bien pour moi, mais j'ai dû faire pas mal de recherches pour comprendre ce que je regarde lors du profilage de mes applications. J'espère pouvoir transmettre ceci d'une manière compréhensible pour le débutant, jusqu'au professionnel chevronné..
L'optimisation prend du temps. Les gestionnaires, les clients et les pairs, d'ailleurs, n'aiment pas entendre dire qu'un projet est en retard par rapport aux prévisions en raison de son optimisation. Souvent, nous nous dépêchons d'optimiser pour atteindre ces objectifs. En fin de compte, cependant, nous ne rendons service à personne. La plus belle application Web au monde vous incitera à renouveler votre activité s'il faut 10 secondes pour charger chaque page. De même, si nous attendons d’optimiser jusqu’à la fin de nos projets, il y aura probablement beaucoup plus de travail à faire que si nous avions vérifié le déroulement du projet..
Quelques notes avant d'entrer dans la viande et les pommes de terre. Premièrement, je ne vais pas entrer dans MySQL Tuning, car il est un peu en dehors de la portée de ce tutoriel. Bien que l'optimisation soit une optimisation, c'est un sujet à part entière à mon avis. Je mentionnerai brièvement quelques possibilités d'optimisation de la configuration de votre serveur, mais les mentions seront brèves. De plus, je regarderai principalement les tables MyISAM et non les tables InnoDB. La règle de base est que si vous écrivez beaucoup de données, utilisez InnoDB, mais si vous utilisez beaucoup plus de SELECT, utilisez MyISAM. De plus, je n'entre pas dans les domaines REPAIR, OPTIMIZE, CHECK et ANALYZE au niveau de la table, car ce didacticiel couvre l'optimisation des requêtes avec phpMyAdmin. Encore une fois, ceci est un peu en dehors de la portée de ce tutoriel.
Enfin, je vais regarder WordPress comme un exemple concret. Je serai le premier à vous dire que je ne suis pas un expert de WordPress, mais je peux examiner les requêtes générées avec les meilleurs d'entre elles. D'après ce que j'ai vu, la base de données avec WordPress est bien indexée, mais une fois que nous avons commencé à ajouter des éléments extérieurs à ces fichiers de base principaux, ces index ne sont peut-être pas les meilleurs pour ce dont nous avons besoin..
"L'optimisation prend du temps. Les responsables, les clients et les pairs, d'ailleurs, n'aiment pas savoir qu'un projet est en retard par rapport à l'optimisation."
la reponse courte est oui.
La réponse longue est que phpMyAdmin nous donne une chance de voir si nous devons optimiser nos requêtes et à quel point nous devons les optimiser. J'imagine que vous avez vu cet écran plus d'une fois si vous avez utilisé phpMyAdmin:
C'est l'écran de démarrage standard de phpMyAdmin. À moins que vous ne cherchiez des moyens d’optimiser, vous pourriez aller directement à vos tableaux dans le menu de gauche et ne jamais voir l’onglet du menu en haut. Ce menu, en particulier les onglets Statut et Variables, est l'endroit où nous allons commencer..
Commençons par l'écran Statut, qui pourrait être l'outil le plus important fourni par phpMyAdmin:
C'est le haut de l'écran d'état. Bien qu'il contienne des données intéressantes, si vous n'êtes jamais allé au-dessous du parchemin, vous avez manqué des informations très importantes. Par souci de brièveté, je voudrais examiner deux valeurs de compteur très simples sur lesquelles je suis obsédée, la première de mon environnement de test:
Les deux valeurs à surveiller sont Handler_read_rnd et Handler_read_rnd_next. Si ces deux valeurs sont en rouge, il y a des requêtes qui doivent être vérifiées, comme lorsque MySQL fait un SELECT, il lit l'intégralité de la table. Dans certains cas, il peut s'agir d'un problème de conception, car lorsque vous placez un index sur une table, l'écriture prend plus de temps et prend un peu plus d'espace. Cependant, si vous voyez quelque chose comme ça:
les chances sont, ce n'était pas par la conception. 141 millions de demandes de lecture d'une ligne sur une position fixe, et 16 milliards de demandes de lecture de la ligne suivante, signifient probablement qu'il nous manque un index ou deux (mille). Évidemment, ce nombre augmente en fonction du nombre de demandes. Par conséquent, plus un moteur de recherche indexe votre site, ou plus vous avez de visiteurs, plus un petit index oublié devient grand. Les balayages de table complets sont l’ennemi, ce qui vous donne un moyen rapide de voir à quel point cet ennemi est proche des portes..
Une autre table intéressante pour vérifier les performances des requêtes examine directement les sélections et les index:
Ce tableau accorde une attention particulière à vos jointures. Une combinaison dangereuse n'utilise et n'indexe sur aucune table, car vos analyses complètes augmentent de façon exponentielle en fonction du nombre de jointures que vous utilisez. Plus vos tables sont normalisées, plus vous devez prêter attention à vos index, ainsi qu'à la définition des champs que vous rejoignez..
Enfin, en fonction d'une variable globale, vous voudrez également vérifier cette table de variables:
Si vous enregistrez vos requêtes lentes, ce compteur de variable affiche le nombre identifié pour l'observation, en fonction du réglage du temps d'interrogation long. Ces variables peuvent être trouvées dans l'onglet Variables. Un rapide coup d’œil dans mon environnement de test montre ce paramètre (pour l’instant):
Ces deux onglets contiennent beaucoup plus d'informations, dont certaines sont absolument essentielles pour optimiser votre serveur MySQL. PhpMyAdmin permet même au novice d'identifier un problème et d'avoir une compréhension de base de ce que ce problème pourrait être. Si une valeur est verte, nous sommes bons. Si c'est rouge, il faut faire attention. Cela nous permet également de comprendre que nous avons fait des progrès. Lorsque nous redémarrons notre serveur, ces variables de session sont toutes vidées. Si nous avons apporté des modifications, nous pouvons voir dès le départ si nous avons eu un impact.
Maintenant que nous avons identifié la nécessité d'optimiser, examinons certains des outils que nous allons utiliser avant de résoudre nos problèmes. Le premier des outils, et probablement le plus utile, consiste à utiliser EXPLAIN. EXPLAIN nous donne essentiellement notre plan d'exécution des requêtes. Cela nous indique ce que MySQL prévoit de faire avec cette requête avant qu'elle ne soit exécutée..
Sans lire sur EXPLAIN, le résultat ne vous dit peut-être pas grand-chose. À l'aide d'une table que j'ai créée pour un tutoriel précédent, examinons un plan d'exécution non optimisé. Dans ce cas, ma table n'a que deux champs, l'un étant sales_id et l'autre étant sale_amount. Voici la requête avec laquelle je travaille:
SELECT sales_id, sale_amount FROM tutorial.sales ORDER BY sale_amount
En apparence, c'est une requête très simple. En tant que table de vente, la table grandira et grandira et grandira. J'ai généré 200 enregistrements pour le didacticiel précédent, et en faisant un simple SELECT avec une clause ORDER BY, cela a pris un peu plus de temps que prévu:
Cette requête avec seulement 200 enregistrements nous a coûté 0,15 seconde. Utilisons EXPLAIN pour comprendre comment MySQL voit cette requête. Cliquez simplement sur le lien "Expliquez SQL" pour voir les résultats:
Comme la plupart des choses, cela n’a pas beaucoup de sens si vous ne comprenez pas ce qui se dit. Pour quelqu'un qui n'a jamais exécuté EXPLAIN sur une requête, cela pourrait aussi bien être écrit en hiéroglyphes. Voyons si nous pouvons traduire quelque chose d'un peu plus compréhensible.
Le select_type nous dit que MySQL considère ce SELECT comme un simple, allez dans une table et traitez. S'il y avait une union ou une sous-requête, cela indiquerait quelle partie de l'instruction SELECT elle appelle. Par exemple, si je crée une requête qui a une sous-requête:
SELECT sale_amount en tant que montant FROM sales WHERE identifiant sales_id IN (SELECT commercial_id FROM sales_force WHERE identifiant sales_d = 4)
Nous obtenons un EXPLAIN de ceci:
Ce qui nous parle de la requête elle-même. Dans ce cas, notre select_type a changé pour indiquer que la première requête est la requête principale, puis MySQL va exécuter la sous-requête, qui est une vue. Il y a donc une autre sous-requête à exécuter. ids. Le manuel de référence de MySQL donne toutes les valeurs possibles:
Retour à notre exemple d'origine:
Le type à utiliser est celui à surveiller car il vous indique si MySQL va analyser toute la table ou s'il utilisera un index pour trouver rapidement les résultats. C’est la colonne principale à examiner lorsque vous optimisez vos requêtes. De l'ordre du bon au mauvais, les valeurs sont:
Pour commencer, vous devez optimiser toutes les requêtes correspondant au type de indice ou tout. Si vous pouvez débarrasser votre application de ces deux types, vos performances vont s'améliorer. C'est ici que mes amis commencent.
Le reste des colonnes traite des index utilisés par MySQL, ainsi que du nombre de lignes qu'il devra analyser avant de voir s'il existe un résultat valide. Au fur et à mesure que vous vous débarrassez des types "index" et "all", ils vous permettront de comprendre exactement quel index est utilisé par MySQL pour exécuter cette requête. Pour déplacer une requête dans l’échelle, vous commencez à peaufiner vos index pour améliorer les performances. À des fins d'illustration, je vais m'en tenir à la résolution de "tous" ou de scans de table complets.
La dernière colonne est la colonne "extra". La colonne extra vous indique des informations sur la requête, si une clause WHERE est utilisée ou non, si c'est un WHERE impossible ou non, ce qui signifie que cette requête retournera toujours une valeur NULL car la clause WHERE le rend impossible à exécuter. La seule valeur à laquelle nous devons accorder une attention toute particulière et nous en débarrasser est le "Utilisation du fichier" que nous avons dans notre exemple. Quand vous voyez cette valeur, MySQL doit faire un autre passage parmi les résultats pour trier les valeurs. Donc, dans le cas de notre requête initiale:
SELECT sales_id, sale_amount FROM tutorial.sales ORDER BY sale_amount
Non seulement MySQL scanne toute la table, mais il doit l’analyser deux fois pour trier les résultats à cause de notre instruction ORDER BY. Ceci est évidemment doublement mauvais. Nous optimiserons cette requête et bien d’autres dans les sections suivantes..
Dans MySQL 5.0.37, nous avons utilisé un autre outil d'optimisation: le profileur MySQL. De plus, phpMyAdmin a ajouté le support pour cette fonctionnalité dans la version 2.11, donc si vous avez les deux versions disponibles, nous aurons un autre outil à ajouter à l'optimisation.
Le profileur MySQL fournit des informations sur les goulots d’étranglement de nos requêtes. Cela nous permet de voir ce qui se passe pendant l'exécution réelle de nos requêtes, vice que EXPLAIN fait, qui est donner le plan d'exécution avant. Voyons quelles informations nous pouvons obtenir de phpMyAdmin à partir de ma mauvaise requête originale:
Si nous cliquons sur la case "Profiling" en dessous de notre requête, un nouveau monde s'ouvre avec:
phpMyAdmin fournit les temps d'exécution réels de la requête fournie. Nous pouvons maintenant voir les goulets d'étranglement d'où nos requêtes, ou même la structure au niveau de la table, devraient être adressées. Peut-être voyons-nous dans les fichiers journaux la nécessité que cette table ne soit pas vraiment écrite autant qu’elle est lue, donc au lieu d’InnoDB, nous pouvons maintenant passer à MyISAM.
Il y a un inconvénient à utiliser phpMyAdmin avec le profileur MySQL, à savoir que le profileur est basé sur la session et que phpMyAdmin détruit la session sur chaque page vue… Le problème que cela nous pose est que nous n'avons pas de moyen pour garder un total cumulé des données de profilage, mais il existe un moyen de tromper phpMyAdmin, bien que de manière kludgy:
SET profilage = 1; SELECT sales_id, sale_amount FROM tutorial.sales ORDER BY sale_amount; Profils SHOW;
Ce qui résulte en:
Étant donné que nous exécutons plusieurs requêtes, vous devez utiliser le délimiteur. Cela montrera que ma requête est query_id 1. Chaque fois que j'exécuterai cette requête, il restera toujours query_id 1 puisque ma session est détruite au démarrage. Je ne sais pas si c'est par conception, un bogue ou une ignorance de ma part, que phpMyAdmin détruit la session avec la commande QUIT, mais nous pouvons contourner ce problème un peu. MySQL a beaucoup écrit sur l'utilisation du profileur de Robin Schumacher, et je vais utiliser un peu de la requête de Robin pour obtenir le nombre d'opérations dans phpMyAdmin:
SET profilage = 1; SELECT sales_id, sale_amount FROM tutorial.sales ORDER BY sale_amount; SELECT min (seq) en tant que séquence, state, count (*) en tant qu'opérations, arrondi (somme (durée), 5) en tant que durée FROM information_schema.profiling WHERE query_id = 1 GROUP par état ORDER par seq;
Encore une fois, pas idéal avec phpMyAdmin, mais nous obtenons toujours ce que nous voulons à la fin:
Avant de rassembler tout ce que nous avons appris, examinons également comment capturer des requêtes à l'aide des fichiers journaux de MySQL. Nous pouvons capturer chaque requête exécutée par MySQL dans la table mysql.general_log. En exécutant cette commande:
SET GLOBAL general_log = 'ON'; SET GLOBAL log_output = 'TABLE';
Nous pouvons maintenant avoir un enregistrement pour toutes les requêtes exécutées, quelle que soit la source. Bien que cette opération soit coûteuse et que je ne l'utilise pas en production, elle nous fournit une méthode claire et concise pour obtenir toutes nos requêtes et leur ordre d'exécution à partir de nos applications. En bref, il s’agit peut-être de l’outil d’optimisation des requêtes SQL le plus précieux de votre boîte à outils. En définissant ces deux vars GLOBAL, nous avons la dernière étape pour obtenir des techniques d'optimisation pratiques.
Voici une sortie abrégée de la table mysql.general_log utilisant cette requête:
SELECT event_time, type_commande, argument FROM mysql.general_log ORDER BY event_time
produit ceci:
J'ai en gros ma requête, ainsi que tout ce que phpMyAdmin a fait en arrière-plan. Si je vide la table avant chaque nouvelle commande, je peux utiliser un élément sur chaque affichage de page ou un appel AJAX que je passe à partir de mes applications. Pour vider le journal, nous TRONCONS simplement la table comme ceci:
TRUNCATE mysql.general_log
Truncate est une instruction bien meilleure à utiliser ici que DELETE FROM, car l'instruction DELETE supprime ligne par ligne, tandis que TRUNCATE vide la table entière d'un coup..
Une fois que vous avez terminé votre optimisation, vous devez simplement désactiver votre journal de requêtes avec cette commande:
SET GLOBAL general_log = 'OFF';
Le journal général devient coûteux au fil du temps et ralentit certainement les performances de votre application. Je le laisse désactivé entre mes optimisations simplement pour pouvoir avoir une idée organique de la performance de ce que j'écris. Cela dit, en développement, je garde toujours le journal de requête lent activé car je veux voir mes requêtes plus lentes comme un outil d'optimisation rapide. Vous pouvez le faire facilement:
SET GLOBAL slow_query_log = 'ON'; SET GLOBAL log_queries_not_using_indexes = 'ON'; SET GLOBAL log_output = 'TABLE';
et nous pouvons vérifier cela depuis notre onglet Variables depuis notre page d'accueil:
Pour voir le résultat, il suffit de vérifier le fichier mysql.slow_log ou d'utiliser une requête comme celle-ci:
SÉLECTIONNER Sql_text FROM mysql.slow_log
Ce qui me donne les requêtes réelles qui ont été enregistrées en tant que lent:
Maintenant, nous pouvons résumer tout cela et utiliser phpMyAdmin en tant qu'outil d'optimisation de requête relativement décent. Commençons par le premier exemple de requête:
EXPLAIN SELECT sales_id, sale_amount FROM tutorial.sales ORDER BY sale_amount
Ce qui produit une sortie de:
Nous savons que nous devons avoir au moins un INDEX sur cette table. Arrêtons-nous et pensons comment cette table est utilisée. C'est une simple table de consultation qui permet de joindre une table sales_force afin de nous indiquer qu'ils ont réalisé une vente correspondant au montant enregistré. Si tout ce que nous faisons est de joindre cette table à l’adresse sales_id, c’est ce que nous devons indexer en cliquant sur le lien Détails:
Nous pouvons alors simplement définir cet index comme suit:
Notre requête initiale nous donne toujours une analyse complète, mais dans une application pratique:
SELECT sfn.first_name, sfn.last_name, s.sale_amount FROM sales_force_normalized sfn INNER JOIN sales s ON sfn.sales_id = s.sales_id
Voyons si c'est mieux:
Maintenant, nous allons quelque part. Cependant, si nous faisons quelque chose comme ceci:
SELECT max (sale_amount) FROM sales
Ensuite, nous sommes de retour dans le même bateau pour faire un scan complet de la table. Dans ce cas, nous pouvons simplement éditer l'index et ajouter le sale_amount:
Ce qui nous améliore de vraiment mal en mal:
Ou nous pouvons ajouter un nouvel index uniquement sur le montant:
Et nous avons le merveilleux résultat de:
Ce qui signifie que MySQL n’a même pas besoin d’ouvrir la table, il suffit de regarder dans l’index. Nous avons maintenant atteint le niveau optimum absolu pour cette fonction COUNT. Découvrez combien de temps il a fallu pour exécuter cette requête maintenant:
Et pour faire bonne mesure, cochez la case Profiling de la requête pour voir les éventuels goulots d'étranglement:
Nous avons joué avec des requêtes simulées et des bases de données simulées, mais mettons ce didacticiel à l'épreuve. J'ai une installation WordPress standard, avec seulement le plugin Lorem Ipsum pour ajouter environ 5 000 messages et 11 000 commentaires, afin que nous puissions mettre un peu de pression sur MySQL lorsque nous effectuons nos sélections..
Commençons par consigner à nouveau nos requêtes à partir de phpMyAdmin et tronquons également les journaux lents et généraux afin que nous puissions voir ce qui se passe lorsque nous chargeons une page à partir de WordPress:
SET GLOBAL general_log = 'ON'; TRUNCATE mysql.slow_log; TRUNCATE mysql.general_log;
General_log va contenir quelques artefacts car phpMyAdmin provoque une activité dans MySQL, mais nous devrions être en mesure de tout mettre en ordre lorsque je rechargerai ma page d'index depuis WordPress à ce stade, et si nous utilisons une condition LIKE, nous peut obtenir principalement des résultats WordPress puisque les tables sont préfixées de wp_:
SELECT event_time, type_commande, argument FROM mysql.general_log WHERE argument LIKE "% wp_%" ORDER BY event_time
Ce qui nous donne un résultat raisonnable de:
Nous savons maintenant que WordPress nous donne simplement 11 requêtes sur le chargement de la page d’index avec une jolie installation vanille. Trouvons quelque chose d'optimisé qu'ils auraient pu manquer. Si nous prenons la toute première requête exécutée à chaque chargement de WordPress:
EXPLAIN SELECT nom_option, valeur_option FROM wp_options WHERE autoload = 'yes'
Nous constatons que ceci n'est pas optimisé:
Jetons un coup d'œil à ce qu'ils ont fait via phpMyAdmin:
Nous voyons qu'il existe un index sur nom_option, mais pas d'index sur le chargement automatique, ce qui est la condition spécifiée sur la page d'index. Ajoutons-le et voyons s'il est impossible d'optimiser un peu l'installation de base de WordPress:
Puisque autoload est varchar et que "oui" ou "non", je peux limiter la valeur d'index à 1. Cela signifie maintenant que "y" ou "n" réduit encore plus notre temps. Voyons le EXPLAIN après avoir optimisé:
Nous sommes passés de très mauvais, au quatrième meilleur type. Pas mal pour quelques minutes de travail. Certes, WordPress n’étouffait pas avec cette valeur, mais en fonction de la charge de votre blog, chaque petit geste compte. Certes, les écritures prennent plus de temps, car nous devons indexer notre "y" ou "n" pour chaque ligne écrite..
Si nous allons un peu plus loin, nous pouvons également voir le profileur MySQL en action en cochant simplement la case "Profilage". Nous voyons maintenant que notre requête bourdonne vraiment:
L'optimisation n'est pas facile, ni vraiment amusante. Cependant, lorsque vous ignorez cette étape du développement, cela revient toujours à vous hanter. Je pense qu'il est relativement facile d'utiliser les outils de phpMyAdmin pour obtenir une assez bonne optimisation dans vos applications. Cela dit, de nouveaux outils sont ajoutés en permanence, tels que Jet Profiler, qui prend en temps réel ce que je viens de faire et une nature graphique..
.
Accentsconagua