MySQL Explain执行计划详解_rowsinset-优快云博客

MYSQL 5.6.3以前只能EXPLAIN SELECT; MYSQL5.6.3以后就可以EXPLAIN SELECT,UPDATE,DELETE

EXPLAIN 语法例子：

mysql> explain select customer_id,a.store_id,first_name,last_name, b.manager_staff_id from customer a left join store b on a.store_id=b.store_id;
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref               | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-------+
|  1 | SIMPLE      | a     | ALL    | NULL          | NULL    | NULL    | NULL              |  599 | NULL  |
|  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 1       | sakila.a.store_id |    1 | NULL  |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-------+
2 rows in set

EXPLAIN还有一种语法，类似于desc

mysql> explain actor;
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                 | Null | Key | Default           | Extra                       |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| actor_id    | smallint(5) unsigned | NO   | PRI | NULL              | auto_increment              |
| first_name  | varchar(45)          | NO   |     | NULL              |                             |
| last_name   | varchar(45)          | NO   | MUL | NULL              |                             |
| last_update | timestamp            | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+----------------------+------+-----+-------------------+-----------------------------+

4 rows in set

EXPLAIN的输出

EXPLAIN主要包含以下信息：

Column	JSON Name	Meaning
id	select_id	The SELECT identifier
select_type	None	The SELECT type
table	table_name	The table for the output row
partitions	partitions	The matching partitions
type	access_type	The join type
possible_keys	possible_keys	The possible indexes to choose
key	key	The index actually chosen
key_len	key_length	The length of the chosen key
ref	ref	The columns compared to the index
rows	rows	Estimate of rows to be examined
filtered	filtered	Percentage of rows filtered by table condition
Extra	None	Additional information

id (JSON name: select_id)

SQL查询中的序列号。

select_type (JSON name: none)

查询的类型，可以是下表的任何一种类型：

select_type Value	JSON Name	Meaning
SIMPLE	None	简单查询(不适用union和子查询的)
PRIMARY	None	最外层的查询
UNION	None	UNION中的第二个或者后面的SELECT语句
DEPENDENT UNION	dependent (true)	UNION中的第二个或者后面的SELECT语句,依赖于外部查询
UNION RESULT	union_result	UNION结果
SUBQUERY	None	子查询中的第一个SELECT语句
DEPENDENT SUBQUERY	dependent (true)	子查询中的第一个SELECT语句，依赖于外部查询
DERIVED	None	派生表的SELECT(FROM子句的子查询)
MATERIALIZED	materialized_from_subquery	物化子查询
UNCACHEABLE SUBQUERY	cacheable (false)	对于该结果不能被缓存，必须重新评估外部查询的每一行子查询
UNCACHEABLE UNION	cacheable (false)	UNION中的第二个或者后面的SELECT语句属于不可缓存子查询 (see UNCACHEABLE SUBQUERY)

查询类型例子：

1、SIMPLE 简单查询(不适用union和子查询的)

mysql> explain select * from staff;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | staff | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set

2、PRIMARY 最外层的查询

mysql> explain select * from (select last_name,first_name from customer) a;
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |  599 | NULL  |
|  2 | DERIVED     | customer   | ALL  | NULL          | NULL | NULL    | NULL |  599 | NULL  |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
2 rows in set

3、UNION UNION中的第二个或者后面的SELECT语句

mysql> explain select first_name,last_name from customer a where customer_id=1 union select first_name,last_name from customer b where customer_id=2;
+------+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
| id   | select_type  | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra           |
+------+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
|    1 | PRIMARY      | a          | const | PRIMARY       | PRIMARY | 2       | const |    1 | NULL            |
|    2 | UNION        | b          | const | PRIMARY       | PRIMARY | 2       | const |    1 | NULL            |
| NULL | UNION RESULT | <union1,2> | ALL   | NULL          | NULL    | NULL    | NULL  | NULL | Using temporary |
+------+--------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
3 rows in set

4、DEPENDENT UNION UNION中的第二个或者后面的SELECT语句，依赖于外部查询

