Explain-MySQL分析和优化查询的核心工具

2024-08-25 12:04

本文主要是介绍Explain-MySQL分析和优化查询的核心工具,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

1、什么是Explain

在MySQL中,Explain是用于分析和优化 SQL 查询的执行计划的工具。帮助开发者和数据库管理员理解查询是如何在 MySQL 中执行的,并找出潜在的性能瓶颈或优化机会。

Explain 是一个 SQL 语句,可以在 select、delete、insert和 update语句之前使用。它会显示 MySQL 在执行查询时采用的计划,包括如何访问表、使用哪些索引、连接类型、行数估算等信息。

explain + 查询语句 

在这里插入图片描述

2、Explain输出字段详解

Explain语句执行完会输出12个字段。

2.1、id-字段

1、编号是 select 的序列号,有几个select就对应几条记录,按 select 出现的顺序增长。

2、值越大执行优先级越高,值相同则从上往下执行,id为NULL最后执行。

2.2、select_type-字段

查询类型,有4种类型:

1、simple:简单查询,不包含子查询和union

2、primary:复杂查询中最外层的 select

3、subquery:包含在 select 中的子查询(不在 from 子句中)

4、derived:包含在 from 子句中的子查询。MySQL将结果存放在一个临时表中,也称为派生表

2.3、table-字段

表示 explain 的一行正在访问哪个表,当包含子查询或union时:

1、当 from 子句中有子查询时,内容格式是 ,表示当前查询依赖 id=N 的查询,先执行 id=N 的查询。

2、当有 union 时, 内容格式是,1和2表示参与 union 的 select 行id。

2.4、partitions-字段

如果查询是基于分区表的话,partitions 字段会显示查询将访问的分区,否则显示NULL。

2.5、type-字段

表示关联类型或访问类型,是MySQL决定如何查找表中的行,查找数据行记录的大概范围。

有7种类型,从最优到最差分别为:system > const > eq_ref > ref > range > index > ALL

一般来说,要保证查询达到range级别,最好达到ref

各个类型详解:

0、NULL:没用索引或不再访问表,例如:在索引列中选取最小值,可以单独查找索引来完成,不需要在执行时访问表

1、system:是const的特例,表里只有一条元组匹配时,比如:分析包含子查询,子查询结果是1条记录,select_type=primary, type=system,子查询type=const

**2、const:**对查询的某部分进行优化并将其转化成一个常量(可以看show warnings 的结果)。用于 primary key 或 unique key 的所有列与常数比较时,表最多有一个匹配行,读取1次,速度比较快。

3、eq_ref:primary key 或 unique key 索引的所有部分被连接使用 ,最多只会返回一条符合条件的记录。可能是在 const 之外最好的联接类型,简单的 select 查询不会出现这种 type。

4、ref:相比 eq_ref,不使用唯一索引,而是使用普通索引或者唯一性索引的部分前缀,索引要和某个值相比较,可能会找到多个符合条件的行。

5、range:范围扫描通常出现在 in(), between ,> ,<, >= 等操作中,使用一个索引来检索给定范围的行。

6、index:扫描全索引就能拿到结果,一般是扫描某个二级索引的叶子节点遍历和扫描,不会从索引树根节点开始快速查找,速度比较慢,一般使用覆盖索引,二级索引一般比较小,所以这种通常比ALL快一些。

7、ALL:全表扫描,扫描聚簇索引的所有叶子节点,一般需要增加索引来进行优化。

2.6、possible_keys-字段

显示查询可能使用哪些索引来查找。

如果值是NULL,key也显示NULL可能两种情况:

1、表中数据不多,使用索引对查询帮助不大,选择全表查询

2、没有相关索引,解决方案:可通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能,然后用 explain 查看效果。

2.7、key-字段

显示mysql实际采用哪个索引来优化对该表的访问。

如果值是NULL,表示没有使用索引,possible_keys又有值,是mysql认为使用索引对查询帮助不大。

如果想强制mysql使用或忽视possible_keys列中的索引,在查询中使用 force index、ignore index。

2.8、key_len-字段

显示了mysql在索引里使用的字节数,通过这个值可以算出具体使用了索引中的哪些列。

key_len计算规则如下:

  • 如果字段允许为 NULL,需要1字节记录是否为 NULL
  • 字符串:
    • char(n)和varchar(n),5.0.3以后版本中,n均代表字符数,而不是字节数,如果是utf-8,一个数字或字母占1个字节,一个汉字占3个字节
    • char(n):如果存汉字长度就是 3n 字节
    • varchar(n):如果存汉字则长度是 3n + 2 字节(不允许为NULL时),加的2字节用来存储字符串长度,因为varchar是变长字符串,如果是可为NULL,长度是 3n + 2 +1(NULL记录长度1)字节
  • 数值类型:
    • tinyint:1字节
    • smallint:2字节
    • int:4字节
    • bigint:8字节
  • 时间类型:
    • date:3字节
    • timestamp:4字节
    • datetime:8字节

索引最大长度是768字节,当字符串过长时,mysql会做一个类似左前缀索引的处理,将前半部分的字符提取出来做索引。

2.9、ref-字段

显示在key列记录的索引中,表示查找值所用到的常量或列:

1、const(常量)

2、字段名(例:film.id)

2.10、rows-字段

表示估计要读取并检测的行数,注意:不是结果集里的行数。

