优化MySQL的执行流程

Mysql执行计划(基于5.7版本)
执行Explain可以获得Mysql语句的执行信息，通过分析这些信息可以为优化Sql语句的性能带来很大的帮助。Mysql 5.7版本执行explain后会输出以下12列信息。

一、id 标识符
表示Select标识符，同时表明执行顺序，id列的值越大执行优先级越高、越先执行，id列的值相同则从上往下执行，id列为Null的最后执行。

二、select_type 查询类型 （重要指标）
查询类型共有SIMPLE、PRIMARY、UNION、DEPENDENT UNION、UNION RESULT、SUBQUERY、DEPENDENT SUBQUERY、DERIVED、MATERIALIZED、UNCACHEABLE SUBQUERY、UNCACHEABLE UNION这些。

SIMPLE（简单查询，不使用union 和 子查询）
PRIMARY（查询中最外层的select，如两表做UNION操作或者存在子查询的外层的表操作是PRIMARY，内层是UNION）
UNION （查询中内层的select，UNION中第二个或后面的Select语句）
DEPENDENT UNION（查询中内层的select，UNION中第二个或后面的Select语句，同时依赖外层的查询结果）
UNION RESULT （UNION的结果）
SUBQUERY （子查询中的第一个select）
DEPENDENT SUBQUERY (子查询中的第一个select，依赖于外部的查询)
DERIVED （表示包含在 from 子句中的子查询的select）
MATERIALIZED （物化子查询，用于子查询优化，如IN/NOT IN子查询以及FROM子查询），具体实现方式是：在SQL执行过程中，第一次需要子查询结果时执行子查询并将子查询的结果保存为临时表 ，后续对子查询结果集的访问将直接通过临时表获得。
UNCACHEABLE SUBQUERY * （子查询结果不能被缓存，必须重新评估外部查询的每一行）
UNCACHEABLE UNION （UNION中第二个或后面的子查询结果不能被缓存）

三、table 表名或表的别名
访问到的表的名称

四、partitions 分区表
表示是否使用分区表，null表示未使用分区表

五、type 访问类型或连接类型 (重要指标)
type表示查询使用了何种类型，性能从好到坏依次为：null、system、const、eq_ref、ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、all。除了All以外其余访问类型都用到了索引，除了index_merge外都只用到了一个索引。

null （mysql优化器在优化阶段阶段分解查询语句，在执行阶段不用再访问表或索引，性能最好）
system （表只有一行数据，是const类型一个特殊的例子）
const (表最多只有一个匹配行，只需要读取一次，速度非常快，出现在用主键或唯一键的所有列与常数进行比较时)
eq_ref (主键或唯一键的所有部分被连接使用，最多只会返回一条符合条件的记录)
ref (使用普通索引与常量进行等值匹配时，该表对应的访问类型可能是ref)
fulltext (全文索引)
ref_or_null (连接类型类似于ref，使用到的普通索引是null值时，对该表的访问类型可能是 ref_or_null )
index_merge (索引合并，查询使用了多个以上的索引，输出的执行计划中key列包含了使用的索引，key_len包含了所用索引的最长的关键元素)
unique_subquery （类似于eq_ref访问类型，unique_subquery用在一些包含In子查询的查询语句中，而且子查询可以使用主键进行匹配）
index_subquery （和unique_subquery类似，在子查询中使用的是普通索引）
range (使用索引获取某些范围区间的记录，可能使用range访问类型，通常出现在> 、<、Between and 、in、like等操作中)
index (只扫描所有的索引数据，从索引树上进行扫描，比全表扫描开销小的多)
all （全表扫描，遍历整个表寻找符合条件的记录，性能最差）
六、possible_keys
显示查询可能使用哪些索引来进行查找。如果执行计划输出信息出现possible_keys列不为空，key列为null的情况，可能是因为表数据较少，mysql优化器认为查询索引对性能帮助不大，因此不走索引而直接进行全表扫描。

七、key
这一列是优化器实际采用的索引，若没有使用索引则为null。

八、key_len
通过这个值可以算出使用了联合索引中的哪些列。key_len计算规则如下：