mysql> explain select * from customer where customer_id in(select customer_id from customer a where customer_id=1 union all select customer_id from customer b where customer_id=2);
+------+--------------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
| id   | select_type        | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra           |
+------+--------------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
|    1 | PRIMARY            | customer   | ALL   | NULL          | NULL    | NULL    | NULL  |  599 | Using where     |
|    2 | DEPENDENT SUBQUERY | a          | const | PRIMARY       | PRIMARY | 2       | const |    1 | Using index     |
|    3 | DEPENDENT UNION    | b          | const | PRIMARY       | PRIMARY | 2       | const |    1 | Using index     |
| NULL | UNION RESULT       | <union2,3> | ALL   | NULL          | NULL    | NULL    | NULL  | NULL | Using temporary |
+------+--------------------+------------+-------+---------------+---------+---------+-------+------+-----------------+
4 rows in set

5、UNION RESULT UNION结果

mysql> explain select * from staff union select * from staff;
+------+--------------+------------+------+---------------+------+---------+------+------+-----------------+
| id   | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+------+--------------+------------+------+---------------+------+---------+------+------+-----------------+
|    1 | PRIMARY      | staff      | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL            |
|    2 | UNION        | staff      | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL            |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using temporary |
+------+--------------+------------+------+---------------+------+---------+------+------+-----------------+
3 rows in set

6、SUBQUERY 子查询中的第一个SELECT语句

mysql> explain select customer_id from customer where store_id =
(select store_id from store where store_id=1);
+----+-------------+----------+-------+-----------------+-----------------+---------+-------+------+--------------------------+
| id | select_type | table    | type  | possible_keys   | key             | key_len | ref   | rows | Extra                    |
+----+-------------+----------+-------+-----------------+-----------------+---------+-------+------+--------------------------+
|  1 | PRIMARY     | customer | ref   | idx_fk_store_id | idx_fk_store_id | 1       | const |  326 | Using where; Using index |
|  2 | SUBQUERY    | store    | const | PRIMARY         | PRIMARY         | 1       | const |    1 | Using index              |
+----+-------------+----------+-------+-----------------+-----------------+---------+-------+------+--------------------------+
2 rows in set

有兴趣的可以去把=号换成in试试

7、DERIVED 派生表的SELECT(FROM子句的子查询)

mysql> explain select * from (select * from customer) a;
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |  599 | NULL  |
|  2 | DERIVED     | customer   | ALL  | NULL          | NULL | NULL    | NULL |  599 | NULL  |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
2 rows in set

8、其它如物化视图等查询自己去造例子去

table(JSON name: table_name)

显示这一行的数据是关于哪张表的，也可以是下列值之一：
unionM,N: The row refers to the union of the rows with id values of M and N.
derivedN: The row refers to the derived table result for the row with an id value of N. A derived table may result, for example, from a subquery in the FROM clause.
subqueryN: The row refers to the result of a materialized subquery for the row with an id value of N.

partitions (JSON name: partitions)

分区中的记录将被查询相匹配。显示此列仅在使用分区关键字。该值为NULL对于非分区表。

type (JSON name: access_type)

EXPLAIN输出的类型列描述了表的连接方法。下面的列表介绍了连接类型，从最好的类型到最差的命令：

1、system
这是const的一个特例联接类型。表只有一行（=系统表）。

mysql> explain select * from (select * from customer where customer_id=1) a;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL    | NULL    | NULL  |    1 | NULL  |
|  2 | DERIVED     | customer   | const  | PRIMARY       | PRIMARY | 2       | const |    1 | NULL  |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
2 rows in set

2、const

表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const表很快，因为它们只读取一次！
const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。在下面的查询中，tbl_name可以用于const表：

SELECT * from tbl_name WHERE primary_key=1；
SELECT * from tbl_name WHERE primary_key_part1=1和 primary_key_part2=2；

3、eq_ref

对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。
eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。
在下面的例子中，MySQL可以使用eq_ref联接来处理ref_tables：

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

