explain官网解释

explain主要用来优化sql，包括表结构的索引设计，查询语句优化等。今天就来看看这个命令返回的字段都是什么意思。

mysql> explain select * from teacher;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | teacher | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

 

详细说明如下：

测试表及数据

#--教师表
create table `teacher`(`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) not null, #--姓名`tno` char(12) not null, #--老师编号primary key (`id`),unique index ux_idx_tno(tno(12))
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;#--学生表
create table `students` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) NOT NULL, #--学生姓名`sno` char(12) NOT NULL, #--学号`age` int(3) NOT NULL DEFAULT '0',	#--年龄`sex` tinyint(1) NOT NULL DEFAULT '1', #--性别primary key (`id`),unique index ux_idx_sno(sno(12))
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;#--专业表
create table `subject`(`id` int(11) NOT NULL AUTO_INCREMENT,`sjno` char(15) NOT NULL, #--科目编号`title` varchar(20) NOT NULL, #--科目名称PRIMARY KEY (`id`),unique index ux_idx_sjno(sjno(15))
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;#--老师-专业表
create table `teacher_subject`(`id` int(11) NOT NULL AUTO_INCREMENT,`tno` char(12) not null, #--老师编号`sjno` char(15) NOT NULL, #--科目编号PRIMARY KEY (`id`),unique index ux_idx_tno_sjno(tno(12), sjno(15))
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;#--学生-专业表
create table `students_subject`(`id` int(11) NOT NULL AUTO_INCREMENT,`sno` char(12) not null, #--学生编号`sjno` char(15) NOT NULL, #--科目编号PRIMARY KEY (`id`),unique index ux_idx_sno_sjno(sno(12), sjno(15))
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;#--插入老师测试数据 老师号编号规则 T+4位学校编号+7位（千万）顺序编号
insert into teacher (name,tno)  values('刘德华','T20200000001');
insert into teacher (name,tno)  values('赵丽颖','T20200000002');
insert into teacher (name,tno)  values('杨紫','T20200000003');
insert into teacher (name,tno)  values('刘亦菲','T20200000004');
insert into teacher (name,tno)  values('陈伟霆','T20200000005');
insert into teacher (name,tno)  values('古力娜扎','T20200000006');
insert into teacher (name,tno)  values('李易峰','T20200000007');
insert into teacher (name,tno)  values('鹿晗','T20200000008');#--插入学生测试数据 学号编号规则 4位年份+4位专业+2位班级+2位顺序编号
insert into students (name,sno,age,sex) values('雷军','202110021201',18,1);
insert into students (name,sno,age,sex) values('马云','202110021202',18,1);
insert into students (name,sno,age,sex) values('马化腾','202110021203',18,1);
insert into students (name,sno,age,sex) values('刘强东','202110021204',18,1);
insert into students (name,sno,age,sex) values('章泽天','202110021205',18,2);
insert into students (name,sno,age,sex) values('董明珠','202110021206',18,2);
insert into students (name,sno,age,sex) values('杨惠妍','202110021207',18,2);
insert into students (name,sno,age,sex) values('玛丽亚·弗兰卡·费索洛','202110021208',18,2);#--插入专业测试数据 科目编号规则 无
insert into subject (sjno,title) values('B080901','计算机科学与技术');
insert into subject (sjno,title) values('A050106','中国现当代文学');
insert into subject (sjno,title) values('A081001','通信与信息系统');
insert into subject (sjno,title) values('B080703','通信工程');
insert into subject (sjno,title) values('B080903','网络工程');
insert into subject (sjno,title) values('B080902','软件工程');
insert into subject (sjno,title) values('A083501','软件工程');
insert into subject (sjno,title) values('A081203','计算机应用技术');#--插入老师-专业测试数据
insert into teacher_subject (tno,sjno) values('T20200000001', 'A050106');
insert into teacher_subject (tno,sjno) values('T20200000002', 'A081001');
insert into teacher_subject (tno,sjno) values('T20200000003', 'B080901');
insert into teacher_subject (tno,sjno) values('T20200000004', 'B080703');
insert into teacher_subject (tno,sjno) values('T20200000005', 'B080903');
insert into teacher_subject (tno,sjno) values('T20200000006', 'B080902');
insert into teacher_subject (tno,sjno) values('T20200000007', 'A083501');
insert into teacher_subject (tno,sjno) values('T20200000008', 'A081203');#--插入学生-专业测试数据
insert into students_subject (sno,sjno) values('202110021201', 'A050106');
insert into students_subject (sno,sjno) values('202110021202', 'A081001');
insert into students_subject (sno,sjno) values('202110021203', 'B080901');
insert into students_subject (sno,sjno) values('202110021204', 'B080703');
insert into students_subject (sno,sjno) values('202110021205', 'B080903');
insert into students_subject (sno,sjno) values('202110021206', 'B080902');
insert into students_subject (sno,sjno) values('202110021207', 'A083501');
insert into students_subject (sno,sjno) values('202110021208', 'A081203');