MySQL的explain,你真的会用吗?

EXPLAIN字段详解&测试        测试数据库来自mysql示例数据库(https://launchpad.net/test-db)。所用的表为sakila.film以及sakila.film_actor和sakila.language。表结构分别为:       CREATE TABLE `film` (  `film_id` smallint(5) unsigned NOT NULLAUTO_INCREMENT, ... 阅读详情

如果问 MySQL的索引优化如何做,很多人知道用 explain 命令,但是里面的门道你是否也清楚?

引言

数据库性能优化是每个后端程序猿必备的基础技能之一,而Mysql中的explain堪称Mysql的性能优化分析神器,我们可以通过它来分析SQL语句的对应的执行计划在Mysql底层到底是如何执行的,它对于我们评估SQL的执行效率以及确定Mysql的性能优化方向具有重要的意义。

但是很多同学对于如何根据explain对已有SQL进行深度的执行分析还是丈二和尚摸不着头脑,因此本文详细阐述通过explain分析定位数据库性能问题。

explain基础

对于每个SQL来说,当它被客户端发送到Mysql服务端之后,会经过Mysql的优化器部件的分析,主要包括一些特殊的处理、执行顺序的改变以确保最优的执行效率,最终生成对应的执行计划。所谓的执行计划,实际就是在存储引擎层面如何获取数据的,是通过索引获取数据还是进行全表扫描获取数据,获取到数据后需不需要回表,等等,简单理解就是Mysql获取数据的过程。

接下来我们来详细看下,这个explain到底是何方神圣,为什么能指导我们进行性能优化。当我们执行如下语句:

explain SELECT * FROM user_info where NAME='mufeng'explain SELECT * FROM user_info where NAME='mufeng'

执行explain语句之后,我们会得到如下的执行结果,这个类似数据库表的12个字段实际上就是对Mysql执行怎样的执行计划的详细描述。下面我们来好好研究下这12个字段分别代表什么意思,只有搞清楚它们的含义,我们才能明确Mysql到底是怎么执行数据查询的。

1、id

实际上每次select查询都会对应一个id,它代表着SQL执行的顺序,如果id值越大,说明对应的SQL语句执行的优先级越高。在一些复杂的查询SQL语句中常常包含一些子查询,那么id序号就会递增,如果出现嵌套查询,我们可以发现最里层的查询对应的id最大,因此也优先被执行。

如上图所示,SQL查询语句中,第一个执行计划的id为1,第二个执行计划的id为2,id为1的执行计划对应的table为order,id为2的执行计划对应的table是user_info,结合SQL语句,我们知道先执行子查询select id from user_info,而后再执行关于表order的数据查询。

2、select_type

select_type表示的执行计划的对应的查询是什么类型,常见的查询类型主要包括普通查询、联合查询以及子查询等。SIMPLE(查询语句为简单的查询不包含子查询)、PRIMARY(当查询语句中包含子查询的时候,对应最外层的查询类型)、UNION(union之后出现的select语句对应的查询类型会标记此类型)、SUBQUERY(子查询会被标记为此类型)、DEPENDENT SUBQUERY(取决于外面的查询 )。

3、table

table代表表名称,表示要查询哪张表。当然不一定是真实的表的名称,也可能是表的别名或者临时表。

4、partitions

partitions代表的是分区的概念,表示在进行查询时,如果对应的表存在分区表,那么这里就会显示具体的分区信息。

5、type

type是非常核心的属性,需要重点掌握。它表示的是当前通过什么样的方式对数据库表进行分访问。

(1)system

该表只有一行(相当于系统表),数据量很小,查询速度很快,system是const类型的特例。

(2)const

如果type是const,说明在进行数据查询的时候,命中了primary key或唯一索引,此类数据查询速度非常快。

(3)eq_ref

在进行数据查询的过程中,如果SQL语句中在表连接情况下可以基于聚簇索引或者非null值的唯一索引记性数据扫描,那么此时type对应的值就会显示为eq_ref。

(4)ref

数据查询的时候如果命中的索引是二级索引不是唯一索引,测试查询速度也会很快,但是type是ref。另外如果是多字段的联合索引,那么根据最左匹配原则,从联合索引的最左侧开始连续多个列的字段进行等值比较也是ref的类型。

(5)ref_or_null

这种连接类型类似于 ref,区别在于 MySQL会额外搜索包含NULL值的行。

(7)unique_subquery

在where条件中的关于in的子查询条件集合

(8)index_subquery

区别于unique_subquery,用于非唯一索引,可以返回重复值。

(9)range

使用索引进行行数据检索,只对指定范围内的行数据进行检索。换句话说就是针对一个有索引的字段,在指定范围中检索数据。在where语句中使用 bettween...and、<、>、<=、in 等条件查询 type 都是 range。

(10)index

Index 与ALL 其实都是读全表,区别在于index是遍历索引树读取,而ALL是从硬盘中读取。

(11)all

遍历全表进行数据匹配,此时的数据查询性能最差。

6、possible_keys

表示哪些索引可以被Mysql的优化器进行选择,也就是索引候选者有哪些。

7、key

在possible_keys中实际选择的索引

8、key_len

表示索引的长度,和实际的字段属性以及是否为null都有关系。

9、ref

当使用字段进行常量等值查询时ref此处为const,当查询条件中使用了表达式或者函数则ref显示为func,则其他的显示为null。

10、rows

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

11、filtered

filtered 这个是一个百分比的值,表里符合条件的记录数的百分比。简单点说,这个字段表示存储引擎返回的数据在经过过滤后,剩下的记录满足条件的记录数量的比例。

12、extra

在其他列不显示额外信息在此列进行展示。

(1)Using index

在进行数据查询的时候,数据库使用了覆盖索引,就是查询的列被索引覆盖,使用到覆盖索引查询速度会非常快。不是使用select * ,而是使用select phone_number,就会用到覆盖索引。

(2)Using where

查询时未找到可用的索引,进而通过where条件过滤获取所需数据,但要注意的是并不是所有带where语句的查询都会显示Using where。

(3)Using temporary

表示查询后结果需要使用临时表来存储,一般在排序或者分组查询时用到。

(4)Using filesort

此类型表示无法利用索引完成指定的排序操作,也就是ORDER BY的字段实际没有索引,因此此类SQL是需要进行优化的。

explain分析实战

上文中我们阐述了explain在分析SQL语句时,可以通过12个属性来分析SQL的大致执行过程,并以此来判断SQL存在的性能问题。那么接下来我们通过一个实际的例子,来具体看下如何结合explain来实现SQL的性能分析。

其实所谓的Mysql性能问题,大部分都指的是平台出现了慢查询问题。慢查询实际上是可以通过配置进行记录的,把执行时间超过某个设定的阈值的sql都记录下来,当出现问题的时候可以通过记录的慢查询日志进行问题的定位。但是有的时候,出现大量慢查询会导致数据库连接被占满,导致整个平台的出现异常。

实际上我们在产品评价表product_evaluation中是建立了索引的,正常来说应该是可以使用到对应的索引字段进行查询的。但是实际上查询耗时有几十秒的时间,远远超过我们的预期。那我们猜测是不是由于某种原因导致Mysql优化器没有选择对应的索引进行数据检索,最后造成慢查询的发生。到底执行计划是怎样的,还是得借助于explain来看下。

如上文所说,虽然explain有12个字段属性帮助我们进行执行计划的分析,但是实际上常用的核心字段也就几个。我们可以看的出来在possible_key中实际上包含了我们设置的索引的,但是实际上Mysql却选择了PRIMARY作为其实际使用的。那么问题来了,为什么明明设置了索引,但是实际并没有用上,被Mysql吃了吗?另外为什么之前的业务中没有出现这个问题,而现在出现了?我们需要进行进一步的分析。

我们所建立的idx_evaluation_type实际上是一个二级索引(叶子节点是主键id),对于数千万一张的大表来说,实际上这个二级索引也是非常大的,而且这个字段本身的值就三个,变化不大。因此Mysql的优化器在分析这个SQL的时候发现,如果按照SQL中的索引来获取数据后再根据where条件进行筛选,筛选后的数据还需要回表到聚簇索引中获取实际的数据。

假如通过二级索引筛选出来的数据有几万条,而后还需要进行排序,这些操作都是基于临时磁盘我恩建进行的,Mysql判断这种方式的性能可能会很差,因此优化器放弃了原有的数据查询方式,直接通过主键id对应的聚簇索引来进行数据的获取,因为id本身就是有序的。

那么知道了查询慢的原因,我们应该怎么进行优化呢?实际上可以在SQL语句中增加force idnex,强制Mysql使用我们设置的二级索引。

SELECT * FROM product_evaluation force index(idx_product_id)WHERE product_id =1 and evaluation_type='GOOD' ORDER BY id desc LIMIT 200SELECT * FROM product_evaluation force index(idx_product_id)WHERE product_id =1 and evaluation_type='GOOD' ORDER BY id desc LIMIT 200

总结

通过上文对于explain使用的介绍,大家在遇到慢SQL问题的时候,可以先通过explain来进行初步的分析,主要明确SQL在Mysql中实际的执行过程是怎样的,如果查询字段没有索引则增加索引,如果有索引就要分析为什么没有用到索引。只要明确具体的执行过程,我们才能确定具体的查询优化方案。

MySQL EXPLAIN 详解 EXPLAIN 是 MySQL 提供的性能分析工具,用于查看 SQL 查询的执行计划(即优化器如何执行查询)。它通过模拟查询过程(不实际执行 SQL)返回一个表格,展示索引使用、表连接方式、扫描行数等关键信息,帮助开发者诊断性能瓶颈并优化查询。核心作用:基本语法: FORMAT:指定输出格式,默认是表格形式(TRADITIONAL),JSON 格式包含更详细信息。 示例: 输出结果示例:性能瓶颈难以定位索引优化盲目性资源浪费与扩展性问题维护成本高必用 EXPLAIN 的场景:快速优化步骤:通过 EXPL 阅读详情

相关推荐

【Telephony】Telephony总纲!Phone进程流程(AOSP)

整个Framework层的Telephony框架运行在一个叫做Phone(com.android.phone)的进程中。 Phone进程启动的过程中,会初始化非常多的分支功能,SIM卡流程初始化就是其中之一,所以我觉得有必要写一下Phone进程的框架

weixin_44513784的博客 1981

Mysql的explain,你真的会用吗?

数据库性能优化是每个后端程序猿必备的基础技能之一,而Mysql中的explain堪称Mysql的性能优化分析神器,我们可以通过它来分析SQL语句的对应的执行计划在Mysql底层到底是如何执行的,它对于我们评估SQL的执行效率以及确定Mysql的性能优化方向具有重要的意义。但是很多同学对于如何根据explain对已有SQL进行深度的执行分析还是丈二和尚摸不着头脑,因此本文详细阐述通过explain分析定位数据库性能问题。

3234

labview设置工业摄像头外部触发

使用labview对海康MV-CA013-GM相机设置外部触发。 一、硬件部分 1、硬件引脚定义 2、触发模式设置 相机的触发模式分为两种,一种是内触发模式、另外一种是外触发模式。 3、接线图 信号线的地要跟给信号的设备共地。信号线给高电平时触发拍照。 二、软件部分 1、创建vision acquisition 2、选择使用的摄像头。 3、选择模式。 4、在Acquisition Control中设置这些选项。 5、设置保存图片路径 设置保存路径和保存图片的格式。点击完成即可 参考:https:/

qq_43493715的博客 5668

Mysql性能分析-Explain

Mysql性能分析-Explain 一·Explain是什么 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是 如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈 二·Explain分析字段解释 id 含义: 1)select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序 三种情况: 1)id相同,自上而下顺序执行 2)id不同,如果是子查询,id序号会递增,id值越大优先级越高,越先被执行 3)id相同不同同时存在,按id值分组

