当前位置: 首页 > news >正文

EXPLAIN概述与字段剖析

 6. 分析查询语句:EXPLAIN(重点)

6.1 概述

定位了查询慢的sQL之后,我们就可以使用EXPLAIN或DESCRIBE 工具做针对性的分析查询语句。DESCRIBE语句的使用方法与EXPLAIN语句是一样的,并且分析结果也是一样的。
MySQL中有专门负责优化SELECT语句的优化器模块,主要功能:通过计算分析系统中收集到的统计信息,为客户端请求的Query提供它认为最优的
执行计划(他认为最优的数据检索方式,但不见得是DBA认为是最优的,这部分最耗费时间)。
这个执行计划展示了接下来具体执行查询的方式,比如多表连接的顺序是什么,对于每个表采用什么访问方法来具体执行查询等等。MySQL为我们提供了
EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划,大家看懂EXPLAIN语句的各个输出项,可以有针对性的提升我们查询语句的性能。

1.能做什么?

●表的读取顺序
●数据读取操作的操作类型

●哪些索引可以使用
●哪些索引被实际使用

●表之间的引用
●每张表有多少行被优化器查询
 

2. 版本情况

1. MySQL 5.6.3以前只能 EXPLAIN SELECT ;MYSQL 5.6.3以后就可以 EXPLAIN SELECT,UPDATE,DELETE

2. 在5.7以前的版本中,想要显示 partitions 需要使用 explain partitions 命令;想要显示

filtered 需要使用 explain extended 命令。在5.7版本后,默认explain直接显示partitions和

filtered中的信息。

6.2 基本语法

如果我们想看看某个查询的执行计划的话,可以在具体的查询语句前边加一个 EXPLAIN ,就像这样:

mysql> EXPLAIN SELECT 1;

EXPLAIN 语句输出的各个列的作用如下:

6.3 数据准备

1. 建表

表s1首先将id设为主键,  对key1 , key3 建立普通索引, 对key2建立唯一索引, 对key_part1, key_part2, key_part3按顺序建立联合索引

普通字段 common_field

CREATE TABLE s1 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;CREATE TABLE s2 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

2. 设置参数 log_bin_trust_function_creators  

创建函数,假如报错,需开启如下命令:允许创建函数设置:

set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。

 3. 创建函数

DELIMITER //
CREATE FUNCTION rand_string1(n INT)RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGINDECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';DECLARE return_str VARCHAR(255) DEFAULT '';DECLARE i INT DEFAULT 0;WHILE i < n DOSET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));SET i = i + 1;END WHILE;RETURN return_str;
END //
DELIMITER ;

 4. 创建存储过程

创建往s1表中插入数据的存储过程:

DELIMITER //
CREATE PROCEDURE insert_s1 (IN min_num INT (10),IN max_num INT (10))
BEGINDECLARE i INT DEFAULT 0;SET autocommit = 0;REPEATSET i = i + 1;INSERT INTO s1 VALUES((min_num + i),rand_string1(6),(min_num + 30 * i + 5),rand_string1(6),rand_string1(10),rand_string1(5),rand_string1(10),rand_string1(10));UNTIL i = max_numEND REPEAT;COMMIT;
END //
DELIMITER ;

创建往s2表中插入数据的存储过程:

DELIMITER //
CREATE PROCEDURE insert_s2 (IN min_num INT (10),IN max_num INT (10))
BEGINDECLARE i INT DEFAULT 0;SET autocommit = 0;REPEATSET i = i + 1;INSERT INTO s2 VALUES((min_num + i),rand_string1(6),(min_num + 30 * i + 5),rand_string1(6),rand_string1(10),rand_string1(5),rand_string1(10),rand_string1(10));UNTIL i = max_numEND REPEAT;COMMIT;
END //
DELIMITER ;

5. 调用存储过程

s1表数据的添加:加入1万条记录:

CALL insert_s1(10001,10000);

s2表数据的添加:加入1万条记录:

CALL insert_s2(10001,10000);

6.4 EXPLAIN各列作用

为了让大家有比较好的体验,我们调整了下 EXPLAIN 输出列的顺序。

1. table

不论我们的查询语句有多复杂,里边儿 包含了多少个表 ,到最后也是需要对每个表进行 单表访问 的,所 以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法,该条记录的table列代表着该表的表名(有时不是真实的表名字,可能是简称)。

