MySQL8.0分析查询语句EXPLAIN

技术人生-小草

已于 2022-11-17 15:48:45 修改

阅读量546

点赞数

分类专栏： MySQL8.0从入门到高级文章标签：数据库 sql mysql

于 2022-11-17 15:32:29 首次发布

本文链接：https://blog.csdn.net/ChinaYangJu/article/details/127904839

版权

MySQL8.0从入门到高级专栏收录该内容

26 篇文章 0 订阅

订阅专栏

文章目录

学习资料
分析查询语句EXPLAIN

学习资料

【MySQL数据库教程天花板，mysql安装到mysql高级，强！硬！-哔哩哔哩】
【阿里巴巴Java开发手册】https://www.w3cschool.cn/alibaba_java

分析查询语句EXPLAIN

EXPLAIN或DESCRIBE语句语法形式如下：

EXPLAIN SELECT 1;
或者
DESCRIBE SELECT 1;

如果我们想看看某个查询的执行计划，的话，可以具体的查询语句前面加一个EXPLAIN，就像这样：

EXPLAIN SELECT 1;

在这里插入图片描述

输出的上述信息就是所谓的执行计划。在这个执行计划的辅助下，我们需要知道应该怎样改进自己的查询语句以使查询执行起来更高效。其实除了以SELECT开头的查询语句，其余DELETE、INSERT、REPLACE以及UPDATE语句等都可以加上EXPLAIN，用来查看这些语句的执行计划，只是平时我们对SELECT语句更感兴趣。
注意：执行EXPLAIN时并没有真正的执行该后面的语句，因此可以安全的查看执行计划。
EXPLAIN语句输出的各个列的作用如下：

列名	描述
id	在一个大的查询语句中每个SELECT关键字都对应一个`唯一的id`
select_type	SELECT关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type【重点】	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上使用的索引
key_len【重点】	实际使用到的索引长度
ref	当使用索引等值查询时，与索引列进行等值匹配的对象信息
rows【重点】	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra【重点】	一些额外的信息

id

id如果相同，可以认为是一组，从上往下顺序执行
在所有组中，id值越大，优先级越高，越先执行
关注点：id号每个号码，表示一趟独立的查询，一个sql的查询趟数越少越好

select_type

一条大的查询语句里边可以包含若干个SELECT关键字，每个SELECT关键字代表着一个小的查询语句，而每个SELECT关键字的FROM子句里都可以包含若干张表（这些表用来做连接查询），每一张表都对应着执行计划输出中的一条记录，对于同一个SELECT关键字中的表来说，它们的id值都是相同的。
MySQL为每一个SELECT关键字代表的小查询都定义了一个称之为select_type的属性，意思是只要我们知道了某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色。

SIMPLE：查询语句中不包含UNION或者子查询的查询都算作是SIMPLE类型，无论是单表查询还是联合查询这些查询的级别都是SIMPLE。顾名思义，这些查询都被 MySQL 认为是比较简单的查询模式。

PRIMARY：对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的select_type值就是PRIMARY。

UNION：包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的嘛。除了第一个是 PRIMARY，其他的都是 UNION。

UNION RESULT：如果 MySQL 中的 UNION 需要用到临时表进行去重的话，那么这个小查询的级别就是 UNION RESULT。

SUBQUERY：如果我们的子查询不能转换对应 semi-join(多表连接)的形式，而且这个查询不是相关子查询的话，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，这个时候该子查询的第一个 SELECT 的级别就是 SUBQUERY。

DEPENDENT SUBQUERY：如果包含子查询的查询语句不能够转为对应的semi-join(多表连接)的形式，并且该子查询是相关子查询，则该子查询的第一个SELECT关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY。

DEPENDENT UNION：在包含UNION或者UNION ALL的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的select_type的值就是DEPENDENT UNION。

DERIVED：对于包含‘派生表’的查询，该派生表对应的子查询的select_type的值就是DERIVED。

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

partitions（可略）

代表分区表中命中的情况，非分区表，该项为NULL。一般情况我们的查询语句执行计划的partitions列值都是NULL。

type【重点】

执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法，又称“访问类型”，其中的type列就表明了这个访问方法是啥，是较为重要的一个指标。比如，看到type列的值是ref，表明MySQL即将使用ref访问方法来执行对数据表的查询。

完整访问方法如下（性能由优到劣排序）：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。

SQL性能优化的目标：至少要达到range级别，要求是ref级别，最好是consts级别（来源Alibaba开发手册）。

