mysql执行计划

　　文章：https://blog.csdn.net/wuseyukui/article/details/71512793的学习笔记

----------------------------------------------------------------------------------------------

　　目前国内大多数互联网公司使用的数据库都是mysql，虽然postgresql也非常优秀，甚至在某些方面超过mysql，但不可忽视的一个问题是mysql拥有完善的技术社区，基本上生产碰到的问题都会在社区中找到相关的处理办法。mysql毕竟不是商用数据库，在数据量偏大之后(单表几十万)，基本就需要开始进行优化了。

　　而sql的优化，一切都是围绕执行计划展开的，看懂mysql的执行计划也就显得尤其重要了。

　　可以看到执行计划有很多字段，其中比较重要的为id、type、key、rows、extra。我们逐个看一下各个字段的含义:

1、id，表示语句的执行优先级，数字越大优先级越高；id相同的时候，执行顺序为从上到下；

可以看到，a,b,c三张表id都是1，表示这三个表查询的优先级都是相同的。

而上图，由于有子查询，子查询的语句优先级较高，因此id为2，比另外三个要高。除此以外，我们还注意到，三张表的执行顺序变了，跟子查询条件关联的tb_a表变成了在id为1的情况下优先查询的表。这说明where条件可以影响表的查询顺序。

2、select_type，查询类型；主要用于区分普通查询、联合查询、子查询等分类；

　　1、SIMPLE：简单的select查询，查询中不包含子查询或者union

　　2、PRIMARY：查询中包含任何复杂的子部分，最外层查询则被标记为primary

　　3、SUBQUERY：在select 或 where列表中包含了子查询

　　4、DERIVED：在from列表中包含的子查询被标记为derived（衍生），mysql或递归执行这些子查询，把结果放在临时表里

　　5、UNION：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived

　　6、UNION RESULT：从union表获取结果的select

　　其中1、2、3可以合起来理解，就是一个普通的查询，比如select * from table_a，我们叫做简单查询(simple)。如果有子查询，则外层的叫主查询(primary)，内层的叫子查询(subquery)。4的意思是临时表的情况，比如我们要从一个查询的结果集中获取数据，再跟其它表进行join，则这个作为临时表跟其它表进行join操作的结果集就是派生查询(derived，派生，衍生的意思)。5跟6的意思，下图很明显了，就是union之后的查询被标记位union，最终的结果就是union result。

3、type，访问类型；sql优化中很重要的一个指标，从好到坏一次是：

　　system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

　　一般来说，好的sql查询至少达到range级别，最好能达到ref

　　1、system：表里只有一行记录，相当于const类型的特例，平时几乎不会出现，可以忽略不计；

　　2、const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只需匹配一行数据，所以很快。如果主键置于where中，mysql就能将该查询转换为一个const；const：常数；

　　3、eq_ref: 唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或者唯一索引。

　　4、ref： 非唯一索引，返回匹配某个单独之的所有行。本质也是一种索引访问，它返回所有匹配某个单独值的行，然而它也可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。

　　5、range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般就是在where中出现了between、>、<、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束语某个点，不用扫描全部索引。

　　6、index：全索引扫描；index与all区别是index类型只遍历索引树，而all为全表扫描；两者虽然都是全表扫描，但index是从索引中读取，而all是从硬盘读取；

　　id为主键，有主键索引，但因为要查找所有的id，因此需要扫描全部索引，上图走的是age_index索引，不是主键索引，因为此时主键没有索引，若在主键添加唯一索引id_index，则key列会变为id_index，表示使用的是主键的唯一索引；name字段没有索引，故而扫描全表数据：

4、possible_keys：查询涉及到的字段上存在索引，则列出该索引，但不一定会被使用；

5、key：真正使用的索引；

6、key_len：表示索引中使用的字节数，可以通过该列计算查询中使用的索引的长度；并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。理论上越小越好。

7、ref：当前表在利用key列记录中的索引进行查询时，用到的列或者常数。

8、rows：查询所涉及的表数据的行数，越小越好；

9、extra：扩展信息，不适合在其他字段中展示，但十分重要的额外信息；

　　1、using filesort：mysql对数据使用一个外部的索引排序，而不是按照表内的索引进行排序读取。也就是mysql无法利用索引完成的排序操作称为“文件排序”；

　　由于name字段没有索引，我们在利用主键完成数据检索后按name进行排序，这个时候mysql是不知道如何进行的，因为name没有现成的索引排序，因此mysql内部必须再实现一次“文件排序”；

　　2、using temporary：使用临时表保存中间结果，也就是mysql在对查询结果进行排序时使用了临时表，常见于order by跟group by

　　3、using index：表示相应的select操作中使用了覆盖索引，避免了访问表的数据行，效率高；

如果同时出现了using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据，而非进行查找动作；

　　覆盖索引：

　　实际上就是数据存在了索引上，因此在查询的时候，只需要读取了索引也就顺便拿到了数据，不需要再次通过索引中的指针去查找硬盘中的表数据。当然，这种情况下，因为索引中包含数据，故而索引通常体积是比较大的，所以，一般来说，对于一张表，只有一个覆盖索引，这个索引通常都是主键索引。想一下，如果一个表有两个覆盖索引，那这个表的数据不是有2份了么，这太浪费存储空间了。

　　4、using where：使用了where过滤条件；

　　5、using join buffer：使用了连接缓存；

　　6、impossible where：where子句中的值总是false，不能用来过滤任何数据；

　　7、select tables optimized away：在没有group by子句的情况下，基于索引优化min/max操作或者对于myisam引擎优化count(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即可完成优化；

　　8、distinct：优化distinct操作，在找到第一个匹配的数据后即停止查找同样值的操作；