新闻资讯  快讯  焦点  财经  政策  社会
互 联 网   电商  金融  数据  计算  技巧
生活百科  科技  职场  健康  法律  汽车
手机百科  知识  软件  修理  测评  微信
软件技术  应用  系统  图像  视频  经验
硬件技术  知识  技术  测评  选购  维修
网络技术  硬件  软件  设置  安全  技术
程序开发  语言  移动  数据  开源  百科
安全防护  资讯  黑客  木马  病毒  移动
站长技术  搜索  SEO  推广  媒体  移动
财经百科  股票  知识  理财  财务  金融
教育考试  育儿  小学  高考  考研  留学
您当前的位置:首页 > IT百科 > 数据库 > 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   点击:()  评论:()
声明:本站部分内容来自互联网,内容观点仅代表作者本人,如有任何版权侵犯请与我们联系,我们将立即删除。
▌相关评论
发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表
▌相关推荐
作者:胡呈清整理 MySQL 8.0 文档时发现一个变更:默认字符集由 latin1 变为 utf8mb4。想起以前整理过字符集转换文档,升级到 MySQL 8.0 后大概率会有字符集转换的需求,在此正好分...【详细内容】
2019-12-27   MySQL  点击:(1)  评论:(0)  加入收藏
1、Windows下安装MySQL数据库  具体参考:https://blog.csdn.net/dongDONG2014_/article/details/897169052、 MySQL目录结构MySQL的数据存储目录为data,data目录通常在C:\D...【详细内容】
2019-12-27   MySQL  点击:(0)  评论:(0)  加入收藏
一、前言这里记录一次将MySQL数据库中的表数据迁移到Oracle数据库中的全过程 ,使用工具 Navicat,版本 12.0.11操作环境及所用工具: mysql5.7 oracle18c windows Navicat12.0.1...【详细内容】
2019-12-27   MySQL  点击:(3)  评论:(0)  加入收藏
我会谈谈对于索引结构我自己的看法,以及分享如何从零开始一层一层向上最终理解索引结构。从一个简单的表开始createtableuser(idintprimarykey,ageint,heightint,weightint...【详细内容】
2019-12-26   MySQL  点击:(1)  评论:(0)  加入收藏
高并发这个阶段,肯定是需要做读写分离的,啥意思?因为实际上大部分的互联网公司,一些网站,或者是 app,其实都是读多写少。所以针对这个情况,就是写一个主库,但是主库挂多个从库,然后从...【详细内容】
2019-12-26   MySQL  点击:(2)  评论:(0)  加入收藏
一、连接查询图解示意图 1、建表语句部门和员工关系表:CREATE TABLE `tb_dept` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID', `deptName` varchar(3...【详细内容】
2019-12-24   MySQL  点击:(3)  评论:(0)  加入收藏
在软件应用开发过程中经常出现因数据库表、数据库表字段格式不规则而影响开发进度的问题。在维护应用使用原来数据库表时,也会因为数据库表的可读性不够高,表字段规则不统一,造...【详细内容】
2019-12-24   MySQL  点击:(1)  评论:(0)  加入收藏
前言Emoji 在我们生活中真的是越来越常见了,几乎每次发消息的时候不带个 Emoji,总觉得少了点什么,似乎干巴巴的文字已经无法承载我们丰富的感情了。对于我们开发者来说,如何将 E...【详细内容】
2019-10-28   MySQL  点击:(2)  评论:(0)  加入收藏
学习索引,主要是写出更快的sql,当我们写sql的时候,需要明确的知道sql为什么会走索引?为什么有些sql不走索引?sql会走那些索引,为什么会这么走?我们需要了解其原理,了解内部具体过程,...【详细内容】
2019-12-24   MySQL  点击:(0)  评论:(0)  加入收藏
作为一名软件开发人员,尤其是服务端开发,数据库已经是一个必备的技能了,Mysql也是这些年使用最广泛的数据库之一,小编平时也是开发工作中也是基于mysql数据库的,经常跟mysql打交...【详细内容】
2019-12-24   MySQL  点击:(3)  评论:(0)  加入收藏
本文章向大家介绍MySQL锁详细讲解,包括数据库锁基本知识、表锁、表读锁、表写锁、行锁、MVCC、事务的隔离级别、悲观锁、乐观锁、间隙锁GAP、死锁等等,需要的朋友可以参考一下...【详细内容】
2019-12-24   MySQL  点击:(2)  评论:(0)  加入收藏
字符型数据主要是指离散的类别型数据,并且这些数据以字符串的形式呈现,如用户的姓名、性别、汽车的型号、产品的名称等。在MySQL数据库中,关于常用的字符型数据可以参考表所示...【详细内容】
2019-12-23   MySQL  点击:(7)  评论:(0)  加入收藏
概述在几个流行的数据库中,我首先接触到的是MySQL,随着工作发展,接触到越来越多的是PostgreSQL数据库。这两个十分流行的开源数据库。在这之后,我就会经常和一些朋友进行讨论:MyS...【详细内容】
2019-12-23   MySQL  点击:(6)  评论:(0)  加入收藏
作者 | wzy0623责编 | 屠敏出品 | CSDN 博客大多数MySQL高可用解决方案都是基于MySQL自带的各种复制技术。本质上是将一个实例上的数据更新或事务,在其它实例上进行重放,从而...【详细内容】
2019-12-23   MySQL  点击:(4)  评论:(0)  加入收藏
--sql语言的四大分类--数据定义语言(DDL)create alter drop--数据查询语言(DQL)select--数据操作语言(DML)insert update delete--数据控制语言(DCL)revoke grant--数据库的相关操作...【详细内容】
2019-12-23   MySQL  点击:(3)  评论:(0)  加入收藏
作者:YvesHe链接:https://blog.csdn.net/u011479200/article/details/78513632通配符的分类:%百分号通配符: 表示任何字符出现任意次数 (可以是0次)._下划线通配符:表示只能匹...【详细内容】
2019-12-23   MySQL  点击:(3)  评论:(0)  加入收藏
配置MySQL服务器第一步是设置MySQL服务器,以侦听机器上的某个特定IP地址或所有IP地址。 如果MySQL服务器和客户端可以通过专用网络相互通信,那么最好的选择是设置MySQL服务器,...【详细内容】
2019-12-23   MySQL  点击:(9)  评论:(0)  加入收藏
MySQL 导出数据MySQL中你可以使用SELECT...INTO OUTFILE语句来简单的导出数据到文本文件上。 使用 SELECT ... INTO OUTFILE 语句导出数据以下实例中我们将数据表 runoob_tb...【详细内容】
2019-12-19   MySQL  点击:(4)  评论:(0)  加入收藏
MySQL数据库如何实现每秒570000的写入,通过本文了解下。...【详细内容】
2019-12-18   MySQL  点击:(9)  评论:(0)  加入收藏
连接查询的优化无论什么数据库,多表连接的查询成本都是比较高的,因此对于高并发应用,应该尽量减少有连接的查询,多表连接的个数不要超过4张表。一般数据量少的时候,连接开小不...【详细内容】
2019-12-17   MySQL  点击:(15)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条