Mysql 的 Explain性能分析

最新推荐文章于 2024-09-19 16:23:27 发布
最新推荐文章于 2024-09-19 16:23:27 发布
阅读量171 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_43688472/article/details/106570345
版权

Mysql 的 Explain性能分析

概念

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

用法： Explain+SQL语句。

Explain执行后返回的信息：

在这里插入图片描述

Explain性能分析

id

select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序。
①id相同，执行顺序由上至下

②id不同，id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

③有相同也有不同

在这里插入图片描述
id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行衍生 = DERIVED

关注点：id号每个号码，表示一趟独立的查询。一个sql 的查询趟数越少越好。

select_type

select_type代表查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

在这里插入图片描述

table

这个数据是基于哪张表的。

type

type是查询的访问类型。是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ，一般来说，得保证查询至少达到range级别，最好能达到ref。

SIMPLE
SIMPLE代表单表查询；

PRIMARY
查询中若包含任何复杂的子部分，最外层查询则被标记为Primary。

在这里插入图片描述

DERIVED
在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询, 把结果放在临时表里。

SUBQUERY
在SELECT或WHERE列表中包含了子查询。

DEPENDENT SUBQUERY
在SELECT或WHERE列表中包含了子查询,子查询基于外层。

在这里插入图片描述

都是where后面的条件，subquery是单个值，dependent subquery是一组值。

UNCACHEABLE SUBQUREY

当使用了@@来引用系统变量的时候，不会使用缓存。

UNION
若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED。

在这里插入图片描述

UNION RESULT
从UNION表获取结果的SELECT。

table

这个数据是基于哪张表的。

type

type是查询的访问类型。是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ，一般来说，得保证查询至少达到range级别，最好能达到ref。

system
表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计

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

如将主键置于where列表中，MySQL就能将该查询转换为一个常量。

eq_ref
唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

ref
非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

没用索引前：

在这里插入图片描述

建立索引后：

在这里插入图片描述

range
只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

在这里插入图片描述

index
出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组。

all
Full Table Scan，将遍历全表以找到匹配的行。

index_merge
在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的sql中。

ref_or_null
对于某个字段既需要关联条件，也需要null值得情况下。查询优化器会选择用ref_or_null连接查询。

index_subquery
利用索引来关联子查询，不再全表扫描。
在这里插入图片描述

unique_subquery
该联接类型类似于index_subquery。 子查询中的唯一索引。

备注：一般来说，得保证查询至少达到range级别，最好能达到ref。

possible_keys

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key

实际使用的索引。如果为NULL，则没有使用索引。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 key_len字段能够帮你检查是否充分的利用上了索引。ken_len越长，说明索引使用的越充分。

在这里插入图片描述

如何计算：

①先看索引上字段的类型+长度比如 int=4 ; varchar(20) =20 ; char(20) =20

②如果是varchar或者char这种字符串字段，视字符集要乘不同的值，比如utf-8 要乘 3,GBK要乘2，

③varchar这种动态字符串要加2个字节

④允许为空的字段要加1个字节

第一组：key_len=age的字节长度+name的字节长度=4+1 + ( 20*3+2)=5+62=67

第二组：key_len=age的字节长度=4+1=5
在这里插入图片描述

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

rows

rows列显示MySQL认为它执行查询时必须检查的行数。越少越好！

  1. Extra
    其他的额外重要的信息。

12.1 Using filesort
说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。

出现filesort的情况：

在这里插入图片描述

优化后，不再出现filesort的情况：

在这里插入图片描述

查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。

Using temporary

使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

优化前：
在这里插入图片描述

优化后：

在这里插入图片描述

Using index

Using index代表表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！

如果同时出现using where，表明索引被用来执行索引键值的查找;如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。

利用索引进行了排序或分组。

Using where

表明使用了where过滤。

Using join buffer

使用了连接缓存。

impossible where

where子句的值总是false，不能用来获取任何元组。

select tables optimized away

在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

在innodb中：
在这里插入图片描述

在Myisam中：

在这里插入图片描述