zyk1.的博客 516

【全覆盖算法】栅格地图实现布氏单元分解算法+栅格地图实现牛耕Boustrophedon Cellular Decomposition Path Planning

本文先对做个简单的介绍,具体内容后续再更,其他模块可以参考去我其他文章提示:以下是本篇文章正文内容。

qq_35635374的博客 282

安全与加密常识(10)linux系统证书管理

1. 将自定义CA证书(如my-ca.crt)放到源目录# 2. 编辑配置文件,启用该证书(末尾添加一行 my-ca.crt)# 3. 执行命令同步(核心工作环节)# 4. 验证结果:检查是否生成哈希链接和合并到证书束ls /etc/ssl/certs/my-ca.pem # 证书文件ls /etc/ssl/certs/$(openssl x509 -hash -noout -in /usr/share/ca-certificates/my-ca.crt).0 # 哈希链接。

二进制君 293

MySQL性能分析之explain

explain分析 EXPLAIN SELECT * FROM order WHERE id>2 AND order_sn<>'' 结果: 说明: id:一组数字,操作顺序,如果id相同,则执行的顺序就由上至下,如果是子查询,id的序号递增,值越大优先级越高,越先被执行; select_type:表示每个字句的类型,简单还是复杂,取值如下: 参数 描述 s...

