MySQL执行计划EXPLAIN

目录

• 1. id
• 2. select_type
• 3. table
• 4. type
• 5. possible_keys
• 6. key
• 7. key_len
• 8. ref
• 9. rows
• 10. filtered
• 11. extra
• 参考文章

1. id

sql语句的执行顺序

1. 查询的sql

EXPLAIN select * from log where log_type_id = (select id from log_type where id = '11e0aeb4551ab5990e54a0940edc5764');

2. 说明
   从 2 个表中查询，对应输出 2 行，每行对应一个表。 id 列表示执行顺序，id 越大，越先执行，id 相同时，由上至下执行。

2. select_type

查询类型

1. SIMPLE
   • 查询的sql

EXPLAIN select id from log_type where id = '11e0aeb4551ab5990e54a0940edc5764';

• 说明：简单的SELECT语句（不包括UNION操作或子查询操作）

2. PRIMARY、UNION、UNION RESULT
   • 查询的sql

EXPLAIN select log_type_id from log where log_type_id = '072bc3eeb95934ce2d66351d539ae9b5'
UNION
select id from log_type where id = '11e0aeb4551ab5990e54a0940edc5764';

• PRIMARY：查询中最外层的SELECT（如两表做UNION或者存在子查询的外层的表操作为PRIMARY，内层的操作为UNION）
• UNION：UNION操作中，查询中处于内层的SELECT（内层的SELECT语句与外层的SELECT语句没有依赖关系）
• UNION RESULT：UNION操作的结果，id值通常为NULL

3. SUBQUERY
   • 查询的sql

EXPLAIN select * from log where log_type_id = (select id from log_type where id = '11e0aeb4551ab5990e54a0940edc5764');

• 说明：子查询中首个SELECT（如果有多个子查询存在）

4. DEPENDENT SUBQUERY

1. 会严重消耗性能
2. 不会进行子查询，会先进行外部查询,生成结果集,再在内部进行关联查询
3. 子查询的执行效率受制于外层查询的记录数
4. 可以尝试改成join查询

• 查询的sql

EXPLAIN select * from log t_log where t_log.log_type_id = (select id from log_type t_log_type where t_log_type.id = '11e0aeb4551ab5990e54a0940edc5764' and t_log.log_type_id = t_log_type.id);

• 说明：子查询中首个SELECT，但依赖于外层的表（如果有多个子查询存在）

5. DERIVED
   • 查询的sql

EXPLAIN select t_log.id from log t_log JOIN 
(select id, parent_id from log_type GROUP BY parent_id, id) t_log_type
ON t_log.log_type_id = t_log_type.id;

• 说明：被驱动的SELECT子查询（子查询位于FROM子句）

6. MATERIALIZED
   • 查询的sql

EXPLAIN select count(0) from log t_log WHERE t_log.log_type_id in 
(select parent_id from log_type WHERE id in ('11e0aeb4551ab5990e54a0940edc5764', '083d17b0bc58009ac9ed4a4edc5ceda0'));

• 说明：被物化的子查询，IN 子查询的结果物化之后（select_type = MATERIALIZED）和 t_log表进行连接操作

7. UNCACHEABLE SUBQUERY

不推荐使用，需要优化

• 查询的sql

EXPLAIN select * from log t_log WHERE t_log.log_type_id = (select parent_id from log_type WHERE owner_id = @@sort_buffer_size);

• 说明：对于外层的主表，子查询不可被物化，每次都需要计算（耗时操作）

8. UNCACHEABLE UNION

不推荐使用，需要优化

• 查询的sql

EXPLAIN select t_log.id from log t_log WHERE EXISTS (
 select id from log_type b WHERE b.parent_id = t_log.log_type_id
 UNION
 select id from log_type a WHERE a.owner_id = 262144
);

• 说明：UNION操作中，内层的不可被物化的子查询（类似于UNCACHEABLE SUBQUERY）

3. table

显示这一行的数据是关于哪张表的，有时不是真实的表名字，也可能是临时表

4. type

1. 表示MySQL在表中找到所需行的方式，又称“访问类型”。
2. 常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）

1. ALL
   Full Table Scan， MySQL将遍历全表以找到匹配的行
   

