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

相关文章

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

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

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

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

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结合使

Mysql用户授权(GRANT)语法及示例解读

《Mysql用户授权(GRANT)语法及示例解读》:本文主要介绍Mysql用户授权(GRANT)语法及示例,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql用户授权(GRANT)语法授予用户权限语法GRANT语句中的<权限类型>的使用WITH GRANT

Mysql如何解决死锁问题

《Mysql如何解决死锁问题》:本文主要介绍Mysql如何解决死锁问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录【一】mysql中锁分类和加锁情况【1】按锁的粒度分类全局锁表级锁行级锁【2】按锁的模式分类【二】加锁方式的影响因素【三】Mysql的死锁情况【1

SQL BETWEEN 的常见用法小结

《SQLBETWEEN的常见用法小结》BETWEEN操作符是SQL中非常有用的工具,它允许你快速选取某个范围内的值,本文给大家介绍SQLBETWEEN的常见用法,感兴趣的朋友一起看看吧... 在SQL中,BETWEEN是一个操作符,用于选取介于两个值之间的数据。它包含这两个边界值。BETWEEN操作符常用

MySQL索引的优化之LIKE模糊查询功能实现

《MySQL索引的优化之LIKE模糊查询功能实现》:本文主要介绍MySQL索引的优化之LIKE模糊查询功能实现,本文通过示例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录一、前缀匹配优化二、后缀匹配优化三、中间匹配优化四、覆盖索引优化五、减少查询范围六、避免通配符开头七、使用外部搜索引擎八、分

Java程序进程起来了但是不打印日志的原因分析

《Java程序进程起来了但是不打印日志的原因分析》:本文主要介绍Java程序进程起来了但是不打印日志的原因分析,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录Java程序进程起来了但是不打印日志的原因1、日志配置问题2、日志文件权限问题3、日志文件路径问题4、程序

MySql match against工具详细用法

《MySqlmatchagainst工具详细用法》在MySQL中,MATCH……AGAINST是全文索引(Full-Textindex)的查询语法,它允许你对文本进行高效的全文搜素,支持自然语言搜... 目录一、全文索引的基本概念二、创建全文索引三、自然语言搜索四、布尔搜索五、相关性排序六、全文索引的限制七