MySQL EXPLAIN 输出列解释【详尽版】MySQL EXPLAIN 输出列解释【详尽版】简单来讲，通过EXPL

持续更新中....

1. 简介

本文测试使用的SQL文件:

链接：pan.baidu.com/s/1i11Ce2fx…
提取码：java

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语句中，但是此查询受外部查询的影响

mysql> EXPLAIN SELECT * FROM employees
    -> WHERE emp_no IN (SELECT 10001 UNION SELECT 10002);
+----+--------------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+
| id | select_type        | table      | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra           |
+----+--------------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+
|  1 | PRIMARY            | employees  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299468 |   100.00 | Using where     |
|  2 | DEPENDENT SUBQUERY | NULL       | NULL       | NULL | NULL          | NULL | NULL    | NULL |   NULL |     NULL | No tables used  |
|  3 | DEPENDENT UNION    | NULL       | NULL       | NULL | NULL          | NULL | NULL    | NULL |   NULL |     NULL | No tables used  |
| NULL | UNION RESULT       | <union2,3> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   NULL |     NULL | Using temporary |
+----+--------------------+------------+------------+------+---------------+------+---------+------+--------+----------+-----------------+
4 rows in set, 1 warning (0.00 sec)

SUBQUERY

子查询中的第一个SELECT，取决于外部查询，出现在复杂的不相关子查询中

举例如下：

mysql> EXPLAIN SELECT * FROM employees emp
    -> WHERE emp.emp_no IN (SELECT emp_no FROM dept_manager);
+----+-------------+--------------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+------------------------+
| id | select_type | table        | partitions | type   | possible_keys | key     | key_len | ref                           | rows | filtered | Extra                  |
+----+-------------+--------------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+------------------------+
|  1 | SIMPLE      | dept_manager | NULL       | index  | PRIMARY       | PRIMARY | 16      | NULL                          |   24 |   100.00 | Using index; LooseScan |
|  1 | SIMPLE      | emp          | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | employees.dept_manager.emp_no |    1 |   100.00 | NULL                   |
+----+-------------+--------------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+------------------------+
2 rows in set, 1 warning (0.00 sec)

发现table为dept_manager的id并不是2,而select_type也不是SUBQUERY
经查证资料，是因为MySQL优化器对IN进行了优化，IN中的子查询被优化成了相关子查询
对于使用IN子查询的语句，优化器将其重写为相关子查询。考虑以下使用不相关子查询的语句：

SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2);

大概是下面这个样子，大概率是，有大佬的话还请指点一下：

mysql> EXPLAIN SELECT /*+ SEMIJOIN(@subq1 LOOSESCAN) */ * FROM employees emp
    -> WHERE emp.emp_no IN (SELECT /*+ QB_NAME(subq1) */ emp_no FROM dept_manager);
+----+-------------+--------------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+------------------------+
| id | select_type | table        | partitions | type   | possible_keys | key     | key_len | ref                           | rows | filtered | Extra                  |
+----+-------------+--------------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+------------------------+
|  1 | SIMPLE      | dept_manager | NULL       | index  | PRIMARY       | PRIMARY | 16      | NULL                          |   24 |   100.00 | Using index; LooseScan |
|  1 | SIMPLE      | emp          | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | employees.dept_manager.emp_no |    1 |   100.00 | NULL                   |
+----+-------------+--------------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+------------------------+
2 rows in set, 1 warning (0.00 sec)

回到正题，select_type 为 SUBQUERY

mysql> EXPLAIN SELECT * FROM employees emp
    -> WHERE emp.emp_no IN (SELECT /*+ SUBQUERY(MATERIALIZATION) */ emp_no FROM dept_manager);
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| 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    | dept_manager | NULL       | index | PRIMARY       | dept_no | 12      | NULL |     24 |   100.00 | Using index |
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

注解产出的效果，参考官方文档：
dev.mysql.com/doc/refman/…

DEPENDENT SUBQUERY

如果子查询使用外部查询的任何引用/引用，则将其称为从属/相关子查询。

mysql> EXPLAIN SELECT emp.emp_no,emp.first_name FROM employees emp
    -> WHERE emp_no NOT IN (SELECT MAX(dm.emp_no) FROM dept_emp dm);
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+
| 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.01 sec)

