1. 简介
explain语句提供有关 mysql 如何执行语句的信息。
explain与select、delete、insert、replace和update语句一起使用。
mysql> explain select * from employees where emp_no = 10001; +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | simple | employees | null | const | primary | primary | 4 | const | 1 | 100.00 | null | +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
简单来讲，通过explain可以分析出sql语句走没走索引，走的是什么索引。
explain为select语句中使用的每个表返回一行信息，它按照 mysql 在处理语句时读取它们的顺序列出了输出中的表。
mysql 使用嵌套循环连接(nested-loop join algorithms)解析所有连接，这意味着 mysql 从第一个表中读取一行，然后在第二个表，第三个表中找到匹配的行，依此类推。处理完所有表后，mysql将通过表列表输出选定的列后回溯直到找到一个表，其中存在更多匹配的行。从该表中读取下一行，然后继续下一个表。
2.explain 输出列
- mysql版本 5.7.33
- windows10 64位
从上图看到 explain 的结果中，包括的表头id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、extra，这些字段的意思我们来学习然后通过实例进行了解一下。
2.1 id
select 标识符，查询中 select 的顺序号。如果该行引用其他行的并集结果，则该值可以为null。在这种情况下，表列显示类似<unionm,n>的值，以指示该行引用 id 值为 m 和 n 的行的并集。
id 值分三种情况：
id 相同,执行顺序由上至下
mysql> explain (
-> select * from employees emp
-> left join dept_emp de on emp.emp_no = de.emp_no
-> left join departments dept on dept.dept_no = de.dept_no
-> where emp.emp_no = 10001);
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------+
| 1 | simple | emp | null | const | primary | primary | 4 | const | 1 | 100.00 | null |
| 1 | simple | de | null | ref | primary | primary | 4 | const | 1 | 100.00 | null |
| 1 | simple | dept | null | eq_ref | primary | primary | 12 | employees.de.dept_no | 1 | 100.00 | null |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------+
3 rows in set, 1 warning (0.03 sec)
id不相同，如果是子查询，id的序号会递增，id的值越大被执行的优先级越高
mysql> explain select * from employees emp
-> where emp.emp_no not in ( select de.emp_no from dept_emp de
-> where de.dept_no not in ( select dept_no from departments where dept_name = 'development'));
+----+-------------+-------------+------------+-------+-------------------+-----------+---------+-------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |
+----+-------------+-------------+------------+-------+-------------------+-----------+---------+-------+--------+----------+--------------------------+
| 1 | primary | emp | null | all | null | null | null | null | 299468 | 100.00 | using where |
| 2 | subquery | de | null | index | primary | dept_no | 12 | null | 308493 | 100.00 | using where; using index |
| 3 | subquery | departments | null | const | primary,dept_name | dept_name | 122 | const | 1 | 100.00 | using index |
+----+-------------+-------------+------------+-------+-------------------+-----------+---------+-------+--------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)
id相同和不相同都存在
如果id相同可以认为是一组，同一组id执行顺序由上至下，不同组之间，id值越大被执行的优先级越高。
mysql> explain select * from employees emp
-> where emp.emp_no in ( select de.emp_no from dept_emp de
-> where de.dept_no in ( select dept_no from departments where dept_name like '%develop%'));
+----+--------------+-------------+------------+-------+-----------------+-----------+---------+-------------------------------+--------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |
+----+--------------+-------------+------------+-------+-----------------+-----------+---------+-------------------------------+--------+----------+----------------------------------------------------+
| 1 | simple | <subquery2> | null | all | null | null | null | null | null | 100.00 | null |
| 1 | simple | emp | null | all | primary | null | null | null | 299468 | 0.00 | using where; using join buffer (block nested loop) |
| 2 | materialized | departments | null | index | primary | dept_name | 122 | null | 9 | 11.11 | using where; using index |
| 2 | materialized | de | null | ref | primary,dept_no | dept_no | 12 | employees.departments.dept_no | 38561 | 100.00 | using index |
+----+--------------+-------------+------------+-------+-----------------+-----------+---------+-------------------------------+--------+----------+----------------------------------------------------+
4 rows in set, 1 warning (0.01 sec)
2.2 select_type
查询的类型，主要用来区别普通查询，联合查询，子查询等复杂查询。
包含simple、primary、union、dependent union、union result、subquery、dependent subquery、derived、materialized、uncacheable subquery、uncacheable union
simple
简单的select，不使用union或子查询。
mysql> explain select * from employees where emp_no=10001; +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | simple | employees | null | const | primary | primary | 4 | const | 1 | 100.00 | null | +----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
primary
查询中若包含任何复杂的子部分，最外层的查询则被标记为primary
mysql> explain select * from employees emp
-> where emp.emp_no in ( select max(emp_no) from dept_emp);
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+
| 1 | primary | emp | null | all | null | null | null | null | 299468 | 100.00 | using where |
| 2 | dependent subquery | null | null | null | null | null | null | null | null | null | select tables optimized away |
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+
2 rows in set, 1 warning (0.00 sec)
union
第二个或更靠后的 select 语句出现在 union 之后，则被标记为 union
mysql> explain (select emp_no,dept_no from dept_emp limit 10)
-> union
-> select emp_no,dept_no from dept_manager;
+----+--------------+--------------+------------+-------+---------------+---------+---------+------+--------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |
+----+--------------+--------------+------------+-------+---------------+---------+---------+------+--------+----------+-----------------+
| 1 | primary | dept_emp | null | index | null | dept_no | 12 | null | 308493 | 100.00 | using index |
| 2 | union | dept_manager | null | index | null | dept_no | 12 | null | 24 | 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)
dependent union
与 union 相同，它出现在 union 或 union all语句中，但是此查询受外部查询的影响
| union result union_result result of a union.
| subquery none first select in subquery
| dependent subquery dependent (true) first select in subquery, dependent on outer query
| derived none derived table
| materialized materialized_from_subquery materialized subquery
| uncacheable subquery cacheable (false) a subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
| uncacheable union cacheable (false) the second or later select in a union that belongs to an uncacheable subquery (see uncacheable subquery)
总结
到此这篇关于mysql explain输出列的文章就介绍到这了,更多相关mysql explain输出列内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！