2.11、filtered-字段

是一个百分比的值,rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数(前一个表指 explain 中的id值比当前表id值小的表)。

2.12、Extra-字段

展示的是额外信息,常见6个重要值如下:

1、Using index:使用覆盖索引

覆盖索引定义:mysql执行计划explain结果里的key有使用索引,如果select后面查询的字段都可以从这个索引的树中获取,一般有用了覆盖索引,extra里一般都有using index;覆盖索引一般针对的是辅助索引,整个查询结果只通过辅助索引就能拿到结果,不需要通过辅助索引树找到主键,再通过主键去主键索引树里获取其它字段值。

2、Using where:使用 where 语句来处理结果,并且查询的列未被索引覆盖

3、Using index condition:查询的列不完全被索引覆盖,where条件中是一个前导列的范围;

4、Using temporary:mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的,首先是想到用索引来优化。

5、Using filesort:将用外部排序而不是索引排序,数据较小时从内存排序,否则需要在磁盘完成排序。一般也是要考虑使用索引来优化的。

6、Select tables optimized away:使用某些聚合函数(比如 max、min)来访问存在索引的某个字段

3、我的公众号

敬请关注我的公众号:大象只为你,持续更新技术知识…

这篇关于Explain-MySQL分析和优化查询的核心工具的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



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

相关文章

SpringBoot3实现Gzip压缩优化的技术指南

《SpringBoot3实现Gzip压缩优化的技术指南》随着Web应用的用户量和数据量增加,网络带宽和页面加载速度逐渐成为瓶颈,为了减少数据传输量,提高用户体验,我们可以使用Gzip压缩HTTP响应,... 目录1、简述2、配置2.1 添加依赖2.2 配置 Gzip 压缩3、服务端应用4、前端应用4.1 N

Go标准库常见错误分析和解决办法

《Go标准库常见错误分析和解决办法》Go语言的标准库为开发者提供了丰富且高效的工具,涵盖了从网络编程到文件操作等各个方面,然而,标准库虽好,使用不当却可能适得其反,正所谓工欲善其事,必先利其器,本文将... 目录1. 使用了错误的time.Duration2. time.After导致的内存泄漏3. jsO

MySQL双主搭建+keepalived高可用的实现

《MySQL双主搭建+keepalived高可用的实现》本文主要介绍了MySQL双主搭建+keepalived高可用的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,... 目录一、测试环境准备二、主从搭建1.创建复制用户2.创建复制关系3.开启复制,确认复制是否成功4.同

Spring Boot + MyBatis Plus 高效开发实战从入门到进阶优化(推荐)

《SpringBoot+MyBatisPlus高效开发实战从入门到进阶优化(推荐)》本文将详细介绍SpringBoot+MyBatisPlus的完整开发流程,并深入剖析分页查询、批量操作、动... 目录Spring Boot + MyBATis Plus 高效开发实战:从入门到进阶优化1. MyBatis

MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

《MyBatis动态SQL优化之标签的实战与技巧(常见用法)》本文通过详细的示例和实际应用场景,介绍了如何有效利用这些标签来优化MyBatis配置,提升开发效率,确保SQL的高效执行和安全性,感... 目录动态SQL详解一、动态SQL的核心概念1.1 什么是动态SQL?1.2 动态SQL的优点1.3 动态S

Python基于wxPython和FFmpeg开发一个视频标签工具

《Python基于wxPython和FFmpeg开发一个视频标签工具》在当今数字媒体时代,视频内容的管理和标记变得越来越重要,无论是研究人员需要对实验视频进行时间点标记,还是个人用户希望对家庭视频进行... 目录引言1. 应用概述2. 技术栈分析2.1 核心库和模块2.2 wxpython作为GUI选择的优

Mysql表的简单操作(基本技能)

《Mysql表的简单操作(基本技能)》在数据库中,表的操作主要包括表的创建、查看、修改、删除等,了解如何操作这些表是数据库管理和开发的基本技能,本文给大家介绍Mysql表的简单操作,感兴趣的朋友一起看... 目录3.1 创建表 3.2 查看表结构3.3 修改表3.4 实践案例:修改表在数据库中,表的操作主要

Python如何使用__slots__实现节省内存和性能优化

《Python如何使用__slots__实现节省内存和性能优化》你有想过,一个小小的__slots__能让你的Python类内存消耗直接减半吗,没错,今天咱们要聊的就是这个让人眼前一亮的技巧,感兴趣的... 目录背景:内存吃得满满的类__slots__:你的内存管理小助手举个大概的例子:看看效果如何?1.

一文详解SpringBoot响应压缩功能的配置与优化

《一文详解SpringBoot响应压缩功能的配置与优化》SpringBoot的响应压缩功能基于智能协商机制,需同时满足很多条件,本文主要为大家详细介绍了SpringBoot响应压缩功能的配置与优化,需... 目录一、核心工作机制1.1 自动协商触发条件1.2 压缩处理流程二、配置方案详解2.1 基础YAML

Spring事务中@Transactional注解不生效的原因分析与解决

《Spring事务中@Transactional注解不生效的原因分析与解决》在Spring框架中,@Transactional注解是管理数据库事务的核心方式,本文将深入分析事务自调用的底层原理,解释为... 目录1. 引言2. 事务自调用问题重现2.1 示例代码2.2 问题现象3. 为什么事务自调用会失效3