2021年08月13日阅读 11

Mysql的Explain说明

这是我参与8月更文挑战的第13天，活动详情查看：8月更文挑战

因博主最近涉及到Mysql的优化,所以整理一下相关Explain的使用说明

1 Explain的概述

在Mysql的5.6版本后,SELECT,INSERT,UPDATE和DELETE都可以使用EXPLAIN查看Sql的执行计划,查看Sql语句如何使用索引,怎样进行连接表查询,可以帮助选择更好的索引,帮助我们优化Sql语句,提高使用性能.

数据准备

下面两个表没有什么关系,主要用来演示Explain

-- 准备学生表
CREATE TABLE `user` (
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `name` varchar(30) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 准备学生数据
INSERT INTO `test`.`user`(`id`, `name`, `age`, `email`) VALUES (1, 'Jone', 18, 'test1@baomidou.com');
INSERT INTO `test`.`user`(`id`, `name`, `age`, `email`) VALUES (2, 'Jack', 20, 'test2@baomidou.com');
INSERT INTO `test`.`user`(`id`, `name`, `age`, `email`) VALUES (3, 'Tom', 28, 'test3@baomidou.com');
INSERT INTO `test`.`user`(`id`, `name`, `age`, `email`) VALUES (4, 'Sandy', 21, 'test4@baomidou.com');


-- 准备学生日志表
CREATE TABLE `user_log` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `operation` varchar(20) NOT NULL COMMENT '操作类型, insert/update/delete',
  `operate_time` datetime NOT NULL COMMENT '操作时间',
  `operate_id` int(11) NOT NULL COMMENT '操作表的ID',
  `operate_params` varchar(500) DEFAULT NULL COMMENT '操作参数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- 准备学生日志数据
INSERT INTO `test`.`user_log`(`id`, `operation`, `operate_time`, `operate_id`, `operate_params`) VALUES


    
 (1, 'insert', '2021-05-04 15:18:01', 5, '插入后(id:5, name:Billie, age:30, salary:test5@baomidou.com)');
INSERT INTO `test`.`user_log`(`id`, `operation`, `operate_time`, `operate_id`, `operate_params`) VALUES (2, 'update', '2021-05-04 15:18:51', 5, '修改前(id:5, name:Billie, age:30, salary:test5@baomidou.com) , 修改后(id5name:Billie, age:18, salary:test5@baomidou.com)');
INSERT INTO `test`.`user_log`(`id`, `operation`, `operate_time`, `operate_id`, `operate_params`) VALUES (3, 'delete', '2021-05-04 15:18:58', 5, '删除前(id:5, name:Billie, age:18, salary:test5@baomidou.com)');
复制代码

Explain字段初使用

EXPLAIN
SELECT
	ID
FROM
	`user`
复制代码

字段	说明
id	执行顺序.id相同，执行顺序由上至下;id不同,id值越大,优先级越高,越先执行.
select_type	查询类型,用来区别普通查询,联合查询,子查询等查询.
table	查询数据行的表名
partitions	5.7版本后新增字段,使用的哪个分区，需要结合表分区才可以看到.非分区表为null
type	联结类型,反应了查询使用了什么类型.
possible_keys	完成查询,可以使用那些索引
key	显示查询中使用的索引,如果没有,为null
key_len	索引字段的最大可能长度,不是实际使用的长度.同等精度下,长度越短越好.
ref	显示索引实际使用的列,可能是一个常数
rows	查询数据,需要读取的行数,是一个估算值
filtered	5.7版本后新增字段,表示针对符合查询条件的记录数的百分比估算，用rows和filtered相乘可以计算出关联表的行数
Extra	解析查询的附加信息.

2 字段详解

1 id

id的编号是select的顺序号,代表执行顺序,有几个select就有几个id,且id的顺序是按照select的出现顺序增长的.

id相同，执行顺序由上至下;id不同,id值越大,优先级越高,越先执行,id为null最后执行.

案列1

EXPLAIN
SELECT
	ID
FROM
	`user`
	where ID in (SELECT ID FROM user_log)
复制代码

id为2的是where条件后面的子查询,该Sql语句,先执行后面的子查询,再完成外面的查询,所以里面的执行顺序高一些.

案列2

EXPLAIN
SELECT
	t1.ID 
FROM
   `user` t1
   left join  user_log t2 on t1.ID = t2.ID;
复制代码

因为上面Sql语句是连接查询,两个表查询的顺序一直,按照先后顺序,先查询表1,再查询表2.

2 select_type

查询类型,用来区别普通查询,联合查询,子查询等查询.

1 SIMPLE

简单查询,表示查询语句中没有子查询或者UNION(可见案例2)

2 PRIMARY

如果不是简单查询,那么最外层查询就会被称为PRIMARY.即在子查询的外层查询,或者UNION,UNION ALL的最左侧查询.(可见案列1)

3 UNION

对于包含的UNION或UNION ALL的查询,除了最左侧的查询(为PRIMARY), 其余的小查询都是UNION,与类型2组合.

案列3

EXPLAIN
SELECT
	t1.ID 
FROM
	`user` t1
   left join  user_log t2 on t1.ID = t2.ID;
复制代码

最外层,最左侧为PRIMARY,使用UNION连接的,其余的都为UNION.

4 UNION RESULT

Mysql使用临时表来完成UNION查询的去重,针对临时表的查询就为此类型.(可见案例3最后一行)

5 SUBQUERY

不在from里的子查询.

案列4

EXPLAIN SELECT
	* 
FROM
	`user` 
WHERE
	ID = (
	SELECT
		ID 
	FROM
		user 
	WHERE
		ID = 1 
	)
复制代码

子查询的查询语句不能够转为对应的使用IN 或 EXISTS的形式,并且该子查询是不相关子查询.

6 DEPENDENT SUBQUERY

如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是相关子查询.

案列5

EXPLAIN SELECT
	* 
FROM
	`user` 
WHERE
	ID IN (
	SELECT
		ID 
	FROM
		user 
	WHERE
		ID = 1 
	)
复制代码

7 DEPENDENT UNION

在包含UNION或者UNION ALL的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的select_type的值就是DEPENDENT UNION

案列6

EXPLAIN SELECT
	* 
FROM
	`user` 
WHERE
	ID IN (
	SELECT
		ID 
	FROM
		user 
	WHERE
		ID = 1 
	UNION 
		SELECT
		ID 
	FROM
		user_log
	WHERE
		ID = 1 
	)
复制代码

8 DERIVED

用来标记出现在from里的子查询，这个结果会放入临时表中，也叫做派生表。

案列7

EXPLAIN SELECT
	* 
FROM
	( SELECT * FROM user WHERE ID = 1 ) AS t1 
WHERE
	t1.ID = 1
复制代码

id为2的代表子查询的执行方式,他的类型是DERIVED,表示以物化的方式执行的.id为1的table是<derived2>，表示该查询是针对将派生表物化之后的表进行查询的。

9 MATERIALIZED

查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询为MATERIALIZED.

EXPLAIN SELECT * FROM t1 WHERE key1 IN


    
 (SELECT key1 FROM t2);
复制代码

3 table

查询数据行的表名(可见案列1)

4 partitions

5.7版本后新增字段,数据的分区信息,使用的哪个分区，需要结合表分区才可以看到.非分区表为null.

5 type

联结类型,反应了查询使用了什么类型.

按照从优到差排序:

system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL

1 system

只有一条数据的系统表.(可见案例7)

2 const

主键或者唯一索引查询,最后Mysql判断为常量查询

案例8

EXPLAIN SELECT
	ID 
FROM
	`user_log`
	WHERE  ID = 1
复制代码

3 eq_ref

主键或者唯一索引,最多返回一条数据.(可见案列2)

4 ref

最左前缀匹配,非主键和唯一索引,返回多条数据.

案列9

-- 1 给user表中的age字段建立普通索引

-- 2 执行sql
EXPLAIN SELECT
	*
FROM
	`user`
	WHERE  age = 1
复制代码

5 fulltext

fulltext索引

6 ref_or_null

ref的特殊情况,is null多进行一次查询

7 index_merge

索引合并

8 unique_subquery

in (SELECT pk)特殊场景出现.(可见案列1)

9 index_subquery

非唯一索引的unique_subquery.

10 range

范围查询

11 index

按照索引顺序扫描表查询

12 ALL

全表扫描

案列10

EXPLAIN SELECT
	*
FROM
	`user`
复制代码

6 possible_keys

完成查询,可以使用那些索引.(可见案列)

7 key

显示查询中使用的索引,如果没有,为null.(可见案列9,10)

8 key_len

索引字段的最大可能长度,不是实际使用的长度.同等精度下,长度越短越好.(可见案列8,9)

9 ref

显示索引实际使用的列,可能是一个常数(可见案列8,9)

10 rows

查询数据,需要读取的行数,是一个估算值.(可见案列9,10)

11 filtered

5.7版本后新增字段,表示针对符合查询条件的记录数的百分比估算，用rows和filtered相乘可以计算出关联表的行数.

12 Extra

解析查询的附加信息.

1 Using index

使用覆盖索引.

2 Using index condition

使用索引下推，索引下推简单来说就是加上了条件筛选，减少了回表的操作.

3 Using temporary

排序使用了临时表.

4 Using filesort

使用外部索引文件排序.

5 Using where

使用where过滤.

6 Zero limit

使用LIMIT 0

7 Using sort_union()和 Using union()和 sing intersect()

使用了索引合并

8 NULL

查询的列未被索引覆盖，并且where筛选条件是索引的前导列，意味着用到了索引，但是部分字段未被索引覆盖，必须通过回表来实现.

9 No tables used

查询语句的没有FROM子句时将会提示该额外信息.

案列11

EXPLAIN SELECT 1;
复制代码

10 Impossible WHERE

查询语句的WHERE子句永远为FALSE时将会提示该额外信息.

案列12

EXPLAIN SELECT  
	*
FROM
	`user`
	WHERE 1 != 1
复制代码