您当前的位置:首页 > 电脑百科 > 数据库 > 百科

SQL调优之Explain关键字详解

时间:2023-11-10 12:45:53  来源:  作者:

explAIn关键字可以模拟优化器执行 SQL 查询语句,从而知道 MySQL 是如何处理 SQL 语句的。分析查询语句或表结构的性能瓶颈。执行语句:explain + SQL语句。表头信息如下:

SQL调优之Explain关键字详解

explain各个字段代表的意思

  • id :select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序。
  • select_type :查询类型 或者是 其他操作类型。
  • table :正在访问哪个表。
  • partitions :匹配的分区。
  • type :访问的类型。
  • possible_keys :显示可能应用在这张表中的索引,一个或多个,但不一定实际使用到。
  • key :实际使用到的索引,如果为NULL,则没有使用索引。
  • key_len :表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。
  • ref :显示索引的哪一列被使用了,如果可能的话,是一个常数,哪些列或常量被用于查找索引列上的值。
  • rows :根据表统计信息及索引选用情况,大致估算出找到所需的记录所需读取的行数。
  • filtered :查询的表行占表的百分比。
  • Extra :包含不适合在其它列中显示但十分重要的额外信息。

ID 字段

select 查询的序列号,包含一组数字,表示查询中执行 select 子句或操作表的顺序。三种情况:

id 相同: 执行顺序由上而下:

explain select t2.* from t1,t2,t3 where t1.id = t2.id and t1.id = t3.id and t1.other_column = '';
  • 1.

SQL调优之Explain关键字详解

id 不同: 如果是子查询,id 序号会递增,id 越大优先级越高,越先被执行。

explain select t2.* from t2 where id = (select id from t1 where id = (select t3.id from t3 where t3.other_column = ''));
  • 1.

SQL调优之Explain关键字详解

id 相同不同同时存在: id 如果相同,可以认为是一组,由上往下执行;在所有组里 id 越大,优先级越高,越先执行。

explain select t2.* from (select t3.id from t3 where t3.other_column = '') s1,t2 where s1.id = t2.id;
  • 1.

SQL调优之Explain关键字详解

select_type 字段

主要用于区别普通查询、联合查询、子查询等的复杂程度。
SIMPLE: 简单的 select 查询,查询中不包含子查询或者 UNION。
PRIMARY: 查询中若包含任何复杂的自查询,最外层查询为 PRIMARY。

SQL调优之Explain关键字详解

SUBQUERY: 在 SELECT 或 WHERE 中包含子查询。
UNCACHEABLE SUBQUERY:一个子查询的结果不能被缓存,必须重新评估外链接的第一行。
DERIVED: 在 FROM 列表中包含的子查询被标记为 DERIVED(衍生)MySQL 会递归执行这些子查询,把结果放进临时表。

SQL调优之Explain关键字详解

UNION: 若第二个 SELECT 出现在 UNION 之后,则被标记为 UNION,若 UNION 包含在 FROM 子句的子查询,则外层SELECT 将被标记为 DERIVED。
UNION RESULT: 从 UNION表中获取结果的 SELECT。

table字段

显示这行数据是关于那张表

SQL调优之Explain关键字详解

type字段

首先说一下这个字段,要记住以下10个状态,(从左往右,越靠左边的越优秀)

NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL
  • 1.

NULL

MySQL能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引。存在这样一种情况,大家都知道索引是将数据在B+Tree中进行排序了,所以你的查询速率才这么高,那么B+树的最边上的叶子节点是不是要么是最大值要么是最小值啊?既然你都知道了,那MySQL比你更知道啊!当你要查询最大值或者最小值时,MySQL会直接到你的索引得分叶子节点上直接拿,所以不用访问表或者索引。

NULL的前提是你已经建立了索引。

SQL调优之Explain关键字详解

SYSTEM

表只有一行记录(等于系统表),这是const类型的特列,平时不大会出现,可以忽略。

const

表示通过索引一次就能找到,const用于比较 primary和 unique索引。因为只匹配一行数据,所以很快。

简单来说,const是直接按主键或唯一键读取。

