explain执行计划分析 ref_

这里写目录标题

  • 什么是Explain
  • Explain命令扩展
      • explain extended
      • explain partitions
  • 两点重要提示
  • 本文示例使用的数据库表
  • Explain命令(关键字)
      • explain简单示例
      • explain结果列说明
        • 【id列】
        • 【select_type列】
        • 【table列】
        • 【type列】
      • 【possible_keys列】
      • 【key列】
      • 【key_len列】
      • 【ref列】
      • 【rows列】
      • 【Extra列】
  • 查询优化建议
  • 索引使用情况分析
      • 数据库表
  • 实例说明
      • 实例一:
      • 实例二:
      • 实例三:
  • 总结

什么是Explain

Explain被称为执行计划,在语句之前增加 explain 关键字,MySQL 会在查询上设置一个标记,模拟MySQL优化器来执行SQL语句,执行查询时,会返回执行计划的信息,并不执行这条SQL。(注意,如果 from 中包含子查询,仍会执行该子查询,将结果放入临时表中)。

Explain可以用来分析SQL语句和表结构的性能瓶颈。通过explain的结果,可以了解到如数据表的查询顺序、数据查询操作的操作类型、哪些索引可以被命中、哪些索引实际会命中、每个数据表有多少行记录被查询等信息。

Explain命令扩展

explain extended

在explain的基础上提供一些额外的查询信息,在explian extended执行以后,通过show warnings命令可以得到优化后的查询语句,可以看出优化器做了哪些工作,还可以通过某些数据估算表连接的行数。

explain partitions

用于分析使用了分区的表,会显示出可能用到的分区。

两点重要提示

  1. Explain结果是基于数据表中现有数据的。
  2. Explain结果与MySQL版本有很大的关系,不同版本的优化器的优化策略不同。

本文示例使用的数据库表

在这里插入图片描述

Explain命令(关键字)

explain简单示例

mysql>explain select * from t_user;

在这里插入图片描述

在查询中的每个”表”会输出一行,这里的“表”的意义非常广泛,不仅仅是数据库表,还可以是子查询、一个union 结果等。

explain结果列说明

【id列】

id列是一个有顺序的编号,是查询的顺序号,有几个 select 就显示几行。id的顺序是按 select 出现的顺序增长的。id列的值越大执行优先级越高越先执行,id列的值相同则从上往下执行,id列的值为NULL最后执行。

【select_type列】

select_type列的值标明查询的类型:

1)simple:表明当前行对应的select是简单查询,不包含子查询和union

2)primary:表明当前行对应的select是复杂查询中最外层的 select

3)subquery:表明当前行对应的select是包含在 select 中的子查询(不在 from 子句中)

4)derived:表明当前行对应的select是包含在 from 子句中的子查询。

MySQL会创建一个临时表来存放子查询的查询结果。用如下的语句示例说明:

explain select (select 1 fromt_user where user_id=1) from (select * from t_group where group_id=1) tmp;

在这里插入图片描述

*注意,在资料收集过程中,发现不同版本的MySQL表现不一致,经反复对比,5.7及以后版本的输出如下:
在这里插入图片描述

很显然,MySQL在这方面进行了优化.

*注意,MySQL不同版本Explain表现差异很大,有些场景,从语句层面看,是要使用到索引,但经过优化器分析,结合表中现有数据,如果MySQL认为全表扫描性能更优,则会使用全表扫描。

5)union:表明当前行对应的select是在 union 中的第二个和随后的 select

6)union result:表明当前行对应的select是从 union 临时表检索结果的 select

explain select 1 union all select 2 fromdual;

在这里插入图片描述

MySQL5.7及以后同样做了优化

在这里插入图片描述

【table列】

table列的结果表明当前行对应的select正在访问哪个表。当查询的子句中有子查询时,table列是 格式,表示当前的select依赖 id=N结果行对应的查询,要先执行 id序号=N 的查询。当存在 union 时,UNION RESULT 的 table 列的值为<unionN1,N2>,N1和N2表示参与 union 的select 行的id序号。

【type列】

type列的结果表明当前行对应的select的关联类型或访问类型,也就是优化器决定怎么查找数据表中的行,以及查找数据行记录的大概范围。该列的取值优化程度的优劣,从最优到最差依次为:null>system> const > eq_ref > ref > range > index > ALL。一般来说,要保证查询达到range级别,最好达到ref。

