欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL优化之执行计划

程序员文章站 2022-07-06 12:45:18
前言 研究SQL性能问题,其实本质就是优化索引,而优化索引,一个非常重要的工具就是执行计划(explain),它可以模拟SQL优化器执行SQL语句,从而让开发人员知道自己编写的SQL的运行情况。 执行计划语法 执行计划的语法非常简单,就是在要执行的SQL语句前加上 即可。 以我们在上一篇文章中创建的 ......

前言

研究sql性能问题,其实本质就是优化索引,而优化索引,一个非常重要的工具就是执行计划(explain),它可以模拟sql优化器执行sql语句,从而让开发人员知道自己编写的sql的运行情况。

执行计划语法

执行计划的语法非常简单,就是在要执行的sql语句前加上explain即可。
以我们在上一篇文章中创建的student表为例:

mysql> explain select * from student where id = 1;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | simple      | student | null       | const | primary       | primary | 4       | const |    1 |   100.00 | null  |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

数据准备

为了更好的讲明白执行计划,我们将新建三张表,一张为employee表,一张为salary表,另一张为department表。其表结构以及数据如下:

employee表

e_id e_name d_id
1 zhang 1
2 wang 1
3 song 3
4 liu 2
5 wang 2

salary表

s_id s_salary
1 11000
2 8000
3 6500
4 5000
5 7200

department 表

d_id d_name
1 tech
2 hr
3 pd

三张表建表语句如下:

/* employee表创建 */
create table employee(
    e_id int(4) auto_increment,
    e_name varchar(20) default null,
    d_id int(4), 
    primary key(e_id) 
);
/* 创建索引 */
create unique index e_idx1 on employee(e_id);
create index e_idx2 on employee(e_name, d_id);
create index e_idx3 on employee(e_name);

/* salary表创建 */
create table salary(
    s_id int(4),
    s_salary decimal(15,2)
);
/* 创建索引 */
create unique index s_idx1 on salary(s_id);
create index s_idx2 on salary(s_salary);

/* department表创建 */
create table department(
    d_id int(4),
    d_name char(10) not null
);
/* 创建索引 */
create unique index d_idx1 on department(d_id);
create index d_idx2 on department(d_name);

/* employee表插入数据 */
insert into employee values(1, 'zhang', 1);
insert into employee values(2, 'wang', 1);
insert into employee values(3, 'song', 3);
insert into employee values(4, 'liu', 2);
insert into employee values(5, 'wang', 2);

/* salary表插入数据 */
insert into salary values(1, 11000);
insert into salary values(2, 8000);
insert into salary values(3, 65000);
insert into salary values(4, 5000);
insert into salary values(5, 7200);

/* department 表插入数据 */
insert into department values(1, 'tech');
insert into department values(2, 'hr');
insert into department values(3, 'pd');

如何去看执行计划

看执行计划,其实就是看explain所展示出来的列的含义。下面我们来逐一分析。

id

id用来表示sql语句查询的顺序。它遵循三条原则:

id 值情况 执行顺序 常见场景
1 id相同 按顺序执行,从上往下 关联表查询
2 id不同 id值越大,执行优先级越高 子查询
3 null 表示为一个结果集,不需要用它来查询 union语句

为了说明id的情况,不妨做一个如下查询:查询hr部门,工资为5000的员工的名字。
我们很容易就能写出sql语句:

mysql> select e.e_name from employee e, salary s, department d where e.e_id = s.s_id and e.d_id = d.d_id and s.s_salary = 5000 and d.d_name = 'hr';
+--------+
| e_name |
+--------+
| liu    |
+--------+
1 row in set (0.01 sec)

以上sql语句没有问题,但是我们现在要研究的并不是这个语句本身,而是执行计划,所以加上执行计划再执行一遍:

mysql> explain select e.e_name from employee e, salary s, department d where e.e_id = s.s_id and e.d_id = d.d_id and s.s_salary = 5000 and d.d_name = 'hr';
+----+-------------+-------+------------+--------+----------------+---------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys  | key     | key_len | ref           | rows | filtered | extra       |
+----+-------------+-------+------------+--------+----------------+---------+---------+---------------+------+----------+-------------+
|  1 | simple      | s     | null       | ref    | s_idx1,s_idx2  | s_idx2  | 8       | const         |    1 |   100.00 | using where |
|  1 | simple      | e     | null       | eq_ref | primary,e_idx1 | primary | 4       | testdb.s.s_id |    1 |   100.00 | using where |
|  1 | simple      | d     | null       | ref    | d_idx1,d_idx2  | d_idx1  | 5       | testdb.e.d_id |    1 |    33.33 | using where |
+----+-------------+-------+------------+--------+----------------+---------+---------+---------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

从以上结果可以看到,三张表的id都为1,所以这三张表是按照从上往下的顺序执行的,即 s->e->d的顺序。不难看出,这个顺序和我们编写sql的表的顺序是无关的。
注意:当id相同时,左连接和右连接可以破坏sql的执行顺序。
如果id相同,执行顺序靠什么控制的?
答:如果id相同,和表中的数据条数有关。