SQL调优之Explain关键字详解

eq_ref

用于联表查询的情况,按联表的主键或唯一键联合查询。多表join时,对于来自前面表的每一行,在当前表中只能找到一行。这可能是除了system和const之外最好的类型。

SQL调优之Explain关键字详解

ref 可以用于单表扫描或者连接。如果是连接的话,驱动表的一条记录能够在被驱动表中通过非唯一(主键)属性所在索引中匹配多行数据,或者是在单表查询的时候通过非唯一(主键)属性所在索引中查到一行数据。

ref_or_null 类似ref,但是可以搜索值为NULL的行。

SQL调优之Explain关键字详解

index_merge

表示查询使用了两个以上的索引,最后取交集或者并集,常见and ,or的条件使用了不同的索引,官方排序这个在ref_or_null之后,但是实际上由于要读取多个索引,性能可能大部分时间都不如range。

SQL调优之Explain关键字详解

range

只检索给定范围的行,使用一个索引来选择行。一般where语句中出现between、<、>、in等的查询。这种范围扫描索引比全表扫描要好,因为只需开始索引的某一点,而结束另一点,不用扫描全部索引;

SQL调优之Explain关键字详解

index

Full Index Scan,index与 ALL区别为 index类型只遍历索引树,索引文件通常比数据文件小。index从索引中读取,而All是从硬盘读取;

SQL调优之Explain关键字详解

ALL

从磁盘中读取;如果一个查询的type是All,并且表的数据量很大,那么请解决它!!!

possible_keys字段

这个表里面存在且可能会被使用的索引,可能会在这个字段下面出现,但是一般都以key为准。

key字段

实际使用的索引,如果为null,则没有使用索引,否则会显示你使用了哪些索引,查询中若使用了覆盖索引(查询的列刚好是索引),则该索引仅出现在key列表。

ref字段

列与索引的比较,表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值。

rows字段和Filter字段

rows是根据表的统计信息和索引的选用情况,优化器大概帮你估算出你执行这行函数所需要查询的行数。

Filter是查询的行数与总行数的比值。其实作用与rows差不多,都是数值越小,效率越高。

Extra字段

Using index

表示相应的 select操作中使用了覆盖索引(convering index),避免访问了表的数据行,效率不错!

SQL调优之Explain关键字详解

覆盖索引(covering index ,或称为索引覆盖)即从非主键索引中就能查到的记录,而不需要查询主键索引中的记录,避免了回表的产生减少了树的搜索次数,显著提升性能。

Using temporary

表示MySQL需要使用临时表来存储结果集,常见于排序 order by和分组查询 group by。

Using fileSort

表示当SQL中有一个地方需要对一些数据进行排序的时候,优化器找不到能够使用的索引,所以只能使用外部的索引排序。

MySQL无法利用索引完成的排序操作称为“文件排序”。导致该问题的原因一般是Where条件和order by子句作用在了不同的列上,一般可以通过合适的索引来减少或者避免。(出现表示不好)

上面提到的常见情况,SQL语句通常写成这样select * from a where type = 5 order by id,这类语句一般会产生Using filesort这个选项,即使你在type和id上分别添加了索引。我们想一下它的工作过程,先根据type的索引从所有数据信息中挑选出满足type = 5条件的,然后根据id列的索引信息对挑选的数据进行排序,所以产生了Using filesort选项。可以通过联合索引解决这个问题,即在type, id两列上建立一个联合索引。

Using where

查询的列未被索引覆盖,where筛选条件非索引的前导列,Extra 中为 Using where。

SQL调优之Explain关键字详解

 

所谓前导列,就是在创建复合索引语句的第一列或者连续的多列。比如通过:CREATE INDEX comp_ind ON table1(x, y, z)创建索引,那么x,xy,xyz都是前导列,而yz,y,z这样的就不是。

using where,using index

查询的列被索引覆盖,并且 where筛选条件是索引列之一但是不是索引的前导列,Extra中为Using where; Using index,意味着无法直接通过索引查找来查询到符合条件的数据;

SQL调优之Explain关键字详解

