MySQL命令 Explain参数说明

最新推荐文章于 2023-06-25 08:11:38 发布

校长我错了

最新推荐文章于 2023-06-25 08:11:38 发布

阅读量204

点赞数

分类专栏： mysql 文章标签： mysql explain

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

MySQL EXPLAIN命令是查询性能优化不可缺少的一部分，该文主要讲解explain命令的使用及相关参数说明。

参考：
MySQL 性能优化神器 Explain 使用分析
 MySQL 索引原理及慢查询优化
 MySQL系列—EXPLAIN 介绍
 SQL 语句 explain 分析
 EXPLAIN Output Format

讲解过程中使用的数据表的结构：

# 我们所示例的数据表和SQL语句均是工作在InnoDB数据库引擎下
# myuser数据表一共有4个字段，3个索引。
# user_name字段上创建了非唯一键非聚簇索引
# user_number字段上创建了唯一键非聚簇索引
# id字段上是聚簇索引
CREATE TABLE `myuser` (
  `Id` INT (11) NOT NULL AUTO_INCREMENT,
  `user_name` VARCHAR (255) NOT NULL DEFAULT '',
  `usersex` INT (9) NOT NULL DEFAULT '0',
  `user_number` INT (11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`Id`),
  UNIQUE KEY `number_index` (`user_number`),
  KEY `name_index` (`user_name`)
)

EXPLAIN Output Columns

列名	说明
id	每个被独立执行的操作的标识，表示对象被操作的顺序；ID值大，先被执行；如果相同，执行顺序一般从上到下
select_type	数据库引擎将SQL拆分成若干部分的子查询/子操作，每个查询select子句中的查询类型
table	本次子查询所查询的目标数据表。SQL查询语句即使再复杂，一次子查询也只可能最多关联一张数据表；
type	子查询类型，非常重要的性能衡量点。这个字段项可能显示的值包括：“ALL->index->range->ref->eq_ref->const
possible_keys	本次子查询可能使用的索引（前提是，您要建立了索引）。如果查询所使用的检索条件可能涉及到多个索引，这里将会列出这些所有的可能性
key	本次子查询最终被选定的执行索引。有的时候possible_keys可能有值，但keys可能没有，这就代表InnoDB引擎最终并没有使用任何索引作为检所依据
key_len	被选定的索引键的长度
ref	表示本次子查询参照的参照条件/参照数据表，这个字段的值还可能是一个常量；
rows	为了找到所需的行而需要读取的行数，估算值，不精确。通过把所有rows列值相乘，可粗略估算整个查询会检查的行数
Extra	包含不适合在其他列中显示但十分重要的额外信息

1. id

id列数字越大越先执行；
如果说数字一样大，那么就从上往下依次执行
id列为null的就表示这是一个结果集(其他行的联合结果)，不需要使用它来进行查询

2. select_type

表示查询的类型

类型	说明
simple	表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple，且只有一个
primary	一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。且只有一个
derived	from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select
union	union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是union
union result	包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null
dependent union	与union一样，出现在union 或union all语句中，从第二个或者在union 之后的select 作为 union 查询，依赖于外部查询
subquery	除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery
dependent subquery	与dependent union类似，表示这个subquery的查询要受到外部表查询的影响

3. table

对应行正在访问哪一个表，表名或者别名

显示的查询表名，如果查询使用了别名，那么这里显示的是别名；
如果不涉及对数据表的操作，那么这显示为null，
如果显示为尖括号括起来的<derived N>就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。
如果是尖括号括起来的<union M,N>，与<derived N>类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。

注意：MySQL对待这些表和普通表一样，但是这些“临时表”是没有任何索引的。

4. type

type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ，一般来说，得保证查询至少达到range级别，最好能达到ref。
除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引

system：
表中只有一行数据或者是空表，且只能用于myisam和memory表。
如果是Innodb引擎表，type列在这个情况通常都是all或者index

const：
使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。
其他数据库也叫做唯一索引扫描


# 直接使用主键值就可以在索引中进行定位，无论数据量多大，这个定位的性能都不会改变

explain select * from myuser where id = 1

eq_ref：

