MySQL优化之执行计划
前言
研究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 | select或where中包含的子查询语句 |
子查询 |
| 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的优化并没有太大的影响。
总之，执行计划只是一个分析性能的工具，掌握该工具并不在于死记硬背，而在于探索和实践。
