MySQL的explain语句分析
一、Explain概述
Mysql所提供的explain关键词是用于调优排患的一个神器，通过它可以分析查询语句的执行情况，DBA可以通过分析语句的执行结果对查询语句甚至表结构进行优化，例如添加索引，修改索引，使用覆盖索引等等。
二、Explain结构
创建一张学生表和一张成绩表：
CREATE TABLE `student_info`(
`id` int not null AUTO_INCREMENT comment '学生学号',
`stu_name` VARCHAR(10) NOT NULL comment '学生姓名',
`age` tinyint not null comment '年龄',
`gender` VARCHAR(1) not null comment '性别',
PRIMARY KEY (id),
key idx_stu_name(stu_name)
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;
INSERT INTO student_info(stu_name,age,gender)
VALUES ('张三',20,'男'),('李四',18,'男'),('韩梅梅',20,'女'),('李华',101,'女');
CREATE TABLE `score_info`(
`id` int not null AUTO_INCREMENT comment '成绩单号',
`stu_id` int not null comment '学生学号',
`score` tinyint not null comment '分数',
PRIMARY KEY (id),
KEY idx_stu_id(stu_id)
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;
INSERT INTO score_info(stu_id,score)
VALUES (1,59),(2,60),(3,80),(4,100);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
查询所有的数据，并使用explain分析执行情况：
mysql> explain select * from student_info \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student_info
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
字段含义：
id：Select语句的标识符，代表查询语句的顺序
select_type：查询的类型（单个查询，还是有连接union）
table：使用的哪张表
partitions：使用哪个分区
type：查询的方式或者访问方式
possible_keys：可能使用的索引
key：实际使用的索引
key_len：索引长度
ref：使用哪个列与索引一起从表中选择
rows：结果又多少行
filtered：数据被过滤之后，剩余结果的百分比
Extra：额外的执行情况描述
下面着重分析几个非常重要的字段的作用。
三、字段详解
select_type：表示查询的类型，主要有SIMPLE,PRIMARY,UNION,SUBQUERY，分别表示不使用表连接，主查询，UNION中之后的查询，子查询中的第一个，如下：
mysql> explain select * from student_info where id not in (select id from student_info)\G;
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: student_info
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: student_info
partitions: NULL
type: unique_subquery
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: func
rows: 1
filtered: 100.00
Extra: Using index
2 rows in set, 1 warning (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
type：表示查询的方式或者访问的方式，常见的取值有：
-
system：表中仅仅只有一条匹配行
-
const：针对主键或者唯一索引的扫描，且结果仅仅返回一行数据
mysql> explain select id from student_info where id =1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student_info
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- ref：通常出现在多表的连接查询，使用了普通索引，或者使用了前缀扫描规则：
mysql> explain select * from student_info st inner join score_info sc on st.id=sc.stu_id where st.id=1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: st
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: sc
partitions: NULL
type: ref
possible_keys: idx_stu_id
key: idx_stu_id
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
2 rows in set, 1 warning (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- range：在索引范围查询，且使用了<、<=、>、>=、between语句：
mysql> explain select * from student_info where id>1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student_info
partitions: NULL
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 3
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- index：表示扫描整个的索引文件，在索引树中可以找到所需要的数据：
mysql> explain select stu_name from student_info\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student_info
partitions: NULL
type: index
possible_keys: NULL
key: idx_stu_name
key_len: 32
ref: NULL
rows: 4
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- ALL：表示全表扫描，这是相当于没有使用任何的索引列，因此非常耗时，建了索引却不用，那索引的意义何在呢，因此必须要调整：
mysql> explain select * from student_info\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student_info
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
key：查询中所使用到的索引，如果建了索引，且key为空时，相当于未使用索引，需要进行调整
extra: 一些额外的信息，主要有一下几种类型：
- Using index：表示使用了覆盖索引扫描即所查询的列上都建立了索引，是非常良好的一种情况：
mysql> explain select id,stu_name from student_info\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student_info
partitions: NULL
type: index
possible_keys: NULL
key: idx_stu_name
key_len: 32
ref: NULL
rows: 4
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- Using filesort：表示一些额外的排序工作不能通过索引完成，需要占用cpu资源去完成，应当优化：
mysql> explain select * from student_info order by stu_name\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: student_info
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
filtered: 100.00
//stu_name字段上并未建立排序规则，因此需要mysql自身对结果进行排序，耗费cpu资源
Extra: Using filesort
1 row in set, 1 warning (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- Using tempory：表示使用了临时表，多出现于连表查询的情况，需要优化：
mysql> explain select * from student_info st inner join score_info sc on st.id=sc.stu_id order by st.id\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: st
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 4
filtered: 100.00
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: sc
partitions: NULL
type: ALL
possible_keys: idx_stu_id
key: NULL
key_len: NULL
ref: NULL
rows: 4
filtered: 25.00
Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- Using where：出现了回表情况，如上
