Une introduction détaillée au processus d'exécution MySQL et au cache de requêtes

Cet article vous apporte une introduction détaillée au processus d'exécution de MySQL et au cache de requêtes. J'espère qu'il vous sera utile.

MySQL exécute un processus de requête :
Lorsque nous envoyons une requête à MySQL, que fait exactement MySQL :

1. au serveur
2. Le serveur vérifie d'abord le cache des requêtes. Si le cache est atteint, il renvoie immédiatement les résultats stockés dans le cache. Sinon passez à l'étape suivante.
3. Le serveur effectue l'analyse SQL et le prétraitement, puis l'optimiseur génère le plan d'exécution correspondant.
4. MySQL appelle l'API du moteur de stockage pour exécuter la requête en fonction du plan d'exécution généré par l'optimiseur
5 Renvoie le résultat au client.

mysql est principalement composé de deux parties : la couche serveur et la couche de stockage.
La couche serveur comprend principalement des connecteurs, un cache de requêtes, des analyseurs, des optimiseurs et des exécuteurs.
La couche de stockage est principalement utilisée pour stocker et interroger des données. Les moteurs de stockage couramment utilisés incluent InnoDB, MyISAM,

(1) Protocole de communication client/serveur MySQL

Client MySQL et La communication. Le protocole entre le serveur est "half-duplex", ce qui signifie qu'à tout moment, soit le serveur envoie des données au client, soit le client envoie des données au serveur. Ces deux actions ne peuvent pas se produire en même temps. Nous ne pouvons donc pas et n’avons pas besoin de découper un message en petits morceaux et de les envoyer indépendamment.

Avantages et inconvénients :
Ce protocole rend la communication MySQL simple et rapide, mais il limite également MySQL à de nombreux endroits.
Une limitation évidente est que cela signifie aucun contrôle de flux. Une fois qu'une extrémité commence à envoyer un message, l'autre extrémité doit recevoir l'intégralité du message avant de pouvoir y répondre. C'est comme le jeu de récupération : à tout moment, une seule personne peut contrôler le ballon, et seule la personne qui contrôle le ballon peut renvoyer le ballon (envoyer un message).

(2). Connecteur

Le client et le serveur MySQL établissent une connexion et obtiennent les autorisations de l'utilisateur actuellement connecté

(3) Cache de requête
Avant d'analyser une instruction de requête, si le cache de requêtes est activé, MySQL vérifiera le cache pour voir s'il atteint les données dans le cache de requêtes. Cette vérification est implémentée via une recherche de hachage sensible à la casse. Même s'il n'y a qu'un seul octet de différence entre la requête
et la requête dans le cache, elle ne correspondra pas au résultat mis en cache. Dans ce cas, la requête
entrera dans l'étape suivante du traitement.

Si la requête en cours atteint le cache de requêtes, MySQL vérifiera les autorisations de l'utilisateur
une fois avant de renvoyer les résultats de la requête. Cela ne nécessite toujours pas d'analyser l'instruction SQL de la requête, car les informations de table auxquelles la requête actuelle doit accéder sont déjà stockées dans le cache de requêtes. S'il n'y a aucun problème avec les autorisations, MySQL ignorera toutes les autres étapes, obtiendra le résultat directement du cache et le renverra au client. Dans ce cas, la requête ne sera pas analysée, aucun plan d'exécution ne sera généré et elle ne sera pas exécutée

ps : Notez que la fonction de cache de requêtes n'est plus disponible après mysql8, car ce cache est. très facile à effacer. Le taux de réussite est relativement faible.

(3). Analyseur

Le cache n'étant pas trouvé, il est nécessaire de commencer à exécuter l'instruction sql. L'instruction sql doit être analysée avant l'exécution.

L'analyseur effectue principalement une analyse syntaxique et sémantique des instructions SQL, vérifie si les mots sont mal orthographiés et vérifie si la table ou le champ à interroger existe

(4) Optimisation des requêtes

Requête L'étape suivante du cycle de vie consiste à convertir un SQL en plan d'exécution, MySQL interagit ensuite avec le moteur de stockage selon ce

plan d'exécution. Cela comprend plusieurs sous-phases : analyse SQL, prétraitement, optimisation du plan d'exécution SQ.

Toute erreur dans ce processus (telle que des erreurs de syntaxe) peut mettre fin à la requête.

2. À propos du cache de requêtes

(1)

La méthode MySQL pour déterminer les accès au cache est très simple : le cache est stocké dans une table de référence et référencé via une valeur de hachage.

Le cache de requête MySOL enregistre les résultats complets renvoyés par la requête. Lorsque la requête atteint le cache, MySQL renvoie immédiatement les résultats, en ignorant les étapes d'analyse, d'optimisation et d'exécution.

Le système de cache de requêtes suivra chaque table impliquée dans la requête. Si ces tables changent, alors le cache de requêtes. toutes les données stockées pertinentes seront invalides.

