如何理解MySQL执行计划中的各个参数及含义

这篇文章给大家介绍如何理解MySQL执行计划中的各个参数及含义，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。

本文是对于MySQL执行计划的解析，主要解释了MySQL执行计划中的各个参数及含义。

possible_keys

显示可能应用在这张表中的索引,一个或者多个；

查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用(可能自己创建了4个索引,在执行的时候,

可能根据内部的自动判断,只使用了3个)。

先创建索引
CREATE INDEX IDX_EMP_01 ON employee(dep_id);
CREATE INDEX IDX_EMP_02 ON employee(dep_id,NAME);
查看索引
mysql> show index from employee;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| employee |          0 | PRIMARY    |            1 | id          | A         |           8 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| employee |          1 | IDX_EMP_01 |            1 | dep_id      | A         |           4 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| employee |          1 | IDX_EMP_02 |            1 | dep_id      | A         |           4 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| employee |          1 | IDX_EMP_02 |            2 | name        | A         |           8 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.06 sec)

可以看到表上有3个索引。

进行查询，并且查看执行计划：

EXPLAIN 
SELECT ID
FROM   EMPLOYEE
WHERE  DEP_ID = 1
       AND NAME = '鲁班';
+----+-------------+----------+------------+------+-----------------------+------------+---------+-------------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys         | key        | key_len | ref         | rows | filtered | Extra       |
+----+-------------+----------+------------+------+-----------------------+------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | EMPLOYEE | NULL       | ref  | IDX_EMP_01,IDX_EMP_02 | IDX_EMP_02 | 68      | const,const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+------+-----------------------+------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.05 sec)

表上有三个索引，其中索引IDX_EMP_01是dep_id的单列索引，IDX_EMP_02是列dep_id和列name的复合索引，

在进行查询时，可能会用到索引，因为有过滤条件dep_id=1，所以会考虑使用这两个索引，因为索引的第一列都是dep_id，

此时的possiable_keys为IDX_EMP_01和IDX_EMP_02。

key

实际使用的索引,如果为NULL,则没有使用索引 ;

查询中若使用了覆盖索引 ,则该索引仅出现在key列表中。

possible_keys与key关系：possiable_keys表示理论应该用到哪些索引，key表示实际用到了哪些索引。

还是和上面一样：

EXPLAIN 
SELECT ID
FROM   EMPLOYEE
WHERE  DEP_ID = 1
       AND NAME = '鲁班';
+----+-------------+----------+------------+------+-----------------------+------------+---------+-------------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys         | key        | key_len | ref         | rows | filtered | Extra       |
+----+-------------+----------+------------+------+-----------------------+------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | EMPLOYEE | NULL       | ref  | IDX_EMP_01,IDX_EMP_02 | IDX_EMP_02 | 68      | const,const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+------+-----------------------+------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.05 sec)

在possiable_keys里显示了两个索引，但是呢，并不是这两个索引都会使用，用哪一个需要由优化器自己决定。

通过key可以发现，使用的是IDX_EMP_02这个索引，因为where里同时有dep_id和name条件，

因此使用这个索引最为合理，效率最高。

通过key可以发现，对该表的真实的索引的使用情况。

key_len

表示索引中使用的字节数,可通过该列计算查询中使用的索引长度。

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

可以看到只使用索引的第一列时，长度为5个字节。

EXPLAIN 
SELECT ID
FROM   EMPLOYEE
WHERE  DEP_ID = 1
       AND NAME = '鲁班';
+----+-------------+----------+------------+------+-----------------------+------------+---------+-------------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys         | key        | key_len | ref         | rows | filtered | Extra       |
+----+-------------+----------+------------+------+-----------------------+------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | EMPLOYEE | NULL       | ref  | IDX_EMP_01,IDX_EMP_02 | IDX_EMP_02 | 68      | const,const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+------+-----------------------+------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.05 sec)

对于使用了整个索引时，显示的是该索引的大小，key_len由5变成了68，说明name这个字段在索引里占用的字节数为63。

并且，key_len显示的值是where里的索引的具体大小，不包括order by或者group by时使用的索引。

ref

索引是否被引入到, 到底引用到了哪几个索引。

ref列用来显示使用哪个列或常数与key一起从表中选择相应的行。它显示的列的名字（或const），此列多数时候为null。

EXPLAIN 
SELECT D.ID
      ,D.ADDRESS
FROM   DEPARTMENT D
      ,EMPLOYEE   E
