一、执行计划：
执行计划是SQL语句经过查询分析器后得到的 抽象语法树 和 相关表的统计信息 作出的一个查询方案，这个方案是由查询优化器自动分析产生的。由于是动态数据采样统计分析出来的结果，所以可能会存在分析错误的情况，也就是存在执行计划并不是最优的情况。
通过explain关键字知道MySQL是如何执行SQL查询语句的，分析select 语句的性能瓶颈，从而改进我们的查询，explain的结果如下：
下面是有关各列的详细介绍，重要的有id、type、key、key_len、rows、extra：
1、id：
id列的编号就是select 的序列号，可以理解为SQL执行顺序的标识，有几个select 就有几个id。
- （1）id值不同：id值越大优先级越高，越先被执行；
- （2）id值相同：从上往下依次执行；
- （3）id列为null：表示这是一个结果集，不需要使用它来进行查询。
2、select_type：
查询的类型，主要用于区分普通查询、联合查询、子查询等复杂的查询；
（1）simple：表示查询中不包括union操作或者子查询，位于最外层的查询的select_type即为simple，且只有一个；
explain select * from t3 where id=3952602;
（2）primary：需要union操作或者含有子查询的select，位于最外层的查询的select_type即为primary，且只有一个；
explain select * from (select * from t3 where id=3952602) a ;
（3）derived：from列表中出现的子查询，也叫做衍生表；mysql或者递归执行这些子查询，把结果放在临时表里。
explain select * from (select * from t3 where id=3952602) a ;
（4）subquery：除了from子句中包含的子查询外，其他地方出现的子查询都可能是subquery。
explain select * from t3 where id = (select id from t3 whereid=3952602 ) ；
（5）union：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived。
explain select * from t3 where id=3952602 union all select * from t3；
（6）union result：从union表获取结果的select ，因为它不需要参与查询，所以id字段为null。
explain select * from t3 where id=3952602 union all select * from t3;
（7）dependent union：与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响；
（8）dependent subquery：与dependent union类似，子查询中的第一个SELECT，这个subquery的查询要受到外部表查询的影响。
3、table：
表示 explain 的一行正在访问哪个表。
- （1）如果查询使用了别名，那么这里显示的是别名;
- （2）如果不涉及对数据表的操作，那么这显示为null;
- （3）如果显示为尖括号括起来的
<derived N>就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生; - （4）如果是尖括号括起来的
<union M,N>，与<derived N>类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。
4、type：
访问类型，即MySQL决定如何查找表中的行。依次从好到差：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL，除了all之外，其他的 type类型 都可以使用到索引，除了 index_merge 之外，其他的type只可以用到一个索引。一般要求type为ref，范围查找需要达到 range。
（1）system：表中只有一条元组匹配（等于系统表），这是 const 类型的特例，平时不会出现，可以忽略不计。
（2）const：通过索引一次就找到了，表示使用主键索引或者唯一索引。
（3）eq_ref：主键或者唯一索引中的所有字段被用于连接使用，只会返回一行匹配的数据。简单的select查询语句不会出现这种情况。
（4）ref：普通索引扫描，可能返回多个符合查询条件的行。
（5）fulltext：全文索引检索，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引。
（6）ref_or_null：与ref方法类似，只是增加了null值的比较。
（7）index_merge：表示查询使用了两个以上的索引，索引合并的优化方法，最后取交集或者并集，常见and ，or的条件使用了不同的索引。
（8）unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值；
（9）index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。
（9）range：索引范围扫描，常见于使用>,<,between ,in ,like等运算符的查询中。
（10）index：索引全表扫描，把索引树从头到尾扫描一遍；
（11）all：遍历全表以找到匹配的行（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取）
（12）NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引。
5、possible_keys：
显示查询可能使用到的索引。
6、key：
实际使用哪个索引来优化对该表的访问；select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。
7、key_len：
实际上用于优化查询的索引长度，即索引中使用的字节数。通过这个值，可以计算出一个多列索引里实际使用了索引的哪写字段。key_len的计算规则：
字段类型 | 索引长度 |
|---|---|
tinyint | 1字节 |
smallint | 2字节 |
int | 4字节 |
bigint | 8字节 |
date | 3字节 |
timestamp | 4字节 |
datetime | 8字节 |
char(n) | GBK编码：2n字节 utf8编码：3n字节 utf8mb4编码：4n字节 |
varchar(n) | GBK编码：（2n 2）字节 utf8编码：（3n 2）字节 utf8mb4编码：（4n 2）字节 |
注意：
（1）如果字段允许为 NULL，需要额外1字节记录是否为 NULL
（2）索引的最大长度为767字节，当字符串过长时，mysql会做类似左前缀索引的处理，将前半部分的字符提取出来做索引。
（3）key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
（4）key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。
8、ref：
显示哪个字段或者常量与key一起被使用。
（1）如果是使用的常量等值查询，这里会显示const。
（2）如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段。
（3）如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func。
9、rows：
表示MySQL根据表统计信息及索引选用情况，大致估算此处查询需要读取的行数，不是精确值。
10、extra：
这一列展示一些额外信息，同样十分重要。这个列可以显示的信息非常多，有几十种，常用的有：
类型 | 说明 |
|---|---|
using index | 使用覆盖索引 |
using index condition | 查询的列未被索引覆盖，where筛选条件是索引的前导列 |
using where | 查询的列未被索引覆盖，where筛选条件非索引的前导列 |
using index;using where | 查询的列被索引覆盖，where筛选条件非索引的前导列 |
NULL | （既没有using index，也没有using where; using index，也没有using where） 查询的列未被索引覆盖，并且where筛选条件是索引的前导列。意味着用到了索引，但是部分字段未被索引覆盖，须通过“回表”来获取查询所需的字段 |
using temporary | 用临时表保存中间结果，常用于GROUP BY 和 ORDER BY操作中，通常是因为group by的列上没有索引。也有可能是因为同时有group by和order by，但group by和order by的列又不一样，一般看到它说明查询需要优化了 |
using filesort | MySQL有两种方式对查询结果进行排序，一种是使用索引，另一种是filesort（基于快排实现的外部排序，性能比较差），当数据量很大时，这将是一个CPU密集型的过程，所以可以通过建立合适的索引来优化排序的性能 |
using join buffer | 使用了连接缓存：（1）Block Nested Loop：连接算法是块嵌套循环连接; （2）Batched Key Access：连接算法是批量索引连接 |
Not exists | MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行， 就不再搜索了 |
impossible where | where子句的值总是false |
distinct | 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作 |
select tables optimized away | 在没有GROUP BY子句的情况下，基于索引优化MIN/MAX操作，或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化 |
11、filtered：
使用explain extended时会出现这个列，5.7之后的版本默认就有这个字段，不需要使用explain extended了。这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。
二、MySQL执行计划的局限性：
（1）EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况；
（2）EXPLAIN不考虑各种Cache；
（3）EXPLAIN不能显示MySQL在执行查询时所作的优化工作；
（4）部分统计信息是估算的，并非精确值；
（5）EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看。
三、案例分析：
执行顺序：
第一：（id = 4）：【select id, name from t2】：select_type 为union，说明id=4的select是union里面的第二个select。
第二：（id = 3）：【select id, name from t1 where address = ‘11’】：因为是在from语句中包含的子查询所以被标记为DERIVED（衍生），where address = ‘11’ 通过复合索引idx_name_email_address就能检索到，所以type为index。
第三：（id = 2）：【select id from t3】：因为是在select中包含的子查询所以被标记为SUBQUERY。
第四：（id = 1）：【select d1.name, … d2 from … d1】：select_type为PRIMARY表示该查询为最外层查询，table列被标记为 “derived3”表示查询结果来自于一个衍生表（id = 3 的select结果）。
第五：（id = NULL）：【 … union … 】：代表从union的临时表中读取行的阶段，table列的 “union 1, 4”表示用id=1 和 id=4 的select结果进行union操作。
发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/100059.html原文链接：https://javaforall.cn