2. index
   Full Index Scan，index与ALL区别为index类型只遍历索引树
   

3. range
   只检索给定范围的行，使用一个索引来选择行
   

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

5. eq_ref
   类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
   

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

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

5. possible_keys

1. 指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
2. 如果该列是NULL，则没有相关的索引。
3. 如果这个列出现大量可能被使用的索引(例如多于3 个), 那么这 意味着备选索引数量太多了，同时也可能提示存在无效的索引。

1. 单个索引
   

2. 没有使用索引
   

3. 所用多个索引
   

6. key

1. 显示MySQL实际决定使用的键（索引）

1. 使用多个索引
   
   

2. 没有使用索引
   

3. 使用单个索引
   

7. key_len

1. 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）
2. 不损失精确性的情况下，长度越短越好

1. 先看索引上字段的类型+长度比如 int=4 ; varchar(20) =20 ; char(20) =20
2. 如果是varchar或者char这种字符串字段，视字符集要乘不同的值，比如utf-8 要乘3，GBK要乘2
3. varchar这种动态字符串要加2个字节
4. 允许为空的字段要加1个字节
   

8. ref

1. 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
2. 指出对 key 列所选择的索引的查找方式，常见的值有 const, func, NULL, 具体字段名。当 key 列为 NULL ，即不使用索引时 。如果值是func，则使用的值是某个函数的结果

1. const：使用常量等值查询
   

2. NULL：不使用索引
   

3. 具体字段名：关联查询
   

4. func：查询条件使用了表达式、函数，或者条件列发生内部隐式转换
   

9. rows

1. 以表的统计信息和索引使用情况，估算要找到我们所需的记录，需要读取的行数。
2. 这是评估SQL性能的一个比较重要的数据，mysql需要扫描的行数，很直观的显示SQL性能的好坏，一般情况下rows值越小越好。

10. filtered

1. 这个是一个百分比的值，表里符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。
2. 在MySQL.5.7版本以前想要显示filtered需要使用explain extended命令。MySQL.5.7后，默认explain直接显示partitions和filtered的信息。

11. extra

不适合在其他列中显示的信息，Explain中的很多额外的信息会在Extra字段显示。

1. Using index
   我们在相应的 select 操作中使用了覆盖索引，通俗一点讲就是查询的列被索引覆盖，使用到覆盖索引查询速度会非常快，SQl优化中理想的状态。
   

覆盖索引：一条 SQL只需要通过索引就可以返回，我们所需要查询的数据（一个或几个字段），而不必通过二级索引，查到主键之后再通过主键查询整行数据（select * ）。

2. Using where
   查询时未找到可用的索引，进而通过where条件过滤获取所需数据，但要注意的是并不是所有带where语句的查询都会显示Using where。
   

3. Using temporary
   表示查询后结果需要使用临时表来存储，一般在排序或者分组查询时用到。建议添加适当的索引。
   

4. Using filesort
   表示无法利用索引完成的排序操作，也就是ORDER BY的字段没有索引，通常这样的SQL都是需要优化的。建议添加适当的索引。
   

5. Using join buffer
   在我们联表查询的时候，如果表的连接条件没有用到索引，需要有一个连接缓冲区来存储中间结果。
   

6. Impossible where
   表示在我们用不太正确的where语句，导致没有符合条件的行。
   

7. No tables used
   我们的查询语句中没有FROM子句，或者有 FROM DUAL子句。
   

8. Using index condition
   查询条件中虽然出现了索引列，但是有部分条件无法使用索引，会根据能用索引的条件先搜索一遍再匹配无法使用索引的条件。
   
   sql中b.name这列就不是索引列

9. Impossible WHERE
   where子句的值总是false，不能用来获取任何元素
   

10. Using index for group-by
    类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。
    

11. Not exists
    MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了
    

12. Distinct
    MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。对于此项没有找到合适的例子

参考文章

1. mysql 查询优化 ~explain解读之select_type的解读
2. 搞清楚 MySQL 派生表、物化表、临时表
3. MySQL执行计划
4. https://forums.mysql.com/read.php?115,429364,429839#msg-429839
5. https://juejin.cn/post/6844904163969630221
6. Explain详解