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

MySQL的查询性能分析神器:explain命令的使用详解

时间:2019-06-06 13:01:27  来源:  作者:

MySQL的SQL查询性能分析当中,主要使用explain命令对查询SQL语句的执行情况进行分析,包含查询所涉及的表,查询索引使用情况,排序情况等,用法的使用很简单,示例如下:

mysql> EXPLAIN SELECT DISTINCT(trade_date) FROM order WHERE user_id=1 ORDER BY trade_date DESC LIMIT 10;
+----+-------------+-------------------+------------+------+----------------------+--------------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------------+------------+------+----------------------+--------------+---------+-------+------+----------+--------------------------+
| 1 | SIMPLE | order | NULL | ref | PRIMARY,idx_user_trade|idx_user_trade| 768 | const | 20 | 100.00 | Using where; Using index |
+----+-------------+-------------------+------------+------+----------------------+--------------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.03 sec)
  • 以上示例对应order表的索引情况:在user_id和trade_date两个字段建立了一个联合索引。
KEY `idx_user_trade` (`user_id`,`trade_date`) USING BTREE

以上示例的explain命名输出的各参数含义如下:

1.id:执行的序号,这个语句没有子查询,故只有1,值越大越先执行;

2.select_type:数据读取类型,这里只是针对单表,也没有UNION操作,故是SIMPLE,其他类型包括:PRIMARY, UNION RESULT, SUBQUERY等;

3.table:查询涉及的表;

4.partitions:查询涉及的表的哪些分区;

5.type:访问类型,指明了MySQL以何种方式查找表中符合条件的行,这个也是需要重点关注的一项指标,包含的类型为:ALL, index, range, ref, eq_ref, const/system, NULL,性能依次变好,其中:

  • ALL:为全表扫描,性能最差;
  • index:为全索引扫描,性能通常也是不够理想;
  • range:为对索引进行范围扫描,然后返回对应的数据行,如SQL包含BETWEEN,>=,IN()等语句时就是range;
  • ref:为索引查找,返回匹配单个索引值的数据行,如果不是范围查询,则需要至少达到这个级别;
  • eq_ref:也是索引查找,不过最多只返回一条记录,通常是在主键或者唯一性索引上,性能较好;
  • const/system:为表只有最多一个匹配行,直接读取对应的数据行,不需要查找索引再根据索引的结果读取数据行,通常是主键或唯一性索引上有固定值的情形;
  • NULL:在执行阶段不需要访问表,直接从索引返回需要的值。

6.possible_keys:该次查询可以使用的索引;

7.key:该次查询实际使用的索引;

8.key_len:使用索引时,所使用的索引值的最大字节数,当type为NULL时,该值也为NULL;

9.ref:哪些字段或常量配合key将数据行从表中获取出来;

10.rows:估计查找到所需要的行,大概需要扫描读取多少数据行,这个值越小越好;即存储引擎大概需要读取并返回rows的值这么多行数据给server层;通常与下面的filtered一起分析,即如果rows为5,而实际需要的查询结果为1条记录,则filtered的值为大概为1/5;如下:

mysql> explain SELECT type, expire_date, id FROM test WHERE id='11111' AND type=3 AND price BETWEEN 80 AND 80.999;
+----+-------------+-------------------+------------+------+---------------+-------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------------+------------+------+---------------+-------------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | test			 | NULL | ref | idx_ul_type | idx_id_type | 769 | const,const | 5 | 11.11 | Using where |
+----+-------------+-------------------+------------+------+---------------+-------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

SQL执行情况:这条SQL实际返回了一条记录。

mysql> SELECT type, expire_date, id FROM test WHERE id='11111' AND type=3 AND price BETWEEN 80 AND 80.999;
省略具体内容
1 row in set (0.00 sec)

如果把price BETWEEN 80 AND 80.999的条件去掉,则返回5条记录:

mysql> SELECT type, expire_date, id FROM test WHERE id='11111' AND type=3;
省略具体内容
5 rows in set (0.00 sec)

索引情况如下:在id和type的两个列建了一个联合索引,不包含price列,故由于price没有索引,实际存储引擎会读取并返回5条记录给server层,server层在根据 price BETWEEN 80 AND 80.999再过滤最后剩下一条记录。

KEY `idx_id_type` (`id`,`type`) USING BTREE

如果把price加到idx_id_type索引中,即idx_id_type (id,type,price) ,则执行情况如下:

mysql> explain SELECT type, expire_date, id FROM test WHERE ul='11111' AND type=3 AND price BETWEEN 80 AND 80.999;
+----+-------------+-------------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | test			 | NULL | range | idx_id_type | idx_id_type | 778 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

11.filtered:此查询条件所过滤的数据的百分比,表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例;越大表示存储引擎返回给server层的都是有用的,故说明效率较高,通常都是100%;

12.Extra:此处执行的额外信息,一次SQL查询的Extra可以包含以下的一条或多条,包括:

  • Using where:表示SQL语句存在WHERE条件,不是获取全表数据,一般使用了WHERE条件都会有这个;
  • Using index:表示使用覆盖索引返回数据,不需要访问表,通常是指该查询性能较好;
  • Using index condition:这个是在5.6版本后加入的新特性,主要作用是当WHERE中的某个条件对应的字段,如a,是加了索引的,但是无法使用,如使用了 a like ‘%abc%’ 这种SQL,在没有这个特性之前,存储引擎此时无法使用这个字段对应的索引了,需要回表找到符合WHERE其他条件的数据行,即full row正行数据,然后传给server层,最后在server层处理a like '%abc%'这个条件,即使a这个字段加了索引,这样就存储引擎就需要读取较多的数据行和传给server层较多的数据。拥有这个特性之后,存储引擎层会使用索引来处理a like '%abc%'这个SQL,进一步过滤,从而减少回表查询的次数和传给server层的数据量;详见官方文档:8.2.1.5 Index Condition Pushdown Optimization
  • ,如下为详细例子:
MySQL的查询性能分析神器:explain命令的使用详解

 

  •  
  • Using temporary:表示使用了临时表来进行分组、排序或者多表join,通常表示查询效率不高,需要优化;
  • Using filesort:表示需要使用一个外部文件索引来对结果进行排序,而不是直接根据内部索引顺序从表中读取数据,这个过程通常需要消耗比较大的CPU资源,需要优化。
  •  


Tags:MySQL   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  Tags: MySQL  点击:(7)  评论:(0)  加入收藏
一、为什么要搭建主从架构呢1.数据安全,可以进行数据的备份。2.读写分离,大部分的业务系统来说都是读数据多,写数据少,当访问压力过大时,可以把读请求给到从服务器。从而缓解数据...【详细内容】
2021-12-15  Tags: MySQL  点击:(12)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  Tags: MySQL  点击:(18)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  Tags: MySQL  点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  Tags: MySQL  点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  Tags: MySQL  点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  Tags: MySQL  点击:(17)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  Tags: MySQL  点击:(21)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  Tags: MySQL  点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Tags: MySQL  点击:(31)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(7)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(18)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(17)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(21)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(28)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条