MySQL高级性能优化---Explain

淩亂°似流年 2021-12-12 13:09 471阅读 0赞

**使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。  
通过EXPLAIN，我们可以分析出以下结果：  
表的读取顺序  
数据读取操作的操作类型  
哪些索引可以使用  
哪些索引被实际使用  
表之间的引用  
每张表有多少行被优化器查询**

# 1. Explain使用方法 #

explain sql语句  
示例: EXPLAIN select \* from employee WHERE cus\_id = 2

![在这里插入图片描述][20190711134327294.png]

# 2. 分析包含信息 #

## 2.1 id ##

select查询的序列号  
包含一组数字，当sql复杂的时候会有多个数字，表示查询中执行select子句或操作表的顺序

id值的三种情况:

1.  **id相同**

EXPLAIN select * from employee e, department d WHERE e.dep_id = d.id

![在这里插入图片描述][20190711135320579.png]  
id相同时执行顺序由上而下

1.  **id不同**

EXPLAIN SELECT * from department where id = (SELECT id from employee WHERE id = 4)

![在这里插入图片描述][20190711135957600.png]  
如果是子查询，id序号会递增，id值越大优先级越高，优先被执行  
3. **id相同不同，同时存在**  
可以认为是一组，从上往下执行。在所有组中，id值越大优先级越高，越先执行。deriverd衍生出来的虚表

EXPLAIN SELECT * from employee e,
    (SELECT * from department GROUP BY id) t
    where e.dep_id = t.id

![在这里插入图片描述][20190711142032231.png]  
**总结：  
相同，顺序从上往下走。不同看谁大，谁大谁先执行。大的先执行**

## 2.2 select\_type ##

作用: 查询类型，主要用于区别普通查询，联合查询，子查询等复杂查询  
结果值

### 2.2.1 SIMPLE ###

简单select查询，查询中不包含子查询或UNION  
![在这里插入图片描述][20190711143846143.png]

### 2.2.2 PRIMARY ###

查询中若包含任何复杂的子查询，最外层查询被标记为PRIMARY  
![在这里插入图片描述][20190711144213277.png]

### 2.2.3 SUBQUERY ###

查询中的子查询  
![在这里插入图片描述][20190711144312926.png]

### 2.2.4 DERIVED ###

在from列表中包含的子查询被标记为DERIVED(衍生)，把结果放在临时表中  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70]

### 2.2.5 UNION， UNION RESULT ###

从union表获取结果select，两个union合并的结果集的最后  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 1]

## 2.3 table ##

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

## 2.4 partitions ##

如果查询是基于分区表的话，会显示查询访问的分区

## 2.5 type（重点） ##

type代表访问表的类型，分辨**全表的扫描**还是**条件的扫描**还是**范围的扫描**等

type结果值讲解顺序以最好到最差的顺序讲解

### 2.5.1 system ###

表中有一行记录(系统表)，一次性就找到了这个记录，就会出现system类型。 这是const类型的特例，平时不会出现

### 2.5.2 const ###

表示通过索引一次就找到了。const用于比较primary或者unique索引。直接查询主键或者唯一索引。因为只匹配一行数据，所以很快。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 2]

### 2.5.3 ref (查询的结果是多行不是唯一对应) ###

非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问。它返回所有匹配某个单独值的行。可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。

下边的dep\_id列创建的普通索引 （一个普通索引，另一个主索引）  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 3]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 4]

### 2.5.4 eq\_ref（查询的结果唯一对应） ###

唯一性索引扫描。对于每个索引键，**表中只有一条记录与之匹配**。常见于主键或唯一索引

（两个建都是唯一索引或主键）  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 5]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 6]

### 2.5.5 range ###

范围查找  
一般就是你在where语句中出现 between > < in等查询时会出现，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点和结束于另一点，不用扫描全部索引  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 7]

### 2.5.6 index ###

查询索引。 我们以主键，普通索引，普通字段来举例  
index与all区别为index类型只遍历索引树，通常比all要快，因为索引文件通常比数据文件要小。all和index都是读全表，但index是从索引中读取，all是从硬盘当中读取

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 8]  
![在这里插入图片描述][20190711154510843.png]  
![在这里插入图片描述][2019071115452398.png]

### 2.5.7 ALL ###

全表扫描。当出现all的时候一定要对sql进行优化

## 2.6 possible\_keys 理论应该用到的索引 ##

## 2.7 key（重要） 实际用到的索引 ##

**2.6 possible\_keys 和 2.7 keys 一起讲解**

