新闻资讯  快讯  焦点  财经  政策  社会
互 联 网   电商  金融  数据  计算  技巧
生活百科  科技  职场  健康  法律  汽车
手机百科  知识  软件  修理  测评  微信
软件技术  应用  系统  图像  视频  经验
硬件技术  知识  技术  测评  选购  维修
网络技术  硬件  软件  设置  安全  技术
程序开发  语言  移动  数据  开源  百科
安全防护  资讯  黑客  木马  病毒  移动
站长技术  搜索  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   点击:()  评论:()
声明:本站部分内容来自互联网,如有任何版权侵犯或其他问题请与我们联系,我们将立即删除或处理。
▌相关评论
发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表
▌相关推荐
这是我在知乎上的一个回答, 如果有兴趣的同学可以在知乎上搜索我的账户: jsppedu。1、横向分类前端:HTML、CSS、JavaScript后端:PHP、MySQL2、责任分类HTML:负责网页结构部分CSS:...【详细内容】
2019-06-18 HTML  点击:(1)  评论:(0)  加入收藏
MySQL数据库 Too many connections出现这种错误明显就是 mysql_connect 之后忘记 mysql_close;当大量的connect之后,就会出现Too many connections的错误,mysql默认的连接为10...【详细内容】
2019-06-17 Mysql  点击:(3)  评论:(0)  加入收藏