出现在要连接过个表的查询计划中，驱动表只返回一行数据，
且这行数据是第二个表的主键或者唯一索引，且必须为not null；
唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref
ref：
不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求、只要使用相等条件检索时就可能出现；
常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现；
返回数据不唯一的等值查找就可能出现。
一种索引访问，它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引或唯一性索引非唯一性前缀时才会发生。这个类型跟eq_ref不同的是，它用在关联操作只使用了索引的最左前缀，或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或<=>操作符的带索引的列。
```
# 在myuser中已基于user_name字段建立了非聚簇索引，且并非唯一键索引

explain select count(*) from myuser where user_name = '用户1'
```
fulltext：
全文索引检索，要注意，全文索引的优先级很高；
若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引
ref_or_null：
与ref方法类似，只是增加了null值的比较。实际用的不多。
unique_subquery：
用于where中的in形式子查询，子查询返回不重复值唯一值
index_subquery：
用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值；
可以使用索引将子查询去重。

range：
索引范围扫描；
常见于使用>,<,is null,between ,in ,like等运算符的查询中。


# 以下查询语句在聚簇索引上检索一个范围

explain select * from myuser where id >= 10

index_merge：
表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引；
官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range
index：
索引全表扫描，把索引从头到尾扫一遍；
常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
主要优点就是避免了排序, 但是开销仍然非常大。
如在Extra列看到Using index，说明正在使用覆盖索引，只扫描索引的数据，它比按索引次序全表扫描的开销要小很多
```
# 以下语句还是要进行全表扫描，但是它并不需要读取任何数据信息。

explain select count(*) from myuser
```
all：
这个就是全表扫描数据文件；
然后再在server层进行过滤返回符合要求的记录。

5. possible_keys

显示查询使用了哪些索引，表示该索引可以进行高效地查找，但是列出来的索引对于后续优化过程可能是没有用的

6. key

key列显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

7. key_len

用于处理查询的索引长度；越短越好、速度越快；
如果是单列索引，那就整个索引长度算进去；
如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去；
留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。
要注意，mysql的ICP特性使用到的索引不会计入其中。
另外，key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。

表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到. key_len 的计算规则如下:

字符串
- char(n): n 字节长度
- varchar(n): 如果是 utf8 编码, 则是 3 n + 2字节; 如果是 utf8mb4 编码, 则是 4 n + 2 字节.
数值类型:
- TINYINT: 1字节
- SMALLINT: 2字节
- MEDIUMINT: 3字节
- INT: 4字节
- BIGINT: 8字节
时间类型
- DATE: 3字节
- TIMESTAMP: 4字节
- DATETIME: 8字节
字段属性: NULL 属性占用一个字节. 如果一个字段是 NOT NULL 的, 则没有此属性.

8. ref

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

9. rows

rows列显示MySQL认为它执行查询时必须检查的行数。注意这是一个预估值。

10. filtered

　使用explain extended时会出现这个列；
　5.7之后的版本默认就有这个字段，不需要使用explain extended了。
　这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数

11. Extra

Extra是EXPLAIN输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息，MySQL查询优化器执行查询的过程中对查询计划的重要补充信息。

执行计划分析结果中的Extra字段，包含了结果中其他字段中没有说明但是对性能分析有非常有帮助的信息。甚至有的时候可以但从这个字段分析出某个子查询是否需要调整、涉及到的索引是否需要调整或者MySQL服务的环境参数配置是否需要进行调整。Extra字段还可以看成是对特定子查询的总结。

A：distinct：

在select部分使用了distinc关键字
优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

B：no tables used：

不带from字句的查询或者From dual查询

C：using filesort：

