2020-heibaiying-MySQL EXPLAIN

MySQL EXPLAIN

EXPLAIN关键字可以用于获取SQL语句执行计划的相关信息，在MySQL 8.0中，EXPLAIN支持大多数SQL语句，如SELECT 、DELETE 、INSERT 、REPLACE、 UPDATE 。示例如下：

mysql> EXPLAIN SELECT * FROM employees;
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 299379 | NULL  |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------+

注：本篇文章的测试数据来源于MySQL官方提供的 Employees Sample Database，其数据库结构如下：

以下分别介绍EXPLAIN输出结果中各个字段的含义：

1. id

id为行标识符，同时也表示语句执行的优先级，值越大则优先级越高。特殊情况下，如果某行语句引用了其他多行结果集的并集，则该值可以为NULL。示例如下：

# 该FROM字句只是用于演示，并没有任何实际意义
mysql> EXPLAIN SELECT COUNT(1) FROM (SELECT emp_no FROM salaries) AS t;
+----+-------------+------------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+------------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL | 2757635 | NULL        |
|  2 | DERIVED     | salaries   | index | NULL          | PRIMARY | 7       | NULL | 2757635 | Using index |
+----+-------------+------------+-------+---------------+---------+---------+------+---------+-------------+

# 查询工资大于500000或部门编号等于d007的所有雇员的编号
mysql> EXPLAIN SELECT emp_no FROM salaries WHERE salary>500000 UNION ALL SELECT emp_no FROM dept_emp WHERE dept_no = "d007";
+----+--------------+------------+------+---------------+---------+---------+-------+---------+--------------------------+
| id | select_type  | table      | type | possible_keys | key     | key_len | ref   | rows    | Extra                    |
+----+--------------+------------+------+---------------+---------+---------+-------+---------+--------------------------+
|  1 | PRIMARY      | salaries   | ALL  | NULL          | NULL    | NULL    | NULL  | 2837161 | Using where              |
|  2 | UNION        | dept_emp   | ref  | dept_no       | dept_no | 4       | const |   91566 | Using where; Using index |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL    | NULL    | NULL  |    NULL | Using temporary          |
+----+--------------+------------+------+---------------+---------+---------+-------+---------+--------------------------+

2. select_type

select_type用于表示查询的类型，常见类型及其含义如下：

  • SIMPLE：不包含子查询或者UNION操作的查询；
  • PRIMARY：查询中如果包含任何子查询，那么最外层的查询则被标记为PRIMARY ；
  • SUBQUERY：子查询中第一个SELECT ；
  • DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外部查询；
  • UNION：UNION操作的第二个或者之后的查询；
  • DEPENDENT UNION：UNION操作的第二个或者之后的查询,取决于外部查询；
  • UNION RESULT：UNION产生的结果集；
  • DERIVED：出现在FROM字句中的子查询。

这里以查询类型为SUBQUERY的情况进行演示，示例如下：

# 根据员工编号查询员工姓名及其工资总和
mysql> EXPLAIN SELECT first_name,(SELECT sum(salary) FROM salaries WHERE emp_no = 10001) FROM employees WHERE emp_no = 10001;
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
|  1 | PRIMARY     | employees | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
|  2 | SUBQUERY    | salaries  | ref   | PRIMARY       | PRIMARY | 4       | const |   17 | NULL  |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+

3. table

表示语句执行的目标表，除了正常的表名或表别名外，还会出现以下取值：

  • <unionM,N>：输出结果中编号为M的行与编号为N的行的结果集的并集。
  • <derivedN>：输出结果中编号为N的行的结果集，derived表示这是一个派生结果集，如FROM子句中的查询。
  • <subqueryN>：输出结果中编号为N的行的结果集，subquery表示这是一个物化子查询。

4. type

type字段是进行性能判断的重要依据，它表示MySQL使用何种方式来查找目标数据集，不同查找方式会导致不同的性能开销，常见查找方式及其性能表现按照由高到低的顺序排序如下：

1. system：这是const类型的一个特例，只会出现在待查询的表只有一行数据的情况下。

2. const：常出现在主键或唯一索引与常量值进行比较的场景下，此时查询性能是最优的。

mysql> EXPLAIN SELECT * FROM employees WHERE emp_no = 10008;
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | employees | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+

3. eq_ref：当连接使用的是完整的索引并且是PRIMARY KEY或UNIQUE NOT NULL INDEX时使用它。

# 这里员工部门关系表 dept_no 的联合主键为 emp_no + dept_no ，即员工编号+部门标号
mysql> EXPLAIN SELECT * FROM employees e,dept_emp d WHERE e.emp_no = d.emp_no AND dept_no = "d005";
+----+-------------+-------+--------+-----------------+---------+---------+--------------------+------+-----------------------+
| id | select_type | table | type   | possible_keys   | key     | key_len | ref                | rows | Extra                 |
+----+-------------+-------+--------+-----------------+---------+---------+--------------------+------+-----------------------+
|  1 | SIMPLE      | d     | ref    | PRIMARY,dept_no | dept_no | 4       | const              |    1 | Using index condition |
|  1 | SIMPLE      | e     | eq_ref | PRIMARY         | PRIMARY | 4       | employees.d.emp_no |    1 | NULL                  |
+----+-------------+-------+--------+-----------------+---------+---------+--------------------+------+-----------------------+