如果我要查pd部门所有人的薪水情况,这次改用子查询的方式:

mysql> select s.* from salary s where s.s_id = (select e.e_id from employee e where e.d_id = (select d.d_id from department d where d.d_name = 'pd'));
+------+----------+
| s_id | s_salary |
+------+----------+
|    3 | 65000.00 |
+------+----------+
1 row in set (0.00 sec)

其执行计划如下所示:

mysql> explain select s.* from salary s where s.s_id = (select e.e_id from employee e where e.d_id = (select d.d_id from department d where d.d_name = 'pd'));
+----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref   | rows | filtered | extra                    |
+----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+----------+--------------------------+
|  1 | primary     | s     | null       | const | s_idx1        | s_idx1 | 5       | const |    1 |   100.00 | null                     |
|  2 | subquery    | e     | null       | index | null          | e_idx2 | 68      | null  |    5 |    20.00 | using where; using index |
|  3 | subquery    | d     | null       | ref   | d_idx2        | d_idx2 | 30      | const |    1 |   100.00 | null                     |
+----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

可以看到,id为1,2,3,分别对应的表为s,e,d,根据id越大,执行优先级越高的原则,执行顺序应该是d->e->s。至于原因,其实很好理解,按照常规思维,要查salary表,首先要从查employee表查出员工id,而要查employee表,则要先从department表查出部门id,因此,查询顺序就是先查department,再查employee,最后查salary。

接下来演示一个union查询的例子,如:查询employee表中id为1和5的员工信息:

mysql> select * from employee where e_id = 1 union select * from employee where e_id = 5;
+------+--------+------+
| e_id | e_name | d_id |
+------+--------+------+
|    1 | zhang  |    1 |
|    5 | wang   |    2 |
+------+--------+------+
2 rows in set (0.01 sec)

其执行计划如下:

mysql> explain select * from employee where e_id = 1 union select * from employee where e_id = 5;
+----+--------------+------------+------------+-------+----------------+---------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys  | key     | key_len | ref   | rows | filtered | extra           |
+----+--------------+------------+------------+-------+----------------+---------+---------+-------+------+----------+-----------------+
|  1 | primary      | employee   | null       | const | primary,e_idx1 | primary | 4       | const |    1 |   100.00 | null            |
|  2 | union        | employee   | null       | const | primary,e_idx1 | primary | 4       | const |    1 |   100.00 | null            |
| null | union result | <union1,2> | null       | all   | null           | null    | null    | null  | null |     null | using temporary |
+----+--------------+------------+------------+-------+----------------+---------+---------+-------+------+----------+-----------------+
3 rows in set, 1 warning (0.01 sec)

上例很好的说明了这个问题,从id的值,很直观就能看出sql执行的顺序,先执行union的表,再执行前面的表,结果集通过union result显示出来。

select_type

select_type按字面意思,就是查询类型。常见的查询类型有以下几种:

id select_type 描述 常见场景
1 simple 不包含任何子查询或union查询 简单的单表查询
2 primary 包含子查询的最外层就是primary,意思为主查询语句 子查询
3 subquery selectwhere中包含的子查询语句 子查询
4 derived from语句中包含的查询(衍生查询) 临时表
5 union union查询的后一条查询语句 union查询
6 union result union查询的的结果集 union查询

simple

这个比较好举例,如下面的sql语句,查询employee表中id为1的员工信息:

mysql> explain select * from employee where e_id = 1;
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys  | key     | key_len | ref   | rows | filtered | extra |
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
|  1 | simple      | employee | null       | const | primary,e_idx1 | primary | 4       | const |    1 |   100.00 | null  |
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

出现simple的关键是,只能有当前一张表单表查询,且不涉及任何子查询、union查询、临时表查询。

primary 和 subquery

这两个都是子查询中会出现的,仍然以上面那条子查询的sql拿来分析:

mysql> explain select s.* from salary s where s.s_id = (select e.e_id from employee e where e.d_id = (select d.d_id from department d where d.d_name = 'pd'));
+----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref   | rows | filtered | extra                    |
+----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+----------+--------------------------+
|  1 | primary     | s     | null       | const | s_idx1        | s_idx1 | 5       | const |    1 |   100.00 | null                     |
|  2 | subquery    | e     | null       | index | null          | e_idx2 | 68      | null  |    5 |    20.00 | using where; using index |
|  3 | subquery    | d     | null       | ref   | d_idx2        | d_idx2 | 30      | const |    1 |   100.00 | null                     |
+----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

e表和d表都是subquery,因为它们是子查询语句,而s表则是primary,则是因为s表示select要输出的表,所以属于主查询。

derived

derived一般出现在临时表中。一般分两种情况:

  • 当from子查询的衍生查询只有一张表时,该临时表就是derived;
  • 当from子查询的衍生查询中,有union查询时,一般union的第一个查询为derived.
    如下例所示:
