Mysql之EXPLAIN命令查看SQL执行计划

1、EXPLAIN介绍

[root@VM_0_5_centos ~]# mysqldumpslow -a /var/log/mariadb/slow_query_log.txt

Reading mysql slow query log from /var/log/mariadb/slow_query_log.txt
Count: 4  Time=21.46s (85s)  Lock=0.00s (0s)  Rows_sent=25615.0 (102460), Rows_examined=85985.0 (343940), root[root]@[218.17.185.19]
  SELECT * FROM `order` WHERE username="1372874xxxx"

Count: 2  Time=18.81s (37s)  Lock=0.00s (0s)  Rows_sent=25465.0 (50930), Rows_examined=25540.0 (51080), root[root]@[218.17.185.19]
  SELECT * FROM `order` WHERE username="1372874xxxx" and side="buy"

通过mysqldumpslow分析慢查询日志，已经得到了具体的SQL；

接下来就要分析SQL执行慢的原因，也就是分析SQL的执行计划。

使用示例：

EXPLAIN SELECT * FROM `order` WHERE username="1372874xxxx";

执行结果：

 

2、执行计划字段含义

ID  SQL执行的顺序的标识,从大到小的执行，同数值从上到下执行

select_type  查询类型，例如SIMPLE、PRIMARY、Union。。

table  可能是表名、表的别名，或者一个为查询产生的临时表

type  连接类型

possible_keys  可能用到的索引，如果一个列值多于3个，则说明引用的索引太多，或者存在一个无效的索引

Key  key列显示MySQL实际决定使用的键（索引），正在用到的索引。如果没有选择索引，键是NULL

Key_len  索引键的长度，用于确认索引的有效性，及多列索引中用到的索引的数量非常重要

rows  显示MySQL认为它执行查询时必须检查的行数，采用嵌套算法，如第一列查出需要2行，第二列查出有10行，则数值为20，值越大，表示查询用到的数量越多

extra   额外的信息

3、执行计划中的重要字段

rows 查询过滤出的数据行数

key 实际使用的索引

type -- 连接类型
这里只记录和理解最重要且经常遇见的六种类型，它们分别是
all,index,range,ref,eq_ref，const。从左到右，它们的效率依次是增强的

all  这便是所谓的“全表扫描”  用all去查找数据就好比这样的一个情形：S学校有俩万人，我告诉你你给我找到小明，然后你怎么做呢！你当然是把全校俩万人挨个找一遍，即使你很幸运第一个人便找到了小明，但是你仍然不能停下，因为你无法确认是否有另外一个小明存在，直到你把俩万人找完为止。所以，基本所有情况，我们都要避免这样类型的查找，除非你不得不这样做。

index
这种连接类型只是另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序

range 指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制

ref 出现该连接类型的条件是： 查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。

ref_eq 与 ref相比牛的地方是，它知道这种类型的查找结果集只有一个？什么情况下结果集只有一个呢！那便是使用了主键或者唯一性索引进行查找的情况

const 通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化，这个取决于优化器
 

extra列返回的描述的意义
Select tables optimized away  直接走的索引结果,一般出现在索引列的max\min\count\order by
Using where 使用到查询条件 没有走索引或者索引失效,或者部分条件没有走索引
Using index 全索引扫描 查询条件里只有查询列
Using temporary  使用到了临时表保存过程数据
Using filesort  使用到了文件排序
system  唯一值返回

 

key_len长度的计算意义：
解决复合索引中到底哪个列被使用
如果复合索引中被使用的key_len一直很小，那么就需要考虑是否需要这个复合索引

 

执行计划并不能简单的通过某个字段直观的确定SQL是否有问题，而是要通过多个字段组合分析。

 

4、执行计划分析

接下来就是对执行计划的分析——确定SQL是否需要优化。

1、看rows是不是很大

2、有没有使用合适的key

3、看extra扩展信息

如果rows很大，又没有使用key，则需要优化

如果rows很大，extra提示没有走索引，则需要优化

 

大表走索引，小表不走索引。