# 相对于下面的ref区别就是它使用的唯一索引，即主键或唯一索引，而ref使用的是非唯一索引或者普通索引。id是主键
mysql> explain select a.*,b.* from testa a,testb b where a.id=b.id
;
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref         | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------------+
|  1 | SIMPLE      | b     | ALL    | NULL          | NULL    | NULL    | NULL        |    1 | Using where |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | sakila.b.id |    1 | NULL        |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+-------------+
2 rows in set

4、ref

对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。
ref可以用于使用=或<=>操作符的带索引的列。
在下面的例子中，MySQL可以使用ref联接来处理ref_tables：

SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

# 使用非唯一性索引或者唯一索引的前缀扫描，返回匹配某个单独值的记录行。name有非唯一性索引
mysql> explain select * from testa where name='aaa';
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | testa | ref  | idx_name      | idx_name | 33      | const |    2 | Using index condition |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------+
1 row in set

mysql> explain select a.*,b.* from testa a,testb b where a.name=b.cname;
+----+-------------+-------+------+---------------+----------+---------+----------------+------+-------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref            | rows | Extra       |
+----+-------------+-------+------+---------------+----------+---------+----------------+------+-------------+
|  1 | SIMPLE      | b     | ALL  | NULL          | NULL     | NULL    | NULL           |    1 | Using where |
|  1 | SIMPLE      | a     | ref  | idx_name      | idx_name | 33      | sakila.b.cname |    1 | NULL        |
+----+-------------+-------+------+---------------+----------+---------+----------------+------+-------------+
2 rows in set

5、 fulltext

使用FULLTEXT索引进行联接。

6、ref_or_null

该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
在下面的例子中，MySQL可以使用ref_or_null联接来处理ref_tables：

SELECT * FROM ref_table
  WHERE key_column=expr OR key_column IS NULL;

mysql> explain select * from (select cusno from testa t1,testb t2 where t1.id=t2.id) t where cusno =2 or cusno is null;
+----+-------------+------------+-------------+---------------+-------------+---------+--------------+------+--------------------------+
| id | select_type | table      | type        | possible_keys | key         | key_len | ref          | rows | Extra                    |
+----+-------------+------------+-------------+---------------+-------------+---------+--------------+------+--------------------------+
|  1 | PRIMARY     | <derived2> | ref_or_null | <auto_key0>   | <auto_key0> | 5       | const        |    2 | Using where; Using index |
|  2 | DERIVED     | t2         | index       | PRIMARY       | PRIMARY     | 4       | NULL         |    1 | Using index              |
|  2 | DERIVED     | t1         | eq_ref      | PRIMARY       | PRIMARY     | 4       | sakila.t2.id |    1 | NULL                     |
+----+-------------+------------+-------------+---------------+-------------+---------+--------------+------+--------------------------+
3 rows in set

此处按照官网的格式未测试出例子来，若有例子的请留言，我测试更新

7、index_merge

该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。

此处按照官网的格式未测试出例子来，若有例子的请留言，我测试更新

8、unique_subquery

unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。
该类型替换了下面形式的IN子查询的ref：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

此处按照官网的格式未测试出例子来，若有例子的请留言，我测试更新

9、index_subquery

该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：

value IN (SELECT key_column FROM single_table WHERE some_expr)

此处按照官网的格式未测试出例子来，若有例子的请留言，我测试更新

10、range

只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。
当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range

SELECT * FROM tbl_name
  WHERE key_column = 10;

SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name
  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

11、index

索引类型与ALL类型一样，除了它是走索引树扫描的，它有两种方式：

如果该覆盖索引能满足查询的所有数据，那仅仅扫描这索引树。在这种情况下，Extra列就会显示用Using index。一般仅仅用索引是扫描的比ALL扫描的要快，因为索引树比表数据小很多。

全表扫描被用到从索引中去读取数据， Extra列就不会显示用Using index。

如果查询仅仅是索引列，那MySQL会这个index索引类型

mysql> alter table testa add primary key p_id(id);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx_name on testa(name);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into testa values(2,2,'aaa');
Query OK, 1 row affected

# 因为查询的列name上建有索引，所以如果这样type走的是index
mysql> explain select name from testa;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | testa | index | NULL          | idx_name | 33      | NULL |    2 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set