mysql> explain select t.* from (select e_name from  employee where e_id = 1 union select e_name from  employee where e_id = 5)  t;
+----+--------------+------------+------------+-------+----------------+---------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys  | key     | key_len | ref   | rows | filtered | extra           |
+----+--------------+------------+------------+-------+----------------+---------+---------+-------+------+----------+-----------------+
|  1 | primary      | <derived2> | null       | all   | null           | null    | null    | null  |    2 |   100.00 | null            |
|  2 | derived      | employee   | null       | const | primary,e_idx1 | primary | 4       | const |    1 |   100.00 | null            |
|  3 | union        | employee   | null       | const | primary,e_idx1 | primary | 4       | const |    1 |   100.00 | null            |
| null | union result | <union2,3> | null       | all   | null           | null    | null    | null  | null |     null | using temporary |
+----+--------------+------------+------------+-------+----------------+---------+---------+-------+------+----------+-----------------+
4 rows in set, 1 warning (0.00 sec)

union 和 union result

仍然可以拿上面union查询的例子来分析:

mysql> explain select * from employee where e_id = 1 union select * from employee where e_id = 5;
+----+--------------+------------+------------+-------+----------------+---------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys  | key     | key_len | ref   | rows | filtered | extra           |
+----+--------------+------------+------------+-------+----------------+---------+---------+-------+------+----------+-----------------+
|  1 | primary      | employee   | null       | const | primary,e_idx1 | primary | 4       | const |    1 |   100.00 | null            |
|  2 | union        | employee   | null       | const | primary,e_idx1 | primary | 4       | const |    1 |   100.00 | null            |
| null | union result | <union1,2> | null       | all   | null           | null    | null    | null  | null |     null | using temporary |
+----+--------------+------------+------------+-------+----------------+---------+---------+-------+------+----------+-----------------+
3 rows in set, 1 warning (0.01 sec)

前面第一部分查询:select * from employee where e_id = 1,它给的是primary,第二张表的查询select * from employee where e_id = 5就是union。而它们的结果集则是union result

table

table就是用到的表名,当有别名的时候,显示的是别名。

id table 描述 常见场景
1 原表名 当表没有别名时,显示的就是表名本身 表没有别名
2 别名 当表有别名时,显示的就是别名 表定义有别名
3 union<m,n> union查询时id为m和n的联表查询结果集的显示结果,m和n为id值 union查询

在前例中可以很明确的看到这点的演示。
如显示原表名:

mysql> explain select * from employee where e_id = 1;
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys  | key     | key_len | ref   | rows | filtered | extra |
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
|  1 | simple      | employee | null       | const | primary,e_idx1 | primary | 4       | const |    1 |   100.00 | null  |
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

显示别名:

mysql> explain select e.* from employee e where e.e_id = 1;
+----+-------------+-------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys  | key     | key_len | ref   | rows | filtered | extra |
+----+-------------+-------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
|  1 | simple      | e     | null       | const | primary,e_idx1 | primary | 4       | const |    1 |   100.00 | null  |
+----+-------------+-------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

从以上两个例子可以很明显的看出来,sql语句一模一样,第二个语句只是加了一个别名,所以table列显示的就变成了别名。

partitions

partions指的是查询涉及到的分区,如果不涉及分区,则显示为null;如果有分区,则显示的是分区情况。
要讲这个,需要先说一下表分区的概念。表分区指的是在物理上不是一块内存,但是在逻辑上仍然是一张表。这样的好处是可以合理利用硬盘空间,从而提高效率。
查询mysql服务是否支持表分区:

mysql> show plugins;

创建分区表:

mysql> create table tb_partition(
    ->     id int(4) auto_increment,
    ->     name varchar(20),
    ->     passwd char(20),
    ->     primary key(id)
    -> )partition by hash(id)
    -> partitions 4
    -> ;
query ok, 0 rows affected (0.59 sec)

注意,按hash分区时,分区的字段一定要是int型,且为主键,如果不是,则要将其转为主键才能分区成功。
关于表分区的更多内容,请参考这篇文章:mysql分区表
partitions字段可以有以下取值:

id partitions 描述
1 null 没有表分区,或有表分区但是查询数据不存在时
2 所有表分区均显示出来 查询所有数据,或所查询出来的数据覆盖到了所有的分区
3 显示具体表分区 表里有数据,显示为当前数据所在的表分区

示例1:没有表分区,显示为null。

mysql> explain select * from employee where e_id = 1;
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys  | key     | key_len | ref   | rows | filtered | extra |
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
|  1 | simple      | employee | null       | const | primary,e_idx1 | primary | 4       | const |    1 |   100.00 | null  |
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

示例2:有表分区,但是查询的结果为空。

mysql> explain select * from tb_partition 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 | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set, 1 warning (0.00 sec)

注意此时,它所展示的table也为null,这点在前文没有讲到,说明当使用到分区表,且查询数据不存在时,table取值为null。
示例3:查询表中所有数据,显示所有表分区。

mysql> explain select * from tb_partition;
+----+-------------+--------------+-------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table        | partitions  | type | possible_keys | key  | key_len | ref  | rows | filtered | extra |
+----+-------------+--------------+-------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | simple      | tb_partition | p0,p1,p2,p3 | all  | null          | null | null    | null |    4 |   100.00 | null  |
+----+-------------+--------------+-------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

