【数据库】MySQL EXPLAIN 命令详解

最新推荐文章于 2024-07-16 14:15:48 发布

林坤源

最新推荐文章于 2024-07-16 14:15:48 发布

阅读量109

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/loveruguo/article/details/116022309

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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