在企业的应用场景中，为了优化SQL语句的执行，加快SQL语句的执行效率，通常是先查看SQL语句的具体执行过程，分析当前SQL的执行计划。在MySQL里，其为我们提供了EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划；官网地址为：MySQL 8.0 Reference Manual，本篇文章会详细讲解EXPLAIN各输出项代表的是什么意思，从而可以有针对性的提升我们查询语句的性能。
如果我们想看看某个查询的执行计划的话，可以在具体的查询语句前边加一个EXPLAIN，就像这样：
mysql> explain select * from person;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | person | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
上面输出的，就是我们经常看到的执行计划，在本篇下面，我们会对每列的具体作用都做详细解释；其实除了以SELECT开头的查询语句，其余的DELETE、INSERT、REPLACE以及UPDATE语句前面都可以加上EXPLAIN来输出执行计划，不过，我们经常遇到的问题是如何优化SQL查询，所以，本篇文章，基本上都是以SELECT语句来描述EXPLAIN语句的用法。
1. Explain各列详解
下面，先把EXPLAIN语句输出的各个列的作用先大致罗列一下，大家先具体有个感官上的认识，大致有个印象：
| 列名 | 描述 |
|---|---|
id | select查询的序列号，包含一组数字，表示查询中执行select子句或者操作表的顺序,一个大的查询语句中每个SELECT关键字都对应一个唯一的id |
select_type | SELECT关键字对应的那个查询的类型,用来分辨查询的类型，是普通查询还是联合查询还是子查询 |
table | 表名，也可能是临时表或者union合并结果集 |
partitions | 分区表信息 |
type | 单表查询方式 |
possible_keys | 可能用到的索引 |
key | 实际使用到的索引 |
key_len | 实际使用到的索引长度 |
ref | 当使用索引列等值查询时，与索引列进行等值匹配的对象信息，一般是个常数 |
rows | 预估的需要读取的记录条数 |
filtered | 某个表经过搜索条件过滤后剩余记录条数的百分比 |
Extra | 额外的信息 |
1.1 id
不管我们写的查询语句有多复杂，EXPLAIN每遇到一个SELECT关键字，就会为这条SELECT语句分配一个id，输出的id值，不同行的值可能相同，也可能不同，如果是操作同一张表，则id相同，如果操作不同的表，则id不同
mysql> explain select * from person p1 inner join person p2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+
| 1 | SIMPLE | p1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | p2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------------+
mysql> explain select * from person where country_name in(select name from country where id =1) or country_name='新罗帝国';
+----+--------------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | PRIMARY | person | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | country | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+--------------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
可以看到，如果id相同，那么执行顺序从上到下，如果id不同，id值越大优先级越高，越先被执行，如上面第二条SQL，如果是子查询，子查询有一个独立的SELECT关键字，操作的是country表，子查询先执行，所以对应的SELECT语句id值是2。
熟悉MySQL系统架构的都知道，一条sql在经过分析器之后，会经过查询优化器优化之后再会交由执行器执行；所以，这里需要特别注意的是，查询优化器可能会对涉及子查询的查询语句进行优化重写，将子查询转为连接查询。因此，如果想知道查询优化器是否对包含子查询的语句进行了重写，直接查看执行计划就可，如下：
mysql> explain select * from person where country_name in(select name from country where id =1);
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | country | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | person | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
可以看到，上面的查询语句虽然是一个子查询，但是执行计划中country和person表对应的记录的id值全部是1，这就表明了查询优化器将子查询转换为了连接查询
1.2 select_type
select_type属性主要是区分查询类型的，是普通查询还是联合查询还是子查询:
值（为了避免中文翻译带来误解，先直接使用文档中的英文做简要描述）：
| 名称 | 描述 |
|---|---|
SIMPLE | Simple SELECT (not using UNION or subqueries) |
PRIMARY | Outermost SELECT |
UNION | Second or later SELECT statement in a UNION |
UNION RESULT | Result of a UNION |
SUBQUERY | First SELECT in subquery |
DEPENDENT SUBQUERY | First SELECT in subquery, dependent on outer query |
DEPENDENT UNION | Second or later SELECT statement in a UNION, dependent on outer query |
DERIVED | Derived table |
MATERIALIZED | Materialized subquery |
UNCACHEABLE SUBQUERY | A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query |
UNCACHEABLE UNION | The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY) |
1.2.1 SIMPLE
简单的查询，查询语句不包含子查询和union，如下：
mysql> explain select * from person where grade > 97;
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | person | NULL | ALL | ix_grade | NULL | NULL | NULL | 309733 | 19.41 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
不过，需要注意的是，JOIN也是SIMPLE类型:
mysql> explain select * from person p left join country c on p.country_name= c.name;
+----+-------------+-------+------------+------+---------------+---------+---------+---------------------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+---------------------+--------+----------+-------+
| 1 | SIMPLE | p | NULL | ALL | NULL | NULL | NULL | NULL | 309733 | 100.00 | NULL |
| 1 | SIMPLE | c | NULL | ref | uk_name | uk_name | 403 | test.p.country_name | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+---------+---------+---------------------+--------+----------+-------+
1.2.2 PRIMARY
如果查询中若包含任何复杂查询，如子查询，UNION、UNION ALL，最外层查询(最左边)则被标记Primary
mysql> explain select id,country_name from person union select id,name from country;
+----+--------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-----------------+
| 1 | PRIMARY | person | NULL | ALL | NULL | NULL | NULL | NULL | 309733 | 100.00 | NULL |
| 2 | UNION | country | NULL | index | NULL | uk_name | 403 | NULL | 1046520 | 100.00 | Using index |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+---------+----------+-----------------+
1.2.3 UNION
对于包含UNION或者UNION ALL的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询的select_type值就是UNION，可以对比上一个例子的效果
1.2.4 UNION RESULT
从UNION表获取结果的select，UNION表一般是借助于临时表，例子可参考1.2.2章节示例
1.2.5 SUBQUERY
在select或者where列表中包含子查询。需要注意的是，包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是不相关子查询
mysql> explain select * from country where name in (select country_name from person where grade >90) or area > 100;
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | PRIMARY | country | NULL | ALL | NULL | NULL | NULL | NULL | 1046520 | 100.00 | Using where |
| 2 | SUBQUERY | person | NULL | ALL | ix_grade | NULL | NULL | NULL | 309733 | 50.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+
外层查询的select_type就是PRIMARY，子查询的select_type就是SUBQUERY;
1.2.6 DEPENDENT UNION
DEPENDENT UNION也是一种特殊的子查询，与SUBQUERY不同的是，SUBQUERY只会执行一次，而DEPENDENT UNION因为受到外部查询表的影响，其对应的查询语句会被执行多次，如下所：
mysql> explain select * from country where name in (select country_name from person where person.id = country.id) or area >100;
+----+--------------------+---------+------------+--------+---------------+---------+---------+-----------------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+---------+------------+--------+---------------+---------+---------+-----------------+---------+----------+-------------+
| 1 | PRIMARY | country | NULL | ALL | NULL | NULL | NULL | NULL | 1046520 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | person | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.country.id | 1 | 10.00 | Using where |
+----+--------------------+---------+------------+--------+---------------+---------+---------+-----------------+---------+----------+-------------+
1.2.7 DEPENDENT UNION
跟UNION类似，此处的DEPENDENT表示UNION或UNION联合而成的结果会受外部表影响，既在包含UNION或者UNION ALL的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的select_type的值就是DEPENDENT UNION。说的有些绕哈
mysql> explain select * from person where name in (select name from person where grade=97 union select name from person where country_name='新罗帝国');
+----+--------------------+------------+------------+------+---------------+----------+---------+-------+--------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+------------+------------+------+---------------+----------+---------+-------+--------+----------+-----------------+
| 1 | PRIMARY | person | NULL | ALL | NULL | NULL | NULL | NULL | 309733 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | person | NULL | ref | ix_grade | ix_grade | 5 | const | 154866 | 10.00 | Using where |
| 3 | DEPENDENT UNION | person | NULL | ALL | NULL | NULL | NULL | NULL | 309733 | 1.00 | Using where |
| NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------------+------------+------------+------+---------------+----------+---------+-------+--------+----------+-----------------+
查询表复杂，大查询里包含了一个子查询，子查询里又是由UNION连起来的两个小查询，
1.3 table
对应行正在访问哪一个表，表名或者别名，可能是临时表或者union合并结果集:
- 如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别名
- 表名是derivedN的形式，表示使用了id为N的查询产生的衍生表
- 当有
unionresult的时候，表名是union n1,n2等的形式，n1,n2表示参与union的id
1.4 partitions
分区，实际场景使用的比较少，一般情况下，我们的查询语句对应的执行计划的partitions列的值都是NULL
1.5 type
type显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据，最容易想的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下，访问的类型有很多，效率从最好到最坏依次是：system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL；下面我们再详细介绍下面每一种类型:
1.5.1 system
当表中只有一条记录的时候而且存储引擎统计的数据是准确的，比如MyISAM、Memory，那么对该表的访问方法就是system；systemconst类型是const类型的特例，平时不会出现：
mysql> CREATE TABLE type_test_myisam (name VARCHAR(100) ) Engine = MyISAM;
mysql> insert type_test_myisam value('戴沫白');
mysql> explain select * from type_test_myisam;
+----+-------------+------------------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------+------------+--------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | type_test_myisam | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+------------------+------------+--------+---------------+------+---------+------+------+----------+-------+
可以看到，上面的Explain语句输出的type为system；其实当你把上面的建表语句改成使用InnoDB存储引擎，执行计划输出的type就不再是system而是ALL。
1.5.2 const
如果我们根据主键或者唯一二级索引列与常数进行等值匹配时，Explain语句输出的type就为const；如下：根据主键Id或者唯一索引，Explain语句输出的type都是为const。
mysql> explain select * from country where id =1;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | country | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
mysql> explain select * from country where name='新罗帝国';
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | country | NULL | const | uk_name | uk_name | 403 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------
1.5.3 eq_ref
eq_ref这种类型，常出现在连接查询的时候，当被驱动表是通过主键或者唯一索引列进行等值匹配的时候，被驱动表的访问方式就为eq_ref:
mysql> explain select p.* from person p left join country c on p.country_name = c.name;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
| 1 | SIMPLE | p | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | c | NULL | eq_ref | uk_name | uk_name | 403 | test.p.country_name | 1 | 100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
1.5.4 ref
使用了非唯一性索引进行等值匹配来访问某个表时，Explain语句输出的type都是为ref。
mysql> drop index uk_name on country;
mysql> create index uk_name on country (name);
mysql> explain select * from country where name='星罗帝国';
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | country | NULL | ref | uk_name | uk_name | 403 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1.5.5 fulltext
与全文索引有关，一般全文索引也不会用到MySQL，就不再举例说明。
1.5.6 ref_or_null
ref_or_null与ref区别不大，都是对普通索引进行等值匹配查询的时候，Explain语句输出的type值；如果该索引列的值也可以是NULL值时，那么对该表的访问方法就可能是ref_or_null，如下：
mysql> explain select * from country where name='新罗帝国' or name is null;
+----+-------------+---------+------------+-------------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------------+---------------+---------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | country | NULL | ref_or_null | uk_name | uk_name | 403 | const | 2 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------------+---------------+---------+---------+-------+------+----------+-----------------------+
1.5.7 index_merge
index_merge 从字面意思看就是索引合并；当然，这是MySQL为了解决单表访问，只能使用到一个索引所提出来的一种优化，例如下面的SQL:
explain select * from person where grade >90 or age > 30;
假设grade和age分别创建了索引，很多人第一印象可能是ix_age和ix_grade都能发挥作用，其实不是的，因为ix_age索引只存储了列age和id的值，无法判断grade>90条件是否成立，所以只能拿着id去回表查询；同样ix_grade索引只存储了列grade和id的值，无法判断age>30条件是否成立，也只能拿着id去回表查询。 可以看到，最大的开销其实是回表操作，为了减少回表操作，MySQL采用预估的方式，并分别计算使用这两个索引进行查询各自的成本是多少，最终选择执行成本更低的索引方案。至于index_merge 三种类型，后面如果有感兴趣的同学，可点赞加关注，后面博主会续上此内容，对index_merge做详细分析。
1.5.8 unique_subquery
unique_subquery是针对在一些包含IN子查询的查询语句中，子查询用唯一索引进行等值匹配时，那么该子查询执行计划的type列的值就是unique_subquery，比如下边的这个查询语句：
mysql> drop index uk_name on country;
mysql> create unique index uk_name on country (name);
mysql> explain select * from person where country_name in(select distinct name from country ) or id >3;
+----+--------------------+---------+------------+-----------------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+---------+------------+-----------------+---------------+---------+---------+------+------+----------+-------------+
| 1 | PRIMARY | person | NULL | ALL | PRIMARY | NULL | NULL | NULL | 3 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | country | NULL | unique_subquery | uk_name | uk_name | 403 | func | 1 | 100.00 | Using index |
+----+--------------------+---------+------------+-----------------+---------------+---------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
1.5.9 index_subquery
index_subquery与unique_subquery类似，只不过访问子查询中的表时使用的是普通的索引，如下，我们先将之前的唯一索引删掉，建立普通索引，然后执行相同的sql语句：
mysql> drop index uk_name on country;
mysql> create index uk_name on country (name);
mysql> explain select * from person where country_name in(select distinct name from country ) or id >3;
+----+--------------------+---------+------------+----------------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+---------+------------+----------------+---------------+---------+---------+------+------+----------+-------------+
| 1 | PRIMARY | person | NULL | ALL | PRIMARY | NULL | NULL | NULL | 3 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | country | NULL | index_subquery | uk_name | uk_name | 403 | func | 1 | 100.00 | Using index |
+----+--------------------+---------+------------+----------------+---------------+---------+---------+------+------+----------+-------------+
1.5.10 range
索引范围查询，表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描，适用的操作符： =, <>, >, >=, <, <=, IS NULL, BETWEEN,IN()；比如下边的这个查询：
mysql> explain select * from person where age > 10;
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | person | NULL | range | ix_age | ix_age | 5 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)
mysql> explain select * from country where name like '1%';
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | country | NULL | range | uk_name | uk_name | 403 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
1.5.11 index
当我们可以使用索引覆盖(既查找的数据就是索引结构存储的数据，不需要回表查询)，但需要扫描全部的索引记录时，该表的访问方法就是index，比如下面这样：
mysql> explain select id ,name from country where name like 's%';
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | country | NULL | index | uk_name | uk_name | 403 | NULL | 1 | 100.00 | Using where; Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1.5.12 all
全表扫描，一般情况下出现这样的sql语句而且数据量比较大的话那么就需要进行优化。比如下面这样，
mysql> explain select * from person where name ='张三';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | person | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
总的来说，Explain输出的type值，除了All之外，其余的都能用到索引；同时，除了index_merge访问外，其余的都最多只能用到一个索引。
1.6 possible_keys
EXPLAIN语句输出的执行计划中，possible_keys对应的值表示可能用到这张表的索引，但并不一定会实际使用到：
mysql> explain select * from person where age >10 and grade >90;
+----+-------------+--------+------------+-------+-----------------+--------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+-----------------+--------+---------+------+------+----------+------------------------------------+
| 1 | SIMPLE | person | NULL | range | ix_age,ix_grade | ix_age | 5 | NULL | 2 | 66.67 | Using index condition; Using where |
+----+-------------+--------+------------+-------+-----------------+--------+---------+------+------+----------+------------------------------------+
上述执行计划的possible_keys列的值是ix_age,ix_grade，表示该查询可能使用到ix_age,ix_grade两个索引。
需要注意的是，possible_keys对应的值不是越多越好，因为MySQL优化器在比较使用哪个索引使用成本更大时，就需要更多的时间，所以如果可以的话，尽量删除用不到的索引。
1.7 key
key列表示实际用到的索引，还是回到上面的那个例子：
mysql> explain select * from person where age >10 and grade >90;
+----+-------------+--------+------------+-------+-----------------+--------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+-----------------+--------+---------+------+------+----------+------------------------------------+
| 1 | SIMPLE | person | NULL | range | ix_age,ix_grade | ix_age | 5 | NULL | 2 | 66.67 | Using index condition; Using where |
+----+-------------+--------+------------+-------+-----------------+--------+---------+------+------+----------+------------------------------------+
上述执行计划的possible_keys列的值是ix_age,ix_grade，表示该查询可能使用到ix_age,ix_grade两个索引。然后key列的值是ix_age，表示经过查询优化器计算使用不同索引的成本后，最后决定使用ix_age来执行查询比较划算。
1.8 key_len
key_len列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度，单位为字节。在不损失精度的情况下长度越短越好
1.9 rows
根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反映的sql找了多少数据，在完成目的的情况下越少越好，比如下面这个SQL语句：
mysql> explain select * from person where grade >10;
+----+-------------+--------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | person | NULL | range | ix_grade | ix_grade | 5 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+--------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
执行计划的rows列的值是2，这意味着查询优化器在经过分析使用索引ix_grade进行查询的成本之后，觉得满足grade > 10这个条件的记录只有2条。
1.10 filtered
filtered列的值主要是针对连接查询的驱动表而言的，其值表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例；对于单表查询而言，这个filtered列的值是没什么意义的。
mysql> explain select * from person p left join country c on p.country_name=c.name where p.country_name != null;
+----+-------------+-------+------------+------+---------------+---------+---------+---------------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+---------------------+--------+----------+-------------+
| 1 | SIMPLE | p | NULL | ALL | NULL | NULL | NULL | NULL | 309733 | 90.00 | Using where |
| 1 | SIMPLE | c | NULL | ref | uk_name | uk_name | 403 | test.p.country_name | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+---------+---------+---------------------+--------+----------+-------------+
从执行计划中可以看出来，person为驱动表，country为被驱动表。到驱动表person表的执行计划的rows列为309733， filtered列为90.00，这意味着驱动表309733的查询的值就是309733 × 90.00% = 27875970。
1.11 Extra
顾名思义，Extra列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句
1.11.1 using filesort
说明mysql无法利用索引进行排序，只能利用排序算法进行排序，会消耗额外的位置。
mysql> explain select * from country order by area;
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+----------------+
| 1 | SIMPLE | country | NULL | ALL | NULL | NULL | NULL | NULL | 1046520 | 100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+----------------+
1.11.2 Using where
当某个搜索条件需要在server层进行判断时，在Extra列中会提示Using where，如下面查询：
mysql> explain select * from person where grade = 97 and name='唐昊';
+----+-------------+--------+------------+------+---------------+----------+---------+-------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+--------+----------+-------------+
| 1 | SIMPLE | person | NULL | ref | ix_grade | ix_grade | 5 | const | 154866 | 10.00 | Using where |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+--------+----------+-------------+
上面的执行计划，实际使用到的索引为二级索引ix_grade，但由于二级索引只存储grade和id值，并不包含name值，也就是说该条件不能作为索引条件下推的条件在存储引擎层进行判断，
存储引擎需要根据二级索引记录执行回表操作后，将完整的用户记录返回给server层之后，在server层再判断这个条件是否成立，所以本例中的Extra列也显示了Using where的提示。
1.11.3 using index
这个表示当前的查询是覆盖索引的，直接从索引中读取数据，而不用访问数据表
mysql> explain select id,grade from person where grade = 97;
+----+-------------+--------+------------+------+---------------+----------+---------+-------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+--------+----------+-------------+
| 1 | SIMPLE | person | NULL | ref | ix_grade | ix_grade | 5 | const | 154866 | 100.00 | Using index |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+--------+----------+-------------+
1.11.4 using index condition
表示在查询语句的执行过程中将要使用索引条件下推这个特性，，在Extra列中将会显示Using index condition，比如这样：
mysql> explain select * from person where age <10;
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | person | NULL | range | ix_age | ix_age | 5 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
注意是索引条件下推而不是索引下推。
1.11.5 using join buffer
在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度
1.11.6 Using temporary
表示查询结果需要借助临时表来完成一些功能，比如去重、排序之类的，如DISTINCT、GROUP BY、UNION等子句的查询过程中，如果不能有效利用索引来完成查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。
mysql> explain select name ,count(*) from person where grade =97 group by name;
+----+-------------+--------+------------+------+---------------+----------+---------+-------+--------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+--------+----------+-----------------+
| 1 | SIMPLE | person | NULL | ref | ix_grade | ix_grade | 5 | const | 154866 | 100.00 | Using temporary |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+--------+----------+-----------------+
因为MySQL提供的Extra有好几十个，涉及到文章篇幅和个人精力问题(一星期中午没睡觉了😴😴😴)，我们就不一个一个介绍了，如果对Extra列的其它值存在疑问或者想要分享的同学，可点赞加留言哦。
