这期内容当中小编将会给大家带来有关MySQL执行计划中的各个参数及含义指的是什么,文章内容丰富且以专业的角度为大家分析和叙述,阅读完这篇文章希望大家可以有所收获。

查看执行计划

可以使用explain + SQL或者desc + SQL

1、使用explain查看执行计划

mysql>explainselect*fromemployee;+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+|1|SIMPLE|employee|NULL|ALL|NULL|NULL|NULL|NULL|8|100.00|NULL|+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+1rowinset,1warning(0.01sec)2、使用desc查看执行计划

mysql>descselect*fromemployee;+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+|1|SIMPLE|employee|NULL|ALL|NULL|NULL|NULL|NULL|8|100.00|NULL|+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+1rowinset,1warning(0.00sec)一、ID

select查询的序列号;包含一组数字,表示查询中执行select子句或操作表的顺序。

id的值分为三种情况:

id完全相同;

id完全不同;

id部分相同部分不相同。

1、id完全相同

对三个表进行关联

EXPLAINSELECT*FROMEMPLOYEEE,DEPARTMENTD,CUSTOMERCWHEREE.DEP_ID=D.IDANDE.CUS_ID=C.ID;+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+----------------------------------------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+----------------------------------------------------+|**1**|SIMPLE|C|NULL|ALL|PRIMARY|NULL|NULL|NULL|1|100.00|NULL||1|SIMPLE|E|NULL|ALL|NULL|NULL|NULL|NULL|8|12.50|Usingwhere;Usingjoinbuffer(BlockNestedLoop)||1|SIMPLE|D|NULL|eq_ref|PRIMARY|PRIMARY|4|demo.E.dep_id|1|100.00|NULL|+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+----------------------------------------------------+3rowsinset,1warning(0.01sec)

可以看到id这一列的数据都是相同的,都是1,执行顺序由上到下(先扫描c,在扫描e,最后扫描d)。

2、id完全不同

使用嵌套子查询

EXPLAINSELECT*FROMDEPARTMENTWHEREID=(SELECTIDFROMEMPLOYEEWHEREID=(SELECTIDFROMCUSTOMERWHEREID=1));+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+|1|PRIMARY|DEPARTMENT|NULL|const|PRIMARY|PRIMARY|4|const|1|100.00|NULL||2|SUBQUERY|EMPLOYEE|NULL|const|PRIMARY|PRIMARY|4|const|1|100.00|Usingindex||3|SUBQUERY|CUSTOMER|NULL|const|PRIMARY|PRIMARY|4|const|1|100.00|Usingindex|+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+3rowsinset,1warning(0.01sec)

id为1、2、3,如果是子查询,id的序号会递增,id值越大优先级越高,优先被执行。

3、id部分相同部分不相同

使用子查询作为表

EXPLAINSELECT*FROMDEPARTMENTD,(SELECTDEP_IDFROMEMPLOYEEGROUPBYDEP_ID)EWHERED.ID=E.DEP_ID;+----+-------------+------------+------------+------+---------------+-------------+---------+-----------+------+----------+-----------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+------------+------------+------+---------------+-------------+---------+-----------+------+----------+-----------------+|1|PRIMARY|D|NULL|ALL|PRIMARY|NULL|NULL|NULL|5|100.00|NULL||1|PRIMARY|<derived2>|NULL|ref|<auto_key0>|<auto_key0>|5|demo.D.id|2|100.00|Usingindex||2|DERIVED|EMPLOYEE|NULL|ALL|NULL|NULL|NULL|NULL|8|100.00|Usingtemporary|+----+-------------+------------+------------+------+---------------+-------------+---------+-----------+------+----------+-----------------+3rowsinset,1warning(0.00sec)

id部分相同,部分不相同。可以认为是一组,(id=1为一组,id=2为另一组)从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行;先执行完2组,然后执行1组,1组内,相同的依然是从上到下的执行顺序。

最终的执行顺序:

①id = 2先执行

②id = 1的组内的第一行先执行(d)

③id = 1的组内的第二行最后执行(<derived2>)

对于ID字段来说,相同的值则遵循从上往下的执行顺序;不同值时遵循大的先执行。

二、select_type

查询类型,主要用于区别普通查询,联合查询,子查询等复杂查询

SIMPLE、 PRIMARY、SUBQUERY、DERIVED、UNION、UNION RESULT(DEPENDENT SUBQUERY、DEPENDENT UNION)

