Explain详解与实践

     Explain是mysql提供的工具，用于分析SQL语句的执行情况

图1.

图2.

show warnings；  查看MySQL内部sql优化的情况

关闭衍生表合并优化，显示更详细的步骤

set session optimizer_switch='derived_merge=off';

      涉及三个查询，DERIVED (衍生查询，从user中查)， SUBQUERY（子查询，table为 client），这是一个复杂查询 PRIMARY(最外层的，对应的table为 衍生表 ,derived ,id 为3 )

图3.

 select_type:

1.在执行SQL语句过程中，id越大表示执行的优先级越高（也有相同的情况）

    DERIVED ：一般最先执行，包含在from 之后生成的查询结果集，生成一个临时表。MySQL会将结果存放在一个临时表中，也称为派生表

   SIMPLE ：查询的语句为简单查询，如图1，没有关联，子查询等情况

   PRIMARY ：查询包含子查询的时候，对应的是复杂查询中最外层的select 语句

   UNION : union之后出现的select语句对应的查询类型、

   SUBQUERY ：子查询

   DEPENDENT SUBQUERY ： 取决于外面的查询   目前第一步从user表中查询;

2.然后执行的类型为PRIMARY包含有子查询，从派生表里面查询数据 ；

3.目前表是没有分区的 所以partitions为空

4.type 代表的是当前通过什么样的方式对数据库表进行访问，关联类型或者访问类型（MySQL决定如何查找表中的行，查找数据行记录的大概范围）

   一般常用 system >const >eq_ref >ref >range>index>ALL，一般需要保证查询达到range级别，最好达到ref级别，效率逐渐递减。

    NULL: MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如查找min（id），底层B+Three本就是排好序了的，非常容易拿到。单独查找索引即完成了，不需要执行的时候访问表。

    system : 该表只有一行，相当于系统表，查询速度很快，读取1次，system是const类型的特例，本身结果集就只有一条，如同查常量一样，直接查出来的。

   const : 说明在进行数据查询的时候，命中了primary key或唯一索引，此类数据查询非常快

   eq_ref : primary key或者unique key 索引的所有部分被连接使用，最多只会返回一条符合条件的记录。这可能是在const之外最好的连接类型了

   ref : 如果命中的索引是二级索引,不是unique key，type是ref,如果是多字段的联合索引，根据最左匹配原则，从联合索引的最左侧开始连续多个列的字段进行等值比较也是ref类型。

  ref_or_null : 类似于ref ,区别在于会额外搜索包含NULL值的行

  unique_subquery : 在where条件中的关于in的子查询集合

  index_subquery : 用于非唯一索引，可以返回重复值

  range : 使用了索引进行行数据检索，只对指定范围内的行数据检索。就是针对的一个有索引的字段，在指定的范围中检索数据 如果where 语句中使用 between、and 、< 、> 、<=、 in 等条件都是range

   index : 与ALL都是读全表，区别在于index是走二级索引（全索引扫描），而ALL是从硬盘中读取。【如果查找的结果集，即在二级索引有，在主键索引也有，有时候会优化选择走二级索引，因为主键索引整体比二级索引大，优先扫描二级索引，所占磁盘量也小，因为比主键索引小；具体如何选，内部有自己的算法】

  以下是全表扫描，如果是index,则是全索引扫描

  ALL : 扫描的主键索引，不是从根节点开始走的，而是从最小的值开始，进行遍历全表扫描。进行数据匹配，此时的数据查询性能最差（读取的磁盘数据量比index更大一点）。

5.possible_keys （和key放的都是这张表的索引）

表示哪些索引可以按MySQL的优化器进行选择，也就是索引候选者有哪些。

6. key 

7. key_len

    sql语句执行过程中，所具体用到的索引的长度（比如联合索引，只用了其中一个，通过key_len也可以区分用到的是哪个索引，如果都用到就是总长度），和实际的字段属性以及是否为null都有关系，长度有不同的计算方式，一般tinyint:1字节，smallint:2字节 ，int:4字节 bigint:8字节 ，date: 3字节 ，timestamp:4字节，datetime:8字节 ，如果字段运行为空，还需要一个字节来记录是否为NULL