示例4:查询结果存在,显示数据所在的分区。
先插入几条数据:

insert into tb_partition values(1,'zhangsan', '123456');
insert into tb_partition values(2,'lisi', '123123');
insert into tb_partition values(3,'mayun', '123321');
insert into tb_partition values(4,'trump', '654321');

再执行查询语句:

mysql> explain select * from tb_partition where id = 1;
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table        | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | extra |
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | simple      | tb_partition | p1         | const | primary       | primary | 4       | const |    1 |   100.00 | null  |
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

此时显示的分区是p1,也就是id = 1那条数据所在的分区。如果查询的结果不止一条,则显示所有数据的分区,这点应该不难想象,就不示例了。

type

type在sql优化中是一个很重要的概念,sql语句好不好,和该字段展示的值有很大关系。type的值有很多,常见的有以下这几种:

id type 描述
1 system 连接类型的特例,表中只有一条数据,相当于系统表
2 const 根据主键或唯一索引的主键查询查询结果只有1条记录
3 eq_ref 唯一索引扫描,对于每个索引键,只有一条记录与之对应
4 ref 针对非唯一或非主键索引,查询的结果可以有多条或0条
5 range 使用索引范围查询
6 index 遍历索引,只查询索引列,无须回表查询
7 all 全局扫描,当表没有索引或没用到索引时会出现,基本上等于没有任何优化

以上所列的顺序,基本上就是性能效率从高到低的排列顺序,即system>const>eq_ref>ref>range>index>all。

需要注意的是,type字段针对的是索引列,当表中不存在索引时,此时不管表中有多少数据,type都是all。实际的优化过程中,system和const级别都是可遇不可求的,能够达到ref级别,就说明已经达到了优化的效果。

system

这种情况一般很难达到,只有当查询系统表,衍生表只有一条数据的主查询时能够达到这个级别。

const

一般根据主键去做的单表查询,type都是这个级别。

mysql> explain select * from employee where e_id = 1;
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys  | key     | key_len | ref   | rows | filtered | extra |
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
|  1 | simple      | employee | null       | const | primary,e_idx1 | primary | 4       | const |    1 |   100.00 | null  |
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

需要注意的是,当使用复合索引作为唯一索引的时候,必须复合索引中所有的列都用到,才能是const。

eq_ref

唯一性索引,对于每个索引键的查询,返回匹配唯一行数据(有且仅有1个,不能多个,不能0个),常见于唯一索引和主键索引。

mysql> explain select e.e_id from employee e, salary s where e.e_id = s.s_id;
+----+-------------+-------+------------+-------+----------------+--------+---------+---------------+------+----------+----------
---+
| id | select_type | table | partitions | type  | possible_keys  | key    | key_len | ref           | rows | filtered | extra
   |
+----+-------------+-------+------------+-------+----------------+--------+---------+---------------+------+----------+----------
---+
|  1 | simple      | e     | null       | index | primary,e_idx1 | e_idx1 | 4       | null          |    5 |   100.00 | using ind
ex |
|  1 | simple      | s     | null       | ref   | s_idx1         | s_idx1 | 5       | testdb.e.e_id |    1 |   100.00 | using ind
ex |
+----+-------------+-------+------------+-------+----------------+--------+---------+---------------+------+----------+----------
---+

疑问:为啥出来的不是eq_ref?

ref

ref通常针对普通索引,通过索引查询出多条数据或0条数据。

mysql> explain select * from employee where e_name = 'zhangsan';
+----+-------------+----------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | extra       |
+----+-------------+----------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
|  1 | simple      | employee | null       | ref  | e_idx2,e_idx3 | e_idx2 | 63      | const |    1 |   100.00 | using index |
+----+-------------+----------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

以上是查询有结果的情况,接下来看查询结果为0条的情况:

mysql> explain select * from employee where e_name = 'none';
+----+-------------+----------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | extra       |
+----+-------------+----------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
|  1 | simple      | employee | null       | ref  | e_idx2,e_idx3 | e_idx2 | 63      | const |    1 |   100.00 | using index |
+----+-------------+----------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

range

根据索引查询的条件为一个范围,如>,<,between ... and, like等。
我们仍然看以下几个示例:

/*情形一:使用大于的情况*/
mysql> explain select * from employee where e_id > 1;
+----+-------------+----------+------------+-------+----------------+---------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys  | key     | key_len | ref  | rows | filtered | extra       |
+----+-------------+----------+------------+-------+----------------+---------+---------+------+------+----------+-------------+
|  1 | simple      | employee | null       | range | primary,e_idx1 | primary | 4       | null |    4 |   100.00 | using where |
+----+-------------+----------+------------+-------+----------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

