MySQL SQL Explain输出学习--转发

目录

主要用法
测试数据
输出说明

id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra

总结
参考

正文

MySQL的explain命令语句提供了如何执行SQL语句的信息，解析SQL语句的执行计划并展示，explain支持select、delete、insert、replace和update等语句，也支持对分区表的解析。通常explain用来获取select语句的执行计划，通过explain展示的信息我们可以了解到表查询的顺序，表连接的方式等，并根据这些信息判断select执行效率，决定是否添加索引或改写SQL语句优化表连接方式以提高执行效率。本文参考官方文档：EXPLAIN Output Format对explain输出的内容进行说明，同时也对自己之前使用explain不清晰的方面进行总结。

本文使用的MySQL版本为官方社区版 5.7.24。

mysql root@localhost:(none)> select version();
+------------+
| version()  |
+------------+
| 5.7.24-log |
+------------+
1 row in set
Time: 0.066s

主要用法

{ EXPLAIN | DESCRIBE } [EXTENDED | PARTITIONS | FORMAT=[TRADITIONAL | JSON]] SQL_STATEMENT;

EXPLAIN和DESCRIBE(可以简写成DESC)都可以用来查看语句的执行计划，但通常使用EXPLAIN较多；
FORMAT选项可以指定执行计划输出信息为JSON格式，而且包含一些更详细的指标说明；
EXTENDED和PARTITIONS选项可以输出更详细选项说明，语法上是为了兼容低版本MySQL，未来会废弃，默认使用EXPLAIN命令即可。

测试数据

本文基于MySQL官方示例数据库employee：Example Databases进行解析说明，使用到的表如下：

-- employees：
mysql root@localhost:employees> show create table employees\G;
***************************[ 1. row ]***************************
Table        | employees
Create Table | CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`),
  KEY `idx_first_last` (`first_name`,`last_name`),
  KEY `idx_birth_hire` (`birth_date`,`hire_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set
Time: 0.008s

-- dept_emp：
mysql root@localhost:employees> show create table dept_emp\G;
***************************[ 1. row ]***************************
Table        | dept_emp
Create Table | CREATE TABLE `dept_emp` (
  `emp_no` int(11) NOT NULL,
  `dept_no` char(4) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`dept_no`),
  KEY `dept_no` (`dept_no`),
  CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
  CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set
Time: 0.010s

