EXPLAIN各个字段是什么意思

最新推荐文章于 2024-03-26 23:52:22 发布

逍遥大俠

最新推荐文章于 2024-03-26 23:52:22 发布

阅读量755

点赞数 2

分类专栏：数据库面试文章标签： mysql 数据库面试

本文链接：https://blog.csdn.net/w139074301/article/details/118605301

版权

数据库同时被 2 个专栏收录

10 篇文章 3 订阅

订阅专栏

面试

3 篇文章 3 订阅

订阅专栏

mysql使用explain xxx可以分析sql语句的性能，本文详细总结一下每个字段的意义

MYSQL 5.6.3以前只能EXPLAIN SELECT; MYSQL5.6.3以后就可以EXPLAIN SELECT,UPDATE,DELETE

概述

首先看一个简单的示例

mysql> explain select * from staff;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | staff | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set

先总结下所有字段的意义

column	含义
`id`	查询序号
`select_type`	查询类型
`table`	表名
`type`	`join`类型
`prossible_keys`	可能会选择的索引
`key`	实际选择的索引
`key_len`	索引的长度
`ref`	与索引作比较的列
`rows`	要检索的行数(估算值)
`Extra`	额外信息

再详细介绍每个字段的意义

1. id

SQL查询中的序列号

id列数字越大越先执行，如果说数字一样大，那么就从上往下依次执行。

例如：

mysql> explain select * from (select * from ( select * from t3 where id=3952602) a) b;
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | <derived3> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  3 | DERIVED     | t3         | const  | PRIMARY,idx_t3_id | PRIMARY | 4       |      |    1 |       |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

sql语句从内到外按照id的顺序3 => 2 => 1依次执行

2. select_type

查询的类型，可以是下表的任何一种类型

select_type	类型说明
`SIMPLE`	简单`SELECT`(不使用`UNION`或子查询)
`PRIMARY`	最外层的`SELECT`
`UNION`	`UNION`中第二个或之后的`SELECT`语句
`DEPENDENT UNION`	`UNION`中第二个或之后的`SELECT`语句取决于外面的查询
`UNION RESULT`	`UNION`的结果
`SUBQUERY`	子查询中的第一个`SELECT`
`DEPENDENT SUBQUERY`	子查询中的第一个`SELECT`, 取决于外面的查询
`DERIVED`	衍生表(`FROM`子句中的子查询)
`MATERIALIZED`	物化子查询
`UNCACHEABLE SUBQUERY`	结果集无法缓存的子查询，必须重新评估外部查询的每一行
`UNCACHEABLE UNION`	`UNION`中第二个或之后的`SELECT`，属于无法缓存的子查询

SIMPLE示例：

mysql> explain select * from t3 where id=3952602;
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t3    | const | PRIMARY,idx_t3_id | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+

PRIMARY示例：

mysql> explain select * from (select * from t3 where id=3952602) a ;
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | t3         | const  | PRIMARY,idx_t3_id | PRIMARY | 4       |      |    1 |       |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

UNION、UNION RESULT示例：

mysql> explain select * from t3 where id=3952602 union all select * from t3 ;
+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type  | table      | type  | possible_keys     | key     | key_len | ref   | rows | Extra |
+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
|  1 | PRIMARY      | t3         | const | PRIMARY,idx_t3_id | PRIMARY | 4       | const |    1 |       |
|  2 | UNION        | t3         | ALL   | NULL              | NULL    | NULL    | NULL  | 1000 |       |
|NULL | UNION RESULT | <union1,2> | ALL   | NULL              | NULL    | NULL    | NULL  | NULL |       |
+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+

3. table

查询的表名。不一定是实际存在的表。

可以为如下的值：

<unionM,N>: 引用id为M和N UNION后的结果。
<derivedN>: 引用id为N的结果派生出的表。派生表可以是一个结果集，例如派生自FROM中子查询的结果。
<subqueryN>: 引用id为N的子查询结果物化得到的表。即生成一个临时表保存子查询的结果。

例如：

mysql> explain select * from (select * from ( select * from t3 where id=3952602) a) b;
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | <derived3> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  3 | DERIVED     | t3         | const  | PRIMARY,idx_t3_id | PRIMARY | 4       |      |    1 |       |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

4. type（重要）

这是最重要的字段之一，显示查询使用了何种类型。

下表从上到下性能依次递减