1)null,MySQL优化器在优化阶段分解查询语句,在优化过程中就已经可以得到结果,那么在执行阶段就不用再访问表或索引。

explain select min(user_id) from t_user;

在这里插入图片描述

这时的函数min,在索引列user_id中选取最小值,可以直接查找索引来完成,不需要执行时再访问数据表。

2)const和system:const出现在用 primary key(主键) 或 unique key(唯一键) 的所有列与常数比较时,优化器对查询进行优化并将其部分查询转化成一个常量。最多有一个匹配行,读取1次,速度非常快。而system是const的特例,表中数据只有一条匹配时为system。此时可以用explain extended+show warnings查看执行结果。

explain extended select * from (select * from t_user where user_id = 1) tmp;show warnings;

MySQL5.7:

7fbbcad2e902adebc13376fcfd9abeae.png
31f8f406a85311adb6a9b6b0727879d2.png

3)eq_ref:primary key(主键)或 unique key(唯一键) 索引的所有构成部分被join使用 ,只会返回一条符合条件的数据行。这是仅次于const的连接类型。

explain select * from t_group_user gu left join t_group g ong.group_id = gu.group_id;

在这里插入图片描述

  1. ref:与eq_ref相比,ref类型不是使用primary key(主键) 或 unique key(唯一键)等唯一索引,而是使用普通索引或者联合唯一性索引的部分前缀,索引和某个值相比较,可能会找到符合条件的多个数据行。
  1. 如下示例,使用的group_name是普通索引
explain select * from t_group where group_name= 'group1';

在这里插入图片描述

2.关联表查询

explain select g.group_id from t_group gleft join t_group_user gu on gu.group_id = g.group_id;

在这里插入图片描述

5)range:出现在 in(),between ,> ,<, >= 等操作符中。使用一个索引来查询给定范围的行。

6)index:扫描全表索引(index是从索引中读取的,所有字段都有索引,而all是从硬盘中读取),比ALL要快。

explain select * from t_group;

在这里插入图片描述

7)all:即全表扫描,需要从头到尾去查找所需要的行。一般这种情况下这需要增加索引来进行查询优化了

explain select * from t_user;

在这里插入图片描述

【possible_keys列】

这一列的结果表明查询可能使用到哪些索引。但有些时候也会出现出现possible_keys 列有结果,而 后面的key列显示 null 的情况,这是因为此时表中数据不多,优化器认为查询索引对查询帮助不大,所以没有走索引查询而是进行了全表扫描。

如果possible_keys列的结果是null,则表明没有相关的索引。这时,可以通过优化where子句,增加恰当的索引来提升查询性能。

【key列】

这一列表明优化器实际采用哪个索引来优化对该表的访问。如果没有使用索引,则该列是 null。

【key_len列】

这一列表明了在索引里使用的字节数,通过这个值可以大致估算出具体使用了联合索引中的前几个列。

key_len计算规则这里不再赘述,不同的数据类型所占的字节数是不一致的。

【ref列】

这一列表明了在key列记录的索引中,表查找值所用到的列或常量,常见的有:const(常量),字段名,如user.user_id

【rows列】

这一列表明优化器大概要读取并检测的行数。跟实际的数据行数大部分情况是不一致的。

【Extra列】

顾名思义,这一列表明的是额外信息,这一列的取值对优化SQL非常有参考意义。常见的重要取值如下:

1)using index:所有被查询的字段都是索引列(称为覆盖索引),并且where条件是索引的前导列,出现这样的结果,是性能高的表现。

explain select group_id,group_name from t_group;

在这里插入图片描述

2)using where:被查询的列未被索引覆盖,where条件也并非索引的前导列,表示 MySQL 执行器从存储引擎接收到查询数据,再进行“后过滤”(Post-filter)。所谓“后过滤”,就是先读取整行数据,再检查此行是否符合 where 句的条件,符合就留下,不符合便丢弃。

explain select * from t_user whereuser_name='user1';

在这里插入图片描述

3)using where Using index:被查询的列被索引覆盖,并且where条件是索引列之一但是不是索引的前导列,也就是没有办法直接通过索引来查询到符合条件的数据

explain select * from t_group where group_name = 'group1';

在这里插入图片描述

4)null:被查询的列没有被索引覆盖,但where条件是索引的前导列,此时用到了索引,但是部分列未被索引覆盖,必须通过“回表查询”来实现,不是纯粹地用到了索引,也不是完全没用到索引

explain select * from t_user where user_id='1';