# 因为查询的列cusno没有建索引，或者查询的列包含没有索引的列，这样查询就会走ALL扫描，如下：
mysql> explain select cusno from testa;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | testa | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set

# *包含有未见索引的列
mysql> explain select * from testa;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | testa | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set

12、all

对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。

possible_keys (JSON name: possible_keys)

possible_keys列指出MySQL能使用哪个索引在该表中找到行。而下面的key是MYSQL实际用到的索引，这意味着在possible_keys中的是计划中的，而key是实际的，也就是计划中有这个索引，实际执行时未必能用到。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询。

例子参考下面

key (JSON name: key)

key列显示MySQL实际决定使用的键（索引）。

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

例子参考下面

key_len (JSON name: key_length)

key_len列显示MySQL决定使用的键长度。如果KEY键是NULL，则长度为NULL。
使用的索引的长度。在不损失精确性的情况下，长度越短越好.

例子参考下面

ref (JSON name: ref)

ref列显示使用哪个列或常数与key一起从表中选择行。它显示的是列的名字（或单词“const”），MySQL将根据这些列来选择行。

例子参考下面

rows (JSON name: rows)

rows列显示MySQL认为它执行查询时必须检查的行数。

例子参考下面

filtered (JSON name: filtered)

如果你用EXPLAIN EXTENDED将会展示出这列filtered(MySQL5.7缺省就会输出filtered)，它指返回结果的行占需要读到的行(rows列的值)的百分比。按说filtered是个非常有用的值，因为对于join操作，前一个表的结果集大小直接影响了循环的次数。但是我的环境下测试的结果却是，filtered的值一直是100%，也就是说失去了意义。

上面部分EXPLAIN展示的列的例子：

mysql> alter table testa add primary key p_id(id);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx_name on testa(name);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into testa values(2,2,'aaa');
Query OK, 1 row affected

# 下面possible_keys可能会用到的索引有主键和我建的索引，但是key实际用到的是主键，主键长度是4，ref用的列的名字（或单词“const”，此处用的是常量const，速度快，rows扫描的只有1行
mysql> explain select cusno from testa where id=2 and name='aaa';
+----+-------------+-------+-------+------------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys    | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | testa | const | PRIMARY,idx_name | PRIMARY | 4       | const |    1 | NULL  |
+----+-------------+-------+-------+------------------+---------+---------+-------+------+-------+
1 row in set

# 下面虽然name有索引，但是查询的列cusno没有索引，这时mysql计划possible_keys有索引，但实际key未走索引，若果cusno换成有索引的列，参照下面。
mysql> explain select cusno from testa where name='aaa';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | testa | ALL  | idx_name      | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set

# id是有主键索引的，这时实际key已经走索引了，若果查询列换成既有索引的列也有无索引的列，参照下面
mysql> explain select id from testa where name='aaa';
+----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | testa | ref  | idx_name      | idx_name | 33      | const |    2 | Using where; Using index |
+----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+
1 row in set

# 证明只要你查询的列包含有无索引列就不走索引
mysql> explain select cusno,name from testa where name='aaa';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | testa | ALL  | idx_name      | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set

SQL执行计划是经过优化器决策，产生的SQL在数据库内部执行的访问路径计划；
由如下语法得到：

explain select col1,col2 from t1..;
desc select col1,col2 from t1..;

理解输出各个列的含义
这里写图片描述

id:每个select子句的标识id
select_type:select语句的类型
table:当前表名
显示查询将访问的分区，如果你的查询是基于分区表
type：当前表内访问方式
possible_keys:可能使用到的索引
key:经过优化器评估最终使用的索引
key_length:使用到的索引长度
ref:引用到的上一个表的列
rows:rows_examined，要得到最终记录索要扫描经过的记录数
filtered:表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。
Extra:额外的信息说明

接下来主要针对extra字段进行详细解释，EXPLAIN输出的Extra列包含有关MySQL如何解析查询的其他信息。此字段能够给出让我们深入理解执行计划进一步的细节信息，比如是否使用ICP，MRR等。
首先说明下在extra字段进行测试过程中使用到的表和MySQL版本：

