MySQL apprend à parler du processus d'exécution des instructions de requête

Si vous souhaitez apprendre MySQL en profondeur, vous devez commencer par l'architecture des macros. Dans cet article, nous apprendrons le processus d'exécution des instructions de requête MySQL. J'espère que cela sera utile à tout le monde !

La version MySQL de cet article est 8.0.18

Schéma d'architecture

Parser

Le rôle de l'analyseur est d'effectuer les tâches suivantes sur les instructions SQL envoyées depuis le client :

• Analyse grammaticale : Vérifiez la syntaxe de l'instruction SQL, si les crochets et les guillemets sont fermés, etc.
• Analyse lexicale : Divisez les mots-clés, les noms de tables et les noms de champs de l'instruction SQL en nœuds, et enfin obtenir un arbre d'analyse

Préprocesseur

L'analyseur vérifie principalement la grammaire et le lexique. Cependant, si la grammaire et le lexique sont corrects, mais que la table et les champs n'existent pas, alors cette instruction SQL ne peut pas. être exécuté correctement.

Le rôle du préprocesseur est donc : Analyse sémantique, pour déterminer si la sémantique de l'arbre d'analyse est correcte et si des tables et des champs existent. Après le prétraitement, un nouvel arbre d'analyse sera obtenu.

Optimiseur de requête

Structure de l'optimiseur de requête

Il existe de nombreuses façons d'exécuter une instruction SQL dans MySQL Bien que le même résultat soit obtenu à la fin, il existe des différences de surcharge, des choix spécifiques. La méthode d'exécution est déterminée par l'optimiseur de requêtes. Par exemple :

• Il existe plusieurs index parmi lesquels choisir dans la table, quel index choisir spécifiquement
• Lorsque nous effectuons des requêtes associées sur plusieurs tables, les données de la table sont utilisées comme table de référence

Query Optimizer Il s'agit de un optimiseur basé sur les coûts . Son principe de fonctionnement est de générer plusieurs plans d'exécution basés sur l'arbre d'analyse. Il évaluera le coût (coût) requis pour diverses méthodes d'exécution et obtiendra finalement un plan d'exécution avec le coût minimum. solution finale .

Mais cette méthode d'exécution avec la plus petite surcharge n'est pas nécessairement la méthode d'exécution optimale. Par exemple, un index doit être utilisé, mais une analyse complète de la table est effectuée. Bien qu'il y ait deux mots « optimisation » dans l'optimiseur de requêtes, cette optimisation n'est pas omnipotente. Dans de nombreux cas, il est plus nécessaire de se demander si l'instruction SQL est écrite de manière raisonnable.

Optimisation des requêtes logiques

L'optimisation des requêtes logiques est principalement responsable de l'exécution d'une certaine algèbre relationnelle pour optimiser les instructions SQL, afin que les instructions SQL puissent être exécutées plus efficacement

Optimisation des requêtes logiques Nous pouvons utiliser plusieurs cas pour comprendre simplement

