mysql执行计划详解

执行计划详解：
一、概述
1、可以在select 语句前加EXPLAIN或者DESCRIBE，分析出来的效果一样
2、5.7以上才能用EXPLAIN update ， delete，之前只能EXPLAIN  select；
3、执行计划并未真正执行sql语句。每行数据代表一个单表的查询信息

二、字段分析：
1、id：
（1）表示当前sql有几个select，实际上显示的是查询优化器优化后，真正执行的sql里面有几个select；
如：
EXPLAIN SELECT * FROM user_points  WHERE id in(SELECT id FROM admin);
查询优化器会优化成：
EXPLAIN SELECT * FROM user_points up，admin  a  WHERE up.id=a.id ;
看着有2个select，实际上只有一个；
（2）id的值越大，说明执行的优先级越高；
（3）优化：id的个数越多，代表查询的select越多，因此优化时可能通过减少id的个数来减少select的个数；

2、select_type:  select在整个sql语句中扮演什么角色，
    simple: 不使用union和子查询，使用了关联查询，还是simple；简单的
    primary：最外层的select；                                主要的，首要的
    
    union：union中的第二个或最后一个select，不依赖于外部查询的结果集       两个表数据的合集
    dependent union：union中的第二个或最后一个select，依赖与外部结构集     
    union result：临时表，union后的结果集查询，由于union是取两张表的并集，会生成一张临时表进行去重，这个类型就代表那个临时表，但union all不会去重，因此不会有这张临时表的查询计划

    subquery：子查询，子查询中的第一个select，与最外层的select无关    
    dependent subquery：子查询中的第一个select，依赖外部查询结果集
    
    derived：from里面有子查询的情况，mysql会递归执行这些子查询，把结果集放临时表

2、table：查询哪张表，有时是表的别名

3、partitions：分区

4、type：使用到的索引类型
    system ：针对系统表
    const： 命中主键索引或者唯一二级索引，并且where语句使用等值常量匹配，即where  字段=常量，只可能匹配到一行数据，效率最高
        如：
        EXPLAIN SELECT * FROM population WHERE id=1;                        ---- 匹配到主键，且where后面是等于一个常量；
        EXPLAIN SELECT * FROM t_pop_label WHERE pop_id=1  AND label_id=1;   ---- 匹配到唯一二级索引，且where后面是等于一个常量；

    eq_ref:  针对关联查询，
        在此之前先搞清楚驱动表和被驱动表：进行关联查询的时候，mysql查询优化器会根据查询性能判定哪个表是驱动表，哪个表是被驱动表，与from后面的表的顺序无关，
        一般情况下是数据量少的表（小表）驱动数据量大的表（大表），数据库少的表称为驱动表，数据量大的表被称为被驱动表；

        当被驱动表（大表）是通过主键或唯一索引进行等值匹配的时候，被驱动表就是eq_ref类型：
        （1）首先，通过mysql查询优化器判定哪个表是被驱动表；
        （2）被驱动表通过主键或唯一索引与驱动表进行等值匹配，type就是eq_ref,
        如：
        EXPLAIN SELECT * FROM population pop,t_pop_label  tpl WHERE  pop.id=tpl.id;
        （1）mysql通过查询优化器判定，t_pop_label是被驱动表，
        （2）t_pop_label表只有一个主键id，和一个联合唯一索引：
        idx_pop_id_label_id_unique    pop_id, label_id    Unique    
        （3）当WHERE  pop.id=tpl.id; t_pop_label是通过主键id与驱动表关联，所以type是eq_ref；
        （4）当WHERE  pop.id=tpl.pop_id; t_pop_label不是通过主键id或唯一索引，而是tpl.pop_id与驱动表关联，tpl.pop_id只有二级索引（非主键）或普通索引（非唯一），所以type是ref；

    ref:  有两种情况会出现ref，针对单表和关联查询
        （1）单表：where 字段有二级索引且进行常量等值匹配，如：
        id_card字段有普通二级索引（单列索引）：
        EXPLAIN SELECT * FROM population WHERE id_card='510122202005630035';
        注意：如果id_card是与其他字段有联合索引，且id_card不是索引最左侧的字段，则会走全表扫描；
        （2）关联查询，参考eq_ref中第（4）点

    fulltext:  走的全文索引
    ref_or_null: 在ref的情况下，单个允许字段匹配null值，如：
        EXPLAIN SELECT * FROM population WHERE id_card='510122202005630035'  or  id_card  is null;

    index_merge:  
        在ref_or_null的情况下，where多个字段，且使用or查询；
        EXPLAIN SELECT * FROM population WHERE id_card='510122202005630035'  or  `name` = 'abc';
        多个字段进行or，且多个字段都有二级索引，type=index_merge说明进行了索引合并，而不是使用2个索引

    unique_subquery: 针对子查询中的in（）查询，
        如果mysql的查询优化器认为in（）查询必须优化为exist（）；
        且子查询中的where条件与外部查询关联，
        且子查询使用了主键进行等值匹配
    range:  使用了了范围查询，< > in(),between  and  
    index:  使用了覆盖索引，哪怕只是使用到覆盖索引中的一部分，没有进行回表查询
    all：全表扫描，应该是指到聚簇索引表里面查找数据

阿里规范：sql优化的要求，至少达到range级别，要求是达到ref，最好是const；

5、possible_keys:  可能会使用到的索引

6、key：实际使用到的索引：
    当type=ALL，key=null说明全表扫描，没用到索引

7、key_len: 使用的索引字节长度，越长说明索引索引越充分

8、ref： 关联信息，
    当进行索引列等值查询时，ref=const；
    当进行索引列关联查询时，ref=关联的库名.表名（别名）.字段；
9、rows：表示扫描的数据行数