526互联

MySQL之Explain

发布时间 2023-08-16 15:06:07作者: yangleduo114

执行计划输出中各列详解

table

EXPLAIN语句输出的每条记录都对应着某个单表的访问方法， 该条记录的table列代表着该表的表名
这个查询语句只涉及对sys_user表的单表查询， 所以EXPLAIN输出中只有一条记录， 其中的table列的值是sys_user， 表明这条记录是用来说明对sys_user表的单表访问方法的。

mysql> EXPLAIN SELECT * FROM sys_user;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | sys_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1250 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

这个连接查询的执行计划中有两条记录， 这两条记录的table列分别是sys_user和sys_user_role， 这两条记录用来分别说明对sys_user表和sys_user_role表的访问方法是什么。

mysql> EXPLAIN SELECT * FROM sys_user INNER JOIN sys_user_role;
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table         | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | sys_user      | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1250 |   100.00 | NULL                                  |
|  1 | SIMPLE      | sys_user_role | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4997 |   100.00 | Using join buffer (Block Nested Loop) |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+

id

每个表都会对应一条记录

查询语句中每出现一个SELECT关键字， MySQL会为它分配一个唯一的id值。 这个id值就是EXPLAIN语句的第一个列，对于连接查询来说， 一个SELECT关键字后边的FROM子句中可以跟随多个表， 所以在连接查询的执行计划中， 每个表都会对应一条记录， 但是这些记录的id值都是**相同的 **

  • 案例
mysql> EXPLAIN SELECT * FROM sys_user INNER JOIN sys_user_role;
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table         | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | sys_user      | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1250 |   100.00 | NULL                                  |
|  1 | SIMPLE      | sys_user_role | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4997 |   100.00 | Using join buffer (Block Nested Loop) |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
2 rows in set, 1 warning (0.00 sec)

在连接查询的执行计划中， 每个表都会对应一条记录， 这些记录的id列的值是相同的， 出现在前边的表表示驱动表， 出现在后边的表表示被驱动表。 所以从上边的EXPLAIN输出中， 查询优化器准备让sys_user表作为驱动表， 让sys_user_role表作为被驱动表来执行查询。

子查询每个SELECT关键字都会对应一个唯一的id值

对于包含子查询的查询语句来说， 就可能涉及多个SELECT关键字， 所以在包含子查询的查询语句的执行计划中， 每个SELECT关键字都会对应一个唯一的id值

mysql> EXPLAIN SELECT * FROM sys_user WHERE id IN (SELECT user_id FROM sys_announcement_send) OR phone = '15106196276';
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table                 | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | PRIMARY     | sys_user              | NULL       | ALL  | index_phone   | NULL | NULL    | NULL |  1250 |   100.00 | Using where |
|  2 | SUBQUERY    | sys_announcement_send | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 62062 |   100.00 | NULL        |
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

优化器优化了子查询

注意：查询优化器可能对涉及子查询的查询语句进行重写， 从而转换为连接查询。 所以如果想知道查询优化器对某个包含子查询的语句是否进行了重写， 直接查看执行计划就可以了。例如下面的查询， 虽然查询语句是包含一个子查询， 但是执行计划中sys_announcement_send和sys_user表对应的记录的id值全部是1， 这就表明了查询优化器将子查询转换为了连接查询。

mysql> EXPLAIN SELECT *  FROM sys_announcement_send  WHERE user_id IN  (SELECT id FROM sys_user);
+----+-------------+-----------------------+------------+--------+---------------+---------+---------+---------------------------------------+-------+----------+--------------------------+
| id | select_type | table                 | partitions | type   | possible_keys | key     | key_len | ref                                   | rows  | filtered | Extra                    |
+----+-------------+-----------------------+------------+--------+---------------+---------+---------+---------------------------------------+-------+----------+--------------------------+
|  1 | SIMPLE      | sys_announcement_send | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                                  | 62062 |   100.00 | Using where              |
|  1 | SIMPLE      | sys_user              | NULL       | eq_ref | PRIMARY       | PRIMARY | 386     | ei_fire.sys_announcement_send.user_id |     1 |   100.00 | Using where; Using index |
+----+-------------+-----------------------+------------+--------+---------------+---------+---------+---------------------------------------+-------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

