[大数据]Mysql之explain详解

explain字段说明

在需要执行的sql语句前面加上 explain 可以模拟优化器对语句的执行过程。
示例说明: explain select * from sys_user;

explain会返回1条或多条数据，下面解释各列含义：

1、 id 执行顺序。

这是一个相对大小，表示各条语句执行的先后顺序，越大越先执行，id相同时从上到下执行。另外在union查询时，外层的union结果语句的id为null，同时这一行的table列会是<unionM,N>的形式，表示引用 id 值为 M 和 N 的行的并集。

explain select realname from sys_user
union select par_name from  sys_param 

先查询sys_param表，然后查询sys_user表，最后将1和2的结果进行并集。第三行union1,2对应上面id=1和id=2的两条查询

2、 select_type 查询类型。

下面列举常见的几种值

• SIMPLE 简单查询，没有union和子查询
• PRIMARY 最外层查询
• 有子查询的情况下，最外层的查询就是PRIMARY。在union 语句中，第一个查询也是PRIMARY，如上图。注意它不是主键查询，跟主键没关系
  
• UNION union查询中，第二个以及之后的查询标记为UNION

explain select realname from sys_user
union select par_name from  sys_param 

• DEPENDENT UNION
  依赖于外部查询的union语句中， 第二个以及之后的查询标记为DEPENDENT UNION。
• DEPENDENT SUBQUERY 依赖于外部查询的子查询中第一个查询

这里的依赖外部查询是指什么呢，就是说他本身是一个子查询，但是它引用了外部查询中的某个表。

• UNION RESULT UNION的结果,一般id为null
  典型示例：

explain select 
u.USER_USERNAME
from SYS_USER u 
where exists (select  UR_ROLE_ID from SYS_USER_ROLE where  UR_USER_ID= u.USER_USERNAME
union select ORG_NAME from SYS_ORGANIZATION org
where org.ORG_CODE = u.USER_ORGANIZATION_CODE) 

• SUBQUERY select子句或者where条件里的子查询（当然这个子查询不是依赖于外部查询的）

explain select 
u.USER_USERNAME,(SELECT  max(ORG_NAME) from SYS_ORGANIZATION org1 where org1.ORG_CODE = u.USER_ORGANIZATION_CODE) as org_name,
(SELECT  min(ORG_NAME) from SYS_ORGANIZATION org2) as org_name1
from SYS_USER u 

• DERIVED 衍生查询
  在FROM列表中包含的子查询被标记为DERIVED（衍生）

explain select 
u.USER_USERNAME
from SYS_USER u 
left join (select ORG_CODE,ORG_NAME from SYS_ORGANIZATION ) org  on org.ORG_CODE = u.USER_ORGANIZATION_CODE
left join (select UR_USER_ID,UR_ROLE_ID  from SYS_USER_ROLE) ur 
ON ur.UR_USER_ID = u.ID

• MATERIALIZED 物化的子查询 ，UNCACHEABLE SUBQUERY 不可缓存的子查询（对于外层查询结果的每一行都要重新计算）， UNCACHEABLE UNION不可缓存的union查询（在UNCACHEABLE SUBQUERY查询中，第二个及之后的union）

3、table表名

查询的表名，但是也有3中比较特殊的情况

• 1、 <unionM,N>: 指 id 值为 M 和 N 的查询的并集
  
• 2、: 引用 id 值为 N 的行的派生表结果

explain select 
u.USER_USERNAME
from SYS_USER u 
left join (select ORG_CODE,ORG_NAME from SYS_ORGANIZATION ) org  on org.ORG_CODE = u.USER_ORGANIZATION_CODE
left join (select UR_USER_ID,UR_ROLE_ID  from SYS_USER_ROLE) ur 
ON ur.UR_USER_ID = u.ID

第3行， 表示引用id=3这条衍生查询的结果

4、partitions 分区（在一些老版本的mysql中没有这一列，可以在查询语句前加explain partitions 就会出现了）

查询的记录所在的分区，非分区表的话，值为null

5、type (重点！)

应该叫访问类型比较合适，它反映了是如何查询表的，有没有可能用索引，实际有没有用索引，用了哪些索引，是否全表扫描等。
结果值从好到坏依次是： system > const > eq_ref > ref > full_text > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all
常用的： const > eq_ref > ref > range > index > all
现在我们一个个来说明

• system
  表中只有一条记录，类似于系统表，它是const的一种特例，一般很少见
• const
  表中只有一条匹配的记录，通过索引一次就可以找到。通常用来查询主键索引或者唯一索引等于一个常量值

explain 
select * from sys_config
where id= 4

• eq_ref
  跟别的表记录比较时，只匹配一条记录。常见于主键索引或唯一索引扫描。

## HN_ENT_VISIT_INFO  的manager_id 有创建索引，  SYS_USER 的主键为id字段
explain 
select u.USER_REAL_NAME,c.* from HN_ENT_VISIT_INFO c left join SYS_USER u on u.id = c.manager_id

• ref 非唯一性索引扫描，匹配某个值对应的所有行，包括最左前缀匹配
• ref 可用于使用 = 或 <=，>= 运算符比较的索引列

explain select b.* from cs_mid_baseinfo b left join labels_enterprise_base leb on leb.pripid = b.pripid
## 把 leb.pripid = b.pripid 换成leb.pripid >= b.pripid 也一样

