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

发表评论

邮箱地址不会被公开。 必填项已用*标注