第 15 章 查询优化的百科全书——EXPLAIN 详解

一条查询语句在经过 MySQL 查询优化器的各种基于成本和规则的优化后会生成一个所谓的执行计划，它展示了接下来具体执行查询的方式。

使用Explain语句来查看执行计划。

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

15.1 执行计划输出中各列详解

15.1.1 table

EXPLAIN 语句输出的每条记录都对应着某个单表的访问方法，该条记录的 table 列代表着该表的表名。

 EXPLAIN SELECT * FROM s1;

在这里插入图片描述

EXPLAIN SELECT * FROM s1 INNER JOIN s2;

在这里插入图片描述

15.1.2 id

查询语句中每出现一个SELECT关键字，MySQL 就会为它分配一个唯一的 id。

在连接查询的执行计划中，每个表都会对应一条记录，这些记录的 id 是相同的，出现在前边的表是驱动表，后面表的是被驱动表。

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';

在这里插入图片描述

查询优化器可能对涉及子查询的查询语句进行重写，从而转换为连接查询。

对于包含 UNION 子句的查询语句，id 可能为 NULL

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

UNION 去重时内部创建了一个名为 <union1,2> 的临时表，它的 id 为 NULL。

UNION 因为不需要去重，所以没有这个问题。

15.1.3 select_type
名称描述举例
SIMPLE查询语句中不包含 UNION 或者子查询EXPLAIN SELECT * FROM s1
PRIMARY包含 UNION、UNION ALL 或者子查询的查询，其中最左边的那个查询。EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
UNION包含 UNION、UNION ALL 或者子查询的查询，除最左边的那个以外的查询。同上
UNION RESULTMySQL 使用临时表来完成 UNION 的去重工作，该临时表的查询。同上
SUBQUERY不相关子查询，不能转为 semi-join，使用物化方案的子查询。EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = ‘a’;
DEPENDENT SUBQUERY相关子查询，不能转为 semi-join 的子查询。该类型的查询可能被会执行多次EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = ‘a’;
DEPENDENT UNION在包含 UNION 或者 UNION ALL 的大查询中，如果各个小查询都依赖于外层查询的话，除了最左边的那个小查询的查询。EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = ‘a’ UNION SELECT key1 FROM s1 WHERE key1 = ‘b’);
DERIVED采用物化方式执行的，包含派生表的查询。EXPLAIN SELECT * FROM (SELECT key1, count(*) as c FROM s1 GROUP BY key1) AS derived_s1 where c > 1;
MATERIALIZED包含子查询，并将子查询物化之后与外层查询进行连接的查询。EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2);
UNCACHEABLE SUBQUERY不常用，略
UNCACHEABLE UNION不常用，略
15.1.4 partitions

分区，略

15.1.5 type
type描述举例
system当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如 MyISAM、Memory 时CREATE TABLE t(i int) Engine=MyISAM;
INSERT INTO t VALUES(1);
EXPLAIN SELECT * FROM t;
const根据主键或唯一二级索引列与常数进行等值匹配时EXPLAIN SELECT * FROM s1 WHERE id = 5;
eq_ref连接查询时，被驱动表是通过主键或者唯一二级索引列进行等值匹配时EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
ref使用普通二级索引与常量进行等值匹配时EXPLAIN SELECT * FROM s1 WHERE key1 = ‘1’;
fulltext全文索引，略
ref_or_null使用普通二级索引进行等值匹配，但索引列的值可以是 NULL 时EXPLAIN SELECT * FROM s1 WHERE key1 = ‘a’ OR key1 IS NULL;
index_merge发生索引合并时EXPLAIN SELECT * FROM s1 WHERE key1 = ‘a’ OR key3 = ‘a’;
unique_subquery将 IN 转换为 EXISTS 并且子查询可以使用主键进行等值匹配时EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 where s1.key1 = s2.key1) OR key3 = ‘a’;
index_subquery与 unique_subquery 类似，只不过使用的是普通索引EXPLAIN SELECT * FROM s1 WHERE common_field IN (SELECT key3 FROM s2 where s1.key1 = s2.key1) OR key3 = ‘a’;
range使用索引获取某些区间范围的记录时EXPLAIN SELECT * FROM s1 WHERE key1 IN (‘a’, ‘b’, ‘c’);
EXPLAIN SELECT * FROM s1 WHERE key1 > ‘a’ AND key1 < ‘b’;
index使用索引覆盖，但需要扫描全部的索引记录时EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = ‘a’;
ALL全表扫描EXPLAIN SELECT * FROM s1
15.1.6 possible_keys 和 key

可能用到的索引和实际用到的索引。

15.1.7 key_len