排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中
Mysql服务无法直接使用索引完成排序时，就需要动用一个内存空间甚至需要磁盘交换动作辅助才能完成排序操作。
这句话有两层含义，如果排序所依据的字段（一个或者多个）并没有创建索引，那么肯定无法基于索引完成排序；即使排序过程能够依据正确的索引完成，但是由于涉及到的查询结果太多，导致用于排序的内存空间不足，所以MySQL服务在进行排序时还会有磁盘交换动作。负责配置某一个客户（session）可用的内存空间参数项名字为“sort_buffer_size”。默认的大小为256KB，如果读者对查询结果集有特别要求，可以将该值改为1MB。一旦在Extra字段中出现了“Using filesort”提示，那么说明这条子查询也需要进行优化；
```
explain select * from myuser order by usersex
+--------+-----------------------+
| ...... |          Extra        |
+--------+-----------------------+
| ...... |   Using filesort      |
+--------+-----------------------+

# 由于usersex并没有创建索引，所以使用filesort策略进行排序。 
```

注意，在子查询中为Group By和Order by操作创建索引时，有时需要联合where关键字使用的查询字段一起创建复合索引才能起作用。这是因为子查询为了检索，所首先选择一个可用的索引项，随后进行排序时，却发现无法按照之前的索引进行排序，所以只有走filesort了。例如以下示例：

```mysql
# user_name字段和user_number字段都独立创建了索引
explain select * from myuser where user_name = '用户1' group by user_number
+--------+------------+----------------------------------------------------------+
| ...... |    key     |                             Extra                        |
+--------+------------+----------------------------------------------------------+
| ...... | name_index |  Using index condition; Using where; Using filesort      |
+--------+------------+----------------------------------------------------------+
# 为了首先完成条件检索，InnoDB引擎选择了user_name字段的索引
# 但是排序时发现无法按照之前的索引字段完成，所以选择走filesort12345678910
```

MySQL有两种方式可以生成有序的结果，通过排序操作或者使用索引，当Extra中出现了Using filesort 说明MySQL使用了后者，但注意虽然叫filesort但并不是说明就是用了文件来进行排序，只要可能排序都是在内存里完成的。大部分情况下利用索引排序更快，所以一般这时也要考虑优化查询了。使用文件完成排序操作，这是可能是ordery by，group by语句的结果，这可能是一个CPU密集型的过程，可以通过选择合适的索引来改进性能，用索引来为查询结果排序。

D：Not exists

MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。

E：using index：

查询时不需要回表查询，直接通过索引就可以获取查询的数据。

使用了索引（无论是聚簇索引还是非聚簇索引）并且整个子查询都只是访问了索引信息，而没有访问真实的数据信息，那么在Extra字段就会出现这个描述。请看如下示例：

explain select user_name from myuser where user_name = '用户1';
+--------+-------------------------------------+
| ...... |                Extra                |
+--------+-------------------------------------+
| ...... |     Using where; Using index        |
+--------+-------------------------------------+

# 使用user_name字段进行查询，原本需要再从聚簇索引中查找数据信息


# 但是InnoDB引擎发现只需要输出一个字段，且这个字段就是user_name本身，甚至不需要去找全部数据了。

说明查询是覆盖了索引的，不需要读取数据文件，从索引树（索引文件）中即可获得信息。如果同时出现using where，表明索引被用来执行索引键值的查找，没有using where，表明索引用来读取数据而非执行查找动作。这是MySQL服务层完成的，但无需再回表查询记录。

F：using join buffer（block nested loop），using join buffer（batched key accss）：

5.6.x之后的版本优化关联查询的BNL，BKA特性。主要是减少内表的循环数量以及比较顺序地扫描查询。

使用InnoDB引擎预留的 join buffer 区域（一个专门用来做表连接的内存区域），这是一个正常现象主要涉及到两个子查询通过join关键字进行连接的操作。每一个客户端连接（session）独立使用的 join buffer 区域大小可以通过join_buffer_size参数进行设置。这个参数在 MySQL 5.6 Version 中的默认值为128KB。如果开发人员经常需要用到join操作，可以适当增加区域大小到1MB或者2MB；


# 以下语句是一个左外连接的操作


# 并且t_interfacemethod.uid和t_interfacemethod_param.interfacemethod之间有外键和索引存在

explain select * from  t_interfacemethod_param 
left join  t_interfacemethod on t_interfacemethod.uid = t_interfacemethod_param.interfacemethod
+--------+----------------------------------------------------------+
| ...... |                          Extra                           |
+--------+----------------------------------------------------------+
| ...... |                                                          |
+--------+----------------------------------------------------------+
| ...... |  Using where; Using join buffer (Block Nested Loop)      |
+--------+----------------------------------------------------------+