key与possible\_keys 主要作用是查看是否使用了建立的索引，也即判断索引失效，在建立多个索引的情况下，mysql最终用到了哪一个索引。  
实际使用的索引，如果为NULL,则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中。possible\_keys与key的关系是理论应该被用到的索引和实际用到了哪些索引。  
覆盖索引：查询的字段和建立的字段刚好吻合，这种情况我们称为覆盖索引  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 9]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 10]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 11]

## 2.8 key\_len ##

表示索引中使用的字节数，可通过该列计算查询中使用的索引长度  
可以使用key\_len来判断索引是否失效。当查询的条件是索引，但是key\_len是null时表示索引失效  
![在这里插入图片描述][20190711162050971.png]

## 2.9 ref ##

索引是否被引用到，到底用了哪几个索引，引用了哪个表的哪个字段  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 12]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 13]

## 2.10 rows ##

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。每张表有多少行被优化器查询过。根据建立索引和没有建立索引的情况不同  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 14]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 15]

## 2.11 fitered ##

满足查询的记录数的比例，注意是百分比，不是具体记录数。值越大越好，fitered列的值依赖统计信息，并不十分准确。

## 2.12 Extra ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 16]

### 2.12.1 Using filesort（需要优化） ###

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行排序，mysql中无法利用索引完成排序操作成为**文件排序**。出现这个情况需要优化

### 2.12.2 Using temporary（需要优化） ###

使用了临时表保存中间结果，Mysql在对查询结果排序时，使用了临时表，常见于排序order by和分组查询group by

### 2.12.3 using index ###

出现这个说明mysql使用了覆盖索引，避免访问了表的数据行，效率不错！

### 2.12.4 using where ###

这说明服务器在存储引擎收到行后将进行过滤。有些where中的条件会有属于索引的列，当它读取使用索引的时候，就会被过滤，所以会出现有些where语句并没有在extra列中出现using where这么一个说明。

### 2.12.3 using join buffer ###

表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。

### 2.12.4 impossible where（需要优化） ###

where子句的值永远是false，不能用来获取任何数据

SELECT * FROM t_user WHERE id = '1' and id = '2'

[20190711134327294.png]: /images/20211211/a1d7e4218b1c4c92adcaa2d204d6ee34.png
[20190711135320579.png]: /images/20211211/09169b9e7e6c417ea0a09575a6d88a5f.png
[20190711135957600.png]: /images/20211211/4b6597f0d842416ea8fa39aa16a31aba.png
[20190711142032231.png]: /images/20211211/0d0d96f346174ee0b5e0b3869e252e86.png
[20190711143846143.png]: /images/20211211/ae5ad523a6f6455684ee8a795b5bd1c6.png
[20190711144213277.png]: /images/20211211/47cd962fd8974a73b647f88d5765577c.png
[20190711144312926.png]: /images/20211211/67840aed260245f4a03f40796185f72b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70]: /images/20211211/fa10844a1d99496dbd41cc1155cd2ad7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 1]: /images/20211211/da650adf77a740fd994ede62b1ab4448.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 2]: /images/20211211/41cca61acf0948ebb07892e9c2482711.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 3]: /images/20211211/9bf88ae61be94345afdd5aafd77eb0bd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 4]: /images/20211211/63b154d984c74f33a645d88414674d33.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 5]: /images/20211211/a8a0d3e63a9f4af0bd6dfe5e53c5e4a3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 6]: /images/20211211/64894ad43d154c0c9488f3ff96706efb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 7]: /images/20211211/eec02e3197794c1ea679b9f31897c16c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 8]: /images/20211211/f91f0d1e5d9d4d7abd0fbe045163e56c.png
[20190711154510843.png]: /images/20211211/a2986aebcfcf4722b3bc80ca4b7b0aa8.png
[2019071115452398.png]: /images/20211211/c6720438bd36421690f83f56862d893e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 9]: /images/20211211/9fecc61ef0314578a42e482268de5667.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 10]: /images/20211211/3200552507c64c82bc2721fe01c0117a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 11]: /images/20211211/c9703afd5c494decb937c9b228de47af.png
[20190711162050971.png]: /images/20211211/e1ef529e046d45c9b92569b196ef48dc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 12]: /images/20211211/cea6d54241d34dd18b5982b4f2243b95.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 13]: /images/20211211/1732161219394fec87bea03ac01fc3d6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 14]: /images/20211211/c846b7bc2023411c98259b5fd4ceac3b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 15]: /images/20211211/fc18ce710f3b4559936786dae25c17da.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 16]: /images/20211211/f89a5000f70f4f139f63c18e115ca085.png