投稿
  1. 极客之音首页
  2. 后端开发

MySQL的EXPLAIN命令总结笔记

小半 • • 后端开发 • 阅读 59

导读：本篇文章讲解 MySQL的EXPLAIN命令总结笔记，希望对大家有帮助，欢迎收藏，转发！站点地址：www.bmabk.com

文章目录

1. 基本介绍

1.1 MySQL的查询优化器

在这里插入图片描述
MySQL不同的版本和不同的存储引擎对同一条SQL，优化器可能有不同的优化行为，所以同一条SQL语句的执行计划可能不同

1.2 MySQL性能的常见瓶颈

MySQL性能的常见瓶颈有以下三点：

  1. CPU: CPU饱和一般发生在数据装入内存或从磁盘上读取数据的时候
  2. IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
  3. 服务器硬件：可以通过top，free，iostat和vmstat来查看系统的性能状态。其中top命令用于查看进程的CPU占有率，free用于查看内存情况，iostat用于监控系统设备的IO负载情况，vmstat用于获得有关进程、虚存、页面交换空间及 CPU活动的信息.

1.3 EXPLAIN简介

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的，以此分析你的查询语句或是表结构的性能瓶颈。EXPLAIN 在MySQL5.6后允许解释非SELECT查询，能对UPDATE,INSERT等的查询进行解释，尽管可以将DML语句转化为准等价的“SELECT”查询并EXPLAIN，但结果并不会完全反映语句是如何执行的，但是仍然非常有帮助。

通过EXPLAIN关键字返回的信息，我们可以得到以下信息：

  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每个表有多少行被优化器查询

用法：EXPLAIN + SQL语句

EXPLAIN 常用的返回的信息如下：
在这里插入图片描述
对于这些字段的简要说明：
在这里插入图片描述
下面逐一解释以上字段的含义

2. 执行计划的各字段解释

准备工作，创建4个表：

CREATE TABLEt1(idINT(10)AUTO_INCREMENT,contentVARCHAR(100)NULL,PRIMARYKEY(id));
CREATE TABLEt2(idINT(10)AUTO_INCREMENT,contentVARCHAR(100)NULL,PRIMARYKEY(id));
CREATE TABLEt3(idINT(10)AUTO_INCREMENT,contentVARCHAR(100)NULL,PRIMARYKEY(id));
CREATE TABLEt4(idINT(10)AUTO_INCREMENT,contentVARCHAR(100)NULL,PRIMARYKEY(id));
INSERT INTOt1(content)VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000)));
INSERT INTOt2(content)VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000)));
INSERT INTOt3(content)VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000)));
INSERT INTOt4(content)VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)));

2.1 id

select查询的序列号，包含一组数字，表示查询中select子句或操作表的顺序。如果在语句当中没有子查询或联合，那么只会有唯一的SELECT，于是每一行在这个列中都将显示一个1。否则，内层的SELECT语句一般会顺序编号，对应于其在原始语句的位置。

注意：id号每个号码表示一次独立的查询。一条SQL语句的查询次数越少越好。

id的值分为以下三种情况：
（1）id相同。则执行顺序由上至下。

EXPLAIN SELECT * FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id

在这里插入图片描述

（2）id不同。如果是子查询，id的序号会递增， id值越大优先级越高，越先被执行。

EXPLAIN SELECT t1.id FROM t1 WHERE t1.id IN 
       (SELECT t2.id FROM t2 WHERE t2.id IN
          (SELECT t3.id FROM t3 WHERE t3.content ='' )
);

在这里插入图片描述

（3）id有相同和不同。id如果相同，可以认为是一组，从上往下顺序执行。在所有组中，id值越大，优先级越高，越先执行衍生=DERIVED。

EXPLAIN SELECT t2.* FROM t2,(SELECT * FROM t3 WHERE t3.content='') s3 WHERE s3.id=t2.id;

在这里插入图片描述

2.2 select_type

select_type代表查询的类型，查询类型分为简单和复杂类型。复杂类型可分为三大类：简单子查询、派生表（DERIVED）、UNION联合查询。select_type包括的类型如下表所示：
在这里插入图片描述

2.2.1 SIMPLE

SIMPLE代表简单的select查询，查询中不包含子查询或者UNION；

EXPLAIN SELECT * FROM t1;

在这里插入图片描述

2.2.2 PRIMARY

查询中若包含任何复杂的子部分，最外层查询则被标记为Primary。

EXPLAIN SELECT * FROM (SELECT t1.content FROM t1) a;

在这里插入图片描述

2.2.3 DERIVED

在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询,把结果放在临时表中。

2.2.4 SUBQUERY

SUBQUERY则是在SELECT或WHERE列表中包含了子查询.

EXPLAIN SELECT t2.id FROM t2 WHERE t2.id=(SELECT t3.id FROM t3 WHERE t3.id =1);

在这里插入图片描述

2.2.5 DEPENDENT SUBQUERY

在SELECT或WHERE列表中包含了子查询,子查询基于外层查询中发现的数据。

EXPLAIN SELECT t2.id FROM t2 WHERE t2.id IN 
(SELECT t3.id FROM t3 WHERE t3.content ='t3_192');

在这里插入图片描述

都是where后面的条件，subquery是单个值，dependentsubquery是一组值。

2.2.6 UNCACHEABLE SUBQUREY

无法使用缓存的子查询。因为SELECT中的某些特性阻止结果被缓存于一个Item_cache中（这不等于查询缓存）。示例：当使用了@@来引用系统变量的时候，不会使用缓存。

EXPLAIN SELECT * FROM t3 WHERE id =(SELECT id FROM t2 WHERE t2.id=@@sort_buffer_size);

在这里插入图片描述

2.2.7 UNION

若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED。

