【MySQL】第六篇:MySQL之详解 Explain

2024-08-27 23:48

本文主要是介绍【MySQL】第六篇:MySQL之详解 Explain,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

一、概念

使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句,从而知道 MySQL 是如何处理你的 SQL 语句的。分 析你的查询语句或是表结构的性能瓶颈。
用法:explain+SQL 语句
explain 执行返回后的信息如下:
在这里插入图片描述
下面针对返回后的信息详细说明,准备工作:
本文基于MySQL 8.0.19,不同的版本 explain 会不同
省市区的级联sql:脚本下载地址

二、explain 结果解析

2.1、id

select 查询的序列号,是一组数字,表示查询中执行 select 子句或操作表的顺序。
关注点:id 号每个号码,表示一趟独立的查询。一个 sql 的查询趟数越少越好。

2.1.1、id相同

id相同,执行顺序由上而下
在这里插入图片描述

2.1.1、id不同

id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先会被执行
在这里插入图片描述

2.1.2、id有相同也有不同

id如果相同,可以认为是一组,从上往下的顺序执行;在所有组中,id值越大,优先级越高,越先执行
在这里插入图片描述

2.2、select_type

select_type 代表查询的类型,主要是用于区别普通查询、联合查询、子查询等的复杂查询。

总览

MySQL 8.0 select_type列表:

名称JSON名含义
SIMPLE简单的SELECT(没有使用UNION或者子查询)
PRIMARY最外层的查询
UNION在一个UNION中第二或后面的SELECT语句
DEPENDENT UNIONdependent (true)在一个UNION中第二或后面的SELECT语句,并且依赖于外层查询
UNION RESULTunion_resultUNION的结果
SUBQUERY在SELECT或WHERE列表中包含了子查询
DEPENDENT SUBQUERYdependent (true)子查询中的第一个SELECT,并且依赖于外层查询
DERIVED派生表(Derived table),在 FROM 列表中包含的子查询被标记为 DERIVED(衍生) ,MySQL 会递归执行这些子查询,把结果放在临时表里
MATERIALIZEDmaterialized_from_subquery实例化子查询(Materialized subquery)
UNCACHEABLE SUBQUERYcacheable (false)不能缓存结果的子查询,并且必须为外部查询的每一行重新计算结果
UNCACHEABLE UNIONcacheable (false)在一个UNCACHEABLE SUBQUERY的UNION语句中第二或后面的SELECT语句

2.2.1、SIMPLE 代表单表查询

在这里插入图片描述

2.2.2、PRIMARY

查询中若包含任何复杂的子部分,最外层查询则被标记为 Primary。
在这里插入图片描述

2.2.3、DERIVED

在 FROM 列表中包含的子查询被标记为 DERIVED(衍生),MySQL 会递归执行这些子查询,把结果放在临时表里。

下面语句在 5.5 版本会显示 DERIVER,当前版本为 8.0 未显示DERIVER
在这里插入图片描述
在这里插入图片描述
在 from 子查询中,如果有 table1 union table2,则(左表)table1就是derived,(右表)table2就是union;
UNION RESULT:告知开发人员,哪些表之间存在union查询;

2.2.4、SUBQUERY

在 SELECT 或 WHERE 列表中包含了子查询。
在这里插入图片描述

2.2.5、DEPENDENT SUBQUERY

在 SELECT 或 WHERE 列表中包含了子查询,子查询基于外层。

等等

2.3、table

这个数据是基于哪张表的

2.4、type

type 是查询的访问类型。是较为重要的一个指标,结果值从最好到最坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > All,一般来说,得保证查询至少达到 range 级别,最好能达到 ref。

2.4.1、system

表只有一行记录(等于系统表),这是 const 类型的特列,平时不会出现,这个也可以忽略不计

2.4.2、const

表示通过索引一次就找到了,const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据,所以很快;如:将主键置于 where 列表中,MySQL 就能将该查询转换为一个常量。
在这里插入图片描述

2.4.3、eq_ref

唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
在这里插入图片描述

2.4.4、ref

非唯一性索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问,它返回所有匹配某个单独值的行, 然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体。

没用索引前:
在这里插入图片描述

建立索引后:
在这里插入图片描述

2.4.5、range

只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的 where 语句中出现 了 between、<、>、in 等的查询这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,结束于另一点,不用扫描全部索引。
在这里插入图片描述

2.4.6、index

出现index是sql使用了索引但是没用通过索引进行过滤,一般是使用了覆盖索引或者是利用索引进行了排序分组。
在这里插入图片描述

2.4.7、all

Full Table Scan,将遍历全表以找到匹配的行。
在这里插入图片描述

2.4.8、index_merge

在查询过程中需要多个索引组合使用,通常出现在有 or 的关键字的 sql 中。
在这里插入图片描述

2.4.9、ref_or_null

对于某个索引字段既需要关联条件,也需要 null 值的情况下。查询优化器会选择用 ref_or_null 连接查询。
在这里插入图片描述

2.4.10、index_subquery

利用索引来关联子查询,不再全表扫描。

2.4.11、unique_subquery

该联接类型类似于 index_subquery。 子查询中的唯一索引。

2.5、possible_keys

显示可能应用在这张表中的索引,一个或多个。查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用。

2.6、key

实际使用的索引。如果为NULL,则没有使用索引。

2.7、key_len

表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。 key_len 字段能够帮你检查是否充分的利用上了索引。ken_len 越长,说明索引使用的越充分。
在这里插入图片描述
如何计算:

  • 先看索引上字段的类型+长度比如 int=4 ; varchar(20) =20 ; char(20) =20
  • 如果是 varchar 或者 char 这种字符串字段,视字符集要乘不同的值,比如 utf-8
  • varchar 这种动态字符串要加 2 个字节
  • 允许为空的字段要加 1 个字节

