Étude détaillée de la commande MySQL EXPLAIN

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';

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';

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

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 :

  ********************* 1. row ***********************
  id: 1
  select_type: SIMPLE
  table: inventory
  type: ref
  possible_keys: item_id
  key: item_id
  key_len: 4
  ref: const
  rows: 1
  Extra:

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 f5d188ed2c074f8b944552db028f98a1 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.

 ********************* 1. row ***********************
 id: 1
 select_type: SIMPLE
 table: p
 type: const
 possible_keys: PRIMARY
 key: PRIMARY
 key_len: 4
 ref: const
 rows: 1
 Extra:
 ********************* 2. row ***********************
 id: 1
 select_type: SIMPLE
 table: c
 type: ref
 possible_keys: parent_id
 key: parent_id
 key_len: 4
 ref: const
 rows: 10
 Extra:

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)

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

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 :

 mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
 +--------------------------------------+---------+
 | Variable_name | Value |
 +--------------------------------------+---------+
 | Handler_read_first | 1 |
 | Handler_read_key | 164 |
 | Handler_read_last | 0 |
 | Handler_read_next | 107 |
 | Handler_read_prev | 0 |
 | Handler_read_rnd | 0 |
 | Handler_read_rnd_next | 161 |
 +--------------------------------------+---------+
 相关的QEP 列还包括key列。

 
 2.3 possible_keys
 possible_keys 列指出优化器为查询选定的索引。
 一个会列出大量可能的索引(例如多于3 个)的QEP 意味着备选索引数量太多了，同时也可能提示存在一个无效的单列索引。
 可以用第2 章详细介绍过的SHOW INDEXES 命令来检查索引是否有效且是否具有合适的基数。
 为查询确定QEP 的速度也会影响到查询的性能。如果发现有大量的可能的索引，则意味着这些索引没有被使用到。
 相关的QEP 列还包括key 列。
 
 2.4 key_len
 key_len 列定义了用于SQL 语句的连接条件的键的长度。此列值对于确认索引的有效性以及多列索引中用到的列的数目很重要。
 此列的一些示例值如下所示：
 
 此列的一些示例值如下所示：
 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

 这个表的索引包括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';
 
 这个查询的QEP 返回的key_len 是62。这说明只有post_type列上的索引用到了(因为(20×3)+2=62)。尽管查询在WHERE 语句
 中使用了post_type 和post_date 列，但只有post_type 部分被用到了。其他索引没有被使用的原因是MySQL 只能使用定义索引的
 最左边部分。为了更好地利用这个索引，可以修改这个查询来调整索引的列。请看下面的示例：

 mysql> EXPLAIN SELECT ID, post_title
 -> FROM wp_posts
 -> WHERE post_type='post'
 -> AND post_status='publish'
 -> AND post_date > '2010-06-01';

 
 在SELECT查询的添加一个post_status 列的限制条件后，QEP显示key_len 的值为132，这意味着post_type、post_status、post_date
 三列(62+62+8，(20×3)+2，(20×3)+2，8)都被用到了。此外，这个索引的主码列ID 的定义是使用MyISAM 存储索
 引的遗留痕迹。当使用InnoDB 存储引擎时，在非主码索引中包含主码列是多余的，这可以从key_len 的用法看出来。
 相关的QEP 列还包括带有Using index 值的Extra 列。
 
 2.5 table
 table 列是EXPLAIN 命令输出结果中的一个单独行的唯一标识符。这个值可能是表名、表的别名或者一个为查询产生临时表
 的标识符，如派生表、子查询或集合。下面是QEP 中table 列的一些示例：
 table: item
 table: 6db98b0ebf978836f3b742968b1383c4
 table: 3d79052a4b39814111818a40e6c7009c
 表中N 和M 的值参考了另一个符合id 列值的table 行。相关的QEP 列还有select_type

 2.6 select_type
 select_type 列提供了各种表示table 列引用的使用方式的类型。最常见的值包括SIMPLE、PRIMARY、DERIVED 和UNION。其他可能
 的值还有UNION RESULT、DEPENDENT SUBQUERY、DEPENDENT UNION、UNCACHEABLE UNION 以及UNCACHEABLE QUERY。

 1. SIMPLE
 对于不包含子查询和其他复杂语法的简单查询，这是一个常 见的类型。

 2. PRIMARY
 这是为更复杂的查询而创建的首要表(也就是最外层的表)。这个类型通常可以在DERIVED 和UNION 类型混合使用时见到。
 
 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 3d79052a4b39814111818a40e6c7009c,
, 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.

 2.11 filtered
 filtered 列给出了一个百分比的值，这个百分比值和rows 列的值一起使用，可以估计出那些将要和QEP 中的前一个表进行连
 接的行的数目。前一个表就是指id 列的值比当前表的id 小的表。这一列只有在EXPLAIN EXTENDED 语句中才会出现。

 2.12 type
 type 列代表QEP 中指定的表使用的连接方式。下面是最常用的几种连接方式：
  const 当这个表最多只有一行匹配的行时出现system 这是const 的特例，当表只有一个row 时会出现
  eq_ref 这个值表示有一行是为了每个之前确定的表而读取的
  ref 这个值表示所有具有匹配的索引值的行都被用到
  range 这个值表示所有符合一个给定范围值的索引行都被用到
  ALL 这个值表示需要一次全表扫描其他类型的值还有fulltext 、ref_or_null 、index_merge 、unique_subquery、index_subquery 以及index。
  想了解更多信息可以访问http://www.php.cn/。
 

 3 解释EXPLAIN 输出结果
 理解你的应用程序(包括技术和实现可能性)和优化SQL 语句同等重要。下面给出一个从父子关系中获取孤立的父辈记录的商
 业需求的例子。这个查询可以用三种不同的方式构造。尽管会产生相同的结果，但QEP 会显示三种不同的路径。

 mysql> EXPLAIN SELECT p.*
 -> FROM parent p
 -> WHERE p.id NOT IN (SELECT c.parent_id FROM child
 c)\G
 ********************* 1. row ***********************
 id: 1
 select type: PRIMARY
 table: p
 type: ALL
 possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 160
 Extra: Using where
 ********************* 2. row ***********************
 id: 2
 select_type: DEPENDENT SUBQUERY
 table: c
 type: index_subquery
 possible_keys: parent_id
 key: parent_id
 key_len: 4
 ref: func
 rows: 1
 Extra: Using index
 2 rows in set (0.00 sec)

 EXPLAIN SELECT p.* FROM parent p LEFT JOIN child c ON p.id = c.parent_id WHERE c.child_id IS NULL\G
 ********************* 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: parent_id
 key: parent_id
 key_len: 4
 ref: test.p.id
 rows: 1
 Extra: Using where; Using index; Not exists
 2 rows in set (0.00 sec)

 以上就是MySQL EXPLAIN 命令详解学习的内容，更多相关内容请关注PHP中文网（www.php.cn）！