墨卿风竹
关注
MYSQL 性能分析器 EXPLAIN 用法实例分析
01-19
本文实例讲述了MYSQL 性能分析器 EXPLAIN 用法。分享给大家供大家参考，具体如下： 使用方法： EXPLAIN SELECT * FROM user; 环境和数据准备 -- 查看 MySQL 版本 SELECT VERSION(); -- MySQL 提供什么存储引擎 ...
MySQL性能分析及explain的使用说明
09-11
### MySQL性能分析及explain的使用说明 #### 一、使用explain语句查看分析结果 `EXPLAIN` 是MySQL提供的一种非常有用的工具，用于帮助我们理解SQL查询是如何执行的，这有助于我们优化查询性能。当我们使用 `...
学习使用mysql explain进行性能分析及优化
ruijiao_ren的博客
03-12 282
(未完，待续。。。)转载并参考自：http://www.itpub.net/thread-1034410-1-2.htmlhttps://segmentfault.com/a/1190000008131735https://www.cnblogs.com/yycc/p/7338894.htmlhttp://blog.csdn.net/zly9923218/article/details/51007...
Using temporary与Using filesort
热门推荐
祈雨v的博客
06-13 4万+
通过explain查看sql的执行计划时，Extra字段的值往往会看到Using where; Using index; Using temporary; Using filesort，其中此次重点关注Using temporary; Using filesort。 Using temporary Using temporary表示由于排序没有走索引、使用union、子查询连接查询、使用某些视图等...
MySql调优（三）Query SQL优化（2）explain优化
最新发布
w_t_y_y的博客
09-19 1609
6、索引优化：上面讲到子查询的优化，以及如何建立索引，而且在多个字段索引时，分别对字段建立了单个索引，后面发现其实建立联合索引效率会更高，尤其是在数据量较大，单个列区分度不高的情况下。这一条虽然order by后面的字段和索引中字段顺序相同，但是一个是降序，一个是升序，所以也会产生using filesort，同时升序和同时降序就不会产生using filesort了。order by后面的字段顺序不符合组合索引中的顺序，所以order by后面的不会走索引，即会产生using filesort。
mysql explain中的select tables optimized away
weixin_33928467的博客
06-04 407
mysql> explain SELECT max( up_start ) AS up_start FROM test WHERE up_start > '2008-01-19 00:00:00' and up_start < '2008-01-19 23:59:59';+----+-------------+-------+------+---------...
Mysql 调优记： INNER JOIN查询 Using temporary; Using filesort 问题优化
不能说的秘密的博客
03-17 1万+
近期笔者在生产环境中发现一条执行非常慢的sql。大概时间为5s左右，于是乎对改SQL场景进行EXPLAIN 分析，发现一个在执行过程中出现对“Using temporary; Using filesort ”。即在执行过程中产生了临时表来存储结果，并在排序时根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序。经过合理对优化，最终EXPLAIN后，没有再发现明细对SQL性能瓶颈，并且SQL...
MySQL中using filesort 与 using temporary
m0_49151953的博客
04-06 1403
使用了文件排序，一般情况下是因为order by 排序索引失效或者没使用索引，在order by 排序之前需要对数据进行过滤，否则索引不会生效，“无过滤不索引”，原因可以查看我之前博客 MySQL开发优化细节 一文。使用了临时表保存结果，一般情况下是因为连表操作时 group by 中索引失效，优化技巧就是需要关注我们的sql执行顺序。以及我们的小表驱动大表，表驱动的 nested loop 原理。
Mysql语句性能分析-EXPLAIN
12-14
分析你的查询语句或是表结构的性能瓶颈。 如下面SQL语句： EXPLAIN select * from system_area where status = 1 获得下面的结果： select_type SIMPLE：简单表，不使用表连接或子查询 PRIMARY：主查询，即外层的...
MySQL 性能优化神器 Explain 使用分析
01-22
MySQL 性能优化 Explain ,MySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 ExplainMySQL 性能优化 Explain
MySQL内部临时表（Using temporary）案例详解及优化解决方法
DBdoctor_off的博客
08-29 2052
内部临时表是MySQL用来辅助复杂SQL聚合计算使用的，会优先占用内存。可用内存大小受会话级参数 tmp_table_size 和 max_heap_table_size同时限制，内存不够时会将内存临时表转化为磁盘临时表，也可以通过SQL_SMALL_RESULT修饰来强制只使用磁盘临时表。
MySQL调优 —— Using temporary
wenniuwuren
08-04 3万+
DBA发来一个线上慢查询问题， SQL如下(为突出重点省略部分内容)： select distinct article0_.id, 等字段 from article_table article0_, hits_table articlehit1_ where article0_.id=articlehit1_.id order by hits; EXPLAIN结果：耗时4.03S
order by产生的 Using temporary的优化
keyboard专栏
03-25 3726
今天遇到一个慢查询的sql，sql如下： EXPLAIN SELECT tas.f_year, tc.pk_id, tas.f_sex, tas.f_rank_score, tas.f_age, tas.f_km_five, tas.f_km_ten, tas.f_km_fifteen, tas.f_km_twenty, tas.f_km_halfway, tas.f_km_...
Explain 执行计划详解
weixin_47184173的博客
05-30 7313
什么是执行计划 我们往往通过慢查询日志，可以找到慢查询的sql具体是怎么写的。我们往往可以通过表的索引，执行引擎等等去自己判断sql为什么执行缓慢的原因。但是这明显不是高效的。 我们的MySQL中为我们提供了EXPLAN关键字来协助我们分析SQL。 一条查询语句在经过 MySQL 查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划，这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。EXPLAIN 语句来帮助我们查看某个查询语
mysql-索引出现Using temporary场景
面朝大海，春暖花开
07-17 308
当在ORDER BY 或者 GROUP BY 后的列不是一个单一列，而是一个表达式，或者列没有合适的索引时，MySQL可能使用临时表存储中间结果，再对临时表进行排序或分组。在进行这些子查询操作时，如果子查询中的求最大值或最小值等操作无法用索引覆盖，MySQL可能会创建临时表来辅助运算。等聚合函数时，如果没有适当的索引，可能会触发临时表的使用。操作时，如果涉及到的列没有相应的索引，MySQL会创建临时表来存储结果并执行去除重复的处理，这也会触发。依据的字段在索引中不是第一个字段时，可能会触发临时表的使用。
mysql优化，Using temporary； Using filesort
xxhhbb1538的专栏
08-17 7789
多表关联时，在order by中使用了非驱动表字段进行排序，会出现Using temporary; Using filesort，此时需要优化。 mysql会自动将数据量较小的表设置为驱动表，但是我们排序的字段又不是该表的字段，使用left join，可以阻止小表被优化为驱动表，但是这种方式往往无法满足业务需求。 此时使用exists子查询刚好满足需求，可以使该表不再被动的置为驱动表，不再出现Using temporary; Using filesort ...
Mysql索引篇（四） 覆盖索引(Using index)、文件排序(Using filesort)和临时表(Using temporary)
jqsfjqsf的博客
02-13 2995
本系列文章目录 展开/收起 Mysql索引篇（一） 索引的数据结构B+树 Mysql索引篇（二） Myisam和Innodb的索引 Mysql索引篇（三） 善于explain分析sql语句 Mysql索引篇（四） 覆盖索引(Using index)、文件排序(Using filesort)和临时表(Using temporary) ...
MySQL 优化笔记：Explain、Profiles、Performance_Schema、Trace 优化器追踪
分享既学习
06-18 2822
本篇文章将详细介绍关于 SQL 优化的知识和技巧，先从数据表的设计谈起，再者就是 MySQL 中的极限，然后就是优化的基础知识索引、SQL执行过程、explain 执行计划 接者就是如何定位问题，将从 profile 开始。然后我们还会聊派生表、半连接、反连接等等
using filesort和using temporary
wwwwwww31311的博客
05-26 1277
using filesort 用Explain分析SQL语句的时候，经常发现有的语句在Extra列会出现Using filesort，mysql官方文档对他的描述也很奇怪，不好理解 概念：mysql会对数据使用一种外部索引排序，而不是基于表内部索引顺序来进行读取，mysql无法利用内部索引来完成的排序被我们称为文件排序 注意：Using filesort 是Mysql里一种速度比较慢的外部排序，如果能避免是最好的了，很多时候，我们可以通过优化索引来尽量避免出现Using filesort，从而提高速度。
MySQL EXPLAIN详解与性能分析
"这份PDF文件主要讲解了MySQL中的EXPLAIN工具的使用，以及如何通过它来分析SQL查询的执行计划，以优化数据库性能。文件中提供了创建和填充测试表的示例，包括`actor`, `film` 和 `film_actor`三张表，以及对`film`表...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值