Cet article vous présente une introduction à la commande d'interprétation EXPLAIN dans MySQL (avec des exemples). Elle a une certaine valeur de référence. Les amis dans le besoin peuvent s'y référer.
1 concept EXPLAIN
EXPLAIN nous fournira des informations sur la façon dont MySQL exécute SQL :
2 EXPLAIN informations sur la colonne de sortie
EXPLAIN informations sur le champ de sortie
Première colonne : nom de la colonne, deuxième colonne : attributs équivalents affichés dans la sortie lorsque FORMAT = Nom JSON, troisième colonne : Champ signifiant
| Column | JSON Name | Meaning |
|---|---|---|
| id | select_id | select标识号 |
| select_type | None | select类型 |
| table | table_name | 这一行数据是关于哪张表的 |
| partitions | partitions | 匹配的分区，对于未分区表，该值为空 |
| type | access_type | 使用的连接类别,有无使用索引 |
| possible_keys | possible_keys | MySQL能使用哪个索引在该表中找到行 |
| key | key | MySQL实际决定使用的键（索引） |
| key_len | key_length | MySQL决定使用的键长度。如果键是NULL，长度为NULL |
| ref | ref | 与索引关联的列 |
| rows | rows | mysql认为执行sql时必须被校验的行数 |
| filtered | filtered | 表示此查询条件所过滤的数据的百分比 |
| Extra | None | 附加信息 |
2.1 id
Identifiant SELECT. SELECT Le numéro de séquence dans la requête, qui peut être vide.
2.2 select_type
Type SELECT, tous les types sont affichés dans le tableau ci-dessous, EXPLAIN au format JSON expose le type SELECT en tant qu'attribut de query_block, sauf s'il est SIMPLE ou PRIMARY. Le nom JSON (Aucun le cas échéant) est également affiché dans le tableau.
| select_type Value | JSON Name | Meaning |
|---|---|---|
| SIMPLE | None | 简单SELECT(不使用UNION或子查询等) |
| PRIMARY | None | 嵌套查询时最外层的查询 |
| UNION | None | UNION中的第二个或后面的SELECT语句 |
| DEPENDENT UNION | dependent (true) | UNION中的第二个或以后的SELECT语句，取决于外部查询 |
| UNION RESULT | union_result | UNION的结果 |
| SUBQUERY | None | 子查询中的第一个选择 |
| DEPENDENT SUBQUERY | dependent (true) | 子查询中的第一个选择，取决于外部查询 |
| DERIVED | None | 派生表（子查询中产生的临时表） |
| MATERIALIZED | materialized_from_subquery | 物化子查询 |
| UNCACHEABLE SUBQUERY | cacheable (false) | 无法缓存结果的子查询，必须对外部查询的每一行进行重新计算 |
| UNCACHEABLE UNION | cacheable (false) | UNION中属于不可缓存子查询的第二个或以后的选择(请参 UNCACHEABLE SUBQUERY) |
Informations sur la table (pour démonstration ultérieure) :
mysql> show create table t_a; ------+ | t_a | CREATE TABLE `t_a` ( `id` bigint(20) NOT NULL DEFAULT '0', `age` int(20) DEFAULT NULL, `code` int(20) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_code` (`code`), KEY `age_key` (`age`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk | +-------+----------------------------------- ------+ 1 row in set (0.03 sec)
SIMPLE : SELECT simple (n'utilise pas UNION ni de sous-requête, etc.)
mysql> explain select * from t_a where id =1; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.03 sec)
PRIMARY : la plus externe lorsque les requêtes sont imbriquées Requête de la couche
mysql> explain select * from t_a where num >(select num from t_a where id = 3); +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+ | 1 | PRIMARY | t_a | NULL | range | num_key | num_key | 5 | NULL | 6 | 100.00 | Using where; Using index | | 2 | SUBQUERY | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+ 2 rows in set, 1 warning (0.03 sec)
UNION : la deuxième instruction SELECT ou suivante dans UNION
mysql> explain select * from t_a where id =9 union all select * from t_a; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | PRIMARY | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL | | 2 | UNION | t_a | NULL | index | NULL | num_key | 5 | NULL | 9 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 2 rows in set, 1 warning (0.04 sec)
DEPENDENT UNION : la deuxième instruction SELECT ou suivante dans UNION SELECT suivant les instructions dépendent de la requête externe
mysql> explain select * from t_a where id in (select id from t_a where id >8 union all select id from t_a where id =5); +----+--------------------+-------+------------+--------+---------------+---------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+--------+---------------+---------+---------+-------+------+----------+--------------------------+ | 1 | PRIMARY | t_a | NULL | index | NULL | num_key | 5 | NULL | 9 | 100.00 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | t_a | NULL | eq_ref | PRIMARY | PRIMARY | 8 | func | 1 | 100.00 | Using where; Using index | | 3 | DEPENDENT UNION | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | Using index | +----+--------------------+-------+------------+--------+---------------+---------+---------+-------+------+----------+--------------------------+ 3 rows in set, 1 warning (0.08 sec)
UNION RESULT : le résultat de UNION
mysql> explain select num from t_a where id = 3 union select num from t_a where id =4; +----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+ | 1 | PRIMARY | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL | | 2 | UNION | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+ 3 rows in set, 1 warning (0.03 sec)
SUBQUERY : la première sélection dans la sous-requête
mysql> explain select * from t_a where num >(select num from t_a where id = 3); +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+ | 1 | PRIMARY | t_a | NULL | range | num_key | num_key | 5 | NULL | 6 | 100.00 | Using where; Using index | | 2 | SUBQUERY | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+ 2 rows in set, 1 warning (0.03 sec)
SOUS-REQUÊTE DÉPENDANTE : La première sélection dans la sous-requête, dépend de la requête externe
mysql> explain select * from t_a where num in(select num from t_a where id = 3 union select num from t_a where id =4); +----+--------------------+------------+------------+-------+-----------------+---------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+------------+------------+-------+-----------------+---------+---------+-------+------+----------+--------------------------+ | 1 | PRIMARY | t_a | NULL | index | NULL | num_key | 5 | NULL | 9 | 100.00 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | t_a | NULL | const | PRIMARY,num_key | PRIMARY | 8 | const | 1 | 100.00 | NULL | | 3 | DEPENDENT UNION | t_a | NULL | const | PRIMARY,num_key | PRIMARY | 8 | const | 1 | 100.00 | NULL | | NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------------+------------+------------+-------+-----------------+---------+---------+-------+------+----------+--------------------------+ 4 rows in set, 1 warning (0.12 sec)
DERIVED : Table dérivée (générée temporairement dans la sous-requête Table)
mysql> explain select a.id from (select id from t_a where id >8 union all select id from t_a where id =5) a; +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL | | 2 | DERIVED | t_a | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 1 | 100.00 | Using where; Using index | | 3 | UNION | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+ 3 rows in set, 1 warning (0.12 sec)
Affiche à quelle table les données de cette ligne se réfèrent Parfois, il s'agit du vrai nom de la table, parfois il peut s'agir des résultats suivants
système : cette table (peut également être la table temporaire interrogée) n'a qu'une seule ligne de données (= table système). un cas particulier de const
const : La table possède au plus une ligne correspondante, qui sera lue au début de la requête. Puisqu'il n'y a qu'une seule ligne, les valeurs des colonnes de cette ligne peuvent être traitées comme des constantes par le reste de l'optimiseur. Les tables const sont rapides car elles ne sont lues qu'une seule fois ! const est utilisé pour toutes les parties de la requête lorsque la condition est une CLÉ PRIMAIRE ou un index UNIQUE et comparée à une valeur constante.
Dans la requête suivante, tbl_name peut être utilisé pour les tables const :
eq_ref : Pour chaque combinaison de lignes dans les tables précédentes, lisez une ligne de cette table. Outre system et const, c’est le meilleur type de connexion. Il est utilisé lorsque la jointure utilise toutes les parties de l'index et que l'index est une clé primaire ou un index unique non nul. eq_ref peut être utilisé sur des colonnes indexées comparées à l'aide de l'opérateur =. La valeur de comparaison peut être une constante ou une expression utilisant une colonne d'un tableau lu avant ce tableau.
SELECT * from tbl_name WHERE primary_key=1； SELECT * from tbl_name WHERE primary_key_part1=1和 primary_key_part2=2； --例子 mysql> explain select * from t_a where id =1; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.07 sec)
ref Pour chaque combinaison de lignes de la table précédente, toutes les lignes avec des valeurs d'index correspondantes seront supprimées de ce tableau Lire. Utilisez ref si la jointure utilise uniquement le préfixe le plus à gauche de la clé, ou si la clé n'est pas une CLÉ UNIQUE ou PRIMARY (en d'autres termes, si la jointure ne peut pas aboutir à une seule ligne basée sur la requête par mot-clé). Ce type de jointure convient si vous utilisez des clés qui correspondent uniquement à un petit nombre de lignes. ref peut être utilisé sur les colonnes indexées à l'aide des opérateurs = ou <=>
SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column; SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1; --例子（t_b为t_a的复制表，表结构相同） mysql> explain select * from t_a,t_b where t_a.code=t_b.code; +----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+ | 1 | SIMPLE | t_a | NULL | ALL | uk_code | NULL | NULL | NULL | 9 | 100.00 | NULL | | 1 | SIMPLE | t_b | NULL | eq_ref | uk_code | uk_code | 4 | test.t_a.code | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+ 2 rows in set, 1 warning (0.03 sec)
fulltext : Effectuer la jointure à l'aide d'un index FULLTEXT
SELECT * FROM ref_table WHERE key_column=expr; SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column; SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1; --例子（t_b为t_a的复制表，表结构相同） mysql> explain select * from t_a,t_b where t_a.age=t_b.age; +----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+-------------+ | 1 | SIMPLE | t_a | NULL | ALL | age_key | NULL | NULL | NULL | 9 | 100.00 | Using where | | 1 | SIMPLE | t_b | NULL | ref | age_key | age_key | 5 | test.t_a.age | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+-------------+ 2 rows in set, 1 warning (0.03 sec)
ref_or_null : Le type de jointure ref est similaire, mais ajoute la possibilité pour MySQL de rechercher spécifiquement des lignes contenant des valeurs NULL. Ce type d'optimisation de jointure est souvent utilisé pour résoudre des sous-requêtes.
Dans l'exemple suivant, MySQL peut utiliser les jointures ref_or_null pour traiter les ref_tables :
index_merge : ce type de jointure indique que la méthode d'optimisation de fusion d'index est utilisée. Dans ce cas, la colonne clé contient la liste des index utilisés, et key_len contient l'élément clé le plus long de l'index utilisé.
SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL; --例子 mysql> explain select * from t_a where t_a.age =3 or t_a.age is null; +----+-------------+-------+------------+-------------+---------------+---------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+---------------+---------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | t_a | NULL | ref_or_null | age_key | age_key | 5 | const | 2 | 100.00 | Using index condition | +----+-------------+-------+------------+-------------+---------------+---------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning (0.03 sec)
unique_subquery : Ce type remplace la référence de la sous-requête IN sous la forme suivante :
SELECT * FROM ref_table WHERE idx1=expr1 OR idx2 =expr2; --例子 mysql> explain select * from t_a where t_a.code =3 or t_a.age = 3; +----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+ | 1 | SIMPLE | t_a | NULL | index_merge | uk_code,age_key | uk_code,age_key | 4,5 | NULL | 2 | 100.00 | Using union(uk_code,age_key); Using where | +----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+ 1 row in set, 1 warning (0.03 sec)
unique_subquery est une fonction de recherche d'index qui peut remplacer complètement la sous-requête par haute efficacité plus élevée.
value IN (SELECT primary_key FROM single_table WHERE some_expr)
range : Récupère uniquement une plage donnée de lignes, en utilisant un index pour sélectionner les lignes . La colonne clé indique quel index a été utilisé. key_len contient l'élément clé le plus long de l'index utilisé. La colonne ref est NULL dans ce type. Lorsque vous utilisez les opérateurs =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN ou IN pour comparer des colonnes clés avec des constantes, vous pouvez utiliser range
value IN (SELECT key_column FROM single_table WHERE some_expr)index : ce type de jointure est le même que ALL, sauf que seule l'arborescence d'index est analysée. C'est généralement plus rapide que ALL car les fichiers d'index sont généralement plus petits que les fichiers de données. MySQL peut utiliser ce type de jointure lorsque la requête utilise uniquement des colonnes faisant partie d'un seul index.
mysql> explain select * from t_a where id > 8; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_a | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.03 sec)
TOUS : effectuez une analyse complète du tableau pour chaque combinaison de lignes du tableau précédent. Ce n'est généralement pas bon si la table est la première à ne pas être marquée const, et c'est généralement mauvais dans ce cas. Il est généralement possible d'ajouter plus d'index sans utiliser ALL afin que les lignes puissent être récupérées en fonction de valeurs constantes ou de valeurs de colonne dans le tableau précédent.
La colonne possible_keys indique quel index MySQL peut utiliser pour trouver des lignes dans cette table. Notez que cette colonne est complètement indépendante de l'ordre des tableaux affichés dans la sortie EXPLAIN. Cela signifie que certaines clés de possible_keys ne peuvent pas réellement être utilisées dans l'ordre de la table générée.
Si la colonne est NULL, il n'y a pas d'index associé. Dans ce cas, vous pouvez améliorer les performances de votre requête en vérifiant la clause WHERE pour voir si elle fait référence à certaines colonnes ou colonnes adaptées à l'indexation. Si tel est le cas, créez un index approprié et vérifiez à nouveau la requête avec EXPLAIN
La colonne clé affiche la clé (index) que MySQL a réellement décidé d'utiliser. Si aucun index n'est sélectionné, la clé est NULL. Pour forcer MySQL à utiliser ou ignorer l'index sur la colonne possible_keys, utilisez FORCE INDEX, USE INDEX ou IGNORE INDEX dans la requête.
La colonne key_len affiche la longueur de clé que MySQL décide d'utiliser. Si la clé est NULL, la longueur est NULL.
La longueur de l'index utilisé. Plus la longueur est courte, mieux c'est sans perdre en précision
La colonne ref indique quelle colonne ou constante est utilisée avec la clé pour sélectionner les lignes du tableau.
La colonne des lignes indique le nombre de lignes que MySQL pense devoir examiner lors de l'exécution de la requête.
Cette colonne contient les détails de MySQL résolvant la requête, détaillés ci-dessous.
[Recommandations associées : Vidéo MySQL tutoriel]
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!
mysql modifier le nom de la table de données
MySQL crée une procédure stockée
La différence entre MongoDB et MySQL
Comment vérifier si le mot de passe MySQL est oublié
mysql créer une base de données
niveau d'isolement des transactions par défaut de MySQL
La différence entre sqlserver et mysql
mysqlmot de passe oublié
![[Web front-end] Démarrage rapide de Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