8.ref (条件的引用方式）

如果使用字段进行常量等值查询时ref 此处为const ；当查询条件中使用了表达式或者函数ref 为 func ；其他的显示 null;

9.rows

执行查询的时候必须检查的行数，正常情况下行数越少，效率越高！！（同时，扫描行数只是决定快慢的因素之一，执行时间，可能还要看回表次数的因素）

10. filtered

是一个百分比的值，rows*fitered/100 可以估算出将要和explain 中前一个表进行连接的行数（前一个表指explain中的id值比当前表id值小的表）

11.extra

其他列不显示的额外信息在此列进行展示。

  Using index ：数据库使用了覆盖索引，是一种查询的方式，查询的结果集被索引覆盖，在用到的这棵索引树里面都包含了（例如：一共两个字段，id.name，name作为二级索引，树里面既有name的内容，也有主键id的内容），比回表（二级索引不一定会把数据拿全，还会拿着主键去主键索引里去查）的效率高

  Using where : 查询时未找到可用的索引，进而通过where条件过滤获取所需数据，且查询的列违背索引覆盖，并不是所有的带where语句的查询都会显示这个。

  Using temporary : mysql需要创建一张临时表来处理查询的结果集，出现这种情况一般是要进行优化的，首先是想到用索引来优化，一般在排序或者分组查询时用到，如下图：

 语句中都有distinct去重，mysql会找把结果集load出来，再去去重操作，但如果查询的字段为存在索引，索引本来就是排好序的，查的时候直接就做了去重操作，所以会提示using index覆盖索引

  Using filesort : 此类型表示无法利用索引完成指定的排序操作，也就是order by的字段实际没有索引，因此此类SQL是需要进行优化的。这表示用到了文件排序，结果集放在内存里再进行排序，如果数据量大，就会放磁盘进行排序。如果order by +索引字段，就可能是走的using index 覆盖索引（索引都是排好序的）

---

SQL优化 总结：

        1.如果是联合索引，有最左匹配原则，同时尽量将联合索引的字段都用上，这样扫描的rows将会最少，最左的字段写最左边，MySQL就不用自行优化（稍微好一点）。

        2.存储引擎不能使用索引中范围条件右边的列，比如联合索引（name,age,score）联合索引，如果用了条件  name='lily' and age>18 and score='100' ,此时索引只会走到age，score是不会用的，因为最左原则，首先根据name 定位筛选出来的数据的叶子节点，里面的索引是有序的，通过age>18是能找到数据的，但 score此时 在此节点能并不能保证是有序的，所以索引并没有走score.

        3.不要再索引列上面做任何操作（计算，函数，自动或者手动类型转换），会导致索引失效而转向全表扫描，相当于拿了索引字段的某一部分值来走索引，是没法走索引树的，不是有序的。（如果对索引进行了函数运算之类的，计算的结果在原有的索引树中是没有这个对应的值的，所以就无法走索引进行查询，也有可能是存在的，但其实函数是比较复杂的，即使mysql进行了优化，也不是代表着会深层次优化然后执行sql）

         4.并不是有索引就一定会走索引，MySQL会自己进行估算，虽然没有走索引，但是 possible_keys提示 是可以走这个索引的，此时直接全表扫描可能比走索引耗费的成本更低。如果走这个索引，还有回表操作

         5.尽量使用覆盖索引，少用select * ，指明具体字段

         6.在使用 != 或者 <>的时候大概率无法使用索引，会导致全表扫描

         7.is null ,is not null 一般情况下也无法使用索引

         8. like以通配符开头 mysql 不会走索引，一般放后面就会走索引，因为：如果通配符在前面，相当于跳过了索引的前面的几个字符来匹配，并不是有序的。而 %在后面，用的是索引的左边的前缀，按照索引的有序原则，这几个前缀字符串在索引树中是有序的，可以从索引树去查找，可以进行定位

 

 如果一定要 % 在前进行执行，可进行优化，使用覆盖索引的方式，查询的字段尽量用索引将其覆盖

       like KK%相当于=常量，如果有都会走索引； %KK和%KK%相当于范围查, field>'lina'，这也是范围，跟in一样。

        9.少用 on 或 in ,不一定会使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。优化的时候会计算成本

        10.范围查找优化，数据量太多，可能全表扫描更快，所以不走，可以减小范围。