绮罗郁金香的博客 210

EtherCAT主站SOEM -- 0 -- 该EtherCAT主站SOEM专栏后续安排

SOEM(Simple Open EtherCAT Master)是一个开源的EtherCAT主站库,允许开发者构建自定义的EtherCAT主站应用程序。EtherCAT是一种实时以太网通信协议,广泛应用于工业自动化和机器控制领域。开源性质:SOEM是一个开源项目,这意味着开发者可以自由地查看、修改和分发代码。这使其成为工业自动化领域的一个强大工具,可以根据具体需求进行定制。EtherCAT主站实现:SOEM库提供了一个完整的EtherCAT主站实现,允许主站与多个EtherCAT从站设备进行通信。

常驻客栈的博客--嵌入式EtherCAT 6681

mysql explain中的rows和limit有关系吗?

翻译过来就是,这个方法仅仅根据给出的关于这个索引的条件和索引本身,来判断需要扫描多少行。可是从DBA的直觉来说,id字段为主键,且为自增属性,另外后面有个limit 1,那么无论如何rows应该不大于1才对。而check_quick_select的功能,在MySQL源码中的注释为。为了避免不擅长的大段落描述,我把几个关键的文件和函数粘贴出来。这周五下班前,发现了一个奇怪问题,大概是这个背景。果然后面的limit 1根本不影响rows的值。总共有37K rows的数据,数据大概是这样。所以新姿势,get!

