性能优化--explain的使用

一、业务场景

当通过查看Mysql慢日志，我们知晓慢sql后，怎么分析一条sql慢在哪里？以及怎么来优化这条sql呢？

同时，假如一张表建立了索引，你想看看你写的sql有没有按预期的用上索引。

二、解决办法

        MySQL 提供了一个 explain 命令，它可以对 select 语句进行分析, 并输出 select 执行的详细信息，以供开发人员进行针对性优化。

         

三、explain详解

        不多说，先上大家随时能够保存下来阅读的思维导图：

      

         各个值的详细解说：

        （一）、id（查询顺序）

        查询标识符，表示执行顺序。规则如下：

        1、id值相同时，执行顺序由上至下

        2、id值越大，优先级越高，越先被执行。如果是子查询，id的值会递增

        举例：

         如上图的案例，执行顺序如图中所示，根据规则（id值越大越先执行）先找到最大的id值，发现是2，且有两个，那再根据规则（id值相同时，执行顺序由上至下），所以最终的执行顺序是：先执行第2行，再执行第三行，最后执行第一行。如图中表示的1-2-3执行步骤。

        （二）、select_type（查询类型）

                select_type的值有：

        1、SIMPLE：简单SELECT，不使用UNION或子查询等。

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

        3、UNION：UNION中的第二个或后面的SELECT语句。 

        4、DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询。

        5、UNION RESULT：UNION的结果，union语句中第二个select开始后面所有select。

        6、SUBQUERY：子查询中的第一个SELECT，结果不依赖于外部查询。子查询中所有的内层查询都是SUBQUERY类型。

        7、DEPENDENT SUBQUERY：子查询中的第一个SELECT，依赖于外部查询。

        8、DERIVED：派生表的SELECT, FROM子句的子查询，表示在得到最终结果之前会用到临时表。

        9、UNCACHEABLE SUBQUERY：一个子查询的结果不能被缓存，必须重新评估外链接的第一行。

        （三）、table（表名）

        显示这一步所访问数据库中表的名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，例如下图的<derived2>        

        （四）、type （连接类型）

        对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

        type 字段比较重要, 它提供了判断查询是否高效的重要依据依据。通过 type 字段, 我们判断此次查询是全表扫描还是索引扫描等。

        常用的类型有： system、const、eq_ref、ref、range、index、ALL （从左到右，性能从高到低）        

        system：表中只有一条数据，这个类型是特殊的 const 类型。

        const：针对主键或唯一索引的等值查询扫描，最多只返回一行数据。const 查询速度非常快， 因为它仅仅读取一次即可。

        eq_ref：此类型通常出现在多表的 join 查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果，并且查询的比较操作通常是 =，查询效率较高。

        ref：此类型通常出现在多表的 join 查询，针对于非唯一或非主键索引，或者是使用了 最左前缀 规则索引的查询。

        range：表示使用索引范围查询，通过索引字段范围获取表中部分数据记录。这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。
        当 type 是 range 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个。

        index：表示全索引扫描(full index scan)，和 ALL 类型类似，只不过 ALL 类型是全表扫描，而 index 类型则仅仅扫描所有的索引，而不扫描数据。
        index 类型通常出现在：所要查询的数据直接在索引树中就可以获取到，而不需要扫描数据。 当是这种情况时，Extra 字段会显示 Using index。

        ALL：表示全表扫描，这个类型的查询是性能最差的查询之一。通常来说，我们的查询不应该出现 ALL 类型的查询，因为这样的查询在数据量大的情况下，对数据库的性能是巨大的灾难。如一个查询是 ALL 类型查询，那么一般来说可以对相应的字段添加索引来避免。

        总结：

        一般情况下，我们要确保我们的sql保证在range（含）级别以上

        （五）、possible_key（可能用到的索引）

        

        possible_keys 表示 MySQL 在查询时，可能使用到的索引。指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）

        注意, 即使有些索引在 possible_keys 中出现，但是并不表示此索引会真正地被 MySQL 使用到。MySQL 在查询时具体使用了哪些索引，由 key 字段决定。

        如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询。

        （六）、key（实际使用的索引）

        此字段是 MySQL 在当前查询时所真正使用到的索引，必然包含在possible_keys中。

        如果没有选择索引，值是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

        （七）、key_len（索引的长度）

        表示索引中使用的字节数，这个字段可以评估组合索引是否完全被使用，或只有最左部分字段被使用到。可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

        不损失精确性的情况下，长度越短越好。

        （八）、rows（扫描的行数）

        MySQL 查询优化器根据统计信息，估算 SQL 要查找到结果集需要扫描读取的数据行数。
这个值非常直观显示 SQL 的效率好坏，原则上 rows 越少越好。

        （九）、ref

        列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

        （十）、Extra（额外说明）

         explain 中很多额外的信息会在 Extra 字段显示，常见的有：

        1、Using index

        "覆盖索引扫描", 表示查询在索引树中就可查找所需数据，不用扫描表数据文件，往往说明性能不错。

        2、Using where

        不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤。

        3、Using temporary
        查询有使用临时表，一般出现于排序，分组和多表 join 的情况，查询效率不高，建议优化。

        4、Using filesort
        当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果。

        一般有 Using filesort，都建议优化去掉，因为这样的查询 CPU 资源消耗大。

        5、Select tables optimized away

        这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行。

四、总结

总结：

• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。