EXPLAIN的作用
1、EXPLAIN语句提供有关MySQL如何执行语句的信息。EXPLAIN可以处理SELECT、DELETE、INSERT、REPLACE和UPDATE语句；
2、EXPLAIN为SELECT语句中使用的每个表返回一行信息。它按照MySQL在处理语句时读取它们的顺序列出输出中的表；
3、对于SELECT语句，EXPLAIN会生成扩展信息，这些信息可以在EXPLAIN之后通过“SHOW WARNINGS”查看。
使用的库表
create table subject
(
id int(10) not null auto_increment,
name varchar(20) not null,
teacher_id int(10) not null comment '与teacher表的id关联',
primary key (id),
index idx_teacher_id (teacher_id)
)engine=innodb comment'学科表';
create table teacher
(
id int(10) not null auto_increment,
name varchar(20) not null,
teacher_no varchar(20) not null,
primary key (id),
unique index unx_teacher_no (teacher_no(20))
)engine=innodb comment'教师表';
create table student
(
id int(10) not null auto_increment,
name varchar(20) not null,
student_no varchar(20) not null,
primary key (id),
unique index unx_student_no (student_no(20))
)engine=innodb comment'学生表';
create table student_score
(
id int(10) not null auto_increment,
student_id int(10) not null comment '与student表的id关联',
subject_id int(10) not null comment '与subject表的id关联',
score int(10) not null,
primary key (id),
index idx_student_id (student_id),
index idx_subject_id (subject_id)
)engine=innodb comment'学生成绩表';
insert into student(id,name,student_no) values (1,'zhangsan','20200001'),(,2'lisi','20200002'),(3,'yan','20200003'),(4,'dede','20200004');
insert into teacher(id,name,teacher_no) values(1,'wangsi','T2010001'),(2,'sunsi','T2010002'),(3,'jiangsi','T2010003'),(4,'zhousi','T2010004');
insert into subject(id,name,teacher_id) values(1,'math',1),(2,'Chinese',2),(3,'English',3),(4,'history',4);
insert into student_score(student_id,subject_id,score) values(1,1,90),(1,2,60),(1,3,80),(1,4,100),(2,4,60),(2,3,50),(2,2,80),(2,1,90),(3,1,90),(3,4,100),(4,1,40),(4,2,80),(4,3,80),(4,5,100);
explain的执行效果
mysql> explain select * from subject where id = 1 \G
******************************************************
id: 1
select_type: SIMPLE
table: subject
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra: NULL
******************************************************
explain包含的字段
1. id //select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
2. select_type //查询类型
3. table //正在访问哪个表
4. type //访问的类型
5. possible_keys //显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到
6. key //实际使用到的索引，如果为NULL，则没有使用索引
7. key_len //表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
8. ref //显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
9. rows //根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数
10. Extra //包含不适合在其它列中显示但十分重要的额外信息
id字段
- id相同
执行顺序从上至下
explain select * from subject
where teacher_id in (select id from teacher where id=1);
读取顺序：teacher > subject
- id不同
如果是子查询，id的序号会递增，id的值越大优先级越高，越先被执行
explain select * from subject
where teacher_id = (select id from teacher where id=1);
读取顺序：teacher > subject
- id相同又不同
id如果相同，可以认为是一组，从上往下顺序执行
在所有组中，id值越大，优先级越高，越先执行
explain select * from student_score
where subject_id in (select id from subject
where teacher_id=(select id from teacher where id=1));
读取顺序：2.teacher > 2.subject > 1.subject > 1.teacher
select_type字段
- SIMPLE
简单查询，不包含子查询或Union查询
explain select * from subject;
- PRIMARY
查询中若包含任何复杂的子部分，最外层查询则被标记为主查询
explain select * from student_score
where subject_id in (select id from subject
where teacher_id=(select id from teacher where id=1));
- SUBQUERY
在select或where中包含子查询
explain select * from student_score
where subject_id in (select id from subject
where teacher_id=(select id from teacher where id=1));
- DERIVED
在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL
会递归执行这些子查询，把结果放在临时表中
explain select t.* from
(select id,name,teacher_id from subject where id=1
union all select 5 id,'test' name,5 teacher_id) t;
- UNION
若第二个select出现在uion之后，则被标记为UNION
explain select t.* from
(select id,name,teacher_id from subject where id=1
union all select 5 id,'test' name,5 teacher_id) t;
- UNION RESULT
从UNION表获取结果的select
explain select id,name,teacher_id from subject
union select 5 id,'test' name,5 teacher_id;
这个union会导致使用临时表保存union的结果，因为union在MySQL中需要去除相同的行。如果union本身就是唯一的、应用程序可以做去重处理或者应用程序允许重复，那么应该使用union all。union all由于不需要去重，也就把查询到的数据直接放入结果集里了。
type字段
从最好到最坏情况，其可以取如此的值，
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL
一般来说我们至少要做到range。
-
system
表只有一行记录（等于系统表），这是const类型的特列，平时不大会出现。 -
const
表最多有一个匹配行。因为只有一行，所以优化器的其余部分可以将该行中列的值视为常量。常量非常快，因为它们只被读取一次。将主键或唯一索引的所有部分与常量值进行比较时，type就会是const。
explain select id,name,teacher_id from subject where id=1;
- eq_ref
当连接使用索引的所有部分，并且索引是主键或唯一的非空索引时，将使用该索引。
explain select subject.* from subject
straight_join teacher on subject.teacher_id = teacher.id;
- ref
使用了主键索引或者唯一索引的最左前缀的一部分，或者使用了非唯一索引的等值查询：
explain select subject.* from subject where teacher_id=1;
-
fulltext
使用了全文索引 -
ref_or_null
类似ref，但是使用索引的列允许为空。如果把表subject的teacher_id列改为允许为空，那么将会出现此类型
explain select subject.* from subject where teacher_id=1 or teacher_id is null;
-
index_merge
表示使用了索引合并的优化方法，更多细节见官方文档 -
range
只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引
一般就是在你的where语句中出现=、<>、>、>=、<、<=、is NULL、<=>、BETWEEN、LIKE或IN（）等的查询。
explain select * from subject where id between 1 and 3;
- index
Full index Scan，Index与All区别：index只遍历索引树，通常比All快
因为索引树通常比主键索引树要小，遍历索引树自然要快一些。
explain select count(*) from subject;
- ALL
Full Table Scan，将遍历全表（主键索引树）以找到匹配行
explain select * from subject;
table字段
数据来自哪张表
possible_keys字段
显示可能应用在这张表中的索引，查询涉及到的字段若存在索引，则该索引将被列出，但不一定被实际使用
key字段
实际使用到的索引，如果为NULL，则没有使用索引
key_len字段
key_len列表示MySQL决定使用的键的长度。key_len的值使您能够确定MySQL实际使用了key的哪些列。如果key列显示NULL，则key_len列也显示NULL。
由于key存储格式，对于可以为NULL的列，key长度比非NULL列的key长度大1。这也是为什么一般定义表的时候，都不允许为空的原因之一。
ref字段
ref列显示将哪些列或常量与键列中命名的索引进行比较，以便从表中选择行。
如果值是func，则使用的值是某个函数的结果。要查看哪个函数，请使用EXPLAIN后面的SHOW WARNINGS来查看扩展的EXPLAIN输出。
explain select subject.* from subject
straight_join teacher on subject.teacher_id = teacher.id;;
rows字段
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数。如果行数非常不准，我们一般需要执行如下的语句重新统计索引信息：
ANALYZE TABLE T
Extra字段
包含MySQL如何解析查询的非常重要的附加信息。如果输出包含filesort、temporary等额外信息，往往需要优化语句。
- Using filesort
说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。此时意味着需要添加索引，以优化查询效率。
explain select * from subject order by name;
它的执行过程如下：
A.初始化sort_buffer数组，包含id、name、与teacher_id字段；
B.扫描主键索引树的叶子节点，取出id、name、与teacher_id列的值，放入sort_buffer数组中；如果sort_buffer数组不够存放数据，那么还会保存到磁盘文件中；sort_buffer的大小受如下变量控制：
show variables like 'sort_buffer_size';
我们可以通过通过OPTIMIZER_TRACE查看排序结果。要使用OPTIMIZER_TRACE只需要“SET optimizer_trace=“enabled=on”;”，跑完语句后查看information_schema.OPTIMIZER_TRACE即可
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
},
"filesort_execution": [
],
"filesort_summary": {
"rows": 4,
"examined_rows": 4,
"number_of_tmp_files": 0,
"sort_buffer_size": 322920,
"sort_mode": "<sort_key, packed_additional_fields>"
sort_mode 里面的 packed_additional_fields 的意思是，排序过程对字符串做了“紧凑”处理。即使 name 字段的定义是 varchar(20)，在排序过程中还是要按照实际长度来分配空间的。
C.遍历完成后，使用排序算法按照name排序sort_buffer数组，至于是否使用文件排序取决于结果集的大小与sort_buffer_size的大小，如果使用了文件排序，可以根据情况适当调大sort_buffer_size的值，还可以使用下文介绍的方法排序；
D.排序完成后返回结果集。
上面介绍的排序算法，sort_buffer数组中保存了结果集中的全部字段，如果查询要返回的字段很多的话，那么 sort_buffer 里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。为此MySQL引入了参数：
show variables like 'max_length_for_sort_data';
max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。这个算法就是rowid 排序
上面的例子中，如果设置如下的值：
set max_length_for_sort_data = 16;
由于id、name、与teacher_id的总大小超过了16，为此会使用rowid 排序，其流程如下：
A.初始化sort_buffer数组，包含id、name；
B.扫描主键索引树的叶子节点，取出id、name列的值，放入sort_buffer数组中；
C.遍历完成后，使用排序算法按照name排序sort_buffer数组；
D.从sort_buffer中取出id的值，回表中查找出id、name、与teacher_id列的值。
从如上的过程中可以看出多了一个回表的过程，同时sort_buffer只包含了需要排序的字段的值与主键ID的值。此时information_schema.OPTIMIZER_TRACE的值如下：
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
},
"filesort_execution": [
],
"filesort_summary": {
"rows": 3,
"examined_rows": 8,
"number_of_tmp_files": 0,
"sort_buffer_size": 1285312,
"sort_mode": "<sort_key, rowid>"
可以看到sort_mode的值变为了"<sort_key, rowid>"。
假设subject是一个千万级的大表，SQL语句改写成如下的语句：
explain select * from subject order by name limit 10;
explain select * from subject order by name limit 100000,10;
那么如果使用上面介绍rowid 排序算法，效率将会提高很多。除了通过修改max_length_for_sort_data的值，还可以改写成如下的延迟关联语句提高效率：
explain select * from subject
inner join (select id from subject order by name limit 10) t using(id)
order by name;
explain select * from subject
inner join (select id from subject order by name limit 100000,10) t using(id)
order by name;
如上的延迟关联SQL在排序可以用上索引且需要回表的情况下，也是可以提高效率的。
- Using temporary
使用了临时表保存中间结果，MySQL在对结果排序时使用临时表，常见于分组查询group by
explain select id%10 m,count(*) c from student_score group by id%10
从输出中可以看出使用了索引idx_student_id进行扫描，同时还使用了临时表与文件排序。
这个语句的工作过程是：
A.创建内存临时表，表中包含字段m与c，其中m是主键索引；
B.遍历索引idx_student_id的叶子节点并且取出id的值，计算id%10得到结果x，如果x在临时表中，则自增c，否则插入(x,1)；
C.遍历完成后，根据字段m进行排序，排序完成后返回结果。
如果我们对结果集不需要按照分组排序，那么可以添加order by null
explain select id%10 m,count(*) c from student_score group by id%10 order by null;
此时的输出如下：
可以看到少了filesort，就是少了C的排序步骤
但是使用临时表有一个问题，如果临时表超过了内存临时表的最大大小，那么MySQL会把内存临时表转换为磁盘临时表，内存临时表的最大大小由如下参数控制
# 控制临时表大小：
show global variables like 'tmp_table_size';
set [global ] tmp_table_size=64*1024*1024;
set [global ] tmp_table_size=64M;
# 控制内存表大小：
show global variables like 'max_heap_table_size';
set [global ] max_heap_table_size=64*1024*1024;
set [global ] max_heap_table_size=64M;
我们可以根据结果集的大小，适当调大这个值
但是如果结果集实在太大，内存临时表放不下，那么我们可以添加big result的提示：
explain select sql_big_result id%10 m,count(*) c from student_score group by id%10 order by null;
从输出中可以看出没有使用临时表了。此时的执行流程如下：
A.初始化sort_buffer，确定放入一个整形字段m；
B.遍历索引idx_student_id的叶子节点并且取出id的值，计算id%10得到结果x，然后把x放入sort_buffer；
C.遍历完成后，根据m排序sort_buffer，得到有序数组；
D.扫描有序数组可以得到各个m的个数c，将(m,c)放入结果集。
但是如上的优化，还是要扫描表，是一个很重的超重。所以如果能够使用索引还是得添加索引。
- Using join buffer
使用了连接缓存
# 插入数据
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=10000)do
insert into subject(name,teacher_id) value ('math',i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
# 删除索引idx_teacher_id
alter table subject
drop key idx_teacher_id;
explain select teacher.*,subject.* from teacher
straight_join subject on teacher.id=subject.teacher_id
where teacher.id IN (1,2);
# 重新添加索引idx_teacher_id
alter table subject
add key idx_teacher_id (teacher_id);
从explain的输出可以看出使用了BNL（Block Nested Loop Join）算法，这个算法的执行流程如下
A.构造join buffer数组，包含字段id、name与teacher_no
B.从驱动表teacher中取出2行数据(id、name与teacher_no)放入到join buffer中；
C.遍历被驱动表subject（本例遍历主键索引树的叶子节点），取出(id、name、teacher_id)的值，根据连接条件同join_buffer数据对比，如果满足条件再同where中的条件对比（本例中where条件中没有subject的条件，故不会对比where中的条件），如果还是满足条件，则放入结果集；
D.遍历完被驱动表subject后，若驱动表teacher的全部数据没有处理完成，则清空join_buffer，重复B、C与D
假设驱动表有m条满足条件的数据，被驱动表有n条数据，从如上的算法中可以看出，时间复杂度是O(mn)，扫描了被驱动表(size(m)+join_buffer_size-1)/join_buffer_size次记为x，从表中总共获取了(m+nx)次。为此可以得出如下的结论：
A.从扫描次数可以看出应该使用小表作为驱动表；
B.根据驱动表的大小，可以适当调大join_buffer_size的大小,以减少扫描次数;
show variables like 'join_buffer_size';
BNL join算法非常耗费资源，应该给被驱动表加上索引，上例中如果重新添加索引idx_teacher_id，则explain结果如下:
可以看出使用上了被驱动表的索引，此时的join算法为INL join(Index Nexted Loop Join)算法；
C.如果无法给被驱动表添加索引且扫描的行数较多，那么应该再应用程序做join操作。
补充INL join(Index Nexted Loop Join)算法的流程：
A.驱动表teacher中取出一行数据(id、name与teacher_no)；
B.根据连接条件，使用驱动表的teacher.id与被驱动表的索引idx_teacher_id，到被驱动表查找匹配的元素，找到后放入结果集；
C.重复A与B，直到遍历完驱动表teacher（本例中用了索引只有2行数据，故循环2次）。
从算法中可以看出，如果可以使用被驱动表的索引，效果还是不错的，此时可以使用MySQL的join操作。INL join(Index Nexted Loop Join)算法还可以继续优化：MRR与BKA优化，但是对于SSD硬盘，由于随机读与顺序读的速度差不多，优化效果没有HDD的硬盘好，效果不大。
- Using index
表示相应的select操作中使用了覆盖索引（Covering Index）,避免访问了表的数据行，效率不错！
使用覆盖索引进行全表扫描例子：
explain select id from teacher;
可以看出type的值为index，key为unx_teacher_no，possable_keys为NULL，表示全表扫描了索引树unx_teacher_no的叶子节点。
使用覆盖索引且使用上了索引的例子：
explain select id from teacher where teacher_no='T2010002';
可以看出type的值为const，key为unx_teacher_no，possable_keys为unx_teacher_no，表示使用了索引unx_teacher_no找到了1行数据。
为此如果可以使用上索引且为覆盖索引，不需要回表，效率还是相当不错的。
- Using where
使用了where条件
explain select * from subject where teacher_id=1 and name='math';
- impossible where
where子句的值总是false，不能用来获取任何元组
explain select * from teacher where name='wangsi' and teacher_no='T2010002';
- distinct
一旦mysql找到了与行相联合匹配的行，就不再搜索了
explain select distinct teacher.name from teacher
inner join subject on teacher.id = subject.teacher_id;
- Select tables optimized away
在优化阶段可以读取要读取的行（例如，通过读取索引行），在查询执行期间不需要读取任何表。
explain select min(id) from subject;
explain的更多说明见官方文档。
1835
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