2. id

我们写的查询语句一般都以 SELECT 关键字开头,比较简单的查询语句里只有一个 SELECT 关键字 (大多数情况下:出现几个select就有几个id)

特殊情况:

1. 查询优化器可能对涉及子查询的查询语句进行重写, 转变为多表查询的操作:

 2.  Union 去重,  需要创建临时表(第3行), 再在其中去重

 Union All 不需要去重 所以不创建临时表

小结: 

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

在所有组中,id值越大,优先级越高,越先执行

关注点:id号每个号码,表示一趟独立的查询, 一个sql的查询趟数越少越好

  3. select_type

 一条大的查询语句里边可以包含若干个SELECT关键字,每个SELECT关键字代表着一个小的查询语句,而每个SELECT关键字的FROM子句中都可以包含若干张表(这些表用来做连接查询),每一张表都对应着执行计划输出中的一条记录,对于在同一个SELECT关键字中的表来说,它们的id值是相同的。
MySQL为每一个SELECT关键字代表的小查询都定义了一个称之为select_type的属性,意思是我们只要知道了某个小查询的select_type属性,就知道了这个小查询在整个大查询中扮演了一个什么角色,我们看一下select_type都能取哪些值,请看官方文档:

SIMPLE:

 查询语句不包含'UNION'或者子查询的查询都算是'SIMPLE'类型

 PRIMARY:

对于包含'UNION'或者'UNION ALL' 的大查询来说,, 他是有几个小查询组成的, 其中最左边select_typee'值就是'PRIMARY'

UNION

对于包含'UNION' 或者'UNION ALL'的大查询来说, 除了最左边的查询外, 其余的小查询的'select_type' 值就是'UNION'

UNION RESULT

MysQL 选择使用临时表来完成'UNION'查询的去重工作, 针对该临时表的查询的select_type就是UNION RESULT

mysql> EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

SUBQUERY

包含子查询的查询语句不能转为对应的semi-join 形式, 并且该子查询是不相关子查询,则该子查询的第一个select关键字代表的那个查询select_type 就是SUBQUERY

包含子查询的查询语句不能转为对应的semi-join 形式, 并且该子查询是相关子查询,则该子查询的第一个select关键字代表的那个查询select_type 就是DEPENDENT SUBQUERY 

 DEPENDENT UNION

在包含UINON或者UNION ALL的大查询中, 如果各个小查询都是依赖外层查询的话, 那除了最左边的那个小查询外, 其余的小查询select_type 都是 DEPENDENT UNION

DERIVED  

派生表

MATERIALIZED

4.  type

针对单表的访问方法
完整的访问方法如下: system const eq_ref ref fulltext ref_or_null
index_merge unique_subquery index_subquery range index ALL
system:
当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的, 比如MyISAM, Memory,那么对该表的访问方法就是system
mysql> CREATE TABLE t(i int) Engine=MyISAM;
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO t VALUES(1);
Query OK, 1 row affected (0.01 sec)mysql> EXPLAIN SELECT * FROM t;

const
当我们根据主键或者一二级索引列与常数进行等值匹配时, 对单表的访问方法就是const
mysql> EXPLAIN SELECT * FROM s1 WHERE id = 10005 ;

 eq_ref

在连接查询时, 如果被驱动表是通过主键或者唯一二级索引等值匹配的方式进行访问的

(如果该主键或者唯一二级索引是联合索引的话, 所有的索引列都必须进行等值比较), 则对该被驱动表的访问方法就是eq_ref

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

 从执行计划的结果中可以看出,MySQL打算将s2作为驱动表,s1作为被驱动表,重点关注s1的访问方法是 eq_ref ,表明在访问s1表的时候可以 通过主键的等值匹配 来进行访问。

ref
当通过普通的二级索引列与常量进行等值匹配时
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' ;

range

使用索引获取某些范围区间的记录 

index

mysql> EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a' ;

当我们可以使用索引覆盖

mysql> EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';

小结:
结果值从最好到最坏依次是: system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
其中比较重要的几个提取出来(见上图中的蓝 色)。 SQL 性能优化的目标:至少要达到 range 级别,要求是 ref 级别,最好是 consts 级别。(阿里巴巴 开发手册要求)