在这里插入图片描述

5)using index condition:与using where类似,查询的列不完全被索引覆盖,where条件中是一个前导列的范围;这种情况未能通过示例显现,可能跟MySQL版本有关系。

6) using temporary:这表明需要通过创建临时表来处理查询。出现这种情况一般是要进行优化的,用索引来优化。创建临时表的情况:distinct,group by,orderby,子查询等

explain select distinct user_name from t_user;

在这里插入图片描述

explain select distinct group_name fromt_group; --group_name是索引列

在这里插入图片描述

  1. usingfilesort:在使用order by的情况下出现,mysql会对结果使用一个外部索引排序,而不是按索引次序从表里读取行。此时mysql会根据连接类型浏览所有符合条件的记录,并保存排序关键字和行指针,然后排序关键字并按顺序检索行信息。这种情况下要考虑使用索引来优化的。
explain select * from t_user orderby user_name;

在这里插入图片描述

explain select * from t_group order bygroup_name; --group_name是索引列

在这里插入图片描述

查询优化建议

结合前面的描述,首先看 type列的结果,如果有类型是 all 时,表示预计会进行全表扫描(fulltable scan)。通常全表扫描的代价是比较大的,建议创建适当的索引,通过索引检索避免全表扫描。

再来看下 Extra 列的结果,如果有出现 Using temporary 或者 Using filesort 则要多加关注:

  • Using temporary,表示需要创建临时表以满足需求,通常是因为GROUP BY的列没有索引,或者GROUP BY和ORDER BY的列不一样,也需要创建临时表,建议添加适当的索引。

  • Using filesort,表示无法利用索引完成排序,也有可能是因为多表连接时,排序字段不是驱动表中的字段,因此也没办法利用索引完成排序,建议添加适当的索引。

  • Using where,通常是因为全表扫描或全索引扫描时(type 列显示为 ALL 或index),又加上了WHERE条件,建议添加适当的索引。

索引使用情况分析

数据库表

主键索引:demo_id

联合索引:c1,c2,c3

在这里插入图片描述

实例说明

实例一:

explain select * from t_demo where c1='d1'and c2='d2' and c3='d3';explain select * from t_demo where c2='d2'and c1='d1' and c3='d3';explain select * from t_demo where c3='d3'and c1='d1' and c2='d3';

在这里插入图片描述

几个Sql表现一致

type=ref,ref=const,const,const

执行常量等值查询时,改变索引列的顺序并不会更改explain的执行结果,优化器会进行优化,推荐按照索引顺序列编写sql语句。

实例二:

explain select * from t_demo where c1='d1’and c2>‘d2’ and c3=‘d3’;

ce59d28ae7e48e1bf87097eca05b0764.png

explain select * from t_demo where c1='d1’and c3>‘d3’ and c2=‘d2’;
在这里插入图片描述

第一个例子范围右侧索引失效,使用到了两个索引。

第二个例子,由于优化器优化的原因,使用到了全部的三个索引。

实例三:

explain select * from t_demo wherec1>‘c’ and c2=‘d2’ and c3=‘d3’;

00b6e7ab3049a2022b7e5f8f83e43b10.png

explain select * from t_demo wherec1>‘e’ and c2=‘d2’ and c3=‘d3’;

32c9260dbd5fba441302d674b00c1947.png

从上面两个实例可以发现,同样使用最左的索引列范围查询,有些情况未用到索引,做了全表扫描(第一个例子);有些情况使用到了索引(第二个例子)。

经反复验证,发现如下规律(不一定可靠),也可能与数据的第一行或最小值相关。

  1. 跟存储的数据有关

  2. 在大于条件下,如果条件数据小于列数据,则索引无效;如果条件数据大于列数据,则索引有效;

在设计查询条件时,请注意规避。

针对第一个例子,可以采用覆盖索引的方式优化。

实例四:

explain select * from t_demo where c1='d1'and c2='d2' order by c3;

在这里插入图片描述

explain select * from t_demo where c1='d1'order by c3;

在这里插入图片描述

explain select * from t_demo where c1='d1’and c3=‘d3’ order by c2;

ea8785842b0b5670378650729464e2c4.png

order by排序使用到索引和没使用到索引的情况

实例五:

explain select * from t_demo where c1='d1'and c4='d4' order by c1,c2;

de647a4ef983af82bea443eacf6e2b3c.png

条件列包含没有索引的列,出现了Using filesort