字符串 char(n) ：n字节长度
varchar(n)：会使用2字节来存储变长列的实际长度，如果使用的字符集是utf-8，则长度为 3n + 2
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节
时间类型：date是3字节， timestamp是4字节，datetime是8字节
如果索引列可以存储Null值，则key_len比不可以存储Null值时多1个字节
九、ref
显示了在key列所记录的索引中，表查找所用到的列或常量。key列为null时，此值一定为null。在访问类型是const、eq_ref、ref、ref_or_null、unique_subquery其中之一时，ref列展示的就是与索引列做等职匹配的东西，常见的有const、func、null、字段名。

当使用常量等值查询，显示const
当进行关联查询时，会显示关联表的关联字段
当查询使用了表达式、函数或条件列发生内部隐式转换，可能是func
其它情况是null
十、rows （重要指标）
以表的统计信息和索引使用情况，估算查询需要读取的行数，值越小越好。

十一、filtered
表示经过过滤以后，表里符合条件的记录数所占的百分比

十二、Extra （重要指标）
官方文档提供的额外信息有几十个，下面对常见的一些额外信息进行介绍。

1、No tables used
当查询语句没有from子句会提示该信息，如

explain select 1;

2、Impossible WHERE
查询语句的WHERE子句永远为FALSE时将会提示该额外信息

explain select * from table where 1 != 1;

3、No matching min/max row
查询列表中有min或max聚集函数，但是没有符合where子句中的搜索条件的记录时，会提示该信息。如查询users表中id=12的记录（实际上并不存在这条记录）

explain select max(Id) from users where Id = 12;

4、Using index (索引覆盖)
查询的字段以及搜索条件只包含属于某个索引的列，就是可以使用索引覆盖的情况下，不需要去做回表操作。

5、Using index condition （索引下推）
当查询的列不完全被索引覆盖，如下边的查询语句：

select * from users where userName > ‘aa’ and userName like ’ %b’;

在Mysql 5.6版本以前，上述语句的执行步骤如下：

先根据userName > ‘aa’ 从普通索引idx_userName中获取到对应的索引记录
根据获取到的索引记录的主键值进行回表，读取完整的用户记录再判断记录是否符合 ‘%b’ 这个条件，将符合条件的记录加入到结果集。
回表是一个随机IO操作，比较耗时，为了减少回表对性能的影响，Mysql 5.6版本引入了索引下推技术对这个过程进行了优化，优化后的流程如下：

先根据userName > ‘aa’ 从普通索引idx_userName中获取到对应的索引记录
对于获取到的普通索引记录，先不做回表操作，而是先检查该记录是否符合userName是否符合 ’%b‘这个条件，如果不满足则过滤
对于满足两个条件的普通索引记录进行回表操作，大大减少了回表次数。
6、Using where
当使用全表扫描来执行对某个表的查询时，并且Where子句中有针对该表的搜索条件时，extra列会输出 Using where

explain select * from users where userName = ‘lufei’;

当使用索引来执行查询，并且Where子句中有除了该索引以外的条件时，extra列也会输出Using where

explain select * from users where id > 1 and userName = ‘lufei’;

7、Using join buffer
进行连接查询时当被驱动表不能有效的利用索引加快访问速度，Mysql一般会为其分配一块名为join buffer的内存块来加速查询速度。

8、Using filesort （需要考虑进行优化）
一些情况下对结果集中的记录做排序操作可以用到索引，这种情况下extra列输出为null，如下所示：

explain select * from users order by id;

当对结果集中的记录进行排序时，无法使用索引进行排序，只能在内存中或磁盘上进行排序，此时extra列会输出 Using filesort。记录比较少的时候在内存中进行排序，记录比较多的时候在磁盘上进行排序，当记录非常多的时候使用filesort方式排序会很耗费性能的，需要考虑添加适当的索引。

explain select * from users order by userName limit 10;

9、Using temporary （需要考虑进行优化）
查询的时候需要创建临时表来完成排序、去重等功能，在Distinct、Group By、Union等子句的查询过程中如果不能利用索引来完成查询，可能就需要创建临时表来完成查询，用到临时表的时候Extra 就会输出 Using temporary。

explain select distinct userName from users;

Explain结果中需重点关注的信息
先看 type 列，如果类型是All可能会进行全表扫描，这个代价太大，可以考虑调整Sql语句或添加合适的索引。
然后看 Extra 列，如果出现了 Using temporary 或 Using filesort则需要考虑是否添加适当的索引。
Mysql 性能优化之物化Mysql 5.7中Derived table变形记SQL优化Mysql常见SQL错误用法Explain执行计划