查询的列被索引覆盖,并且where筛选条件是索引列前导列的一个范围,同样意味着无法直接通过索引查询到符合条件的数据

SQL调优之Explain关键字详解

Using index condition

查询的列不全在索引中,where条件中是一个前导列的范围

SQL调优之Explain关键字详解

查询列不完全被索引覆盖,查询条件完全可以使用到索引(进行索引查找)

SQL调优之Explain关键字详解

NULL(既没有Using index,也没有Using where Using index,也没有using where)。

查询的列未被索引覆盖,并且where筛选条件是索引的前导列,意味着用到了索引,但是部分字段未被索引覆盖,必须通过“回表”来实现,不是纯粹地用到了索引,也不是完全没用到索引,Extra中为NULL(没有信息)。

SQL调优之Explain关键字详解

Using join buffer

使用了连接缓存。

小表驱动大表

在表连接过程中。一般选择小表作为驱动表,大表作为被驱动表。

驱动表(小表)的连接字段无论建立没建立索引都需要全表扫描的。被驱动表(大表)如果在连接字段建立了索引,则可以走索引。如果没有建立索引则也需要全表扫描。

 

两张表连接的情况

被驱动表的连接字段有索引:主键索引

对于驱动表中的每一条数据,到被驱动表的聚簇索引上寻找其对应的数据。

被驱动表的连接字段有索引:二级索引

对于驱动表上的每一条数据,到被驱动表的二次索引上寻找其对应的数据id,然后再根据数据id到聚簇索引上寻找对应的数据。

被驱动表的连接字段没有索引

对于驱动表上的每一条数据,都要到被驱动表上进行一次全表遍历,找到对应的数据。

join buffer的作用

就是针对被驱动表的连接字段没有索引的情况下需要进行全表扫描,所以引入了join buffer内存缓冲区来对这个全表扫描过程进行优化。

impossible where

where子句总是false,不能用来获取任何元素。即筛选条件没能筛选出任何数据。

select tables optimized away

在没有 GROUPBY 子句的情况下,基于索引优化 MIN/MAX操作。

distinct

优化 distinct操作。在找到第一匹配的时候就停止找同样的动作。



