[MySQL优化] explain性能分析：id, select_type, type, keys, extras

本文链接：https://blog.csdn.net/weixin_42593937/article/details/113829393

本文详细解析了MySQL Explain命令的作用，包括执行计划分析、表读取顺序、索引使用情况和性能优化技巧。重点讲解了id、select_type、type、possible_keys、keys和extras等输出字段的含义，以及如何根据这些信息优化查询性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

[MySQL优化] explain性能分析：id, select_type, type, keys, extras

性能分析：explain

性能分析：explain

MySQL Query Optimizer

专门负责SELECT语句的优化器，通过收集到的统计信息，为客户端请求的Query提供它认为的最优执行计划
当MySQL收到Query，对其分类转发给Optimizer：
- 首先对整条Query优化，处理常量表达式的预算，替换成常量值
- 对Query的条件进行简化和转换，例如去掉一些显而易见的条件、结构调整等
- 分析Query的Hint信息，是否符合执行计划
- 读取对象统计信息，得出执行计划

Explain 介绍

目标：查看执行计划
使用：explain+对应的SELECT语句

Explain 作用

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引实际被使用
表之间的引用
每张表有多少行被优化器查询

Explain 输出

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	tbl_emp	NULL	ALL	NULL	NULL	NULL	NULL	8	100	NULL

id

数字大：优先级最高，最先执行

数字相同：从上到下执行

id相同，则执行顺序从上到下

id不同，如果有子查询，则id递增；id越大，优先级越高，越先被执行
- 最先执行最里层的子查询，则其id最高
- 外层子查询/主查询优先级最低，则id最低

id有相同，也有不同：大数字先执行，相同数字按序执行
- DERIVED2：id为2的表查询

select_type

类型	解释
SIMPLE	简单的SELECT查询，不包含子查询与UNION
PRIMARY	包含子查询的，最外层查询（最后加载的id-1）
SUBQUERY	SELECT或WHERE列表中，包含子查询
DERIVED	FROM中包含的子查询被标记为derived； MySQL递归执行这些子查询，放在临时表 --> 增加系统负担
UNION	第二个SELECT出现在UNION后，则被标记为UNION 若UNION包含在FROM的子查询中，则外层被标记为DERIVED
UNION RESULT	从UNION表中获取结果的SELECT

UNION
- 第一条SELECT语句，被标记为两个PRIMARY（同时执行、最后执行、无子查询）
- UNION后的SELECT语句，执行效果差不多一样，但是被标记为UNION，只因为出现在UNION后
- UNION_RESULT表示UNION后的结果

type

SYSTEM > CONST > EQ_REF > REF > RANGE > INDEX > ALL

百万级数据，需要优化到RANGE级别；最好达到eq_ref

从最好到最差：system > const > eq_ref > ref > range > index > ALL
- 优化到eq_ref / ref就算很好了
- 一般需要优化到RANGE级别
SYSTEM：表中只有一行记录，等于系统表
- 是const类型的特例，平时不会出现
CONST：将主键或唯一索引的所有部分与常量值比较
- 如将主键置于where列表中，就能转换成一个常量const
- 表最多有一个匹配行，由于只有一行，则可以被优化器的其余部分视为常量；CONST很快，因为只读一次；用于***将主键或唯一索引的所有部分与常量值比较***
- CONST的意义：只有一个值能够对应（PRIMARY或UNIQUE）
  该行的值可以被优化器的其余部分视为常量
EQ_REF：用于多表查询，唯一性索引（主键/UNIQUE）
- 常见于主键和唯一索引扫描（即单列索引）
- 与const的区别：JOIN的最好的type；比较值是常量或使用此表之前读取的列的表达式
REF：非唯一性索引扫描/多列索引，非主键/UNIQUE，可能是外键或其他非唯一性索引，
可能返回多行
RANGE：只检索给定范围的行，用一个索引来选择行
- 一般是WHERE语句中出现了Between、<、>、IN等查询
- 比全表扫描好，只需要开始于索引的一点，结束于另一点
INDEX：使用索引（覆盖索引/利用索引排序分组），但是没有使用索引过滤
- 全索引扫描：SELECT id FROM t1;
ALL：全表扫描

possible_keys

显示可能用在这张表的索引，一个或多个
查询涉及到的字段，若存在索引，则将其列出；但不一定被查询实际使用

keys

实际用到的索引，最关心的指标，覆盖索引

key_len

索引中使用的字节数，可以通过该列计算查询中使用的索引长度；
在不损失精确度的情况，长度越短越好
key_len为索引字段的最大可能长度，并非实际使用长度
例子：同样的查询结果，精度越小越好；
变长字段需要额外的2字节，null需要额外的一字节
所以索引最好不要为null，null让统计更复杂，并需要一个额外的字节存储

ref

哪些列或常量被用于查找索引列的值，如果可能，则是一个常数
- t2全表扫描：小表驱动大表
- t1引用：shared.t2.col1：t1.col1 = t2.col1
- t1引用：const：t1.col2 = ‘ac’

rows

根据表统计信息和索引选用情况，大概估算找到所需记录需要读取的行数
行数越少越好，例如下图，图1的第一个查询需要寻找640行；而图2的第二个查询利用索引只需要查询142行

每张表有多少行被优化器查询过

Extras

先上总结：
Using Filesort：排序索引失效，常见于OrderBy/GroupBy
排序Order By：与前面where顺序相连、个数一致
# Using where; Using index; Using filesort
explain select col1 from t1 where col1 = 'ac' order by col3;	
# Using where; Using index;
explain select col1 from t1 where col1 = 'ac' order by col2, col3;	
分组Group By：用什么分组，就建什么索引
# Using where; Using index; Using temporary; Using filesort
explain select col1 from t1 where col1 in ('ac','ab') group by col2;
# Using where; Using index for group-by
explain select col1 from t1 where col1 in ('ac','ab') group by col2;
Using Temporary：需要创建临时表，常见于GroupBy