MySQL Explain 字段详解

Explain 工具介绍

Explain 一般被称为解释器，通过 Explain 工具，我们能分析我们使用的查询语句或是结构的性能瓶颈，它提供 MySQL 如何执行语句的信息。

使用语法：

explain [extended|partition] select

在 select 关键字前加 explain 关键字，MySQL 会返回该查询的执行计划不是执行这条 SQL。

explain 分析示例

创建表语句

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`(
`id` INT(11) NOT NULL,
`name` VARCHAR(45) DEFAULT NULL,
`gender` CHAR(1) DEFAULT 0,
PRIMARY KEY(`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;INSERT INTO `user` (`id`, `name`,`gender`) VALUES(1,'a',0),(2,'b',1),(3,'c',0);DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`(
`id` INT(11) NOT NULL,
`name` VARCHAR(45) DEFAULT NULL,
`subject` VARCHAR(20) DEFAULT 0,
PRIMARY KEY(`id`),
KEY `idx_name` (`name`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;INSERT INTO `teacher` (`id`, `name`,`subject`) VALUES(1,'t1',"语文"),(2,'t2',"数学"),(3,'t3',"体育");DROP TABLE IF EXISTS `teacher_user`;
CREATE TABLE `teacher_user`(
`id` INT(11) NOT NULL,
`teacher_id` INT(11) NOT NULL,
`user_id` INT(11) NOT NULL,
PRIMARY KEY(`id`),
KEY `idx_teacher_user_id` (`teacher_id`,`user_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;INSERT INTO `teacher_user` (`id`, `teacher_id`,`user_id`) VALUES(1,1,1),(2,3,2),(3,1,2);

使用 explain 工具

EXPLAIN SELECT * FROM USER;

简单使用 explain 工具

Explain 的两个变种

  1. Explain extended：会在explain的基础上额外提供一些查询优化的信息。紧随其后通过show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外的还有 filtered 列，它显示的是一个百分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的 id 值比当前表 id 值小的表）。
  2. explain partitions：显示查询会访问的分区，输出中增加 partitions 字段。

注意：如果你用的是 MySQL 5.6 ，这两个命令可以用，MySQL 8已经不支持了，使用会报错，不过他们将这两个字段的输出已经整合在默认的 explain 中了，直接后接show warnings就可以看到重构后的命令了。

EXPLAIN SELECT * FROM USER WHERE id=1;
SHOW WARNINGS;

在这里插入图片描述

在这里插入图片描述

Explain 字段详解

列含义
idselect 的序列号，有几个 select 就有几个id，id越大，越先执行
select_type表示语句是简单查询还是复杂查询，该列的值有：simple、primairy、subquery、derived、union
table显示 explain 语句正在访问的表
partitions如果查询是基于分区表的话，会显示查询将要访问的分区
type关联的类型，也就是 MySQL 决定如何查找表中的行，查找数据行记录的大概范围
possible_keys可能使用的索引
keyMySQL 实际采用了哪个索引来优化对该表的访问
key_len索引里使用的字节数，通过该列的值可以算出具体使用了索引的哪些列
ref在 key 列记录的索引中，表查找值所用到的列或常量
rowsMySQL预估要读取并检测的行数，不是结果集中的行数
filtered百分比的值，根据 rows*filtered/100 可以估算出将要和 explain中前一个表进行连接的行数
Extra显示额外的信息

详细描述每一列的含义

id 列

id 列的值显示的是语句的执行顺序，id 列越大执行优先级越高，id 相同则从上往下执行，id 为 NULL 最后执行。

select_type 列

  1. simple：简单查询。查询不包含子查询和 union。
    EXPLAIN SELECT * FROM USER WHERE id=1;在这里插入图片描述
  2. primary：复杂查询中最外层的 select。
  3. subquery：包含在 select 中的子查询（不在 from 子句中）。
  4. derived：包含在 from 子句中的子查询。MySLQL 会将结果放在一个临时表中， 也称为派生表。