Tags:SQL调优   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,不构成投资建议。投资者据此操作,风险自担。如有任何标注错误或版权侵犯请与我们联系,我们将及时更正、删除。
▌相关推荐
SQL调优之Explain关键字详解
explain关键字可以模拟优化器执行 SQL 查询语句,从而知道 MySQL 是如何处理 SQL 语句的。分析查询语句或表结构的性能瓶颈。执行语句:explain + SQL语句。表头信息如下:explain...【详细内容】
2023-11-10  Search: SQL调优  点击:(198)  评论:(0)  加入收藏
MySQL调优工具:MySQLtuner.pl及tuning-primer.sh
一、概述MySQL调优工具是用于分析和优化MySQL数据库性能的软件工具。它们可以帮助识别潜在的性能瓶颈、优化查询性能、调整配置参数以及提高数据库的吞吐量和响应时间。今天...【详细内容】
2023-10-20  Search: SQL调优  点击:(278)  评论:(0)  加入收藏
Oracle数据库性能优化指南:从SQL调优到表分区,全面提升
当涉及到Oracle数据库性能优化时,以下是一些重要的方面: SQL查询性能调优。 编写高效的SQL查询语句,避免使用不必要的表连接、子查询等。 使用适当的谓词和操作符,以减少数据检...【详细内容】
2023-09-11  Search: SQL调优  点击:(266)  评论:(0)  加入收藏
MySQL调优实践最全!必懂!知识点一站式掌握
一.索引 什么是索引什么是索引?提到索引, 我们想到的是查询慢了 设置索引呗!但是索引为什么起作用?设置了索引为什么还是慢我们其实不是很清楚。在关系数据库中,索引是一种数据结...【详细内容】
2023-06-15  Search: SQL调优  点击:(252)  评论:(0)  加入收藏
▌简易百科推荐
向量数据库落地实践
本文基于京东内部向量数据库vearch进行实践。Vearch 是对大规模深度学习向量进行高性能相似搜索的弹性分布式系统。详见: https://github.com/vearch/zh_docs/blob/v3.3.X/do...【详细内容】
2024-04-03  京东云开发者    Tags:向量数据库   点击:(4)  评论:(0)  加入收藏
原来 SQL 函数是可以内联的!
介绍在某些情况下,SQL 函数(即指定LANGUAGE SQL)会将其函数体内联到调用它的查询中,而不是直接调用。这可以带来显著的性能提升,因为函数体可以暴露给调用查询的规划器,从而规划器...【详细内容】
2024-04-03  红石PG  微信公众号  Tags:SQL 函数   点击:(3)  评论:(0)  加入收藏
如何正确选择NoSQL数据库
译者 | 陈峻审校 | 重楼Allied Market Research最近发布的一份报告指出,业界对于NoSQL数据库的需求正在持续上升。2022年,全球NoSQL市场的销售额已达73亿美元,预计到2032年将达...【详细内容】
2024-03-28    51CTO  Tags:NoSQL   点击:(13)  评论:(0)  加入收藏
为什么数据库连接池不采用 IO 多路复用?
这是一个非常好的问题。IO多路复用被视为是非常好的性能助力器。但是一般我们在使用DB时,还是经常性采用c3p0,tomcat connection pool等技术来与DB连接,哪怕整个程序已经变成以...【详细内容】
2024-03-27  dbaplus社群    Tags:数据库连接池   点击:(12)  评论:(0)  加入收藏
八个常见的数据可视化错误以及如何避免它们
在当今以数据驱动为主导的世界里,清晰且具有洞察力的数据可视化至关重要。然而,在创建数据可视化时很容易犯错误,这可能导致对数据的错误解读。本文将探讨一些常见的糟糕数据可...【详细内容】
2024-03-26  DeepHub IMBA  微信公众号  Tags:数据可视化   点击:(6)  评论:(0)  加入收藏
到底有没有必要分库分表,如何考量的
关于是否需要进行分库分表,可以根据以下考量因素来决定: 数据量和负载:如果数据量巨大且负载压力较大,单一库单一表可能无法满足性能需求,考虑分库分表。 数据增长:预估数据增长...【详细内容】
2024-03-20  码上遇见你  微信公众号  Tags:分库分表   点击:(13)  评论:(0)  加入收藏
在 SQL 中写了 in 和 not in,技术总监说要炒了我……
WHY?IN 和 NOT IN 是比较常用的关键字,为什么要尽量避免呢?1、效率低项目中遇到这么个情况:t1表 和 t2表 都是150w条数据,600M的样子,都不算大。但是这样一句查询 &darr;select *...【详细内容】
2024-03-18  dbaplus社群    Tags:SQL   点击:(5)  评论:(0)  加入收藏
应对慢SQL的致胜法宝:7大实例剖析+优化原则
大促备战,最大的隐患项之一就是慢SQL,对于服务平稳运行带来的破坏性最大,也是日常工作中经常带来整个应用抖动的最大隐患,在日常开发中如何避免出现慢SQL,出现了慢SQL应该按照什...【详细内容】
2024-03-14  京东云开发者    Tags:慢SQL   点击:(4)  评论:(0)  加入收藏
过去一年,我看到了数据库领域的十大发展趋势
作者 | 朱洁策划 | 李冬梅过去一年,行业信心跌至冰点2022 年中,红衫的一篇《适应与忍耐》的报告,对公司经营提出了预警,让各个公司保持现金流,重整团队,想办法增加盈利。这篇报告...【详细内容】
2024-03-12    InfoQ  Tags:数据库   点击:(25)  评论:(0)  加入收藏
SQL优化的七个方法,你会哪个?
一、插入数据优化 普通插入:在平时我们执行insert语句的时候,可能都是一条一条数据插入进去的,就像下面这样。INSERT INTO `department` VALUES(1, &#39;研发部(RD)&#39;, &#39...【详细内容】
2024-03-07  程序员恰恰  微信公众号  Tags:SQL优化   点击:(19)  评论:(0)  加入收藏
站内最新
站内热门
站内头条