【MySQL】 # expalin命令的使用

1. explain命令作用

很多时候，我们写完了一条SQL语句之后，想知道这条SQL语句执行是否高效（或者说，我们建立好的索引在这条SQL语句中是否使用到了）就可以使用 explain 命令分析一下。

通过 explain 命令我们可以得到的信息：表的读取顺序，数据读取操作的类型，哪些索引可以使用，哪些索引用到了，表之间的引用，每张表有多少行被优化器查询。。。等

2. 使用 explain 命令

EXPLAIN SELECT * FROM student;

使用方法很简单，只需要在 explain 后边跟 SQL语句 即可。（MySQL5.6 之前的版本只能解释 SELECT 语句，5.6 及之后可以解释 非SELECT 语句）

3. 解读执行的结果

3.1 id

一组数字，表示查询中执行 SELECT 子句或操作表的顺序。

• 如果 id 相同：执行顺序由上至下
• 如果 id 不同：id 值越大，优先级越高，越先被执行（有子查询的SQL语句，id就会不同）

3.2 select_type

select 查询的类型

• SIMPLLE：简单查询，该查询不包含 UNION 或子查询
• PRIMARY：如果查询包含 UNION 或子查询，则最外层的查询被标识为PRIMARY
• UNION：表示此查询是 union 中的第二个或者之后的查询
• DEPENDENT UNION：union 中的第二个或后面的查询语句，取决于外面的查询
• UNION RESULT：union 的结果
• SUBQUERY：子查询中的第一个 select 语句（不在 from 子句中）
• DEPENDENT SUBQUERY：子查询中的第一个select，取决于外面的查询，即子查询依赖于外层查询的结果
• DERIVED：包含在 from 子句中的子查询
• UNCACHEABLE SUBQUERY：满足是子查询中的第一个 select语句，同时意味着 select中的某些特性阻止结果被缓存于一个 item_cache 中
• UNCACHEABLE UNION：满足此查询是 UNION 中的第二个或者随后的查询，同时意味着 select 中的某些特性阻止结果被缓存于一个 Item_cache 中

3.3 table

表示正在访问哪张表（有别名就显示别名）

当 from 子句中有子查询时，table 列是 <derivenN> 格式，表示当前查询依赖于 id=N 的查询，于是先执行 id=N 的查询

3.4 partitions

匹配的分区，一般为NULL

3.5 type

关系类型或者访问类型，指明了MySQL决定如何查找表中符合条件的行，同时是我们判断查询是否高效的重要依据

通常来说，不同type的性能如下：
ALL < index < range ~ index_merge < ref < eq_ref < const < system

ALL类型是全表扫描，因此在相同的查询条件下，是最慢的。一般来说，system 是最快的。

• ALL：全表扫描

  性能最差的查询之一，极力避免

• index：全索引扫描

  和 ALL 类型类似，只不过扫描的为全部的索引，避免了排序，开销依然非常大。

  如果在 Extra 列看到 Using index，说明正在使用覆盖索引，只扫描索引的数据，它比按索引次序全表扫描的开销少的多

• range：范围扫描

  一个有限制的索引扫描，开始于索引里的某一点，返回匹配这个值域的行

  通常出现在 = &lt;&gt; &gt; &gt;= < <= IS NULL <=> BETWEEN IN()的操作中

  当 type 是 range 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个

• ref：索引查找

  返回所有匹配某个单个值的行。通常出现在多表的 join 查询，针对于非唯一或非主键索引，或者是使用了最左前缀规则索引的查询。

  EXPLAIN SELECT * FROM user_info, order_info 
  WHERE user_info.id = order_info.user_id AND order_info.user_id = 5
  

• eq_ref：索引查找

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

  EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id
  

• const

  针对主键或唯一索引的等值查询扫描，最多只返回一行数据

  EXPLAIN SELECT * FROM user_info WHERE id = 2
  

• system

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

• NULL：在执行阶段不需要访问表

3.6 possible_keys

显示查询可能使用哪些索引来查找

3.7 key

显示MySQL实际决定使用的索引，如果没有选择，则为 NULL

3.8 key_len

显示了在索引里使用的字节数，当 key 列的值为 NULL 时，该列也为 NULL

• 字符串
  • char(n)：n 字节长度
  • varchar(n)：如果是 utf8编码，则是 3n+2字节，如果是 utf8mb4编码，则是 4n+2字节
• 数值类型
  • TINYINT：1 字节
  • SMALLINT：2 字节
  • MEDIUMINT：3 字节
  • INT：4 字节
  • BIGINT：8字节
• 时间类型
  • DATE：3 字节
  • TIMESTAMP：4 字节
  • DATETIME：8 字节
• 字段属性：NULL 属性，占用一个字节，如果一个字段是 NOT NULL 的, 则没有此属性

3.9 ref

显示了哪些字段或者常量被用来和 key 配合，从表中查询出记录

3.10 rows

显示了估计要找到所需的行而要读取的行数，这是个估计值，原则上越小越好

3.11 filtered

表示此查询条件所过滤的数据的百分比

3.12 Extra

其他的信息

• Using index：使用覆盖索引，表示查询索引就可查到所需数据，不用扫描表数据文件，出现了代表SQL语句性能不错
• Using Where：在存储引擎检索行后再进行过滤，使用了 where 从句来限制哪些行将与下一张表匹配或者是返回给用户
• Using temporary：在查询结果排序时会使用一个临时表，一般出现于排序、分组和多表 join 的情形，建议优化
• Using filesort：对结果使用一个外部索引排序，而不是按索引次序从表里读取行，出现该值，建议优化