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');