MySQL explain 优化sql时，你知道 select_type, type, possible_keys, key, key_len,ref, row, Extra 这些个字段是代表什么意思？那就来看看吧
explain
别名：desc
例子些
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 |
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)
两者的区别主要是两点：
- 没有MRR的情况下，随机IO增加，因为从二级索引里面得到的索引元组是有序，但是他们在主键索引里面却是无序的，所以每次去主键索引里面得到non_key_column的时候都是随机IO。（如果索引覆盖，那也就没必要利用MRR的特性了，直接从索引里面得到所有数据）
-
没有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