L'efficacité de ce mécanisme semble relativement faible, car il est très probable que les résultats de la requête ne changeront pas lorsque la table de données change, mais le coût de cette implémentation simple est très faible, et c'est très important pour un système très chargé. Très important.

Le système de mise en cache des requêtes est totalement transparent pour les applications. Les applications n'ont pas besoin de se soucier de savoir si MySQL renvoie les résultats du cache de requêtes ou de l'exécution réelle. En fait, les résultats de ces deux méthodes sont exactement les mêmes. En d’autres termes, il n’est pas nécessaire d’utiliser une syntaxe pour interroger le cache. Que la mise en cache des requêtes MYSQL soit activée ou désactivée, elle est transparente pour l'application.

(2) Déterminer l'accès au cache

Lors de la détermination de l'accès au cache, MySQL n'analysera pas, ne "normalisera" pas et ne paramétrera pas les instructions de requête, mais utilisera directement les instructions SQL pour envoyer au client Autres informations originales qui apparaît comporte des caractères différents, tels que des espaces et des commentaires, et toute différence entraînera un échec du cache.

Lorsqu'il y a des données incertaines dans l'instruction de requête, elles ne seront pas mises en cache. Par exemple, les requêtes contenant la fonction NOW() ou CURRENT_DATE()
ne seront pas mises en cache.

Malentendu :
On entend souvent : "MySQL ne vérifiera pas le cache des requêtes si la requête contient une fonction non définie". Cette affirmation est incorrecte.

Comme l'instruction SQL n'a pas été analysée lors de la vérification du cache de requêtes, MySQL ne sait pas si l'instruction de requête contient une telle fonction.

Avant de vérifier le cache des requêtes, MySQL ne fait qu'une seule chose, c'est-à-dire voir si l'instruction SQL commence par 5EL via une vérification insensible à la casse.

L'instruction précise doit être : "Si l'instruction de requête contient une fonction incertaine, il est impossible de trouver le résultat mis en cache dans le cache de requête."

Remarque :
Le cache de requêtes MySQL peut améliorer les performances des requêtes dans de nombreux cas. Certains problèmes nécessitent une attention particulière lors de son utilisation. Tout d'abord, l'activation du cache de requêtes entraînera une consommation supplémentaire pour les opérations de lecture et d'écriture :

1. La requête de lecture doit d'abord vérifier si elle atteint le cache avant de démarrer
2. peut être mis en cache, puis une fois l'exécution terminée, si MySQL constate que la requête n'existe pas dans le cache de requêtes, il stockera les résultats dans le cache de requêtes, ce qui entraînera une consommation supplémentaire du système.
3. Cela aura également un impact sur les opérations d'écriture, car lors de l'écriture de données dans une table, MySQL doit invalider tous les caches de la table correspondante. Si le cache de requêtes est très volumineux ou fragmenté, cette opération peut entraîner une consommation système importante (lorsque beaucoup de mémoire est définie pour le cache de requêtes)

Si le cache de requêtes utilise une grande quantité de mémoire, le cache devenir invalide. L'opération peut devenir un goulot d'étranglement très sérieux
Si un grand nombre de résultats de requête sont stockés dans le cache, l'ensemble du système peut se bloquer pendant un certain temps pendant l'opération d'invalidation du cache

Car cette opération repose sur un verrou global Pour la protection des opérations, toutes les requêtes qui doivent effectuer cette opération doivent attendre ce verrou,
et si elle détecte si elle atteint le cache ou si elle détecte l'invalidation du cache, elle doit attendre ce verrou global.

(3) Dans quelles circonstances la mise en cache des requêtes fonctionne-t-elle ?
Théoriquement, vous pouvez déterminer si vous devez activer les requêtes en observant l'efficacité du système lorsque la mise en cache des requêtes est activée ou désactivée.

Les requêtes adverses qui consomment beaucoup de ressources sont généralement très adaptées à la mise en cache.
Par exemple, certaines requêtes de calcul récapitulatif sont spécifiques, comme COUNT(), etc. De manière générale, la mise en cache des requêtes peut être utilisée pour les instructions SELECT complexes.
Par exemple, le tri et la pagination sont requis après une JOIN multi-tables. Ce type de requête consomme beaucoup d'argent à chaque exécution, mais l'ensemble de résultats est renvoyé. est très petit, très approprié pour la mise en cache des requêtes.

Cependant, il convient de noter qu'il y a très peu d'opérations UPDATE, DELETE et INSERT sur les tables impliquées par rapport à SELECT.

La donnée directe permettant de juger si le cache de requêtes est efficace est le taux de réussite. Il s'agit du rapport entre les résultats renvoyés à l'aide du cache de requêtes et la requête totale

Cependant, le taux de réussite du cache est une valeur difficile à juger. Qu’est-ce qu’un bon taux de réussite ? Situation spécifique, analyse spécifique.

