mysql中关键字explain

创建数据库和表

use explain_test;
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;

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;

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;

通过例子阐述explain关键字的列

set session optimizer_switch='derived_merge=off'; #关闭mysql5.7新特性对衍生表的合并优化
explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;

explain中关键字段语义

1、ID列

一个sql语句中有几个select就有几个id，id数值越大越优先执行，id值也可能相同说明执行优先度一样。

2、select_type列

1）simple：简单查询。查询不包含子查询和union

2）primary：复杂查询中最外层的 select

3）subquery：包含在 select 中的子查询（不在 from 子句中）

4）derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（ derived的英文含 义）

5）union：在 union 中的第二个和随后的 select （包含union查询）

3、table

表示explain的一行是哪个表。

4、type

type表示关联类型或者访问类型，最优到最差依次为：system>const>eq_ref>ref>range>index>ALL；咋一看不好理解。

（1）system、const：mysql能对查询的某部分进行优化并将其转化为一个常量。where中过滤条件字段为唯一主键索引（PRIMARY KEY）

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

 system是const的特例，表示带查询的表里只有一条数据

 

 （2）eq_ref：eq表示equal，ref表示关联字段（这是我的理解方便记忆）。连接的字段是唯一主键索引

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

 film中id关联film_actor中film_id就表示eq_ref连接类型。

（3）ref：查询条件的字段不是唯一索引。

explain select * from film where name = 'film1';
explain select film_id from film left join film_actor on film.id = film_actor.film_id;

第一个sql：name为普通索引

第二个sql：file_actor中film_id为联合索引

（4）range表示范围通常出现在in，between，>，<，>=等操作中

（5）index扫描索引就能拿到结果的，一般是扫描二级索引（非聚集索引），这种扫描不会从索引的根节点开始扫描，而是直接扫描或者遍历叶子结点。比如覆盖索引：

 （6）ALL表示全表扫描。扫描聚集索引的所有叶子结点

 5、possible_keys

possible_keys列表示mysql在分析sql语句时可能会用到的索引，但是实际并不一定会使用。

当出现possible_keys列有值，但是key显示null时，这种情况表示表中数据量少，mysql认为走索引还没有全表查询快。如果为null，表示没有可用的索引

6、key

key表示该查询使用的哪个索引。如果没有使用查询则为null。如果想强制使用索引，可以使用force index

7、key_length 

表示使用的索引字节数。

char(n)：3n；varchar(n)：3n+2；tinyint：1字节；smallint：2字节；int：4字节；bigint：8字节；date：3字节；timestamp：4字节；datetime：8字节；如果字段允许为null，需要1字节表示是否为null。

8、ref 

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）

9、rows

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

10、Extra

表示额外信息。

（1）using index：使用覆盖索引，覆盖索引表示本次查询的结果集全部可以从某个索引中查询到。

（2）using where：使用where语句来查询，但是查询的列未被索引覆盖。

（3）using index condition：表示where中过滤字段是覆盖索引的左侧一部分，不完全被索引覆盖

explain select * from film_actor where film_id > 1;

（4）using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般需要使用索引来优化

explain select distinct name from actor; ##name字段没有创建索引
explain select distinct name from film; ##name字段创建了索引

 可以看到没有创建索引的字段列会创建临时表来处理。

（5）using filesort：表示使用外部排序而不是索引排序，如果数据量较小时，会从内存排序，数据量较大时先保存到磁盘然后排序。

explain select * from actor order by name;

 actor.name 没有创建索引，会全局扫描聚集索引保存name和id，然后排序name和检索行记录

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

explain select min(id) from actor;

mysql索引实战 

创建表并插入测试数据，其中创建了主键索引id和联合索引（idx_name_age_position）

use explain_test;
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW()); 
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

1、全值匹配——匹配联合索引示例

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';

 name字段查询匹配联合索引，测试结果得知查询走了联合索引name字段；包括下面两个sql都会走联合索引。

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';

2、最左前缀原则

下面sql只有第一个会走联合索引。

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE position ='manager';

3、函数、类型转换、计算会导致索引失效转为全表扫描

（1）使用函数导致索引失效

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';
EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';

（2）时间字段转换

在hire_time字段增加索引，并在where条件中使用date()函数转换hire_time字段长度，由于创建hire_time字段类型的索引时，不存在date()函数转换后的值，所以不会走索引。

ALTER TABLE `employees` ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE;
EXPLAIN select * from employees where date(hire_time) ='2022‐01‐10';

将上面sql进行优化，转为范围查询：

EXPLAIN select * from employees where hire_time >='2022‐01‐10 00:00:00' and hire_time <='2022‐01‐10 23:59:59';

此时查询就有可能会走索引，因为如果数据量较少时会选择不走索引，如果数据量比较大，mysql可能会有索引，具体取决于mysql在执行sql前计算出来的cost成本来决定走不走索引。

4、查询数据使用范围查询时，无法使用联合索引中范围查询右边的字段

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager'; 
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';

第一个sql的key_len:140；而第二个为78，只用了两个字段

5、尽量使用覆盖索引，减少回表，不适用select * 语句

6、mysql在使用 !=、<>、not in、not exists不会使用索引导致全表扫描。而<,>,<=,>=会根据具体情况是否使用索引

7、is null、is not null一般情况下也无法使用索引

8、like以通配符开头的会使索引失效导致全表扫描

        如何解决like '%abc%'不会使用索引？

（1）使用覆盖索引：（原因参考这里）

EXPLAIN SELECT name,age,position FROM employees WHERE name like '%abc%';

因为查询字段在覆盖索引中都能找到，所以不需要回表查询，扫描覆盖索引即可。

using index ：使用覆盖索引的时候就会出现
using where：未使用索引，需要全表查询
using index condition：查找使用了索引，但是需要回表查询数据
using index & using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据。 

 （2）借助搜索引擎

9、字符串不加单引号索引失效

10、少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，详见范围查询优化

11、范围查询优化

新增age字段索引，根据age范围查询，可能因为范围太大导致查询的数据很多导致最终没有走索引而选择全表扫描。

 ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE ;
 explain select * from employees where age >=1 and age <=2000;

对范围很大的查询条件进行拆分，拆分为多个小的范围查询，使单次查询数据量减少，mysql可能就会走索引。

explain select * from employees where age >=1 and age <=1000;
explain select * from employees where age >=1001 and age <=2000;

mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。