weixin_49505075的博客 344

SAP Gateway 中事务码 /IWFND/MAINT_SERVICE 的用法介绍

本文系统梳理了SAP Gateway中/IWFND/MAINT_SERVICE事务码的核心功能与运维实践。作为OData服务管理中樞,它支持服务注册激活、ICF节点配置、系统别名绑定、批量处理等关键操作。文章详解了服务目录、ICF节点、系统别名的配置要点,提供了批量激活、缓存清理、性能监控等实用技巧,并针对常见陷阱(如传输配置、删除残留)给出解决方案。通过制造业批量激活、零售业OAuth集成等案例,展示了事务码在企业级应用中的实际价值,为SAP Gateway运维提供了一套完整的治理清单。

2007 年 ~ 2025 年,深耕 SAP 技术 18 年 314

5步VS传统WinForm!AntdUI让界面设计从“土味”到“超模”只需30秒?

摘要:墨瑾轩编程教程《AntdUI的5大魔法步骤》详解如何将传统WinForm升级为现代化UI。教程通过5个核心步骤:安装AntdUI库、控件替换、主题定制、复杂布局构建及实战案例演示,对比传统开发与AntdUI在美观度(动态阴影/动画)、开发效率(拖拽组件VS手工绘制)和主题定制(一键换肤VS重写控件)的显著差异。使用C#代码示例展示按钮动画、3D翻转、聊天界面等特效实现,帮助开发者快速掌握这一基于Ant Design风格的WinForm增强框架。(150字)

java专栏 1129

Mysql优化之explain你真的会吗?

一、explain是什么? 1、 定义 EXPLAIN是mysql中的一个命令,可以模拟优化器执行SQL语句并返回执行计划。通过执行计划,我们可以分析查询语句或表结构的性能瓶颈,从而进行SQL优化。 2、用法 mysql> explain select * from employee; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+

笑不语的博客 304

28-3 文件上传漏洞 -白盒审计绕过

这行代码是用来获取文件名中最后一个点(.)后的部分,也就是文件的后缀名。这个函数会返回以最后一个点(.)开始到字符串末尾的部分,包括点(.)本身。然而,这段代码存在一个潜在的安全风险。攻击者可以利用多个点(.)来构造恶意文件名,以绕过后缀名检查。切换到bp将抓到的上传请求发送到重发器然后改包。先上传一个普通的php文件然后使用bp抓包。