DERIVED

当查询在派生表中使用内联视图时，将显示此关键字

mysql> EXPLAIN SELECT foo.first_name FROM
    -> (
    -> SELECT 1 AS first_name
    -> UNION
    -> SELECT 2 AS first_name
    -> ) AS foo;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL            |
|  2 | DERIVED      | NULL       | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used  |
|  3 | UNION        | NULL       | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used  |
| NULL | UNION RESULT | <union2,3> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
4 rows in set, 1 warning (0.00 sec)

MATERIALIZED

对子查询的物化视图，相当于缓存

mysql> EXPLAIN SELECT  * FROM employees emp
    -> WHERE emp.emp_no IN ( SELECT /*+ NO_RANGE_OPTIMIZATION(dept_emp PRIMARY) */ emp_no FROM dept_emp);
+----+--------------+-------------+------------+--------+---------------+------------+---------+----------------------+--------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys | key        | key_len | ref                  | rows   | filtered | Extra       |
+----+--------------+-------------+------------+--------+---------------+------------+---------+----------------------+--------+----------+-------------+
|  1 | SIMPLE       | emp         | NULL       | ALL    | PRIMARY       | NULL       | NULL    | NULL                 | 299468 |   100.00 | Using where |
|  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_key>    | <auto_key> | 4       | employees.emp.emp_no |      1 |   100.00 | NULL        |
|  2 | MATERIALIZED | dept_emp    | NULL       | index  | PRIMARY       | dept_no    | 12      | NULL                 | 308493 |   100.00 | Using index |
+----+--------------+-------------+------------+--------+---------------+------------+---------+----------------------+--------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

tips: MySQL的优化器真的很强大

在特定情况与MySQL优化器的加持下，双端模糊查询一样走索引的。

mysql> EXPLAIN SELECT emp_no,dept_no FROM dept_emp WHERE dept_no LIKE '%d00%';
+----+-------------+----------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | dept_emp | NULL       | index | NULL          | dept_no | 12      | NULL | 308493 |    11.11 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

不过，上面的emp_no与dept_no都是外键，所以也是索引,当查询的列中加一个不是索引的to_date

mysql> EXPLAIN SELECT emp_no,dept_no,to_date FROM dept_emp WHERE dept_no LIKE '%d00%';
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | dept_emp | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 308493 |    11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

上面的type为ALL，说明走了全表扫描

UNCACHEABLE SUBQUERY

子查询，其结果无法缓存，必须针对外部查询的每一行重新进行评估，表示使用子查询的结果不能被缓存

mysql> EXPLAIN SELECT * FROM employees emp 
    -> where emp.emp_no = (select de.emp_no from dept_emp de where de.dept_no=@@sort_buffer_size);
+----+----------------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------------+
| id | select_type          | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                          |
+----+----------------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------------+
|  1 | PRIMARY              | NULL  | NULL       | NULL  | NULL          | NULL    | NULL    | NULL |   NULL |     NULL | no matching row in const table |
|  2 | UNCACHEABLE SUBQUERY | de    | NULL       | index | dept_no       | dept_no | 12      | NULL | 308493 |    10.00 | Using where; Using index       |
+----+----------------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------------+
2 rows in set, 65535 warnings (0.82 sec)

UNCACHEABLE UNION

属于不可缓存子查询的UNION中的第二个或更高版本选择

mysql> EXPLAIN SELECT * FROM employees emp 
    -> WHERE emp.birth_date IN (
    -> SELECT to_date FROM (
    -> SELECT de.to_date,de.dept_no FROM dept_emp de WHERE de.dept_no=@@sort_buffer_size
    -> UNION
    -> SELECT dm.to_date,dm.dept_no FROM dept_manager dm WHERE dm.dept_no=@@sort_buffer_size) AS dep WHERE dep.dept_no=@@sort_buffer_size
    -> );