实例六:
explain select * from t_demo where c1='d1’and c4=‘d4’ group by c1,c2;

77169502065010a4667d04c82982b83d.png

性能非常差的场景,同时出现了Using temporary和Using filesort

总结

  1. 两种方式的排序filesort和index,Usingindex是指MySQL扫描索引本身完成排序。index效率高,filesort效率低。

  2. order by满足两种情况会使用Using index。

    1)order by语句使用索引最左前列。

    2)使用where子句与order by子句条件列组合满足索引最左前列。

  3. 尽量在索引列上完成排序,遵循索引建立(索引创建的顺序)时的最佳左前缀法则。

  4. group by与order by很类似,都是先排序后分组,遵照索引创建顺序的最佳左前缀法则。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/466866.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

关于elementui el-radio 赋值问题

今天遇到这样的问题&#xff1a; 点击的时候&#xff0c;同时选中 照抄官网&#xff01; 后来发现了问题&#xff1a; 也就是说如果你的版本太低&#xff0c;就不能用value&#xff0c;而得用label&#xff0c;于是修改 <el-radio-group v-model"searchTime"&g…

微服务系列六:分布式事务与seata

目录 实验环境说明 前言 一、分布式事务问题与策略 1.1 分布式事务介绍 1.2 分布式事务解决策略分析 二、分布式事务解决方案 Seata 2.1 认识Seata 2.2 Seata的工作原理 2.3 部署Seata微服务 2.3.1 准备数据库表 2.3.2 准备配置文件 2.3.3 docker部署 2.4 微服务集…

OceanBase 安装使用详细说明

OceanBase 安装使用详细说明 一、系统环境要求二、安装OceanBase环境方案一:在线下载并安装all-in-one安装包方案二:离线安装all-in-one安装包安装前的准备工作三、配置OceanBase集群编辑配置文件部署和启动集群连接到集群集群状态和管理四、创建业务租户和数据库创建用户并赋…

如何使用 SSH 连接并管理你的 WordPress 网站

在当今数字化的世界里&#xff0c;网站的管理与维护至关重要。对于使用 WordPress 搭建网站的用户而言&#xff0c;掌握基本的 SSH&#xff08;安全壳&#xff09;命令能够极大地简化网站管理工作。本指南将向你介绍 SSH 的基本知识&#xff0c;并教你如何通过 SSH 连接和管理你…

核心数据类型转换

核心数据类型转换 前言 前几集我们简单做了三条我们前后端交互接口的约定&#xff0c;简单看了我们的proto文件的内容&#xff0c;简单介绍了我们的Protobuf&#xff0c;并将protobuffer引入了我们的项目之中。 那么这一集我们就要把我们protobuffer的proto文件里的核心数据…

深入学习指针(5)!!!!!!!!!!!!!!!

文章目录 1.回调函数是什么&#xff1f;2.qsort使用举例2.1使用qsort函数排序整形数据2.2使用sqort排序结构数据 3.qsort函数的模拟实现 1.回调函数是什么&#xff1f; 回调函数就是⼀个通过函数指针调⽤的函数。 如果你把函数的指针&#xff08;地址&#xff09;作为参数传递…

MyBatis xml 文件中 SQL 语句的小于号未转义导致报错

问题现象 在 MyBatis 的 xml 文件中添加了一个 SQL 语句 <select id"countXxx" resultType"int">select count(*) from t1 where count < 3 </select>启动 Spring Boot 应用程序后报错&#xff1a; Caused by: org.apache.ibatis.builde…

测试-正交表与工具pairs的介绍使用(1)

目录 正交表 生成正交表 步骤 实操 注意事项 编写测试用例 根据正交表编写测试用例 补充遗漏的重要测试用例 正交表 关于长篇大论也不多介绍了&#xff0c;我们只需要知道正交法的⽬的是为了减少⽤例数⽬&#xff0c;⽤尽量少的⽤例覆盖输⼊的两两组合 正交表的构成&…

qt QErrorMessage详解

1、概述 QErrorMessage是Qt框架中用于显示错误消息的一个对话框类。它提供了一个简单的模态对话框&#xff0c;用于向用户显示错误或警告消息。QErrorMessage通常用于应用程序中&#xff0c;当需要向用户报告错误但不希望中断当前操作时。它提供了一个标准的错误消息界面&…

设计模式(主要的五种)

