SQL优化 MySQL版，explain SQL执行计划详解

目录
一、执行计划分析
二、SQL执行计划中的参数讲解——explain中的id
1、id值相同，从上往下顺序执行。
2、ID值不同，id值越大越优先查询
3、id值有相同，又有不同。id值越大越优先；id值相同，从上往下顺序执行
二、SQL执行计划中的参数讲解——explain中的select_type
三、SQL执行计划中的参数讲解——explain中的table
四、SQL执行计划中的参数讲解——explain中的type
1、type字段中的——system
2、type字段中的——const
3、type字段中的——eq_ref
4、type字段中的——ref
5、type字段中的——range
6、type字段中的——index
7、type字段中的——all
四、SQL执行计划中的参数讲解——explain中的possible_keys
五、SQL执行计划中的参数讲解——explain中的key
六、SQL执行计划中的参数讲解——explain中的key_len
七、SQL执行计划中的参数讲解——explain中的ref
八、SQL执行计划中的参数讲解——explain中的rows
九、SQL执行计划中的参数讲解——explain中的Extra
1、Extra字段中——using filesort
2、Extra字段中——using temporary
3、Extra字段中——using index
4、Extra字段中——using where
十、优化案例，单表优化、两表优化、三表优化
十一、避免索引失效的一些原则

前不久看了SQL优化的视频，学的时候醍醐灌顶，学完后发现就是在围绕着explain的各个字段讲解，先把学习的知识梳理一下，当然自己还是有很多不会的地方，后期不断完善。

sql优化是面试必问的面试点，而SQL优化主要就是在优化索引。在学习索引优化之前，需要知道SQL的执行顺序，这里只给结论，深入学习可参考文章：步步深入：MySQL架构总览->查询执行流程->SQL解析顺序

SQL执行顺序
先执行SQL语句中的from,在执行on.. join ..where ..group by ....having ...,最后执行select dinstinct ..order by limit ...

from .. on.. join ..where ..group by ....having ...
select dinstinct ..order by limit ...

一、执行计划分析

通过explain可以知道mysql是如何处理语句的，并分析出查询或是表结构的性能瓶颈，其实就是在干查询优化器的事，通过expalin可以得到查询执行计划。

#语法：
explain +SQL语句
explain  select  * from tb 

• 各字段含义

id : 编号
select_type ：查询类型
table ：表
type ：类型
possible_keys ：预测用到的索引
key ：实际使用的索引
key_len ：实际使用索引的长度
ref :表之间的引用
rows ：通过索引查询到的数据量
Extra :额外的信息

下面讲解explain中各字段的具体含义，我们只关注三种，分别是type，key，rows

二、SQL执行计划中的参数讲解——explain中的id

explain中id是SQL执行顺序的标识，id的返回结果是数字，id 值越大优先级越高,越先被执行；id 相同,从上往下顺序执行。返回结果有三种情况。

目录
1、id值相同，从上往下顺序执行。
2、ID值不同，id值越大越优先查询
3、id值有相同，又有不同。id值越大越优先；id值相同，从上往下顺序执行

1、id值相同，从上往下顺序执行。

案例如下

explain select t.* from teacher t,course c,teacherCard tc where  t.tid=c.tid 
and t.tcid = tc.tcid 
and (c.cid=2 or tc.tcid=3)

图1

在上图SQL逻辑中，id值相同都为1，从上往下顺序执行。先执行teacher表（t表），在执行teacherCard 表（tc表），最后执行course 表（c表）。

建表：
create table course
(
cid int(3),
cname varchar(20),
tid int(3)
);
create table teacher
(
tid int(3),
tname varchar(20),
tcid int(3)
);

create table teacherCard
(
tcid int(3),
tcdesc varchar(200)
);

插入数据
insert into course values(1,'java',1);
insert into course values(2,'html',1);
insert into course values(3,'sql',2);
insert into course values(4,'web',3);

insert into teacher values(1,'tz',1);
insert into teacher values(2,'tw',2);
insert into teacher values(3,'tl',3);

insert into teacherCard values(1,'tzdesc') ;
insert into teacherCard values(2,'twdesc') ;
insert into teacherCard values(3,'tldesc') ;