• fulltext 使用全文索引
• ref_or_null
  类似ref，但是对于包含NULL值的行做了额外优化。
  ref_or_null 首先读取引用键，然后单独搜索具有 NULL 键值的行，但是注意这种优化只能处理一个 IS NULL。

##   可以使用 ref_or_null 优化
SELECT * FROM ref_table  WHERE key_column=expr OR key_column IS NULL;
## 表达式 (t1.a=t2.a AND t2.a IS NULL) 上使用null优化索引查找，不能在 b 上使用null优化功能
SELECT * FROM t1, t2
  WHERE (t1.a=t2.a AND t2.a IS NULL)
  OR (t1.b=t2.b AND t2.b IS NULL);
  

• index_merge 表示使用了索引合并优化，这种情况下，key字段列出了使用的索引列表（多个索引），并且key_len列出对应索引的长度列表

Index Merge 我们的where条件或者join中涉及到多个字段，它们之间进行and 或者 or，这时候就有可能使用到索引合并。索引合并对同一个表的多个索引分别进行范围扫描，然后将结果合并为一个。
注意这里说的是同一个表，多表的多索引是没法合并的

explain select * from cs_mid_baseinfo
where pripid like  '3301020000131583%' or unicode = '913310030656305487'

详细的索引合并的内容就先不在这里讲了

• unique_subquery 唯一子查询。类似与eq_ref，只不过是在子查询语句中
  此类型替换了以下形式的某些 IN 子查询的 eq_ref:

value IN (SELECT primary_key FROM single_table WHERE some_expr)

explain 
select c.* from HN_ENT_VISIT_INFO c 
where c.manager_id in (select id from SYS_USER)

• index_subquery 跟unique_subquery类似，只是使用的不是唯一性索引，而是普通索引。一般出现在下面这样的子查询中

value IN (SELECT key_column FROM single_table WHERE some_expr)

• range 使用索引范围查找
  在使用索引列跟一个常量进行比较时，包括=，<>, >, <, >=,<=,in ,like ,is null, between

explain select * from cs_mid_baseinfo
where pripid like  '3301020000131583%' ;

explain select * from cs_mid_baseinfo
where pripid in( '3301020000131583' ,'3301840000298653');

• index 全索引扫描,比 ALL 快，因为索引数文件比全数据文件小。
  两种情况下会出现index
  1. 覆盖索引能够满足查询要求，这时候 Extra 字段会出现 using index
  2. 全表扫描的数据需要按照索引字段来排序， 这时候Extra 字段不会出现 using index

# pripid是索引字段
explain select pripid from HN_ENT_VISIT_INFO；

• ALL 全表扫描

一般至少要达到range级别才行。ALL是一定要避免的，除非表很小

6、 possible keys 执行查询时可能采用的索引

7、 key 执行采用的索引

Mysql优化器会根据表大小，检索比例等多方面评估是否使用索引，使用什么索引，例如有时候表数据量不大，使用索引的代价可能比全表扫描还要大（使用索引多了一步回表操作），那就不如全表扫描

8、key_len 使用的索引的长度，在联合索引的情况下可以根据这个字段判断使用了哪部分索引

具体的索引长度计算方法，参考 Mysql联合索引

9、ref 参考信息

10、rows 优化器估算需要扫描的行数

这个数据并不精确，只是个估算值。当然这个值越小越好，可以略作参考

11、 Extra 执行过程中额外的重要信息

这里列举几个重要的值，一些不重要的就不一一说明了，可以参考Mysql官方文档： Mysql Document

1. Using Index
   表示当前查询使用了覆盖索引，只扫描索引树不需要查询数据行。

2. Using where 这意味这Mysql服务层在存储引擎检索行之后再进行过滤。如果where条件里的字段能够在同一个索引里被满足，那么就不需要在存储引擎返回之后再过滤，这种情况下就不会出现Using where

explain select pripid,unicode from cs_mid_baseinfo_copy
where pripid like '3301020000131583%' 
and unicode = '91330000CSXM11459E';

explain select pripid,unicode from cs_mid_baseinfo_copy
where pripid = '3301020000131583'

3. Using Index Condition 表示使用了索引下推优化
   5.6及之后的版本，mysql引入了新的优化特性： 索引下推。

explain select * from cs_mid_baseinfo_copy
where pripid like '3301020000131583%' 
and unicode = '91330000CSXM11459E';

具体什么是索引下推优化，后面再写

4. Using index for group-by
   跟Using index类似，表示 MySQL 找到了一个索引，该索引可用于检索 GROUP BY 或 DISTINCT 查询的所有列，而无需对实际表进行任何额外的磁盘访问

5. Using join buffer join查询中使用了基于块的嵌套循环联接算法。
   具体什么是基于块的嵌套循环联接算法，后面再写

explain select * from cs_mid_baseinfo_copy c inner join  cs_ent_unicode u on u.entname = c.entname
where c.pripid like '3301020000131583%' 
and c.unicode = '91330000CSXM11459E';

6. Using filesort 说明Mysql使用了外部排序，而不是使用索引排序。注意虽然叫filesort，但是并不一定是需要文件排序，这个要看需要排序的数据量大小以及sort_buffer_size 变量值来确定

explain select * from cs_mid_baseinfo_copy
order by entname

7. . Using temporary 使用了临时表来存储查询结果

explain select pripid,entname,dom from cs_mid_baseinfo_copy
group by pripid
order by entname