mysql性能分析（explain）

## explain是什么?
**模拟优化器查看执行计划**
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的**性能瓶颈**

## explain能干什么？
- 表的读取顺序

- 那些索引可以使用

- 数据读取操作的操作类型

- 那些索引被实际使用

- 表之间的引用

- 每张表有多少行被物理查询

## explain怎么玩？
explain + SQL语句
```
实例：
explain select * from t1, t2, t3 where t1.id=t2.id and t2.id=t3.id;
```
## 各字段解释

### id查询序列号
select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

关注点：每个id号码，表示一趟独立的查询。一个sql 的查询趟数越少越好。

四种情况：
1. id相同，执行顺序由上至下。例如上图

2. id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

3. id既有相同又有不同

4. id为null最后执行

### select_type
查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询
| 查询类型 | 描述 |
| ---------------------- | ------------------------------------------------------------ |
| `SIMPLE` | 简单的 SELECT(没有 使用UNION或者 子查询（PS：单表查询）) |
| `PRIMARY` | 最外层的Select 作为primary 查询。(PS:含有子查询的情况,但是并不复杂)案例1 |
| `DERIVED` | 在from 查询语句中的（派生，嵌套很多）子查询。需要临时表。 EXPLAIN select * from t1, (select 2) as v; |
| `SUBQUERY` | 在SELECT或WHERE列表中包含了子查询。案例2 |
| `DEPENDENT SUBQUERY` | 第一个查询是子查询，依赖于外部查询（相关查询）。案例3 |
| `UNCACHEABLE SUBQUERY` | 子查询结果不能被缓存， 而且必须重写（分析）外部查询的每一行。案例4 |
| `UNION` | UNION中的第二个或后面的SELECT语句。案例5 |
| `UNION RESULT` | 结果集是通过union 而来的。案例5 |

### table
显示这一行的数据是关于哪表的

### partitions
代表分区表中的命中情况，非分区表，该项为null

### type

![](/media/202502/2025-02-22_204031_0949780.14442391563129364.png)
type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

**一般来说，得保证查询至少达到range级别，最好能达到ref。**

常见：system > const > eq_ref > ref > range > index > ALL

**system**：表仅有一行，这是const类型的特列，平时不会出现，这个也可以忽略不计

**const**：针对主键或唯一索引的等值查询扫描，最多只返回一行数据。const 查询速度非常快, 因为它仅仅读取一次即可。例如：select * from t1 where t1.id=1

**eq_ref**：多表连接中使用primary key或者 unique key作为关联条件。例如：select * from t1,t2 where t1.id=t2.id

**ref**：当搜索的索引列不是主键也不是唯一索引时才会发生。例如：select * from t1 where t1.content=''；（content列创建了单值索引）

**range**：只检索给定范围的行，一般就是在你的where语句中出现了between、<、>、!=等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。例如：select * from t1 where t1.id<10

**index**：该联接类型与ALL相同，只是只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。（explain select id from t1）

**all**：Full Table Scan，将遍历全表以找到匹配的行。（explain select * from t1）

其他：

**index_merge**：在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的sql中。

**ref_or_null**：类似于ref，在需要null值的情况下。查询优化器会选择用ref_or_null连接查询。例如：explain select * from t1 where content = "" or content is null;

**index_subquery**：利用索引来关联子查询，不再全表扫描。

**unique_subquery**：该联接类型类似于index_subquery。 子查询中的唯一索引。

### possible_keys
显示当前查询可能用到的索引，一个或多个。查询涉及到的字段上若存在索引 则列出，**但不一定被查询实际使用**

### key
当前查询实际使用的索引。如果为NULL则没有使用索引。

**查询中若使用了覆盖索引，则该索引和查询的select字段重叠**
![](/media/202502/2025-02-22_204234_8344140.41831872731475617.png)

### key_len
表示索引中使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

key_len字段能够帮你检查是否充分的利用上了索引。

![](/media/202502/2025-02-22_204418_8017040.344954320633999.png)

如何计算：

1. 先看索引上字段的类型 + 长度。比如 int=4；varchar(20) =20；char(20) =20

字符串：char(n)=n；varchar(n)=n

数值类型：tinyint=1；smallint=2；int=4；bigint=8

时间类型：date=3；timestamp=4；datetime=8

2. 如果是varchar或者char这种字符串字段，视字符集要乘不同的值。比如：utf-8要乘 3，GBK要乘2

3. varchar要额外加2个字节

4. 允许为NULL的字段额外加1个字节

索引字段最好不要为NULL，因为NULL让统计更加复杂，并且需要额外一个字节的存储空间。

备注：**key_len不包含order by/group by使用到的索引列。**

### ref
哪些列或常量被用于查找索引列上的值。显示索引的哪一列被使用了，如果可能的话，是一个常数。常见的有：const（常量），func，NULL，字段名

### rows
rows列显示MySQL认为它执行查询时必须检查的行数。**越少越好**

### filtered
通过查询条件获取的最终记录行数 占 通过type字段指明的搜索方式搜索出来的记录行数 的 百分比。

这个值越高越好：说明更多的数据是通过索引搜素出来的。

### extra
不适合在其他列中显示但十分重要的额外信息

**Using filesort**：文件排序，说明使用了非索引字段排序，此时mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。解决方案：对排序字段创建索引

![](/media/202502/2025-02-22_204642_4294750.5877322625304939.png)

**Using temporary**：使了用临时表保存中间结果，常见于排序 order by 和分组查询 group by。group by和order by同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。
![](/media/202502/2025-02-22_204716_2551880.1773628370190684.png)
**USING index**：利用索引进行了查询、排序或分组。例如：explain select * from t1 where content='xxx';（content创建了索引）

**Using where**：表明使用了where过滤。explain select * from t1 where content>'';（content创建了索引）

**using join buffer**：使用了连接缓存，非主键关联

![](/media/202502/2025-02-22_204741_3247540.2125320375118277.png)

**impossible where**：where子句的值总是false。（EXPLAIN select * from t_emp where false;）

## 小结

**表的读取顺序：id**（趟数越少越好）

那些索引可以使用：possible_keys

**数据读取操作的操作类型：type**（system > const > eq_ref > ref > range > index > ALL）

**那些索引被实际使用：key**

**那些索引列被实际使用：key_len**

表之间的引用：table

**每张表有多少行被物理查询：rows**（越小越好）

**额外的重要信息：extra**

避免：Using filesort（排序）、Using temporary（分组）、using join buffer（多表联查）

## explain的局限性
1. EXPLAIN不考虑各种Cache

2. EXPLAIN不能显示MySQL在执行查询时所作的优化工作

3. EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况

4. 部分统计信息是估算的，并非精确值