• Fusion de sous-requêtes

  Avant la fusion

  SELECT * FROM t1 WHERE a1<10 AND (
    EXISTS(SELECT a2 FROM t2 WHERE t2.a2<5 AND t2.b2=1) OR
    EXISTS(SELECT a2 FROM t2 WHERE t2.a2<5 AND t2.b2=2)
  );

  Après la fusion

  SELECT * FROM t1 WHERE a1<10 AND (
    EXISTS(SELECT a2 FROM t2 WHERE t2.a2<5 AND (t2.b2=1 OR t2.b2=2)
  );

  Fusionnez plusieurs sous-requêtes en fusionnant les conditions de requête et réduisez plusieurs opérations de connexion à une seule analyse de table et une seule connexion

• Prédicat équivalent lourd Write

  comme le familier comme la requête floue, % est écrit après la condition pour effectuer la requête de plage d'index. En fait, c'est le mérite de l'optimiseur de requête

  Supposons que les conditions utilisées soient toutes indexées, avant la réécriture

  SELECT * FROM USERINFO WHERE name LIKE 'Abc%';

  Après la réécriture

  SELECT * FROM USERINFO WHERE name >= &#39;Abc&#39; AND name < &#39;Abd&#39;;

  Ceci. est la réponse à la requête de plage d'index

• Simplification conditionnelle

  La simplification conditionnelle utilise également certaines équations et relations algébriques pour parvenir à la simplification

  • Supprimez les crochets redondants dans les expressions et réduisez le niveau des arbres ET et OU générés lors de l'analyse syntaxique, tel que ((a AND b) AND (c AND d)) est simplifié en a AND b AND c AND d((a AND b) AND (c AND d)) 简化为 a AND b AND c AND d
  • 常量传递，比如 col1 = col2 AND col2 = 3 简化为 col1 = 3 AND col2 = 3
  • 表达式计算，对于一些可直接求解的表达式会转换为最终的计算结果，比如 col1 = 1+2 简化为 col1 = 3
  Transfert constant, tel que col1 = col2 AND col2 = 3 est simplifié en col1 = 3 AND col2 = 3
Calcul d'expression, pour certaines expressions qui peuvent être résolues directement Convertir en résultat final du calcul, comme col1 = 1+2 simplifié en col1 = 3

Optimisation des requêtes physiques

Principalement effectué par l'optimisation des requêtes physiques Le travail consiste à évaluer le coût de plusieurs plans d'exécution basés sur des instructions SQL.

• L'optimisation physique des requêtes résout principalement les problèmes suivants :

• Quelle méthode est la moins coûteuse en analyse de table unique (analyse de l'index + retour de table ou analyse de table complète)

Quand il y a une connexion par table, quelle méthode de connexion est la moins chère à utiliser ?

Une brève introduction à l'évaluation des coûts, l'évaluation des coûts est basée sur les deux dimensions du coût CPU et du coût IO Méthode de numérisation Formule d'évaluation des coûtsAnalyse séquentielleN_page * a_page_IO_time + N_tuple * a_tuple_CPU_timeIndex scanC_index + N_page_index * a_page_IO_time 🎜🎜 🎜

Les paramètres ci-dessus sont expliqués comme suit :

• a_page_IO_time, le temps IO de chargement d'une page de données
• N_page, le nombre de pages de données
• N_tuple, le nombre de tuples (un tuple est compris comme une ligne de données)
• a_tuple_CPU_time, un tuple de données Le temps CPU passé à l'analyse dans la page
• C_index, le temps IO passé sur l'index
• N_page_index, le nombre de pages d'index

Vous pouvez vous référer à cet article pour le calcul du coût de l'index : Pourquoi la requête MySQL a-t-elle choisi d'utiliser cet index ? ——Basé sur le calcul du coût de l'index MySQL 8.0.22

Plan d'exécution

Le plan d'exécution est le produit de l'optimiseur de requêtes et sera éventuellement transmis au moteur de stockage pour exécution. Le plan d'exécution peut nous aider à savoir comment MySQL exécutera cette instruction SQL.

Utilisez le mot-clé explain pour visualiser le plan d'exécution de l'instruction SQL, et vous pourrez obtenir les informations suivantes :

• id : l'ordre d'exécution de la requête dans la requête imbriquée
• possible_keys : les index qui peuvent être utilisés dans cette requête
• Clé : index réellement utilisé
• rows : environ combien de lignes de données doivent être récupérées pour obtenir le résultat
• select_type Le type de connexion entre plusieurs tables
• extra : informations supplémentaires, s'il existe une couverture d'index, un index pushdown, etc.

Moteur de stockage

Le serveur MySQL stipule les spécifications sur la façon dont les données sont stockées, extraites et mises à jour. Cette spécification est implémentée par les moteurs de stockage. Différents moteurs de stockage ont des méthodes de mise en œuvre différentes, donc différents moteurs de stockage le feront. présentent leurs fonctions et caractéristiques uniques. Les moteurs de stockage les plus couramment utilisés sont InnoDB et MyISAM

Parlons brièvement des caractéristiques de ces deux moteurs de stockage

InnoDB :

• Prend en charge les clés étrangères et les transactions, garantissant l'intégrité et la cohérence des données
• Prend en charge une granularité de verrouillage plus détaillée , un meilleur contrôle des verrous, une efficacité de lecture et d'écriture plus élevée

MyISAM

• ne prend pas en charge les transactions, prend uniquement en charge les verrous de ligne, adapté aux scénarios de lecture seule de données

Le moteur de stockage ne sera pas étendu pour l'instant, le sera Dans d'autres articles, ils continueront à intercaler leurs comparaisons, et analyseront en détail le processus de mise à jour des données dans InnoDB

Résumé

Dans le passé, je savais seulement écrire des instructions SQL sur le logiciel client, cliquez sur pour exécuter et obtenir les données

Jusqu'à présent, j'ai finalement appris qu'une instruction de requête doit passer par cette série d'opérations après avoir été transmise au serveur MySQL. L'analyseur vérifie la syntaxe et le lexique de l'instruction SQL s'il y en a. aucune erreur, il sera divisé en morceaux en fonction des nœuds de mots-clés, et formera enfin un arbre d'analyse

• Le préprocesseur vérifiera la sémantique de l'instruction SQL, vérifiera si l'instruction SQL est ambiguë, si les champs, etc. exister et former un nouvel arbre d'analyse

• L'optimiseur de requêtes l'obtient. Les différents plans d'exécution générés par cet arbre d'analyse sont obtenus grâce à l'optimisation de requêtes logiques et à l'optimisation de requêtes physiques pour obtenir un plan d'exécution au coût minimum

• Le Le moteur d'exécution obtient ce plan d'exécution et appelle l'interface du moteur de stockage

• Le moteur de stockage basé sur Le plan d'exécution effectue une requête de données. La requête interrogera et appellera certaines interfaces du système de fichiers dans le système d'exploitation, complétera la requête de données, et enfin le retourner au client

• [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!