Une certaine expérience dans l'optimisation des performances MySQL

Optimisez vos requêtes pour les requêtes

La plupart des serveurs MySQL ont la mise en cache des requêtes activée. C'est l'un des moyens les plus efficaces d'améliorer les performances, et il est géré par le moteur de base de données MySQL. Lorsque plusieurs requêtes identiques sont exécutées plusieurs fois, les résultats de la requête seront placés dans un cache, de sorte que les requêtes identiques suivantes n'auront pas besoin d'exploiter la table mais d'accéder directement aux résultats mis en cache.

Le principal problème ici est que pour les programmeurs, cette question est facilement négligée. Parce que certaines de nos instructions de requête empêcheront MySQL d'utiliser le cache. Veuillez consulter l'exemple suivant :

// 查询缓存不开启
$r = mysql_query("SELECT username FROM user WHERE     signup_date >= CURDATE()");
// 开启查询缓存
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

La différence entre les deux instructions SQL ci-dessus est que CURDATE() le cache de requêtes MySQL ne fonctionne pas sur cette fonction. Par conséquent, les fonctions SQL comme NOW() et RAND() ou d'autres fonctions similaires ne permettront pas la mise en cache des requêtes car les retours de ces fonctions sont volatiles. Il vous suffit donc de remplacer la fonction MySQL par une variable pour activer la mise en cache.

Apprenez à utiliser EXPLAIN

L'utilisation du mot-clé EXPLAIN peut vous permettre de savoir comment MySQL traite votre instruction SQL.

select id, title, cate from news where cate = 1

Si vous trouvez que la requête est lente, alors ajoutez un index sur le champ cate, cela accélérera la requête

Utilisez LIMIT 1 lorsqu'il n'y en a qu'un ligne de données

Lorsque vous interrogez la table et n'avez besoin que d'une seule donnée, veuillez utiliser la limite 1.

Utilisez correctement les index

Les index ne sont pas nécessairement destinés aux clés primaires ou aux champs uniques. S'il y a un champ dans votre table que vous utilisez toujours pour les recherches, les photos et les conditions, veuillez créer un index pour celui-ci.

Ne pas ORDER BY RAND()

Une requête aléatoire très inefficace.

Éviter SELECT *

Plus de données sont lues dans la base de données, plus la requête deviendra lente. De plus, si votre serveur de base de données et votre serveur WEB sont deux serveurs indépendants, cela augmentera également la charge de transmission réseau. Vous devez développer une bonne habitude de prendre tout ce dont vous avez besoin.

Utilisez ENUM au lieu de VARCHAR

Le type ENUM est très rapide et compact. En fait, il contient un TINYINT, mais il apparaît sous la forme d'une chaîne. De cette façon, il devient tout à fait parfait d'utiliser ce champ pour faire des listes de choix.

Si vous disposez d'un champ, tel que "sexe", "pays", "origine ethnique", "statut" ou "département", et que vous savez que les valeurs de ces champs sont limitées et fixes, alors vous devriez utiliser ENUM au lieu de VARCHAR.

Utilisez NOT NULL

Sauf si vous avez une raison très précise d'utiliser des valeurs NULL, vous devez toujours garder vos champs NOT NULL. Cela peut sembler un peu controversé, veuillez continuer à lire.

Tout d'abord, demandez-vous quelle est la différence entre "Vide" et "NULL" (dans le cas de INT, c'est 0 et NULL) ? Si vous pensez qu’il n’y a aucune différence entre eux, vous ne devez pas utiliser NULL. (Le saviez-vous ? Dans Oracle, les chaînes NULL et vides sont identiques !)

Ne pensez pas que NULL n'a pas besoin d'espace, il a besoin d'espace supplémentaire, et lorsque vous comparez, votre procédure sera plus compliqué. Bien sûr, cela ne signifie pas que vous ne pouvez pas utiliser NULL. La réalité est très compliquée et il y aura toujours des situations dans lesquelles vous devrez utiliser des valeurs NULL.

Ce qui suit est extrait de la propre documentation de MySQL