WHERE  D.ID = E.DEP_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       | ref  | IDX_EMP_01,IDX_EMP_02 | IDX_EMP_01 | 5       | demo.D.id |    2 |   100.00 | Using index |
+----+-------------+-------+------------+------+-----------------------+------------+---------+-----------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

Ref是用来表示索引是否被使用，到底用了哪个索引，从执行计划可看到，对表d进行扫描时，没有使用索引，type为all，

是一个全表扫描，因此ref值为null。对表e的扫描时，type为ref，表示使用了索引，并且通过key可以看到确实使用了索引，

因此在ref列需要被标记，索引被引用了，并且把该索引引用了到了d表的id字段上。

Rows

rows列显示的是mysql解析器认为执行此SQL时必须扫描的行数。此数值为一个预估值，不是具体值，通常比实际值小。

没有建索引前：

EXPLAIN 
SELECT D.ID
      ,E.DEP_ID
FROM   DEPARTMENT D
      ,EMPLOYEE   E
WHERE  D.ID = E.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       | ALL   | NULL          | NULL    | NULL    | NULL |    8 |    12.50 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

通过执行计划可以看到对表d采用索引全扫的扫描方式，需要对整个索引进行扫描；对表e采用全表扫描的方式，

因此需要对整个数据集进行扫描，d表有5行，e表有8行，因此在rows里显示为5和8.

对e表的dep_id列创建索引：

create index idx_employee_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_employee_01 | idx_employee_01 | 5       | demo.D.id |    2 |   100.00 | Using index |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+-----------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

通过执行计划可以看到，对d表采用索引全扫的方式，需要对整个索引进行扫描；对表e采用ref的非唯一性索引扫描，

只需要扫描索引的部分数据，d表有5行数据，所以rows为5，对于e表，扫描满足条件的数据，rows为2。

表d的数据：
+----+----------------+---------+
| id | deptName       | address |
+----+----------------+---------+
|  1 | 研发部(RD)     | 2层     |
|  2 | 人事部(HR)     | 3层     |
|  3 | 市场部(MK)     | 4层     |
|  4 | 后勤部(MIS)    | 5层     |
|  5 | 财务部(FD)     | 6层     |
+----+----------------+---------+
5 rows in set (0.07 sec)
表e的数据：
+----+-----------+--------+------+---------+--------+
| id | name      | dep_id | age  | salary  | cus_id |
+----+-----------+--------+------+---------+--------+
|  1 | 鲁班      |      1 |   10 | 1000.00 |      1 |
|  2 | 后裔      |      1 |   20 | 2000.00 |      1 |
|  3 | 孙尚香    |      1 |   20 | 2500.00 |      1 |
|  4 | 凯        |      4 |   20 | 3000.00 |      1 |
|  5 | 典韦      |      4 |   40 | 3500.00 |      2 |
|  6 | 貂蝉      |      6 |   20 | 5000.00 |      1 |
|  7 | 孙膑      |      6 |   50 | 5000.00 |      1 |
|  8 | 蔡文姬    |     30 |   35 | 4000.00 |      1 |
+----+-----------+--------+------+---------+--------+
8 rows in set (0.00 sec)
对d表的id和e表的dep_id查询：
+----+--------+
| ID | DEP_ID |
+----+--------+
|  1 |      1 |
|  1 |      1 |
|  1 |      1 |
|  4 |      4 |
|  4 |      4 |
+----+--------+
5 rows in set (0.00 sec)

Ref是对传过来的数据进行索引的扫描，d表的id有5行，e的dep_id值有多个，在进行等值匹配时，只有1和4满足条件。

对表d先进性操作，先扫描这5行数据，然后把1和4传给e表，所以对于e表只需要对1和4进行索引的扫描，只需要扫描两行。

Filtered

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

对全表进行扫描时：

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)

进行全表扫描时，会对所有的数据进行扫描，此时filtered为100.

当进行范围查询时：

EXPLAIN
SELECT * FROM EMPLOYEE WHERE DEP_ID > 4;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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 |    33.33 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

对dep_id大于4的数据进行查询，也是全表扫描，返回的数据为3行，优化器估计返回了三分之一的数据，标记为33.33。

再一次进行查询：

EXPLAIN
SELECT * FROM EMPLOYEE WHERE DEP_ID > 1;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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 |    33.33 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

对dep_id大于1的数据进行查询，也是全表扫描，返回的数据为5行，但是还是被认定filterd为33.33。

关于如何理解MySQL执行计划中的各个参数及含义就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。