comment fonctionne MySQL

Architecture logique MySQL

L'architecture logique MySQL est divisée en trois couches. La couche supérieure est la couche client, qui n'est pas unique à MySQL. Les fonctions telles que le traitement des connexions, l'authentification des autorisations et la sécurité sont toutes gérées. dans cette couche.

La plupart des services de base de MySQL se trouvent dans la couche intermédiaire, y compris l'analyse des requêtes, l'analyse, l'optimisation, la mise en cache, les fonctions intégrées (temps, mathématiques, cryptage, etc.), et toutes les fonctions du moteur de stockage croisé sont également dans cette couche. Implémentation de la couche : procédures stockées, déclencheurs, vues, etc.

La couche inférieure est le moteur de stockage, qui est responsable du stockage et de la récupération des données dans MySQL. La couche de service intermédiaire communique avec le moteur de stockage via des API. Ces interfaces API protègent les différences entre les différents moteurs de stockage.

Processus de requête MySQL

Lors de l'envoi d'une requête à MySQL :

1. Protocole de communication client/serveur

Le protocole de communication client/serveur MySQL est « semi-duplex » : à tout moment, soit le serveur envoie des données au client , ou le client envoie des données au serveur. Ces deux actions ne peuvent pas se produire en même temps. 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. Nous ne pouvons donc pas et n'avons pas besoin de couper un message en petits morceaux et de les envoyer indépendamment, et il n'y a aucun moyen de contrôler le message. couler.

Le client envoie la requête de requête au serveur dans un paquet de données séparé, donc lorsque l'instruction de requête est très longue, le paramètre max_allowed_packet doit être défini. Cependant, il convient de noter que si la requête est trop volumineuse, le serveur refusera d’accepter davantage de données et lèvera une exception.

Au contraire, les données que le serveur répond à l'utilisateur sont généralement constituées d'un grand nombre de données, constituées de plusieurs paquets de données. Mais lorsque le serveur répond à la demande du client, celui-ci doit accepter complètement l'intégralité du résultat renvoyé, au lieu de simplement prendre les premiers résultats et demander ensuite au serveur d'arrêter l'envoi. Par conséquent, dans le développement actuel, c'est une très bonne habitude de garder les requêtes aussi simples que possible et de ne renvoyer que les données nécessaires, et de réduire la taille et le nombre de paquets de données pendant la communication. C'est aussi la raison pour laquelle nous essayons d'éviter d'utiliser SELECT. * et ajout de restrictions LIMIT dans la première requête.

2. Cache de requête

Avant d'analyser une instruction de requête, si le cache de requête est activé, MySQL vérifiera si l'instruction de requête atteint les données dans le cache de requête . Si la requête en cours atteint le cache de requêtes, les résultats dans le cache seront renvoyés directement après avoir vérifié une fois les autorisations de l'utilisateur. 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.

MySQL stocke le cache dans une table de référence (une structure de données similaire à HashMap), indexée par une valeur de hachage. Cette valeur de hachage est déterminée par la requête elle-même, la base de données actuellement interrogée, le numéro de version du protocole client. , etc. Certaines informations calculées peuvent affecter les résultats. Par conséquent, toute différence de caractères entre les deux requêtes (espaces, commentaires) entraînera une perte du cache.

Si la requête contient des fonctions définies par l'utilisateur, des fonctions stockées, des variables utilisateur, des tables temporaires ou des tables système dans la bibliothèque MySQL, les résultats de la requête ne seront pas mis en cache. Par exemple, la fonction NOW() ou CURRENT_DATE() renverra des résultats de requête différents en raison de temps de requête différents. Un autre exemple est que l'instruction de requête contenant CURRENT_USER ou CONNECION_ID() renverra des résultats différents en raison de différents utilisateurs en cache. Cela n'a aucun sens.

3. Invalidation du cache

Le système de cache de requêtes MySQL garde une trace de chaque table impliquée dans la requête. Si ces tables (données ou structure) changent, alors toutes sont mises en cache. les données liées à ce tableau seront invalidées. Pour cette raison, MySQL doit invalider tous les caches de la table correspondante lors de toute opération d'écriture. Si le cache de requêtes est très volumineux ou fragmenté, cette opération peut entraîner une consommation importante du système et même provoquer un blocage du système pendant un certain temps. De plus, la consommation supplémentaire du cache de requêtes sur le système concerne non seulement les opérations d'écriture, mais également les opérations de lecture :

1. Toute instruction de requête doit être vérifiée avant de démarrer, même si cette instruction SQL n'atteindra jamais. Mise en cache

2. Si les résultats de la requête peuvent être mis en cache, une fois l'exécution terminée, les résultats seront stockés dans le cache, ce qui entraînera également une consommation système supplémentaire

Sur cette base, vous devez savoir ce que ce n'est pas. Dans ce cas, la mise en cache des requêtes améliorera les performances du système, et la mise en cache et l'invalidation entraîneront une consommation supplémentaire. Ce n'est que lorsque les économies de ressources apportées par la mise en cache sont supérieures aux ressources consommées par elle-même qu'elle apportera une amélioration des performances. au système. Mais il est très difficile d’évaluer si l’activation du cache peut améliorer les performances. Si le système rencontre des problèmes de performances, vous pouvez essayer d'activer le cache de requêtes et d'effectuer quelques optimisations dans la conception de la base de données : Par exemple :

1 Utilisez plusieurs petites tables au lieu d'une grande table, et soyez. veillez à ne pas surconcevoir

