MySQL Explain详解

MySQL Explain 分析：SQL性能优化实战指南
最新推荐文章于 2025-10-31 14:21:11 发布
原创 最新推荐文章于 2025-10-31 14:21:11 发布 · 594 阅读 · 2 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #sql #后端

最近代码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. https://blog.youkuaiyun.com/weixin_42288943/article/month/2017/08

  5. MySQLexplain之Extra介绍

最后

大家如果喜欢我的文章，可以关注我的公众号（程序员麻辣烫）

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

图片

往期文章回顾：

  1. 设计模式

  2. 招聘

  3. 思考

  4. 存储

  5. 算法系列

  6. 读书笔记

  7. 小工具

  8. 架构

  9. 网络

  10. Go语言

MySQL EXPLAIN 详解
qq_34319145的博客
03-11 3728
EXPLAIN 是 MySQL 提供的性能分析工具，用于查看 SQL 查询的执行计划（即优化器如何执行查询）。它通过模拟查询过程（不实际执行 SQL）返回一个表格，展示索引使用、表连接方式、扫描行数等关键信息，帮助开发者诊断性能瓶颈并优化查询。核心作用：基本语法： FORMAT：指定输出格式，默认是表格形式（TRADITIONAL），JSON 格式包含更详细信息。 示例： 输出结果示例：性能瓶颈难以定位索引优化盲目性资源浪费与扩展性问题维护成本高必用 EXPLAIN 的场景：快速优化步骤：通过 EXPL
Mysql Explain 详解
10-17
### MySQL EXPLAIN 详解 #### 一、引言 在数据库优化过程中，了解查询执行计划是非常重要的一步。MySQL 提供了 `EXPLAIN` 命令来帮助我们查看查询计划，这对于理解 MySQL 如何执行 SQL 查询以及如何进行优化至关...
Mysql Explain 详解 [原创]
cixin1802的博客
08-06 882
一.语法 explain < table_name > 例如: explain select * from t3 where id=3952602; 二.explain输出解释 +----+---------...
Mysql Explain
02-28
Mysql Explain
MySQL之Explain详解
最新发布
IT- 研究者
10-31 1150
比如A、B、C三列创建一个联合索引idx_abc，假设3个列的数据长度都是3，如果key显示的索引是idx_abc，结合key_len列来看，如果显示的是3，代表联合索引的B和C列索引未生效，如果key_len长度为6或者9，依次类推。6）index：扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些。执行之后返回的是执行计划的信息。
mysql索引possible_keys，key问题。
helianus的专栏
06-07 2621
举例来说就是上面的例子，一个条件查询了表中的百分之99的数据，即使你的where条件中使用到了索引（并且使用了正确使用索引的姿势。），那么优化器也会选择放弃使用这个索引，因为你使用了这个索引，还会额外带来回表的代码，那么还不如直接全表扫描。好了，得出的结论就是possible_keys会列出你的where条件中可能会使用到的索引列，但是具体用不到这个索引，是需要根据你的实际情况来的，如果你的条件，使用到索引和不使用到索引所消耗的效果差不错（磁盘io，数据读取等）。
MySQL explain详解
weixin_30609287的博客
01-15 337
Explain简介 本文主要讲述如何通过 explain 命令获取 select 语句的执行计划，通过 explain 我们可以知道以下信息：表的读取顺序，数据读取操作的类型，哪些索引可以使用，哪些索引实际使用了，表之间的引用，每张表有多少行被优化器查询等信息。 下面是使用 explain 的例子： 在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记...
mysql explain详解
03-29
MySQL中的`EXPLAIN`命令是用于分析SQL查询执行计划的重要工具，它可以帮助数据库管理员和开发者了解MySQL如何处理SQL语句，从而进行性能优化。在深入理解`EXPLAIN`之前，我们先来了解一下数据库优化的基本概念。 ...
mysql explain详解1
08-04
MySQL的EXPLAIN命令是数据库管理员和开发者用来分析SQL查询执行计划的重要工具，它能帮助我们理解MySQL如何使用索引来处理SELECT语句，并优化查询性能。本文将深入解析EXPLAIN输出的各项参数，以便更好地理解和优化...
MySql explain
咸鱼的博客
08-24 237
mysql&amp;gt; explain select * from (select * from ( select * from t1 where id=2602) a) b; +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+ | id | s...
关于mysql中explain的那些事儿
东南风的专栏
12-29 690
有两种用法： 1.EXPLAIN tbl_name 2.EXPLAIN [EXTENDED] SELECT select_options 为了更好的说明它，我们需要建两张表，下面的语句用于创建一张测试用的订单表： CREATE TABLE `t_order` (  `order_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMME
MySQL 对于explain中possible_key，与key的思考
weixin_59141600的博客
10-15 4285
对于MySQL，explain中的相关字段的思考，例如possible_keys、key等
mysql Explain 详解
05-19
MySQL的EXPLAIN是一个非常有用的工具，可以帮助开发人员优化查询语句并提高查询性能。通过执行EXPLAIN，可以了解查询的执行计划、表之间的连接方式以及使用的索引等信息。下面是一份比较详细的MySQL Explain解释： ...
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

程序员麻辣烫

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值