当前位置:   article > 正文

数据库 explain 关键字解析_explain的关键词

explain的关键词

目录

1. explain 概述

2. explain 关键字的使用方式

3. explain 的版本迭代

4. explain 只分析SQL语句,不执行SQL语句

5. explain 输出结果中各个字段的含义

6. type 表示检索表数据的方式

7. key_len表示使用的索引的长度

8. rows 表示预估读取到的行数

9. Extra 表示SQL执行时的一些额外信息


1. explain 概述

有些同学可能不知道,我们在数据库中运行 SQL 语句时,底层优化器会选择最优的执行方式来执行我们的 SQL 语句,也就是说,底层执行 SQL 语句的顺序并不一定跟我们写的 SQL 语句的顺序是一致的。MySQL 中有专门优化 SELECT 语句的优化器模块,主要功能是:通过计算分析系统中收集到的统计信息,为客户端请求的 Query(查询) 提供它认为最优的执行计划。

(这里要注意一点,这里最优的执行计划是服务器通过计算得出的,但不见得是我们开发人员认为是最优的,所以实际开发过程中我们的DBA不一定会采用它所提供的执行计划,而是在所提供的执行计划上综合业务逻辑做一些调整)

而通过 explain 关键字就可以让我们知道数据库对 SQL 语句的执行计划是怎么样的,比如多表连接的顺序是什么,执行一条 SQL 语句时,数据库在执行的时候先做什么,再做什么,都可以通过 explain 关键字来查看。explain 语句有很多的输出项,我们就可以通过这些输出项的数据来对 SQL 语句做判断,从而优化那些查询慢的 SQL 语句。

2. explain 关键字的使用方式

explain 关键字的使用方式极其简单,我们只需要将它加在SQL 语句的最前面就可以了,如下所示

  1. # 加在 SELECT 查询语句的前面
  2. EXPLAIN SELECT * FROM t_patent WHERE pid > 115;
  3. # 加在 DELETE 删除语句的前面
  4. EXPLAIN DELETE FROM t_patent WHERE pid = 110;
  5. # 加在 UPDATE 更新语句的前面
  6. EXPLAIN UPDATE t_patent
  7. SET t_patent.patent_name = "张三" WHERE pid = 130;

3. explain 的版本迭代

(1)在 MySQL5.6.3 版本以前,explain 只能分析 SELECT 语句的执行计划,但是在 MySQL5.6.3 版本以后,explain 不仅可以分析 SELECT 语句的执行计划,还可以分析UPDATE,DELETE语句的执行计划,只是我们更多的是用于分析 SELECT 查询语句,这一点作为了解即可;

(2)在 MySQL5.7 以前的版本中,想要显示 partitions 需要使用 explain partitions 命令,想要显示 filtered 需要使用 explain extended 命令。而 5.7 版本之后,默认 explain 将直接显示 partitions 和 filtered 中的信息。也许现在有些同学还不 partitions 和 filtered 是什么,这里先不用关心,看到下面的讲解自然就会明白了。这里你先可以简单理解为,在 5.7 版本之后,explain 关键字做了增强,explain 将另外两个关键字的功能也包含进去了,不需要再像以前一样执行三个关键字。

4. explain 只分析SQL语句,不执行SQL语句

这句话应该很好理解,我们使用 explain 关键字执行一条语句时,它只是输出当前 SQL 语句最优的执行计划,但并没有真正的去执行这条 SQL 语句。

我简单举个例子演示一下

(1)如下为 patent 数据库中的 t_patent 表,表中有一些数据

(2)然后,我使用 explain 关键字查询输出当前 DELETE 语句的执行计划,运行成功输出了结果,这里暂时不关心结果中的各个字段含义是什么

(3)我们再来查询一下刚才 pid = 110 的那条数据是否存在,如下,我们发现仍然可以查询到 pid = 110 的这条数据。

通过这个例子也证实了刚才的结论,explain 只负责输出最优的执行计划,但并没有真正的去执行 SQL 语句。