weixin_43263566的博客 392

仅仅会用MySQL的EXPLAIN还不够,还需要会用EXPLAIN ANALYZE

在 MySQL 8.0.18 中,MySQL 引入了 EXPLAIN ANALYZE,一个在常规 EXPLAIN 查询计划工具之上的新功能。除了列出查询计划和估算的成本,EXPLAIN ANALYZE 还打印了执行计划中各个迭代器的实际成本。示例EXPLAIN ANALYZE语句:注意事项:EXPLAIN ANALYZE 实际上会运行查询,因此如果你不希望查询在实时数据库上运行,请不要使用 EXPLAIN ANALYZE。估算的执行成本(一些迭代器未被成本模型纳入,因此在估算中未包含它们)

程序新视界 1577

NTscan扫描工具

ntscan是一个扫描器,可以扫描没有密码和弱密码的共享,先到配置文件中填写猜测的用户名和密码,扫描过程中右侧出现的是扫描日志。

精雕软件(JDpaint) 5.5 绿色版

精雕软件(JDpaint) 5.5 绿色版精雕软件(JDpaint) 5.5 绿色版精雕软件(JDpaint) 5.5 绿色版

你真的会用EXPLAIN么,SQL性能优化王者晋级之路

MySQL的开发人员或者DBA,经常使用EXPLAIN语句来查看SQL的执行计划。EXPLAIN的解读文章多如牛毛,每个开发人员对EXPLAIN结果都有自己的理解。然而,你真的会使用EXPLAIN吗? 使用`explain for connection`可以查看指定会话的SQL执行计划。这样就可以看到当前正在实际执行的SQL的执行计划。MySQL 8.0引入的`EXPLAIN ANALYZE`向我们清晰展示了SQL的估算执行成本和实际执行消耗,对于优化SQL性能有着非常大的价值。

真水无香,大象无形,做本分的自己,记录技术点滴 8128

jre9下载 jdk9

(Java SE Runtime Environment 9.0) 9.0u175 官方最新版 【x86|x64】

Explain你真的会用吗?

文章目录Explain的作用?idid相同id不同id相同又不同select_typeSIMPLEPRIMARYSUBQUERYDERIVEDUNIONUNION RESULTtabletype Explain的作用? id id相同 id不同 id相同又不同 select_type SIMPLE PRIMARY SUBQUERY DERIVED UNION UNION RESULT table type ...

沸羊羊_ 2502

jdk-8u144-linux-x64.tar.gz.

该资源为搭建Linux系统Java环境用,资源的后缀为tar.gz格式的压缩包

【最全最详细explain讲解】explain | 索引优化的这把绝世好剑,你真的会用吗?

对于互联网公司来说,随着用户量和数据量的不断增加,慢查询是无法避免的问题。一般情况下如果出现慢查询,意味着接口响应慢、接口超时等问题。如果是高并发的场景,可能会出现数据库连接被占满的情况,直接导致服务不可用。慢查询的确会导致很多问题,我们要如何优化慢查询呢?主要解决办法有:监控sql执行情况,发邮件、短信报警,便于快速识别慢查询sql打开数据库慢查询日志功能简化业务逻辑代码重构、优化异步处理sql优化索引优化其他的办法先不说,后面有机会再单独介绍。

wufaqidong1的博客 5320

<explain>索引优化的这把绝世好剑,你真的会用吗?

对于互联网公司来说,随着用户量和数据量的不断增加,慢查询是无法避免的问题。一般情况下如果出现慢查询,意味着接口响应慢、接口超时等问题。如果是高并发的场景,可能会出现数据库连接被占满的情况,直接导致服务不可用。 慢查询的确会导致很多问题,我们要如何优化慢查询呢?...

人生若只初相见@的博客 1万+

MySQL中EXPLAIN详解

EXPLAIN作为MySQL的性能分析神器,读懂其结果是很有必要的,然而我在各种搜索引擎上竟然找不到特别完整的解读。都是只有重点,没有细节(例如type的取值不全、Extra缺乏完整的介绍等)。 文章目录 ...

wh柒八九的博客 5921
上一篇: 还在实体类中用Date?JDK8新的日期类型不香么?
下一篇: Java虚拟机(JVM)超详细面试题
Java5658
博客等级 码龄4年 169粉丝 107原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值