EXPLAIN SELECT t2.id,t2.content FROM t2 
UNION ALL 
SELECT t3.id,t3.content FROM t3;

在这里插入图片描述

2.2.8 UNION RESULT

从UNION的匿名临时表检索结果的SELECT未标记为UNION RESULT。

2.3 table

table是指明基于那张表或该表的别名（如果SQL中定义了别名）。

2.4 type

type 是查询的访问类型，即MySQL决定如何查找表中的行，是较为重要的一个指标。结果值从最好到最坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >all，一般来说，得保证查询至少达到 range 级别，最好能达到 ref。

在这里插入图片描述

2.4.1 system

表只有一行记录（等于系统表），这是 const 类型的特列，平时不会出现，这个也可以忽略不计

2.4.2 const

表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量.

EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE t1.id=1) s;

在这里插入图片描述

2.4.3 eq_ref

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

EXPLAIN SELECT * FROM t1,t2 WHERE t1.id=t2.id;

在这里插入图片描述

2.4.4 ref

这是一种索引访问，返回匹配某个单独值的所有行，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。此类索引访问只有当使用非唯一性索引或者唯一性索引的非唯一性前缀时才会发生。

没用索引前：

EXPLAIN SELECT * FROM t1,t2 WHERE t1.content=t2.content;

在这里插入图片描述
建立索引后：

在这里插入图片描述

2.4.5 range

只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

EXPLAIN SELECT * FROM t1 WHERE t1.id<10;

在这里插入图片描述

EXPLAIN SELECT * FROM t1 WHERE t1.id in (10,13,14);

在这里插入图片描述

2.4.6 index

index跟全表扫描一样，只是MySQL扫描表时按索引次序进行而不是行。它的主要优点是避免了排序，最大的缺点是要承担按索引次序读取整个表的开销。

在这里插入图片描述

2.4.7 all

FullTableScan全表扫描，通常意味着MySQL必须扫描整个表以找到匹配的行。
在这里插入图片描述

2.4.8 index_merge

在查询过程中需要多个索引组合使用，通常出现在有or的关键字的sql中。
在这里插入图片描述

2.4.9 ref_or_null

对于某个字段既需要关联条件，也需要null值得情况下。查询优化器会选择用ref_or_null连接查询。
在这里插入图片描述

2.4.10 index_subquery

利用索引来关联子查询，不再全表扫描。
在这里插入图片描述
在这里插入图片描述

在这里插入图片描述

2.4.11 unique_subquery

该联接类型类似于index_subquery。子查询中的唯一索引。
在这里插入图片描述

2.5 possible_keys

显示可能应用在这张表中的索引，一个或多个。==查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。==因为这个列表是在优化过程的早期创建的，因此有些罗列出来的索引可能对于后续优化过程是没用的。

2.6 key

key列显示了实际使用的索引。如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中，而没有出现在 possible_keys列中。

换句话说， possible_keys揭示了哪一个索引有助于高效地行查找，而key显示的是优化采用哪一个索引可以最小化查询成本。

要想强制 MySQL 使用或忽视 possible_keys 列中的索引，在查询中可以使用 FORCE INDEX、USE INDEX 或者 IGNORE INDEX命令。

2.7 key_len

key_len列表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。key_len字段能够帮你检查是否充分的利用上了索引。ken_len越长，说明索引使用的越充分。
在这里插入图片描述
在这里插入图片描述

①先看索引上字段的类型+长度比如int=4;varchar(20)=20;char(20)=20
②如果是varchar或者char这种字符串字段，视字符集要乘不同的值，比如utf-8要乘3,GBK要乘2，③varchar这种动态字符串要加2个字节
④允许为空的字段要加1个字节
第一组：
key_len=age的字节长度+name的字节长度=4+1+(20*3+2)=5+62=67
第二组：
key_len=age的字节长度=4+1=5

具体的计算规则如下：
在这里插入图片描述

2.8 ref

ref列显示key列记录的索引中查找值所用的列或常量。
在这里插入图片描述

2.9 rows

rows列显示MySQL认为它执行查询时必须检查的行数。越少越好！注意这是一个预估值。
在这里插入图片描述

2.10 Extra

Extra包含其他的额外重要的信息。该列显示 MySQL 在查询过程中的一些详细信息，MySQL 查询优化器执行查询的过程中对查询计划的重要补充信息。

在这里插入图片描述

2.10.1 Using filesort

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。

出现filesort的情况：
在这里插入图片描述
优化后，不再出现filesort的情况：
在这里插入图片描述
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

2.10.2 Using temporary

Using temporary表示使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
优化前：
在这里插入图片描述

优化后：

在这里插入图片描述

2.10.3 Using index

Using index代表表示相应的select操作中使用了覆盖索引(CoveringIndex)，避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找;如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。利用索引进行了排序或分组。

覆盖索引的概念
在这里插入图片描述

2.10.4 Using where

Using where表明MySQL服务器将在存储引擎检索行后再进行过滤。许多WHERE条件里涉及索引中的列，当它读取索引时，就能被存储引擎检验。简单理解就是Using where表示WHERE条件里涉及了索引中的列。

2.10.5 Using join buffer

在这里插入图片描述
使用了连接缓存

2.10.6 Impossible where

where子句的值总是false，不能用来获取任何元组。
在这里插入图片描述

2.10.7 Select tables optimized away

在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

在innodb中：
在这里插入图片描述

在MyISAM中：
在这里插入图片描述

2.10.8 Distinct

优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

参考资料：
1.https://www.bilibili.com/video/BV1KW411u7vy
2. https://www.jianshu.com/p/ea3fc71fdc45
3. 官方文档：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/44298.html

赞 (0)
小半的头像小半
0 0

相关推荐

极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！