MySQL中explain的结果字段介绍（1）

AsiaYe

发布于 2019-11-06 17:45:40

2.8K0

发布于 2019-11-06 17:45:40

文章被收录于专栏：DBA随笔DBA随笔

MySQL中explain的结果字段介绍

我们在使用MySQL的时候，用的最多的情况可能就是select语句了，当我们在一个表查找数据的时候，经常会遇到查找的速度比较慢的情况，作为一名DBA，我也会经常遇见业务方写的SQL性能不太好的情况，这种情况下，通常会让业务方去进行调优。而判断一条SQL语句是否会变慢的最主要依据还是"执行计划"。

MySQL中，可以通过explain的语句来查看一个SQL的执行计划。explain的语法大家可能都清楚，我们看看explain的基本语法和输出内容：

mysql 19:49:29>>explain select 1;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

我们来说说这个explain语句中这些字段的意思：

首先可以看到它包含了:id、select_type、table、type、possible_keys、key，key_len、ref、rows以及extra几个字段，我们来说说这写字段的含义吧。

id值

在一个大的select语句中，每一个语句都对应一个id值，例如上面的例子中，这个select 1就对应了一个id值，再来看下面这个SQL:

mysql:(none) 21:49:37>>explain select 1 union select 1;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 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 tables used  |
|  2 | UNION        | NULL       | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used  |
|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这个连接的关键字，这个select语句就变成了包含2个select的SQL，可以看到，还出现了第3行内容，这一行内容其实是union语法中间的临时表，临时表的id值为null。如果上面的语句中使用了union all的语法，那么就不会出现第三3行的结果，因为union all的过程中，不会对结果集进行去重，所以也就不会使用那个临时表。

关于id，其实就是一个SQL对应一个id，如果有子查询，那么子查询也将对应一个id值，但是这个id值稍有不同，看下面的SQL：

mysql:yeyztest 23:03:52>>explain select * from test2 where id in (select id from test3);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------------+
| 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 | NULL                                                                  |
|  1 | SIMPLE      | test3 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where; FirstMatch(test2); Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

我们可以注意到，有子查询的SQL的执行计划结果中id值是一样的，这说明了一个问题：

查询优化器将子查询转换成了连接查询。

关于执行计划中的id列的几点总结：

如果使用union值，则会出现多个id值，并且有一个是临时表结果；

如果使用union all，则不会出现临时表结果；

如果使用子查询，将会转化为连接查询，将会出现2个一样的id值

Select_type值

select关键字对应的是查询的类型，如果查询的类型是一般的select，那么select_type字段的值是simple，在上面的几个例子中，我们的结果中已经出现了primary、union、union result、simple这4个类型的值，其实select_type的值往往不止4中，它可能出现的值有以下常见情况：

simple：一般的select，不出现union或者子查询的。

primary：主查询，一般是在最外面的查询。

union：一般联合查询最右面的SQL语句

union result：保存union的结果的临时表SQL

subquery：子查询语句如果不能转化成连接查询，并且最里面的子查询如果被物化（物化就是通过将子查询的结果作为一个临时表来加快查询结果，正常来讲，这个表是保存在内存中的），则这个select_type就是subquery，这个我们给出例子：

mysql> EXPLAIN SELECT * FROM test2 WHERE id IN (SELECT id FROM test3) OR birth = 'a';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | test1 | NULL       | ALL   | NULL          | NULL     | NULL    | NULL | 9688 |   100.00 | Using where |
|  2 | SUBQUERY    | test2 | NULL       | ALL   | NULL          | NULL     | NULL    | NULL | 9954 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

dependent subquery：子查询最里面的查询如果没有被物化，就是这种类型的。看下面的例子：

mysql:yeyztest 23:17:29>>desc test2;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| birth | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql:yeyztest 23:18:06>>explain select * from test2 where id in (select id from test3) or birth=40;
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type        | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | test2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | test3 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

dependent union：在包含union或者union all的查询语句中，如果小查询都依赖于外层查询的话，那么这个查询的类型就是depentdent union

mysql:yeyztest 23:45:25>>explain select * from test2 where id in  (select id from test3 union select id from test4) or id =5;
+----+--------------------+------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type        | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+--------------------+------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | PRIMARY            | test2      | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |    1 |   100.00 | Using where              |
|  2 | DEPENDENT SUBQUERY | test3      | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |    2 |    50.00 | Using where              |
|  3 | DEPENDENT UNION    | test4      | NULL       | index | NULL          | PRIMARY | 4       | NULL |    1 |   100.00 | Using where; Using index |
| NULL | UNION RESULT       | <union2,3> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL | NULL |     NULL | Using temporary          |
+----+--------------------+------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
4 rows in set, 2 warnings (0.00 sec)

derived：采用物化的方式执行的包含派生表的查询，那么这个派生表的子查询类型就是derived，如下：

mysql:yeyztest 23:43:52>>explain select * from  (select id,count(*) from test3 group by id) as tmp where id =5;
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                           |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ref  | <auto_key0>   | <auto_key0> | 9       | const |    1 |   100.00 | NULL                            |
|  2 | DERIVED     | test3      | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  |    2 |   100.00 | Using temporary; Using filesort |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------+
2 rows in set, 1 warning (0.00 sec)

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

table

表的名称，这个比较容易理解。

partitions

分区信息，一般为null，这个可以暂时忽略。

时间关系，先这样吧，后面的几个字段以及示例明天再写吧。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2019-08-26，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server

sql

数据库

本文分享自 DBA随笔微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

云数据库 SQL Server

sql

数据库

登录后参与评论

0 条评论

热度