使用了连接缓存：Block Nested Loop，连接算法是块嵌套循环连接;Batched Key Access，连接算法是批量索引连接

G：using sort_union，using_union，using intersect，using sort_intersection：

using intersect：表示使用and的各个索引的条件时，该信息表示是从处理结果获取交集
　　　　using union：表示使用or连接各个使用索引的条件时，该信息表示从处理结果获取并集
　　　　using sort_union和using sort_intersection：与前面两个对应的类似，只是他们是出现在用and和or查询信息量大时，先查询主键，然后进行排序合并后，才能读取记录并返回。

H：using temporary：

表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table，used_tmp_disk_table才能看出来。
Mysql中的数据引擎需要建立临时表进行中间结果的记录，才能完成查询操作。
这个常见于查询语句中存在 GROUP BY 或者 ORDER BY 操作的情况。但并不是说主要子查询中出现了GROUP BY 或者 ORDER BY就会建立临时表，而如果 Group By 或者 Order By 所依据的字段（或多个字段）没有建立索引，则一定会出现“Using temporary”这样的提示。另一种常见情况发生在子查询join连接时，连接所依据的一个字段（或多个字段）没有建立物理外键和索引。一旦在Extra字段中出现了“Using temporary”提示，一般来说这条子查询就需要重点优化；
用临时表保存中间结果，常用于GROUP BY 和 ORDER BY操作中，一般看到它说明查询需要优化了，就算避免不了临时表的使用也要尽量避免硬盘临时表的使用。

I：using where：

表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。
查询条件中分为限制条件和检查条件，
5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤再返回真正符合查询的数据。
5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量。 extra列显示using index condition

此where关键字并不是SQL查询语句中的where关键字（此where非彼where），而是指该子查询是否需要依据一定的条件对满足条件的索引（全表扫描也是扫描的聚簇索引）进行过滤。示例如下：


# user_number 是一个非聚簇唯一键索引，所以where条件后的user_number只会定位到唯一一条记录


# 不需要再根据这个条件查询是否还有其它满足条件的索引项了

explain select * from myuser where user_number = 77777
+--------+-------------+
| ...... |    Extra    |
+--------+-------------+
| ...... |             |
+--------+-------------+


# user_name 是一个非聚簇非唯一键索引，索引where条件后的user_name可能定位到多条记录


# 这时数据库引擎就会对这些索引进行检索，以便定位满足查询条件的若干索引项


#（由于B+树的结构，所以这些索引项是连续的）

explain select * from from myuser where user_name = '用户1'
+--------+------------------------------+
| ...... |              Extra           |
+--------+------------------------------+
| ...... |   Using index condition      |
+--------+------------------------------+

为什么以上示例中显示的是“Using index condition”而不是“Using where”呢？

这是MySQL Version 5.6+ 的新功能特性，Index Condition Pushdown (ICP)。简单的说就是减少了查询执行时MySQL服务和下层数据引擎的交互次数，达到提高执行性能的目的。如果您关闭MySQL服务中的ICP功能（这个功能默认打开），以上示例的第二个执行语句就会显示“Using where”了。

J：firstmatch(tb_name)：

5.6.x开始引入的优化子查询的新特性之一，常见于where字句含有in()类型的子查询。如果内表的数据量比较大，就可能出现这个

K：loosescan(m..n)：

　　　　5.6.x之后引入的优化子查询的新特性之一，在in()类型的子查询中，子查询返回的可能有重复记录时，就可能出现这个
　　　　除了这些之外，还有很多查询数据字典库，执行计划过程中就发现不可能存在结果的一些提示信息

校长我错了

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL命令 Explain参数说明

MySQL EXPLAIN命令是查询性能优化不可缺少的一部分，该文主要讲解explain命令的使用及相关参数说明。EXPLAIN Output Columns 列名说明 id 执行编号，标识select所属的行。如果在语句中没子查询或关联查询，只有唯一的select，每行都将显示1。否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置...
复制链接

扫一扫