Quel est le plan d'exécution dans MySQL

Dans MySQL, le plan d'exécution est un ensemble d'outils fournis par la base de données aux utilisateurs pour analyser, analyser et optimiser les instructions SQL. Les fonctions du plan d'exécution sont : 1. Afficher l'ordre de lecture de la table ; 2. Le type d'opération de lecture des données ; 3. Afficher les index pouvant être utilisés ; 4. Afficher les index réellement utilisés ; La relation de référence entre eux ; 6. Afficher le nombre de lignes interrogées dans chaque table.

L'environnement d'exploitation de ce tutoriel : système windows7, version mysql8, ordinateur Dell G3.

Lors de l'interrogation de la base de données, nous utilisons généralement des instructions SQL pour interroger les données dont nous avons besoin. Cependant, nous ne savons pas comment SQL est exécuté dans la base de données, s'il utilise des index, quels index sont utilisés, quels champs et tables sont recherchés, quel est leur ordre, combien de temps ils prennent, etc. Comment voir ces informations ? MySQL nous fournit un ensemble d'outils - des plans d'exécution.

1. Qu'est-ce qu'un plan d'exécution ?

Le plan d'exécution est un ensemble d'outils fournis par la base de données pour analyser, analyser et optimiser les instructions SQL. Il a les fonctions suivantes :

• Afficher la séquence de lecture du tableau.

• Type d'opération de lecture de données ;

• Quels index peuvent être utilisés ;

• Quels sont les index réellement utilisés ; de lignes interrogées pour chaque tableau.

• Remarque : Le plan d'exécution n'est que le meilleur plan de référence d'optimisation donné par la base de données pour SQL, et ce n'est pas nécessairement la solution optimale, c'est-à-dire

  Ne faites pas trop confiance au plan d'exécution

• 2 . Comment utiliser le plan d'exécution

Utilisation Le plan d'exécution est très simple, il suffit d'ajouter le mot-clé expliquer devant le SQL à exécuter.

3. Informations sur le plan d'exécution

Comme le montre la figure, le plan d'exécution SQL contient principalement les informations suivantes : id, select_type, table, partitions, type, possible_keys, key, key_len, ref, lignes, filtrées, Extra.

3.1, id

sélectionnez le numéro de série de la requête, l'identifiant est le même, l'ordre d'exécution est de haut en bas ; est exécuté ;

3.2, select_type

select_type : Représente le type d'instruction select, qui peut avoir les valeurs suivantes

SIMPLE : Représente une requête simple, qui n'inclut pas de requête de connexion et de ⼦ requête ;

PRIMARY : représente la requête principale ou l'instruction de requête la plus externe ;

• UNION : représente la deuxième instruction de requête ou suivante de la requête de connexion ;

• DEPENDENT UNION : la deuxième instruction SELECT ou suivante ; dans UNION, dépend de la requête externe ;

• UNION RESULT : Le résultat de la requête de jointure ;

• SOUS-REQUÊTE : ⼦La 1ère instruction SELECT de la requête ;

• SOUS-REQUÊTE DÉPENDANTE : ⼦La 1ère SELECT instruction dans la requête, en fonction de la requête externe ;

• DERIVED : SELECT (sous-requête de la clause FROM).

3.3, table
table : Indique le nom de la table de la requête, qui peut être dans les situations suivantes :

Affiche le nom de la table, si un alias est donné, l'alias est affiché ;

 : indique que la condition de requête est une sous-requête ;

•  : indique que la table 1 et la table 2 utilisent l'union.

3.4, partitions
partitions : partitions correspondantes.

3.5, type

type : Cette colonne représente le type d'association de table ou le type d'accès, c'est-à-dire que la base de données détermine comment trouver les lignes dans la table et la plage approximative des enregistrements de lignes de données. Du meilleur au pire, ce sont : system > const > eq_ref > range > all

system : Il n'y a qu'une seule ligne d'enregistrements dans la table, ce qui équivaut à la table système, qui est de type const. Les colonnes spéciales n'apparaissent généralement pas et peuvent être ignorées

const : un accès à l'index, correspondant à une ligne de données, il est donc souvent utilisé dans ; requêtes d'index PRIMARY KEY ou UNIQUE. On peut comprendre que const est l'optimisation ;

• eq_ref : Analyse d'index unique, pour chaque clé d'index, un seul enregistrement dans la table lui correspond, clé primaire couramment utilisée ou analyse d'index unique. , c'est peut-être le meilleur type de jointure en plus de const ;

• ref : analyse d'index non unique, renvoie toutes les lignes correspondant à une seule valeur, utilisée pour les colonnes indexées avec les opérateurs =,

• range : récupérez uniquement les lignes d'une plage donnée, utilisez un index pour sélectionner les lignes, généralement utilisé pour les requêtes telles que entre, , dans, etc. Cette requête de plage est meilleure que l'index car elle n'a besoin que de scanner l'index Un point qui se termine à un autre point ;

• index : l'arbre d'index doit être parcouru ;

• all : une analyse complète de la table, ce qui signifie que la base de données doit trouver ce dont elle a besoin du début à fin. Cela nécessite généralement l'ajout d'index pour l'optimisation.

Remarque : lors de l'optimisation de SQL, vous devez optimiser au moins au moins la plage, et il est recommandé d'optimiser pour ref, de préférence const.

3.6, possible_keys

