MySQL中explain的结果字段介绍
昨天说完了执行计划的前四个字段，今天说说后面几个字段吧。我们看看explain的基本语法和输出内容：
mysql ::>>explain select ;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
row in set (. sec)
01
type值
type值主要保存了查询语句中使用的查询方法，它的取值有以下情况：
system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL。
这些情况，按照上述次序，从好变坏，也就是说，如果使用了system的方法，那么这个SQL的执行速度就会很快，如果使用了all的方法，那么这个SQL的执行速度相对来说就会很慢，因为走了全表扫描，当然，如果你的表里面只有一条记录，那么这些方法其实都是差不多的，查询速度的快慢和表中的数据量多少有着直接的关系。
首先我们定义一个表，这样我们分析这些关系的时候，就能更加清楚了。
mysql:yeyztest 17:30:49>>show create table test_explain\G
*************************** 1. row ***************************
Table: test_explain
Create Table: CREATE TABLE `test_explain` (
`id` int() NOT NULL AUTO_INCREMENT,
`a_key_var` varchar() DEFAULT NULL,
`b_key_int` int() DEFAULT NULL,
`c_key_var` varchar() DEFAULT NULL,
`d_key_var1` varchar() DEFAULT NULL,
`d_key_var2` varchar() DEFAULT NULL,
`d_key_var3` varchar() DEFAULT NULL,
`e_normal_var` varchar() DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_key_b` (`b_key_int`),
KEY `idx_key_a` (`a_key_var`),
KEY `idx_key_c` (`c_key_var`),
KEY `idx_key_d` (`d_key_var1`,`d_key_var2`,`d_key_var3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
row in set (0.00 sec)
对于这个表，简单做一下说明，这个表中包含8个列，其中：
id是主键列、
a_key_var、c_key_var是普通索引列，
b_key_int是唯一索引列，
d_key_var1、d_key_var2、d_key_var3是联合索引的三个列，
e_normal_var是普通列。
我们知道，除了聚集索引外的索引我们都称之为二级索引，下面我们介绍一下type字段出现的情况的含义：
system：
这个一般指的是当表中只有一条记录并且该表使用MyIsam这种精确计数的存储引擎的时候，那么对于这个表的访问方法就是system：
mysql:yeyztest 17:31:00>>insert into test_explain values (,'aa',,'cc','d1','d2','d3','e');
Query OK, 1 row affected (0.00 sec)
mysql:yeyztest 17:40:05>>alter table test_explain engine=myisam;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql:yeyztest 17:40:42>>explain select *from test_explain;
+----+-------------+--------------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+--------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | test_explain | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+--------------+------------+--------+---------------+------+---------+------+------+----------+-------+
1 row in set, warning (0.00 sec)
可以看到，我们插入一条数据，然后将表的存储引擎改为myisam的，然后使用explain语句查看，那么这个时候type的值就是system的，如果这个时候再次插入一条记录：
mysql:yeyztest 17:41:13>>insert into test_explain values (,'aa',,'cc','d1','d2','d3','e');
Query OK, 1 row affected (0.00 sec)
mysql:yeyztest 17:41:52>>explain select *from test_explain;
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | test_explain | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, warning (0.00 sec)
可以看到，当表里面有两条记录的时候，就变成了all的方法。此时表test_explain的存储引擎是myisam，有2条记录，我们修改存储引擎为innodb，然后删除1条记录，再来看结果：
mysql:yeyztest 17:41:55>>alter table test_explain engine=innodb;
Query OK, 2 rows affected (0.42 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql:yeyztest 17:42:06>>select *from test_explain;
+----+-----------+-----------+-----------+------------+------------+------------+--------------+
| id | a_key_var | b_key_int | c_key_var | d_key_var1 | d_key_var2 | d_key_var3 | e_normal_var |
+----+-----------+-----------+-----------+------------+------------+------------+--------------+
| 1 | aa | 2 | cc | d1 | d2 | d3 | e |
| 2 | aa | 3 | cc | d1 | d2 | d3 | e |
+----+-----------+-----------+-----------+------------+------------+------------+--------------+
2 rows in set (0.00 sec)
mysql:yeyztest ::>>delete from test_explain where id=;
Query OK, 1 row affected (0.00 sec)
mysql:yeyztest 17:42:34>>explain select *from test_explain;
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | test_explain | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, warning (0.00 sec)
存储引擎改变了，这个时候type的值也就变成了all了。
Const
const值就是当我们根据主键或者唯一二级索引列与常数进行等值匹配的时候，对表的访问方法就是const，为了演示效果，我们给表中多插入了几条数据，如下：
mysql:yeyztest 17:51:37>>select * from test_explain;
+----+-----------+-----------+-----------+------------+------------+------------+--------------+
| id | a_key_var | b_key_int | c_key_var | d_key_var1 | d_key_var2 | d_key_var3 | e_normal_var |
+----+-----------+-----------+-----------+------------+------------+------------+--------------+
| 1 | aa | 2 | cc | d1 | d2 | d3 | e |
| 2 | aa | 3 | cc | d1 | d2 | d3 | e |
| 3 | aa | 4 | cc | d1 | d2 | d3 | e |
| 4 | bb | 5 | aa | dd1 | dd2 | dd3 | e |
| 5 | bb | 6 | aa | dd1 | dd2 | dd3 | e |
| 6 | bb | 7 | cc | ddd1 | ddd2 | ddd3 | e |
| 7 | bb | 8 | cc | ddd1 | ddd2 | ddd3 | e |
| 8 | cc | 9 | bb | dddd1 | dddd2 | dddd3 | e |
| 9 | cc | 10 | bb | dddd1 | dddd2 | dddd3 | e |
+----+-----------+-----------+-----------+------------+------------+------------+--------------+
9 rows in set (0.00 sec)
mysql:yeyztest ::>>explain select * from test_explain where id=;
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test_explain | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, warning (0.00 sec)
mysql:yeyztest ::>>explain select * from test_explain where b_key_int=;
+----+-------------+--------------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test_explain | NULL | const | idx_key_b | idx_key_b | 5 | const | 1 | 100.00 | NULL |
+----+-------------+--------------+------------+-------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, warning (0.00 sec)
可以看到，当我们根据主键id值和唯一二级索引b_key_int等值匹配来查询的时候，explain中的type字段就是const。
eq_ref
在连接查询的时候，如果子表是按照主键等值匹配来进行查询的，则对被驱动表的访问方法就是eq_ref，来看例子：
mysql:yeyztest 17:52:22>>select *from test2;
+------+---------------------+
| id | birth |
+------+---------------------+
| 1 | 1993-05-04 11:12:13 |
+------+---------------------+
1 row in set (0.00 sec)
mysql:yeyztest ::>>explain select * from test2 inner join test_explain on test2.id=test_explain.id;
+----+-------------+--------------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE | test2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
| 1 | SIMPLE | test_explain | NULL | eq_ref | PRIMARY | PRIMARY | 4 | yeyztest.test2.id | 1 | 100.00 | NULL |
+----+-------------+--------------+------------+--------+---------------+---------+---------+-------------------+------+----------+-------------+
2 rows in set, warning (0.00 sec)
mysql--dba_admin@127.0.0.1:yeyztest 17:58:23>>explain select * from test2 inner join test_explain on test2.id=test_explain.b_key_int;
+----+-------------+--------------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------+
| | SIMPLE | test2 | NULL | ALL | PRIMARY | NULL | NULL | NULL | | 100.00 | NULL |
| | SIMPLE | test_explain | NULL | ref | idx_key_b | idx_key_b | | yeyztest.test2.id | | 100.00 | NULL |
+----+-------------+--------------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------+
rows in set, warning (0.00 sec)
可以看到，我们创建了一个test2的表。里面有一条数据，然后使用连接的方法进行查询，如果我们使用test_explain
的主键值和test2的id字段去进行等值匹配，那么type字段的值就是eq_ref，使用唯一索引去进行等值匹配，type字段的值就是ref
ref
当我们通过普通二级索引和常量进行等值比较的时候，那么对于这个表的访问方法就是ref，看看例子：
mysql:yeyztest 18:13:41>>explain select * from test_explain where test_explain.a_key_var='aa';
+----+-------------+--------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test_explain | NULL | ref | idx_key_a | idx_key_a | 303 | const | 3 | 100.00 | NULL |
+----+-------------+--------------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, warning (0.00 sec)
我们可以看到，type字段的值是ref
ref_or_null
我们知道，普通的索引的值可能是null值，如果我们对于一个普通索引列进行等值判断或者null值判断的时候，那么这个SQL语句的值就是ref_or_null
mysql:yeyztest 18:13:46>>explain select * from test_explain where a_key_var='aa' or a_key_var is null;
+----+-------------+--------------+------------+-------------+---------------+-----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------------+---------------+-----------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | test_explain | NULL | ref_or_null | idx_key_a | idx_key_a | 303 | const | 4 | 100.00 | Using index condition |
+----+-------------+--------------+------------+-------------+---------------+-----------+---------+-------+------+----------+-----------------------+
1 row in set, warning (0.00 sec)
index_merge
顾名思义，这个意思是索引合并，也就是说当我们在一个SQL中使用了字段的时候，这两个字段又分别使用了索引，那么这个时候就会取这两个字段的交集作为结果展示给客户端。
mysql:yeyztest 18:30:59>>explain select * from test_explain where a_key_var='aa' and c_key_var='bb';
+----+-------------+--------------+------------+-------------+---------------------+---------------------+---------+------+------+----------+---------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------------+---------------------+---------------------+---------+------+------+----------+---------------------------------------------------+
| 1 | SIMPLE | test_explain | NULL | index_merge | idx_key_a,idx_key_c | idx_key_c,idx_key_a | 303,303 | NULL | 1 | 100.00 | Using intersect(idx_key_c,idx_key_a); Using where |
+----+-------------+--------------+------------+-------------+---------------------+---------------------+---------+------+------+----------+---------------------------------------------------+
1 row in set, warning (0.00 sec)
我们可以看到，我们使用了a_key_var字段和c_key_var字段来取交集，最终explain中的type字段的值变成了index_merge，也就是索引合并。
unique_subquery
这个类型是指在一些包含in的子查询的查询语句中，如果子查询可以使用到主键进行等值匹配的话，那么该子查询的执行计划中的type列的值就是unique_subquery。
mysql:yeyztest 18:55:38>>explain select * from test_explain t1 where t1.id in (select t2.id from test_explain t2 where t1.id=t2.a_key_var) or t1.a_key_var='b';
+----+--------------------+-------+------------+-----------------+-------------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+-----------------+-------------------+---------+---------+------+------+----------+-------------+
| 1 | PRIMARY | t1 | NULL | ALL | idx_key_a | NULL | NULL | NULL | 9 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | NULL | unique_subquery | PRIMARY,idx_key_a | PRIMARY | 4 | func | 1 | 11.11 | Using where |
+----+--------------------+-------+------------+-----------------+-------------------+---------+---------+------+------+----------+-------------+
2 rows in set, warnings (0.00 sec)
这个例子中，子查询中使用了test_explain表中的普通索引字段a_key_var和test_explain表中的主键id字段进行等值匹配，外层的where条件中我们使用的是test_explain的主键id值进行in的匹配，所以子查询中的查询方法就是unique_subquery。
index_subquery
这个和上面一样，但是in条件中的子查询使用的是a_key_var普通索引字段，而不是id字段：
mysql:yeyztest 18:53:30>>explain select * from test_explain t1 where e_normal_var in (select a_key_var from test_explain t2 where t1.id=t2.a_key_var) or a_key_var='b';
+----+--------------------+-------+------------+----------------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+----------------+---------------+-----------+---------+------+------+----------+--------------------------+
| 1 | PRIMARY | t1 | NULL | ALL | idx_key_a | NULL | NULL | NULL | 9 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | NULL | index_subquery | idx_key_a | idx_key_a | 303 | func | 3 | 100.00 | Using where; Using index |
+----+--------------------+-------+------------+----------------+---------------+-----------+---------+------+------+----------+--------------------------+
2 rows in set, warnings (0.00 sec)
range
如果使用索引来获取某些范围字段，那么就会用到range的方法，例如下面这个：
mysql:yeyztest 18:59:34>>explain select * from test_explain where id in (,,);
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test_explain | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 3 | 100.00 | Using where |
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, warning (0.00 sec)
mysql:yeyztest ::>>explain select * from test_explain where id > and id <;
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test_explain | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 5 | 100.00 | Using where |
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, warning (0.00 sec)
index
我们知道，二级索引的B+树中包含的索引字段和聚集索引字段，如果一个二级索引是包含多个字段的联合索引，当我们使用联合索引的某些列来匹配查询其他联合索引列的时候，或者匹配聚集索引类的时候，这种情况下将会用到"索引覆盖"功能，在发生索引覆盖的时候，如果这个SQL会扫描二级索引上面的全部记录，那么这SQL的执行计划type字段的值就是index，说简单点，如果一个SQL只扫描二级索引的全部记录，那么他的执行计划type字段就是index，如下：
mysql:yeyztest 19:08:18>>explain select id,d_key_var2 from test_explain where d_key_var3='b';
+----+-------------+--------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test_explain | NULL | index | NULL | idx_key_d | 909 | NULL | 9 | 11.11 | Using where; Using index |
+----+-------------+--------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, warning (0.00 sec)
上述SQL就是使用联合索引中的d_key_var3来查询聚集索引列id和联合索引的另外一个列
ALL
这个很好理解，就是走了全表扫描。例如select * from table_name这种SQL
02
possible_keys和key值
其中，possible_keys的值是可能会用到的索引，而key的值是实际用到的查询索引。我们来看下面这个SQL：
mysql:yeyztest 19:24:23>>create table test1 (
-> id int not null auto_increment,
-> a_key_var varchar(),
-> b_key_int int,
-> c_key_var varchar(),
-> primary key (id),
-> key idx_key_a (a_key_var),
-> unique key idx_key_b (b_key_int),
-> key idx_key_c (c_key_var),
-> key idx_key_d (a_key_var, b_key_int, c_key_var)
-> ) engine=innodb charset=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql:yeyztest 19:24:28>>explain select *from test1 where a_key_var='aa';
+----+-------------+-------+------------+------+---------------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+-----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test1 | NULL | ref | idx_key_a,idx_key_d | idx_key_a | 303 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------------+-----------+---------+-------+------+----------+-------+
1 row in set, warning (0.00 sec)
表结构中，a_key_var是普通索引idx_key_a的索引列，又是idx_key_d这个联合索引的索引列，所以当我们使用a_key_var作为where语句中的过滤条件时，possible_keys中的值就是这两个索引，而key的值是实际使用到的索引。
这篇内容有点多，其他的字段后续在写吧。
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2019-08-28，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL（TencentDB for MySQL）为用户提供安全可靠，性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性，包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL，可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理，不仅经济实惠，而且稳定可靠，易于运维。