• 改变表中数据量会影响表的执行顺序

图1表中数据量：
teacher表3条
teacherCard 表3条
course 表4条

改变teacher表数据条数，增加3条数据

insert into teacher values(4,'ta',4);
insert into teacher values(5,'tb',5);
insert into teacher values(6,'tc',6);

查看表的执行顺序

图2

我们可以看见修改了表数据量之后，表的执行也改变了。先执行teacherCard 表（tc表），在执行course 表（c表），最后执行teacher表（t表）。

• 表的执行顺序因数量的个数改变而改变的原因
  笛卡儿积。数据量小对程序占用内存小，优先计算（数据小的表，优先查询）

2、ID值不同，id值越大越优先查询

案例如下

mysql> explain select tc.tcdesc from teacherCard tc where tc.tcid = 
    -> (select t.tcid from teacher t where  t.tid =  
    ->     (select c.tid from course c where c.cname = 'sql')
    -> );

+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------+
|  1 | PRIMARY     | tc    | ALL  | NULL          | NULL        | NULL    | NULL  |    3 | Using where           |
|  2 | SUBQUERY    | t     | ALL  | NULL          | NULL        | NULL    | NULL  |    6 | Using where           |
|  3 | SUBQUERY    | c     | ref  | cname_index   | cname_index | 23      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+-----------------------+

在上面逻辑中，id值不相同，id值越大越优先查询 (本质：在嵌套子查询时，先查内层 再查外层)
从id字段和table字段可以知道程序在底层先执行course 表（c表），在执行teacher表（t表），最后执行teacherCard 表（tc表）

3、id值有相同，又有不同。id值越大越优先；id值相同，从上往下顺序执行

案例如下

mysql> explain select t.tname ,tc.tcdesc from teacher t,teacherCard tc where t.tcid= tc.tcid
    -> and t.tid = (select c.tid from course c where cname = 'sql') ;

+----+-------------+-------+------+---------------+-------------+---------+-------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key         | key_len | ref   | rows | Extra                                              |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+----------------------------------------------------+
|  1 | PRIMARY     | tc    | ALL  | NULL          | NULL        | NULL    | NULL  |    3 | NULL                                               |
|  1 | PRIMARY     | t     | ALL  | NULL          | NULL        | NULL    | NULL  |    6 | Using where; Using join buffer (Block Nested Loop) |
|  2 | SUBQUERY    | c     | ref  | cname_index   | cname_index | 23      | const |    1 | Using index condition                              |
+----+-------------+-------+------+---------------+-------------+---------+-------+------+----------------------------------------------------+

在上面案例中，id值有相同，又有不同。先执行course 表（c表），在执行teacherCard 表（tc表），最后执行teacher表（t表）。

二、SQL执行计划中的参数讲解——explain中的select_type

select_type是查询类型，常见的查询类型如下。

1、 简单查询，查询SQL中不包含子查询或者UNION
2、PRIMARY: 查询中若包含复杂的子查询,最外层的查询则标记为
3、SUBQUERY : 包含子查询SQL中的 子查询 （非最外层）
4、DERIVED : 衍生查询(使用到了临时表)。在from列表中包含子查询被标记为DRIVED衍生,MYSQL会递归执行这些子查询,把结果放到临时表中
5、UNION: 若第二个SELECT出现在union之后,则被标记为UNION, 若union包含在from子句的子查询中,外层select被标记为:derived
6、UNION RESULT: 从union表获取结果的select

查询类型这里只总结了结果，进一步了解建议大家看参考文章。
参考文章1：MySQL的Explain信息中select_type字段解释
参考文章2：explain之select_type

三、SQL执行计划中的参数讲解——explain中的table

显示这一行的数据是关于哪张表的，有时不是真实的表名字,看到的是derivedx(x是个数字,我的理解是第几步执行的结果)

mysql> explain select cr.cname from
    ->  ( select * from course where tid = 1 union select * from course where tid = 2 ) cr;

+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
|  1 | PRIMARY      | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    8 | NULL            |
|  2 | DERIVED      | course     | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where     |
|  3 | UNION        | course     | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where     |
| NULL | UNION RESULT | <union2,3> | ALL  | NULL          | NULL | N