Outil d'optimisation des performances de la base de données Mysql - expliquer le mot-clé

Introduction à l'outil Explain

L'utilisation du mot-clé EXPLAIN peut simuler l'optimiseur exécutant des instructions SQL et analyser les goulots d'étranglement des performances des instructions ou des structures de requête. En ajoutant le mot-clé d'explication avant l'instruction select, MySQL définira une marque sur la requête et l'exécution de la requête renverra les informations du plan d'exécution au lieu d'exécuter SQL.

Exemple d'analyse d'explication

-- actor建表语句：CREATE TABLE `actor` (  `id` int(11) NOT NULL,  `name` varchar(45) DEFAULT NULL,  `update_time` 
datetime DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- film建表语句：CREATE TABLE `film` (  `id` int(11) NOT NULL,  `name` varchar(10) NOT NULL,
PRIMARY KEY (`id`),  KEY `idx_name` (`name`))ENGINE=InnoDB DEFAULT CHARSET=utf8

-- film_actor建表语句：CREATE TABLE `film_actor` (  `id` int(11) NOT NULL,  `film_id` int(11) NOT NULL,  `actor_id` 
int(11) NOT NULL,  `remark` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`),KEY `idx_film_actor_id` (`film_id`,`actor_id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Explication d'exécution :

explain select * from actor;

Si l'instruction select renvoie le résultat de l'exécution, l'ajout d'explication devant l'instruction select renvoie le SQL d'exécution de cette instruction de requête.

Deux variantes d'EXPLAIN

1 expliquer étendu

fourniront une optimisation supplémentaire des requêtes sur la base des informations d'explication. . Utilisez ensuite la commande show warns pour obtenir l’instruction de requête optimisée afin de voir ce que l’optimiseur a optimisé. Il existe également une colonne filtrée, qui est une valeur de demi-rapport. rows*filtered / 100 peut estimer le nombre de lignes qui seront connectées à la table précédente dans l'explication (la table précédente signifie que la valeur id dans l'explication est inférieure à la valeur id de la surface actuelle de la table).

explain EXTENDED select * from actor where id = 1;

2. expliquer les partitions

a un champ de partitions de plus que expliquer. Si la requête est basée sur une table de partition, il affichera les partitions auxquelles la requête accédera.

Expliquez la colonne

colonne id

Le numéro de la colonne id est le numéro de série de la sélection, il y a plusieurs sélections Il existe plusieurs identifiants et l'ordre des identifiants augmente dans l'ordre dans lequel la sélection apparaît.
Plus l'identifiant est grand, plus la priorité d'exécution est élevée. Si l'identifiant est le même, il sera exécuté de haut en bas. Si l'identifiant est NULL, il sera exécuté en dernier.

explain select (select 1 from actor where id = 1) from (select * from filmwhere id = 1) der;

colonne de type select

le type de sélection indique si la ligne correspondante est une requête simple ou complexe.
simple : requête simple. La requête ne contient pas de sous-requêtes ni d'unions.

explain select * from film where id=1

primary : La sélection la plus externe dans une requête complexe
subquery : La sous-requête incluse dans la sélection (pas dans la clause from)
derived : sous-requête incluse dans la clause from. MySQL stockera les résultats dans une table temporaire, également appelée table dérivée

explain select (select 1 from actor where id = 1) from (select * from filmwhere id = 1) der;

union: sellct suivant le mot-clé union.

EXPLAIN select 1 union all select 1;

colonne du tableau

这一列表示explain的一行正在访问哪个表。
当from子句中有子查询时，table列是格式，表示当前查询依赖id=N的查询，于是先执行id=N的查询。
当有union时，UNION RESULT的table列的值为，1和2表示参与union的select行id。

type列

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行对应的大概范围。
依次从最优到最差的分别为：system>const>eq_ref>ref>range>index>All
一般来说，得保证查询达到range级别，最好达到ref。
NULL：MySQL能够在优化阶段分解查询语句，在执行阶段用不着在访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需在执行时访问表

EXPLAIN select min(id) from film;

const、system：mysql能对查询的某部分进行优化并将其转换成一个常量（可看成是show warnings的结果）。用于primay key或unique key的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速读较快。system 是const的特例，表中只有一行元素匹配时为system。

EXPLAIN select * from (select * from film where id= 1) as tmp;

eq_ref：primay key或 unique key索引的所有部分被连接使用，最多只会返回一条符合条件的记录。这可能是const之外最好的联接类型，简单的select查询不会出现这种type。

EXPLAIN select * from (select * from film where id= 1) as tmp;

ref：相比eq_ref，不适用唯一索引，而是使用普通索引或者唯一索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
简单select查询，name是普通索引（非主键索引或唯一索引）

EXPLAIN select * from film where name='film1';

关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分。

EXPLAIN select film_id from film LEFT JOIN film_actor on film.id = film_actor.film_id;

range：范围扫描通常出现在in(), between,>,=等操作中。使用一个索引来检索给定范围的行。

EXPLAIN select * from actor WHERE id >1;

index：扫描全表索引，通常比All快一些

EXPLAIN select * from film;

all：即全表扫描，意味着MySQL需要从头到尾去查找所需要的行。这种情况下需要增加索引来进行优化。

explain select * from actor;

possible_keys列

这一列显示select可能会使用哪些查询来查找。
explain时可能会出现possible_keys有列，而key显示为NULL的情况，这种情况是因为表中的数据不多，MySQL认为索引对此查询帮助不大，选择了全表扫描。
如果该列为NULL，则没有相关的索引。这种情况下，可以通过检查where子句看是否可以创造一个适当的索引来提高查询性能，然后用explain查看效果。

EXPLAIN SELECT * from film_actor where film_id =1;

key列

这一列显示MySQL实际采用哪个索引对该表的访问。
如果没有使用索引，则改列为NULL。如果想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用force index、 ignore index。

key_len列

这一列显示了mysql在索引里使用的字节数，通过这个值可以估算出具体使用了索引中的哪些列。

EXPLAIN SELECT * from film_actor where film_id =1;

film_actor的联合索引idx_film_actor_id由film_id和actor_id两个id列组成，并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。

ken_len计算规则如下：

字符串
char(n)：n字节长度
varchar(n)：n字节存储字符串长度，如果是utf-8， 则长度是3n+2

数值类型
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节

时间类型
date：3字节
timestamp：4字节
datetime：8字节

如果字段允许为NULL，需要1字节记录是否为NULL
索引最大长度是768字节，当字符串过长时，MySQL会做一个类似做前缀索引的处理，将前半部分的字符串提取出来做索引。

ref列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有： const(常量)，字段名等。一般是查询条件或关联条件中等号右边的值，如果是常量那么ref列是const，非常量的话ref列就是字段名。

EXPLAIN SELECT * from film_actor where film_id =1;

row列

这一列是mysql估计要读取并检测的行数，注意这个不是结果集的行数。

Extra列

这一列是额外信息。

Using index：使用覆盖索引（结果集的字段是索引，即select后的film_id）

explain select film_id from film_actor where film_id=1;

Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导的范围

explain select * from film_actor where film_id > 1;

Using where：使用where语句来处理结果，查询的列未被索引覆盖

explain select * from actor where name ='a'

Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般要进行优化，首先要想到是索引优化。

explain select DISTINCT name from actor;

actor.name没有索引，此时创建了临时表来处理distinct。

explain select DISTINCT name from film;

file.name建立了普通索引，此时查询时Extra是Using index，没有用到临时表。

Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。

explain select * from actor order by name;

actor.name未创建索引，会浏览acotr整个表，保存排序关键字name和对应id，然后排序name并检索行记录。

explain select * from film order by name;

film.name建立了idx_name索引，此时查询时extra是Using index。

select tables optimized away：使用某些聚合函数（比如：max、min）来访问存在索引的某个字段

explain select min(id) from film ;

Les étudiants intéressés peuvent visiter le site Web PHP chinois pour en savoir plus sur le contenu associé : 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!