SET SESSION optimizer_switch='derived_merge=off'; 关闭对衍生表的合并
EXPLAIN SELECT (SELECT 1 FROM teacher WHERE id = 1) FROM (SELECT * FROM USER WHERE id = 1) der;

在这里插入图片描述

  1. union：在 union 中的第二个和以后的查询会被标为 union 类型。
  2. union result：从 union 构建的临时表检索结果的查询类型。
EXPLAIN SELECT id FROM teacher UNION SELECT id FROM teacher;

在这里插入图片描述

table 列

这列表示 explain 的一行正在访问哪个表，当 from 子句中有子查询时，table 列是 <derivenN> 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。
当有 union 时，UNION RESULT 的 table 列的值为<union1,2>,1 和 2 表示参与 union 的 select 行 id。

partitions 列

如果查询的是基于分区的表，该字段显示查询将会访问的分区。

type 列

这一列表示关联类型或访问类型，即 MySQL 决定如何查找表中的行，查找数据记录的大概范围。
最优到最差：

system > const > eq_ref > ref > range > index > all

一般来讲，保证查询达到 range 级别，最好达到 ref
NULL：MySQL 能在优化阶段分解查询语句，在执行阶段不需访问表或索引。比如：

EXPLAIN SELECT MAX(id) FROM teacher

在这里插入图片描述
system：该表只有一行，是 const 的特例。
const：该表最多有一个匹配的行，MySQL 能对查询的某部分进行优化并将其转化成一个常量，因为只有一个匹配的行，所以速度非常快。

EXPLAIN SELECT * FROM (SELECT * FROM teacher WHERE id=1) tmp;

在这里插入图片描述
eq_ref：primary key 或 unique key 索引的所有部分被连接使用，最多只会返回一条符合条件的记录。

EXPLAIN SELECT * FROM teacher INNER JOIN teacher_user WHERE teacher.`id`=teacher_user.`teacher_id`;

在这里插入图片描述
ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值比较，可能会得到多个结果。

简单查询，name 是普通索引（非唯一索引）

EXPLAIN SELECT * FROM teacher WHERE NAME='t1';

在这里插入图片描述
关联表查询，idx_teacher_user_id 是 teacher_id 和 user_id 的联合索引，使用左边前缀 teacher_id 进行查询。

EXPLAIN SELECT teacher_id FROM teacher LEFT JOIN teacher_user ON teacher.id=teacher_user.teacher_id;

在这里插入图片描述
range：范围扫描通常出现在 in，between，>，<，>=等操作中，使用一个索引来检查给定范围的行。

EXPLAIN SELECT * FROM teacher WHERE id > 1;

在这里插入图片描述
index：扫描全索引就能拿到结果，一般是扫描某个二级索引，对二级索引的叶子节点进行遍历和扫描，所以速度较慢，而二级索引一般比较小，所以比 ALL 快。

EXPLAIN SELECT NAME FROM teacher;

在这里插入图片描述
ALL：全表扫描，扫描聚簇索引的所有叶子节点，通常这种情况需要增加索引进行优化。

EXPLAIN SELECT * FROM teacher;

在这里插入图片描述

possible_keys 列

这列显示的是此次查询可能用到的索引，一个或者多个，有时显示的是 NULL 值，是因为 MySQL 判断表中数据不多，不需要使用索引查询，选择全表查询。

如果该列是NULL，则没用相关索引。这种情况下，可以通过检查 where 子句看是否可以建立一个合适的索引来提高查询性能，再看 explain 的情况。覆盖索引查询的情况该列也为 NULL，但依然进行索引查询。

EXPLAIN SELECT NAME FROM teacher;

在这里插入图片描述

key 列

显示的是 MySQL 实际使用的索引。如果没有使用索引，则该列是 NULL，如果强制 MySQL 使用或忽视 possible_keys 列中的索引，在查询中使用 force index、ignore index。

EXPLAIN SELECT NAME FROM teacher IGNORE INDEX(idx_name);

在这里插入图片描述