5. possible_keyskey

 可能用到的索引和 实际上使用的索引

 6. key_len

实际使用到的索引长度(即:字节数)  

可以看到606 会比303 更好

utf8一个字符占3个字节 

303 = 允许长度100 * 3 + 一个字节(null) + 两个字节变长字段

7. Extra

太多了 不抄了 见书260

相关文章:

EXPLAIN概述与字段剖析

6. 分析查询语句&#xff1a;EXPLAIN(重点) 6.1 概述 定位了查询慢的sQL之后&#xff0c;我们就可以使用EXPLAIN或DESCRIBE 工具做针对性的分析查询语句。DESCRIBE语句的使用方法与EXPLAIN语句是一样的&#xff0c;并且分析结果也是一样的。 MySQL中有专门负责优化SELECT语句…...

基于Java IO 序列化方案的memcached-session-manager多memcached节点配置

session的序列化方案官方推荐的有4种 java serializationmsm-kryo-serializermsm-javolution-serializermsm-xstream-serializer 关于这几种&#xff0c;官方也给出了比较: Java serialization is very robust and a proven technology. The biggest disadvantage IMHO is th…...

LinkedList(3):并发异常

1 LinkedList并发异常 package com.example.demo;import java.util.Iterator; import java.util.LinkedList;public class TestLinkedList {public static void main(String[] args) {LinkedList linkedList new LinkedList(); //双向链表linkedList.add(11);linkedList.add(…...

vue里el-form+el-table实现验证规则的写法

vue里el-formel-table实现验证规则的写法 vue里el-formel-table实现验证规则的写法 vue里el-formel-table实现验证规则的写法 重点是因为使用el-form el-table与单独使用el-form时数据不同&#xff0c;前者是对象json数组&#xff0c;后者是对象&#xff0c;导致了el-form-ite…...

K8S 基础概念学习

1.K8S 通过Deployment 实现滚动发布&#xff0c;比如左边的ReplicatSet 的 pod 中 是V1版本的镜像&#xff0c;Deployment通过 再启动一个 ReplicatSet 中启动 pod中 镜像就是V2 2.每个pod 中都有一个pause 容器&#xff0c;他会连接本pod中的其他容器&#xff0c;实现互通。p…...

Java之正则表达式的详细解析

正则表达式 1.1 正则表达式的概念及演示 在Java中&#xff0c;我们经常需要验证一些字符串&#xff0c;例如&#xff1a;年龄必须是2位的数字、用户名必须是8位长度而且只能包含大小写字母、数字等。正则表达式就是用来验证各种字符串的规则。它内部描述了一些规则&#xff0c…...

移动端的屏幕分辨率与浏览器的视口宽度(视口大小)是两回事儿

问&#xff1a;在移动端的Web设计中&#xff0c;屏幕的分辨率和视口大小是不是是两回事儿&#xff1f; 答&#xff1a; 是的&#xff0c;屏幕的分辨率和视口大小在移动端的Web设计中是两个不同的概念。 屏幕分辨率&#xff08;Screen Resolution&#xff09;&#xff1a;这指的…...

分布式 - 服务器Nginx:基础系列之Nginx静态资源优化配置指令sendfile | tcp_nopush | tcp_nodelay

文章目录 1. sendfile 指令2. tcp_nopush 指令3. tcp_nodelay 指令 1. sendfile 指令 请求静态资源的过程&#xff1a;客户端通过网络接口向服务端发送请求&#xff0c;操作系统将这些客户端的请求传递给服务器端应用程序&#xff0c;服务器端应用程序会处理这些请求&#xff…...

Sentinel配置的blockHandler方法不生效

①首先配置流控的资源名跟SentinelResource中的Value配置的一定要一直且唯一 ②其次blockhandler后面的方法一定要跟下面指定的方法名称是一样的 ③也就是我犯下的错误&#xff0c;一定要注意是上面那个才是Sentinel的&#xff0c;下面的是sun公司的…我说呢&#xff0c;一直…...

Mybatis的三种映射关系以及联表查询

目录 一、概念 二、一对一 1、配置generatorConfig.xml 2、Vo包的编写 3、xml的sql编写 4、编写对应接口及实现类 5、测试 三、一对多 1、Vo包类的编写 2、xml的sql编写 3、编写对应接口及实现类 4、测试 四、多对多 1、Vo类 2、xml的sql配置 3、接口及接口实现…...

基于串口校时的数字钟设计

文章目录 设计目标硬件设计数码管串口 软件设计顶层模块串口接收模块数据处理模块时钟模块串口发送模块 总结 设计目标 环境&#xff1a;ACX720开发板 实现功能&#xff1a; 数码管能够显示时分秒能够接收串口数据修改时间能够将当前时间以1s一次速率发送到电脑 硬件设计 数…...

支持向量机(二)

文章目录 前言具体内容 前言 总算要对稍微有点难度的地方动手了&#xff0c;前面介绍的线性可分或者线性不可分的情况&#xff0c;都是使用平面作为分割面的&#xff0c;现在我们采用另一种分割面的设计方法&#xff0c;也就是核方法。 核方法涉及的分割面不再是 w x b 0 wx…...

Arrays.asList 和 null 类型

一、Arrays.asList 类型简析 Arrays.asList() 返回的List 是它的内部类&#xff0c;不能使用 retainAll() 取交集&#xff0c;导致元素的删除&#xff0c;会报错。 List<String> list Arrays.asList(value.split(",")); 替换为> List<String> list…...

《论文阅读》用提示和释义模拟对话情绪识别的思维过程 IJCAI 2023

《论文阅读》用提示和复述模拟对话情绪识别的思维过程 IJCAI 2023 前言简介相关知识prompt engineeringparaphrasing模型架构第一阶段第二阶段History-oriented promptExperience-oriented Prompt ConstructionLabel Paraphrasing损失函数前言 你是否也对于理解论文存在困惑?…...

【AI】机器学习——绪论

文章目录 1.1 机器学习概念1.1.1 定义统计机器学习与数据挖掘区别机器学习前提 1.1.2 术语1.1.3 特点以数据为研究对象目标方法——基于数据构建模型SML三要素SML步骤 1.2 分类1.2.1 参数化/非参数化方法1.2.2 按算法分类1.2.3 按模型分类概率模型非概率模型逻辑斯蒂回归 1.2.4…...

linux 查看端口占用

查看端口占用 使用lsof 可以使用lsof -i:端口号 来查看端口占用情况 lsof -i:8010COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAMEnginx 35653 zhanghe 10u IPv4 0xcac2e413ddf9c5b9 0t0 TCP *:8010 (LISTEN)nginx 35654 zhanghe 10u…...

modernC++手撸任意层神经网络22前向传播反向传播梯度下降等23代码补全的例子0901b

以下神经网络代码,请添加输入:{{1,0},{1,1}},输出{1,0};添加反向传播,梯度下降等训练! 以下神经网络代码,请添加输入:{{1,0},{1,1}},输出{1,0};添加反向传播,梯度下降等训练! #include <iostream> #include<vector> #include<Eigen/Dense> #include<rando…...

tkinter控件样式

文章目录 以按钮为例共有参数动态属性 tkinter系列&#xff1a; GUI初步&#x1f48e;布局&#x1f48e;绑定变量&#x1f48e;绑定事件&#x1f48e;消息框&#x1f48e;文件对话框&#x1f48e;控件样式扫雷小游戏&#x1f48e;强行表白神器 以按钮为例 tkinter对控件的诸…...

【linux命令讲解大全】042. 深入了解 which 命令:查找和显示命令的绝对路径

文章目录 which补充说明语法选项参数实例 从零学 python which 查找并显示给定命令的绝对路径 补充说明 which 命令用于查找并显示给定命令的绝对路径&#xff0c;环境变量 PATH 中保存了查找命令时需要遍历的目录。which 指令会在环境变量 $PATH 设置的目录里查找符合条件的…...

实战项目 在线学院之集成springsecurity的配置以及执行流程

一 后端操作配置 1.0 工程结构 1.1 在common下创建spring_security模块 1.2 pom文件中依赖的注入 1.3 在service_acl模块服务中引入spring-security权限认证模块 1.3.1 service_acl引入spring-security 1.3.2 在service_acl编写查询数据库信息 定义userDetailServiceImpl 查…...

基于ASP.NET+ SQL Server实现(Web)医院信息管理系统

医院信息管理系统 1. 课程设计内容 在 visual studio 2017 平台上&#xff0c;开发一个“医院信息管理系统”Web 程序。 2. 课程设计目的 综合运用 c#.net 知识&#xff0c;在 vs 2017 平台上&#xff0c;进行 ASP.NET 应用程序和简易网站的开发&#xff1b;初步熟悉开发一…...

QMC5883L的驱动

简介 本篇文章的代码已经上传到了github上面&#xff0c;开源代码 作为一个电子罗盘模块&#xff0c;我们可以通过I2C从中获取偏航角yaw&#xff0c;相对于六轴陀螺仪的yaw&#xff0c;qmc5883l几乎不会零飘并且成本较低。 参考资料 QMC5883L磁场传感器驱动 QMC5883L磁力计…...

Mybatis逆向工程,动态创建实体类、条件扩展类、Mapper接口、Mapper.xml映射文件

今天呢&#xff0c;博主的学习进度也是步入了Java Mybatis 框架&#xff0c;目前正在逐步杨帆旗航。 那么接下来就给大家出一期有关 Mybatis 逆向工程的教学&#xff0c;希望能对大家有所帮助&#xff0c;也特别欢迎大家指点不足之处&#xff0c;小生很乐意接受正确的建议&…...

在Ubuntu中设置开机自动运行(sudo)指令的指南

在Ubuntu系统中&#xff0c;有时需要在系统启动时自动执行某些命令&#xff0c;特别是需要 sudo权限的指令。为了实现这一功能&#xff0c;可以使用多种方法&#xff0c;包括编写Systemd服务、配置 rc.local文件或使用 cron任务计划。本文将详细介绍这些方法&#xff0c;并提供…...

uniapp微信小程序视频实时流+pc端预览方案

方案类型技术实现是否免费优点缺点适用场景延迟范围开发复杂度​WebSocket图片帧​定时拍照Base64传输✅ 完全免费无需服务器 纯前端实现高延迟高流量 帧率极低个人demo测试 超低频监控500ms-2s⭐⭐​RTMP推流​TRTC/即构SDK推流❌ 付费方案 &#xff08;部分有免费额度&#x…...

PL0语法,分析器实现!

简介 PL/0 是一种简单的编程语言,通常用于教学编译原理。它的语法结构清晰,功能包括常量定义、变量声明、过程(子程序)定义以及基本的控制结构(如条件语句和循环语句)。 PL/0 语法规范 PL/0 是一种教学用的小型编程语言,由 Niklaus Wirth 设计,用于展示编译原理的核…...

代码规范和架构【立芯理论一】(2025.06.08)

1、代码规范的目标 代码简洁精炼、美观&#xff0c;可持续性好高效率高复用&#xff0c;可移植性好高内聚&#xff0c;低耦合没有冗余规范性&#xff0c;代码有规可循&#xff0c;可以看出自己当时的思考过程特殊排版&#xff0c;特殊语法&#xff0c;特殊指令&#xff0c;必须…...

SpringAI实战:ChatModel智能对话全解

一、引言&#xff1a;Spring AI 与 Chat Model 的核心价值 &#x1f680; 在 Java 生态中集成大模型能力&#xff0c;Spring AI 提供了高效的解决方案 &#x1f916;。其中 Chat Model 作为核心交互组件&#xff0c;通过标准化接口简化了与大语言模型&#xff08;LLM&#xff0…...

【Veristand】Veristand环境安装教程-Linux RT / Windows

首先声明&#xff0c;此教程是针对Simulink编译模型并导入Veristand中编写的&#xff0c;同时需要注意的是老用户编译可能用的是Veristand Model Framework&#xff0c;那个是历史版本&#xff0c;且NI不会再维护&#xff0c;新版本编译支持为VeriStand Model Generation Suppo…...

SQL注入篇-sqlmap的配置和使用

在之前的皮卡丘靶场第五期SQL注入的内容中我们谈到了sqlmap&#xff0c;但是由于很多朋友看不了解命令行格式&#xff0c;所以是纯手动获取数据库信息的 接下来我们就用sqlmap来进行皮卡丘靶场的sql注入学习&#xff0c;链接&#xff1a;https://wwhc.lanzoue.com/ifJY32ybh6vc…...