MySQL Explain详解

691 阅读9分钟

最近代码review时,因一个SQL语句的性能问题和同学产生争论。程序员的优点是大家都认事实,用explain查看结果,轻松解决争议。

原因

之所以想写explain,有以下几个原因:

  1. 最近看《MySQL45讲》里面有很多场景都用explain解释

  2. 前些日子因一个SQL产生争议,使用explain进行验证

  3. 网上很多资料,只讲explain各个字段的含义,却没有对应实例,大家感受不深

  4. 对explain虽有一些了解,但没有特别深入

基于这些原因,整理一下explain各字段含义。

表结构和数据

表结构

数据库中创建两张表trace_sp_info和trace_spinfo2,两个表结构完成一致:

mysql> show create table trace_sp_info;

CREATE TABLE `trace_sp_info` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `sp_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '服务商id',
  `sp_name` varchar(50) NOT NULL DEFAULT '' COMMENT '服务商名称',
  `type` tinyint DEFAULT '0' COMMENT '服务商类型:0供应链服务商 1审核服务商 2溯源码供应商',
  `type_name` varchar(50) NOT NULL DEFAULT '' COMMENT '服务商类型名称',
  `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '状态 0未激活 1激活 2失效',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `create_by` varchar(50) NOT NULL DEFAULT '' COMMENT '创建人',
  `update_by` varchar(50) NOT NULL DEFAULT '' COMMENT '更新人',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_spid` (`sp_id`),
  KEY `idx_update_time` (`update_time`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='服务商类型';

数据

mysql> select * from trace_sp_info;

图片

mysql> select * from trace_sp_info2;

图片

MySQL版本

mysql> select version();

图片

字段详解

mysql> explain select * from trace_sp_info; 

图片

通过explain结果,可以看到有id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra字段。

id

含义

代表SQL语句的执行顺序

  1. id如果相同,可以认为是同一组,从上往下顺序执行

  2. id值越大,优先级越高,越先执行;对于子查询,id的序号会递增

实例

同id

mysql> explain select * from trace_sp_info2 where id in (select id from trace_sp_info where id = 2);

图片

不同id

mysql> explain select * from trace_sp_info2 where id = (select id from trace_sp_info where id = 2);

图片

select_type

含义

select查询的类型,主要用以区别普通查询和联合查询、子查询之类的复杂查询。有如下类型:

  1. SIMPLE:简单SELECT,不使用UNION或子查询等

  2. PRIMARY:子查询中最外层查询,查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY

  3. UNION:UNION中的第二个或后面的SELECT语句

  4. DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询

  5. UNION RESULT:UNION的结果,union语句中第二个select开始后面所有select

  6. SUBQUERY:子查询中的第一个SELECT,结果不依赖于外部查询

  7. DEPENDENT SUBQUERY:子查询中的第一个SELECT,依赖于外部查询

  8. DERIVED:派生表的SELECT, FROM子句的子查询

  9. UNCACHEABLE SUBQUERY:一个子查询的结果不能被缓存,必须重新评估外链接的第一行

工作中常看到SIMPLE、PRIMARY、SUBQUERY。

实例

SIMPLE

mysql> explain select * from trace_sp_info;

图片

PRIMARY+SUBQUERY

mysql> explain select * from trace_sp_info2 where id = (select id from trace_sp_info where id = 2);

图片

table

含义

表示这一步SQL所访问的表名称,即这一行的数据来源于哪张表。有时不是真实的表名字,可能是别名,也可能是第几步执行的结果的简称

实例

别名

mysql> explain select * from trace_sp_info s1, trace_sp_info2 s2 where s1.id=s2.id;

图片

partitions

含义

如果查询是基于分区表的话,会显示查询将访问的分区

type

含义

对表的访问方式,表示MySQL在表中找到所需行的方式,又称“访问类型”。通过type可以快速查看SQL性能。

常用的类型有: ALL、index、range、 ref、eq_ref、const、system、NULL(从左到右,性能从差到好)

  1. ALL:Full Table Scan, MySQL将遍历全表以找到匹配的行

  2. index:Full Index Scan,index与ALL区别为index类型只遍历索引树

  3. range:只检索给定范围的行,使用一个索引来选择行

  4. ref:表示表的连接匹配条件,即哪些列或常量被用于查找索引列上的值,查指定值,而不是范围

  5. eq_ref:类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用primary key或者 unique key作为关联条件

  6. const、system:当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该查询转换为一个常量,system是const类型的特例,当查询的表只有一行的情况下,使用system

  7. NULL:MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。

实例

ALL

mysql> explain select * from trace_sp_info;

这种需求只能遍历全表

图片

index

mysql> explain select id from trace_sp_info;

只需遍历非聚簇索引idx_update_time,直接获取到id,不用回表,实现了覆盖索引的功能。

从这里也能推断出,如果MySQL选择不同索引树,输出的id顺序可能不一致。

图片

range

mysql> explain select * from trace_sp_info where id >1;

优化器利用主键索引,实现树搜索,能够快速定位到id>1的位置,然后从大于1的位置开始遍历

图片

ref

mysql> explain select * from trace_sp_info where update_time ='2021-08-31 18:03:5';

图片

eq_ref

mysql> explain select * from trace_sp_info s1,trace_sp_info2 s2 where s1.sp_id=s2.sp_id and s1.sp_id >=1;

一般是primary key或者 unique key作为关联条件

图片

const

mysql> explain select * from trace_sp_info where id=1;

图片

NULL

mysql> explain select max(id) from trace_sp_info;

图片

possible_keys

含义

显示可能应用的索引,一般一个或者多个。

SQL语句涉及到的字段上若存在索引,则该索引被列出,但是不一定被实际使用。

如果该列是NULL,则没有相关的索引。

实例

mysql> explain select id from trace_sp_info where id >1;

图片

key

含义

key列显示MySQL实际决定使用的键(索引),大概率包含在possible_keys中。

key的值不一定是possible-keys的子集。SQL语句中的字段,若没有涉及到索引,MySQL选择覆盖索引,则该索引仅出现在key列表中。

如果没有选择索引,键是NULL。

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

实例

覆盖索引

mysql> explain select id from trace_sp_info;

图片

force index

使用force index可以解决MySQL选择索引错误的问题。

mysql> explain select id from trace_sp_info where id >1;

图片

mysql> explain select id from trace_sp_info force index (idx_update_time) where id >1;

图片

key_len

含义

表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度,在不损失精确性的情况下,长度越短越好。

key-len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。

实例

mysql> explain select * from trace_sp_info force index (id) where update_time>0;

图片

mysql> explain select * from trace_sp_info where id=1;

图片

ref

含义

显示索引的哪一列被使用了。

有时候会是一个常量,表示哪些列或常量被用于查找索引列上的值。

实例

const

mysql> explain select * from trace_sp_info where update_time ='2021-08-31 18:03:5';

图片

指定列

mysql> explain select * from trace_sp_info s1,trace_sp_info2 s2 where s1.sp_id=s2.sp_id and s1.sp_id >=1;

表明trace_sp_info的sp_id列的值被用于查找trace_sp_info2上sp_id对应的值。

图片

rows

含义

估算结果集行数,表示MySQL根据表统计信息及索引选用情况,估算找到所需记录需要读取的行数。

表统计信息中,有一个名词为基数,表示一个索引上不同的值的个数。采样统计的时候,InnoDB 默认会选择 N 个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数。

实例

mysql> explain select * from trace_sp_info; 

图片

filtered

含义

filtered表示返回结果的行数占需读取行数的百分比,filtered列的值越大越好,也是估算值

实例

100%

mysql> explain select * from trace_sp_info;

图片

50%

mysql> explain select * from trace_sp_info where status=0;

图片

Extra

含义

该列包含MySQL解决查询的详细信息,如是否使用文件等,主要有以下几种情况:

  1. Using where:使用where过滤条件

  2. Using temporary(十死无生的提示,极大影响mysql性能,需要尽快优化):表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询,常见 group by ; order by。group by一定要遵循所建索引的顺序。

  3. Using filesort(九死一生的提示,需要尽快优化):当Query中包含 order by 操作,而且无法利用索引完成

    的排序操作称为“文件排序”。排序的时候最好遵循所建索引的顺序否则就可能会出现Using filesort。

  4. Using Index(效率不错):

    表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错

    如果同时出现Using Where,表明索引被用来执行索引键值的查找

    如果没有同时出现Using Where,表明索引用来读取数据而非执行查找工作

  5. Using join buffer:改值强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。如果出现了这个值,那应该注意,根据查询的具体情况可能需要添加索引来改进能。

  6. Impossible where:这个值强调了where语句会导致没有符合条件的行(通过收集统计信息不可能存在结果)。

  7. Select tables optimized away:这个值意味着仅通过使用索引,优化器可能仅从聚合函数结果中返回一行

  8. No tables used:Query语句中使用from dual 或不含任何from子句

其中Using filesort,Using temporary,Using index最为常见,出现前两种表示是需要优化的地方,出现第三种表示索引效率不错。

实例

Using where

mysql> explain select * from trace_sp_info where type=0;

图片

Using filesort

mysql> explain select * from trace_sp_info order by type ;

图片

Using temporary

mysql> explain select count(*),type from trace_sp_info group by type;

图片

mysql> explain select count(*) from trace_sp_info group by update_time;

图片

Using index

mysql> explain select id from trace_sp_info where update_time ='2021-08-31 18:03:5';

图片

Using where+Using index

mysql> explain select id from trace_sp_info where update_time > 0;

图片

总结


虽然explain有很多字段,但大家可以重点关注type、rows和Extra。通过改变索引或者改变SQL,让性能更好。

今后写SQL的时候,都可以用explain检查一下,既能查看性能,也能检查是否与自己想法一致。

资料

  1. MySQL Explain详解

  2. 004--Explain实战详解:id分析

  3. explain执行计划详解

  4. blog.csdn.net/weixin\_422…

  5. MySQLexplain之Extra介绍

最后

大家如果喜欢我的文章,可以关注我的公众号(程序员麻辣烫)

我的个人博客为:shidawuhen.github.io/

往期文章回顾:

  1. 设计模式

  2. 招聘

  3. 思考

  4. 存储

  5. 算法系列

  6. 读书笔记

  7. 小工具

  8. 架构

  9. 网络

  10. Go语言