Share
Les opérations de base de données tendent souvent à constituer le principal goulot d'étranglement pour la plupart des applications Web aujourd'hui. Ce ne sont pas uniquement les administrateurs de base de données (administrateurs de bases de données) qui doivent se préoccuper de ces problèmes de performances. En tant que programmeurs, nous devons faire notre part en structurant correctement les tables, en écrivant des requêtes optimisées et en améliorant le code. Dans cet article, je vais énumérer quelques techniques d'optimisation MySQL pour les programmeurs..
Avant de commencer, sachez que vous pouvez trouver une tonne de scripts et d’utilitaires MySQL utiles sur Envato Market..
Scripts et utilitaires MySQL sur Envato Market La mise en cache des requêtes est activée sur la plupart des serveurs MySQL. C'est l'une des méthodes les plus efficaces d'amélioration des performances, gérée de manière discrète par le moteur de base de données. Lorsque la même requête est exécutée plusieurs fois, le résultat est extrait du cache, ce qui est assez rapide..
Le problème principal est qu’il est si facile et caché par le programmeur que la plupart d’entre nous ont tendance à l’ignorer. Certaines choses que nous faisons peuvent en réalité empêcher le cache de requêtes d’exécuter sa tâche..
Accentsconagua
// le cache d'interrogation ne fonctionne PAS $ r = mysql_query ("SELECT nom d'utilisateur DE L'utilisateur WHERE date_abonnement> = CURDATE ()"); // le cache de requête fonctionne! $ aujourd'hui = date ("Y-m-d"); $ r = mysql_query ("SELECT le nom d'utilisateur de l'utilisateur WHERE date_abonnement> = '$ aujourd'hui'"); La raison pour laquelle le cache de requête ne fonctionne pas à la première ligne est l'utilisation de la fonction CURDATE (). Cela s'applique à toutes les fonctions non déterministes telles que NOW () et RAND (), etc. Comme le résultat renvoyé par la fonction peut changer, MySQL décide de désactiver la mise en cache de la requête pour cette requête. Tout ce que nous avions à faire, c'était d'ajouter une ligne supplémentaire de PHP avant la requête pour éviter que cela ne se produise..
L'utilisation du mot clé EXPLAIN peut vous aider à comprendre ce que fait MySQL pour exécuter votre requête. Cela peut vous aider à identifier les goulots d'étranglement et autres problèmes liés à la structure de votre requête ou de votre table..
Les résultats d'une requête EXPLAIN vous indiqueront quels index sont utilisés, comment la table est analysée et triée, etc.
Prenez une requête SELECT (de préférence complexe, avec des jointures) et ajoutez le mot clé EXPLAIN devant elle. Vous pouvez simplement utiliser phpmyadmin pour cela. Il vous montrera les résultats dans un joli tableau. Par exemple, supposons que j'ai oublié d'ajouter un index à une colonne, sur laquelle j'effectue des jointures:
Après avoir ajouté l'index au champ group_id:
Au lieu d'analyser 7883 lignes, il numérisera seulement 9 et 16 lignes des 2 tables. Une bonne règle consiste à multiplier tous les nombres dans la colonne "lignes", et les performances de votre requête seront quelque peu proportionnelles au nombre obtenu..
Parfois, lorsque vous interrogez vos tables, vous savez déjà que vous ne recherchez qu'une seule ligne. Vous êtes peut-être en train de récupérer un enregistrement unique ou vous vous contentez simplement de vérifier l'existence d'un nombre quelconque d'enregistrements satisfaisant votre clause WHERE..
Dans de tels cas, l'ajout de LIMIT 1 à votre requête peut améliorer les performances. De cette façon, le moteur de base de données arrêtera d'analyser les enregistrements après avoir trouvé 1, au lieu de parcourir l'intégralité de la table ou de l'index..
// ai-je des utilisateurs de l'Alabama? // ce qu'il ne faut PAS faire: $ r = mysql_query ("SELECT * DE l'utilisateur WHERE state = 'Alabama'"); if (mysql_num_rows ($ r)> 0) //… // beaucoup mieux: $ r = mysql_query ("SELECT 1 de l'utilisateur WHERE state = 'Alabama' LIMIT 1"); if (mysql_num_rows ($ r)> 0) //… Les index ne concernent pas uniquement les clés primaires ou les clés uniques. Si vous recherchez des colonnes dans votre table, vous devez presque toujours les indexer..
Comme vous pouvez le constater, cette règle s’applique également à une recherche de chaîne partielle telle que "nom_de_LIEN". Lors de la recherche depuis le début de la chaîne, MySQL peut utiliser l'index de cette colonne..
Vous devez également comprendre quels types de recherche ne peuvent pas utiliser les index réguliers. Par exemple, lorsque vous recherchez un mot (par exemple, "WHERE post_content LIKE '% apple%'"), vous ne verrez pas l'avantage d'un index normal. Vous ferez mieux d'utiliser mysql fulltext search ou de créer votre propre solution d'indexation.
Si votre application contient de nombreuses requêtes JOIN, vous devez vous assurer que les colonnes que vous joignez sont indexées sur les deux tables. Cela affecte la manière dont MySQL optimise en interne l'opération de jointure..
En outre, les colonnes jointes doivent être du même type. Par exemple, si vous joignez une colonne DECIMAL à une colonne INT d'une autre table, MySQL ne pourra pas utiliser au moins un des index. Même les codages de caractères doivent être du même type pour les colonnes de type chaîne.
// recherche d'entreprises dans mon état $ r = mysql_query ("SELECT nom_entreprise provenant d'utilisateurs gauche LEFT JOIN sociétés ON (utilisateurs.state = entreprises.state) WHERE utilisateurs.id = $ id_utilisateur"); // les deux colonnes d'état doivent être indexées // et le même type et le même codage de caractères // ou MySQL peut effectuer des analyses complètes des tables C’est un de ces trucs qui sonnent bien au début, et de nombreux programmeurs débutants craignent pour ce piège. Vous ne réalisez peut-être pas le type de goulet d’étranglement que vous pouvez créer une fois que vous commencez à l’utiliser dans vos requêtes..
Si vous avez vraiment besoin de lignes aléatoires dans vos résultats, il existe de bien meilleures façons de le faire. Cela prend du code supplémentaire, mais vous éviterez un goulot d'étranglement qui s'aggrave de façon exponentielle avec la croissance de vos données. Le problème est que MySQL devra effectuer l'opération RAND () (qui nécessite beaucoup de puissance de traitement) pour chaque ligne de la table avant de la trier et de ne vous donner qu'une ligne..
// ce qu'il ne faut PAS faire: $ r = mysql_query ("SELECT le nom d'utilisateur de l'utilisateur ORDER BY RAND () LIMIT 1"); // beaucoup mieux: $ r = mysql_query ("SELECT count (*) FROM user"); $ d = mysql_fetch_row ($ r); $ rand = mt_rand (0, $ d [0] - 1); $ r = mysql_query ("SELECT le nom d'utilisateur DE l'utilisateur LIMIT $ rand, 1"); Donc, vous choisissez un nombre aléatoire inférieur au nombre de résultats et vous l'utilisez comme décalage dans votre clause LIMIT.
Plus les données extraites des tables sont lues, plus la requête sera lente. Cela augmente le temps nécessaire aux opérations sur le disque. De même, lorsque le serveur de base de données est séparé du serveur Web, les retards sur le réseau sont plus longs en raison du transfert de données entre les serveurs..
C'est une bonne habitude de toujours spécifier les colonnes dont vous avez besoin lorsque vous effectuez vos sélections..
// pas préféré $ r = mysql_query ("SELECT * FROM utilisateur WHERE user_id = 1"); $ d = mysql_fetch_assoc ($ r); echo "Bienvenue $ d ['nom d'utilisateur']"; // mieux: $ r = mysql_query ("SELECT nom d'utilisateur DE l'utilisateur WHERE user_id = 1"); $ d = mysql_fetch_assoc ($ r); echo "Bienvenue $ d ['nom d'utilisateur']"; // les différences sont plus significatives avec des ensembles de résultats plus importants Dans chaque table, une colonne id contenant les clés PRIMARY KEY, AUTO_INCREMENT et l’une des variantes de INT. Également de préférence UNSIGNED, puisque la valeur ne peut pas être négative.
Même si vous avez une table d'utilisateurs ayant un champ nom d'utilisateur unique, n'en faites pas votre clé primaire. Les champs VARCHAR en tant que clés primaires sont plus lents. Et vous aurez une meilleure structure dans votre code en faisant référence à tous les utilisateurs avec leur identifiant en interne.
Il existe également des opérations en arrière-plan effectuées par le moteur MySQL lui-même, qui utilise le champ de clé primaire en interne. Plus cela devient important, plus la configuration de la base de données est compliquée. (clusters, partitioning etc…).
Les "tables d'association", utilisées pour les associations de type plusieurs à plusieurs entre deux tables, constituent une exception à la règle. Par exemple, une table "posts_tags" contenant 2 colonnes: post_id, tag_id, utilisée pour les relations entre deux tables nommées "post" et "tags". Ces tables peuvent avoir une clé PRIMARY contenant les deux champs id.
Les colonnes de type ENUM sont très rapides et compactes. En interne, ils sont stockés comme TINYINT, mais ils peuvent contenir et afficher des valeurs de chaîne. Cela les rend un candidat idéal pour certains domaines.
Si vous avez un champ qui ne contiendra que quelques types de valeurs différents, utilisez ENUM au lieu de VARCHAR. Par exemple, il peut s'agir d'une colonne nommée "status" et ne contenir que des valeurs telles que "actif", "inactif", "en attente", "expiré", etc.
Il existe même un moyen d'obtenir de MySQL une "suggestion" sur la restructuration de votre table. Lorsque vous avez un champ VARCHAR, il peut vous suggérer de remplacer le type de colonne par ENUM. Ceci est fait en utilisant l'appel PROCEDURE ANALYZE (). Ce qui nous amène à:
PROCEDURE ANALYZE () laissera MySQL analyser les structures de colonnes et les données réelles de votre table afin de vous proposer certaines suggestions. Ce n'est utile que s'il y a des données réelles dans vos tables, car elles jouent un rôle important dans la prise de décision..
Par exemple, si vous avez créé un champ INT pour votre clé primaire, mais que vous n’avez pas trop de lignes, il peut vous suggérer d’utiliser plutôt un MEDIUMINT. Ou, si vous utilisez un champ VARCHAR, vous pourriez avoir la suggestion de le convertir en ENUM, s’il n’ya que quelques valeurs uniques..
Vous pouvez également exécuter ceci en cliquant sur le lien "Proposer une structure de table" dans phpmyadmin, dans l'une de vos vues de table..
Gardez à l'esprit que ce ne sont que des suggestions. Et si votre table s'agrandit, il se peut qu'elles ne soient même pas les bonnes suggestions à suivre. La decision finale est à toi.
Sauf si vous avez une raison très spécifique d'utiliser une valeur NULL, vous devez toujours définir vos colonnes comme NOT NULL..
Tout d'abord, demandez-vous s'il existe une différence entre une valeur de chaîne vide et une valeur NULL (pour les champs INT: 0 par rapport à NULL). S'il n'y a aucune raison d'avoir les deux, vous n'avez pas besoin d'un champ NULL. (Saviez-vous qu'Oracle considère que NULL et une chaîne vide sont identiques?)
Les colonnes NULL nécessitent un espace supplémentaire et peuvent ajouter de la complexité à vos instructions de comparaison. Il suffit de les éviter quand vous le pouvez. Cependant, je comprends que certaines personnes peuvent avoir des raisons très spécifiques d’avoir des valeurs NULL, ce qui n’est pas toujours une mauvaise chose..
De la documentation MySQL:
"Les colonnes NULL nécessitent un espace supplémentaire dans la ligne pour indiquer si leurs valeurs sont NULL. Pour les tables MyISAM, chaque colonne NULL nécessite un bit supplémentaire, arrondi à l'octet le plus proche."
L'utilisation des instructions préparées présente de nombreux avantages, pour des raisons de performances et de sécurité..
Les instructions préparées filtreront les variables que vous leur liez par défaut, ce qui est idéal pour protéger votre application contre les attaques par injection SQL. Vous pouvez bien sûr filtrer vos variables manuellement aussi, mais ces méthodes sont plus sujettes aux erreurs humaines et à l’oubli du programmeur. C'est moins un problème lorsque vous utilisez un type de framework ou ORM.
Puisque nous nous concentrons sur la performance, je devrais également mentionner les avantages dans ce domaine. Ces avantages sont plus importants lorsque la même requête est utilisée plusieurs fois dans votre application. Vous pouvez assigner différentes valeurs à la même instruction préparée, mais MySQL n’aura qu’à l’analyser une fois..
De plus, les dernières versions de MySQL transmettent les instructions préparées sous une forme binaire native, qui sont plus efficaces et peuvent également contribuer à réduire les délais réseau..
Il fut un temps où de nombreux programmeurs évitaient délibérément les déclarations préparées, pour une raison importante. Ils n'étaient pas mis en cache par le cache de requêtes MySQL. Mais depuis la version 5.1, la mise en cache des requêtes est également prise en charge..
Pour utiliser des instructions préparées en PHP, vous devez extraire l'extension mysqli ou utiliser une couche d'abstraction de base de données telle que PDO..
// crée une instruction préparée if ($ stmt = $ mysqli-> prepare ("SELECT nom d'utilisateur FROM utilisateur WHERE state =?")) // paramètres de liaison $ stmt-> bind_param ("s", $ state); // execute $ stmt-> execute (); // Lier les variables de résultat $ stmt-> bind_result ($ username); // valeur de récupération $ stmt-> fetch (); printf ("% s provient de% s \ n", $ username, $ state); $ stmt-> close (); Normalement, lorsque vous effectuez une requête à partir d'un script, il attend que l'exécution de cette requête se termine avant de pouvoir continuer. Vous pouvez changer cela en utilisant des requêtes sans mémoire tampon.
Il existe une bonne explication dans la documentation PHP pour la fonction mysql_unbuffered_query ():
"mysql_unbuffered_query () envoie la requête SQL à MySQL sans récupérer ni mettre en mémoire tampon les résultats, comme le fait mysql_query (). Cela permet d'économiser une quantité de mémoire considérable avec les requêtes SQL produisant de grands ensembles de résultats. Vous pouvez ainsi commencer à travailler sur l'ensemble de résultats. immédiatement après l'extraction de la première ligne car vous n'avez pas à attendre que la requête SQL complète ait été effectuée. "
Cependant, il vient avec certaines limitations. Vous devez lire toutes les lignes ou appeler mysql_free_result () avant de pouvoir effectuer une autre requête. De plus, vous n'êtes pas autorisé à utiliser mysql_num_rows () ou mysql_data_seek () sur le jeu de résultats.
De nombreux programmeurs créeront un champ VARCHAR (15) sans se rendre compte qu'ils peuvent réellement stocker les adresses IP sous forme de valeurs entières. Avec un INT, vous ne réduisez que 4 octets et vous avez un champ de taille fixe..
Vous devez vous assurer que votre colonne est un UNSIGNED INT, car les adresses IP utilisent toute la plage d'un entier non signé de 32 bits..
Dans vos requêtes, vous pouvez utiliser INET_ATON () pour convertir et IP en entier, et INET_NTOA () pour inversement. Il existe également des fonctions similaires en PHP appelées ip2long () et long2ip ().
$ r = "UPDATE utilisateurs SET ip = INET_ATON ('$ _ SERVEUR' 'REMOTE_ADDR'] ') WHERE id_utilisateur = $ id_utilisateur"; Lorsque chaque colonne d'une table est "à longueur fixe", la table est également considérée comme "statique" ou "à longueur fixe". Des exemples de types de colonne qui ne sont PAS de longueur fixe sont: VARCHAR, TEXT, BLOB. Si vous n'incluez qu'un seul de ces types de colonnes, la table cesse d'être de longueur fixe et doit être gérée différemment par le moteur MySQL..
Les tables de longueur fixe peuvent améliorer les performances car le moteur MySQL recherche plus rapidement dans les enregistrements. Lorsqu'il souhaite lire une ligne spécifique dans un tableau, il peut en calculer rapidement la position. Si la taille de la ligne n'est pas fixe, chaque fois qu'il doit effectuer une recherche, il doit consulter l'index de clé primaire..
Ils sont également plus faciles à mettre en cache et à reconstruire après un crash. Mais ils peuvent aussi prendre plus de place. Par exemple, si vous convertissez un champ VARCHAR (20) en un champ CHAR (20), il prendra toujours 20 octets d’espace, quelle que soit sa nature..
En utilisant des techniques de "partitionnement vertical", vous pouvez séparer les colonnes de longueur variable en un tableau séparé. Ce qui nous amène à:
Le partitionnement vertical consiste à scinder verticalement la structure de votre table pour des raisons d'optimisation..
Exemple 1: Vous pouvez avoir une table d'utilisateurs contenant des adresses de base, qui ne sont pas lues souvent. Vous pouvez choisir de diviser votre table et de stocker les informations d'adresse sur une table séparée. De cette façon, la taille de votre table d'utilisateurs principale sera réduite. Comme vous le savez, les petites tables fonctionnent plus rapidement.
Exemple 2: Vous avez un champ "last_login" dans votre table. Il est mis à jour chaque fois qu'un utilisateur se connecte au site Web. Mais chaque mise à jour sur une table entraîne le vidage du cache de requêtes de cette table. Vous pouvez mettre ce champ dans une autre table pour limiter au minimum les mises à jour de votre table d'utilisateurs.
Mais vous devez également vous assurer que vous n'avez pas constamment besoin de joindre ces 2 tables après le partitionnement, sinon vous risqueriez de perdre des performances..
Si vous devez exécuter une requête DELETE ou INSERT volumineuse sur un site Web actif, veillez à ne pas perturber le trafic Web. Lorsqu'une telle requête est exécutée, cela peut verrouiller vos tables et interrompre votre application Web..
Apache exécute de nombreux processus / threads parallèles. Par conséquent, cela fonctionne plus efficacement lorsque les scripts ont fini de s'exécuter le plus rapidement possible, afin que les serveurs ne subissent pas trop de connexions ouvertes et de processus en même temps qui consomment des ressources, en particulier de la mémoire..
Si vous verrouillez vos tables pendant une période prolongée (environ 30 secondes ou plus) sur un site Web à fort trafic, vous créez un processus et une pile de requêtes, ce qui peut prendre un certain temps pour effacer ou même mettre en panne votre site Web. serveur.
Si vous avez une sorte de script de maintenance qui doit supprimer un grand nombre de lignes, utilisez simplement la clause LIMIT pour le faire en petits lots afin d'éviter cet encombrement..
while (1) mysql_query ("DELETE FROM les journaux WHERE log_date <= '2009-10-01' LIMIT 10000"); if (mysql_affected_rows() == 0) // done deleting break; // you can even pause a bit usleep(50000); Avec les moteurs de base de données, le disque est peut-être le goulet d'étranglement le plus important. Garder les choses plus petites et plus compactes est généralement utile en termes de performances, pour réduire la quantité de transfert de disque..
Les documents MySQL ont une liste d'exigences de stockage pour tous les types de données..
Si une table ne doit comporter que très peu de lignes, il n'y a aucune raison de faire de la clé primaire un INT, au lieu de MEDIUMINT, SMALLINT ou même, dans certains cas, TINYINT. Si vous n'avez pas besoin du composant heure, utilisez DATE au lieu de DATETIME..
Assurez-vous simplement de laisser une marge de croissance raisonnable, sinon vous pourriez vous retrouver comme Slashdot..
Les deux principaux moteurs de stockage dans MySQL sont MyISAM et InnoDB. Chacun a ses avantages et ses inconvénients.
MyISAM convient aux applications nécessitant beaucoup de lecture, mais il ne s'adapte pas très bien lorsqu'il y a beaucoup d'écritures. Même si vous mettez à jour un champ d'une ligne, toute la table est verrouillée et aucun autre processus ne peut même la lire tant que cette requête n'est pas terminée. MyISAM est très rapide dans le calcul des types de requêtes SELECT COUNT (*).
InnoDB a tendance à être un moteur de stockage plus compliqué et peut être plus lent que MyISAM pour la plupart des petites applications. Mais il prend en charge le verrouillage basé sur les lignes, qui évolue mieux. Il prend également en charge certaines fonctionnalités plus avancées telles que les transactions.
En utilisant un ORM (Object Relational Mapper), vous pouvez obtenir certains avantages en termes de performances. Tout ce qu'un ORM peut faire peut également être codé manuellement. Mais cela peut signifier trop de travail supplémentaire et nécessiter un haut niveau d'expertise.
Les ORM sont parfaits pour "Chargement paresseux". Cela signifie qu'ils ne peuvent récupérer les valeurs que si elles sont nécessaires. Mais vous devez être prudent avec eux ou vous pouvez créer de nombreuses mini-requêtes pouvant réduire les performances..
Les ORM peuvent également regrouper vos requêtes en transactions, qui fonctionnent beaucoup plus rapidement que l'envoi de requêtes individuelles à la base de données..
Actuellement, mon ORM préféré pour PHP est Doctrine. J'ai écrit un article sur la façon d'installer Doctrine avec CodeIgniter.
Les connexions persistantes ont pour but de réduire la charge liée à la recréation de connexions à MySQL. Lorsqu'une connexion persistante est créée, elle reste ouverte même après l'exécution du script. Comme Apache réutilise ses processus enfants, lors de la prochaine exécution du processus pour un nouveau script, il réutilisera la même connexion MySQL..
Cela sonne bien en théorie. Mais de mon expérience personnelle (et de beaucoup d'autres), cette fonctionnalité s'avère ne vaut pas la peine. Vous pouvez avoir de sérieux problèmes avec les limites de connexion, les problèmes de mémoire, etc..
Apache est extrêmement parallèle et crée de nombreux processus enfants. C'est la raison principale pour laquelle les connexions persistantes ne fonctionnent pas très bien dans cet environnement. Avant d’envisager d’utiliser la fonction mysql_pconnect (), consultez votre administrateur système..