UNION 查询创建临时表

UNION子句是为了把id为1的查询和id为2的查询的结果集合并起来并去重， 所以在内部创建了一个名为<union1, 2>的临时表（就是执行计划第三条记录的table列的名称） ， id为NULL表明这个临时表是为了合并两个查询的结果集而创建的。

mysql> EXPLAIN SELECT id FROM sys_user UNION SELECT user_id FROM sys_user_role;
+----+--------------+---------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------+
| id | select_type  | table         | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra           |
+----+--------------+---------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | sys_user      | NULL       | index | NULL          | index_phone | 36      | NULL | 1250 |   100.00 | Using index     |
|  2 | UNION        | sys_user_role | NULL       | ALL   | NULL          | NULL        | NULL    | NULL | 4997 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2>    | NULL       | ALL   | NULL          | NULL        | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+---------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

UNION ALL查询不创建临时表

UNION ALL就不需要为最终的结果集进行去重， 它只是单纯的把多个查询的结果集中的记录合并成一个并返回给用户， 所以也就不需要使用临时表

mysql> EXPLAIN SELECT id FROM sys_user UNION ALL SELECT user_id FROM sys_user_role;
+----+-------------+---------------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | sys_user      | NULL       | index | NULL          | index_phone | 36      | NULL | 1250 |   100.00 | Using index |
|  2 | UNION       | sys_user_role | NULL       | ALL   | NULL          | NULL        | NULL    | NULL | 4997 |   100.00 | NULL        |
+----+-------------+---------------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

总结

一条大的查询语句里边可以包含若干个SELECT关键字， 每个SELECT关键字代表着一个小的查询语句， 而每个SELECT关键字的FROM子句中都可以包含若干张表（这些表用来做连接查询） ， 每一张表都对应着执行计划输出中的一条记录， 对于在同一个SELECT关键字中的表来说， 它们的id值是相同的

select_type

表示查询的类型

SIMPLE Simple SELECT (not using UNION or subqueries)
PRIMARY Outermost SELECT
UNION Second or later SELECT statement in a UNION
UNION RESULT Result of a UNION
SUBQUERY First SELECT in subquery
DEPENDENT SUBQUERY First SELECT in subquery, dependent on outer query
DEPENDENT UNION Second or later SELECT statement in a UNION, dependent on outer query
DERIVED Derived table
MATERIALIZED Materialized subquery
UNCACHEABLE SUBQUERY A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

SIMPLE

查询语句中不包含UNION或者子查询的查询都算作是SIMPLE类型，当然， 连接查询也算是SIMPLE类型

mysql> EXPLAIN SELECT * FROM sys_user;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | sys_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1250 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)


mysql>  EXPLAIN SELECT * FROM sys_user INNER JOIN sys_user_role;
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table         | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | sys_user      | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1250 |   100.00 | NULL                                  |
|  1 | SIMPLE      | sys_user_role | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4997 |   100.00 | Using join buffer (Block Nested Loop) |
+----+-------------+---------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
2 rows in set, 1 warning (0.00 sec)

PRIMARY

对于包含UNION、 UNION ALL或者子查询的大查询来说， 它是由几个小查询组成的， 其中最左边的那个查询的select_type值就是PRIMARY