+----+-------------------+-------------+------------+--------+---------------+------------+---------+--------------------------+--------+----------+-----------------+
| 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     |
|  1 | PRIMARY           | <subquery2> | NULL       | eq_ref | <auto_key>    | <auto_key> | 3       | employees.emp.birth_date |      1 |   100.00 | NULL            |
|  2 | MATERIALIZED      | <derived3>  | NULL       | ALL    | NULL          | NULL       | NULL    | NULL                     |  30851 |    10.00 | Using where     |
|  3 | DERIVED           | de          | NULL       | ALL    | dept_no       | NULL       | NULL    | NULL                     | 308493 |    10.00 | Using where     |
|  4 | UNCACHEABLE UNION | dm          | NULL       | ALL    | dept_no       | NULL       | NULL    | NULL                     |     24 |    10.00 | Using where     |
| NULL | UNION RESULT      | <union3,4>  | NULL       | ALL    | NULL          | NULL       | NULL    | NULL                     |   NULL |     NULL | Using temporary |
+----+-------------------+-------------+------------+--------+---------------+------------+---------+--------------------------+--------+----------+-----------------+
6 rows in set, 5 warnings (0.00 sec)

非SELECT语句的select_type值显示受影响表的语句类型。例如，对于DELETE语句，select_type是DELETE。

2.3 table

表（JSON名称：table_name）
输出行所引用的表的名称。这也可以是以下值之一：

<unionM,N>：该行引用ID值为M和N的行的并集。
<derivedN>：该行引用ID值为N的行的派生表结果,派生表可能来自例如FROM子句中的子查询。
<subqueryN>：该行引用ID为N的行的实体化子查询的结果。

2.4 partitions

分区, JSON名称：partitions
查询将从中匹配记录的分区。对于非分区表，该值为NULL。

2.5 type

类型，JSON名称：access_type，联接类型。有关不同类型的说明。EXPLAIN输出的type列描述如何联接表。在JSON格式的输出中，将这些作为access_type属性的值。以下列表描述了连接类型，从最佳类型到最差类型：

system 该表只有一行，是const的特例。如果使用InnoDB引擎，InnoDB不能可靠地维护表的大小，因此查询优化器不能确保该表具有精确的1行。

mysql> alter table test_system engine=myisam;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select count(*) from test_system;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> EXPLAIN select * from test_system;
+----+-------------+-------------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table       | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------------+------------+--------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | test_system | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+--------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

const
该表最多具有一个匹配行，该行在查询开始时读取。因为只有一行，所以优化器的其余部分可以将这一行中列的值视为常量。 const表非常快，因为它们只能读取一次。

当使用主键索引或者唯一索引的全部时，这个表可以作为const表使用，例如：

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)

eq_ref 只匹配一行的时候，除了const,system的情况外，eq_ref是最好的类型了，当使用主键或者唯一索引的全部都用上的时候，只有唯一一条数据匹配到，在联表查询的时候使用主键索引或者唯一索引作为联表条件

mysql> EXPLAIN SELECT emp.emp_no,emp.first_name FROM employees emp,dept_manager dm  WHERE emp.emp_no = dm.emp_no;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                 | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
|  1 | SIMPLE      | dm    | NULL       | index  | PRIMARY       | dept_no | 12      | NULL                |   24 |   100.00 | Using index |
|  1 | SIMPLE      | emp   | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | employees.dm.emp_no |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

ref
对于上个表中行的每种组合，将从该表中读取最匹配的索引的所有行。
如果联接仅使用键的最左前缀

就是假如你配置了 A(列名)+B(列名)+C(列名) 组成的索引，使用 A 作为条件的话，就会使用 ref

例子：

mysql> ALTER TABLE `employees`.`employees` ADD INDEX first_last(`first_name`, `last_name`) USING BTREE;
Query OK, 0 rows affected (2.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM employees WHERE first_name = "Mary";
+----+-------------+-----------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | first_last    | first_last | 44      | const |  224 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

，或者如果键不是PRIMARY KEY或UNIQUE索引（换句话说，如果联接无法基于键值选择单个行），则使用ref。如果使用的键仅匹配几行，则这是一种很好的联接类型。

fulltext
ref_or_null
index_merge
unique_subquery
index_subquery
range
index
ALL

3.相关术语解释

3.1 相关子查询

依赖于主查询中的变量且不能独立运行的子查询。
因此，如果您不能单独运行子查询，则它是相关的。