MySQL执行计划中的各个参数及含义是什么

发布时间：2021-10-08 16:05:30 作者：柒染
来源：亿速云阅读：124

本篇文章给大家分享的是有关MySQL执行计划中的各个参数及含义是什么，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。

TABLE

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

EXPLAIN 
SELECT *
FROM   EMPLOYEE   E
      ,DEPARTMENT D
      ,CUSTOMER   C
WHERE  E.DEP_ID = D.ID
       AND E.CUS_ID = C.ID;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref           | rows | filtered | Extra                                              |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | C     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL          |    1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | E     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL          |    8 |    12.50 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | D     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | demo.E.dep_id |    1 |   100.00 | NULL                                               |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+----------------------------------------------------+
3 rows in set, 1 warning (0.04 sec)

Id完全相同，都是1，并且select_type的类型为simple，所以是对单表进行扫描，第一步执行时，操作的数据是c的，

第二步在执行时，操作的数据是e的，最后执行时，操作的数据是d的。

Partitions

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

Type

Type表示访问类型，以下为最好的到最差的访问类型的排列

结果值最好到最差：

Type：null、system、const、eq_ref、ref、range、index、ALL

type要求：

一般来说,保证查询至少达到range级别；

最好能达到ref。

NULL

Null是最好的访问方式，MySQL对语句进行优化分解后，在执行阶段用不着访问表或者索引（在索引列中查询最小值）。

EXPLAIN
SELECT MIN(ID) FROM EMPLOYEE WHERE ID < 10;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.03 sec

可以看到此时的type的类型为null，不经过扫描表或者索引，访问的效率是最高的

SYSTEM

SYSTEM表示表里只有一行记录时，const类型的特例。

EXPLAIN
SELECT * FROM (SELECT * FROM CUSTOMER LIMIT 1) A;
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table      | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
|  2 | DERIVED     | CUSTOMER   | NULL       | ALL    | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.03 sec)

先执行的id=2的，把子查询里的结果放在临时表里，该临时表只有1行记录，然后在外部查询该临时表，

此时id=1对应的type的值为system。

Const

确定只有一行匹配的时候，mysql优化器会在查询前读取它并且只读取一次，速度非常快；

const用于比较primary 或者 unique索引；直接查询主键或者唯一索引。

EXPLAIN 
SELECT * FROM EMPLOYEE WHERE ID = 1;
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | EMPLOYEE | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

在查询时，使用了id列的主键索引(或者唯一索引)，是唯一的，所以type的结果为const。

System和const对应的记录都只有一条，system对应的是表的记录只有一条；const对应的是通过主键索引或者唯一索引，

只查询到表的记录的一条，两个值都对应一条记录，但是const对应的表的记录不一定只有一行，大部分情况下，

const的情况很多，system很少。

Eq_ref

对于每个来自于前面的表的行组合，从该表中读取一行，常用在一个索引是unique key或者primary key；

对于每个索引键,表中只有一条记录与之匹配。

EXPLAIN 
SELECT *
FROM   EMPLOYEE   E
      ,DEPARTMENT D
WHERE  E.ID = D.ID;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------+
|  1 | SIMPLE      | D     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL      |    5 |   100.00 | NULL  |
|  1 | SIMPLE      | E     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | demo.D.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

Id相同，从上往下执行，先对表d进行扫描，然后扫描e；d扫描的结果id的字段与e扫描的结果id进行连接查询，

由于e的id字段有主键索引，所以对与该表的扫描的type为eq_ref。

Ref

对于来自前面的表的行组合，所有有匹配索引值的行都从这张表中读取,如果联接只使用键的最左边的前缀，

或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。

ref可以用于使用=或<=>操作符的带索引的列；

非唯一性索引扫描,返回匹配某个单独值的所有行；

本质上也是一种索引访问；

它返回所有匹配某个单独值的行；

可能会找到多个符合条件的行,；

所以它应该属于查找和扫描的混合体。

查询employee表dep_id和department表的id字段。
SELECT E.DEP_ID AS E_DEP_ID
      ,D.ID     AS D_ID
FROM   EMPLOYEE   E
      ,DEPARTMENT D
WHERE  E.DEP_ID = D.ID;
+----------+------+
| E_DEP_ID | D_ID |
+----------+------+
|        1 |    1 |
|        1 |    1 |
|        1 |    1 |
|        4 |    4 |
|        4 |    4 |
+----------+------+
5 rows in set (0.01 sec)
查看该执行计划：
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref           | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------------+
|  1 | SIMPLE      | E     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL          |    8 |   100.00 | Using where |
|  1 | SIMPLE      | D     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | demo.E.dep_id |    1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

对于表e的扫描的type为all，全表扫描。

在e表的dep_id字段建立索引：

create index idx_emp_01 on employee(dep_id);
再次查看执行计划：
+----+-------------+-------+------------+-------+---------------+------------+---------+-----------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref       | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------------+---------+-----------+------+----------+-------------+
|  1 | SIMPLE      | D     | NULL       | index | PRIMARY       | PRIMARY    | 4       | NULL      |    5 |   100.00 | Using index |
|  1 | SIMPLE      | E     | NULL       | ref   | idx_emp_01    | idx_emp_01 | 5       | demo.D.id |    2 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------------+---------+-----------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

对于e表的type由all变成了ref，表示使用了索引，但是由于dep_id的值1多个重复值，因此会根据该值查询出多个记录，

所以该type为ref，表示使用了索引，但是由于存在重复值，因此只能是ref。

Range

索引范围扫描：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。

key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL，当使用=、<>、>、>=、<、<=、IS NULL、<=>、

BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range。

这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点而结束语另一点，不用扫描全部索引。

EXPLAIN 
SELECT * FROM EMPLOYEE E WHERE ID BETWEEN 1 AND 3;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | E     | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

查询id在1到3之间的数据，由于id有索引，因此是对该字段的索引的范围扫描，type为range。

Index

Full Index Scan 索引全扫描，index与All区别为index类型只遍历索引树,通常比All要快,因为索引文件通常比数据文件要小，

all和index都是读全表,但index是从索引中读取,all是从硬盘当中读取。

EXPLAIN 
SELECT ID FROM EMPLOYEE;
+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | EMPLOYEE | NULL       | index | NULL          | idx_emp_01 | 5       | NULL |    8 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

查询表里id字段，需要对整个表进行扫描，但是由于id有字段，所以不用从磁盘去扫描全数据，

只需要对整个索引进行全扫描，此时type值为index，表示对索引的全扫描。

All

将全表进行扫描,从硬盘当中读取数据。

EXPLAIN 
SELECT * FROM EMPLOYEE;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | EMPLOYEE | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

对全表数据的查询，此时只能对全表进行扫描，采用全表扫描的方式，此时type的值为all，表示全表扫描。

在对表进行扫描的方式有8种，性能依次为null、system、const、eq_ref、ref、range、index、ALL。

其中all是全表扫描，性能最差；index是索引全扫描，性能也不好，因此，尽量想办法时扫描的type值至少是

变为range以上，走索引，并且是高效的索引。

以上就是MySQL执行计划中的各个参数及含义是什么，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注亿速云行业资讯频道。