mysql> EXPLAIN SELECT id FROM sys_user UNION SELECT user_id FROM sys_user_role;
+----+--------------+---------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------+
| id | select_type  | table         | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra           |
+----+--------------+---------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | sys_user      | NULL       | index | NULL          | index_phone | 36      | NULL | 1250 |   100.00 | Using index     |
|  2 | UNION        | sys_user_role | NULL       | ALL   | NULL          | NULL        | NULL    | NULL | 4997 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2>    | NULL       | ALL   | NULL          | NULL        | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+---------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT id FROM sys_user UNION ALL SELECT user_id FROM sys_user_role;
+----+-------------+---------------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | sys_user      | NULL       | index | NULL          | index_phone | 36      | NULL | 1250 |   100.00 | Using index |
|  2 | UNION       | sys_user_role | NULL       | ALL   | NULL          | NULL        | NULL    | NULL | 4997 |   100.00 | NULL        |
+----+-------------+---------------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+

UNION

对于包含UNION或者UNION ALL的大查询来说， 它是由几个小查询组成的， 其中除了最左边的那个小查询以外， 其余的小查询的select_type值就是UNION

 EXPLAIN SELECT id FROM sys_user UNION SELECT user_id FROM sys_user_role UNION SELECT user_id FROM sys_user_tenant;
+----+--------------+-----------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------+
| id | select_type  | table           | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra           |
+----+--------------+-----------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | sys_user        | NULL       | index | NULL          | index_phone | 36      | NULL | 1250 |   100.00 | Using index     |
|  2 | UNION        | sys_user_role   | NULL       | ALL   | NULL          | NULL        | NULL    | NULL | 4997 |   100.00 | NULL            |
|  3 | UNION        | sys_user_tenant | NULL       | ALL   | NULL          | NULL        | NULL    | NULL |    2 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2,3>    | NULL       | ALL   | NULL          | NULL        | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+-----------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------+
4 rows in set, 1 warning (0.00 sec)

UNION RESULT

使用临时表来完成UNION查询的去重工作， 针对该临时表的查询的select_type就是UNION RESULT

mysql> EXPLAIN SELECT id FROM sys_user UNION SELECT user_id FROM sys_user_role;
+----+--------------+---------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------+
| id | select_type  | table         | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra           |
+----+--------------+---------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | sys_user      | NULL       | index | NULL          | index_phone | 36      | NULL | 1250 |   100.00 | Using index     |
|  2 | UNION        | sys_user_role | NULL       | ALL   | NULL          | NULL        | NULL    | NULL | 4997 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2>    | NULL       | ALL   | NULL          | NULL        | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+---------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

SUBQUERY

如果包含子查询的查询语句不能够转为对应的semi-join的形式， 并且该子查询是不相关子查询， 并且查询优化器决定采用将该子查询物化的方案来执行该子查询时， 该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY。

mysql> EXPLAIN SELECT * FROM sys_user WHERE id IN (SELECT user_id FROM sys_announcement_send) OR phone = '15106196276' ;
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table                 | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | PRIMARY     | sys_user              | NULL       | ALL  | index_phone   | NULL | NULL    | NULL |  1250 |   100.00 | Using where |
|  2 | SUBQUERY    | sys_announcement_send | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 62062 |   100.00 | NULL        |
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

select_type为SUBQUERY的子查询由于会被物化， 所以只需要执行一遍

DEPENDENT SUBQUERY

如果包含子查询的查询语句不能够转为对应的semi-join的形式， 并且该子查询是相关子查询， 则该子查询的第一个SELECT关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY

mysql> EXPLAIN  SELECT p.* FROM sys_permission p WHERE EXISTS(SELECT a.id FROM sys_dept_permission a JOIN sys_dept b ON a.dept_id = b.id WHERE p.id = a.menu_id   AND a.status = 0 );
+----+--------------------+-------+------------+--------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+
| id | select_type        | table | partitions | type   | possible_keys         | key                   | key_len | ref               | rows | filtered | Extra       |
+----+--------------------+-------+------------+--------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+
|  1 | PRIMARY            | p     | NULL       | ALL    | NULL                  | NULL                  | NULL    | NULL              |  202 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | a     | NULL       | ref    | FK_FK_PERMISSION_DEPT | FK_FK_PERMISSION_DEPT | 386     | ei_fire.p.id      |    3 |    10.00 | Using where |
|  2 | DEPENDENT SUBQUERY | b     | NULL       | eq_ref | PRIMARY               | PRIMARY               | 386     | ei_fire.a.dept_id |    1 |   100.00 | Using index |
+----+--------------------+-------+------------+--------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+
3 rows in set, 2 warnings (0.00 sec)

