MySQL explain详解

前言

MySQL提供explain关键字为我们提供语句SQL如何执行的信息，explain可用于 select,delete,insert,replace和update语句前。
select语句之前增加explain关键字，MySQL会在查询上设置一个标记，使语句执行时只会返回select语句的执行计划，但不执行select语句，使开发者可以根据执行计划开发者可特定策略提高SQL语句性能。该文主要参考自MySQL官方文档EXPLAIN Output Format。

explain返回结果列解析

• id：标识SQL中的select执行顺序

  • id相同时，查询语句从上往下查询
  • id不同时，id越大的执行优先级越高

• select_type：查询类型

  类型	说明
  SIMPLE(常见)	简单查询，在当今数据量爆炸、单表操作常见的时代只需了解该类型即可
  PRIMARY	包含union或者子查询，最外层查询语句类型标记为primary
  SUBQUERY	在SELECT或where列表中的子查询
  UNION	union关键字后的查询都被标记为UNION
  DERIVED	派生表，from后的子查询，如select * from (select …)
  MATERIALIZED	物化子查询，出现在 in (select … where)子查询条件没有索引的情况
  …	…

• table：执行的select查到是哪个表

• partitions：查询将匹配记录的分区

• type(access_type,重点)：访问(连接)类型，表示获取所需数据行的方式

  类型	说明	效率
  system	该表只有一行(=系统表),这是const防伪标类型的特例。	S+
  const	表中最多具有一个匹配行，当使用主键或唯一索引字段进行=判断时使用。	S
  eq_ref	表间连接使用等值比较的列索引为主键或唯一非空索引时(如user_base join user_detail on user_base.id=user_detail.user_id 或 where user_base.id = user_detail.user_id，a.id为主键索引)使用该类型访问数据	A
  ref	使用=或<=>(注：<=>其实就是=，不是<或>或=)运算符进行条件比较普通索引列时使用该类型，如where user_id=1，user_id为普通索引	B
  fulltext	使用FULLTEXT 索引执行联接	C
  ref_or_null	与ref类似，只是条件多出了is null判断，如col=1 or col is null	D
  index_merge	索引合并优化,仅合并来自单个表的索引扫描，而不合并多个表的扫描，possible_keys列出现可能使用的索引数组。如explain select * from user_detail where (city = ‘火星’ or user_id = 21)，city与user_id都有索引，Mysql将进行合并优化	E
  unique_subquery(少用可忽略)	一个索引查找函数，替代某些in子查询以提高效率。	F
  index_subquery(少用可忽略)	类似于 unique_subquery，用于某些形式下的in子查询中的非唯一索引	G
  range	where条件范围查询索引列	H
  index	全索引扫描，只扫描索引树而不扫描数据树	I
  ALL	全表扫描，该考虑条件索引了	渣渣

• possible_keys：查询中可能会被使用的索引

• key：实际使用的索引

  注：key会命名possible_keys中不存在的索引

• key_len：实际使用的索引列长度(如int列索引长度为4)

  若索引列允许NULL值，则该索引列长度比NOT NULL长度大1，如int类型索引列可空则使用该索引时key_len=5。varchar与char的实际长度会根据编码类型更改，在utf8编码环境下实际长度+2，可参考如下2图：
  
  

• ref(不常用)：将哪些列或常量与该key列中命名的索引进行比较，以从表中选择行。

  当为索引列等值判断时该值为const；当为表间连接时则值为连接字段；当值为func，则使用的值是某些函数的结果，比较失败则为null。

• rows：MySQL认为执行查询必须检查的行数

  InnoDB引擎下，该数值只是估计值，不一定准确

• filtered：将按表条件筛选的表行的估计百分比

  最大值为100，意味着没有发生行过滤(即都符合条件)，值从100减少表示过滤量增加。如列rows=3，filtered=66.7%，则最后查询出的列数约为2行，如(user_detail全量数据):
  
  explain结果3 * 66.67约为2，如下图(实际并不一定准确)：
  

• Extra：有关MySQL如何解析查询的额外信息

  常见的如Using Where、Using Index、Distinct等