type	说明
`system`	表中只有一行数据或者是空表，这是`const`类型的一个特例。且只能用于`myisam`和`memory`表。如果是`Innodb`引擎表，`type`列在这个情况通常都是`all`或者`index`
`const`	最多只有一行记录匹配。当联合主键或唯一索引的所有字段跟常量值比较时，`join`类型为`const`。其他数据库也叫做唯一索引扫描
`eq_ref`	多表`join`时，对于来自前面表的每一行，在当前表中只能找到一行。它用在一个索引的所有部分被联接使用并且索引是`UNIQUE`或`PRIMARY KEY`
`ref`	对于来自前面表的每一行，在此表的索引中可以匹配到多行。
`fulltext`	使用全文索引的时候是这个类型。要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，`mysql`不管代价，优先选择使用全文索引
`ref_or_null`	该联接类型如同`ref`，但是添加了`MySQL`可以专门搜索包含`NULL`值的行
`index_merge`	表示查询使用了两个以上的索引，最后取交集或者并集，常见`and` ，`or`的条件使用了不同的索引
`unique_subquery`	用于`where`中的`in`形式子查询，子查询返回不重复值唯一值，可以完全替换子查询，效率更高。该类型替换了下面形式的`IN`子查询的`ref`： `value IN (SELECT primary_key FROM single_table WHERE some_expr)`
`index_subquery`	该联接类型类似于`unique_subquery`。适用于非唯一索引，可以返回重复值。
`range`	索引范围查询，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。比如：`SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20;`
`index`	索引全表扫描，把索引从头到尾扫一遍。这里包含两种情况：一种是查询使用了覆盖索引，那么它只需要扫描索引就可以获得数据，这个效率要比全表扫描要快，因为索引通常比数据表小，而且还能避免二次查询。在`extra`中显示`Using index`，反之，如果在索引上进行全表扫描，没有`Using index`的提示。
`ALL`	全表扫描，性能最差

const示例：

mysql> explain select * from t3 where id=3952602;
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t3    | const | PRIMARY,idx_t3_id | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+

eq_ref示例：

mysql> create unique index  idx_t3_id on t3(id) ;
Query OK, 1000 rows affected (0.03 sec)
Records: 1000  Duplicates: 0  Warnings: 0
 
mysql> explain select * from t3,t4 where t3.id=t4.accountid;
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+
| id | select_type | table | type   | possible_keys     | key       | key_len | ref                  | rows | Extra |
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+
|  1 | SIMPLE      | t4    | ALL    | NULL              | NULL      | NULL    | NULL                 | 1000 |       |
|  1 | SIMPLE      | t3    | eq_ref | PRIMARY,idx_t3_id | idx_t3_id | 4       | dbatest.t4.accountid |    1 |       |
+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-------+

index_merge示例：

mysql> explain select * from t4 where id=3952602 or accountid=31754306 ;
+----+-------------+-------+-------------+----------------------------+----------------------------+---------+------+------+------------------------------------------------------+
| id | select_type | table | type        | possible_keys              | key                        | key_len | ref  | rows | Extra                                                |
+----+-------------+-------+-------------+----------------------------+----------------------------+---------+------+------+------------------------------------------------------+
|  1 | SIMPLE      | t4    | index_merge | idx_t4_id,idx_t4_accountid | idx_t4_id,idx_t4_accountid | 4,4     | NULL |    2 | Using union(idx_t4_id,idx_t4_accountid); Using where |
+----+-------------+-------+-------------+----------------------------+----------------------------+---------+------+------+------------------------------------------------------+

range示例：

mysql> explain SELECT * FROM t3 WHERE id BETWEEN 10 and 1000;
+----+-------------+-------+-------+-------------------+-----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys     | key       | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+-------------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | t3    | range | PRIMARY,idx_t3_id | idx_t3_id | 4       | NULL |    2 | Using where |
+----+-------------+-------+-------+-------------------+-----------+---------+------+------+-------------+

5. possible_keys

查询可能使用到的索引都会在这里列出来

6. key

查询真正使用到的索引。

select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

7. key_len

查询用到的索引长度（字节数）

如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，用多少算多少。

在不损失精确性的情况下，长度越短越好

key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中

8. ref

和前方表连接的字段

如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

9. rows（重要）

这是mysql估算的需要扫描的行数（不是精确值）。

这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.

10. Extra（重要）

EXplain 中的很多额外的信息会在 Extra 字段显示, 常见的有以下几种内容:

Extra	说明
`Distinct`	一旦找到了与行相联合匹配的行，就不再搜索了。在`select`部分使用了`distinc`关键字会出现
`Using filesort`	表示需额外的排序操作, 不能通过索引顺序达到排序效果。看到这个的时候，查询就需要优化了。
`Using index`	“覆盖索引扫描”, 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错
`Using temporary`	查询有使用临时表, 一般出现于排序, 分组和多表 `join` 的情况, 查询效率不高, 建议优化.

以上就是explain各个字段的意义，这里一般做个进阶了解

在实际开发中，为了提升开发效率，我们可以选择搭配一些可视化SQL优化工具使用，可以参考我的这篇文章《小白也会用的SQL优化工具推荐》

逍遥大俠

关注

2
点赞
踩
4

收藏

觉得还不错? 一键收藏
2
评论
EXPLAIN各个字段是什么意思

mysql使用explain xxx可以分析sql语句的性能，本文详细总结一下每个字段的意义MYSQL 5.6.3以前只能EXPLAIN SELECT; MYSQL5.6.3以后就可以EXPLAIN SELECT,UPDATE,DELETE概述首先看一个简单的示例mysql> explain select * from staff;+----+-------------+-------+------+---------------+------+---------+------+------
复制链接

扫一扫

专栏目录