select_type为DEPENDENT SUBQUERY的查询可能会被执行多次

DEPENDENT UNION

在包含UNION或者UNION ALL的大查询中， 如果各个小查询都依赖于外层查询的话， 那除了最左边的那个小查询之外， 其余的小查询的select_type的值就是DEPENDENT UNION。

mysql> EXPLAIN SELECT * FROM sys_user_tenant  WHERE user_id IN (SELECT id FROM sys_user UNION SELECT user_id FROM sys_user_role UNION SELECT user_id FROM sys_user_tenant);
+----+--------------------+-----------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+
| id | select_type        | table           | partitions | type   | possible_keys | key     | key_len | ref  | rows | filtered | Extra           |
+----+--------------------+-----------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+
|  1 | PRIMARY            | sys_user_tenant | NULL       | ALL    | NULL          | NULL    | NULL    | NULL |    2 |   100.00 | Using where     |
|  2 | DEPENDENT SUBQUERY | sys_user        | NULL       | eq_ref | PRIMARY       | PRIMARY | 386     | func |    1 |   100.00 | Using index     |
|  3 | DEPENDENT UNION    | sys_user_role   | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 4997 |    10.00 | Using where     |
|  4 | DEPENDENT UNION    | sys_user_tenant | NULL       | ALL    | NULL          | NULL    | NULL    | NULL |    2 |    50.00 | Using where     |
| NULL | UNION RESULT     | <union2,3,4>    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------------+-----------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+
5 rows in set, 1 warning (0.00 sec)

大查询里包含了一个子查询， 子查询里又是由UNION连起来的三个小查询。 从执行计划中可以看出来，SELECT id FROM sys_user 这个小查询由于是子查询中第一个查询， 所以它的select_type是DEPENDENT SUBQUERY， 而SELECT user_id FROM sys_user_role以及SELECT user_id FROM sys_user_tenant这两个个查询的select_type就是DEPENDENT UNION。

DERIVED

派生表

把子查询放在外层查询的FROM子句后， 那么这个子查询的结果相当于一个派生表， 比如下边这个查询：

SELECT * FROM (
SELECT id AS d_id, key3 AS d_key3 FROM s2 WHERE key1 = 'a'
) AS derived_s1 WHERE d_key3 = 'a';

子查询( SELECT id AS d_id, key3 AS d_key3 FROM s2 WHERE key1 = 'a')的结果就相当于一个派生表， 这个表的名称是derived_s1， 该表有两个列， 分别是d_id和d_key3

对于采用物化的方式执行的包含派生表的查询， 该派生表对应的子查询的select_type就是DERIVED


mysql> EXPLAIN SELECT * FROM ( SELECT count(1) FROM sys_user ) a;
+----+-------------+------------+------------+--------+---------------+-------------+---------+------+------+----------+-------------+
| 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.00 | NULL        |
|  2 | DERIVED     | sys_user   | NULL       | index  | NULL          | index_phone | 36      | NULL | 1250 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+-------------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

从执行计划中可以看出， id为2的记录就代表子查询的执行方式， 它的select_type是DERIVED， 说明该子查询是以物化的方式执行的。 id为1的记录代表外层查询， 它的table列显示的是， 表示该查询是针对将派生表物化之后的表进行查询的。

MATERIALIZED

当查询优化器在执行包含子查询的语句时， 选择将子查询物化之后与外层查询进行连接查询时， 该子查询对应的select_type属性就是MATERIALIZE

