Explication détaillée de la commande MySQL EXPLAIN
La commande EXPLAIN de MySQL est utilisée pour le plan d'exécution de requête (QEP) des instructions SQL. Le résultat de cette commande nous permet de comprendre comment l'optimiseur MySQL exécute l'instruction SQL. Cette commande ne fournit aucune suggestion de réglage, mais elle peut fournir des informations importantes pour vous aider à prendre des décisions de réglage.
1 Syntaxe La syntaxe EXPLAIN de MySQL peut être exécutée sur une instruction SELECT ou une table spécifique. Si vous agissez sur une table, cette commande est équivalente à la commande DESC table. Les commandes UPDATE et DELETE nécessitent également des améliorations de performances. Lorsque ces commandes ne sont pas exécutées directement sur la clé principale de la table, afin de garantir une utilisation optimale de l'index, elles doivent être transformées en instructions SELECT (afin qu'elles exécutent la commande EXPLAIN). ). Veuillez consulter l'exemple suivant :
UPDATE table1
SET col1 = X, col2 = Y
WHERE id1 = 9
AND dt >= '2010-01-01';
Copier après la connexion
Cette instruction UPDATE peut être réécrite en une instruction SELECT comme ceci :
SELECT col1, col2
FROM table1
WHERE id1 = 9
AND dt >= '2010-01-01';
Copier après la connexion
Dans la version 5.6.10, vous pouvez directement effectuer des opérations d'analyse d'explication sur les instructions DML.L'optimiseur MySQL fonctionne en fonction du coût, il ne fournit aucune position QEP. Cela signifie que QEP est calculé dynamiquement à mesure que chaque instruction SQL est exécutée. Les instructions SQL dans les procédures stockées MySQL calculent également le QEP à chaque fois qu'elles sont exécutées. Le cache de procédures stockées analyse uniquement l'arborescence des requêtes.
2 Explication détaillée de chaque colonne
La commande MySQL EXPLAIN peut générer les informations suivantes pour chaque table de l'instruction SQL :
mysql> EXPLAIN SELECT * FROM inventory WHERE item_id = 16102176\G;
********************* 1. row ***********************
id: 1
select_type: SIMPLE
table: inventory
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 787338
Extra: Using where
Copier après la connexion
Ce QEP montre qu'aucun index n'est utilisé (c'est-à-dire une analyse complète de la table) et qu'un grand nombre de lignes sont traitées pour satisfaire la requête . Pour la même instruction SELECT, un QEP optimisé est le suivant :
Dans ce QEP, on voit qu'un index est utilisé, et on estime qu'il y a seulement une ligne. Les données seront récupérées.
Toutes les listes pour chaque ligne de QEP ressemblent à ceci :
id select_type table partitions (cette colonne n'apparaîtra que dans la syntaxe EXPLAIN PARTITIONS) possible_keys key key_len ref rows filtrée (cette colonne apparaît uniquement uniquement dans la syntaxe EXPLAINED EXTENDED) Extra
Ces colonnes affichent le QEP de l'instruction SELECT pour chaque table. Une table peut être associée à une table de schéma physique ou à une table temporaire interne générée lors de l'exécution de SQL (par exemple, à partir d'une sous-requête ou d'une opération de fusion).
Vous pouvez vous référer au manuel de référence MySQL pour plus d'informations : http://www.php.cn/.
Clé 2.1 La colonne clé indique l'index choisi par l'optimiseur. De manière générale, un seul index est utilisé par table dans une requête SQL. Il existe quelques exceptions à la fusion d'index, par exemple lorsque deux index ou plus sont utilisés sur une table donnée. Ce qui suit est un exemple de colonne clé dans QEP : clé : item_id clé : NULL clé : premier, dernier La commande SHOW CREATE TABLE
pour afficher le tableau et Comment indexer les détails des colonnes. Les colonnes liées à la colonne clé incluent également possible_keys, rows et key_len.
2.2 LIGNES
La colonne lignes fournit une estimation du nombre de lignes que l'optimiseur MySQL a tenté d'analyser pour toutes les lignes présentes dans l'ensemble de résultats cumulé. QEP permet de décrire facilement cette statistique difficile. Le nombre total d'opérations de lecture dans la requête est basé sur l'accumulation continue de la valeur des lignes de chaque ligne avant de fusionner les lignes. Il s'agit d'un algorithme de lignes imbriquées.
Prenons comme exemple QEP reliant deux tables. La valeur de la première ligne trouvée via la condition id=1 est 1, ce qui équivaut à une opération de lecture sur la première table. La deuxième ligne est trouvée par avec id=2, et la valeur des lignes est 5. Cela équivaut à 5 lectures correspondant à l’accumulation actuelle de 1. En se référant aux deux tableaux, le nombre total d’opérations de lecture est de 6. Dans un autre QEP , la valeur des premières lignes est 5 et la valeur des deuxièmes lignes est 1. Cela équivaut à 5 lectures pour le premier tableau, une pour chacune des 5 accumulations. Par conséquent, le nombre total d'opérations de lecture pour les deux tables est de 10 (5 5) fois.
La meilleure estimation est 1. Généralement, cela se produit lorsque la ligne que vous recherchez peut être trouvée dans le tableau grâce à la clé primaire ou à la clé unique. Dans le QEP ci-dessous, la boucle imbriquée externe peut être trouvée par id=1, et son numéro de ligne physique estimé est 1. La deuxième boucle traitait 10 lignes.
Vous pouvez utiliser la commande SHOW STATUS pour afficher les opérations réelles sur les lignes. Cette commande constitue le meilleur moyen de confirmer les opérations sur les lignes physiques. Voir l'exemple suivant :
mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 11 |
+-----------------------+-------+
7 rows in set (0.00 sec)
Copier après la connexion
Dans le QEP suivant, la boucle imbriquée externe trouvée avec id=1 est estimée à 160 lignes. La deuxième boucle est estimée à 1 rangée.
********************* 1. row ***********************
id: 1
select_type: SIMPLE
table: p
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 160
Extra:
********************* 2. row ***********************
id: 1
select type: SIMPLE
table: c
type: ref
possible_keys: PRIMARY,parent_id
key: parent_id
key_len: 4
ref: test.p.parent_id
rows: 1
Extra: Using where
Copier après la connexion
Les opérations de ligne réelles peuvent être visualisées via la commande SHOW STATUS, qui montre que le nombre d'opérations de lecture physique a considérablement augmenté. Voir l'exemple ci-dessous :
此列的一些示例值如下所示： key_len: 4 // INT NOT NULL key_len: 5 // INT NULL key_len: 30 // CHAR(30) NOT NULL key_len: 32 // VARCHAR(30) NOT NULL key_len: 92 // VARCHAR(30) NULL CHARSET=utf8
从这些示例中可以看出，是否可以为空、可变长度的列以及key_len 列的值只和用在连接和WHERE 条件中的索引的列 有关。索引中的其他列会在ORDER BY 或者GROUP BY 语句中被用到。下面这个来自于著名的开源博客软件WordPress 的表展示了 如何以最佳方式使用带有定义好的表索引的SQL 语句：
CREATE TABLE `wp_posts` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_status` varchar(20) NOT NULL DEFAULT 'publish' ,
`post_type` varchar(20) NOT NULL DEFAULT 'post',
PRIMARY KEY (`ID`),
KEY `type_status_date`(`post_type`,`post_status`,`post_date`,`ID`)
) DEFAULT CHARSET=utf8
CREATE TABLE `wp_posts` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_status` varchar(20) NOT NULL DEFAULT 'publish' ,
`post_type` varchar(20) NOT NULL DEFAULT 'post',
PRIMARY KEY (`ID`),
KEY `type_status_date`(`post_type`,`post_status`,`post_date`,`ID`)
) DEFAULT CHARSET=utf8
Copier après la connexion
这个表的索引包括post_type、post_status、post_date 以及ID列。下面是一个演示索引列用法的SQL 查询： EXPLAIN SELECT ID, post_title FROM wp_posts WHERE post_type='post' AND post_date > '2010-06-01';
mysql> EXPLAIN SELECT ID, post_title
-> FROM wp_posts
-> WHERE post_type='post'
-> AND post_status='publish'
-> AND post_date > '2010-06-01';
3. DERIVED 当一个表不是一个物理表时，那么就被叫做DERIVED。下面的SQL 语句给出了一个QEP 中DERIVED select-type 类型的 示例： mysql> EXPLAIN SELECT MAX(id) -> FROM (SELECT id FROM users WHERE first = 'west') c;
4. SOUS-REQUÊTE DÉPENDANTE Cette valeur de type de sélection est définie pour l'utilisation de sous-requêtes. L'instruction SQL suivante fournit cette valeur : mysql> EXPLAIN SELECT p.* -> FROM parent p -> WHERE p.id NOT IN (SELECT c.parent_id FROM child c); 🎜>
5. UNION Il s'agit d'un élément SQL dans l'instruction UNION. 6. UNION RESULT Il s'agit du résultat de retour d'une série de tables définies dans l'instruction UNION. Lorsque select_type est cette valeur, vous pouvez souvent voir que la valeur de table est , , ce qui signifie que la ligne d'identifiant correspondant fait partie de cet ensemble. Le SQL suivant génère un type de sélection UNION et UNION RESULT : mysql> EXPLAIN SELECT p.* FROM parent p WHERE p.val LIKE 'a%' -> ; SELECT p.* FROM parent p WHERE p.id > 5;
2.7 partitions
La colonne partitions représente les partitions utilisées par la table donnée. Cette colonne n'apparaîtra que dans l'instruction EXPLAIN PARTITIONS.
2.8 Extra
La colonne Extra fournit une gamme de informations supplémentaires sur les différents types de chemins d'optimisation MySQL. Les colonnes supplémentaires peuvent contenir plusieurs valeurs et peuvent avoir de nombreuses valeurs différentes, et ces valeurs continuent d'augmenter avec la sortie des nouvelles versions de MySQL. Vous trouverez ci-dessous une liste de valeurs couramment utilisées pour . Vous pouvez trouver une liste plus complète de valeurs sur : http://www.php.cn/.
1. Utilisation de Where
Cette valeur indique que la requête utilise l'instruction Where pour traiter les résultats - par exemple, effectuez une analyse complète de la table. Si des index sont également utilisés, les contraintes de lignes sont obtenues en obtenant les données nécessaires puis en traitant le tampon de lecture.
2. Utilisation temporaire
Cette valeur indique l'utilisation d'une table temporaire interne (basée sur la mémoire). Une requête peut utiliser plusieurs tables temporaires. Il existe de nombreuses raisons pour lesquelles MySQL crée des tables temporaires lors de l'exécution d'une requête. Deux raisons courantes sont l'utilisation de DISTINCT sur des colonnes de différentes tables ou l'utilisation de colonnes ORDER BY et GROUP BY différentes. Pour plus d'informations, veuillez visiter http://www.php.cn/ of_query_execution_and_use_of_temp_tables. Vous pouvez forcer une table temporaire à utiliser le moteur de stockage MyISAM sur disque . Il y a deux raisons principales à cela : L'espace occupé par la table temporaire interne dépasse la limite de la variable système min(tmp_table_size, max_ heap_table_size) Des colonnes TEXT/BLOB sont utilisées
3. Utilisation du tri de fichiers
Ceci est le résultat de l'instruction ORDER BY. Cela peut être un processus gourmand en CPU. Vous pouvez améliorer les performances en choisissant les index appropriés et en utilisant des index pour trier les résultats des requêtes. Veuillez vous référer au chapitre 4 pour les procédures détaillées.
4. Utilisation de l'index
Cette valeur souligne que seul l'index peut être utilisé pour répondre aux exigences de la table de requête et qu'il n'est pas nécessaire d'accéder directement aux données de la table. Veuillez vous référer aux exemples détaillés du chapitre 5 pour comprendre cette valeur.
5. Utilisation du tampon de jointure
Cette valeur souligne qu'aucun index n'est utilisé lors de l'obtention de la condition de jointure et qu'un tampon de jointure est nécessaire pour stocker les résultats intermédiaires. Si cette valeur apparaît, sachez qu'en fonction des conditions spécifiques de la requête, vous devrez peut-être ajouter un index pour améliorer les performances.
6. Impossible Where
Cette valeur souligne que l'instruction Where n'entraînera aucune ligne répondant aux conditions. Veuillez consulter l'exemple suivant : mysql> EXPLAIN SELECT * FROM user WHERE 1=2;
7. Sélectionnez les tables optimisées
Cette valeur signifie uniquement en utilisant l'index , l'optimiseur ne peut renvoyer qu'une seule ligne du résultat de la fonction d'agrégation. Voir l'exemple suivant : 8. Distinct
Cette valeur signifie que MySQL arrêtera de rechercher d'autres lignes après avoir trouvé la première ligne correspondante. 9. Fusions d'index
Lorsque MySQL décide d'utiliser plus d'un index sur une table donnée, l'un des formats suivants apparaîtra, détaillant l'utilisation des types d'index et de fusion. Utilisation de sort_union(...) Utilisation de union(...) Utilisation de intersect(...)
2.9 id
La colonne id est une référence continue au tableau affiché dans QEP. 2.10 ref
La colonne ref peut être utilisée pour identifier les colonnes ou les constantes utilisées pour la comparaison d'index.
Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn
![[Web front-end] Démarrage rapide de Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
