详解mysql的EXPLAIN命令

2020-08-04 本文已影响0人今年五年级

一简介

使用explain命令可以模拟优化器执行sql语句，从而知道mysql是如何处理我们写的sql语句的，还可以分析你的查询语句或者是结构的性能瓶颈

二注意事项

在select语句之前增加explain关键字以后，mysql会在查询上设置一个标记，执行查询时，会返回执行计划的信息，而不是执行这条sql（如果from中包含子查询，则仍会执行该子查询，将结果放入临时表中）

三数据准备

创建如下3个表，演员，电影，演员电影中间表
电影表对电影名称name字段创建普通索引
演员表对电影id和演员id联合辅助索引，非联合主键索引

DROP TABLE IF EXISTS `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;

INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017-12-22 15:27:18'), (2,'b','2017-12-22 15:27:18'), (3,'c','2017-12-22 15:27:18');

DROP TABLE IF EXISTS `film`;
CREATE TABLE `film` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)    // 电影名称索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');

DROP TABLE IF EXISTS `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`)     //电影id和演员id联合辅助索引，非联合主键索引！！！
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);

四 explain结果每一列解析

4.1 id列

id列的编号是select的序列号，有几个select就会有几个id，并且id的顺序是按照select出现的顺序来顺序增长的，mysql将select查询分为简单查询（SIMPLE）和复杂查询（PRIMARY）
复杂查询分为三类

简单子查询
派生表（from语句中的子查询）
union查询
id列越大，执行优先级越高，id相同则从上往下执行，id为null最后执行

（1）简单子查询

explain select( select 1 from actor limit 1) from file

（2）from字句中的子查询

explain select id from (select id from film) as der

查询执行时有临时表别名为der，外部select查询引用了该临时表

（3）union查询
见4.2节union案例，union实际用的不太多

union结果总是放在一个匿名临时表中，临时表不在SQL中出现，因此它的id是NULL

4.2 select_type列

select_type 表示对应行是简单还是复杂的查询，如果是复杂的查询，又是上述三种复杂查询中的哪一种

explain select * from film where id = 1

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

DERIVED：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为衍生表/派生表（derived的英文含义）
SUBQUERY：包含在 select 中的子查询（不在 from 子句中）
PRIMARY：复杂查询中最外层的 select，使用了id为3的查询结果衍生出来的表

explain select 1 union all select 1

union：在 union 中的第二个和随后的 select
union result：从 union 临时表检索结果的 select

4.3 table列

改行表示explain的一行sql正在访问哪个表
当from子句中有子查询时，table列是<derivedN>格式，表示当前查询依赖id=N的查询，因此先执行id=N的查询
当有union时，union result的table列的值为<union1,2>,1和2表示参与union的select的id

4.4 type列（重要）

关联类型，表示mysql将如何找到查找表中的行，查询数据行的大概范围
结果从最好到最坏依次是
system>const>eq_ref>ref>range>index>ALL
一般需要保证查询达到range级别，最好达到ref

（1）system

表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略不计

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

（2）const

表的查询结果只有一行，一般出现在主键索引和唯一索引上，因为是唯一，所以读取1次，速度比较快

select * from film where id = 1

（3）eq_ref（const之外最好的性能）

表连接的时候，关联的b表的主键索引或者唯一索引，b表根据主键索引或者唯一索引最多返回一条记录，这样关联的效率高

Mysql执行计划中eq_ref和ref类型的区别
 MySQL解释了eq_ref和ref类型的含义
这个JOIN非常快，因为对于表A中扫描的每一行，表B中只能有一行满足JOIN条件。一个，不超过一个。那是因为B.id是独一无二的。在这里，您是一个伪代码，它说明了服务器端的处理：
foreach (rowA in A)
{
   if (existsInBRowWithID(rowA.id)
   {
       addToResult(rowA.text, getRowInBWithID(rowA.id).text);
       break;
   }
}

explain select * from film_actor a left join film b on a.film_id=b.id

（4）ref

表连接的时候，关联的b表采用普通辅助索引（非唯一索引）或者唯一索引的部分前缀，一般都是用在多表连接上的，关联的字段不是主键索引或者唯一索引即a left join b on a.filmname=b.name

此JOIN不如前一个er_ref快，因为对于表A中扫描的每一行，表C中有几个可能的行，它们可以满足JOIN条件(上面的循环中没有中断)。那是因为C.ID不是独一无二的
foreach (rowA in A)
{
   foreach (rowC in C)
   {
       if (rowA.id == rowC.id)
       {
           addToResult(rowA.text, rowC.text);
       }
   }
}

（4.1）简单 select 查询，name是普通索引（非唯一索引）

explain select * from film where name='film1'

使用唯一索引（
索引名：idx_film_actor_id |
构成： file_id和actor_id
）的部分前缀film_id

explain select * from film_actor where film_id=1

explain select * from film_actor where actor_id=1

（4.2）关联表查询

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;

eq_ref 和 ref的核心对比

A表的基础：A表的非主键字段
A表的外键关联了B表的主键
A表的外键关联了B表的非主键字段，B表对应A表外键字段的记录有多条

（5）range

范围扫描通常出现在范围查找中，比如in()，between ，>， <， >= ，<=等操作中，使用一个索引来检索给定的行

explain select * from actor where id > 1

之所以范围查找速度慢，原因一：查询出的记录多，原因二：比如上面的id>1，除了第一条记录id=1，其他记录都大于1，等于进行了全表扫描

（6）index

会扫描全表索引，通常比ALL速度快，原因是(select * ,* 是id和name，通过name辅助索引树找到对应name的记录，然后获取主键索引id，都是在索引树上查询)index是在索引中读取所有记录（可以设置把索引都加载到内存中），而ALL是在硬盘上读取

explain select * from film;

film表2个字段，id字段为主键索引，name字段为普通辅助索引，要想是index，则必须表的所有字段都有索引

（7）ALL

不会走索引，顺序从第一行到最后一行查找所需要的行，全表扫描，这种情况需要添加索引优化

explain select * from actor;

之所以这次是ALL不是index，主要是因为actor表3个字段，id，name，update_time只有id有主键索引，其他两个字段上都没索引，所以无法采用全表索引查询
尝试只查询id，name

EXPLAIN select id,name from actor

为name字段加上索引再执行

4.5 possible_keys列

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

4.6 key列

意思是这次查询实际使用哪个索引来提高查询性能，如果没有使用索引，该列为null没如果想让mysql强制使用possible_keys列中的索引，可以使用force index

可能出现possible_keys有值，而key为null的情况，这种情况一般发生在表中记录不多，mysql认为使用索引对查询帮助不大，因此选择了全表扫描

4.7 key_len列

本列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列
比如，file_actor表联合索引idx_file_actor_id由 film_id 和 actor_id 两个int列组成，并且每个int是4个字节

explain select * from film_actor where film_id = 2;

通过上图中结果中的key_len=4可推断出查询使用了联合索引的第一个列file_id列
如果加上另一个字段

explain select * from film_actor where film_id = 2;

可以看出这次用了联合索引的全部字段

附注：key_len计算规则：

字符串
char(n) ：n字节长度
varchar(n)：2字节存储字符串的长度，如果是utf-8，则长度为3n+2字节
数值类型
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节
时间类型
date：3字节
timestamp：4字节
datetime：8字节
如果字段允许值为null，则需要1个字节记录是否为null

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引

4.8 ref列

ref列展示的就是与索引列作等值匹配的东东是个啥，比如只是一个常数或者是某个列
由key_len可知，t1表的idx_col_col2被充分利用，col1匹配t2表的col1，col2匹配了一个常数

常见的有：const（常量），字段名（例：film.id）

explain select * from film_actor a left join film b on a.film_id=b.id

用到了b表的主键索引，b表主键索引相比较的是a表的film_id,study是数据库名称

4.9 rows列

根据表统计信息及索引选用情况，mysql大致估算出要读取并检测的行数，该行数不是结果集中的行数
查询优化器全表扫描的时候，代表的是预计需要扫描的行
如果使用索引来查询是，代表的是预计扫描的索引记录的行数。

4.10 extra列

包含不适合在其他列中显示但十分重要的额外信息
详细解读

using index
覆盖索引：索引包含了所有要查询的字段
使用了覆盖索引(Covering Index)，查询的列被索引覆盖（查询的列都位于联合索引里面，由联合索引数据结构，省去了根据索引key来找到索引值的步骤，直接从key获取），并且where筛选条件是索引的前导列（最左前缀的字段），是性能高的表现，对于innodb，如果该联合索引是辅助索引的话，会提高性能

explain select film_id from film_actor where film_id=1

using where;using index
使用了覆盖索引，查询的列被索引覆盖，并且where筛选条件是索引列之一，但是不是索引的前导列，意味着不能直接通过索引找到结果，但是可以通过索引找到select后的字段

explain select film_id from film_actor where actor_id=1

using where
未使用覆盖索引，查询的列未被索引覆盖/未被完全覆盖，且where筛选条件非索引的前导列

explain select remark from film_actor where actor_id=1

explain select * from actor where name='a'

上面的这个* 既包含了有索引的列id，还包含了没有索引的name和updatetime，因此查询的列未完全被索引覆盖，且where使用的条件name也没索引，优化的话：直接name创建索引，不用select * 改用select name

null
查询的列未完全被索引覆盖（部分字段未被索引覆盖），但是where筛选条件是联合索引的前导列，意味着使用了索引，但是部分字段未被索引覆盖，不是纯粹的用到了索引（select后面字段没用），也不是纯粹的没用索引（where后面的用了）

explain select * from film_actor where film_id = 1;  //或者* 换成remark

using index condition
类似于using where，未使用覆盖索引，查询的列未被索引覆盖/未被完全覆盖，并且where条件是联合索引前导列的范围

explain select * from film_actor where film_id>1  // 有问题，最后是using where

using temporary
mysql需要创建一张临时表来处理查询，会拖慢速度，一般出现在distinct语句，group by，子查询。出现这种情况一般要进行优化，使用覆盖索引来进行优化
actor表的name没有索引，此时系统创建了张临时表来distinct

explain select distinct name from actor;

film表的name有索引，因此直接用索引查找，并未使用到临时表

explain select distinct name from film

using filesort
using filesort：在使用order by关键字的时候，如果待排序的内容不能由所使用的索引直接完成排序的话，那么mysql有可能就要进行文件排序。。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息，这种情况需要索引优化
因为索引已经是排好序的，无论是主键索引还是辅助索引还是联合索引，都是从索引数据结果B+树上看都是从左往右递增的，因此使用了索引就不会出现using filesort。
actor的name字段没有索引，会浏览actor整个表，保存排序关键字name和对应的id对应关系，然后排序name并检索行记录

film的name字段有索引，索引本来就是有顺序的，直接按照索引顺序拿值即可

详解mysql的EXPLAIN命令

一简介

二注意事项

三数据准备

四 explain结果每一列解析

4.1 id列

4.2 select_type列

4.3 table列

4.4 type列（重要）

（1）system

（2）const

（3）eq_ref（const之外最好的性能）

（4）ref

（4.1）简单 select 查询，name是普通索引（非唯一索引）

（4.2）关联表查询

eq_ref 和 ref的核心对比

（5）range

（6）index

（7）ALL

4.5 possible_keys列

4.6 key列

4.7 key_len列

4.8 ref列

4.9 rows列

4.10 extra列

猜你喜欢

热点阅读

详解mysql的EXPLAIN命令

一 简介

二 注意事项

三 数据准备

四 explain结果每一列解析

4.1 id列

4.2 select_type列

4.3 table列

4.4 type列（重要）

（1）system

（2）const

（3）eq_ref（const之外最好的性能）

（4）ref

（4.1）简单 select 查询，name是普通索引（非唯一索引）

（4.2）关联表查询

eq_ref 和 ref的核心对比

（5）range

（6）index

（7）ALL

4.5 possible_keys列

4.6 key列

4.7 key_len列

4.8 ref列

4.9 rows列

4.10 extra列

猜你喜欢

热点阅读

一简介

二注意事项

三数据准备