1、SIMPLE简单查询

简单select查询,查询中不包含子查询或者UNION。

对employee进行查询

EXPLAINSELECT*FROMEMPLOYEE;+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+|1|SIMPLE|EMPLOYEE|NULL|ALL|NULL|NULL|NULL|NULL|8|100.00|NULL|+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+1rowinset,1warning(0.00sec)

类型为simple,只对一个单表进行查询。

2、PRIMARY外层查询

查询中若包含任何复杂的子查询,最外层查询则被标记为primary。

EXPLAINSELECT*FROMDEPARTMENTWHEREID=(SELECTIDFROMEMPLOYEEWHEREID=(SELECTIDFROMCUSTOMERWHEREID=1));+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+|1|PRIMARY|DEPARTMENT|NULL|const|PRIMARY|PRIMARY|4|const|1|100.00|NULL||2|SUBQUERY|EMPLOYEE|NULL|const|PRIMARY|PRIMARY|4|const|1|100.00|Usingindex||3|SUBQUERY|CUSTOMER|NULL|const|PRIMARY|PRIMARY|4|const|1|100.00|Usingindex|+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+3rowsinset,1warning(0.00sec)

Id=1的select_type为primary,对表department进行扫描,并且该查询是最外层查询,所以被标记为primary。

3、SUBQUERY子查询3.1、SUBQUERY不依赖于外部查询

在select或where中包含了子查询,子查询中的第一个select查询,不依赖于外部查询结果。

EXPLAINSELECT*FROMDEPARTMENTWHEREID=(SELECTIDFROMEMPLOYEEWHEREID=(SELECTIDFROMCUSTOMERWHEREID=1));+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+|1|PRIMARY|DEPARTMENT|NULL|const|PRIMARY|PRIMARY|4|const|1|100.00|NULL||2|SUBQUERY|EMPLOYEE|NULL|const|PRIMARY|PRIMARY|4|const|1|100.00|Usingindex||3|SUBQUERY|CUSTOMER|NULL|const|PRIMARY|PRIMARY|4|const|1|100.00|Usingindex|+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+3rowsinset,1warning(0.00sec)

Id = 2 和id = 3的类型为subquery,是一个子查询,被标记为subquery,并且由于该子查询不依赖与外部的查询结果,所以只显示subquery。

3.2、SUBQUERY依赖于外部查询

子查询的内容依赖与外部查询的内容,最明显的标量子查询。

EXPLAINSELECTID,(SELECTDEP_IDFROMEMPLOYEEEWHEREE.ID=D.ID)DEP_IDFROMDEPARTMENTD;+----+--------------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+--------------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+|1|PRIMARY|D|NULL|index|NULL|PRIMARY|4|NULL|5|100.00|Usingindex||2|DEPENDENTSUBQUERY|E|NULL|eq_ref|PRIMARY|PRIMARY|4|demo.D.id|1|100.00|NULL|+----+--------------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+2rowsinset,2warnings(0.00sec)

select中的子查询依赖与外部查询d表的结果,所以id = 2是子查询,并且是依赖于外部查询的子查询,被标记为DEPENDENT SUBQUERY。

4、DERIVED衍生表(临时表)

在from列表中包含的子查询被标记为derived(衍生),把结果放在临时表当中。

EXPLAINSELECT*FROMDEPARTMENTD,(SELECTDEP_IDFROMEMPLOYEEGROUPBYDEP_ID)EWHERED.ID=E.DEP_ID;+----+-------------+------------+------------+------+---------------+-------------+---------+-----------+------+----------+-----------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+------------+------------+------+---------------+-------------+---------+-----------+------+----------+-----------------+|1|PRIMARY|D|NULL|ALL|PRIMARY|NULL|NULL|NULL|5|100.00|NULL||1|PRIMARY|<derived2>|NULL|ref|<auto_key0>|<auto_key0>|5|demo.D.id|2|100.00|Usingindex||2|DERIVED|EMPLOYEE|NULL|ALL|NULL|NULL|NULL|NULL|8|100.00|Usingtemporary|+----+-------------+------------+------------+------+---------------+-------------+---------+-----------+------+----------+-----------------+3rowsinset,1warning(0.00sec)

从id=2可以看到,先执行2,从table可以发现,被扫描的表是employee,是子查询的表,该子查询的结果被放在一个临时表里,被标记为DERIVED。

5、UNION5.1、UNION不依赖于外部查询