5. explain 输出结果中各个字段的含义

刚才在第四小节的演示中,我们看到了执行 explain 会输出很多的字段,一共有12个,每个字段代表了不同的涵义,列举如下

在这12个字段中,我们需要重点关注 type,key_len,rows,Extra 这个四个字段,他们是面试过程中最容易问到的点,而且是SQL语句调优的关键点,我们的 SQL 的调优就是基于这个四个关键字段来调的。

6. type 表示检索表数据的方式

执行计划中的 type 的一条记录就代表 MySQL 对某个表执行查询时的访问方式,也称"访问类型"。它有众多访问方式,查询的效率从高到低依次是

system > const > eq > ref > fulltext > ref or null > range > index > ALL

在实际开发过程中,我们希望一条 SQL 语句的查询效率越高越好,大多数情况下应当把访问方式至少定位在 "range" 以上,ALL(全表扫描)是我们最不希望看到的结果,所以在编写 SQL 时要尽量避免此访问类型的发生。

7. key_len表示使用的索引的长度

key_len 通常应用于联合索引,开发过程中,一个表会有很多的字段,为了提高查询效率,我们会为查询频率较高的几个字段建立联合索引,通过 explain 就可以得到使用而索引字段的长度,从而得知当前SQL语句中都是用到了联合索引中的那个字段,以便做进一步的优化。

不同的类型的字段计算规则也不一样,如下所示

(1)字符串类型计算

char(n):算作 n 个字节;

varchar:如果是 utf-8 则算作 3n+2 个字节,如果是utf-mb4,则算作 4n+2 个字节;

(2)数值类型计算

tinyint:1字节;

smaillint:2字节;

int:4字节;

bigint:8字节;

(3)事件类型计算

date:3字节;

timestamp:4字节;

datetime:8字节;

此外,字段如果为空,需要1个字段记录是否为空。

通过 explain 得出的索引时应长度,配合上述三种类型长度的计算,我们就能精准得出联合索引中的那些字段被使用到,那些没有被使用到,可以进一步优化sql语句的编写,提高程序运行效率。

8. rows 表示预估读取到的行数

rows 字段的值通常可以搭配 filtered 字段的值,rows 表示预估会读取到的行数,filtered 则表示经过条件筛选之后剩余记录条数的百分比,而两者想成得出的结果通常就是我们最终查询所得到的数据行数。

9. Extra 表示SQL执行时的一些额外信息

这里的额外信息包含很多种,如下

(1)Using Index:使用非主键索引数就可以查询到所需要的数据,一般是覆盖索引,即想要查询的数据都包含在非剧组索引数中,不需要进行回表操作。

(2)Using where:不通过索引查询所需要的数据,这句话也很好理解,就是我们 where 筛选条件的字段不是索引字段。那么就会进行全表扫描,即 type 字段值为 ALL。

(3)Using index condition:表示查询列不被索引覆盖,where 条件中是一个索引范围查找,过滤玩索引后会回表找到所有符合条件的数据行。例如 SELECT * FROM table where 主键 > 某个值,这种 SQL 语句就符合上面这种情况,

(4)Using temporary:表示在查询的过程中需要利用临时表来处理查询,比如我们使用 union all 取两个表满足条件的并集,得到的数据表实际上是一张临时表;

(5)Using filesort:当查询中包含 order by 操作而且无法利用索引完成的排序操作时,若数据量小就在内存中进行排序,若数据量多就在硬盘中进行排序。

当遇到 Using temporary 或 Using filesort 时,我们可以通过添加索引对 SQL 进行优化。

(6)Select table optimized away:当我们通过聚合函数访问某些索引数据时,在 Extra 中就会显示 Select table optimized away,如果通过聚合函数访问普通非索引字段,在 Extra 中就会显示 null。 

本文内容由网友自发贡献,转载请注明出处:https://www.wpsshop.cn/w/人工智能uu/article/detail/796676
推荐阅读
相关标签
  

闽ICP备14008679号