key_len 列

这一列显示了 MySQL 在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。它显示的是索引的最大长度，而不是实际使用长度。

EXPLAIN SELECT * FROM teacher_user WHERE teacher_id=1;

在这里插入图片描述
key_len 计算规则如下：

  • 字符串，char(n) 和 varchar(n)，n 代表字符数，不是字节数，utf-8 的一个字母或数字占用 1 个字节，一个汉字占用 3 个字节
    • char(n)：存汉字，长度为 3n 字节
    • varchar(n)：存储汉字，长度为 3n + 2 字节，多的 2 字节用来存储字符串长度
  • 数值类型
    • tinyint：1 字节
    • smallint：2 字节
    • int：4 字节
    • bigint：8 字节
  • 时间类型
    • date：3 字节
    • timestamp：4 字节
    • datetime：8 字节
  • 如果字段允许为 NULL，需要 1 字节存储 NULL

ref 列

这一列显示了在 key 列记录的索引中，表查找值所用到的列或常量，常见的有 const（常量），字段名（例：teacher.id）

rows 列

这一列是 MySQOL 预估要读取并检测的行数，注意这个不是结果集里的行数。

filtered 列

该列是一个百分比的值，根据 rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数。

Extra 列

常见的值如下：

  1. Using index：使用覆盖索引
EXPLAIN SELECT teacher_id FROM teacher_user WHERE teacher_id=1;

在这里插入图片描述
使用了联合索引 idx_teacher_user_id，覆盖索引查询索引覆盖的列，extra 列中显示 Using index。

  1. Using where：使用 where 语句来处理结果并且查询的列没被索引覆盖。
EXPLAIN SELECT * FROM teacher WHERE SUBJECT='语文';

在这里插入图片描述

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

  2. Using temporary：MySQL 需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。

EXPLAIN SELECT DISTINCT SUBJECT FROM teacher;

在这里插入图片描述

  1. Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是需要考虑索引进行优化。
  2. Select tables optimized away：使用某些聚合函数来访问存在索引的某个字段。

总结

本文在结合查询例子的基础上对 Explain 工具查询的列进行讲解，很多内容都涉及到了，写到这也就差不多了，想要更详细的学习 Explain，可以去官网链接: MySQL 8 参考手册查看更详细的解释。

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

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

【大数据】Flink学习笔记

【大数据】Flink学习笔记

