1. 全栈程序员必看首页

【mysql系列】细谈explain执行计划之“谜”

全栈程序员-站长 • • 未分类 • 阅读 4

【mysql系列】细谈explain执行计划之“谜”目录理论Part概念实践Partidselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra总结聊到mysql数据库的优化，大家基本都会谈论explain关键字，确认sql是否使用数据库表中建立的索引，然后讨论sql语句或者索引优化方案等等～，那本篇文章主要谈论一下explain的理解。主要分为理论和实践相结合。理论Part概念我们先了解一下explain语法和相关理论知识。语法：EXPLAINSELECTselect_o

大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全家桶1年46，售后保障稳定

目录

聊到mysql数据库的优化，大家基本都会谈论
explain关键字，确认sql是否使用数据库表中建立的索引，然后讨论sql语句或者索引优化方案等等～，那本篇文章主要谈论一下
explain的理解。主要分为
理论和
实践相结合。

理论Part

概念

我们先了解一下explain语法和相关理论知识。
语法：
EXPLAIN SELECT select_options;

  1. select_options是select语句的查询选项，包括from where子句等等。
  2. 执行该语句，可以分析EXPLAIN后面的select语句的执行情况，并且能够分析出所查询的表的一些特征。
    例如：EXPLAIN SELECT * FROM class;
    执行结果如图：
    在这里插入图片描述

执行计划中各个列代表具体含义解释如下：

  • id:
查询的序号，包含一组数字，表示查询中执行select子句或操作表的顺序
1.id相同，执行顺序从上往下
2.id不同，id值越大，优先级越高，越先执行

Jetbrains全家桶1年46，售后保障稳定

  • select_type:
查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询
1.simple ——简单的select查询，查询中不包含子查询或者UNION
2.primary ——查询中若包含任何复杂的子部分，最外层查询被标记
3.subquery——在select或where列表中包含了子查询
4.derived——在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中
5.union——如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived，故在union中第二个及之后的select。
6.union result:UNION 临时表检索结果的select。
  • table:
输出的行所引用的表
  • partitions:
如果查询基于分区表，将会显示访问的是哪个区。
  • type:
显示连接类型，显示查询使用了何种类型，按照从最佳到最坏类型排序
1.system：表中仅有一行（=系统表）这是const联结类型的一个特例。
2.const：表示通过索引一次就找到，const用于比较primary key或者unique索引。因为只匹配一行数据，所以如果将主键置于where列表中，mysql能将该查询转换为一个常量
3.eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于唯一索引或者主键扫描，常用于连接查询。简单查询不会出现该类型
4.ref:非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，是使用普通索引或者唯一性索引的部分前缀，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体
5.range:只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描
6.index:index 与all区别为index类型只遍历索引树。通常比all快，因为索引文件比数据文件小很多。
7.all：遍历全表以找到匹配的行
type常见类型从最优到最差：system > const > eq_ref > ref > range > index > ALL
注意:一般保证查询至少达到range级别，最好能达到ref。
  • possible_keys:
指出MySQL能使用哪个索引在该表中找到行
  • key:
显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。查询中如果使用覆盖索引，则该索引和查询的select字段重叠。
1.要想强制mysql使用或者忽视possible_key列中的索引，在查询中使用force index、use index或者ignore index。
  • key_len:
表示索引中使用的字节数，该列计算查询中使用的索引的长度在不损失精度的情况下，长度越短越好。如果键是NULL,则长度为NULL。该字段显示为索引字段的最大可能长度，并非实际使用长度。
  • ref:
显示索引的哪一列被使用了，如果有可能是一个常数，哪些列或常量被用于查询索引列上的值
  • rows:
根据表统计信息以及索引选用情况，大致估算出找到所需的记录所需要读取的行数
  • filtered:
指返回结果的行占需要读到的行(rows列的值)的百分比。
  • Extra:
包含不适合在其他列中显示，但是十分重要的额外信息
1、Using filesort：说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序”
2、Using temporary:使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。
3、Using index:表示相应的select操作用使用覆盖索引，避免访问了表的数据行。如果同时出现using where，表名索引被用来执行索引键值的查找；如果没有同时出现using where，表名索引用来读取数据而非执行查询动作。
4、Using where :表明使用where过滤
5、using join buffer:使用了连接缓存
6、impossible where:where子句的值总是false，不能用来获取任何元组
7、select tables optimized away：在没有group by子句的情况下，基于索引优化Min、max操作或者对于MyISAM存储引擎优化count（*），不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
8、distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

实践Part

use explain_detail;

DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `id` int(11) NOT NULL comment '教师id',
  `teacher_name` varchar(45) DEFAULT NULL comment '姓名',
  `teacher_no` varchar(45) DEFAULT NULL comment '教师编号',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO `teacher` (`id`, `teacher_name`, `teacher_no`)
