MySQL执行计划分析-Explain

Explain工具介绍

使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或结构的性能瓶颈。在select语句之前增加explain关键字，MySQL会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL。（如果from中包含子查询，仍会执行该子查询，将结果表放入临时表中）；

explain两个变种

1、explain extended：会在explain的基础上额外提供一些查询优化的信息。紧随其后通过show warnings命令可以得到优化的查询语句，从而看出优化器优化了什么；

2、explain partitions：相比explain多了个partition字段，如果查询是基于分区表的话，会显示查询将访问的分区；

explain中的列

explain select * from b_resource_comment where id = 20;

id列：是select的序号列，id越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行；

select_type列：表示对应行是简单还是复杂查询；

        simple：简单查询，不包含子查询和union

        primary：复杂查询中的最外层select

        subquery：包含在select中的子查询（不在from子句中）

        derived：包含在from子句中的子查询。mysql会将结果存放在一个临时表中，也称派生表

type列：表示关联类型或访问类型，查找数据行记录的大概范围；

        从好到坏排序：system > const > eq_ref > ref > index > ALL

一般将sql优化到range级别之上

        null：mysql能够在优化阶段分解查询语句，在执行阶段不用再访问表或索引；

        const,system：能对查询的某部分进行优化并将其转化为一个常量，system是特例，只有一条数据匹配；

        eq_ref：primary key或unique key索引的所有部分被连接使用，最多只会返回一条符合条件的记录；

        ref：使用普通索引或者唯一性索引的部分前缀，索引要和某个值比较，可能会找到多个符合条件的记录；

          range：范围扫描通常出现在in，between，>，<，>= 等操作中。使用一个索引来检索给定范围的记录；

        如：

explain select * from b_resource where id >1

        index：扫描全索引就能拿到结果，一般是扫描某个二级索引，遍历叶子结点，比ALL通常快一些；

        ALL：即全表扫描，扫描聚集索引的所有叶子节点

possible_keys列：显示可能使用哪些索引来查找   ；

key列：显示mysql实际采用哪个索引来优化对该表的访问；

key_len列：显示mysql在索引里使用的字节数，可以算出具体使用了索引中的哪些列；

ref列：显示key列记录的索引中，表查找值所用到的列或常量

rows列：mysql预估要读取并监测的记录行数，而不是结果集的行数；

Extra列：额外信息，常见的重要值如下：

        Using index：使用了覆盖索引，不需要回表

        Using where：使用where语句，且查询的列未被索引覆盖，需要回表

        Using index condition：使用了索引，查询的列不完全被索引覆盖，不需要回表

        Using temporary：需要创建一张临时表来处理（需要优化）

        Using filesort：将用外部排序而不是索引排序，数据较小时在内存排序，否则在磁盘完成（需要优化）

        Select tables optimizied away：使用聚合函数来访问索引的某个字段；

sql优化总结：

        1、一般sql性能优化到type > range 索引范围查找，级别以上

        2、联合索引要遵守最左前缀法则

        3、不在索引列上进行任何操作，否则会导致索引失效

        4、覆盖索引，不回表；二级非聚集索引包含了需要查询的所有字段，不需要通过聚集索引二次查询数据

        5、is null 和 is not null 无法使用索引，可以考虑使用‘’空字符串代替null，业务上做处理

        6、避免隐式类型装换导致索引失效，传入的参数和字段的类型要一致，不能是name=1000