文章目录 认识FlinkDocker安装Flink基本概念Flink的特点Flink 和 Spark Streaming 对比 基本使用WordCount实现依赖 批模式代码流模式代码网络流模式代码在web UI上提交代码创建项目[^1]编写代码配置打包在Web UI上提交 Flink 架构系统架构核心概念并行度算子链(Opeartor Chain…
阅读更多...
基于java+springboot+vue实现的电商个性化推荐系统（文末源码+Lw+ppt）23-389

基于java+springboot+vue实现的电商个性化推荐系统（文末源码+Lw+ppt）23-389

摘 要 伴随着我国社会的发展&#xff0c;人民生活质量日益提高。于是对电商个性化推荐进行规范而严格是十分有必要的&#xff0c;所以许许多多的信息管理系统应运而生。此时单靠人力应对这些事务就显得有些力不从心了。所以本论文将设计一套电商个性化推荐系统&#xff0c;帮…
阅读更多...
bert 适合 embedding 的模型

bert 适合 embedding 的模型

目录 背景 embedding 求最相似的 topk 结果查看 背景 想要求两个文本的相似度&#xff0c;就单纯相似度&#xff0c;不要语义相似度&#xff0c;直接使用 bert 先 embedding 然后找出相似的文本&#xff0c;效果都不太好&#xff0c;试过 bert-base-chinese&#xff0c;be…
阅读更多...
pdf在浏览器上无法正常加载的问题

pdf在浏览器上无法正常加载的问题

一、背景 觉得很有意思给大家分享一下。事情是这样的&#xff0c;开发给我反馈说&#xff0c;线上环境接口请求展示pdf异常&#xff0c;此时碰巧我前不久正好在ingress前加了一层nginx&#xff0c;恰逢此时内心五谷杂陈&#xff0c;思路第一时间便放在了改动项。捣鼓了好久无果…
阅读更多...
vue中使用图片url直接下载图片

vue中使用图片url直接下载图片

vue中使用图片url直接下载图片 // 下载图片downloadByBlob(url, name) {let image new Image()image.setAttribute(crossOrigin, anonymous)image.src urlimage.onload () > {let canvas document.createElement(canvas)canvas.width image.widthcanvas.height image…
阅读更多...
Ubuntu20.04下PCL安装，查看，卸载等操作

Ubuntu20.04下PCL安装，查看，卸载等操作

Ubuntu20.04下PCL安装&#xff0c;查看&#xff0c;卸载等操作 项目来源 https://github.com/PointCloudLibrary/pclhttps://pointclouds.org/documentation/modules.htmlhttps://pcl.readthedocs.io/projects/tutorials/en/master/ 点云学习&#xff1a; https://github.c…
阅读更多...
【Spring】SpringMvc项目当中，页面删除最后一条数据，页面不跳转并且数据为空。

【Spring】SpringMvc项目当中，页面删除最后一条数据，页面不跳转并且数据为空。

期待您的关注 在之前学习SpringMvc的时候遇到过这样一个BUG&#xff0c;当我在一个页面删除该页面的最后一条数据的时候&#xff0c;一旦我删除成功&#xff0c;那么这个页面不会进行跳转&#xff0c;而是还停留在这个本不应该存在的页面&#xff0c;而且数据什么都没有。如下…
阅读更多...
零基础教程：R语言lavaan结构方程模型（SEM）

零基础教程：R语言lavaan结构方程模型（SEM）

查看原文>>>最新基于R语言lavaan结构方程模型&#xff08;SEM&#xff09;实践技术应用 基于R语言lavaan程序包&#xff0c;通过理论讲解和实际操作相结合的方式&#xff0c;由浅入深地系统介绍结构方程模型的建立、拟合、评估、筛选和结果展示的全过程。我们筛选大量…
阅读更多...
ChatGPT与Discord的完美结合——团队协作的得力助手

ChatGPT与Discord的完美结合——团队协作的得力助手

本文将教你如何集成Discord Bot&#xff0c;助力团队在工作中实现更高效的沟通与协作。通过充分发挥ChatGPT的潜力&#xff0c;进一步提升工作效率和团队协作能力。无需编写任何代码即可完成本文所述的操作&#xff0c;进行个性化定制只需对参数进行微调即可。 方案介绍 如果在…
阅读更多...
【JavaWeb】Day27.Web入门——Tomcat介绍

【JavaWeb】Day27.Web入门——Tomcat介绍

目录 WEB服务器-Tomcat 一.服务器概述 二.Web服务器 三.Tomcat- 基本使用 1.下载 2.安装与卸载 3.启动与关闭 4.常见问题 四.Tomcat- 入门程序 WEB服务器-Tomcat 一.服务器概述 服务器硬件&#xff1a;指的也是计算机&#xff0c;只不过服务器要比我们日常使用的计算…
阅读更多...
AI绘画核心技术与实战【课程推荐】

AI绘画核心技术与实战【课程推荐】

AI结合绘画领域属于是《黑客与画家》的结合了&#xff0c;推荐大家来一起学习
阅读更多...
Linux：程序地址空间详解

Linux：程序地址空间详解

目录 一、堆、栈、环境参数所在位置 二、进程地址空间底层实现原理 ​编辑 三、什么是地址空间 四、为什么要有进程地址空间 五、细谈写实拷贝的实现及意义 在C/C学习中&#xff0c;都学习过如上图所示的一套存储结构&#xff0c;我们大致知道一般存储空间分为堆区&#…
阅读更多...
代码随想录算法训练营三刷 day38 | 动态规划之 509. 斐波那契数 70. 爬楼梯 746. 使用最小花费爬楼梯

代码随想录算法训练营三刷 day38 | 动态规划之 509. 斐波那契数 70. 爬楼梯 746. 使用最小花费爬楼梯

三刷day38 509. 斐波那契数1 确定dp数组以及下标的含义2 确定递推公式3 dp数组如何初始化4 确定遍历顺序5 举例推导dp数组 70. 爬楼梯1 确定dp数组以及下标的含义2 确定递推公式3 dp数组如何初始化4 确定遍历顺序5 举例推导dp数组 746. 使用最小花费爬楼梯1 确定dp数组以及下标…
阅读更多...
[C++初阶] 爱上C++ : 与C++的第一次约会

[C++初阶] 爱上C++ : 与C++的第一次约会

&#x1f525;个人主页&#xff1a;guoguoqiang &#x1f525;专栏&#xff1a;我与C的爱恋 本篇内容带大家浅浅的了解一下C中的命名空间。 在c中&#xff0c;名称&#xff08;name&#xff09;可以是符号常量、变量、函数、结构、枚举、类和对象等等。工程越大&#xff0c;名称…
阅读更多...
C++进阶，手把手带你学继承

C++进阶，手把手带你学继承

&#x1fa90;&#x1fa90;&#x1fa90;欢迎来到程序员餐厅&#x1f4ab;&#x1f4ab;&#x1f4ab; 主厨&#xff1a;邪王真眼 主厨的主页&#xff1a;Chef‘s blog 所属专栏&#xff1a;c大冒险 总有光环在陨落&#xff0c;总有新星在闪烁 【本节目标】 1.继…
阅读更多...
【讲解下go和java的区别】

【讲解下go和java的区别】

&#x1f525;博主&#xff1a;程序员不想YY啊&#x1f525; &#x1f4ab;CSDN优质创作者&#xff0c;CSDN实力新星&#xff0c;CSDN博客专家&#x1f4ab; &#x1f917;点赞&#x1f388;收藏⭐再看&#x1f4ab;养成习惯 &#x1f308;希望本文对您有所裨益&#xff0c;如有…
阅读更多...
Windows安装tomcat，以服务的方式管理，如何设置虚拟内存

Windows安装tomcat，以服务的方式管理，如何设置虚拟内存

之前工作中&#xff0c;部署tomcat都是使用Linux服务器&#xff0c;最近遇到个客户&#xff0c;提供的服务器是Windows server&#xff0c;并且需要通过服务的方式管理tomcat&#xff1b;以自己多年的码农经验&#xff0c;感觉应该没有问题&#xff0c;结果啪啪打脸了&#xf…
阅读更多...
python安装删除以及pip的使用

python安装删除以及pip的使用

目录 你无法想象新手到底会在什么地方出问题——十二个小时的血泪之言&#xff01; 问题引入 python modify setup 隐藏文件夹 环境变量的配置 彻底删除python 其他零碎发现 管理员终端 删不掉的windous应用商店apps 发现问题 总结 你无法想象新手到底会在什么地方…
阅读更多...
银河麒麟服务器操作系统安装SQLite数据库

银河麒麟服务器操作系统安装SQLite数据库

SQLite&#xff0c;是一款轻型的数据库&#xff0c;是遵守ACID的关系型数据库管理系统&#xff0c;它包含在一个相对小的C库中。它是D.RichardHipp建立的公有领域项目。它的设计目标是嵌入式的&#xff0c;而且已经在很多嵌入式产品中使用了它&#xff0c;它占用资源非常的低&a…
阅读更多...
服务器配置入门教程

服务器配置入门教程

问题环境&#xff1a; 现场调试的时候遇到很多离奇的问题&#xff0c;部分设备已经老到需要使用清华同方 Windows XP 系统的接口&#xff0c;所以写下这边记录&#xff0c;本文主要是基础教程。 快速入门常识 服务器基础知识_mezz卡-CSDN博客 基本接口识别 IOIOI-RJ45串口&a…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023