system：当表中只有一条记录并且该表使用的存储引擎的统计数据都是精确地，比如MyISAM、Memory，那么对该表的访问方法就是system。

const：当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const。

eq_ref：在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是eq_ref。

ref：当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref。

fulltext：全文检索，不推荐，推荐使用搜索引擎替代。

ref_or_null：当对普通二级索引进行等值匹配查询，该索引列的值也可以是NULL值时，那么对该表的访问方法就可能是ref_or_null。

index_merge：单表访问方法时在某些场景下可以使用Intersection、Union、Sort-Union这三种索引合并的方式来执行查询就是index_merge。

unique_subquery：是针对一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的type列的值就是unique_subquery。

index_subquery：该连接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：value IN SELECT key_column FROM single_table WHERE some_expr)。

range：如果使用索引获取某些范围区间的记录，那么就可能使用到range访问方法。

index：当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是index。

ALL：全表扫描。

possible_keys和key

在EXPLAIN语句输出的执行计划中，possible_keys列标识在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些。一般查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询使用，key列表示实际用到的索引有哪些，如果为NULL，则没有使用索引。

key_len【重点】

实际使用到的索引长度（即：字节数）帮你检查是否充分利用上了索引，值越大越好，主要针对于联合索引，有一定参考意义。

ref

当使用索引列等值查询时，与索引列进行等值匹配时的对象信息。比如只是一个常数或者某个列。

rows【重点】

预估的需要读取的记录条数，值越少越好。

filtered

某个表经过搜索条件过滤后剩余记录条数的百分比。
如果使用的索引执行的是单表扫描，那么计算时需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。
对于单表查询来说，这个filtered没什么意义，因为我们更关注在连接查询中驱动表对应的执行计划记录的filtered值，它决定了被驱动表要执行的次数（即rows * filtered）。

Extra【重点】

顾名思义，Extra列是用来说明一些额外信息的，包含不适合在其他列中显示但十分重要的额外信息。我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。

No tables used：当查询语句的没有FROM子句时，在Extra列中将会提示该额外信息。

Impossible WHERE：查询语句的WHERE子句永远为FALSE时，在Extra列中将会提示该额外信息。

Using where：当我们使用全表扫描来执行对某个表的查询，并且该语句的WHERE子句中游针对该表的搜索条件时，在Extra列中将会提示该额外信息。

(NULL)：
1、当使用索引访问来执行对某个表的查询，并且该语句的WHERE子句中有除了该索引包含的列之外的其他搜索条件时，在Extra列中将会提示该额外信息。
2、有一些情况下对结果集中的记录进行排序是可以使用到索引的，在Extra列中将会提示该额外信息。

No matching min/max row：当查询列表中有MIN或者MAX聚合函数，但是并没有符合WHERE子句中的搜索条件的记录时，在Extra列中将会提示该额外信息。

Select tables optimized away：当查询列表中有MIN或者MAX聚合函数，并且有符合WHERE子句中的搜索条件的记录时，在Extra列中将会提示该额外信息。

Using index：当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用覆盖索引的情况下，在Extra列中将会提示该额外信息。

Using index condition：有些搜索条件中虽然出现了索引列，但却不能使用到索引，在Extra列中将会提示该额外信息。

Using where：Using join buffer（hash join）：在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MyQL一般会为其分配一块叫join buffer的内存块来加快查询速度，也就是我们所讲的基于块的嵌套循环算法，在Extra列中将会提示该额外信息。

Using where：Not exists：当我们使用左（外）连接时，如果WHERE子句中包含要求被驱动表的某个列等于NULL值的搜索条件，而且那个列又是不允许存储NULL值的，在Extra列中将会提示该额外信息。

Using union(...)：Using where：如果执行计划的Extra列出现了Using intersect(...)提示，说明准备使用Intersect索引合并的方式执行查询，括号中的...表示需要进行索引合并的索引名称；如果出现了Using union(...)提示，说明准备使用Union索引合并的方式执行查询；出现Using sort union(...)提示，说明准备使用Sort-Union索引合并的方式执行查询。

Zero limit：当我们LIMIT子句的参数为0时，表示压根儿不打算从表中读出任何记录，在Extra列中将会提示该额外信息。

Using filesort：很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序，MySQL把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：filesort）。如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的Extra列中显示Using filesort提示。

Using tomporary：在许多查询的执行过程中，MySQL可能会借住临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含DISTINCT、GROUP BY、UNION等子句的查询过程中，如果不能有效利用索引来完成计划的Extra列将会显示Using temporary提示。