Tant que l'amélioration de l'efficacité apportée par la mise en cache des requêtes est supérieure à la consommation supplémentaire causée par la mise en cache des requêtes, même un taux de réussite de 30 % sera très bénéfique pour les performances du système. De plus, les requêtes mises en cache sont également très importantes. Par exemple, la requête mise en cache elle-même consomme beaucoup d'argent, donc même si le taux de réussite du cache est très faible, cela sera toujours bénéfique pour les performances du système

Les échecs de cache peuvent être les suivants Plusieurs raisons :

1. L'instruction de requête ne peut pas être mise en cache, peut-être parce que la requête contient une fonction incertaine (telle que CURREN_DATE) ou que le résultat de la requête est trop volumineux pour être mis en cache. Cela entraînera une augmentation de la valeur d'état Cache non mis en cache.
2.MySQL n'a jamais traité cette requête, les résultats n'ont donc jamais été mis en cache.

3. Une autre situation est que, même si les résultats de la requête ont déjà été mis en cache, parce que la mémoire du cache de requête est épuisée, MySQL doit "expulser" certains caches, ou le cache devient invalide car la table de données est modifié.

S'il y a un grand nombre d'échecs de cache sur votre serveur, mais qu'en fait la grande majorité des requêtes sont mises en cache, alors ce qui suit doit se produire :

1. encore terminé Réchauffez-vous. En d’autres termes, MySQL n’a pas eu la possibilité de mettre en cache tous les résultats des requêtes.
2. L'instruction de requête n'a jamais été exécutée auparavant. Si votre application n'exécute pas une instruction de requête à plusieurs reprises, il y aura toujours de nombreux échecs de cache même une fois le préchauffage terminé
3. Trop d'opérations d'invalidation du cache.

(4) Comment configurer et maintenir le cache de requêtes

query_cache_type

S'il faut activer le cache de requêtes. Peut être réglé sur 0FN ou DEMAND. DEMAND signifie que seules les instructions qui indiquent clairement SQL_CACHE dans l'instruction de requête sont placées dans le cache de requête. Cette variable peut être au niveau de la session ou au niveau global

query_cache_size

L'espace mémoire total utilisé par le cache de requêtes, en octets. Cette valeur doit être un multiple entier de 1024, sinon les données réelles allouées par MySQL seront légèrement différentes de celles que vous avez spécifiées.

query_cahce_min_res_unit

L'unité minimale lors de l'allocation de blocs de mémoire dans le cache de requêtes.

query_chache_limit

Le résultat de requête maximum que MySQL peut mettre en cache. Si le résultat de la requête est supérieur à cette valeur, il ne sera pas mis en cache. Étant donné que le cache de requête commence à essayer de mettre en cache les données lorsque les données sont générées, ce n'est que lorsque tous les résultats seront renvoyés qu'il saura si les résultats de la requête dépassent la limite

Si elle dépasse, MySQL augmentera la valeur d'état Cache_not_cached et will Les résultats sont supprimés du cache de requête. Si vous savez à l'avance que de nombreuses situations de ce type se produisent, il est recommandé d'ajouter

(5) alternative

à l'instruction de requête. .Le principe du travail du cache de requêtes MySQL est le suivant : exécution. Le moyen le plus rapide d'interroger n'est pas de l'exécuter, mais la requête doit toujours être envoyée au serveur, et le serveur doit encore faire un peu de travail. Que se passerait-il s'il n'était pas nécessaire de communiquer avec le serveur pour certaines requêtes ? A ce moment, le cache du client peut vous aider dans une large mesure à partager la pression sur le serveur MySQL

Résumé :

Exactement pareil Lorsqu'une requête est exécutée à plusieurs reprises, le cache de requêtes peut renvoyer les résultats immédiatement sans avoir à la réexécuter dans la base de données. D'après notre expérience, interroger le cache dans un environnement à forte pression de concurrence entraînera une diminution des performances du système, voire un gel.

Si vous devez utiliser le cache de requêtes, ne définissez pas trop de mémoire et ne l'utilisez que lorsque les avantages sont confirmés.

Comment juger si le cache de requêtes doit être utilisé ? Il est recommandé d'utiliser le serveur Percona, d'observer des journaux plus détaillés et d'effectuer quelques calculs simples. Vous pouvez également consulter le taux d'accès au cache (pas toujours utile), le « rapport NSERTS sur SELECT » (ce paramètre n'est pas non plus intuitif) ou le « rapport accès à l'écriture » ​​(cette référence est plus significative).

Le cache de requêtes est un cache très pratique qui est totalement transparent pour l'application et ne nécessite aucun codage supplémentaire. Cependant, si vous souhaitez une plus grande efficacité de mise en cache, nous vous recommandons d'utiliser le cache ou d'autres solutions similaires.

[Recommandations associées : Tutoriel vidéo MySQL]

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!