表示使用某个索引执行查询时该索引记录的最大长度，它由三个部分构成：

  1. 对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值。比如字符集utf8，类型VARCHAR(100)，则占用 100 × 3 = 300 字节
  2. 如果该索引列可以存储 NULL 值，则 key_len 比不可以存储 NULL 值时多 1 个字节
  3. 对于变长字段，都会有 2 个字节的存储该变长列的实际长度

key_len 的主要作用是为了让我们区分某个使用联合索引的查询具体用了几个索引列

EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';

在这里插入图片描述

EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';

在这里插入图片描述

15.1.8 ref

当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是 const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery 之一时，ref 展示的就是与索引列作等值匹配的是什么。

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

在这里插入图片描述

15.1.9 rows

预计扫描的记录行数。

15.1.10 filtered
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';

在这里插入图片描述

从执行计划可以看出，查询优化器打算把 s1 当作驱动表， s2 当作被驱动表。驱动表的 rows 为 10141，filtered 列为 10.00，意味着驱动表的扇出值就是 10141 × 10.00% = 1014.1，说明还要对被驱动表执行大约 1014 次查询。

15.1.11 Extra
名称含义举例
No tables used当没有 FROM 子句时EXPLAIN SELECT 1;
Impossible WHERE当 WHERE 子句永远为 FALSE 时EXPLAIN SELECT * FROM s1 WHERE 1 != 1;
No matching min/max row当查询列表有 MIN 或 MAX 聚集函数，但是并没有符合 WHERE 子句条件的记录时EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = ‘abcdefg’;
Using index当可以使用索引覆盖时而不用回表时EXPLAIN SELECT key1 FROM s1 WHERE key1 = ‘a’;
Using index condition当有些搜索条件中虽然出现了索引列，但却不能使用索引而将要进行索引下推时EXPLAIN SELECT * FROM s1 WHERE key1 > ‘z’ AND key1 LIKE ‘%a’;
Using where使用全表扫描并且 WHERE 子句中有针对该表的条件或使用索引访问但 WHERE 子句中有除了该索引列以外的查询条件时EXPLAIN SELECT * FROM s1 WHERE common_field = ‘a’;
Using join buffer(Block Nested Loop)连接查询时，使用基于块的嵌套循环算法EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;
Not exists使用左外连接，如果 WHERE 子句中包含被驱动表中某个列等于 NULL 的条件，而那个列又不允许为 NULL 时EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;
Using intersect/union/sort union出现索引合并时EXPLAIN SELECT * FROM s1 WHERE key1 = ‘a’ AND key3 = ‘a’;
Zero limit当 LIMIT 子句的参数为 0 时EXPLAIN SELECT * FROM s1 LIMIT 0;
Using filesort在内存或磁盘上进行排序时EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;
Using temporary当使用临时表时，如 DISTINCT、GROUP BY、UNION 等EXPLAIN SELECT DISTINCT common_field FROM s1;
Start temporary, End temporary当 IN 子查询转换成 semi-join，并且使用 DuplicateWeedout 执行策略时EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key3 FROM s2 WHERE common_field = ‘a’);
LooseScan当 IN 子查询转换成 semi-join，并且使用 LooseScan执行策略时EXPLAIN SELECT * FROM s1 WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 >‘z’);
FirstMatch(tbl_name)当 IN 子查询转换成 semi-join，并且使用 FirstMatch(tbl_name)执行策略时EXPLAIN SELECT * FROM s1 WHERE common_field IN (SELECT key1 FROM s2 where s1.key3 = s2.key3);

索引条件下推：

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

其中key1 > 'z'可以使用索引，但是key1 LIKE '%a'却无法使用索引。先根据前者定位到具体记录，回表前先检测下该记录是否满足后者，满足才回表。这种方式可以大大减少回表的次数，称为索引条件下推（Index Condition Pushdown）

15.2 Json 格式的执行计划

EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE s1.common_field = 'a';

15.3 Extented EXPLAIN

使用 EXPLAIN 查看执行计划后，还可以使用 SHOW WARNINGS 来查看这个查询计划的一些扩展信息。

EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;SHOW WARNINGS;

15.4 总结

  1. 通过 EXPLAIN 语句可以查看某个语句的执行计划

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.xdnf.cn/news/150350.html

如若内容造成侵权/违法违规/事实不符，请联系一条长河网进行投诉反馈，一经查实，立即删除！

相关文章

新规2027年生效 美国禁止中国智能网联汽车软硬件

新规2027年生效 美国禁止中国智能网联汽车软硬件

