写在前面
想要进行 MySQL SQL 语句的优化 Explain 是一定要掌握的。应用驱动学习。参考 MySQL 官方文档 ：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
SQL 准备 ：
DROP TABLE IF EXISTS student;
CREATE TABLE `student` (
id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
name VARCHAR(30) NOT NULL COMMENT '姓名' ,
birthday VARCHAR(30) NOT NULL COMMENT '生日' ,
sex VARCHAR(10) NOT NULL COMMENT '性别' ,
PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表';
INSERT INTO student VALUES
(NULL , '赵雷' , '1990-01-01' , '男') ,
(NULL , '钱电' , '1990-12-21' , '男') ,
(NULL , '孙风' , '1990-05-20' , '男') ,
(NULL , '李云' , '1990-08-06' , '男') ,
(NULL , '周梅' , '1991-12-01' , '女') ,
(NULL , '吴兰' , '1992-03-01' , '女') ,
(NULL , '郑竹' , '1989-07-01' , '女') ,
(NULL , '王菊' , '1990-01-20' , '女');
DROP TABLE IF EXISTS course;
CREATE TABLE course(
id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键' ,
name VARCHAR(30) NOT NULL COMMENT '课程名称' ,
teacher_id INT UNSIGNED NOT NULL COMMENT '教师ID' ,
PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='课程表';
INSERT INTO course VALUES
(NULL , N'语文' , 1) ,
(NULL , N'数学' , 2) ,
(NULL , N'英语' , 3);
DROP TABLE IF EXISTS teacher;
CREATE TABLE teacher(
id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键' ,
name VARCHAR(30) NOT NULL COMMENT '姓名' ,
PRIMARY KEY (id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='教师表';
INSERT INTO teacher VALUES
(NULL , '张三') ,
(NULL , '李四') ,
(NULL , '王五');
DROP TABLE IF EXISTS score;
CREATE TABLE score(
id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键' ,
student_id INT UNSIGNED NOT NULL COMMENT '学生ID' ,
course_id INT UNSIGNED NOT NULL COMMENT '课程ID' ,
score DECIMAL UNSIGNED DEFAULT 0 COMMENT '成绩' ,
PRIMARY KEY (id)
)ENGINE = innoDB DEFAULT CHARSET = utf8 COMMENT '成绩表';
INSERT INTO score(student_id, course_id,score) VALUES
(1 , 1 , 80) ,
(1 , 2 , 90) ,
(1 , 3 , 99) ,
(2 , 1 , 70) ,
(2 , 2 , 60) ,
(2 , 3 , 80) ,
(3 , 1 , 80) ,
(3 , 2 , 80) ,
(3 , 3 , 80) ,
(4 , 1 , 50) ,
(4 , 2 , 33) ,
(4 , 3 , 20) ,
(5 , 1 , 76) ,
(5 , 2 , 87) ,
(5 , 3 , 31.5) ,
(6 , 1 , 34) ,
(6 , 2 , 89) ,
(6 , 3 , 98) ;
Explain 输出的列
id : 查询标识符，表示 SQL 语句中执行 select 子句或者是操作的顺序。
1. id 相同时执行顺序从上至下。
2. id 不同时 ， 如果是子查询 ，id 的序号会递增， 序号越大的越先执行。
3. id 相同，不同都存在时，id 相同的可以认为是一组查询按从上至下的顺序执行。id 值越大越优先执行。
4. id 为 NULL ， 如果行引用其他行的联合结果，则值可以为NULL。在这种情况下，表列显示像<unionM,N>这样的值，以指示该行引用id值为M和N的行的并。
select_type : 查询的类型，有 11 种
1. SIMPLE ：简单的查询没有使用 UNION 或者是子查询 。
select * from student ;
select * from student where name = '赵雷';
2. PRIMARY ： 最外层的查询类型。
select * ,
(select name from teacher where id = teacher_id) as teacher_name
from course ;
3. UNION ：第二个或者是在 "UNION" 关键字之后的 select 语句。
select * from course
left join score
on course.id = score.course_id
where course.name = '语文'
union
select * from course
left join score
on course.id = score.course_id
where course.name = '数学' ;
4. DEPENDENT UNION ：第二个或者是在 "UNION" 关键字之后的 select 语句，依赖外层查询。
5. UNION RESULT ：两个查询结果集合并后的结果集。
select * from course
left join score
on course.id = score.course_id
where course.name = '语文'
union
select * from course
left join score
on course.id = score.course_id
where course.name = '数学' ;
6. SUBQUERY ：子查询中第一个 select 语句。
select * ,
(select name from teacher where id = 2) as teacher_name
from course ;
7. DEPENDENT SUBQUERY ： 子查询中第一个 select 语句，依赖外层查询。
select * ,
(select name from teacher where id = teacher_id) as teacher_name
from course ;
8. DERIVED : 衍生表。
9. MATERIALIZED ：具体的子查询。
10. UNCACHEABLE SUBQUERY ：无法缓存结果的子查询，必须对外部查询的每行重新进行评估。
11. UNCACHEABLE UNION ： 第二个或者是在 "UNION" 关键字之后的 select 语句 ， 无法缓存结果的子查询。
table : 对应行正在访问哪一个表，表名或者别名
关联优化器会为查询选择关联顺序，左侧深度优先当 from 中有子查询的时候，table 是 derivedN 的形式， N 指向子查询，就是 explain 结果中的那一列。当 select_type 是 union result 的时候 ，table 是 “union 1 ,2” 这样的形势 ，1 ，2 表示 explain 结果中的 id 。注意：MySQL 对待这些表和普通表一样，但是这些“临时表”是没有任何索引的。
select * from course
left join score
on course.id = score.course_id
where course.name = '语文'
union
select * from course
left join score
on course.id = score.course_id
where course.name = '数学' ;
最后一行的 table 是 <union1,2> , 这里的 1 和 2 ，就是图中 id = 1 和 id = 2 的行。
partitions ：将由查询匹配记录的分区。未分区表的值为NULL。
type ：对表的访问类型 ， 有 12 种类型从最好到最糟的顺序如下 ：
system ：这个表中只有一条记录，这是 const 类型的特殊情况。
const : 该表最多有一个匹配行，它在查询开始时被读取。因为只有一行，所以该行中的列的值可以被优化器的其余部分视为常量。const表非常快，因为它们只读一次。
像是这种用主键或者唯一性索引能够唯一定位一条记录的就是 const 类型。
select * from student where id = 2 ;
eq_ref : 最多只返回一条符合条件的记录 ，当使用主键或者是唯一性非NULL索引时产生。
ref : 一种索引访问，返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引或唯一性索引非唯一性前缀时才会发生。这个类型和eq_ref 不同的是，它用在关联操作只使用了索引的最左前缀，或者索引不是唯一的和主键索引。ref 可以用于使用 < ，= ，> 操作符带索引的列。
full_text : 使用到了 full text 索引。
ref_or_null : 类似于 ref 但是MySQL对包含 NULL 值的行进行了特殊的搜索。
index_merge : 表示使用到了索引合并优化，在这种情况下，输出行中的键列包含使用的索引列表，key_len包含所使用的索引的最长键部分的列表。
unique_subquery : 这种类型取代了 eq_ref 在一些子查询中，类似 ：value IN (SELECT primary_key FROM single_table WHERE some_expr) 。
index_subquery : 与 unique_subquery 类似 ， 这种类型也是作用于子查询中， 类似 ：value IN (SELECT key_column FROM single_table WHERE some_expr) 。
range : 只有在指定范围内的行被检索，使用索引来进行查找。explain 结果中的 key 列显示使用了那个索引 。当使用了 = ，< , > , <> , <= , >= , between , like , in() 这些操作符的时候产生。
index ：全索引扫描 ，扫描全表的时候按照索引的顺序而不是按照行进行扫描。如果索引是查询的覆盖索引，并且可以用于满足表中所需的所有数据，则只扫描索引树。如果没有发生索引覆盖则还需要回行到磁盘上查找数据。
ALL ： 在磁盘上对表进行全表扫描。
NULL ： MySQL能在优化阶段分解查询语句，在执行阶段甚至用不到访问表或索引（高效）。
possible_keys : 有可能使用到的索引。
key ： 实际使用到的索引。
key_len : 使用到的索引所占的字节数。
ref : 显示了之前的表在key列记录的索引中查找值所用的列或常量。
rows : 为了找到所需的行而需要读取的行数，估算值。
filtered : 由表条件过滤的表行的估计百分比。最大值为100，这意味着没有发生行过滤。值从100下降表明过滤量增加。
Extra : 额外的信息：
Using filesort : 说明MySQL需要对数据进行额外的排序操作，不能通过索引顺序来进行排序，这个操作比较消耗CPU资源。
Using temporary : 使用了临时表保存中间结果，MySQL在对查询结果排序时使用了临时表。常见于 order by ， group by ， join 操作。
Using index : 发生了索引覆盖 ， 查询时在索引树上取到了需要查询的数据，不需要再进行回行操作。
Using join buffer : 使用了连接缓存：Block Nested Loop，连接算法是块嵌套循环连接;Batched Key Access，连接算法是批量索引连接。
Using where : 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。注意：Extra列出现Using where表示MySQL服务器将存储引擎返回服务层以后再应用WHERE条件过滤。
distinct : 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。