VALUES (1,'溪源a','150921'), (2,'溪源b','201010'), (3,'溪源c','200325');
 
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `id` int(11) NOT NULL AUTO_INCREMENT comment '班级ID',
  `class_name` varchar(10) DEFAULT NULL comment '班级名称',
  PRIMARY KEY (`id`),
  KEY `idx_class_name` (`class_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO `class` (`id`, `class_name`)
VALUES (1,'java1'),(2,'java2'),(3,'java3');
 
# 班级教师关系表
DROP TABLE IF EXISTS `class_teacher`;
CREATE TABLE `class_teacher` (
  `id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL comment '班级ID',
  `teacher_id` int(11) NOT NULL comment '教师ID'
  PRIMARY KEY (`id`),
  KEY `idx_class_teacher_id` (`class_id`,`teacher_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 # 插入数据
INSERT INTO `class_teacher` (`id`, `class_id`, `teacher_id`)
VALUES (1, 1, 1), (2, 1, 2), (3, 2, 1);

id

explain select (select id from teacher limit 1) from class;

在这里插入图片描述

理论知识中介绍到id值越大执行优先级越高，id值相同则从上往下执行，id为null最后执行。从图中ID列，我们看到ID=2的先执行即先查询teacher表。

select_type

  1. simple
    简单的select查询，查询中不包含子查询或者UNION;
    在这里插入图片描述

  2. primary和subquery
    primary：查询中若包含任何复杂的子部分，标记最外层查询语句；
    subquery：在select或where列表中包含子查询,标记子查询语句；

explain select (select id from teacher) from class;

在这里插入图片描述

subquery子查询teacher表，外层select为primary。

在这里插入图片描述
这条sql语句可以依据ID列，区分SQL语句的执行顺序。

  1. derived
    在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中。
explain select * from (select * from teacher limit 1) tmp;

在这里插入图片描述
依据ID=2，先查询teacher表，然后执行最外层查询，并将结果存入临时表。

  1. union、union result
    union:若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived;故在union中第二个及之后的select。
    union result:从union临时表检索结果的select。
explain select * from teacher where id = 1 union select * from teacher;

在这里插入图片描述
id=1为primary;说明是做外层查询，即此条sql语句from前面的语句；
id=2为union；说明是union后面的查询语句；
id=null,标记为UNION RESULT，生成的临时表；
两个的结果合并为union result，供select 检索。
再次说明下id列：1，2，null，执行顺序2 –> 1 –> null。
先执行select 2，然后执行select 1，最后执行执行从两个笛卡尔积检索数据。

table

输出的行所引用的表；

  1. 当 from 子句中有子查询时，table列是 <derivenN> 格式，表示当前查询**依赖 id=N **的查询，于是先执行 id=N 的查询。
    在这里插入图片描述
  2. 当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select行id。
    在这里插入图片描述

type

  1. null
    MySql优化器能够在优化阶段分解查询语句，在执行阶段就不用访问表或索引。
    在这里插入图片描述

  2. system

只有一条数据的系统表 或 衍生表<derived>只有一条数据的主查询才会出现，可以忽略掉，没有太大意义。
在这里插入图片描述

  1. const
    表示通过索引一次就找到了，const用于比较primary key 或者 unique索引（查询类型与索引类型有关）。因为只需匹配一行数据，所有很快。如果将主键置于where列表中，mysql就能将该查询转换为一个const。
    在这里插入图片描述
    where 语句中使用主键索引作为条件。

  2. eq_ref
    唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或 唯一索引扫描。
    primarykey 或 unique key 索引的所有部分被连接使用，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的连接类型了，简单的 select 查询不会出现这种 type。
    在这里插入图片描述
    id列都是1，当id列值一样时，从上到下执行表。所以先执行class_teacher表，后执行class表。

  3. ref
    相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
    在这里插入图片描述

  4. range
    使用索引列检索指定范围，where后面是一个范围查询(between and，in ，>， <， >=)。

在这里插入图片描述

  1. index
    查询全部索引中的数据即只有索引树被扫描;因为索引文件通常比数据文件小,故通常比ALL快一些。
    在这里插入图片描述

注意：class表，上面创建表时，建立class_name索引；同样的查询用于teacher表中，便会全表扫描。

  1. all
    MySQL将遍历全表以找到匹配的行。没有建立索引或索引失效，查询全表数据，开发中应尽量避免。

在这里插入图片描述

possible_keys

指出MySQL能使用哪些索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）
在这里插入图片描述

key

key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中
如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
在这里插入图片描述

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）
不损失精确性的情况下，长度越短越好,长度越短，索引校验匹配效率越高。

下面分别使用普通索引和联合索引看下key_len具体数值；
在这里插入图片描述
使用主键索引，使用字节数4；
在这里插入图片描述
使用联合索引，key_len=8；

下面扩展一下key_len的计算规则：

1）字符串
char(n)：n字节长度；
varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2；
2）数值类型
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节　　
3）时间类型
date：3字节
timestamp：4字节
datetime：8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL。（这是为什么会比正常计算多1的原因）。
索引最大长度是768字节，当字符串过长时，MySql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

ref

显示在key列索引中，表查找值所用到的列或常量，一般比较常见为const或字段名称。
在这里插入图片描述

rows

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

filtered

指返回结果的行占需要读到的行(rows列的值)的百分比。

在这里插入图片描述
从图中可以看到rows=3；指定数据溪源a记录数1条,故filtered = 1 / 3 * 100/100 = 33.33%，保留两位小数。

在这里插入图片描述
那这里为什么是1呢，因为覆盖索引列，不需要与全表对比；

Extra

写到这里终于到最后一列啦，再坚持一下下～

  1. Using index
    Extra显示Using Index，说明用到了索引，是性能高的表现。一般出现在查询的列被索引列覆盖。
    在这里插入图片描述

  2. Using where
    Extra显示Using where，表示没有用到索引，查询的列未被索引列覆盖。
    在这里插入图片描述

  3. Using where Using index
    Extra显示Using whre Using index，表示查询的列被索引列覆盖，并且where筛选条件是索引列之一,但不是最左原则中第一个索引，常出现在联合索引场景。
    在这里插入图片描述

  4. NULL
    Extra显示null，表示查询的列未被索引列覆盖，并且where筛选条件是索引的前导列，说明用到了索引，
    但是部分字段未被索引列覆盖，必须通过“回表”来实现，所以不是纯粹地用到了索引，也不是完全没用到索引。

在这里插入图片描述

  1. Using index condition
    Extra显示Using index condition与Using where类似，查询的列不完全被索引列覆盖，where条件中是一个前导列的范围。

在这里插入图片描述

总结

溪源花费三个晚上终于整理完了这篇文章，希望能够帮助到大家，得到大家的支持，若存在不正之处，望大佬们积极指正。

大家记得
一键三连呀~

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

发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/230876.html原文链接：https://javaforall.net

赞 (0)
0 0

关于作者

全栈程序员-站长的头像

全栈程序员-站长

133.5K 文章
3 粉丝
本网站汇聚当前互联网主流语音，持续更新，欢迎关注公众号“全栈程序员社区”
上一篇 2025年6月16日 上午11:43
下一篇 2025年6月16日 下午12:15


相关推荐

  • pytorch的安装及其在pycharm中的使用「建议收藏」

    pytorch的安装及其在pycharm中的使用「建议收藏」

    pytorch的安装及其在pycharm中的使用「建议收藏」1.首先配置Anaconda虚拟环境在AnacondaPrompt中输入condacreate-npytorchpython==3.72.在该环境中安装pytorch因为前面已经安装了cuda10.0.130和cudnn，安装与之匹配的pytorch版本，官网中寻找，但是官网的貌似不太对，最后看的是这个回答pipinstalltorch==1.2.0torchvision==0.4.0-fhttps://download.pytorch.org/whl/torch_stab

    全栈程序员-站长的头像 全栈程序员-站长
    2022年8月25日
    10
  • 数据库主键和外键的区别

    数据库主键和外键的区别

    数据库主键和外键的区别什么是主键 外键关系型数据库中的一条记录中有若干个属性 若其中某一个属性组 注意是组 能唯一标识一条记录 该属性组就可以成为一个主键 nbsp 比如 nbsp nbsp 学生表 学号 姓名 性别 班级 nbsp 其中每个学生的学号是唯一的 学号就是一个主键 nbsp 课程表 课程编号 课程名 学分 nbsp 其中课程编号是唯一的 课程编号就是一个主键 nbsp 成绩表 学号 课程号 成绩 nbsp 成绩表中单一一个属性无法唯一标识一

    全栈程序员-站长的头像 全栈程序员-站长
    2026年3月18日
    2
  • ajax html例子,AJAX实例[通俗易懂]

    ajax html例子,AJAX实例[通俗易懂]

    ajax html例子,AJAX实例[通俗易懂]AJAX实例实例一：一个简单的AJAX实例创建一个简单的XMLHttpRequest，从一个TXT文件中返回数据实例functionloadXMLDoc(){varxmlhttp;if(window.XMLHttpRequest){//IE7+,Firefox,Chrome,Opera,Safari浏览器执行代码xmlhttp=newXMLHttpRequest();}els…

    全栈程序员-站长的头像 全栈程序员-站长
    2022年8月16日
    10
  • 数据结构与算法栈的详解_数据结构怎么判断出栈的顺序

    数据结构与算法栈的详解_数据结构怎么判断出栈的顺序

    数据结构与算法栈的详解_数据结构怎么判断出栈的顺序一、什么是栈栈（stack）是一种先进后出的有序列表，其中的元素只能在线性表的同一端进出，允许元素插入和删除的一端被称为栈顶（top），固定的另一端被称为栈底（button）。二、数组简单实现栈

    全栈程序员-站长的头像 全栈程序员-站长
    2022年8月16日
    6
  • kafka 和其他 MQ 之间的区别

    kafka 和其他 MQ 之间的区别

    kafka 和其他 MQ 之间的区别

    全栈程序员-站长的头像 全栈程序员-站长
    2026年3月16日
    3
  • hps结构

    hps结构

    hps结构c

    全栈程序员-站长的头像 全栈程序员-站长
    2026年3月17日
    2

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注

关注全栈程序员社区公众号