若第二个select出现在union之后,则被标记为union,并且是不依赖于外部查询。

EXPLAINSELECTIDFROMEMPLOYEEUNIONSELECTIDFROMDEPARTMENT;+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+|1|PRIMARY|EMPLOYEE|NULL|index|NULL|PRIMARY|4|NULL|8|100.00|Usingindex||2|UNION|DEPARTMENT|NULL|index|NULL|PRIMARY|4|NULL|5|100.00|Usingindex||NULL|UNIONRESULT|<union1,2>|NULL|ALL|NULL|NULL|NULL|NULL|NULL|NULL|Usingtemporary|+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+3rowsinset,1warning(0.00sec)

该查询是由两个单独的select进行的union,不存在子查询,所以直接把id=2的类型标记为UNION,并且第一个的select会被默认的标记为primary,之后用来union的select都会在该select之前执行。

5.2、UNION依赖于外部查询

若第二个select出现在union之后,则被标记为union,并且是依赖于外部查询。

EXPLAINSELECTID,NAMEFROMCUSTOMERWHEREIDIN(SELECTIDFROMEMPLOYEEUNIONSELECTIDFROMDEPARTMENT);+----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+|1|PRIMARY|CUSTOMER|NULL|ALL|NULL|NULL|NULL|NULL|1|100.00|Usingwhere||2|DEPENDENTSUBQUERY|EMPLOYEE|NULL|eq_ref|PRIMARY|PRIMARY|4|func|1|100.00|Usingindex||3|DEPENDENTUNION|DEPARTMENT|NULL|eq_ref|PRIMARY|PRIMARY|4|func|1|100.00|Usingindex||NULL|UNIONRESULT|<union2,3>|NULL|ALL|NULL|NULL|NULL|NULL|NULL|NULL|Usingtemporary|+----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+4rowsinset,1warning(0.01sec)

还是和上面一样,emp表和dep表进行了union操作,不过现在放在了子查询里,现在的结果集和外面的查询的结果集有了联系。先执行的id=3为department表,并且该查询在union后面, 由于与外部查询有联系,所以被标记为DEPENDENT UNION;第二步执行的是id= 2的表employee,该部分是一个子查询部分,并且和外部查询有联系,所以是一个依赖子查询,标记为DEPENDENT SUBQUERY;最后执行的是id=1部分,最外层的查询,被标记为primary。

5.3、UNION在from子句

若union包含在from子句的子查询中,外层select将被标记为deriver(deriver表必须有别名),如果没有别名将会报错。

不写别名,会报错:

EXPLAINSELECT*FROM(SELECTID,NAMEFROMEMPLOYEEUNIONSELECTID,DEPTNAMEFROMDEPARTMENT);ERROR1248(42000):Everyderivedtablemusthaveitsownalias

正确写法,写上别名:

EXPLAINSELECT*FROM(SELECTID,NAMEFROMEMPLOYEEUNIONSELECTID,DEPTNAMEFROMDEPARTMENT)T;+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+|1|PRIMARY|<derived2>|NULL|ALL|NULL|NULL|NULL|NULL|13|100.00|NULL||2|DERIVED|EMPLOYEE|NULL|ALL|NULL|NULL|NULL|NULL|8|100.00|NULL||3|UNION|DEPARTMENT|NULL|ALL|NULL|NULL|NULL|NULL|5|100.00|NULL||NULL|UNIONRESULT|<union2,3>|NULL|ALL|NULL|NULL|NULL|NULL|NULL|NULL|Usingtemporary|+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+4rowsinset,1warning(0.00sec)

和上面略有不同,现在是把union的子查询直接放在了from里面当作一个表,而不是根据一个表的字段去in或者exists连接,此时执行计划将会有所不同。

第一点:select * from (select * from b)的写法,必须给括号里的内容起一个别名,如果没有别名,将会报错“1248 - Every derived table must have its own alias”。

第二点:在使用这种写法时,会把括号里的查询生成一个衍生表(临时表),标记为DERIVED,进一步分析子查询,首先执行的是id=3的department表,因为在union 后面,所以对应该表的操作的select_type被标记为union;其次执行的是id=2的employee表,此处被标记为DERIVED;最后执行id=3,并且最外层是select * from (),被标记为primary,外部查询,而且由于是查询括号里的衍生表的记录,所以扫描的表是该衍生表deriverd2。

6、UNION RESULT

从union表获取结果select;两个UNION合并的结果集在最后。