当地时间9月23日&#xff0c;美国商务部工业和安全局&#xff08;BIS&#xff09;发布了一项拟议规则制定通知&#xff08;NPRM&#xff09;&#xff0c;该通知将禁止销售或进口集成特定硬件和软件的联网车辆&#xff0c;或单独销售这些组件&#xff0c;这些硬件和软件与中国或…
阅读更多...
Python 解析 html

Python 解析 html

一、场景分析 假设有如下 html 文档&#xff1a; 写一段 python 脚本&#xff0c;解析出里面的数据&#xff0c;包括经度维度。 <div classstorelist><ul><li lng"100.111111" lat"10.111111"><h4>联盟店1</h4><p>…
阅读更多...
Kubernetes 深入浅出系列 | 容器剖析之容器基本实现原理

Kubernetes 深入浅出系列 | 容器剖析之容器基本实现原理

一、容器基本实现原理 Docker 主要通过如下三个方面来实现容器化&#xff1a; ① 使用操作系统的 namespace 隔离系统资源技术&#xff0c;通过隔离 网络、PID 进程、系统信号量、文件系统挂载、主机名和域名&#xff0c;来实现在同一宿主机系统中&#xff0c;运行不同的容器&…
阅读更多...
yum库 docker的小白安装教程（附部分问题及其解决方案）

yum库 docker的小白安装教程（附部分问题及其解决方案）

yum库 首先我们安装yum 首先在控制台执行下列语句 首先切换到root用户&#xff0c;假如已经是了就不用打下面的语句 su root #使用国内的镜像&#xff0c;不执行直接安装yum是国外的&#xff0c;那个有问题 curl -o /etc/yum.repos.d/CentOS-Base.repo https://mirrors.al…
阅读更多...
打印规律图形

打印规律图形

思路：将主对角线的1,2,3,4看成一行，5,6,7看成一行，8,9看成一行，10看成一行。按照这种方法，遍历一遍，正好是1~10。 然后在遍历的过程中将这些数存放在数组中。 上图所示：1的坐标为（1,1），因为i=1,j=1时为1。因此： 对于第一行的数（i=1）: 1的坐标为（1,1），对应放在…
阅读更多...
低场核磁共振成像系统MRI的成像优势特点

低场核磁共振成像系统MRI的成像优势特点

低场核磁共振成像系统MRI的成像优势特点 精准医疗是未来医疗行业的发展趋势&#xff0c;而医疗技术的发展对于精准医疗的实现至关重要&#xff1b;低场MRI作为一种重要的医学影像技术&#xff0c;在精准医疗时代具有独特的优势。 一、定义与特点 低场MRI是指磁场强度低于高场M…
阅读更多...
防爆对讲机国产化，走对自主可控的必由之路！

防爆对讲机国产化，走对自主可控的必由之路！

在“危、急、特”复杂多变的作业场景中&#xff0c;防爆对讲机作为关键通信工具&#xff0c;其技术自主可控与安全可靠性成为不容忽视的迫切需求。长期以来&#xff0c;核心技术依赖进口的现状成为制约行业发展的瓶颈&#xff0c;面临安全隐患与成本压力的困境&#xff0c;为打…
阅读更多...
Unity开发绘画板——02.创建项目

Unity开发绘画板——02.创建项目

1.创建Unity工程 我们创建一个名为 DrawingBoard 的工程&#xff0c;然后先把必要的工程目录都创建一下&#xff1a; 主要包含了一下几个文件夹&#xff1a; Scripts &#xff1a;存放我们的代码文件 Scenes &#xff1a;工程默认会创建的&#xff0c;存放场景文件 Shaders &…
阅读更多...
（学习记录）使用HAL库 STM32CubeMX——GPIO引脚输入配置

（学习记录）使用HAL库 STM32CubeMX——GPIO引脚输入配置

STM32F103C8T6的GPIO引脚输入配置 时钟配置 &#xff08;学习记录&#xff09;使用 STM32CubeMX——配置时钟&#xff08;入门&#xff09;https://blog.csdn.net/Wang2869902214/article/details/142423522 GPIO 引脚输出配置 &#xff08;学习记录&#xff09;使用 STM32…
阅读更多...
货代选择海运系统时要注意什么问题？

货代选择海运系统时要注意什么问题？

在全球贸易蓬勃发展的当下&#xff0c;货代企业在物流链条中起着举足轻重的作用。而一个优质的海运系统&#xff0c;则是货代企业提升竞争力、实现高效运营的关键利器。那么&#xff0c;货代企业在选择海运系统时&#xff0c;需要注意哪些问题呢&#xff1f; 一、避免过于关注价…
阅读更多...
前端报错401 【已解决】

前端报错401 【已解决】