/*情形二: 使用between ... and*/
mysql> explain select * from employee where e_id  between 1 and 5;
+----+-------------+----------+------------+-------+----------------+---------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys  | key     | key_len | ref  | rows | filtered | extra       |
+----+-------------+----------+------------+-------+----------------+---------+---------+------+------+----------+-------------+
|  1 | simple      | employee | null       | range | primary,e_idx1 | primary | 4       | null |    5 |   100.00 | using where |
+----+-------------+----------+------------+-------+----------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

/*情形三: 使用like*/
mysql> explain select * from employee where e_name like 'zh%';
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | extra
         |
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
|  1 | simple      | employee | null       | range | e_idx2,e_idx3 | e_idx2 | 63      | null |    1 |   100.00 | using where; using index |
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.02 sec)

需要注意的是,不等于号<>(或 !=),in 语法在实际测试中使用到的是index级别的索引,而非range,说明<> 和in实际上使索引级别下降了,因此,在上一篇文章中,在索引注意事项中,才会有尽量避免使用in和not in的说明。
同样,like 的百分号%最好跟在后面,而不是前面,也是一样的道理,在实际测试中,当前面有%时,索引级别也会降为index。

/*不等号<>测试*/
mysql> explain select * from employee where e_id <> 3;
+----+-------------+----------+------------+-------+----------------+--------+---------+------+------+----------+----------------
----------+
| id | select_type | table    | partitions | type  | possible_keys  | key    | key_len | ref  | rows | filtered | extra
          |
+----+-------------+----------+------------+-------+----------------+--------+---------+------+------+----------+----------------
----------+
|  1 | simple      | employee | null       | index | primary,e_idx1 | e_idx2 | 68      | null |    5 |    80.00 | using where; us
ing index |
+----+-------------+----------+------------+-------+----------------+--------+---------+------+------+----------+----------------
----------+
1 row in set, 1 warning (0.00 sec)

/*in 测试*/
mysql> explain select * from employee where e_id  in (1,2,3);
+----+-------------+----------+------------+-------+----------------+--------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys  | key    | key_len | ref  | rows | filtered | extra
          |
+----+-------------+----------+------------+-------+----------------+--------+---------+------+------+----------+--------------------------+
|  1 | simple      | employee | null       | index | primary,e_idx1 | e_idx2 | 68      | null |    5 |    60.00 | using where; using index |
+----+-------------+----------+------------+-------+----------------+--------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

/* like 百分号测试 */
mysql> explain select * from employee where e_name like '%san%';
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | extra
         |
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
|  1 | simple      | employee | null       | index | null          | e_idx2 | 68      | null |    5 |    20.00 | using where; using index |
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

index

index指的是索引扫描树,只要走到了索引,基本上都是这一级别,该级别仅仅比all高一点。
如下面这种情况:

mysql> explain select * from employee where d_id = 3;
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------
---------+
| id | select_type | table    | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | extra
         |
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------
---------+
|  1 | simple      | employee | null       | index | null          | e_idx2 | 68      | null |    5 |    20.00 | using where; usi
ng index |
+----+-------------+----------+------------+-------+---------------+--------+---------+------+------+----------+-----------------
---------+
1 row in set, 1 warning (0.00 sec)

all

all就是全表扫描,这是最差的一种情况,等于没有任何优化,一般当所查询的字段没有索引时,使用到的就是该级别。
如:

mysql> explain select * from salary;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | simple      | salary | null       | all  | null          | null | null    | null |    5 |   100.00 | null  |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

possible_keys 和 key

possible key和key可以放在一起来讲。顾名思义,possible key就是可能用到的索引,而key则是实际用到的索引。这二者并不一定是相同的。举一个例子:

mysql> explain select * from employee where e_id = 1 and e_name = 'zhang';
+----+-------------+----------+------------+-------+------------------------------+---------+---------+-------+------+----------+
-------+
| id | select_type | table    | partitions | type  | possible_keys                | key     | key_len | ref   | rows | filtered |
 extra |
+----+-------------+----------+------------+-------+------------------------------+---------+---------+-------+------+----------+
-------+
|  1 | simple      | employee | null       | const | primary,e_idx1,e_idx2,e_idx3 | primary | 4       | const |    1 |   100.00 |
 null  |
+----+-------------+----------+------------+-------+------------------------------+---------+---------+-------+------+----------+
-------+

可以看到,它列举出的可能走到的索引,包括primary,e_idx1,e_idx2,e_idx3,而实际上,只使用到了primary。
为什么会这样呢?我们先来看一下employee表的索引:

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 |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-----------
-+---------+---------------+
| employee |          0 | primary  |            1 | e_id        | a         |           5 |     null | null   |      | btree
 |         |               |
| employee |          0 | e_idx1   |            1 | e_id        | a         |           5 |     null | null   |      | btree
 |         |               |
| employee |          1 | e_idx2   |            1 | e_name      | a         |           4 |     null | null   | yes  | btree
 |         |               |
| employee |          1 | e_idx2   |            2 | d_id        | a         |           5 |     null | null   | yes  | btree
 |         |               |
| employee |          1 | e_idx3   |            1 | e_name      | a         |           4 |     null | null   | yes  | btree
 |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-----------
-+---------+---------------+
5 rows in set (0.00 sec)

