MySQL apprend à parler du processus d'exécution des instructions de requête-tutoriel mysql-php.cn

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 !

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

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)
);
```
Copier après la connexion
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)
);
```
Copier après la connexion
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 &#39;Abc%&#39;;
```
Copier après la connexion
Après la réécriture
```
SELECT * FROM USERINFO WHERE name >= &#39;Abc&#39; AND name < &#39;Abd&#39;;
```
Copier après la connexion
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

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!