mysql> EXPLAIN SELECT p.* FROM sys_permission p WHERE p.id IN ( SELECT a.menu_id FROM sys_role_permission a );
+----+--------------+-------------+------------+--------+---------------+------------+---------+--------------+------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys | key        | key_len | ref          | rows | filtered | Extra       |
+----+--------------+-------------+------------+--------+---------------+------------+---------+--------------+------+----------+-------------+
|  1 | SIMPLE       | p           | NULL       | ALL    | PRIMARY       | NULL       | NULL    | NULL         |  202 |   100.00 | Using where |
|  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_key>    | <auto_key> | 387     | ei_fire.p.id |    1 |   100.00 | NULL        |
|  2 | MATERIALIZED | a           | NULL       | ALL    | NULL          | NULL       | NULL    | NULL         | 1161 |   100.00 | NULL        |
+----+--------------+-------------+------------+--------+---------------+------------+---------+--------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

执行计划的第三条记录的id值为2， 说明该条记录对应的是一个单表查询， 从它的select_type值为MATERIALIZED可以看出， 查询优化器是要把子查询先转换成物化表。 然后看执行计划的前两条记录的id值都为1， 说明这两条记录对应的表进行连接查询， 需要注意的是第二条记录的table列的值是， 说明该表其实就是id为2对应的子查询执行之后产生的物化表， 然后将s1和该物化表进行连接查询

partitions

匹配的分区

type

条记录就代表着MySQL对某个表的执行查询时的访问方法， 其中的type列就表明了这个访问方法是什么。

const

根据主键或者唯一二级索引列与常数进行等值匹配时， 对单表的访问方法就是const，

ref

当通过普通的二级索引列与常量进行等值匹配时来查询某个表， 那么对该表的访问方法就可能是ref

-- user_name 是联合索引
mysql> EXPLAIN SELECT * FROM sys_user WHERE user_name = '15106196276';
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | sys_user | NULL       | ref  | index_user_status | index_user_status | 51      | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

eq_ref

在连接查询时， 如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话， 所有的索引列都必须进行等值比较） ， 则对该被驱动表的访问方法就是eq_ref

mysql> EXPLAIN SELECT * FROM sys_user_role a INNER JOIN  sys_user b ON a.user_id = b.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL              | 4997 |   100.00 | NULL  |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 386     | ei_fire.a.user_id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

ref_or_null

当对普通二级索引进行等值匹配查询， 该索引列的值也可以是NULL值时， 那么对该表的访问方法就可能是ref_or_null

mysql> EXPLAIN SELECT * FROM sys_user WHERE user_name = '15106196276' OR user_name IS NULL;
+----+-------------+----------+------------+-------------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table    | partitions | type        | possible_keys     | key               | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+----------+------------+-------------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | sys_user | NULL       | ref_or_null | index_user_status | index_user_status | 51      | const |    2 |   100.00 | Using index condition |
+----+-------------+----------+------------+-------------+-------------------+-------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

range

如果使用索引获取某些范围区间的记录， 那么就可能使用到range访问方法

mysql> EXPLAIN SELECT * FROM sys_user WHERE user_name > 'admin';
+----+-------------+----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
| id | select_type | table    | partitions | type  | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | sys_user | NULL       | range | index_user_status | index_user_status | 51      | NULL |   51 |   100.00 | Using index condition |
+----+-------------+----------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

index

当使用索引覆盖， 但需要扫描全部的索引记录时， 该表的访问方法就是index， 即对二级索引进行全索引扫描，直接扫描二级索引的叶子节点

索引覆盖

如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘索引覆盖’。即只需扫描索引而无须回表。

mysql> EXPLAIN SELECT phone FROM sys_user ;
+----+-------------+----------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | sys_user | NULL       | index | NULL          | index_phone | 36      | NULL | 1250 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

index_merge

基于多个二级索引提取数据后进行合并

all

对聚簇索引进行全表扫描

mysql> EXPLAIN SELECT * FROM sys_user ;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | sys_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1250 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