1.设计模式&#xff1a; 设计模式就是代码设计经验 2.设计模式的类型&#xff1a; 分为三大类&#xff1a;创建型模式&#xff0c;结构型模式&#xff0c;行为模式 创建型模式&#xff1a; 单例模式&#xff1a;某个类只能有一个实例&#xff0c;提供一个全局的访问点。 工…

ZABBIX API获取监控服务器OS层信息

Zabbix 是一款强大的开源监控解决方案,能够通过其 API 接口自动化管理和获取监控数据。在这篇文章中,详细讲解如何通过 Zabbix API 批量获取服务器的系统名称、IP 地址及操作系统版本信息,并将数据保存到 CSV 文件中。本文适合对 Python 编程和 Zabbix 监控系统有一定基础的…

医院信息化与智能化系统(18)

医院信息化与智能化系统(18) 这里只描述对应过程&#xff0c;和可能遇到的问题及解决办法以及对应的参考链接&#xff0c;并不会直接每一步详细配置 如果你想通过文字描述或代码画流程图&#xff0c;可以试试PlantUML&#xff0c;告诉GPT你的文件结构&#xff0c;让他给你对应…

场景解决方案丨迎战电商大促,企业管理跟踪驾驶舱助力中小企业打赢决胜之战

该方案已沉淀为➡️订单物流信息跟踪模板&#xff0c;点击&#x1f517;即可体验 随着互联网技术的发展和市场经济的变化&#xff0c;各行业的线上竞争愈发激烈。一方面&#xff0c;互联网平台凭借便捷的服务和丰富的产品吸引了大量客户&#xff1b;另一方面&#xff0c;复杂多…

Python练习12

Python日常练习 题目&#xff1a; 试题文件夹下的文本文件sentences.txt中&#xff0c;每行对应一个句子&#xff0c;该句子包含了多个 单词&#xff0c;单词之间以空格隔开。每个单词仅包含英文字母。我们希望能够将文件中的 每个句子转换为“山羊语”。句子的每…

DeFi 4.0峥嵘初现:主权金融时代的来临

近年来&#xff0c;Web3领域的创新似乎遇到了瓶颈&#xff0c;DeFi&#xff08;去中心化金融&#xff09;从热潮的巅峰逐渐进入了一个沉寂期。我们再也没有见到像DeFi Summer那样的行业兴奋&#xff0c;资本市场的动荡和Meme币的出现&#xff0c;似乎让人们忘记了曾经的区块链技…

如何通过六个步骤保护 AI 生成的代码

可以通过执行静态应用程序安全测试、动态安全测试、软件组合分析、使用安全代码实践、实施安全控制以及培训开发人员安全最佳实践来保护 AI 生成的代码。 随着人工智能 (AI) 在软件开发中发挥着越来越重要的作用&#xff0c;优先考虑 AI 生成的代码的安全性至关重要。 与人类…

51单片机教程(七)- 蜂鸣器

1 项目分析 利用P2.3引脚输出电平变化&#xff0c;控制蜂鸣器的鸣叫。 2 技术准备 1 蜂鸣器介绍 有绿色电路板的一种是无源蜂鸣器&#xff0c;没有电路板而用黑胶封闭的一种是有源蜂鸣器。 有源蜂鸣器和无源蜂鸣器 这里的“源”不是指电源。而是指震荡源。也就是说有源蜂鸣…

LangChain实际应用

1、LangChain与RAG检索增强生成技术 LangChain是个开源框架&#xff0c;可以将大语言模型与本地数据源相结合&#xff0c;该框架目前以Python或JavaScript包的形式提供&#xff1b; 大语言模型&#xff1a;可以是GPT-4或HuggingFace的模型&#xff1b;本地数据源&#xff1a;…

java的面向对象(从入门到深入)

目录 一、基本概念&#xff1a; 1.类 2.对象 3.继承 4.多态 5.封装 6.方法 7.接口 8.抽象 二、深入概念&#xff1a; 三:总结 一、基本概念&#xff1a; 1.类 类就是一个一个东西的蓝图&#xff0c;里面有着它的属性和方法。 2.对象 对象是一个类的实例化。 3.继承…

基础数据结构——队列(链表实现)

队列的性质 先进先出&#xff08;FIFO - First In First Out&#xff09;&#xff1a;最先加入队列的元素最先被移出后进后出&#xff08;后来的元素排在队尾&#xff09;只允许在队尾插入元素&#xff0c;在队首删除元素具有先来先服务的特点 链表实现队列 和之前创建链表相…