Analyse du processus d'exécution de MySQL

MySQL peut être divisé en deux parties : la couche Serveur et la couche moteur de stockage

La couche Serveur comprend connecteurs et cache de requêtes, analyseurs, optimiseurs, exécuteurs, etc., couvrant la plupart des fonctions de base du service MySQL et toutes les fonctions intégrées, toutes les fonctions du moteur de stockage croisé sont implémentées dans cette couche, telles que les procédures stockées, les déclencheurs, les vues, etc. .

La couche moteur de stockage est responsable du stockage et de la récupération des données. Son modèle architectural est plug-in et prend en charge plusieurs moteurs de stockage tels que InnoDB, MyISAM et Memory. Le moteur de stockage le plus couramment utilisé est désormais InnoDB

(tutoriel vidéo d'apprentissage gratuit recommandé : tutoriel vidéo MySQL)

Jetons un coup d'œil au processus d'exécution SQL

Connecteur

Dans un premier temps, vous vous connecterez d'abord à la base de données, et le connecteur vous recevra à ce moment-là. Le connecteur est chargé d'établir une connexion avec le client, d'obtenir les autorisations, de maintenir et de gérer la connexion

Une fois la connexion terminée, si vous n'avez aucune action ultérieure, la connexion sera dans un état inactif. voyez-le dans la commande show processlist

Si le client est inactif pendant trop longtemps, le connecteur le déconnectera automatiquement. Ce temps est contrôlé par le paramètre wait_timeout. La valeur par défaut est de 8 heures

Le processus d'établissement d'une connexion est généralement compliqué, donc les actions d'établissement d'une connexion doivent être minimisées pendant l'utilisation, c'est-à-dire essayer d'utiliser connexions longues

Mais après avoir utilisé toutes les connexions longues, vous constaterez peut-être que parfois la mémoire occupée par MySQL augmente très rapidement. C'est parce que la mémoire temporairement utilisée par MySQL pendant l'exécution est gérée dans l'objet de connexion. Ces ressources ne seront libérées que lorsque la connexion sera déconnectée

Comment résoudre ce problème Vous pouvez envisager les deux options suivantes ?

1. Déconnectez régulièrement les longues connexions. Après l'avoir utilisé pendant un certain temps, ou après que le programme a déterminé qu'une requête volumineuse occupant de la mémoire a été exécutée, la connexion est déconnectée, puis la requête est requise puis reconnectée.

2. Si vous utilisez MySQL 5.7 ou une version plus récente, vous pouvez réinitialiser la ressource de connexion en exécutant mysql_reset_connection à chaque fois après avoir effectué une opération relativement importante. Ce processus ne nécessite pas de reconnexion ni de vérification des autorisations, mais la connexion sera restaurée à l'état où elle vient d'être créée

Cache de requêtes

Une fois la connexion établie, vous Vous pouvez exécuter l'instruction select. La logique d'exécution passera à la deuxième étape : le cache des requêtes. Une fois que MySQL a reçu une requête de requête, il ira d'abord dans le cache de requêtes pour voir si cette instruction a déjà été exécutée. Les instructions précédemment exécutées et leurs résultats peuvent être mis en cache directement en mémoire sous la forme de paires clé-valeur. La clé est l'instruction de requête et la valeur est le résultat de la requête. Si votre requête peut trouver directement la clé dans ce cache, alors la valeur sera renvoyée directement au client

Si l'instruction n'est pas dans le cache de requête, la phase d'exécution suivante se poursuivra. Une fois l'exécution terminée, les résultats de l'exécution seront stockés dans le cache des requêtes. Vous pouvez voir que si la requête atteint le cache, MySQL peut renvoyer directement le résultat sans effectuer d'opérations complexes ultérieures.

Mais dans la plupart des cas, n'utilisez pas le cache de requêtes. Pourquoi ? La mise en cache des requêtes fait souvent plus de mal que de bien.

Le cache de requêtes expire très fréquemment. Tant qu'il y a une mise à jour d'une table, tous les caches de requêtes sur cette table seront effacés. Il est donc possible que vous ayez pris la peine de sauvegarder les résultats et qu'avant même de les utiliser, ils aient été effacés par une mise à jour. Pour les bases de données soumises à une forte pression de mise à jour, le taux de réussite du cache de requêtes sera très faible. Sauf si votre entreprise dispose d'une table statique qui sera mise à jour pendant une longue période

Vous pouvez définir le paramètre query_cache_type sur DEMAND, afin que le cache de requêtes ne soit pas utilisé pour les instructions SQL par défaut

MySQL version 8.0 supprime directement toute la fonction de cache de requêtes, ce qui signifie que cette fonction est complètement absente de 8.0

Analyzer

Si le cache de requêtes est n'est pas atteint, l'exécution proprement dite de l'instruction commence. Tout d'abord, MySQL doit savoir ce que vous voulez faire, il doit donc analyser l'instruction SQL

L'analyseur fera d'abord une "analyse lexicale". Ce que vous saisissez est une instruction SQL composée de plusieurs chaînes et espaces. MySQL doit identifier quelles sont les chaînes qu'elle contient et ce qu'elles représentent

Après avoir terminé ces identifications, il doit effectuer une "analyse syntaxique". Selon les résultats de l'analyse lexicale, l'analyseur de syntaxe déterminera si l'instruction SQL que vous avez saisie satisfait à la syntaxe MySQL

selon les règles grammaticales de l'optimiseur

. passé par l'analyseur, MySQL sait ce que vous voulez faire. Avant le début de l'exécution, il doit être traité par l'optimiseur.

L'optimiseur détermine quel index utiliser lorsqu'il y a plusieurs index dans la table ; ou lorsqu'une instruction a plusieurs associations de tables (jointures), il détermine l'ordre de connexion de chaque table

Après le la phase d'optimisation est terminée, le plan d'exécution de cette instruction est déterminé, puis entre dans la phase d'exécution

exécuteur

Lors du démarrage de l'exécution, vous devez d'abord déterminer si vous avez l'autorisation d'exécuter des requêtes sur cette table T. Sinon, une erreur d'absence d'autorisation sera renvoyée

Si vous avez l'autorisation, ouvrez la table et. poursuivre l'exécution. Lorsque la table est ouverte, l'exécuteur utilisera l'interface fournie par le moteur pour exécuter selon la définition du moteur de la table

À ce stade, la couche serveur a terminé l'exécution de la logique spécifique de la couche moteur. analysez-le dans le prochain article

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!