CREATE TABLE `test_extra1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_number` int(11) NOT NULL,
  `name` varchar(30) NOT NULL DEFAULT '',
  `age` int(11) DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  `region` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_empnumber` (`emp_number`),
  KEY `idx_region` (`region`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `test_extra2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL DEFAULT '',
  `emp_number` int(11) NOT NULL,
  `salary` decimal(10,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_empnumber` (`emp_number`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.14    |
+-----------+
1 row in set (0.00 sec)

extra字段详细解释说明：

const row not found
For a query such as SELECT … FROM tbl_name, the table was empty.(类似于select …. from tbl_name，而表记录为空)
Deleting all rows
For DELETE, some storage engines (such as MyISAM) support a handler method that removes all table rows in a simple and fast way. This Extra value is displayed if the engine uses this optimization. (对于DELETE，一些存储引擎（如MyISAM）支持一种处理方法，可以简单而快速地删除所有的表行。如果引擎使用此优化，则会显示此额外值)
Distinct
MySQL is looking for distinct values, so it stops searching for more rows for the current row combination after it has found the first matching row.(MySQL正在寻找不同的值，因此在找到第一个匹配行后，它将停止搜索当前行组合的更多行)
FirstMatch
The semi-join FirstMatch join shortcutting strategy is used for tbl_name. (半连接去重执行优化策略，当匹配了第一个值之后立即放弃之后记录的搜索。这为表扫描提供了一个早期退出机制而且还消除了不必要记录的产生)；如下图所示：

注：半连接：当一张表在另一张表找到匹配的记录之后，半连接（semi-jion）返回第一张表中的记录。与条件连接相反，即使在右节点中找到几条匹配的记录，左节点的表也只会返回一条记录。另外，右节点的表一条记录也不会返回。半连接通常使用IN或EXISTS 作为连接条件。
Start temporary, End temporary
表示半连接中使用了DuplicateWeedout策略的临时表，具体实现过程如下图所示：
Full scan on NULL key
This occurs for subquery optimization as a fallback strategy when the optimizer cannot use an index-lookup access method.(子查询中的一种优化方式，主要在遇到无法通过索引访问null值的使用)
LooseScan(m..n)
The semi-join LooseScan strategy is used. m and n are key part numbers. 利用索引来扫描一个子查询表可以从每个子查询的值群组中选出一个单一的值。松散扫描(LooseScan)策略采用了分组，子查询中的字段作为一个索引且外部SELECT语句可以可以与很多的内部SELECT记录相匹配。如此便会有通过索引对记录进行分组的效果。
如下图所示：
Impossible HAVING
The HAVING clause is always false and cannot select any rows.(HAVING子句总是为false，不能选择任何行)
Impossible WHERE
The WHERE clause is always false and cannot select any rows.(WHERE子句始终为false，不能选择任何行)
Impossible WHERE noticed after reading const tables
MySQL has read all const (and system) tables and notice that the WHERE clause is always false.(MySQL读取了所有的const和system表，并注意到WHERE子句总是为false)
No matching min/max row
No row satisfies the condition for a query such as SELECT MIN(…) FROM … WHERE condition.(没有满足SELECT MIN（…）FROM … WHERE查询条件的行)
示例中，emp_number最小值为1001，没有满足条件的行:

如果此时将select字段改为其他字段，比如salary，则extra如下显示，使用到ICP优化机制(ICP机制见https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html)
no matching row in const table
For a query with a join, there was an empty table or a table with no rows satisfying a unique index condition.(表为空或者表中根据唯一键查询时没有匹配的行)
No matching rows after partition pruning
For DELETE or UPDATE, the optimizer found nothing to delete or update after partition pruning. It is similar in meaning to Impossible WHERE for SELECT statements.(对于DELETE或UPDATE，优化器在分区修剪后没有发现任何删除或更新。对于SELECT语句，它与Impossible WHERE的含义相似)
No tables used
The query has no FROM clause, or has a FROM DUAL clause.(没有FROM子句或者使用DUAL虚拟表)

.注：DUAL虚拟表纯粹是为了方便那些要求所有SELECT语句应该有FROM和可能的其他子句的人。 MySQL可能会忽略这些条款。如果没有引用表，MySQL不需要FROM DUAL(https://dev.mysql.com/doc/refman/5.7/en/select.html)
Not exists
MySQL能够对查询执行LEFT JOIN优化，并且在找到与LEFT JOIN条件匹配的一行后，不会在上一行组合中检查此表中的更多行。例如：
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id
WHERE t2.id IS NULL;
假设t2.id被定义为NOT NULL。在这种情况下，MySQL会扫描t1，并使用t1.id的值查找t2中的行。如果MySQL在t2中找到一个匹配的行，它会知道t2.id永远不会为NULL，并且不扫描t2中具有相同id值的其余行。换句话说，对于t1中的每一行，MySQL只需要在t2中只执行一次查找，而不考虑在t2中实际匹配的行数。
Range checked for each record (index map: N)
MySQL发现没有使用好的索引，但是发现在前面的表的列值已知之后，可能会使用一些索引。对于上表中的每一行组合，MySQL检查是否可以使用range或index_merge访问方法来检索行。这不是很快，但比执行没有索引的连接更快。
index map N索引的编号从1开始，按照与表的SHOW INDEX所示相同的顺序。索引映射值N是指示哪些索引是候选的位掩码值。例如，0x19（二进制11001）的值意味着将考虑索引1,4和5。
其中name属性为varchar类型；但是条件给出整数型，涉及到隐式转换。
图中t2也没有用到索引，是因为查询之前我将t2中name字段排序规则改为utf8_bin导致的链接字段排序规则不匹配。
Select tables optimized away
当我们使用某些聚合函数来访问存在索引的某个字段时，优化器会通过索引直接一次定位到所需要的数据行完成整个查询。在使用某些聚合函数如min, max的query，直接访问存储结构(B树或者B+树)的最左侧叶子节点或者最右侧叶子节点即可，这些可以通过index解决。Select count(*) from table(不包含where等子句)，MyISAM保存了记录的总数，可以直接返回结果，而Innodb需要全表扫描。Query中不能有group by操作;
Skip_open_table, Open_frm_only, Open_full_table
这些值表示适用于INFORMATION_SCHEMA表查询的文件打开优化；
Skip_open_table：表文件不需要打开。信息已经通过扫描数据库目录在查询中实现可用。
Open_frm_only：只需要打开表的.frm文件。
Open_full_table：未优化的信息查找。必须打开.frm，.MYD和.MYI文件。
unique row not found
对于诸如SELECT … FROM tbl_name的查询，没有行满足表上的UNIQUE索引或PRIMARY KEY的条件。
Using filesort
当Query 中包含 ORDER BY 操作，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer 不得不选择相应的排序算法来实现。数据较少时从内存排序，否则从磁盘排序。Explain不会显示的告诉客户端用哪种排序。官方解释：“MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行”
Using index
仅使用索引树中的信息从表中检索列信息，而不需要进行附加搜索来读取实际行(使用二级覆盖索引即可获取数据)。当查询仅使用作为单个索引的一部分的列时，可以使用此策略。
示例中第一个查询所有数据时，无法通过emp_number的覆盖索引来获取整行数据，所以需要根据主键id回表查询表数据。
Using index condition
Using index condition 会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行；

因为MySQL的架构原因，分成了server层和引擎层，才有所谓的“下推”的说法。所以ICP其实就是实现了index filter技术，将原来的在server层进行的table filter中可以进行index filter的部分，在引擎层面使用index filter进行处理，不再需要回表进行table filter(参考http://www.2cto.com/database/201511/451391.html)。
如下图描述：
Using index for group-by
数据访问和 Using index 一样，所需数据只须要读取索引，当Query 中使用GROUP BY或DISTINCT 子句时，如果分组字段也在索引中，Extra中的信息就会是 Using index for group-by。注：和Using index一样，只需读取覆盖索引
Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)
*注：
Block Nested-Loop Join算法：将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数。优化器管理参数optimizer_switch中中的block_nested_loop参数控制着BNL是否被用于优化器。默认条件下是开启，若果设置为off，优化器在选择 join方式的时候会选择NLJ(Nested Loop Join)算法。
Batched Key Access原理：对于多表join语句，当MySQL使用索引访问第二个join表的时候，使用一个join buffer来收集第一个操作对象生成的相关列值。BKA构建好key后，批量传给引擎层做索引查找。key是通过MRR接口提交给引擎的（mrr目的是较为顺序）MRR使得查询更有效率，要使用BKA，必须调整系统参数optimizer_switch的值，batched_key_access设置为on，因为BKA使用了MRR，因此也要打开MRR (参考http://www.cnblogs.com/chenpingzhao/p/6720531.html)。*
Using MRR
使用MRR策略优化表数据读取，仅仅针对二级索引的范围扫描和使用二级索引进行 join 的情况；
过程：先根据where条件中的辅助索引获取辅助索引与主键的集合，再将结果集放在buffer(read_rnd_buffer_size 直到buffer满了)，然后对结果集按照pk_column排序，得到有序的结果集rest_sort。最后利用已经排序过的结果集，访问表中的数据，此时是顺序IO。即MySQL 将根据辅助索引获取的结果集根据主键进行排序，将无序化为有序，可以用主键顺序访问基表，将随机读转化为顺序读，多页数据记录可一次性读入或根据此次的主键范围分次读入，减少IO操作，提高查询效率。
注：MRR原理：Multi-Range Read Optimization，是优化器将随机 IO 转化为顺序 IO 以降低查询过程中 IO 开销的一种手段，这对IO-bound类型的SQL语句性能带来极大的提升，适用于range ref eq_ref类型的查询；
Using sort_union(…), Using union(…), Using intersect(…)
这些指示索引扫描如何合并为index_merge连接类型。
(参考https://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html)
索引合并交叉口访问算法(The Index Merge Intersection Access Algorithm)：
index intersect merge就是多个索引条件扫描得到的结果进行交集运算。显然在多个索引提交之间是 AND 运算时，才会出现 index intersect merge. 下面两种where条件或者它们的组合时会进行 index intersect merge:
1) 条件使用到复合索引中的所有字段或者左前缀字段;
2) 主键上的任何范围条件。
intersect merge运行方式：多个索引同时扫描，然后结果取交集。如果所有条件字段都是索引字段，使用索引覆盖扫描，无需回表
示例：

SELECT * FROM innodb_table WHERE primary_key < 10 AND key_col1=20;
SELECT * FROM tbl_name  WHERE (key1_part1=1 AND key1_part2=2) AND key2=2;

索引合并联合访问算法(The Index Merge Union Access Algorithm)：
index uion merge就是多个索引条件扫描，对得到的结果进行并集运算，显然是多个条件之间进行的是 OR 运算。以下几种可能会使用到index merge union: 1) 条件使用到复合索引中的所有字段或者左前缀字段(对单字段索引也适用)；2) 主键上的任何范围条件；3) 任何符合 index intersect merge 的where条件；
示例：

SELECT * FROM t1 WHERE key1=1 OR key2=2 OR key3=3;
SELECT * FROM innodb_table WHERE (key1=1 AND key2=2) OR  (key3='foo' AND key4='bar') AND key5=5;

索引合并排序联合访问算法(The Index Merge Sort-Union Access Algorithm):
多个条件扫描进行 OR 运算，但是不符合 index union merge算法的，此时可能会使用 sort_union算法；

SELECT * FROM tbl_name WHERE key_col1 < 10 OR key_col2 < 20;
SELECT * FROM tbl_name
  WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col=30;

Using temporary
要解决查询，MySQL需要创建一个临时表来保存结果。如果查询包含不同列的GROUP BY和ORDER BY子句，则通常会发生这种情况。官方解释：”为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。很明显就是通过where条件一次性检索出来的结果集太大了，内存放不下了，只能通过家里临时表来辅助处理；
Using where
表示Mysql将对storage engine提取的结果进行过滤，过滤条件字段无索引；
Using where with pushed condition
仅用在ndb上。Mysql Cluster用Condition Pushdown优化改善非索引字段和常量之间的直接比较。condition被pushed down到cluster的数据节点，并在所有数据节点同时估算，把不合条件的列剔除避免网络传输