前端报错401 【已解决】 在前端开发中&#xff0c;HTTP状态码401&#xff08;Unauthorized&#xff09;是一个常见的错误&#xff0c;它表明用户试图访问受保护的资源&#xff0c;但未能提供有效的身份验证信息。这个错误不仅关乎用户体验&#xff0c;也直接关系到应用的安全性…
阅读更多...
什么软件可以录屏？5个软件帮助你轻松进行录屏操作

什么软件可以录屏？5个软件帮助你轻松进行录屏操作

什么软件可以录屏&#xff1f;5个软件帮助你轻松进行录屏操作 录屏软件是日常工作和学习中不可或缺的工具&#xff0c;广泛应用于制作教程、记录在线会议、游戏录制等场景。以下是五款功能强大且易于使用的录屏软件&#xff0c;能够帮助你轻松进行录屏操作。 迅捷屏幕录像工具…
阅读更多...
从手动测试菜鸟，到自动化测试老司机，实现自动化落地

从手动测试菜鸟，到自动化测试老司机，实现自动化落地

虽然许多伙伴是一个测试老人了&#xff0c;但是基本上所有的测试经验都停留在手工测试方面&#xff0c;对于自动化测试方面的实战经验少之又少。 其实&#xff0c;究其原因&#xff1a;一方面是&#xff0c;自动化方面不求上进&#xff0c;觉得会手工测试就可以了&#xff0c;自…
阅读更多...
降准到底是什么？

降准到底是什么？

王炸&#xff01;宣布10000亿利好&#xff01;跟我有啥关系&#xff1f; 刚刚宣布&#xff01;10000亿元降准 降准到底是什么&#xff1f;为何央行近年来持续在降准&#xff1f;银行是如何创造流通货币的&#xff1f;降准对股市、楼市、债市、汇市、普通老百姓意味着什么&#…
阅读更多...
相亲交友网站为不同年龄层提供的服务差异

相亲交友网站为不同年龄层提供的服务差异

随着互联网技术的飞速发展&#xff0c;相亲交友网站已经成为现代人寻找伴侣的重要途径之一。无论是年轻人还是中老年人&#xff0c;都可以通过相亲交友网站找到自己的另一半。然而&#xff0c;不同年龄层的人在使用相亲交友网站时的需求和服务体验上存在显著差异。本文将探讨这…
阅读更多...
深入解析SGD、Momentum与Nesterov：优化算法的对比与应用

深入解析SGD、Momentum与Nesterov：优化算法的对比与应用

目录 1. 梯度下降算法2. BGD、SGD、MBGD3. momentum与dampening3.1 另一种形式的momentum3.1.1 学习率固定3.1.2 学习率不固定 4. nesterov4.1 PyTorch中的Nesterov4.2 Polyak与Nesterov的比较 Ref 1. 梯度下降算法 先考虑一元情形。假设待更新的参数为 θ \theta θ&#xf…
阅读更多...
CSP-S 2024 提高级 第一轮（初赛） 阅读程序（1）

CSP-S 2024 提高级 第一轮（初赛） 阅读程序（1）

【题目】 CSP-S 2024 提高级 第一轮&#xff08;初赛&#xff09; 阅读程序&#xff08;1&#xff09; 1 #include <iostream> 2 using namespace std; 3 4 const int N 1000; 5 int c[N]; 6 7 int logic(int x, int y) { 8 return (x & y) ^ ((x ^ y)…
阅读更多...
常见区块链数据模型介绍

常见区块链数据模型介绍

除了加密技术和共识算法&#xff0c;区块链技术还依赖于一种数据模型&#xff0c;它决定了信息如何被结构化、验证和存储。数据模型定义了账户如何管理&#xff0c;状态转换如何发生&#xff0c;以及用户和开发者如何与系统交互。 在区块链技术的短暂历史中&#xff0c;数据…
阅读更多...
鸿蒙OpenHarmony【小型系统基础内核(进程管理调度器)】子系统开发

鸿蒙OpenHarmony【小型系统基础内核(进程管理调度器)】子系统开发

调度器 基本概念 OpenHarmony LiteOS-A内核采用了高优先级优先 同优先级时间片轮转的抢占式调度机制&#xff0c;系统从启动开始基于real time的时间轴向前运行&#xff0c;使得该调度算法具有很好的实时性。 OpenHarmony 的调度算法将 tickless 机制天然嵌入到调度算法中&…
阅读更多...
分区与分桶

分区与分桶

分区 分区字段大小写&#xff1a; 在hive中&#xff0c;分区字段名是不区分大小写的&#xff0c;不过字段值是区分大小写的。我们可以来测试一下 导入数据 load data local inpath /home/hivedata/user1.txt into table part4 partition(year2018,month03,DAy21); load data …
阅读更多...
最新文章

Copyright @ 2022~2023