Mysql如何看懂执行计划

背景:

最近，公司项目，遇到上传和查询异常缓慢的情况，老大要我来优化一下，结果发现是上一位大神，在建表的时候没有，建索引，我是通过EXPLAIN 来进行如何优化你的sql的，今天我们就来好好学学EXPLAIN。

工具和数据库:

Mysql 8.0

Navicat 15

数据库引擎：Innodb

样例一：

EXPLAIN select * from t_test_20230418 where id = 1 and age> 1

一、id列

1. id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的
2. id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行

样例：

二、select_type列  

1、SIMPLE: 简单查询。查询不包含子查询和union

如下:

 

2、primary：复杂查询中最外层的 select

3、subquery：包含在 select 中的子查询（不在 from 子句中）

4、derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表

 三、type列（重要）

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。
依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL一般来说，得保证查询达到range级别，最好达到ref。

1、system
表示这一步只返回一行数据，如果这一步的执行对象是一个驱动表或者主表，那么被驱动表或者子查询只是被访问一次。

2、const

表示这个执行步骤最多只返回一行数据。const通常出现在对主键或唯一索引的等值查询中，例如对表t主键id的查询：

根据主键查询:

type 为 const

3、eq_ref

eq_ref类型一般意味着在表关联时，被关联表上的关联列走的是主键或者唯一索引。例如，表tb_user关联tb_user_info表，关联列分别是两张表的主键列 ： 

上面SQL执行时，tb_user表是驱动表，tb_user_infot是被驱动表，被驱动表的关联列是主键id，type类型为eq_ref。

所以，对于eq_ref类型来说有一个重要的特点就是：这一步涉及到的表是被驱动表；这一步中使用到唯一索引或主键。除了system和const之外，这是效果最好的关联类型。

4、ref

与上面相反，如果执行计划的某一步的type是ref的话，表示这一步的关联列是非唯一索引。例如：

所以ref的特点是：表示这一步访问数据使用的索引是非唯一索引。

5、range 范围扫描

在有索引的列上取一部分数据。常见于在索引列上执行between and操作。

如下

6、index：
索引全扫描，一般发生在覆盖索引的时候，也就是对有索引列发生一次全扫描。

7、ALL 是全表查询

三、possible_keys列

这一列显示查询可能使用哪些索引来查找。

 explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

 四、key列

这一列显示mysql实际采用哪个索引来优化对该表的访问。实际走的索引。

如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

五、key_len列

这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

举例来说，film_actor的联合索引idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找

key_len计算规则如下：

字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节

char(n)：如果存汉字长度就是 3n 字节
varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节
date：3字节
timestamp：4字节
datetime：8字节

六、ref列
        这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id

七、rows列

        这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

很多人，其实都忽略了，最后一列,extra,其实这一列特别重要

1. no tables used

当查询语句中没有from子句时将会提示该额外信息。

explain select 1;

 2. impossible where

查询语句的where子句永远为false时将会提示该额外信息。

explain select * from tb_user where 1!=1;

 

3. using index

使用覆盖索引执行查询时，extra列将会提示该额外信息。

explain select key1 from s1 where key1='a';

 

4. using index condition

有些搜索条件中虽然出现了索引列，但是却不能充当边界条件来形成扫描区间，也就是不能用来减少需要扫描的记录数量，将会提示该额外信息。

如果在查询语句的执行过程中使用索引下推特性，在extra列中将会显示using index condition。

5. using where(显示这个一般性能比较差，需要优化)

当某个搜索条件需要在server层进行判断时，在extra列中会提示using where。

explain select * from s1 where common_field='a';

 

6. using filesort

在有些条件下，当对结果集中的记录进行排序时，是可以使用到索引列的。

explain select * from s1 order by common_field limit 10;

 注意：如果查询中需要使用文件排序的记录非常多，这个过程还是很耗费性能的。我们可以尝试将文件排序的执行方式改为使用索引进行排序。

7. using tempory

在许多查询的执行过程中，MySQL可能会借助临时表来完成一些功能，比如去重，排序之类的。比如我们在执行许多包含distinct、group by、union等子句的查询过程中，如果不能有效利用索引来完整查询，mysql可能通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的extra列将会显示using temporary提示。
 

explain select distinct common_field from s1;

 

如有不对，请大家指出，谢谢！