mysql中explain详解

工具介绍：

        使用explain关键字可以模拟优化器执行sql语句，分析查询语句的性能。注意，此关键字并不会去执行sql，而是返回执行计划信息

explain中每列的信息介绍：

        示例建表sql：

CREATE TABLE actor  (
  `id` int(11) NOT NULL,
  `name` varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `mytest`.`actor`(`id`, `name`, `update_time`) VALUES (1, 'q', '2021-09-26 15:07:01');
INSERT INTO `mytest`.`actor`(`id`, `name`, `update_time`) VALUES (2, 'w', '2021-09-16 15:07:11');
INSERT INTO `mytest`.`actor`(`id`, `name`, `update_time`) VALUES (3, 'e', '2021-09-06 15:07:20');

CREATE TABLE film (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

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

CREATE TABLE film_actor  (
  `id` int(11) NOT NULL,
  `film_id` int(11) NOT NULL,
  `actor_id` int(11) NOT NULL,
  `remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_film_actor_id`(`film_id`, `actor_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `mytest`.`film_actor`(`id`, `film_id`, `actor_id`, `remark`) VALUES (1, 1, 1, NULL);
INSERT INTO `mytest`.`film_actor`(`id`, `film_id`, `actor_id`, `remark`) VALUES (2, 1, 2, NULL);
INSERT INTO `mytest`.`film_actor`(`id`, `film_id`, `actor_id`, `remark`) VALUES (3, 2, 1, NULL);

        执行explain

explain select * from auth_user where id = 1;                       

 

 id列：

        每个select都会对应一个id

        id值越大，执行优先级越高

        相同的id值，从上往下依次执行

select_type列：

        simple：简单查询，不包含子查询和union

explain select * from actor where id =1;

        primary：复杂查询的最外层查询（如下图）

        subquary:包含在select中不包含在from的子查询（如下图）

explain select (select 1 from actor) from actor where id =1;

         derived：包含在from中的子查询，mysql会把这些数据放到临时表中，也称为派生查询。

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

 ps:执行explain select * from (select * from actor)c where id =1; 这条语句时，如果mysql版本时5.7及以上，需要关闭mysql对衍生表的优化，否则只会出现一个简单查询

set session optimizer_switch='derived_merge=off';

        union：在union中第二个和随后的select

explain select * from actor where id =1 union select * from actor where id =2;

table:表示explain正在访问哪张表

type：表示关系类型或者访问类型，即mysql如何查找表中的行

执行效率：system > const > eq_ref > ref > range > index > all

NULL：mysql在优化阶段，分解查询语句，在执行阶段不用访问索引树或者表的查询类型

        system：是const的特例，当表中只有一条数据时的const查询是system

        const:mysql对查询的某部分优化并将其转化为一个常量，用于primary key与常量比较时，表最多返回一条记录。

explain select * from actor where id = 1;

        eq_ref:primary key 或者unque key索引的所有部分被引用，最多只会返回一条记录，简单的select查询不会出现这种type

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

        ref：不实用唯一索引，使用普通索引或者唯一索引的前缀部分，索引和某个值比较，可能会返回多行

简单查询：

explain select * from film_actor where film_id=1;

name是普通索引

关联查询：

explain select film_id from film left join film_actor on film.id = film_actor.film_id;

idx_film_actor_id是film_id和actor_id的联合索引,此处用到了 film_actor的左边前缀film_id部分
 

         range：范围扫描，通常出现在in between   

explain select * from actor where id > 2;

explain select * from actor where id between 2 and 3;

        index:扫描全索引就能拿到结果，一般扫描的是二级索引，这种扫描不会从根节点开始快速查找，而是直接对二级索引的子节点遍历扫描。速率是比较慢的，这种查询一般为使用为覆盖索引，二级索引一般比较小，比all快一些

explain select * from film;

         all:全表扫描，扫描聚簇索引的所有字节点，一般这种情况是需要优化的

explain select * from actor;

 possible_keys:这一列显示可能用到哪些索引。

explain有时会出现possible_key有值，但key列无值的情况，这种情况是表中的数据量不多，mysql认为索引对此查询作用不大，选择了全表扫描

如果该列是null，则没有相关索引，这种情况可以在where条件中选择建一个合适的索引，在进行expain分析

key：mysql在查询过程中使用到的索引

强制mysql使用possiable_key中的索引可以使用force index

强制mysql不实用索引可以使用ignore index；

key_len:mysql在查询中使用索引的长度

        key_len计算规则：

                字符串：char(n) varchar(n)，n代表字符数，不是字节数，utf-8中

                char(n) 就是3n字节

                varchar(n)是3n+2个字节 2用来存储字符串的长度（因为varchar是可变长的）

                数值类型：

                tinyint：1字节

                smallint：2字节

                int：4字节

                bigint：8字节

                时间类型：

                date：3字节

                timestamp：4字节

                datetime：8字节

        如果字符串可以为null需要1字节记录字符串是否weinull

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

ref列：显示在key列记录的索引中，表查找时所用到的列或常量，常见的有 const 常量，字段名 film.id

explain select * from film where id =3;

explain select film_id from film left join film_actor on film.id = film_actor.film_id;

extra:额外信息，常见的重要信息如下：

        useing index：使用覆盖索引

explain select * from film;

覆盖索引定义：

        mysql执行计划中key有使用索引，如果select的字段都可以从这个索引树中查询出来，这种情况一般可以说是用到了覆盖索引，extra里一般都有useing index。覆盖索引一般针对的是二级索引，这个查询结果都可以从这个索引树中找出，不用再次回表查询。（如果film中在增加一个字段，索引不变，那么这个sql就不会用到覆盖索引）

        using where：

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

explain select film_id,actor_id,id from film_actor where film_id = 1;

在idx_film_actor_id索引树中包含主键id，使用了覆盖索引

 

         using index condition:查询的列不完全被索引覆盖

explain select film_id,actor_id,remark from film_actor where film_id >1;

在idx_film_actor_id索引树中不包含remark字段，查询的列不完全被索引覆盖。

         useing temporary：mysql需要创建一张临时表来处理查询，这种情况一般是需要优化的，一般使用覆盖索引来优化

explain select distinct name from actor;

 explain select distinct name from film;

        using filesort:将用外部排序而非索引排序，数据较小时在内存排序，较大时使用文件排序。这种情况通常需要优化

explain select * from actor order by name;

explain select * from film order by name;