MySQL 查询优化,explain 各个字段指标说明

MySQL explain 优化sql时,你知道 select_type, type, possible_keys, key, key_len,ref, row, Extra 这些个字段是代表什么意思?那就来看看吧

explain

别名:desc

例子些

  • 例子1:
select * from `tuanmei_deals` where deal_id = 23421;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tuanmei_deals const PRIMARY PRIMARY 4 const 1 NULL

例子2:

select * from `tuanmei_deals` where ( `end_time` > '1592234464' ) limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tuanmei_deals range end_time end_time 4 NULL 234142 Using index condition; Using MRR
  • 例子3:
select * from `tuanmei_deals` where hash_id = 'ht2020517p51688' or ( `end_time` > '1592234464' ) limit 2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tuanmei_deals index_merge hash_id,end_time,idx_HashId_StartTm hash_id,end_time 32,4 NULL 234143 Using sort_union(hash_id,end_time); Using where

id, table

Id 就不说了,table就是你查阅了哪个表,key就是实际用到的索引

select_type

查询类型,就是我们从sql语法角度来看,会被拆封成什么任务

(1) SIMPLE(简单SELECT,不使用UNION或子查询等), 比如上面的一个单表查询

(2) PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)

(3) UNION(UNION中的第二个或后面的SELECT语句) 如果你使用了union了多个select 集合

(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句,取决于外面的查询)

(5) UNION RESULT(UNION的结果)

(6) SUBQUERY(子查询中的第一个SELECT), 例如你用到了 where in (select )

(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT,取决于外面的查询)

(8) DERIVED(派生表的SELECT, FROM子句的子查询)

(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存,必须重新评估外链接的第一行)

type

ALL:Full Table Scan, MySQL将遍历全表以找到匹配的行

index: Full Index Scan,index与ALL区别为index类型只遍历索引树

range:只检索给定范围的行,使用一个索引来选择行

ref: 表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值

eq_ref: 类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用primary key或者 unique key作为关联条件。触发条件:只匹配到一行的时候。除了system和const之外,这是最好的连接类型了。当我们使用主键索引或者唯一索引的时候,且这个索引的所有组成部分都被用上,才能是该类型。

const、system: 当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该查询转换为一个常量,system是const类型的特例,当查询的表只有一行的情况下,使用system。system的触发条件:表只有一行,这是一个const type 的特殊情况。

NULL: MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。

possible_keys

指出MySQL能使用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用

key列显示MySQL实际决定使用的键(索引)

key

如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度(key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的)

不损失精确性的情况下,长度越短越好。

key_len 表示一行数据该索引占用的数据宽度,比如对一个 字段类型为 int 建立索引,那么索引的key_len 就是 4。和数量多少没有关系。

ref

显示索引的哪一列被使用了,如果可能的话,是一个常数

表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值

rows

表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数, 注意:这是预估值,非实际值

Extra

该列包含MySQL解决查询的详细信息

  • Using index:表示使用索引,如果只有 Using index,说明他没有查询到数据表,只用索引表就完成了这个查询,这个叫覆盖索引。如果同时出现Using where,代表使用索引来查找读取记录, 也是可以用到索引的,但是需要查询到数据表。
  • Using where:表示条件查询,如果不读取表的所有数据,或不是仅仅通过索引就可以获取所有需要的数据,则会出现 Using where。如果type列是ALL或index,而没有出现该信息,则你有可能在执行错误的查询:返回所有数据。
  • Using MRR:MMR全称是Multi-Range Read,是MYSQL5.6优化器的一个新特性,在MariaDB5.5也有这个特性。优化的功能在使用二级索引做范围扫描的过程中减少磁盘随机IO和减少主键索引的访问次数。将随机IO转换为顺序IO
  • Using filesort:不是“使用文件索引”的含义!filesort是MySQL所实现的一种排序策略,通常在使用到排序语句ORDER BY的时候,会出现该信息。
  • Using temporary:表示为了得到结果,使用了临时表,这通常是出现在多表联合查询,结果排序的场合。

MRR和没有MRR的区别

给出一个简单的例子,在innodb表执行下面的查询:

SELECT non_key_column FROM tbl WHERE key_column=x

在没有MRR的情况下,它是这样得到结果的:

1.  select key_column, pk_column from tb where key_column=x order  by key_column ---> 假设这个结果集是t2.  for each row in t ; 
select non_key_column from tb where pk_column = pk_column_value。(在oracle里第2步叫回表)在有MRR的情况下,它是这样执行的:
1.  select key_column, pk_column from tb where key_column = x  order by key_column ---> 假设这个结果集是t
2.  将结果集t放在buffer里面(直到buffer满了),然后对结果集t按照pk_column排序 ---> 假设排序好的结果集是t_sort
3.  select non_key_column fromtb where pk_column in (select pk_column from t_sort)

两者的区别主要是两点:

  1. 没有MRR的情况下,随机IO增加,因为从二级索引里面得到的索引元组是有序,但是他们在主键索引里面却是无序的,所以每次去主键索引里面得到non_key_column的时候都是随机IO。(如果索引覆盖,那也就没必要利用MRR的特性了,直接从索引里面得到所有数据)

  2. 没有MRR的情况下,访问主键索引的次数增加。没有MRR的情况下,二级索引里面得到多少行,那么就要去访问多少次主键索引(也不能完全这样说,因为mysql实现了BNL),而有了MRR的时候,次数就大约减少为之前次数t/buffer_size。

关于 Explain 的相关注意事项

总结:
– • EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
– • EXPLAIN不考虑各种Cache
– • EXPLAIN不能显示MySQL在执行查询时所作的优化工作
– • 部分统计信息是估算的,并非精确值
– • EXPALIN只能解释SELECT操作,其他操作要重写为SELECT后查看执行计划。

show profile

show profile作用:
能够查出最近15条SQL语句的运行状态(包含运行过程中执行了哪些操作,各占用了多长时间),以便开发者的分析。

1、查看MySQL的版本是否支持show profie
2、查看是否已经开启show profile

$ SHOW VARIABLES LIKE 'profiling';

3、开启功能,默认是关闭
SET profiling=ON;
SHOW VARIABLES LIKE ‘profiling’;

4、运行sql
随便运行几条SQL,以便于show prifiles的日志分析。
5、查看结果
show profiles;

6、诊断SQL
用于单独分析某条sql,查看某条sql的生命周期以及各占用多少时间。
SHOW PROFILE cpu, block io FOR QUERY id;
id为show profiles查出来的某条记录的id


set profiling=1; //打开分析 run your sql1; run your sql2; show profiles; //查看sql1,sql2的语句分析 show profile for query 1; //查看sql1的具体分析 show profile ALL for query 1; //查看sql1相关的所有分析【主要看i/o与cpu,下边分析中有各项意义介绍】 set profiling=0; //关闭分析

引用

  • https://mengkang.net/1124.html
  • https://www.cnblogs.com/fu-yong/p/8496368.html
  • https://www.cnblogs.com/kubidemanong/p/10734045.html
  • https://blog.csdn.net/liang_0609/article/details/44040357
  • https://zhuanlan.zhihu.com/p/100427746
  • https://blog.csdn.net/mingover/article/details/79066064
  • https://www.tuicool.com/articles/ZFrUzia
  • https://blog.csdn.net/lihuayong/article/details/42044593
  • https://www.cnblogs.com/xuanzhi201111/p/4175635.html