ITPub博客

mysql的执行计划解释

原创 MySQL 作者:贺子_DBA时代 时间:2018-05-12 12:34:20 0 删除 编辑
关于MySQL的执行计划,做个笔记,可以做为优化的依据,尽量将第四列 type优化到ref,至少要保证range方式,能用覆盖索引的要使用覆盖索引,然后possible_keys显示null不代表不使用索引,覆盖索引的时候,可能只在key列显示,possible_keys显示null;然后注意当分组和排序的时候可能会使用临时表的时候,尽量不使用磁盘临时表;
一:首先生成执行计划:
Explain语法
EXPLAIN SELECT ……
变体:
1. EXPLAIN EXTENDED SELECT ……
将执行计划“反编译”成SELECT语句,运行SHOW WARNINGS 可得到被MySQL优化器优化后的查询语句
例如:
mysql> explain EXTENDED select CUST_ID ,count(*) from biz_member_info group by CUST_ID limit 10;
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+-------------+
| 1 | SIMPLE | biz_member_info | index | CUST_ID | CUST_ID | 768 | NULL | 10 | 17665850.00 | Using index |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+-------------+
mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `cms`.`biz_member_info`.`CUST_ID` AS `CUST_ID`,count(0) AS `count(*)` from `cms`.`biz_member_info` group by `cms`.`biz_member_info`.`CUST_ID` limit 10 |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2. EXPLAIN PARTITIONS SELECT ……
用于分区表的EXPLAIN
二:执行计划的解析
mysql> explain select CUST_ID ,count(*) from biz_member_info group by CUST_ID limit 10;
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | biz_member_info | index | CUST_ID | CUST_ID | 768 | NULL | 10 | Using index |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
2.1、第一列 id
id列数字越大越先执行,如果说数字一样大,那么就从上往下依次执行,id列为null的就表示这是一个结果集,不需要使用它来进行查询。
2.2、第二列 select_type
A:simple:表示不需要union操作或者不包含子查询的简单select查询。有连接查询时,外层的查询为simple,且只有一个
B:primary:一个需要union操作或者含有子查询的select,位于最外层的单位查询的select_type即为primary。且只有一个
C:union:union连接的两个select查询,select .... from table1 union select ..... from table2;第一个查询(即select .... from table1)是dervied派生表,除了第一个表外,第二个以后的表select_type都是union
D:dependent union:与union一样,出现在union 或union all语句中,但是这个查询要受到外部查询的影响
E:union result:包含union的结果集,在union和union all语句中,因为它不需要参与查询,所以id字段为null
F:subquery:除了from字句中包含的子查询外,其他地方出现的子查询都可能是subquery
G:dependent subquery:与dependent union类似,表示这个subquery的查询要受到外部表查询的影响
H:derived:from字句中出现的子查询,也叫做派生表,其他数据库中可能叫做内联视图或嵌套select
2.3、第三列 table-----显示的查询表名
1)如果查询使用了别名,那么这里显示的是别名,
2)如果不涉及对数据表的操作,那么这显示为null,
3)如果显示为尖括号括起来的<derived N>就表示这个是临时表,后边的N就是执行计划中的id,表示结果来自于这个查询产生。
4)如果是尖括号括起来的<union M,N>,与<derived N>类似,也是一个临时表,表示这个结果来自于union查询的id为M,N的结果集。
2.4、第四列 type-----显示查询数据的途径,很重要的一个值!!!!
依次从好到差:system>const>eq_ref>ref>fulltext>ref_or_null>unique_subquery>index_subquery>range>index_merge>index>ALL,除了all之外,其他的type都可以使用到索引,除了index_merge之外,其他的type只可以用到一个索引
A:system:表中只有一行数据或者是空表,且只能用于myisam和memory表。如果是Innodb引擎表,type列在这个情况通常都是all或者index
B:const:使用唯一索引或者主键,返回记录一定是1行记录的等值where条件时,通常type是const。其他数据库也叫做唯一索引扫描
C:eq_ref: 出现在连接多个表的查询计划中,驱动表每次连接只返回一行数据,且这行数据是第二个表的主键或者唯一索引,且必须为not null,唯一索引和主键是多列时,只有所有的列都用作比较时才会出现eq_ref ,(只有这样才能保证唯一性) 总之 一般情况下使用唯一键或者主键进行单表等值查询时,一般是const,当多表连接的时候,第一个表每次在连接条件下只返回一行数据,并且这行数据可以通过第二个表的主键或者唯一索引检索出来,并且唯一键值不为空,一般为eq_ref;如下所示:
D:ref:没有主键和唯一索引的要求,只要使用相等条件检索时就可能出现,常见与辅助索引的等值查找。或者多列主键、多列唯一索引中,使用第一个列之外的列作为等值查找也会出现,总之,返回数据不唯一的等值查找就可能出现