若第二个select出现在union之后,则被标记为union,并且是不依赖于外部查询。

EXPLAINSELECTIDFROMEMPLOYEEUNIONSELECTIDFROMDEPARTMENT;+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+|1|PRIMARY|EMPLOYEE|NULL|index|NULL|PRIMARY|4|NULL|8|100.00|Usingindex||2|UNION|DEPARTMENT|NULL|index|NULL|PRIMARY|4|NULL|5|100.00|Usingindex||NULL|UNIONRESULT|<union1,2>|NULL|ALL|NULL|NULL|NULL|NULL|NULL|NULL|Usingtemporary|+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+3rowsinset,1warning(0.00sec)

该查询是由两个单独的select进行的union,不存在子查询,先执行的id=2,对department进行扫描,在对employee表进行扫描,然后把这两个单独查询结果集进行合并,使用UNION RESULT表示union操作的合并的步骤,并且union是需要对两部分的数据进行排序然后在进行合并,所以只有当存在排序之后的合并才会有UNION RESULT。

7、UNION ALL7.1、UNION ALL不依赖于外部查询

若第二个select出现在union之后,则被标记为union,并且是不依赖于外部查询。

EXPLAINSELECTIDFROMEMPLOYEEUNIONALLSELECTIDFROMDEPARTMENT;+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+|1|PRIMARY|EMPLOYEE|NULL|index|NULL|PRIMARY|4|NULL|8|100.00|Usingindex||2|UNION|DEPARTMENT|NULL|index|NULL|PRIMARY|4|NULL|5|100.00|Usingindex|+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+2rowsinset,1warning(0.00sec)

该查询是由两个单独的select进行的union,不存在子查询,所以直接把id=2的类型标记为UNION,并且第一个的select会被默认的标记为primary,之后用来union的select都会在该select之前执行。Id部分和select_type部分与union时候一致,唯独少了最后的UNION RESULT,该部分是因为要对两部分的数据排序在合并才会产生,而union all不需要排序,只需要把两部分内容合并就可以,所以不会有UNION RESULT。

7.2、UNION ALL依赖于外部查询

若第二个select出现在union之后,则被标记为union,并且是依赖于外部查询。

EXPLAINSELECTID,NAMEFROMCUSTOMERWHEREIDIN(SELECTIDFROMEMPLOYEEUNIONALLSELECTIDFROMDEPARTMENT);+----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-------------+|1|PRIMARY|CUSTOMER|NULL|ALL|NULL|NULL|NULL|NULL|1|100.00|Usingwhere||2|DEPENDENTSUBQUERY|EMPLOYEE|NULL|eq_ref|PRIMARY|PRIMARY|4|func|1|100.00|Usingindex||3|DEPENDENTUNION|DEPARTMENT|NULL|eq_ref|PRIMARY|PRIMARY|4|func|1|100.00|Usingindex|+----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-------------+3rowsinset,1warning(0.00sec)

还是和UNION一样, 只不过少了UNION RESULT部分。

7.3、UNION ALL在from子句

若union all包含在from子句的子查询中,外层select将被标记为deriver(deriver表必须有别名),如果没有别名将会报错。

不写别名,会报错:

EXPLAINSELECT*FROM(SELECTID,NAMEFROMEMPLOYEEUNIONSELECTID,DEPTNAMEFROMDEPARTMENT);ERROR1248(42000):Everyderivedtablemusthaveitsownalias

正确写法:

EXPLAINSELECT*FROM(SELECTID,NAMEFROMEMPLOYEEUNIONSELECTID,DEPTNAMEFROMDEPARTMENT)T;+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+|1|PRIMARY|<derived2>|NULL|ALL|NULL|NULL|NULL|NULL|13|100.00|NULL||2|DERIVED|EMPLOYEE|NULL|ALL|NULL|NULL|NULL|NULL|8|100.00|NULL||3|UNION|DEPARTMENT|NULL|ALL|NULL|NULL|NULL|NULL|5|100.00|NULL||NULL|UNIONRESULT|<union2,3>|NULL|ALL|NULL|NULL|NULL|NULL|NULL|NULL|Usingtemporary|+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+4rowsinset,1warning(0.00sec)

和union一样,不过少了UNION RESULT部分。

上述就是小编为大家分享的MySQL执行计划中的各个参数及含义指的是什么了,如果刚好有类似的疑惑,不妨参照上述分析进行理解。如果想知道更多相关知识,欢迎关注亿速云行业资讯频道。