possible_keys : Cette colonne indique quels index peuvent être utilisés par la requête pour rechercher. Lors de l'explication, il peut arriver que possible_keys ait une colonne, mais que la clé affiche NULL. Cela est dû au fait qu'il n'y a pas beaucoup de données dans la table et que la base de données pense que l'index n'est pas utile pour cette requête, elle choisit donc un index complet. requête de table.
Si la colonne est NULL, il n'y a pas d'index associé. Dans ce cas, vous pouvez vérifier la clause Where pour voir si vous pouvez créer un index approprié pour améliorer les performances des requêtes, puis utiliser expliquer pour voir l'effet.

3.7, key

key : Affiche la clé (index) qui est réellement décidée à utiliser dans la base de données . Si aucun index n'est sélectionné, la valeur de key est NULL. Les index peuvent être forcés à être utilisés ou ignorés.

3.8, key_len

key_len : Cette colonne indique le nombre d'octets utilisés par la base de données dans l'index Grâce à cette valeur, vous pouvez calculer quelles colonnes de l'index sont spécifiquement utilisées. Le calcul numérique est le suivant :

.

Type de chaîne
char(n) : longueur de n octets
varchar(n) : longueur de chaîne de stockage de 2 octets, si elle est utf-8, la longueur est 3n + 2

Type numérique
tinyint : 1 octet
smallint : 2 Octets
int : 4 octets
bigint : 8 octets

type d'heure
date : 3 octets
horodatage : 4 octets
datetime : 8 octets

Si le champ autorise NULL, 1 octet est requis pour enregistrer si Est NULL

Remarque : La longueur maximale de l'index est de 768 octets. Lorsque la chaîne est trop longue, la base de données effectuera un processus similaire à l'index du préfixe gauche et extraira la première moitié des caractères pour l'indexation.

3.9, ref

ref : Cette colonne montre la colonne ou la constante que la table recherche la valeur dans l'index de l'enregistrement de la colonne clé. Les plus courantes sont : const (constante), func, null , nom du champ (exemple : film.id)

3.10, lignes

lignes : Cette colonne est le nombre estimé à être lu et scanné par la base de données . ensemble de résultats. Par conséquent, plus la valeur est petite, mieux c’est.

3.11, filtré

filtré : le nombre de lignes renvoyées en conséquence représente le pourcentage du nombre de lignes lues, plus la valeur est grande, mieux c'est.

3.12, Extra

extra : Cette colonne affiche des

informations supplémentaires, c'est-à-dire des informations non incluses dans d'autres colonnes. Les valeurs spécifiques sont les suivantes :

• distinct : La base de données a trouvé le. première correspondance⾏ Après cela, arrêtez de chercher plus de lignes pour la combinaison de lignes actuelle ;

• n'existe pas : la base de données peut effectuer une optimisation LEFT JOIN sur la requête, et une ligne correspondant à la norme LEFT JOIN est trouvée, qui n'est plus la ligne précédente. La combinaison de lignes vérifie plus de lignes dans le tableau ;

• plage vérifiée pour chaque enregistrement (carte d'index : #) : la base de données n'a pas trouvé un bon index pouvant être utilisé, mais a constaté que si les colonnes du table précédente La valeur est connue, et certains index peuvent être utilisés ;

• using filesort (point clé) : La base de données utilisera un index externe pour trier les résultats, au lieu de lire les lignes de la table dans l'ordre des index. À ce stade, MySQL parcourra tous les enregistrements éligibles en fonction du type de connexion, enregistrera les mots-clés de tri et les pointeurs de ligne, puis triera les mots-clés et récupérera les informations sur les lignes dans l'ordre. Dans ce cas, il faut généralement envisager

  utiliser des index pour optimiser ;

• utiliser des index (point clé) : utiliser uniquement les informations de l'arborescence d'index sans chercher davantage pour lire les lignes réelles Récupérer les informations des colonnes dans le tableau signifie que

  select utilise un index de couverture sans avoir à revenir à la table pour interroger  ; trier par et regrouper par. Lorsque cela se produit, une optimisation doit généralement être effectuée. La première chose à faire est d'utiliser un index pour optimiser

• en utilisant Where : La base de données sera filtrée une fois que le moteur de stockage aura récupéré les lignes. Cela consiste à lire d'abord l'intégralité des données, puis à vérifier en fonction de la condition Where. Si elles correspondent, elles seront conservées, et si elles ne correspondent pas, elles seront supprimées
  en utilisant la condition d'index : similaire à Using ; où, la colonne interrogée n'est pas complètement couverte par l'index, et la condition Where est la plage d'une colonne de début 

• en utilisant sort_union(...), en utilisant union(...), en utilisant intersect(.. .) : Ces fonctions illustrent comment fusionner des analyses d'index pour les types de jointure index_merge ;

• using index for group-by : similaire à la méthode Using index pour accéder à une table, Using index for group-by signifie que la base de données a trouvé un index qui peut être utilisé pour interroger toutes les colonnes du groupe by ou des requêtes distinctes sans recherche supplémentaire sur le disque dur pour accéder à la table réelle ;

• null : la colonne interrogée n'est pas couverte par l'index et la condition de filtre Where est la première colonne de l'index, ce qui signifie que l'index est utilisé, mais certains les champs ne sont pas couverts par l'index et doivent être renvoyés à la table via " "Pour y parvenir, l'index n'est pas purement utilisé, et l'index n'est pas non plus utilisé du tout. Même si l'index est utilisé mais qu'une opération de retour de table est requise, l'opération de retour de table doit être évitée.

【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!