2.8、ref

显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值。
在这里插入图片描述
在这里插入图片描述

2.9、rows

rows 列显示 MySQL 认为它执行查询时必须检查的行数。越少越好!

2.10、Extra

其他的额外重要的信息。

2.10.1、Using filesort (尽量避免)

说明 MySQL 会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL 中无法利用索引完成的排序操作称为“文件排序”。尽量避免按照文件排序,排序字段若通过索引去访问将大大提高排序速度。
出现 filesort 的情况:
在这里插入图片描述

2.10.2、Using temporary (尽量避免)

使了用临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。尽量避免临时表
优化前:
在这里插入图片描述
优化后:
在这里插入图片描述

2.10.3、Using index

Using index 表示相应的 select 操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错!

  • 如果同时出现 using where,表明索引被用来执行索引键值的查找;
  • 如果没有同时出现 using where,表明索引只是用来读取数据而非利用索引执行查找。
    尽量利用索引列进行了排序或分组。

2.10.4、Using where

表明使用了 where 过滤,用来进行查找。

2.10.5、Using join buffer

使用了连接缓存。

2.10.6、impossible where

where 子句的值总是 false,不能用来获取任何元组。
在这里插入图片描述

2.10.7、select tables optimized away

在没有 GROUPBY 子句的情况下,基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*) 操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。

这篇关于【MySQL】第六篇:MySQL之详解 Explain的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1113132

相关文章

Windows 上如果忘记了 MySQL 密码 重置密码的两种方法

《Windows上如果忘记了MySQL密码重置密码的两种方法》:本文主要介绍Windows上如果忘记了MySQL密码重置密码的两种方法,本文通过两种方法结合实例代码给大家介绍的非常详细,感... 目录方法 1:以跳过权限验证模式启动 mysql 并重置密码方法 2:使用 my.ini 文件的临时配置在 Wi

一文详解Java异常处理你都了解哪些知识

《一文详解Java异常处理你都了解哪些知识》:本文主要介绍Java异常处理的相关资料,包括异常的分类、捕获和处理异常的语法、常见的异常类型以及自定义异常的实现,文中通过代码介绍的非常详细,需要的朋... 目录前言一、什么是异常二、异常的分类2.1 受检异常2.2 非受检异常三、异常处理的语法3.1 try-

Java中的@SneakyThrows注解用法详解

《Java中的@SneakyThrows注解用法详解》:本文主要介绍Java中的@SneakyThrows注解用法的相关资料,Lombok的@SneakyThrows注解简化了Java方法中的异常... 目录前言一、@SneakyThrows 简介1.1 什么是 Lombok?二、@SneakyThrows

Java中字符串转时间与时间转字符串的操作详解

《Java中字符串转时间与时间转字符串的操作详解》Java的java.time包提供了强大的日期和时间处理功能,通过DateTimeFormatter可以轻松地在日期时间对象和字符串之间进行转换,下面... 目录一、字符串转时间(一)使用预定义格式(二)自定义格式二、时间转字符串(一)使用预定义格式(二)自

Redis Pipeline(管道) 详解

《RedisPipeline(管道)详解》Pipeline管道是Redis提供的一种批量执行命令的机制,通过将多个命令一次性发送到服务器并统一接收响应,减少网络往返次数(RTT),显著提升执行效率... 目录Redis Pipeline 详解1. Pipeline 的核心概念2. 工作原理与性能提升3. 核

MySQL重复数据处理的七种高效方法

《MySQL重复数据处理的七种高效方法》你是不是也曾遇到过这样的烦恼:明明系统测试时一切正常,上线后却频频出现重复数据,大批量导数据时,总有那么几条不听话的记录导致整个事务莫名回滚,今天,我就跟大家分... 目录1. 重复数据插入问题分析1.1 问题本质1.2 常见场景图2. 基础解决方案:使用异常捕获3.

Python正则表达式语法及re模块中的常用函数详解

《Python正则表达式语法及re模块中的常用函数详解》这篇文章主要给大家介绍了关于Python正则表达式语法及re模块中常用函数的相关资料,正则表达式是一种强大的字符串处理工具,可以用于匹配、切分、... 目录概念、作用和步骤语法re模块中的常用函数总结 概念、作用和步骤概念: 本身也是一个字符串,其中

SQL中redo log 刷⼊磁盘的常见方法

《SQL中redolog刷⼊磁盘的常见方法》本文主要介绍了SQL中redolog刷⼊磁盘的常见方法,将redolog刷入磁盘的方法确保了数据的持久性和一致性,下面就来具体介绍一下,感兴趣的可以了解... 目录Redo Log 刷入磁盘的方法Redo Log 刷入磁盘的过程代码示例(伪代码)在数据库系统中,r

mysql中的group by高级用法

《mysql中的groupby高级用法》MySQL中的GROUPBY是数据聚合分析的核心功能,主要用于将结果集按指定列分组,并结合聚合函数进行统计计算,下面给大家介绍mysql中的groupby用法... 目录一、基本语法与核心功能二、基础用法示例1. 单列分组统计2. 多列组合分组3. 与WHERE结合使

Nginx location匹配模式与规则详解

《Nginxlocation匹配模式与规则详解》:本文主要介绍Nginxlocation匹配模式与规则,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、环境二、匹配模式1. 精准模式2. 前缀模式(不继续匹配正则)3. 前缀模式(继续匹配正则)4. 正则模式(大