mysql之expain执行计划详解

╰半夏微凉° 2023-06-30 01:51 3阅读 0赞

mysql之expain执行计划详解  
  
EXPLAIN SELECT \* FROM USER WHERE NAME = '张三'  
![20200118141849693.png][]  
expain出来的信息有12列，分别是**id、select\_type、table、partitions、type、possible\_keys、key、key\_len、ref、rows、filtered、Extra**  
  
**具体描述：**  
**id:选择标识符  
select\_type:表示查询的类型。  
table:输出结果集的表  
partitions:匹配的分区  
type:表示表的连接类型       
possible\_keys:表示查询时，可能使用的索引  
key:表示实际使用的索引  
key\_len:索引字段的长度  
ref:列与索引的比较  
rows:扫描出的行数(估算的行数)  
filtered:按表条件过滤的行百分比  
Extra:执行情况的描述和说明**  
  
**具体讲解几个重要参数**  
1、type

**对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。**

常用的类型有：** ALL、index、range、 ref、eq\_ref、const、system、****NULL（从左到右，性能从差到好）**

**ALL：**Full Table Scan， MySQL将遍历全表以找到匹配的行。当查询字段或where条件没有建立索引，就会走全盘扫描

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

**range:**只检索给定范围的行，使用一个索引来选择行。当使用=、<>、>、>=、<、<=、IS NULL、<=>、LIKE、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range

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

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

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

**NULL:** MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。当使用MAX，MIN函数时会用到  
  
例子：  
**range（范围）：**  
EXPLAIN SELECT \* FROM USER WHERE age <= 18  
![2020011815073636.png][]  
  
null(常量)：  
EXPLAIN SELECT MAX(id) FROM USER  
![20200118151058903.png][]  
  
**const:**  
EXPLAIN SELECT \* FROM USER WHERE id = 1;  
![20200118151202263.png][]  
或者  
EXPLAIN SELECT \* FROM USER AS u,user\_account AS ua WHERE u.id = ua.user\_id AND ua.id = 1  
![20200118153227177.png][]  
  
**eq\_ref(多表关联查询，关联字段为唯一索引)：**  
EXPLAIN SELECT \* FROM USER AS u,user\_account AS ua WHERE u.number\_no = ua.number\_no  
![20200118154323425.png][]  
  
**ref(多表关联查询，关联字段为普通索引)**  
EXPLAIN SELECT \* FROM USER AS u,user\_account AS ua WHERE u.number\_no = ua.number\_no  
![20200118154709163.png][]  
  
**index:**  
EXPLAIN SELECT id,age FROM USER   
![20200118180739130.png][]  
  
**重点说明：在做where条件范围查询、order by 排序、group by中，select \* 会导致索引失效，原因是索引最多使用一个，不是查询出的字段都建立索引，所以导致会全盘扫描表，最好使用组合索引。**  
**当查询条件是select \* ，那么在做range范围查询、order by 、group by时候不走索引，mysql走全盘扫描，当一个是当查询条件是select id,age，那么在做range范围查询时候会走索引，不会全盘扫描**  
![20200118160119322.png][]  
EXPLAIN **SELECT \*** FROM USER WHERE age <= 20;  
结果如下：**索引失效**  
![20200118160243298.png][]  
或者  
EXPLAIN SELECT \* FROM USER ORDER BY age;  
![20200118175740679.png][]  
  
  
EXPLAIN **SELECT id, age** FROM USER WHERE age <= 20;  
结果如下：**索引有效**  
![2020011817495593.png][]  
或者  
EXPLAIN SELECT id,age FROM USER ORDER BY age;  
![20200118175848955.png][]  
  
2、**Extra**

**Using where**:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

**Using temporary：**表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by

**Using filesort：**当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”

-- 测试Extra的filesort
    EXPLAIN SELECT * FROM USER ORDER BY age;

**Using join buffer：**改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

**Impossible where：**这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。

**Select tables optimized away：**这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

No tables used：Query语句中使用from dual 或不含任何from子句

[20200118141849693.png]: https://img-blog.csdnimg.cn/20200118141849693.png
[2020011815073636.png]: https://img-blog.csdnimg.cn/2020011815073636.png
[20200118151058903.png]: https://img-blog.csdnimg.cn/20200118151058903.png
[20200118151202263.png]: https://img-blog.csdnimg.cn/20200118151202263.png
[20200118153227177.png]: https://img-blog.csdnimg.cn/20200118153227177.png
[20200118154323425.png]: https://img-blog.csdnimg.cn/20200118154323425.png
[20200118154709163.png]: https://img-blog.csdnimg.cn/20200118154709163.png
[20200118180739130.png]: https://img-blog.csdnimg.cn/20200118180739130.png
[20200118160119322.png]: https://img-blog.csdnimg.cn/20200118160119322.png
[20200118160243298.png]: https://img-blog.csdnimg.cn/20200118160243298.png
[20200118175740679.png]: https://img-blog.csdnimg.cn/20200118175740679.png
[2020011817495593.png]: https://img-blog.csdnimg.cn/2020011817495593.png
[20200118175848955.png]: https://img-blog.csdnimg.cn/20200118175848955.png