« Les colonnes NULL nécessitent un espace supplémentaire dans la ligne pour enregistrer si leurs valeurs sont NULL. Pour les tables MyISAM, chaque colonne NULL prend un bit supplémentaire. , arrondi à l'octet le plus proche. la forme d'une chaîne au lieu d'une adresse IP entière. Si vous utilisez un entier pour le stocker, cela ne prend que 4 octets et vous pouvez avoir des champs de longueur fixe. De plus, cela vous apportera des avantages dans les requêtes, surtout lorsque vous devez utiliser des conditions WHERE comme celle-ci : IP entre ip1 et ip2.

Nous devons utiliser UNSIGNED INT, car l'adresse IP utilisera l'intégralité de l'entier non signé de 32 bits

Une table de longueur fixe sera plus rapide

Si tous les champs d'une table sont de « longueur fixe », la table entière est considérée comme « statique » ou « de longueur fixe ». Par exemple, la table ne contient aucun champ des types suivants : VARCHAR, TEXT, BLOB. Tant que vous incluez l'un de ces champs, la table n'est plus une "table statique de longueur fixe" et le moteur MySQL la traitera d'une autre manière.

Les tables de longueur fixe amélioreront les performances car MySQL recherchera plus rapidement. Parce que ces longueurs fixes facilitent le calcul du décalage des données suivantes, la lecture sera naturellement plus rapide. Et si le champ n'est pas de longueur fixe, chaque fois que vous souhaitez trouver le suivant, le programme doit trouver la clé primaire. De plus, les tables de longueur fixe sont plus faciles à mettre en cache et à reconstruire. Cependant, le seul effet secondaire est que les champs de longueur fixe gaspillent de l'espace, car les champs de longueur fixe nécessitent beaucoup d'espace, que vous les utilisiez ou non.

Fréparation verticale

La « division verticale » est une méthode permettant de transformer les tables de la base de données en plusieurs tables par colonnes, ce qui peut réduire la complexité de la table et le nombre de champs, atteignant ainsi des objectifs d'optimisation. Il convient de noter que vous ne rejoindrez pas fréquemment les tables formées par ces champs séparés, sinon les performances seront pires que lorsqu'elles ne sont pas divisées, et ce sera une baisse extrême.

Divisez une grande instruction DELETE ou INSERT

Si vous exécutez une grande requête DELETE ou INSERT sur un site Web en ligne, vous devez être très prudent pour éviter que l'opération provoque l'ensemble de votre site Web ne répond plus. Étant donné que ces deux opérations verrouillent la table, une fois la table verrouillée, aucune autre opération ne peut y entrer.

Apache aura de nombreux processus ou threads enfants. Par conséquent, cela fonctionne assez efficacement et notre serveur ne veut pas avoir trop de processus enfants, de threads et de liens de base de données. Cela consomme beaucoup de ressources du serveur, en particulier de mémoire.

Si vous verrouillez votre table pendant un certain temps, par exemple 30 secondes, alors pour un site avec un nombre élevé de visites, les processus d'accès/threads accumulés au cours de ces 30 secondes, les liens de base de données, ouvrent le numéro de fichiers peut non seulement provoquer le crash de votre service WEB, mais également le blocage immédiat de l'ensemble de votre serveur.

Les colonnes plus petites sont plus rapides

Pour la plupart des moteurs de bases de données, les opérations sur le disque dur constituent probablement le goulot d'étranglement le plus important. Ainsi, rendre vos données compactes peut être très utile dans cette situation car cela réduit l’accès au disque dur.

Choisissez le bon moteur de stockage

Il existe deux moteurs de stockage dans MySQL, MyISAM et InnoDB, et chaque moteur a des avantages et des inconvénients.

MyISAM convient aux applications qui nécessitent un grand nombre de requêtes, mais il n'est pas très bon pour un grand nombre d'opérations d'écriture. Même si vous avez juste besoin de mettre à jour un champ, la table entière sera verrouillée et les autres processus, même le processus de lecture, ne pourront pas fonctionner tant que l'opération de lecture n'est pas terminée. De plus, MyISAM est extrêmement rapide pour les calculs tels que SELECT COUNT(*).

La tendance d'InnoDB sera un moteur de stockage très complexe, et pour certaines petites applications, il sera plus lent que MyISAM. L'autre raison est qu'il prend en charge le "verrouillage des lignes", ce sera donc mieux lorsqu'il y aura plus d'opérations d'écriture. De plus, il prend également en charge des applications plus avancées, telles que les transactions.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!