4. ref：当连接使用的是前缀索引或连接条件不是PRIMARY KEY或UNIQUE INDEX时则使用它。

# 这里仅使用了前缀索引emp_no，所以其类型为 ref , 而不是 eq_ref
mysql> EXPLAIN SELECT * FROM employees e,dept_emp d WHERE e.emp_no = d.emp_no;
+----+-------------+-------+------+---------------+---------+---------+--------------------+--------+-------+
| id | select_type | table | type | possible_keys | key     | key_len | ref                | rows   | Extra |
+----+-------------+-------+------+---------------+---------+---------+--------------------+--------+-------+
|  1 | SIMPLE      | e     | ALL  | PRIMARY       | NULL    | NULL    | NULL               | 299379 | NULL  |
|  1 | SIMPLE      | d     | ref  | PRIMARY       | PRIMARY | 4       | employees.e.emp_no |      1 | NULL  |
+----+-------------+-------+------+---------------+---------+---------+--------------------+--------+-------+

5. ref_or_null：类似于ref类型的查询，但是附加了对NULL值列的查询。示例语句如下：

SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;

6. index_merge：该联接类型表示使用了索引进行合并优化，示例如下：

mysql> EXPLAIN SELECT * FROM dept_emp WHERE dept_no = "d004" AND emp_no < 10020;
+----+-------------+----------+-------------+-----------------+-----------------+---------+------+------+-----------------------------------------------+
| id | select_type | table    | type        | possible_keys   | key             | key_len | ref  | rows | Extra                                         |
+----+-------------+----------+-------------+-----------------+-----------------+---------+------+------+-----------------------------------------------+
|  1 | SIMPLE      | dept_emp | index_merge | PRIMARY,dept_no | dept_no,PRIMARY | 8,4     | NULL |    1 | Using intersect(dept_no,PRIMARY); Using where |
+----+-------------+----------+-------------+-----------------+-----------------+---------+------+------+-----------------------------------------------+

7. range：使用索引进行范围扫描，常见于between、> 、<这样的查询条件。

mysql> EXPLAIN SELECT * FROM employees WHERE emp_no > 10000;
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows   | Extra       |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | employees | range | PRIMARY       | PRIMARY | 4       | NULL | 149689 | Using where |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+

8. index：索引连接类型与ALL相同，只是扫描的是索引树，通常出现在索引是该查询的覆盖索引的情况：

mysql> EXPLAIN SELECT emp_no FROM employees;
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows   | Extra       |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | employees | index | NULL          | PRIMARY | 4       | NULL | 299379 | Using index |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+

9. ALL：全表扫描，效率最差的查找方式。

mysql> EXPLAIN SELECT * FROM employees WHERE first_name ="Bezalel";
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 299379 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

5. possible_keys

表示在执行过程中可能会用到哪些索引来进行优化。

6. key

表示在执行过程中实际用到的用于优化的索引。

7. key_len

表示使用到的索引的字节数。

8. ref

显示哪些列或常量与key列中指定的索引进行比较。

9. rows

MySQL为了找到目标行而读取的所有行的数量，这是一个估算的值。

10. Extra

Extra列主要用于显示额外的信息，常见信息及其含义如下：

1. Using where ：MySQL服务器会在存储引擎检索行后再进行过滤。示例如下：

# first_name 字段是一个普通的列，非索引列
mysql> EXPLAIN SELECT * FROM employees WHERE first_name = "Sumant";
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 299379 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

2. Using filesort：通常出现在GROUP BY或ORDER BY语句中，且排序或分组没有基于索引，此时需要使用文件在内存中进行排序。因为使用索引排序的性能好于使用文件排序，所以出现这种情况可以考虑通过添加索引进行优化。示例如下：

mysql> EXPLAIN SELECT * FROM employees ORDER BY first_name ;
+----+-------------+-----------+------+---------------+------+---------+------+--------+----------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra          |
+----+-------------+-----------+------+---------------+------+---------+------+--------+----------------+
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 299379 | Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+--------+----------------+

3. Using index：使用了覆盖索引进行查询，此时不需要访问表，从索引中就可以获取到所需的全部数据。示例如下：

mysql>  EXPLAIN SELECT emp_no FROM employees;
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows   | Extra       |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | employees | index | NULL          | PRIMARY | 4       | NULL | 299379 | Using index |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+

4. Using temporary：表示需要使用临时表来处理查询，常出现在GROUP BY或ORDER BY语句中，示例如下：

mysql> EXPLAIN SELECT first_name,count(first_name) FROM employees GROUP BY first_name ;
+----+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
+----+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 299379 | Using temporary; Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+

参考资料

更多参数的说明可以参考MySQL官方文档：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

最近更新于0001-01-01