2. Insertion par lots au lieu d'une insertion unique cyclique

3. Contrôlez raisonnablement la taille de l'espace du cache. De manière générale, il est plus approprié de définir la taille sur. dizaines de mégaoctets

4. Vous pouvez utiliser SQL_CACHE et SQL_NO_CACHE Pour contrôler si une certaine instruction de requête doit être mise en cache

N'activez pas facilement le cache de requêtes, en particulier pour les applications gourmandes en écriture. Si vous ne pouvez vraiment pas vous en empêcher, vous pouvez définir query_cache_type sur DEMAND. Pour le moment, seules les requêtes qui ajoutent SQL_CACH seront mises en cache, et les autres requêtes ne le seront pas. Cela vous permet de contrôler librement quelles requêtes doivent être mises en cache.

4. Analyse syntaxique et prétraitement

MySQL analyse l'instruction SQL via des mots-clés et génère un arbre d'analyse correspondant. Cet analyseur de processus vérifie et analyse principalement les règles de grammaire. Par exemple, si les mauvais mots-clés sont utilisés dans SQL ou si l'ordre des mots-clés est correct, etc. Le prétraitement vérifiera en outre si l'arbre d'analyse est légal selon les règles MySQL. Par exemple, vérifiez si la table de données et la colonne de données à interroger existent, etc.

5. Optimisation des requêtes

Une fois que l'arbre syntaxique est considéré comme légal et que l'optimiseur le convertit en plan de requête, dans la plupart des cas, une requête peut avoir plusieurs Tous les méthodes d'exécution finiront par renvoyer les résultats correspondants. Le rôle de l'optimiseur est de trouver le meilleur plan d'exécution parmi elles.

L'optimiseur de requêtes MySQL est un composant très complexe. Il utilise de nombreuses stratégies d'optimisation pour générer un plan d'exécution optimal :

1. Redéfinir l'ordre d'association de la table (lors de l'interrogation de plusieurs tables, ils ne suivent pas forcément l'ordre spécifié en SQL, mais il existe quelques techniques pour préciser l'ordre d'association)

2. Optimiser les fonctions MIN() et MAX() (trouver la valeur minimale d'une colonne) valeur, si la colonne a un index, il vous suffit de trouver l'extrémité la plus à gauche de l'index B+Tree, sinon vous pouvez trouver la valeur maximale)

3. Terminez la requête plus tôt (lors de l'utilisation de Limit, après). trouver un ensemble de résultats qui correspond au nombre de Terminer la requête immédiatement)

4. Optimiser le tri (dans l'ancienne version de MySQL, deux tris de transmission seront utilisés, c'est-à-dire lire d'abord le pointeur de ligne et les champs qui doivent être triés, en les triant en mémoire, puis en les triant en fonction des résultats du tri. Lire les lignes de données, et la nouvelle version utilise le tri à transfert unique, c'est-à-dire lire toutes les lignes de données en même temps, puis les trier en fonction. les colonnes données)

6. Moteur d'exécution de requêtes

Après avoir terminé les étapes d'analyse et d'optimisation, MySQL générera le plan d'exécution correspondant et le moteur d'exécution de requêtes le fera progressivement. exécuter les instructions selon le plan d'exécution pour obtenir les résultats. La plupart des opérations de l'ensemble du processus d'exécution sont effectuées en appelant des interfaces implémentées par le moteur de stockage. Ces interfaces sont appelées API de gestionnaire. Chaque table du processus de requête est représentée par une instance de gestionnaire. En fait, MySQL crée une instance de gestionnaire pour chaque table pendant la phase d'optimisation de la requête. L'optimiseur peut obtenir des informations relatives aux tables en fonction des interfaces de ces instances, y compris tous les noms de colonnes. , statistiques d'index, etc. de la table. L'interface du moteur de stockage offre des fonctions très riches, mais il n'y a que des dizaines d'interfaces en bas. Ces interfaces, comme des tours, réalisent la plupart des opérations d'une requête.

7. Renvoyez les résultats au client

La dernière étape de l'exécution de la requête consiste à renvoyer les résultats au client. Même si aucune donnée ne peut être interrogée, MySQL renverra toujours des informations liées à la requête, telles que le nombre de lignes affectées par la requête, le temps d'exécution, etc.

Si le cache de requêtes est activé et que la requête peut être mise en cache, MySQL stockera également les résultats dans le cache.

Le renvoi de l'ensemble de résultats au client est un processus de retour incrémentiel et étape par étape. Il est possible que MySQL commence à renvoyer progressivement l'ensemble de résultats au client lorsqu'il génère le premier résultat. De cette façon, le serveur n'a pas besoin de stocker trop de résultats et de consommer trop de mémoire, et le client peut également obtenir les résultats renvoyés dès que possible. Il convient de noter que chaque ligne de l'ensemble de résultats sera envoyée sous forme de paquet de données répondant au protocole de communication décrit dans ①, puis transmis via le protocole TCP. Pendant le processus de transmission, les paquets de données MySQL peuvent être mis en cache puis envoyés. lots.

L'ensemble du processus d'exécution de requête de MySQL

1 Le client envoie une requête de requête au serveur MySQL

2. cache de requête.Si Si le cache est atteint, le résultat stocké dans le cache sera renvoyé immédiatement. Sinon, entrez le segment de niveau suivant

3. Le serveur effectue l'analyse et le prétraitement SQL, puis l'optimiseur génère le plan d'exécution correspondant

4. le plan d'exécution. Exécuter la requête

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!