可以看到,where条件中,e_id字段涉及到了primary和e_idx1两个索引,e_name涉及到了e_idx2和e_idx3两个索引,所以,由于这两个字段出现在了where条件中,理论上这四个索引都会出现。而事实上,因为根据primary索引查e_id就直接能查出结果,所以后面的索引自然就用不上了。

key_len

key_len代表的是索引字段的长度,其计算方法是:
key_len = 索引字段实际长度 + (可以为null)1 + (varchar)2
仍然以employee表为例加以说明。先看一下employee表的表结构:

mysql> desc employee;
+--------+-------------+------+-----+---------+----------------+
| field  | type        | null | key | default | extra          |
+--------+-------------+------+-----+---------+----------------+
| e_id   | int(4)      | no   | pri | null    | auto_increment |
| e_name | varchar(20) | yes  | mul | null    |                |
| d_id   | int(4)      | yes  |     | null    |                |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

可以看出,e_id要求是非null的,而e_name和d_id都可以是null。
因此,我们查询以下sql语句的执行计划:

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 |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+-----------
mysql> explain select * from employee where e_id = 1;
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys  | key     | key_len | ref   | rows | filtered | extra |
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
|  1 | simple      | employee | null       | const | primary,e_idx1 | primary | 4       | const |    1 |   100.00 | null  |
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

/*
* 该条sql实际用到的是primary索引,也就是e_id,该字段长度为int(4),要求not null,所以key_len = 4.
*/

mysql> explain select * from employee where e_name = 'zhang';
+----+-------------+----------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | extra       |
+----+-------------+----------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
|  1 | simple      | employee | null       | ref  | e_idx2,e_idx3 | e_idx2 | 63      | const |    1 |   100.00 | using index |
+----+-------------+----------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

/*
*该sql实际使用到的索引为e_idx2,该索引的字段是e_name,由于该字段数据类型为varchar,且可以为空,所以key_len = 20*3(utf8字符长度) + 2(varchar) + 1(可以为null) = 63。

注意:字符长度关系为:
	utf8每个字符3字节
	gbk每个字符2字节
	latin1每个字符1字节
*/

接下来看一个索引字段数据类型为char的例子:

mysql> show index from department;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+---------
---+---------+---------------+
| table      | non_unique | key_name | seq_in_index | column_name | collation | cardinality | sub_part | packed | null | index_ty
pe | comment | index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+---------
---+---------+---------------+
| department |          0 | d_idx1   |            1 | d_id        | a         |           3 |     null | null   | yes  | btree
   |         |               |
| department |          1 | d_idx2   |            1 | d_name      | a         |           3 |     null | null   |      | btree
   |         |               |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+---------
---+---------+---------------+
2 rows in set (0.00 sec)

mysql> desc department;
+--------+----------+------+-----+---------+-------+
| field  | type     | null | key | default | extra |
+--------+----------+------+-----+---------+-------+
| d_id   | int(4)   | yes  | uni | null    |       |
| d_name | char(10) | no   | mul | null    |       |
+--------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

查询sql如下:

mysql> explain select * from department where d_name = 'hr';
+----+-------------+------------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | extra |
+----+-------------+------------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|  1 | simple      | department | null       | ref  | d_idx2        | d_idx2 | 30      | const |    1 |   100.00 | null  |
+----+-------------+------------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

由于d_name字段要求not null,非变长,所以只需要计算字符长度即可,即:key_len = 20*3 = 60.

观察key_len,通常可以用于判断表走到了哪个索引,尤其对于复合索引,可以非常直观的看出其是否走了复合索引的全字段。
为了说明该问题,我们重新建一张表test01:

mysql> create table test01(
    -> id int(4),
    -> name varchar(20),
    -> passwd char(20),
    -> inf char(50));
query ok, 0 rows affected (0.19 sec)
--创建复合索引
mysql> create index t_idx1 on test01(id, name, passwd);
query ok, 0 rows affected (0.16 sec)
records: 0  duplicates: 0  warnings: 0
--插入1条数据
mysql> insert into test01 values(1,'zz', '123456', 'asdfgh');
query ok, 1 row affected (0.04 sec)

通过观察,我们知道,如果走到该索引的所有字段,该索引长度应为: (4 + 1) + (20 * 3 + 2 + 1) + (20 * 3 + 1) = 129。
我们先来看两个正常走到全索引的例子:

mysql> explain select * from test01 where id = 1 and name = 'zz' and passwd = '123';
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref               | rows | filtered | extra
|
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------+
|  1 | simple      | test01 | null       | ref  | t_idx1        | t_idx1 | 129     | const,const,const |    1 |   100.00 | null
|
+----+-------------+--------+------------+------+---------------+--------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select passwd from test01 where name = 'zz' and passwd = '123';
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
-------+
| id | select_type | table  | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | extra
       |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
-------+
|  1 | simple      | test01 | null       | index | null          | t_idx1 | 129     | null |    1 |   100.00 | using where; using
 index |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select passwd from test01 where passwd = '123';
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
-------+
| id | select_type | table  | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | extra
       |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