-- departments：
mysql root@localhost:employees> show create table departments\G;
***************************[ 1. row ]***************************
Table        | departments
Create Table | CREATE TABLE `departments` (
  `dept_no` char(4) NOT NULL,
  `dept_name` varchar(40) NOT NULL,
  PRIMARY KEY (`dept_no`),
  UNIQUE KEY `dept_name` (`dept_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set
Time: 0.012s

输出说明

mysql root@localhost:employees> explain select count(*) from employees;
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+--------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref    | rows   | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+--------+----------+-------------+
| 1  | SIMPLE      | employees | <null>     | index | <null>        | PRIMARY | 4       | <null> | 299512 | 100.0    | Using index |
+----+-------------+-----------+------------+-------+---------------+---------+---------+--------+--------+----------+-------------+
1 row in set
Time: 0.026s

通过以上示例语句得出explain输出有12个字段，主要说明如下表：

字段(Column)	JSON名称(JSON Name)	含义(Meaning)
id	select_id	标识符，语句涉及表的执行顺序
select_type	None	表查询类型
table	table_name	表名称
partitions	partitions	涉及表哪个分区
type	access_type	表的查询(连接)类型
possible_keys	possible_keys	表可能使用到的索引
key	key	表实际使用到的索引
key_len	key_length	表实际使用索引的长度，单位:字节
ref	ref	表哪些字段或者常量用于连接查找索引上的值
rows	rows	查询预估返回表的行数
filtered	filtered	表经过条件过滤之后与总数的百分比
Extra	None	额外的说明信息

id

id为select标识符，语句在执行计划当中的执行顺序。id值的出现有如下几种情况：

id值全相同，则按由上到下顺序执行；
id值全不相同，则按id值大小，由大到小顺序执行；
id值部分相同，部分不相同，则同组id值大的优先执行(组内id值相同的顺序执行)。

-- id全相同
mysql root@localhost:employees> explain select * from employees e,dept_emp d,departments de where e.emp_no = d.emp_no and de.dept_name = 'Human
                                Resources';

+----+-------------+-------+------------+-------+---------------+-----------+---------+--------------------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref                | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+--------------------+--------+----------+-------------+
| 1  | SIMPLE      | de    | <null>     | const | dept_name     | dept_name | 122     | const              | 1      | 100.0    | Using index |
| 1  | SIMPLE      | e     | <null>     | ALL   | PRIMARY       | <null>    | <null>  | <null>             | 299512 | 100.0    | <null>      |
| 1  | SIMPLE      | d     | <null>     | ref   | PRIMARY       | PRIMARY   | 4       | employees.e.emp_no | 1      | 100.0    | <null>      |
+----+-------------+-------+------------+-------+---------------+-----------+---------+--------------------+--------+----------+-------------+
3 rows in set
Time: 0.018s

-- id全不相同
mysql root@localhost:employees> explain select * from employees e where e.emp_no = (select d.emp_no from dept_emp d where d.dept_no = (select de.d
                                ept_no from departments de where de.dept_name = 'Development') and d.emp_no = 10023);
+----+-------------+-------+------------+-------+-----------------+-----------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys   | key       | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+-----------------+-----------+---------+-------------+------+----------+-------------+
| 1  | PRIMARY     | e     | <null>     | const | PRIMARY         | PRIMARY   | 4       | const       | 1    | 100.0    | <null>      |
| 2  | SUBQUERY    | d     | <null>     | const | PRIMARY,dept_no | PRIMARY   | 16      | const,const | 1    | 100.0    | Using index |
| 3  | SUBQUERY    | de    | <null>     | const | dept_name       | dept_name | 122     | const       | 1    | 100.0    | Using index |
+----+-------------+-------+------------+-------+-----------------+-----------+---------+-------------+------+----------+-------------+
3 rows in set
Time: 0.027s

-- id部分相同，部分不相同
mysql root@localhost:employees> explain select * from^Iemployees e where^Ie.emp_no in (select d.emp_no from dept_emp d where d.dept_no = (select d
                                e.dept_no from departments de where de.dept_name = 'Human Resources'));
+----+-------------+-------+------------+--------+-----------------+-----------+---------+--------------------+-------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys   | key       | key_len | ref                | rows  | filtered | Extra       |
+----+-------------+-------+------------+--------+-----------------+-----------+---------+--------------------+-------+----------+-------------+
| 1  | PRIMARY     | d     | <null>     | ref    | PRIMARY,dept_no | dept_no   | 12      | const              | 33212 | 100.0    | Using index |
| 1  | PRIMARY     | e     | <null>     | eq_ref | PRIMARY         | PRIMARY   | 4       | employees.d.emp_no | 1     | 100.0    | <null>      |
| 3  | SUBQUERY    | de    | <null>     | const  | dept_name       | dept_name | 122     | const              | 1     | 100.0    | Using index |
+----+-------------+-------+------------+--------+-----------------+-----------+---------+--------------------+-------+----------+-------------+
3 rows in set
Time: 0.020s

select_type

select_type为表查询的类型，根据官方文档总结几种常见类型如下表：

select_type值(Value)	JSON名称(JSON Name)	含义(Meaning)
SIMPLE	None	简单查询，不包含unino查询或子查询
PRIMARY	None	位于最外层的查询
UNION	None	当出现union查询时第二个或之后的查询
DEPENDENT UNION	dependent(true)	当出现union查询时第二个或之后的查询，取决于外部查询
UNION RESULT	union_result	union查询的结果
SUBQUERY	None	子查询当中第一个select查询
DEPENDENT SUBQUERY	dependent(true)	子查询当中第一个select查询，取决于外部的查询
DERIVED	None	派生表，from子句中出现的子查询

SIMPLE：最常见的查询类型，通常情况下没有子查询、union查询就是SIMPLE类型。

mysql root@localhost:employees> 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.0    | <null> |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
1 row in set
Time: 0.019s

PRIMARY和SUBQUERY：在含有子查询的语句中会出现。

mysql root@localhost:employees> explain select * from dept_emp d where d.dept_no = (select de.dept_no from departments de where de.dept_name = 'De
                                velopment');
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+--------+----------+-------------+
| 1  | PRIMARY     | d     | <null>     | ref   | dept_no       | dept_no   | 12      | const | 148054 | 100.0    | Using where |
| 2  | SUBQUERY    | de    | <null>     | const | dept_name     | dept_name | 122     | const | 1      | 100.0    | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+--------+----------+-------------+
2 rows in set
Time: 0.021s

UNION和UNION RESULT：在有union查询的语句中出现。

mysql root@localhost:employees> explain select * from departments where dept_no = 'd005' union select * from departments where dept_no = 'd004';
+--------+--------------+-------------+------------+-------+---------------+---------+---------+--------+--------+----------+-----------------+
| id     | select_type  | table       | partitions | type  | possible_keys | key     | key_len | ref    | rows   | filtered | Extra           |
+--------+--------------+-------------+------------+-------+---------------+---------+---------+--------+--------+----------+-----------------+
| 1      | PRIMARY      | departments | <null>     | const | PRIMARY       | PRIMARY | 12      | const  | 1      | 100.0    | <null>          |
| 2      | UNION        | departments | <null>     | const | PRIMARY       | PRIMARY | 12      | const  | 1      | 100.0    | <null>          |
| <null> | UNION RESULT | <union1,2>  | <null>     | ALL   | <null>        | <null>  | <null>  | <null> | <null> | <null>   | Using temporary |
+--------+--------------+-------------+------------+-------+---------------+---------+---------+--------+--------+----------+-----------------+
3 rows in set
Time: 0.020s

DEPENDENT UNION和DEPENDENT SUBQUERY：当语句中子查询和union查询依赖外部查询会出现。

mysql root@localhost:employees> explain select * from employees e where e.emp_no in (select d.emp_no from dept_emp d where d.from_date = '1986-06-
                                26' union select d.emp_no from dept_emp d where d.from_date = '1996-08-03');
+--------+--------------------+------------+------------+------+---------------+---------+---------+--------+--------+----------+-----------------+
| id     | select_type        | table      | partitions | type | possible_keys | key     | key_len | ref    | rows   | filtered | Extra          |
+--------+--------------------+------------+------------+------+---------------+---------+---------+--------+--------+----------+-----------------+
| 1      | PRIMARY            | e          | <null>     | ALL  | <null>        | <null>  | <null>  | <null> | 299512 | 100.0    | Using where    |
| 2      | DEPENDENT SUBQUERY | d          | <null>     | ref  | PRIMARY       | PRIMARY | 4       | func   | 1      |  10.0    | Using where    |
| 3      | DEPENDENT UNION    | d          | <null>     | ref  | PRIMARY       | PRIMARY | 4       | func   | 1      |  10.0    | Using where    |
| <null> | UNION RESULT       | <union2,3> | <null>     | ALL  | <null>        | <null>  | <null>  | <null> | <null> | <null>   | Using temporary|
+--------+--------------------+------------+------------+------+---------------+---------+---------+--------+--------+----------+-----------------+
4 rows in set
Time: 0.022s

DERIVED：当查询涉及生成临时表时出现。

mysql root@localhost:employees> explain select * from (select * from departments limit 5) de;
+----+-------------+-------------+------------+-------+---------------+-----------+---------+--------+------+----------+-------------+
| id | select_type | table       | partitions | type  | possible_keys | key       | key_len | ref    | rows | filtered | Extra       |
+----+-------------+-------------+------------+-------+---------------+-----------+---------+--------+------+----------+-------------+
| 1  | PRIMARY     | <derived2>  | <null>     | ALL   | <null>        | <null>    | <null>  | <null> | 5    | 100.0    | <null>      |
| 2  | DERIVED     | departments | <null>     | index | <null>        | dept_name | 122     | <null> | 9    | 100.0    | Using index |
+----+-------------+-------------+------------+-------+---------------+-----------+---------+--------+------+----------+-------------+
2 rows in set
Time: 0.012s

table

指执行计划当中当前是从哪张表获取数据，如果为表指定了别名，则显示别名，如果没有涉及对表的数据读取，则显示NULL，还有如下几种情形：

<unionM,N>：数据来自union查询的id为M和N的结果集；
：数据来自派生表id为N的结果集；
：数据来自子查询id为N的结果集。

partitions

指执行计划中当前从分区表哪个表分区获取数据，如果不是分区表，则显示为NULL。

-- 示例数据库employees的分区表salaries
mysql root@localhost:employees> show create table salaries;
+----------+-----------------------------------------------------------------+
| Table    | Create Table                                                    |
+----------+-----------------------------------------------------------------+
| salaries | CREATE TABLE `salaries` (                                       |
|          |   `emp_no` int(11) NOT NULL,                                    |
|          |   `salary` int(11) NOT NULL,                                    |
|          |   `from_date` date NOT NULL,                                    |
|          |   `to_date` date NOT NULL,                                      |
|          |   PRIMARY KEY (`emp_no`,`from_date`)                            |
|          | ) ENGINE=InnoDB DEFAULT CHARSET=utf8                            |
|          | /*!50500 PARTITION BY RANGE  COLUMNS(from_date)                 |
|          | (PARTITION p01 VALUES LESS THAN ('1985-12-31') ENGINE = InnoDB, |
|          |  PARTITION p02 VALUES LESS THAN ('1986-12-31') ENGINE = InnoDB, |
|          |  PARTITION p03 VALUES LESS THAN ('1987-12-31') ENGINE = InnoDB, |
|          |  PARTITION p04 VALUES LESS THAN ('1988-12-31') ENGINE = InnoDB, |
|          |  PARTITION p05 VALUES LESS THAN ('1989-12-31') ENGINE = InnoDB, |
|          |  PARTITION p06 VALUES LESS THAN ('1990-12-31') ENGINE = InnoDB, |
|          |  PARTITION p07 VALUES LESS THAN ('1991-12-31') ENGINE = InnoDB, |
|          |  PARTITION p08 VALUES LESS THAN ('1992-12-31') ENGINE = InnoDB, |
|          |  PARTITION p09 VALUES LESS THAN ('1993-12-31') ENGINE = InnoDB, |
|          |  PARTITION p10 VALUES LESS THAN ('1994-12-31') ENGINE = InnoDB, |
|          |  PARTITION p11 VALUES LESS THAN ('1995-12-31') ENGINE = InnoDB, |
|          |  PARTITION p12 VALUES LESS THAN ('1996-12-31') ENGINE = InnoDB, |
|          |  PARTITION p13 VALUES LESS THAN ('1997-12-31') ENGINE = InnoDB, |
|          |  PARTITION p14 VALUES LESS THAN ('1998-12-31') ENGINE = InnoDB, |
|          |  PARTITION p15 VALUES LESS THAN ('1999-12-31') ENGINE = InnoDB, |
|          |  PARTITION p16 VALUES LESS THAN ('2000-12-31') ENGINE = InnoDB, |
|          |  PARTITION p17 VALUES LESS THAN ('2001-12-31') ENGINE = InnoDB, |
|          |  PARTITION p18 VALUES LESS THAN ('2002-12-31') ENGINE = InnoDB, |
|          |  PARTITION p19 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */  |
+----------+-----------------------------------------------------------------+
1 row in set
Time: 0.018s

mysql root@localhost:employees> explain select * from salaries where from_date > '1985-12-31' and from_date < '1990-12-31';
+----+-------------+----------+---------------------+------+---------------+--------+---------+--------+--------+----------+-------------+
| id | select_type | table    | partitions          | type | possible_keys | key    | key_len | ref    | rows   | filtered | Extra       |
+----+-------------+----------+---------------------+------+---------------+--------+---------+--------+--------+----------+-------------+
| 1  | SIMPLE      | salaries | p02,p03,p04,p05,p06 | ALL  | <null>        | <null> | <null>  | <null> | 384341 | 11.11    | Using where |
+----+-------------+----------+---------------------+------+---------------+--------+---------+--------+--------+----------+-------------+
1 row in set
Time: 0.023s

type

type应该被认为是解读执行计划当中最重要的部分，根据type显示的内容可以判断语句总体的查询效率。主要有以下几种类型：

system：表只有一行(系统表)，是const的一种特殊情况。

-- 测试表departments_1生成：
mysql root@localhost:employees> create table departments_1 as select * from departments where dept_no='d005';
Query OK, 1 row affected
Time: 0.107s 

mysql root@localhost:employees> alter table departments_1 add primary key(dept_no);
Query OK, 0 rows affected

mysql root@localhost:employees> create index idx_dept_name on departments_1(dept_name);
Query OK, 0 rows affected

mysql root@localhost:employees> show create table departments_1\G;
***************************[ 1. row ]***************************
Table        | departments_1
Create Table | CREATE TABLE `departments_1` (
  `dept_no` char(4) NOT NULL,
  `dept_name` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`dept_no`),
  KEY `idx_dept_name` (`dept_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set
Time: 0.010s

-- 系统表：
mysql root@localhost:employees> explain select * from mysql.proxies_priv;
+----+-------------+--------------+------------+--------+---------------+--------+---------+--------+------+----------+--------+
| id | select_type | table        | partitions | type   | possible_keys | key    | key_len | ref    | rows | filtered | Extra  |
+----+-------------+--------------+------------+--------+---------------+--------+---------+--------+------+----------+--------+
| 1  | SIMPLE      | proxies_priv | <null>     | system | <null>        | <null> | <null>  | <null> | 1    | 100.0    | <null> |
+----+-------------+--------------+------------+--------+---------------+--------+---------+--------+------+----------+--------+
1 row in set
Time: 0.023s

-- 普通表：
mysql root@localhost:employees> explain select * from (select * from departments_1 where dept_no = 'd005' limit 1) de;
+----+-------------+---------------+------------+--------+---------------+---------+---------+--------+------+----------+--------+
| id | select_type | table         | partitions | type   | possible_keys | key     | key_len | ref    | rows | filtered | Extra  |
+----+-------------+---------------+------------+--------+---------------+---------+---------+--------+------+----------+--------+
| 1  | PRIMARY     | <derived2>    | <null>     | system | <null>        | <null>  | <null>  | <null> | 1    | 100.0    | <null> |
| 2  | DERIVED     | departments_1 | <null>     | const  | PRIMARY       | PRIMARY | 12      | const  | 1    | 100.0    | <null> |
+----+-------------+---------------+------------+--------+---------------+---------+---------+--------+------+----------+--------+
2 rows in set
Time: 0.015s

const：对于主键或者唯一索引键的等值查询，只返回一行数据。

mysql root@localhost:employees> explain select * from departments_1 where dept_no = 'd005';
+----+-------------+---------------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
| id | select_type | table         | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra  |
+----+-------------+---------------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
| 1  | SIMPLE      | departments_1 | <null>     | const | PRIMARY       | PRIMARY | 12      | const | 1    | 100.0    | <null> |
+----+-------------+---------------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
1 row in set
Time: 0.018s

eq_ref：对于前表的每一行数据，都只能匹配当前表唯一一行数据。除了system与const之外这是最好的一种连接查询类型，主键或者是非空唯一索引的所有部分都可以在连接时被使用，通常使用的是'='操作符，比较值可以是一个常量，也可以是一个在该表之前读取该表的字段表达式。

explain select * from departments d,departments_1 d1 where d.dept_no = d1.dept_no;
+----+-------------+-------+------------+--------+---------------+---------------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key           | key_len | ref                  | rows | filtered | Extra      |
+----+-------------+-------+------------+--------+---------------+---------------+---------+----------------------+------+----------+-------------+
| 1  | SIMPLE      | d1    | <null>     | index  | PRIMARY       | idx_dept_name | 123     | <null>               | 1    | 100.0    | Using index|
| 1  | SIMPLE      | d     | <null>     | eq_ref | PRIMARY       | PRIMARY       | 12      | employees.d1.dept_no | 1    | 100.0    | <null>     |
+----+-------------+-------+------------+--------+---------------+---------------+---------+----------------------+------+----------+-------------+
2 rows in set
Time: 0.037s

ref：对于前表的每一行数据，都从当前表读取所有匹配索引值的行。与eq_ref相比，连接查询字段不是主键或者唯一索引，又或者是复合索引的部分左前缀，如果连接查询匹配的是少量几行数据，ref是个不同错的选择，通常使用的运算符是'='、'<='或者'>='等。

mysql root@localhost:employees> explain select * from dept_emp where dept_no ='d005';
+----+-------------+----------+------------+------+---------------+---------+---------+-------+--------+----------+--------+
| id | select_type | table    | partitions | type | possible_keys | key     | key_len | ref   | rows   | filtered | Extra  |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+--------+----------+--------+
| 1  | SIMPLE      | dept_emp | <null>     | ref  | dept_no       | dept_no | 12      | const | 148054 | 100.0    | <null> |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+--------+----------+--------+
1 row in set
Time: 0.059s

mysql root@localhost:employees> explain select * from dept_emp d,departments_1 d1 where d.dept_no = d1.dept_no;
+----+-------------+-------+------------+------+---------------+---------+---------+----------------------+-------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref                  | rows  | filtered | Extra  |
+----+-------------+-------+------------+------+---------------+---------+---------+----------------------+-------+----------+--------+
| 1  | SIMPLE      | d1    | <null>     | ALL  | <null>        | <null>  | <null>  | <null>               | 1     | 100.0    | <null> |
| 1  | SIMPLE      | d     | <null>     | ref  | dept_no       | dept_no | 12      | employees.d1.dept_no | 41392 | 100.0    | <null> |
+----+-------------+-------+------------+------+---------------+---------+---------+----------------------+-------+----------+--------+
2 rows in set
Time: 0.012s

ref_or_null：同ref类型，但是包含了对NULL值的搜索。

mysql root@localhost:employees> explain select dept_name from departments_1 where dept_name = 'd005' or dept_name is null;
+----+-------------+---------------+------------+-------------+---------------+---------------+---------+-------+------+----------+--------------------------+
| id | select_type | table         | partitions | type        | possible_keys | key           | key_len | ref   | rows | filtered | Extra                   |
+----+-------------+---------------+------------+-------------+---------------+---------------+---------+-------+------+----------+--------------------------+
| 1  | SIMPLE      | departments_1 | <null>     | ref_or_null | idx_dept_name | idx_dept_name | 123     | const | 2    | 100.0    | Using where; Using index |
+----+-------------+---------------+------------+-------------+---------------+---------------+---------+-------+------+----------+--------------------------+
1 row in set
Time: 0.011s

index_merge：使用了索引合并优化进行查询。如果查询指定条件涉及对多个索引的使用时，会将多个索引合并操作。

mysql root@localhost:employees> explain select * from dept_emp where emp_no = 10001 or dept_no = (select dept_no from departments_1);
+----+-------------+---------------+------------+-------------+-----------------+-----------------+---------+--------+--------+----------+-------------------------------------------+
| id | select_type | table         | partitions | type        | possible_keys   | key             | key_len | ref    | rows   | filtered | Extra                                    |
+----+-------------+---------------+------------+-------------+-----------------+-----------------+---------+--------+--------+----------+-------------------------------------------+
| 1  | PRIMARY     | dept_emp      | <null>     | index_merge | PRIMARY,dept_no | PRIMARY,dept_no | 4,12    | <null> | 148055 | 100.0    | Using union(PRIMARY,dept_no); Using where |
| 2  | SUBQUERY    | departments_1 | <null>     | index       | <null>          | idx_dept_name   | 123     | <null> | 1      | 100.0    | Using index                               |
+----+-------------+---------------+------------+-------------+-----------------+-----------------+---------+--------+--------+----------+-------------------------------------------+
2 rows in set
Time: 0.014s

range：使用索引扫描条件指定范围内的数据。常用的操作符有'>'、'<'、'is null'、'between'、'in'和'like'等。

mysql root@localhost:employees> explain select de.* from dept_emp de,departments_1 d where de.dept_no = d.dept_no and de.emp_no < 10010;
+----+-------------+-------+------------+-------+-----------------+---------------+---------+--------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys   | key           | key_len | ref    | rows | filtered | Extra                                             |
+----+-------------+-------+------------+-------+-----------------+---------------+---------+--------+------+----------+----------------------------------------------------+
| 1  | SIMPLE      | d     | <null>     | index | PRIMARY         | idx_dept_name | 123     | <null> | 1    | 100.0    | Using index                                       |
| 1  | SIMPLE      | de    | <null>     | range | PRIMARY,dept_no | PRIMARY       | 4       | <null> | 9    |  12.5    | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+-----------------+---------------+---------+--------+------+----------+----------------------------------------------------+
2 rows in set
Time: 0.019s

index：使用索引全扫描。类似于全表扫描，只是扫描对象是索引，出现于以下两种情况：

如果索引是覆盖索引，即索引包含查询所需要的所有表数据，就只扫描索引，并且在Extra中出现Using index。通常情况下扫描索引比打描表要更快，因为索引一般比表来的小；
全表扫描采用索引的顺序来读取数据，本质上还是全表扫描，并且在Extra中不会出现Using index，避免再进行排序消耗性能，因为索引本身就是排序好的。

mysql root@localhost:employees> explain select dept_name from departments_1;
+----+-------------+---------------+------------+-------+---------------+---------------+---------+--------+------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key           | key_len | ref    | rows | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+---------------+---------+--------+------+----------+-------------+
| 1  | SIMPLE      | departments_1 | <null>     | index | <null>        | idx_dept_name | 123     | <null> | 1    | 100.0    | Using index |
+----+-------------+---------------+------------+-------+---------------+---------------+---------+--------+------+----------+-------------+
1 row in set
Time: 0.020s

all：使用全表扫描。

mysql root@localhost:employees> drop index idx_dept_name on departments_1;
Query OK, 0 rows affected
Time: 0.052s

mysql root@localhost:employees> explain select * from departments_1;
+----+-------------+---------------+------------+------+---------------+--------+---------+--------+------+----------+--------+
| id | select_type | table         | partitions | type | possible_keys | key    | key_len | ref    | rows | filtered | Extra  |
+----+-------------+---------------+------------+------+---------------+--------+---------+--------+------+----------+--------+
| 1  | SIMPLE      | departments_1 | <null>     | ALL  | <null>        | <null> | <null>  | <null> | 1    | 100.0    | <null> |
+----+-------------+---------------+------------+------+---------------+--------+---------+--------+------+----------+--------+
1 row in set
Time: 0.018s

通过以上各种主要类型的分析，可以总结出各个类型性能排序(从左到右性能从高到低)：

system > const > eq_ref > ref > range > index > all

possible_keys

显示了MySQL在查找当前表中数据的时候可能使用到的索引，如果该字段值为NULL，则表明没有相关索引可用。

key

显示了MySQL在实际查找数据时决定使用的索引，如果该字段值为NULL，则表明没有使用索引。

key_len

显示了MySQL实际使用索引的键大小，单位字节。可以通过key_len的大小判断评估复合索引使用了哪些部分，如果key字段值为NULL，则key_len的值也为NULL。
几种常见字段类型索引长度大小如下，假设字符编码为UTF8：

字段属性是否允许NULL，如果允许NULL，则需要额外增加一个字节；
字符型：
- char(n)：3n个字节
- varchar(n)：3n+2个字节
数值型：
- tinyint：1个字节
- int：4个字节
- bigint：8个字节
时间型：
- date：3个字节
- datetime：5个字节+秒精度字节
- timestamp：4个字节+秒精度字节
- 秒精度字节(最大6位)：
  - 1~2位：1个字节
  - 3~4位：2个字节
  - 5~6位：3个字节

ref

显示哪些常量或者字段被用于查询索引列键值，以获取表中数据行。

如果是常量等值查询，则显示为const；
如果是连接查询，则被驱动表的该字段会显示驱动表的所关联字段；
如果条件当中使用函数表达式，或者值导致条件字段发生隐式转换，这里显示为func。

mysql root@localhost:employees> explain select * from departments d,departments_1 d1 where d.dept_no = d1.dept_no;
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+--------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                  | rows | filtered | Extra  |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+--------+
| 1  | SIMPLE      | d1    | <null>     | ALL    | PRIMARY       | <null>  | <null>  | <null>               | 1    | 100.0    | <null> |
| 1  | SIMPLE      | d     | <null>     | eq_ref | PRIMARY       | PRIMARY | 12      | employees.d1.dept_no | 1    | 100.0    | <null> |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+--------+
2 rows in set
Time: 0.038s

rows

显示预估需要查询的行数。对InnoDB表来说这是个预估值，并非是个准确值。

filtered

显示按表条件过滤的表行的估计百分比。

Extra

显示查询时的额外信息。常见的有如下几种：

Using index
仅查询索引树就可以获取到所需要的数据行，而不需要读取表中实际的数据行。通常适用于select字段就是查询使用索引的一部分，即使用了覆盖索引。

mysql root@localhost:employees> explain select dept_name from departments_1;
+----+-------------+---------------+------------+-------+---------------+---------------+---------+--------+------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key           | key_len | ref    | rows | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+---------------+---------+--------+------+----------+-------------+
| 1  | SIMPLE      | departments_1 | <null>     | index | <null>        | idx_dept_name | 123     | <null> | 1    | 100.0    | Using index |
+----+-------------+---------------+------------+-------+---------------+---------------+---------+--------+------+----------+-------------+
1 row in set
Time: 0.015s

Using index condition
显示采用了Index Condition Pushdown (ICP)特性通过索引去表中获取数据。关于ICP特性可以参考官方文档：Index Condition Pushdown Optimization。简单说法如下：

如果开启ICP特性，部分where条件部分可以下推到存储引擎通过索引进行过滤，ICP可以减少存储引擎访问基表的次数；
如果没有开启ICP特性，则存储引擎根据索引需要直接访问基表获取数据并返回给server层进行where条件的过滤。

-- employees表创建复合索引idx_birth_hire
mysql root@localhost:employees> create index idx_birth_hire on employees(birth_date,hire_date);
Query OK, 0 rows affected
Time: 0.768s

mysql root@localhost:employees> explain select * from employees where birth_date = '1960-01-01' and hire_date > '1980-01-01';
+----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys  | key            | key_len | ref    | rows | filtered | Extra                |
+----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+------+----------+-----------------------+
| 1  | SIMPLE      | employees | <null>     | range | idx_birth_hire | idx_birth_hire | 6       | <null> | 63   | 100.0    | Using index condition |
+----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+------+----------+-----------------------+
1 row in set
Time: 0.016s

Using index for group-by
跟Using index访问表的方式类似，显示MySQL通过索引就可以完成对GROUP BY或DISTINCT字段的查询，而无需再访问表中的数据。

mysql root@localhost:employees> explain select distinct dept_no from dept_emp;
+----+-------------+----------+------------+-------+-----------------+---------+---------+--------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys   | key     | key_len | ref    | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+-----------------+---------+---------+--------+------+----------+--------------------------+
| 1  | SIMPLE      | dept_emp | <null>     | range | PRIMARY,dept_no | dept_no | 12      | <null> | 9    | 100.0    | Using index for group-by |
+----+-------------+----------+------------+-------+-----------------+---------+---------+--------+------+----------+--------------------------+
1 row in set
Time: 0.020s

Using where
显示MySQL通过索引条件定位之后还需要返回表中获得所需要的数据。

mysql root@localhost:employees> explain select * from employees where birth_date < '1970-01-01';
+----+-------------+-----------+------------+------+----------------+--------+---------+--------+--------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys  | key    | key_len | ref    | rows   | filtered | Extra       |
+----+-------------+-----------+------------+------+----------------+--------+---------+--------+--------+----------+-------------+
| 1  | SIMPLE      | employees | <null>     | ALL  | idx_birth_hire | <null> | <null>  | <null> | 299512 | 50.0     | Using where |
+----+-------------+-----------+------------+------+----------------+--------+---------+--------+--------+----------+-------------+
1 row in set
Time: 0.016s

Impossible WHERE
where子句的条件永远都不可能为真。

mysql root@localhost:employees> explain select * from employees where 1 = 0;
+----+-------------+--------+------------+--------+---------------+--------+---------+--------+--------+----------+------------------+
| id | select_type | table  | partitions | type   | possible_keys | key    | key_len | ref    | rows   | filtered | Extra            |
+----+-------------+--------+------------+--------+---------------+--------+---------+--------+--------+----------+------------------+
| 1  | SIMPLE      | <null> | <null>     | <null> | <null>        | <null> | <null>  | <null> | <null> | <null>   | Impossible WHERE |
+----+-------------+--------+------------+--------+---------------+--------+---------+--------+--------+----------+------------------+
1 row in set
Time: 0.015s

Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)
在表联接过程当中，将先前表的部分数据读取到join buffer缓冲区中，然后从缓冲区中读取数据与当前表进行连接。主要有两种算法：Block Nested Loop和Batched Key Access，关于这两种算法说明可以参考官方文档：Block Nested-Loop and Batched Key Access Joins，也可以参考另一篇博文说明：MySQL 查询优化之 Block Nested-Loop 与 Batched Key Access Joins。

-- Block Nested Loop
mysql root@localhost:employees> explain select * from employees e,dept_emp d where e.emp_no > 10001 and e.emp_no <> d.emp_no;
+----+-------------+-------+------------+-------+---------------+---------+---------+--------+--------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref    | rows   | filtered | Extra                                             |
+----+-------------+-------+------------+-------+---------------+---------+---------+--------+--------+----------+----------------------------------------------------+
| 1  | SIMPLE      | e     | <null>     | range | PRIMARY       | PRIMARY | 4       | <null> | 149756 | 100.0    | Using where                                       |
| 1  | SIMPLE      | d     | <null>     | ALL   | <null>        | <null>  | <null>  | <null> | 331143 |  90.0    | Using where; Using join buffer(Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+---------+---------+--------+--------+----------+----------------------------------------------------+
2 rows in set
Time: 0.020s

-- Batched Key Access
mysql root@localhost:employees> explain SELECT /*+ bka(a)*/ a.gender, b.dept_no FROM employees a, dept_emp b WHERE a.birth_date = b.from_date;
+----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+--------+----------+----------------------------------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref                   | rows   | filtered | Extra                                 |
+----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+--------+----------+----------------------------------------+
| 1  | SIMPLE      | b     | <null>     | ALL  | <null>         | <null>         | <null>  | <null>                | 331143 | 100.0    | <null>                                |
| 1  | SIMPLE      | a     | <null>     | ref  | idx_birth_hire | idx_birth_hire | 3       | employees.b.from_date | 63     | 100.0    | Using join buffer (Batched Key Access) |
+----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+--------+----------+----------------------------------------+
2 rows in set
Time: 0.014s

Using MRR
读取数据采用多范围读(Multi-Range Read)的优化策略。关于MRR特性也可以参考官方文档：Multi-Range Read Optimization

mysql root@localhost:employees> set optimizer_switch='mrr=on,mrr_cost_based=off';
Query OK, 0 rows affected
Time: 0.001s
mysql root@localhost:employees> explain select * from employees where birth_date = '1970-01-01' and hire_date > '1990-01-01';
+----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+------+----------+----------------------------------+
| id | select_type | table     | partitions | type  | possible_keys  | key            | key_len | ref    | rows | filtered | Extra                           |
+----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+------+----------+----------------------------------+
| 1  | SIMPLE      | employees | <null>     | range | idx_birth_hire | idx_birth_hire | 6       | <null> | 1    | 100.0    | Using index condition; Using MRR |
+----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+------+----------+----------------------------------+
1 row in set
Time: 0.014s

Range checked for each record (index map: N)
MySQL在获取数据时发现在没有索引可用，但当获取部分先前表字段值时发现可以采用当前表某些索引来获取数据。index map展示的是一个掩码值，如index map:0x19，对应二进制值为11001，表示当前表索引编号为1、4和5号索引可能被用来获取数据，索引编号通过SHOW INDEX语句获得。

mysql root@localhost:employees> explain select * from employees e,dept_emp d where e.emp_no > d.emp_no;
+----+-------------+-------+------------+------+---------------+--------+---------+--------+--------+----------+------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref    | rows   | filtered | Extra                                         |
+----+-------------+-------+------------+------+---------------+--------+---------+--------+--------+----------+------------------------------------------------+
| 1  | SIMPLE      | d     | <null>     | ALL  | PRIMARY       | <null> | <null>  | <null> | 331143 | 100.0    | <null>                                        |
| 1  | SIMPLE      | e     | <null>     | ALL  | PRIMARY       | <null> | <null>  | <null> | 299512 |  33.33   | Range checked for each record (index map: 0x1) |
+----+-------------+-------+------------+------+---------------+--------+---------+--------+--------+----------+------------------------------------------------+
2 rows in set
Time: 0.038s

Select tables optimized away
MySQL优化器能够确定以下两点：

最多只有一行记录被返回；
为了获取这一行数据，有一定的结果集需要获取。

当语句在优化器阶段过程中可以获取查询结果(如获取行数，只需要读取相应索引数据)，而无需再返回表中查询数据，可能会出现Select tables optimized away。例如针对MyISAM引擎的表，使用select count(*)获取表的总行数，而且又没有where子句或者条件总是为真，也没有GROUP BY子句时，其实就包含了以上的条件且隐式含有GROUP BY分组的效果。

-- 创建MyISAM引擎的employees表
mysql root@localhost:employees> create table employees_myisam like employees;
Query OK, 0 rows affected
Time: 0.040s
mysql root@localhost:employees> insert into employees_myisam select * from employees;
Query OK, 300024 rows affected
Time: 5.023s
mysql root@localhost:employees> alter table employees_myisam engine=MyISAM;
Query OK, 300024 rows affected
Time: 1.515s

-- 获取执行count(*)查询行数执行计划
mysql root@localhost:employees> explain select count(*) from employees_myisam;
+----+-------------+--------+------------+--------+---------------+--------+---------+--------+--------+----------+------------------------------+
| id | select_type | table  | partitions | type   | possible_keys | key    | key_len | ref    | rows   | filtered | Extra                        |
+----+-------------+--------+------------+--------+---------------+--------+---------+--------+--------+----------+------------------------------+
| 1  | SIMPLE      | <null> | <null>     | <null> | <null>        | <null> | <null>  | <null> | <null> | <null>   | Select tables optimized away |
+----+-------------+--------+------------+--------+---------------+--------+---------+--------+--------+----------+------------------------------+
1 row in set
Time: 0.024s

Using temporary
MySQL需要创建临时表来存放查询结果集。通常发生在有GROUP BY或ORDER BY子句的语句当中。

mysql root@localhost:employees> explain select hire_date from employees group by hire_date;
+----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+--------+----------+----------------------------------------------+
| id | select_type | table     | partitions | type  | possible_keys  | key            | key_len | ref    | rows   | filtered | Extra
                          |
+----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+--------+----------+----------------------------------------------+
| 1  | SIMPLE      | employees | <null>     | index | idx_birth_hire | idx_birth_hire | 6       | <null> | 299512 | 100.0    | Using index; Using temporary; Using filesort |
+----+-------------+-----------+------------+-------+----------------+----------------+---------+--------+--------+----------+----------------------------------------------+
1 row in set
Time: 0.018s

Using filesort
MySQL需要对获取的数据进行额外的一次排序操作，无法通过索引的排序完成。通常发生在有ORDER BY子句的语句当中。

mysql root@localhost:employees> explain select * from employees order by hire_date;
+----+-------------+-----------+------------+------+---------------+--------+---------+--------+--------+----------+----------------+
| id | select_type | table     | partitions | type | possible_keys | key    | key_len | ref    | rows   | filtered | Extra          |
+----+-------------+-----------+------------+------+---------------+--------+---------+--------+--------+----------+----------------+
| 1  | SIMPLE      | employees | <null>     | ALL  | <null>        | <null> | <null>  | <null> | 299512 | 100.0    | Using filesort |
+----+-------------+-----------+------------+------+---------------+--------+---------+--------+--------+----------+----------------+
1 row in set
Time: 0.015s

总结

以上内容总结了MySQL获取执行计划explain命令执行时输出的主要字段说明，还有许多未仔细说明的参数和选项，以后还需多多实践总结。可以看出explain命令输出内容当中比较重要的是：

type：展示了表的查询/连接类型，体现查询效率；
key/key_len：实际使用了什么索引，使用了哪些部分索引；
Extra：对执行计划步骤额外的说明，采用了哪些查询特性。

参考

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
https://dev.mysql.com/doc/index-other.html
https://blog.csdn.net/u012410733/article/details/66472157
https://blog.csdn.net/wanbin6470398/article/details/82425620
https://blog.csdn.net/tianya9704/article/details/80067860

☆〖本人水平有限，文中如有错误还请留言批评指正！〗☆

posted @ 2021-08-04 14:19 北方客888 阅读(89) 评论(0) 编辑收藏举报

刷新页面返回顶部