持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第16天，点击查看活动详情

前言

在日常工作中，我们会遇到一些执行比较慢的SQL语句，我们常常会用EXPLAIN这个命令来查看一些SQL语句的执行计划，比如查看该SQL语句有没有使用上索引、有没有全表扫描等，从而知道 MySQL 是如何处理你的 SQL 语句的。本文来分看一下EXPLAIN命令的各个输出项都是干嘛使的，希望对大家有所帮助，如有错误或未考虑完全的地方，望不吝赐教。

EXPLAIN用法

我们只需在SQL语句之前增加EXPLAIN关键字，在执行查询时，会返回执行计划的信息，而不是执行这条SQL，例如：

EXPLAIN SELECT * FROM `user`;

执行输出结果如下：

我们需要理解各个字段的含义，才能更好用好EXPLAIN这个关键字。

EXPLAIN字段详解

id

id列的编号是select的序列号，一般比较简单的查询语句里只有一个select，稍微复杂点的查询如包含子查询或者包含union语句的情况会有多个select，有几个select就有几个id，并且id的顺序是按select出现的顺序增长的；

id相同，执行顺序从上之下
id不同，执行顺序从大到小
id相同不同，同时存在，遵守1、2规则

如执行如下sql：

explain select * FROM `user` where class= 1 union select * FROM `user` where class= 2;

union结果放在一个匿名临时表中，临时表不在SQL总出现，因此它的id是NULL。

select_type

查询中每个select的查询类型，如下：

1、SIMPLE：简单select，不使用union和子查询

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

3、UNION：union中第二个后面的select语句

4、UNION RESULT：union的结果

5、SUBQUERY：子查询中的第一个select

table

表示数据来自于哪个表，有时不是真实的表的名字（虚拟表），虚拟表最后一位是数字，代表id为多少的查询。

type

这个字段是我们优化要重点关注的字段，这个字段直接反映我们SQL的性能是否高效。

这个字段表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。这个字段值较多，这里我只重点关注我们开发中经常用到的几个字段:system,const,eq_ref,ref,range,index,all;

性能由好到差依次为：system>const>eq_ref>ref>range>index>all。

system： 表只有一行记录，这个是const的特例，一般不会出现，可以忽略。
const： 表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。

eq_ref： 唯一性索引扫描，表中只有一条记录与之匹配。一般是两表关联，关联条件中的字段是主键或唯一索引。

ref： 非唯一行索引扫描，返回匹配某个单独值的所有行。

range： 检索给定范围的行，一般条件查询中出现了>、<、in、between等查询。

index： 遍历索引树。通常比ALL快，因为索引文件通常比数据文件小。all和index都是读全表，但index是从索引中检索的，而all是从硬盘中检索的。

all： 遍历全表以找到匹配的行。

possible_keys

查询可能使用哪些索引来查找，但不一定被查询实际使用。

key

实际使用的索引。如果没有使用索引，则该列是 NULL。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。一般来说，索引长度越长表示精度越高，效率偏低；长度越短，效率高，但精度就偏低。并不是真正使用索引的长度，是个预估值。

ref

在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），func，NULL，字段名（例：user.id）

rows

大致找到所需记录需要读取的行数。注意这个不是结果集里的行数。

filtered

表示选取的行和读取的行的百分比，100表示选取了100%，80表示读取了80%。

Extra

这一列展示的是额外信息。常见的重要值如下：

Using where： 通常是进行了全表/全索引扫描后再用WHERE子语句完成结果过滤【差，需要加索引或者SQL写的不好】。

Using filesort： 表示没有使用索引的排序【差，需要加索引】。

Using temporary： 使用了临时表。

Using index： 表示使用覆盖索引，查询的字段在覆盖索引中就可以获取到。

JSON格式的执行计划

我们通过在EXPLAIN关键字和真正的查询语句中间加上FORMAT=JSON可以得到一个JSON格式的执行计划，里面包含该计划花费的成本，可以衡量执行计划的好坏。

示例执行如下sql：

EXPLAIN FORMAT=JSON SELECT * FROM `user`;

我们可以得到：

{
  "query_block": {
    "select_id": 1,#整个查询语句只有一个select关键字，该关键字对应的id号
    "cost_info": {
      "query_cost": "2056.80" #整个查询的执行成本预计为2058.80
    },
    "table": {
      "table_name": "user", #驱动表
      "access_type": "ALL", #访问方法为ALL，全表扫描
      "rows_examined_per_scan": 10129, #查询1次user表大致需要扫描10129条记录
      "rows_produced_per_join": 10129,#驱动表user的扇出预计是10129
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "31.00",
        "eval_cost": "2025.80",
        "prefix_cost": "2056.80",#单次查询user表总共的成本
        "data_read_per_join": "16M"#读取的数据量
      },
      "used_columns": [ #执行查询中涉及的列
        "id",
        "name",
        "age",
        "sex",
        "class",
        "phone",
        "email"
      ]
    }
  }
}

小结

我们可以用EXPLAIN这个命令来查看一些SQL语句的执行计划，比如查看该SQL语句有没有使用上索引、有没有全表扫描等，从而知道 MySQL 是如何处理你的 SQL 语句的。

explain中的列总结如下：

id: 在一个大的查询语句中每个 SELECT 关键字都对应一个唯一的 id
select_type: SELECT 关键字对应的那个查询的类型
table: 表名
partitions：匹配的分区信息
type：针对单表的访问方法
possible_keys：可能用到的索引
key：实际上使用的索引
key_len：实际使用到的索引长度
ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows：预估的需要读取的记录条数
filtered：某个表经过搜索条件过滤后剩余记录条数的百分比
Extra：—些额外的信息

mysql explain都不懂,还谈什么SQL优化,看这一篇文章就够了

前言