-------+
|  1 | simple      | test01 | null       | index | null          | t_idx1 | 129     | null |    1 |   100.00 | using where; using
 index |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-------------------
-------+
1 row in set, 1 warning (0.00 sec)

以上三条sql,无论是id = 1 and name = 'zz' and passwd = '123', 还是name = 'zz' and passwd = '123',或者passwd = '123',实际在查询中,都要按顺序将三个字段全部查到,因此都是129。
但是如果把sql改成如下写法:

mysql> explain select passwd from test01 where id = 1 and name = 'zz';
+----+-------------+--------+------------+------+---------------+--------+---------+-------------+------+----------+-------------
+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref         | rows | filtered | extra
|
+----+-------------+--------+------------+------+---------------+--------+---------+-------------+------+----------+-------------
+
|  1 | simple      | test01 | null       | ref  | t_idx1        | t_idx1 | 68      | const,const |    1 |   100.00 | using index
|
+----+-------------+--------+------------+------+---------------+--------+---------+-------------+------+----------+-------------
+
1 row in set, 1 warning (0.00 sec)

发现虽然type的级别仍然是ref,走到的索引也仍然是t_idx1,但是key_len 却只有68,也就是id和name的长度,passwd字段虽然也在索引里,但是由于不在条件里,因此就没有走到。
同理,下面的sql也是一样的道理,因为只用到了id,所以key_len只有5.

mysql> explain select passwd from test01 where id = 1;
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | extra       |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
|  1 | simple      | test01 | null       | ref  | t_idx1        | t_idx1 | 5       | const |    1 |   100.00 | using index |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

但是我们需要注意的是下面这种情况:

mysql> explain select passwd from test01 where id = 1 and passwd = '123';
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
-------+
| id | select_type | table  | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | extra
       |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
-------+
|  1 | simple      | test01 | null       | ref  | t_idx1        | t_idx1 | 5       | const |    1 |   100.00 | using where; using
 index |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------------------
-------+

