2024-07-12
한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina
Les étapes permettant à la couche serveur d'exécuter SQL dans l'ordre sont :
Demande du client -> Connecteur (vérifier l'identité de l'utilisateur et accorder les autorisations) Cache de requête (retourner directement si le cache existe, effectuer les opérations ultérieures sinon) Analyseur (effectuer une analyse lexicale et une analyse syntaxique de SQL) Optimiseur (effectuer principalement l'exécution de la méthode d'optimisation SQL pour sélectionner le plan d'exécution optimal) Exécuteur (lors de l'exécution, il vérifiera d'abord si l'utilisateur dispose de l'autorisation d'exécution, puis utilisera l'interface fournie par ce moteur) -> Accédez à la couche moteur pour obtenir le retour des données (si le cache de requêtes est activé, il mettra en cache les résultats de la requête)
Le pool de tampons est une partie importante du moteur de stockage InnoDB dans la base de données MySQL. Il est principalement utilisé pour mettre en cache les données de table et les données d'index afin de réduire les opérations d'E/S sur disque et d'améliorer l'efficacité du traitement de la base de données. Voici une analyse détaillée du pool de tampons :
définition: Buffer Pool est une zone mémoire du moteur de stockage InnoDB, utilisée pour mettre en cache les pages de données et les pages d'index sur le disque afin de réduire l'accès direct au disque.
effet: Améliorez la vitesse d’accès aux données et réduisez les coûts d’E/S disque grâce au mécanisme de mise en cache.
composition : Buffer Pool se compose de pages de données mises en cache (Page) et de blocs de contrôle correspondants. Le bloc de contrôle stocke les informations de métadonnées de la page de cache, telles que l'espace table auquel elle appartient, le numéro de la page de données, l'adresse de la page de cache dans le pool de tampons, etc.
taille par défaut: La taille par défaut du pool de tampons dans MySQL est généralement de 128 Mo (mais veuillez noter que différentes versions de MySQL ou différentes configurations peuvent entraîner une taille par défaut différente).
Paramètres de configuration:passerinnodb_buffer_pool_sizeLes paramètres peuvent configurer la taille du pool de tampons. Il est généralement recommandé de le définir entre 60 et 80 % de la mémoire système.
allocation de mémoire: Buffer Pool est un espace mémoire continu Lorsque MySQL s'exécute pendant un certain temps, il y aura à la fois des pages de cache libres et des pages de cache utilisées dans cet espace mémoire.
taper
: Les pages de données du Buffer Pool peuvent être divisées en trois types selon leur statut : Page libre, Page propre et Page sale.
Pages gratuites : pages de cache qui ne sont pas utilisées.
Page propre : Une page de cache qui a été utilisée mais les données n'ont pas été modifiées.
Page sale : une page de cache qui a été utilisée et les données ont été modifiées, et ses données sont incohérentes avec les données sur le disque.
gérer
: InnoDB gère ces pages de cache via trois structures de listes chaînées :
Liste chaînée gratuite : gère les pages gratuites et enregistre les informations de bloc de contrôle des pages de cache gratuites.
Liste chaînée LRU : gère les pages propres et les pages sales, utilise un algorithme LRU amélioré et est divisée en zones jeunes et anciennes pour optimiser le taux de réussite du cache.
Liste chaînée Flush : gère les pages sales qui doivent être vidées sur le disque, triées par heure de modification.
accès aux données : Lorsqu'il faut accéder à une page de données, InnoDB vérifiera d'abord si la page est déjà dans le pool de tampons. Si elle existe déjà, la page est utilisée directement ; si elle n'existe pas, la page est lue depuis le disque dans le Buffer Pool et la liste chaînée correspondante est mise à jour.
Mise à jour des données: Lorsqu'une page de données est modifiée, la page sera marquée comme page sale et pourra être ajoutée à la liste chaînée Flush pour attendre que le thread d'arrière-plan la vide sur le disque.
expulsion du cache: Lorsque l'espace du Buffer Pool est insuffisant, la page de cache la moins récemment utilisée sera éliminée selon l'algorithme LRU.
Réglez la taille de manière appropriée: Paramètres raisonnables basés sur la mémoire système et les conditions de charge de la base de donnéesinnodb_buffer_pool_sizeparamètre.
Surveiller et ajuster: Surveillez régulièrement les indicateurs d’utilisation et de performance de Buffer Pool et effectuez les ajustements nécessaires.
Évitez l'analyse complète de la table: L'analyse complète de la table entraînera le chargement d'un grand nombre de pages de données dans le pool de tampons, réduisant ainsi le taux de réussite du cache.
Pour résumer, Buffer Pool est l'un des composants clés du moteur de stockage InnoDB dans la base de données MySQL Grâce à une configuration et une gestion raisonnables, les performances et l'efficacité de la base de données peuvent être considérablement améliorées.
Le processus MySQL implique plusieurs liens, depuis la connexion entre le client et le serveur MySQL, jusqu'à l'exécution, l'optimisation, la lecture des données et le retour des résultats des instructions SQL. Ce qui suit est un aperçu détaillé du processus MySQL :
Connecteur (Gestionnaire de connexion) :
Lorsqu'un client (tel qu'une application ou un outil de ligne de commande) demande une connexion à un serveur MySQL, le connecteur MySQL est responsable du traitement de ces demandes de connexion.
Le connecteur vérifie l'identité et les autorisations du client, ce qui inclut généralement la vérification de la correspondance du nom d'utilisateur et du mot de passe.
Si la vérification réussit, le connecteur allouera un thread (ou une session) au client pour les opérations SQL ultérieures.
Cache de requêtes (Query Cache, remarque : ce module a été supprimé dans MySQL 8.0) :
Pour les requêtes SELECT, MySQL vérifie d'abord si la même requête et ses résultats existent dans le cache de requêtes.
S'il est présent, MySQL renverra directement les résultats dans le cache, évitant ainsi d'effectuer l'opération de requête proprement dite.
Cependant, comme la mise en cache des requêtes peut entraîner une incohérence des données (par exemple, les données mises en cache peuvent avoir été modifiées par d'autres transactions), la fonction de mise en cache des requêtes a été supprimée dans MySQL 8.0.
Analyseur :
L'instruction SQL envoyée par le client est d'abord envoyée à l'analyseur.
La tâche de l'analyseur est d'analyser l'instruction SQL, de vérifier si sa syntaxe est correcte et de la convertir en une structure de données interne (telle qu'un arbre d'analyse ou un arbre de syntaxe).
S'il y a une erreur de syntaxe dans l'instruction SQL, l'analyseur renvoie des informations d'erreur au client.
Préprocesseur :
Dans certaines versions de MySQL ou dans certains scénarios spécifiques, il peut y avoir une étape de préprocesseur.
Le préprocesseur est principalement responsable du traitement ultérieur des instructions SQL, comme vérifier si la table ou le champ existe, étendre * dans l'instruction SELECT à toutes les colonnes de la table, etc.
Optimiseur :
L'optimiseur est chargé d'évaluer différents plans d'exécution pour les instructions SQL et de sélectionner le plan d'exécution optimal.
L'optimiseur prend en compte divers facteurs, tels que les index disponibles, l'efficacité de la méthode de jointure, le coût de la requête, etc.
L'optimiseur peut améliorer considérablement les performances des requêtes grâce à des opérations telles que l'utilisation d'index, la réorganisation des requêtes ou la fusion de requêtes.
Exécuteur:
L'exécuteur effectue les opérations de requête réelles sur la base du plan d'exécution généré par l'optimiseur.
L'exécuteur appellera l'interface du moteur de stockage (tel qu'InnoDB) pour lire les données dans la table de données et effectuer des opérations telles que le tri, l'agrégation et le filtrage.
Enfin, l'exécuteur renvoie les résultats de la requête au client.
Moteur de stockage :
MySQL prend en charge plusieurs moteurs de stockage, et chaque moteur de stockage possède ses propres méthodes spécifiques de stockage et de récupération de données.
InnoDB est l'un des moteurs de stockage par défaut de MySQL et prend en charge des fonctionnalités de base de données avancées telles que le traitement des transactions, le verrouillage au niveau des lignes et les clés étrangères.
Lorsque l'exécuteur appelle l'interface du moteur de stockage, le moteur de stockage est responsable de la lecture des données du disque ou de l'écriture des données sur le disque.
Pool tampon :
Le moteur de stockage InnoDB utilise Buffer Pool pour mettre en cache les données de table et les données d'index afin de réduire l'accès direct au disque.
Les pages de données du pool de tampons sont gérées en fonction de la fréquence d'accès et de l'état de modification afin d'améliorer le taux de réussite du cache et les performances des requêtes.
Transaction:
MySQL prend en charge le traitement des transactions, permettant de valider ou d'annuler plusieurs opérations dans leur ensemble.
Pendant l'exécution de la transaction, MySQL enregistrera les informations de journal nécessaires (telles que le journalisation et l'annulation du journal) pour garantir l'intégrité et la cohérence des données.
Si l'exécution de la transaction réussit, toutes les modifications seront définitivement enregistrées dans la base de données ; si l'exécution de la transaction échoue, vous pouvez utiliser le journal d'annulation pour effectuer une opération de restauration et restaurer les données à l'état avant le démarrage de la transaction.
Le processus de MySQL implique la connexion et l'authentification, le traitement des requêtes, le stockage et la récupération des données, ainsi que le traitement des transactions. En optimisant chaque étape de ces liens, les performances et la fiabilité de la base de données MySQL peuvent être considérablement améliorées. Dans le même temps, comprendre le processus d'exécution de MySQL aidera également à mieux comprendre son mécanisme de fonctionnement interne, permettant ainsi de mieux concevoir et optimiser la base de données.
Le pool de connexions de MySQL est une technologie utilisée pour gérer et réutiliser les connexions de bases de données. Il est conçu pour améliorer les performances et l'efficacité des opérations de bases de données, en particulier dans les environnements à forte concurrence. Ce qui suit est une explication détaillée du pool de connexions MySQL :
Le pool de connexions MySQL établit un nombre suffisant de connexions à la base de données au démarrage du programme et gère ces connexions de manière uniforme pour former un pool de connexions. Lorsque le programme doit accéder à la base de données, il demandera dynamiquement une connexion à partir du pool de connexions et renverra la connexion au pool de connexions après utilisation, au lieu de recréer et de fermer la connexion pour chaque opération.
Réduire la consommation de ressources : La création et la fermeture d'une connexion à une base de données sont un processus relativement long, impliquant la prise de contact à trois et la vague à quatre voies de la connexion TCP, ainsi que le processus d'authentification de la base de données. Grâce au regroupement de connexions, les connexions existantes peuvent être réutilisées pour réduire ces frais généraux.
Améliorer les performances : Dans un scénario à forte concurrence, si une nouvelle connexion à la base de données est créée pour chaque requête, les performances du serveur diminueront considérablement. L'utilisation d'un pool de connexions peut améliorer considérablement la vitesse de réponse et le débit de la base de données.
Évitez les fuites de connexion : Sans utiliser de pool de connexions, si une exception se produit lorsque le programme ferme la connexion, cela peut provoquer une fuite de connexion, c'est-à-dire que la connexion n'est pas fermée correctement et occupe les ressources système. Le pool de connexions peut éviter cette situation grâce au mécanisme de recyclage des délais d'attente.
initialisation: Lorsque le programme démarre, le pool de connexions créera un certain nombre de connexions à la base de données en fonction de la configuration, et placera ces connexions dans le pool de connexions pour sauvegarde.
Demander une connexion : Lorsque le programme doit accéder à la base de données, il demandera une connexion à partir du pool de connexions. S'il y a une connexion inactive dans le pool de connexions, elle sera renvoyée directement au programme pour utilisation ; s'il n'y a pas de connexion inactive, elle attendra un certain temps selon la configuration ou renverra une erreur.
Utiliser la connexion: Le programme utilise la connexion demandée pour effectuer des opérations de base de données.
connexion de retour : Une fois l'opération terminée, le programme renvoie la connexion au pool de connexions. Le pool de connexions effectuera certaines vérifications sur la connexion. Si la connexion est toujours valide, elle sera remise dans le pool de connexions ; si la connexion a expiré, elle sera fermée et supprimée du pool de connexions.
Fermer le pool de connexions: À la fin du programme, toutes les connexions du pool de connexions seront fermées et les ressources système occupées seront libérées.
Il existe de nombreux fournisseurs de pools de connexions MySQL sur le marché, parmi lesquels les plus populaires sont :
DBCP : Il s'agit d'une implémentation de pool de connexions open source dans le cadre du projet Apache, et c'est le pool de connexions fourni avec Tomcat. Il est plus rapide que les autres pools de connexions, mais n'est peut-être pas assez stable.
C3P0 : Il s'agit d'un pool de connexions JDBC open source, qui implémente la source de données et la liaison JNDI, et prend en charge la norme JDBC3 et l'extension standard JDBC2. Le taux de C3P0 est relativement lent mais très stable.
Druide (Druid) : Il s'agit d'un pool de connexions open source fourni par Alibaba. Il combine les avantages de DBCP et C3P0 et offre de puissantes fonctions de surveillance et d'extension. Druid est actuellement l'un des pools de connexions MySQL les plus couramment utilisés.
La configuration du pool de connexions comprend généralement les aspects suivants :
Nombre maximum de connexions: Le nombre maximum de connexions que le pool de connexions peut gérer.
Nombre minimum de connexions: Le nombre initial de connexions créées au démarrage du pool de connexions.
Obtenir le délai d'expiration de la connexion: Le temps d'attente maximum lors de l'obtention d'une connexion à partir du pool de connexions.
Vérification de la connexion: Vérifiez la validité de la connexion avant d'obtenir la connexion ou lors du retour de la connexion.
Stratégie de recyclage des connexions: recyclez les connexions en fonction de leur temps d'inactivité et de leur temps d'utilisation.
Le pooling de connexions et le pooling de threads sont deux technologies de pooling de ressources différentes, mais il existe une certaine relation entre elles. Le pool de threads est principalement utilisé pour gérer les ressources de threads, tandis que le pool de connexions est utilisé pour gérer les ressources de connexion à la base de données. Lorsqu'un thread du pool de threads doit effectuer une opération de base de données, il demandera une connexion à partir du pool de connexions ; une fois l'opération terminée, la connexion sera renvoyée au pool de connexions. Cette relation permet d’obtenir une utilisation efficace des ressources et une gestion simplifiée.
Pour résumer, le pool de connexions MySQL est une technologie importante de gestion des connexions de base de données. Il fournit un support solide pour les opérations de base de données en réutilisant les connexions, en réduisant la consommation de ressources et en améliorant les performances. Dans les applications réelles, le fournisseur de pool de connexions et les paramètres de configuration appropriés peuvent être sélectionnés en fonction des besoins et des scénarios spécifiques du projet.
Les questions d'entretien liées aux journaux MySQL peuvent couvrir de nombreux aspects, notamment le type, le rôle, la configuration, l'optimisation des journaux et l'application des journaux dans la récupération de données, la réplication de données, etc. Voici quelques questions d'entretien courantes liées aux journaux MySQL et leurs réponses détaillées :
Les journaux courants dans MySQL incluent les éléments suivants :
Journal des erreurs : Enregistrez les informations sur les erreurs lorsque le serveur MySQL démarre, s'exécute ou s'arrête, ainsi que toute information sur les erreurs critiques. Cela aide à diagnostiquer le problème.
Journal des requêtes (journal général) : Enregistrez chaque demande et réponse client reçue par le serveur MySQL, y compris les activités de connexion des utilisateurs, les instructions SQL exécutées, etc. Généralement utilisé pour l'audit ou le débogage.
Journal des requêtes lentes : Enregistrez les instructions SQL dont le temps d'exécution dépasse le seuil, ainsi que le temps d'exécution, les tables consultées, les index utilisés et d'autres informations de ces instructions. Utilisé pour le réglage des performances et l’optimisation des requêtes.
Journal binaire (Binlog en abrégé): Enregistre toutes les instructions qui modifient les données de la base de données (à l'exclusion des instructions telles que SELECT et SHOW), principalement utilisées pour la réplication et la récupération de données.
Refaire le journal: Dans le moteur de stockage InnoDB, il est utilisé pour garantir la durabilité des transactions. Même en cas de panne du système, les données peuvent être récupérées via les redo logs.
Annuler le journal: Dans le moteur de stockage InnoDB, il est utilisé pour enregistrer l'état des données avant le démarrage de la transaction, de sorte que lorsque la transaction échoue ou est annulée, les données peuvent être restaurées à l'état avant le démarrage de la transaction.
Journal de relais: Dans l'architecture de réplication MySQL, le journal de relais sur le serveur esclave est utilisé pour stocker le contenu du journal binaire reçu du serveur maître.
Le journal des requêtes lentes peut être ouvert et configuré via le fichier de configuration MySQL (tel que my.cnf ou my.ini), ou il peut être défini dynamiquement via des commandes SQL.
Méthode du fichier de configuration :
Ajoutez ou modifiez les paramètres suivants dans le fichier de configuration MySQL :
[mysqld] slow_query_log = 1 slow_query_log_file = /path/to/your/slow-query.log long_query_time = 2
dans,
slow_query_log
Utilisé pour activer les journaux de requêtes lentes,
slow_query_log_file
Spécifiez le chemin d'accès au fichier journal des requêtes lentes,
long_query_time
Définissez le temps d'exécution des instructions SQL dépassant le nombre de secondes à enregistrer dans le journal des requêtes lentes.
Après avoir modifié le fichier de configuration, vous devez redémarrer le service MySQL.
Mode commande SQL :
Le journal des requêtes lentes peut être activé dynamiquement via des commandes SQL, maisslow_query_log_fileetlong_query_timeLes paramètres devront peut-être être définis via un fichier de configuration, car les paramètres dynamiques peuvent ne pas être pris en charge ou ne pas fonctionner.
Activer le journal des requêtes lentes :
sql复制代码 SET GLOBAL slow_query_log = 'ON';
Notez que le journal des requêtes lentes ouvert dynamiquement à l'aide des commandes SQL peut devenir invalide après le redémarrage du système, il est donc recommandé de le définir via le fichier de configuration.
Les journaux binaires (Binlog) ont trois formats :
DÉCLARATION : réplication basée sur les instructions SQL (réplication basée sur les instructions, SBR). Dans ce format, MySQL enregistrera les instructions SQL exécutées dans le binlog. Son avantage est que le volume du journal est petit, mais il peut rencontrer certains problèmes de réplication, tels que les fonctions, les déclencheurs, les procédures stockées, etc., qui peuvent entraîner une incohérence dans les données maître-esclave.
RANGÉE : Réplication basée sur les lignes (RBR). Dans ce format, MySQL enregistrera les modifications de données des lignes modifiées. Il présente l’avantage d’éviter certains problèmes de réplication, mais le volume de logs peut être important.
MIXTE : Réplication basée sur un mélange (MBR). MySQL choisira automatiquement d'utiliser le format STATEMENT ou ROW selon la situation. Le mode mixte est le mode par défaut et est conçu pour combiner le meilleur des deux mondes.
Redo Log garantit la durabilité des transactions dans le moteur de stockage InnoDB des manières suivantes :
Lorsqu'une transaction est soumise, le moteur InnoDB mettra d'abord en cache le journal de rétablissement de la transaction dans le tampon de journalisation en mémoire, et mettra en même temps à jour la page de données correspondante en mémoire.
Ensuite, au moment approprié, écrivez le journal de rétablissement dans le tampon de journalisation dans le fichier de journalisation sur le disque. Ce processus est asynchrone, mais le timing et la fréquence du brossage du disque peuvent être contrôlés en configurant les paramètres.
En cas de panne du système, le moteur InnoDB vérifiera le fichier de journalisation au démarrage et restaurera les modifications apportées par la transaction soumise la plus récemment en fonction des enregistrements qu'il contient, garantissant ainsi la durabilité des données.
Afficher les fichiers journaux：
journal des erreurs: Cela peut généralement être fait en consultant le fichier de configuration MySQLlog_errorparamètre pour spécifier le chemin du fichier pour localiser le fichier journal des erreurs et utiliser un éditeur de texte ou un outil de ligne de commande tel quetail、catetc.) pour afficher son contenu.
Il se consacre à la recherche technologique depuis plus de 30 ans et maîtrise divers langages tels que java, linux, javascript, php, css, etc. Il a apporté de nombreuses contributions dans le domaine de l'open source. station de documentation pour les développeurs pour partager certains problèmes de développement technologique pour référence future.