possible_keys/key

possible_keys列表示在某个查询语句中， 对某个表执行单表查询时可能用到的索引有哪些， key列表示**实际用到的索引有哪些 **

mysql> EXPLAIN SELECT * FROM sys_user_role WHERE role_id='1390916533697208321' OR user_id='1390915124889186305';
+----+-------------+---------------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table         | partitions | type        | possible_keys | key         | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+---------------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | sys_user_role | NULL       | index_merge | idx_1,idx_2   | idx_1,idx_2 | 386,386 | NULL |    2 |   100.00 | Using union(idx_1,idx_2); Using where |
+----+-------------+---------------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

possible_keys列中的值并不是越多越好， 可能使用的索引越多， 查询优化器计算查询成本时就得花费更长时间， 所以如果可以的话， 尽量删除那些用不到的索引。

key_len

key选定索引后，最大索引长度
key_len列表示当优化器决定使用某个索引执行查询时， 该索引记录的最大长度， 它是由这三个部分构成的：

  1. 对于使用固定长度类型的索引列来说， 它实际占用的存储空间的最大长度就是该固定值， 对于指定字符集的变长类型的索引列来说， 比如某个索引列的类型是VARCHAR(100)， 使用的字符集是utf8，那么该列实际占用的最大存储空间就是100 × 3 = 300个字节。
  2. 如果该索引列可以存储NULL值， 则key_len比不可以存储NULL值时多1个字节。
  3. 对于变长字段来说， 都会有2个字节的空间来存储该变长列的实际长度

ref

索引列等值匹配类型const、ref、eq_ref、ref_or_null,你跟索引列等值匹配的是什么？是等值匹配一个常量值？还是等值匹配另外一个字段的值？
当使用索引列等值匹配的条件去执行查询时， 也就是在访问方法是const、 eq_ref、 ref、 ref_or_null、 unique_subquery、 index_subquery其中之一时， ref列展示的就是与索引列作等值匹配的东西， 比如只是一个常数或者是某个列。

rows

扫描出的行数(估算的行数)

filtered

按表条件过滤的行百分比，比如rows =1000，filtered=10%，按条件过滤后=100条数据

  1. 如果使用的是全表扫描的方式执行的单表查询， 那么计算驱动表扇出时需要估计出满足搜索条件的记录到底有多少条。
  2. 如果使用的是索引执行的单表扫描， 那么计算驱动表扇出的时候需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条
mysql> EXPLAIN SELECT * FROM sys_user_role WHERE role_id >'1390916533697208321' AND user_id='1390915124889186305';
+----+-------------+---------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table         | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | sys_user_role | NULL       | ref  | idx_1,idx_2   | idx_2 | 386     | const |    1 |    49.99 | Using where |
+----+-------------+---------------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

从执行计划的key列中可以看出来， 该查询使用idx_2(user_id, tenant_id)索引来执行查询， 从rows列可以看出满足user_id='1390915124889186305'的记录有386条。 执行计划的filtered列就代表查询优化器预测在这386条记录中， 有多少条记录满足其余的搜索条件， 也就是 role_id >'1390916533697208321' 这个条件的百分比。 此处filtered列的值是49.99， 说明查询优化器预测在386条记录中有49.99%的记录满足role_id >'1390916533697208321'这个条件。

Extra

执行情况的描述和说明

image.png

No tables used

当查询语句的没有FROM子句时将会提示该额外信息

mysql> EXPLAIN SELECT 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 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 | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

Impossible WHERE

查询语句的WHERE子句永远为FALSE时将会提示该额外信息

mysql> EXPLAIN SELECT * FROM sys_user WHERE 1!=1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| 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, 1 warning (0.00 sec)

No matching min/max row

当查询列表处有MIN或者MAX聚集函数， 但是并没有符合WHERE子句中的搜索条件的记录时， 将会提示该额外信息