我们在where条件里带了id和passwd,但并不如我们想象中的key_len = 66,而是等于5,也就是说,它实际只用到了id字段,而并没有用到passwd。
造成这种情况的原因在于,复合索引是严格按照复合索引中字段的先后顺序执行的,因此要求我们写sql的时候,也要按照复合索引的顺序去书写(参见上一篇文章sql优化初探-索引

ref

注意此处的ref和前面type里出现的ref并不是同一个意思。这里的ref代表的是索引关联了哪个字段。
常用取值有:

id ref 说明
1 null 没有用到任何字段
2 const 某个具体的值
3 具体某张表的字段值 一般用于关联语句中

下面仍然以例子来说明:

-- 具体的数值:const
mysql> explain select * from employee where e_id = 1;
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys  | key     | key_len | ref   | rows | filtered | extra |
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
|  1 | simple      | employee | null       | const | primary,e_idx1 | primary | 4       | const |    1 |   100.00 | null  |
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

--不等于任何值
mysql> explain select * from employee where e_id < 5;
+----+-------------+----------+------------+-------+----------------+---------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys  | key     | key_len | ref  | rows | filtered | extra       |
+----+-------------+----------+------------+-------+----------------+---------+---------+------+------+----------+-------------+
|  1 | simple      | employee | null       | range | primary,e_idx1 | primary | 4       | null |    4 |   100.00 | using where |
+----+-------------+----------+------------+-------+----------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

--某个具体字段
mysql> explain select * from employee where e_id in (select s_id from salary);
+----+-------------+----------+------------+-------+----------------+--------+---------+----------------------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys  | key    | key_len | ref                  | rows | filtered | extra       |
+----+-------------+----------+------------+-------+----------------+--------+---------+----------------------+------+----------+-------------+
|  1 | simple      | employee | null       | index | primary,e_idx1 | e_idx2 | 68      | null                 |    5 |   100.00 | using index |
|  1 | simple      | salary   | null       | ref   | s_idx1         | s_idx1 | 5       | testdb.employee.e_id |    1 |   100.00 | using index |
+----+-------------+----------+------------+-------+----------------+--------+---------+----------------------+------+----------+-------------+
2 rows in set, 1 warning (0.02 sec)

rows

通过索引返回的数据条数。

filtered

返回结果的行数占读取行数的百分比,该数值越大越好。
如:

mysql> explain select * from employee where e_id = 1;
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys  | key     | key_len | ref   | rows | filtered | extra |
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
|  1 | simple      | employee | null       | const | primary,e_idx1 | primary | 4       | const |    1 |   100.00 | null  |
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from employee where e_id = 1;
+------+--------+------+
| e_id | e_name | d_id |
+------+--------+------+
|    1 | zhang  |    1 |
+------+--------+------+
1 row in set (0.00 sec)

查询结果为1条,而rows也为1条,因此filtered = 1/1 = 100%.
再看下面这个例子:

mysql> explain select * from employee where e_id < 3;
+----+-------------+----------+------------+-------+----------------+--------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys  | key    | key_len | ref  | rows | filtered | extra
          |
+----+-------------+----------+------------+-------+----------------+--------+---------+------+------+----------+--------------------------+
|  1 | simple      | employee | null       | index | primary,e_idx1 | e_idx2 | 68      | null |    5 |    40.00 | using where; using index |
+----+-------------+----------+------------+-------+----------------+--------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from employee where e_id < 3;
+------+--------+------+
| e_id | e_name | d_id |
+------+--------+------+
|    2 | wang   |    1 |
|    1 | zhang  |    1 |
+------+--------+------+
2 rows in set (0.00 sec)

实际查询结果为2条,rows = 5条,因此filtered = 2/5 = 40%。

extra

extra是额外信息的意思。常见的值如下:

id extra 说明 常见场景
1 use filesort mysql会对数据使用非索引进行排序 通常见于order by
2 use temporary 使用临时中间表保存数据 通常见于group by
3 use index select语句中使用了索引覆盖,避免回表访问 常见于select的字段只有索引字段
4 use where 需要回表查询 常见于where子句

以上四种情形,use filesort 和 use temporary 是比较糟糕的情况,一般出现这两种,意味着sql需要优化;
而如果出现use index,则说明sql性能比较好,通常意味着效率比较高。
下面仍然以例子来说明:

mysql> explain select e_id from employee where e_id < 3 order by d_id;
+----+-------------+----------+------------+-------+----------------+--------+---------+------+------+----------+----------------
--------------------------+
| id | select_type | table    | partitions | type  | possible_keys  | key    | key_len | ref  | rows | filtered | extra
                          |
+----+-------------+----------+------------+-------+----------------+--------+---------+------+------+----------+----------------
--------------------------+
|  1 | simple      | employee | null       | index | primary,e_idx1 | e_idx2 | 68      | null |    5 |    40.00 | using where; us
ing index; using filesort |
+----+-------------+----------+------------+-------+----------------+--------+---------+------+------+----------+----------------
--------------------------+
1 row in set, 1 warning (0.00 sec)

以上sql中出现了using filesort,探究其原因,是因为查询的where条件是e_id,而order by的字段却是d_id。

在上一篇文章中提到了sql的解析过程为:

from ... on ... join ... where ... group by ... having ... select [distinct] ... order by ... limit ...;

这就意味着,在根据e_id查询出e_id后,还需要根据d_id进行排序,而d_id是未知的,这也就意味着有另外一次额外的查询。

再来看第二个例子:

mysql> explain select d_id from employee where e_id < 3 group by d_id;
+----+-------------+----------+------------+-------+-----------------------+--------+---------+------+------+----------+-----------------------------------------------------------+
| id | select_type | table    | partitions | type  | possible_keys         | key    | key_len | ref  | rows | filtered | extra
                                                  |
+----+-------------+----------+------------+-------+-----------------------+--------+---------+------+------+----------+-----------------------------------------------------------+
|  1 | simple      | employee | null       | index | primary,e_idx1,e_idx2 | e_idx2 | 68      | null |    5 |    40.00 | using where; using index; using temporary; using filesort |
+----+-------------+----------+------------+-------+-----------------------+--------+---------+------+------+----------+-----------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)

上句出现了using temporary,原因就是因为查询时使用的索引是e_id,但group by分组时,使用的却是d_id,因此,需要额外的临时空间来进行分组操作,所以就出现了using temporary。
如果把上面语句改一下:

mysql> explain select d_id from employee where e_id < 3 group by e_id;
+----+-------------+----------+------------+-------+------------------------------+---------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys                | key     | key_len | ref  | rows | filtered |
extra       |
+----+-------------+----------+------------+-------+------------------------------+---------+---------+------+------+----------+-------------+
|  1 | simple      | employee | null       | index | primary,e_idx1,e_idx2,e_idx3 | primary | 4       | null |    5 |    40.00 |
using where |
+----+-------------+----------+------------+-------+------------------------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

此时出现的是using where,而没有了之前的using temporary。正是因为不再使用额外空间了的缘故。

最后来看这样一个例子:

mysql> explain select e_id from employee where e_id = 3;
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys  | key     | key_len | ref   | rows | filtered | extra       |
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------------+
|  1 | simple      | employee | null       | const | primary,e_idx1 | primary | 4       | const |    1 |   100.00 | using index |
+----+-------------+----------+------------+-------+----------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

此时出现的是using index,说明在索引树里就能查询到所需要的结果,不需要回表查询,效率当然会很高了。

小结

关于执行计划,由于mysql版本的不同,展示的字段也有所不同,比如mysql5.5就没有partitions和filtered字段的展示。对于某些字段的含义也不尽相同。如mysql5.5中,根据唯一索引查询到的记录为0条,type值为ref,但是在mysql5.7中,type为eq_ref。这些细微的区别其实并不影响对执行计划的解读,只需要在使用的过程中稍加注意就行了。于实际sql的优化并没有太大的影响。
总之,执行计划只是一个分析性能的工具,掌握该工具并不在于死记硬背,而在于探索和实践。