您好，登录后才能下订单哦！
密码登录
登录注册
点击 登录注册 即表示同意《亿速云用户服务条款》
这期内容当中小编将会给大家带来有关MySQL执行计划中的各个参数及含义指的是什么，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。
可以使用explain + SQL或者desc + SQL
mysql> explain select * from employee; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ | 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 | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec)
mysql> desc select * from employee; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ | 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 | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
select查询的序列号；包含一组数字,表示查询中执行select子句或操作表的顺序。
id的值分为三种情况：
id完全相同；
id完全不同；
id部分相同部分不相同。
对三个表进行关联
EXPLAIN SELECT * FROM EMPLOYEE E ,DEPARTMENT D ,CUSTOMER C WHERE E.DEP_ID = D.ID AND E.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 | Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | D | NULL | eq_ref | PRIMARY | PRIMARY | 4 | demo.E.dep_id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+----------------------------------------------------+ 3 rows in set, 1 warning (0.01 sec)
可以看到id这一列的数据都是相同的，都是1，执行顺序由上到下（先扫描c，在扫描e，最后扫描d）。
使用嵌套子查询
EXPLAIN SELECT * FROM DEPARTMENT WHERE ID = (SELECT ID FROM EMPLOYEE WHERE ID = (SELECT ID FROM CUSTOMER WHERE ID = 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 | Using index | | 3 | SUBQUERY | CUSTOMER | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 3 rows in set, 1 warning (0.01 sec)
id为1、2、3，如果是子查询,id的序号会递增,id值越大优先级越高,优先被执行。
使用子查询作为表
EXPLAIN SELECT * FROM DEPARTMENT D ,(SELECT DEP_ID FROM EMPLOYEE GROUP BY DEP_ID) E WHERE D.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 | Using index | | 2 | DERIVED | EMPLOYEE | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Using temporary | +----+-------------+------------+------------+------+---------------+-------------+---------+-----------+------+----------+-----------------+ 3 rows in set, 1 warning (0.00 sec)
id部分相同，部分不相同。可以认为是一组,（id=1为一组，id=2为另一组）从上往下顺序执行；在所有组中,id值越大,优先级越高,越先执行；先执行完2组，然后执行1组，1组内，相同的依然是从上到下的执行顺序。
最终的执行顺序：
①id = 2先执行
②id = 1的组内的第一行先执行(d)
③id = 1的组内的第二行最后执行(<derived2>)
对于ID字段来说，相同的值则遵循从上往下的执行顺序；不同值时遵循大的先执行。
查询类型,主要用于区别普通查询,联合查询,子查询等复杂查询
SIMPLE、 PRIMARY、SUBQUERY、DERIVED、UNION、UNION RESULT（DEPENDENT SUBQUERY、DEPENDENT UNION）
简单select查询,查询中不包含子查询或者UNION。
对employee进行查询
EXPLAIN SELECT * FROM EMPLOYEE; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ | 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 | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
类型为simple，只对一个单表进行查询。
查询中若包含任何复杂的子查询,最外层查询则被标记为primary。
EXPLAIN SELECT * FROM DEPARTMENT WHERE ID = (SELECT ID FROM EMPLOYEE WHERE ID = (SELECT ID FROM CUSTOMER WHERE ID = 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 | Using index | | 3 | SUBQUERY | CUSTOMER | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 3 rows in set, 1 warning (0.00 sec)
Id=1的select_type为primary，对表department进行扫描，并且该查询是最外层查询，所以被标记为primary。
在select或where中包含了子查询，子查询中的第一个select查询，不依赖于外部查询结果。
EXPLAIN SELECT * FROM DEPARTMENT WHERE ID = (SELECT ID FROM EMPLOYEE WHERE ID = (SELECT ID FROM CUSTOMER WHERE ID = 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 | Using index | | 3 | SUBQUERY | CUSTOMER | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 3 rows in set, 1 warning (0.00 sec)
Id = 2 和id = 3的类型为subquery，是一个子查询，被标记为subquery，并且由于该子查询不依赖与外部的查询结果，所以只显示subquery。
子查询的内容依赖与外部查询的内容，最明显的标量子查询。
EXPLAIN SELECT ID ,(SELECT DEP_ID FROM EMPLOYEE E WHERE E.ID = D.ID) DEP_ID FROM DEPARTMENT D; +----+--------------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ | 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 | Using index | | 2 | DEPENDENT SUBQUERY | E | NULL | eq_ref | PRIMARY | PRIMARY | 4 | demo.D.id | 1 | 100.00 | NULL | +----+--------------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ 2 rows in set, 2 warnings (0.00 sec)
select中的子查询依赖与外部查询d表的结果，所以id = 2是子查询，并且是依赖于外部查询的子查询，被标记为DEPENDENT SUBQUERY。
在from列表中包含的子查询被标记为derived(衍生)，把结果放在临时表当中。
EXPLAIN SELECT * FROM DEPARTMENT D ,(SELECT DEP_ID FROM EMPLOYEE GROUP BY DEP_ID) E WHERE D.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 | Using index | | 2 | DERIVED | EMPLOYEE | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Using temporary | +----+-------------+------------+------------+------+---------------+-------------+---------+-----------+------+----------+-----------------+ 3 rows in set, 1 warning (0.00 sec)
从id=2可以看到，先执行2，从table可以发现，被扫描的表是employee，是子查询的表，该子查询的结果被放在一个临时表里，被标记为DERIVED。
若第二个select出现在union之后,则被标记为union，并且是不依赖于外部查询。
EXPLAIN SELECT ID FROM EMPLOYEE UNION SELECT ID FROM DEPARTMENT; +----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ | 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 | Using index | | 2 | UNION | DEPARTMENT | NULL | index | NULL | PRIMARY | 4 | NULL | 5 | 100.00 | Using index | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ 3 rows in set, 1 warning (0.00 sec)
该查询是由两个单独的select进行的union，不存在子查询，所以直接把id=2的类型标记为UNION，并且第一个的select会被默认的标记为primary，之后用来union的select都会在该select之前执行。
若第二个select出现在union之后,则被标记为union，并且是依赖于外部查询。
EXPLAIN SELECT ID ,NAME FROM CUSTOMER WHERE ID IN (SELECT ID FROM EMPLOYEE UNION SELECT ID FROM DEPARTMENT); +----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+ | 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 | Using where | | 2 | DEPENDENT SUBQUERY | EMPLOYEE | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using index | | 3 | DEPENDENT UNION | DEPARTMENT | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using index | | NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+ 4 rows in set, 1 warning (0.01 sec)
还是和上面一样，emp表和dep表进行了union操作，不过现在放在了子查询里，现在的结果集和外面的查询的结果集有了联系。先执行的id=3为department表，并且该查询在union后面， 由于与外部查询有联系，所以被标记为DEPENDENT UNION；第二步执行的是id= 2的表employee，该部分是一个子查询部分，并且和外部查询有联系，所以是一个依赖子查询，标记为DEPENDENT SUBQUERY；最后执行的是id=1部分，最外层的查询，被标记为primary。
若union包含在from子句的子查询中,外层select将被标记为deriver（deriver表必须有别名），如果没有别名将会报错。
不写别名，会报错：
EXPLAIN SELECT * FROM (SELECT ID ,NAME FROM EMPLOYEE UNION SELECT ID ,DEPTNAME FROM DEPARTMENT); ERROR 1248 (42000): Every derived table must have its own alias
正确写法，写上别名：
EXPLAIN SELECT * FROM (SELECT ID ,NAME FROM EMPLOYEE UNION SELECT ID ,DEPTNAME FROM DEPARTMENT) 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 | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 4 rows in set, 1 warning (0.00 sec)
和上面略有不同，现在是把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。
从union表获取结果select；两个UNION合并的结果集在最后。
若第二个select出现在union之后,则被标记为union，并且是不依赖于外部查询。
EXPLAIN SELECT ID FROM EMPLOYEE UNION SELECT ID FROM DEPARTMENT; +----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ | 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 | Using index | | 2 | UNION | DEPARTMENT | NULL | index | NULL | PRIMARY | 4 | NULL | 5 | 100.00 | Using index | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ 3 rows in set, 1 warning (0.00 sec)
该查询是由两个单独的select进行的union，不存在子查询，先执行的id=2，对department进行扫描，在对employee表进行扫描，然后把这两个单独查询结果集进行合并，使用UNION RESULT表示union操作的合并的步骤，并且union是需要对两部分的数据进行排序然后在进行合并，所以只有当存在排序之后的合并才会有UNION RESULT。
若第二个select出现在union之后,则被标记为union，并且是不依赖于外部查询。
EXPLAIN SELECT ID FROM EMPLOYEE UNION ALL SELECT ID FROM DEPARTMENT; +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 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 | Using index | | 2 | UNION | DEPARTMENT | NULL | index | NULL | PRIMARY | 4 | NULL | 5 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)
该查询是由两个单独的select进行的union，不存在子查询，所以直接把id=2的类型标记为UNION，并且第一个的select会被默认的标记为primary，之后用来union的select都会在该select之前执行。Id部分和select_type部分与union时候一致，唯独少了最后的UNION RESULT，该部分是因为要对两部分的数据排序在合并才会产生，而union all不需要排序，只需要把两部分内容合并就可以，所以不会有UNION RESULT。
若第二个select出现在union之后,则被标记为union，并且是依赖于外部查询。
EXPLAIN SELECT ID ,NAME FROM CUSTOMER WHERE ID IN (SELECT ID FROM EMPLOYEE UNION ALL SELECT ID FROM DEPARTMENT); +----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-------------+ | 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 | Using where | | 2 | DEPENDENT SUBQUERY | EMPLOYEE | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using index | | 3 | DEPENDENT UNION | DEPARTMENT | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using index | +----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-------------+ 3 rows in set, 1 warning (0.00 sec)
还是和UNION一样， 只不过少了UNION RESULT部分。
若union all包含在from子句的子查询中,外层select将被标记为deriver（deriver表必须有别名），如果没有别名将会报错。
不写别名，会报错：
EXPLAIN SELECT * FROM (SELECT ID ,NAME FROM EMPLOYEE UNION SELECT ID ,DEPTNAME FROM DEPARTMENT); ERROR 1248 (42000): Every derived table must have its own alias
正确写法：
EXPLAIN SELECT * FROM (SELECT ID ,NAME FROM EMPLOYEE UNION SELECT ID ,DEPTNAME FROM DEPARTMENT) 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 | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 4 rows in set, 1 warning (0.00 sec)
和union一样，不过少了UNION RESULT部分。
上述就是小编为大家分享的MySQL执行计划中的各个参数及含义指的是什么了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注亿速云行业资讯频道。
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。