mysql> EXPLAIN SELECT Max(id) FROM sys_user WHERE id='1111';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+
| 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 | No matching min/max row |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+
1 row in set, 1 warning (0.00 sec)

Using index

查询列表以及搜索条件中只包含属于某个索引的列， 也就是在可以使用索引覆盖(无需回表)的情况下， 在Extra列将会提示该额外信息

mysql> EXPLAIN SELECT phone FROM sys_user;
+----+-------------+----------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | sys_user | NULL       | index | NULL          | index_phone | 36      | NULL | 1250 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Using index condition

索引条件下推

如果where的部分列可以仅使用索引中的列来过滤，则mysql服务器会将这部分条件下推到存储引擎，存储引擎使用索引条目来计算已推入的索引条件，只有满足这个条件，才从表中读取行。从而减少存储引擎访问表的次数和mysql服务器访问存储引擎的次数

  • 案例

在查询语句的执行过程中用到索引条件下推特性

Using where

表示全表扫描或者在查找使用索引的情况下，但是还有查询条件不在索引字段当中

  1. 查询条件中的相关列，不是索引字段， 全表扫描后，通过Using where过滤获取所需的数据。

因为字段D没有索引，所以必须全表扫描，然后在服务器层使用WHERE过滤数据。

mysql> EXPLAIN SELECT COUNT(*) FROM TEST WHERE D = '2000-01-01';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | TEST  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   25 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
  1. 非唯一性索引扫描，但是由于索引未覆盖所有查询条件，在存储引擎返回记录后，仍然需要过滤数据即索引无法过滤掉无效的行
mysql> EXPLAIN SELECT * FROM TEST WHERE i1 = 3 AND d = '2000-01-01';
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | TEST  | NULL       | ref  | PRIMARY       | PRIMARY | 4       | const |    5 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
  1. WHERE筛选条件不是索引的前导列，导致不走索引，而走全表扫描。
mysql> EXPLAIN SELECT * FROM sys_user WHERE real_name = 'admin';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | sys_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1250 |    10.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

当使用索引访问来执行对某个表的查询， 并且该语句的WHERE子句中有除了该索引包含的列之外还有其他搜索条件时， 在Extra列中也会提示上述额外信息。 比如下边这个查询虽然使用idx_key1索引执行查询， 但是搜索条件中除了包含key1的搜索条件key1 = 'a'， 还有包含common_field的搜索条件， 所以Extra列会显示Using where的提示：

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key 			| key_len | ref 	| rows | filtered | Extra			  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| 1  | SIMPLE 		 | s1		 | NULL 			| ref  | idx_key1 		 | idx_key1 | 303 		| const | 8 	 | 10.00 	  | Using where |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+

Using temporary

表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
在执行许多包含DISTINCT、 GROUP BY、 UNION等子句的查询过程中， 如果不能有效利用索引来完成查询， MySQL很有可能寻求通过建立内部的临时表来执行查询。 如果查询中使用到了内部的临时表， 在执行计划的Extra列将会显示Using temporary提示

mysql> EXPLAIN SELECT DISTINCT real_name  FROM sys_user;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | sys_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1250 |   100.00 | Using temporary |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)

Using filesort

orderby没有索引，只能内存或磁盘上排序
排序操作无法使用到索引， 只能在内存中（记录较少的时候） 或者磁盘中（记录较多的时候） 进行排序， MySQL把这种在内存中或者磁盘上进行排序的方式统称为文件排序

mysql> EXPLAIN SELECT * FROM sys_user ORDER BY real_name;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | sys_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1250 |   100.00 | Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

Using join buffer

该值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果，如果出现了这个值，需要根据查询的具体情况可能需要添加索引来改进能。
在连接查询执行过程中， 当被驱动表不能有效的利用索引加快访问速度， MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度，

mysql> EXPLAIN SELECT * FROM sys_user_role a INNER JOIN sys_user_dept b ON a.user_id = b.user_id;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | b     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   81 |   100.00 | NULL                                               |
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4997 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)