E:fulltext:全文索引检索,要注意,全文索引的优先级很高,若全文索引和普通索引同时存在时,mysql不管代价,优先选择使用全文索引
F:ref_or_null:与ref方法类似,只是增加了null值的比较。实际用的不多。
G:unique_subquery:用于where中的in形式子查询,子查询返回不重复值唯一值
H:index_subquery:用于in形式子查询使用到了辅助索引 或者 in常数列表,子查询可能返回重复值,可以使用索引将子查询去重。
I:range:索引范围扫描,常见于使用>,<,is null,between ,in ,like等运算符的查询中。
J:index_merge:表示查询使用了两个以上的索引,最后取交集或者并集,常见and ,or的条件使用了不同的索引,官方排序这个在ref_or_null之后,但是实际上由于要读取所个索引,性能可能大部分时间都不如range
K:index:索引全表扫描,把索引从头到尾扫一遍,常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
L:all:这个就是全表扫描数据文件,然后再在server层进行过滤返回符合要求的记录。
2.5、第五列possible_keys
显示可能使用到的索引都会在这里列出来,查询涉及到的字段上存在索引,则该索引将被列出,但不一定被查询实际使用,注意这里为null不代表一定不会走索引,比如覆盖索引;如下所示表biz_member_info有个组合索引cust_id_2 (CUST_ID ,CUST_NAME),我们知道组合索引使用的时候遵循最左匹配原则,where cust_id=可以使用索引,但是where cust_name=不会使用索引,但是如果可以使用覆盖索引查出所需要的数据列时,就会选择index的方式,也就是扫描所有的索引块,而不去扫描全部的数据块;所以说where cust_name=不会使用索引这个说法,个人觉得有点问题,因为他可以通过扫描全部的索引块来得到结果,也可以理解为使用了索引,毕竟扫描全部的索引块大部分情况是比扫描所有的数据块要效率高,
如下Extra显示 Using index表示使用了覆盖索引,可以看出覆盖索引确实使用的是index的方式,并且possible_keys为null;但是key显示使用了覆盖索引的名字,
如下这种情况不能使用覆盖索引查询出需要的数据列(原因自己百度),所以选择了all的方式,也就是扫描全部数据块,当然这时候possible_keys和key都是null
2.6、第六列key
查询真正使用到的索引,select_type为index_merge时,这里可能出现两个以上的索引,其他的select_type这里只会出现一个。并且如果为NULL,则表示没有使用索引。
查询中如果使用了覆盖索引,则该索引可能仅出现在key列表中,可能不会出现在前面的possible_keys,前面介绍了;
2.7、key_len
用于处理查询的索引长度,单位字节,需要注意:
1)如果是单列索引,那就整个索引长度算进去,如果是多列索引,那么查询不一定都能使用到所有的列,具体使用到了多少个列的索引,这里就会计算进去,没有使用到的列,这里不会计算进去。留意下这个列的值,算一下你的多列索引总长度就知道有没有使用到所有的列了。
2)mysql的ICP特性(后面会介绍)使用到的索引不会计入其中。
3)key_len只计算where条件用到的索引长度,而排序和分组就算用到了索引,也不会计算到key_len中。
4)查询中使用的索引的长度(最大可能长度),并非实际使用长度,理论上长度越短越好。key_len是根据表定义计算而得的,不是通过表内检索出的。
2.8、ref
如果是使用的常数等值查询,这里会显示const,如果是连接查询,被驱动表的执行计划这里会显示驱动表的关联字段,如果是条件使用了表达式或者函数,或者条件列发生了内部隐式转换,这里可能显示为func
2.9、rows
这里是执行计划中估算的扫描行数,不是精确值
3.0、extra
这个列可以显示的信息非常多,有几十种,常用的有
A:distinct在select部分使用了distinc关键字;
B:no tables used:不带from字句的查询或者From dual查询;
D:using filesort:排序时无法使用到索引时,就会出现这个。常见于order by和group by语句中,需要注意的是filesort不代表一定是使用文件排序,其实也是内存中的一种算法;如果sort buffer可以存放所有满足条件需要排序的数据,则进行排序;否则sort buffer满后,进行排序并固化到临时文件中。(排序算法采用的是快速排序算法);
E:using index:查询时不需要回表查询,直接通过索引就可以获取查询的数据,也就是使用了覆盖索引查询到了结果;
F:using join buffer(block nested loop),using join buffer(batched key accss):5.6.x之后的版本优化关联查询的BNL,BKA特性。主要是减少内表的循环数量以及比较顺序地扫描查询。
G:using sort_union,using_union,using intersect,using sort_intersection:
using intersect:表示使用and的各个索引的条件时,该信息表示是从处理结果获取交集
using union:表示使用or连接各个使用索引的条件时,该信息表示从处理结果获取并集
using sort_union和using sort_intersection:与前面两个对应的类似,只是他们是出现在用or和and查询信息量大时,先查询主键,然后进行排序合并后,才能读取记录并返回。
H:using temporary:表示使用了临时表存储中间结果。
一:MySQL在以下几种情况会创建临时表:
1、UNION查询;
2、用到TEMPTABLE算法或者是UNION查询中的视图;
3、ORDER BY和GROUP BY的子句不一样时;
4、表连接中,ORDER BY的列不是驱动表中的;
5、DISTINCT查询并且加上ORDER BY时;
6、SQL中用到SQL_SMALL_RESULT选项时;
7、FROM中的子查询;
二:临时表可以是内存临时表和磁盘临时表,执行计划中看不出来,需要查看status变量;
mysql> show global status like '%tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 65 |
| Created_tmp_tables | 142 |
+-------------------------+-------+
Created_tmp_disk_tables :MySQL server在磁盘上产生的内部临时表的个数;
Created_tmp_tables : MySQL server产生的所有的内部临时表的数量;
三:MySQL是如何选择内存临时表和磁盘临时表
当我们进行一些特殊操作如需要使用临时表才能完成的Order By,Group By 等等,MySQL可能需要使用到临时表。当我们的临时表较小(小于tmp_table_size 参数所设置的大小)的时候,MySQL会将临时表创建成内存临时表,只有当tmp_table_size所设置的大小无法装下整个临时表的时候,MySQL才会将该表创建成MyISAM存储引擎的表存放在磁盘上。不过,当另一个系统参数 max_heap_table_size 的大小还小于 tmp_table_size 的时候,MySQL将使用 max_heap_table_size 参数所设置大小作为最大的内存临时表大小,而忽略tmp_table_size 所设置的值。而且 tmp_table_size 参数从 MySQL 5.1.2 才开始有,之前一直使用 max_heap_table_size;
I:using where表示存储引擎返回的记录并不是所有的都满足查询条件,需要在server层进行过滤。如果没有使用索引,仅仅是表明使用了过滤条件;
J:firstmatch(tb_name):5.6.x开始引入的优化子查询的新特性之一,常见于where字句含有in()类型的子查询。如果内表的数据量比较大,就可能出现这个;
K:loosescan(m..n):5.6.x之后引入的优化子查询的新特性之一,在in()类型的子查询中,子查询返回的可能有重复记录时,就可能出现这个;
L:Using index condiction: 代表使用了ICP优化,主要是针对where条件过滤的优化,ICP是5.6.x之后引入的可以优化 range、ref、eq_ref、ref_or_null类型的查询,ICP是index condition pushdown的缩写,在5.6之前的MySQL版本中不支持ICP,当进行索引查询的时候,首先存储引擎层根据索引来查找记录,然后在server层再根据where条件来过滤记录,在支持ICP后,MySQL数据库会在存储引擎层取出索引的同时,判断是否可以进行where条件的过滤,也就是将where的部分过滤操作放在了存储引擎层,在某些查询下,可以大大减少上层sql层对记录的索取,从而提高数据库整体性能;
3.1、filtered
使用explain extended时会出现这个列,5.7之后的版本默认就有这个字段,不需要使用explain extended了。这个字段表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量/存储引擎返回的数据的比例,注意是百分比,不是具体记录数;也就是filtered=最后的结果数量 /存储引擎层返回的数据量;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29654823/viewspace-2154371/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-05-12

  • 博文量
    218
  • 访问量
    1506525