文章目录

Explain的作用博文背景Explain 使用方法Explain之ID说明1. ID值相同2.ID值不同3.ID值相同，ID值不同共存 Explain的select_type详解SIMPLE 简单查询PRIMARY 主查询DERIVED 衍生UNION 联合SUBQUERY 子查询UNION RESULT 联合结果 Explain的table详解Explain的type域详解1.system2.const3.eq_ref4.ref5.range6.index7.all Explain之possible_keys域详解Explain之key域详解Explain之ref域详解Explain之rows域详解Explain之Extra域详解1. Using filesort（文件排序）2. Using temporary（使用临时表）3. Using index.(使用索引)4. Using Where5. Using join buffer 使用了连接缓存6. impossible where7. select tables optimized away8. distinct 总结

Explain的作用

使用Explain 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理我们的SQL语句的，就可以根据explain获取到的执行计划分析我们SQL语句或是表结构的性能瓶颈，从而调整表结构的索引或是SQL语句的优化。

博文背景

下文中所有的操作都基于这两张表，student，class表，表结构和索引如下

mysql> desc student;+---------+------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra|+---------+------------+------+-----+---------+----------------+| id| bigint| NO | PRI | NULL | auto_increment || name | varchar(9) | YES || NULL ||| age| int | YES || NULL ||| classId | bigint| NO || NULL ||+---------+------------+------+-----+---------+----------------+4 rows in set (0.02 sec)mysql> show index from student;+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| student |0 | PRIMARY| 1 | id| A | 0 | NULL| NULL || BTREE| || YES| NULL || student |0 | idx_name | 1 | name | A | 4 | NULL| NULL | YES | BTREE| || YES| NULL || student |1 | idx_classId | 1 | classId| A | 3 | NULL| NULL || BTREE| || YES| NULL |+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+3 rows in set (0.04 sec)mysql> mysql> desc class;+-------+-------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra|+-------+-------------+------+-----+---------+----------------+| id | bigint| NO | PRI | NULL | auto_increment || name | varchar(32) | YES || NULL ||+-------+-------------+------+-----+---------+----------------+2 rows in set (0.03 sec)mysql> show index from class;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| class |0 | PRIMARY | 1 | id| A | 5 | NULL| NULL || BTREE| || YES| NULL || class |1 | idx_name | 1 | name | A | 5 | NULL| NULL | YES | BTREE| || YES| NULL |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+2 rows in set (0.05 sec)mysql>

Explain 使用方法

Explain + SQL语句，就可以看到相应的执行计划，如下所示。

mysql> explain select * from student;+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+| 1 | SIMPLE| student | NULL | ALL | NULL| NULL | NULL | NULL | 4 | 100.00 | NULL |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+1 row in set (0.03 sec)mysql>

由上我们知道，Explain的结果中有如下几个信息项，下面我将一一来看看每一个选项的作用。

Explain之ID说明

explain的ID指的是查询的序列号，由一组数字组成，表示查询中执行select字句或者操作表的顺序。它包含以下三种情况：

1. ID值相同

ID值相同，按顺序由上到下执行操作

mysql> explain select * from student left join class on student.classId = class.id;+----+-------------+---------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key| key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------+| 1 | SIMPLE| student | NULL | ALL | NULL| NULL | NULL | NULL | 4 | 100.00 | NULL || 1 | SIMPLE| class | NULL | eq_ref | PRIMARY | PRIMARY | 8 | news.student.classId | 1 | 100.00 | NULL |+----+-------------+---------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------+2 rows in set (0.03 sec)mysql>

2.ID值不同

ID值越大，执行的优先级越高。

mysql> explain select classId,name from student union all select * from class where id = 2;+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key| key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| 1 | PRIMARY| student | NULL | ALL | NULL| NULL | NULL | NULL | 4 | 100.00 | NULL || 2 | UNION | class | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

如上所示两条记录的id值递增，表示mysql先操作表class，然后再操作表student，由上到下顺序执行。

3.ID值相同，ID值不同共存

ID值越大的先执行，ID值相同的按照顺序由上到下执行。

mysql> explain select * from student where classId in (select id from class) union all (select * from student where id = 2);+----+-------------+---------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key| key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------------+| 1 | PRIMARY| student | NULL | ALL | NULL| NULL | NULL | NULL | 4 | 100.00 | NULL || 1 | PRIMARY| class | NULL | eq_ref | PRIMARY | PRIMARY | 8 | news.student.classId | 1 | 100.00 | Using index || 3 | UNION | student | NULL | const | PRIMARY | PRIMARY | 8 | const| 1 | 100.00 | NULL |+----+-------------+---------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------------+3 rows in set (0.03 sec)

Explain的select_type详解

查询的类型，主要用于区分普通查询、联合查询、子查询等复杂的查询。

select_type的取值范围有

SIMPLE 简单查询

mysql> explain select * from student where classId in (select name from class);+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------+| 1 | SIMPLE| student | NULL | ALL | NULL| NULL | NULL | NULL | 4 | 100.00 | NULL|| 1 | SIMPLE| class | NULL | ALL | NULL| NULL | NULL | NULL | 5 | 20.00 | Using where; FirstMatch(student); Using join buffer (hash join) |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------------+2 rows in set (0.03 sec)

PRIMARY 主查询

DERIVED 衍生

UNION 联合

mysql> explain SELECT * FROM (select * from student where classId in (select id from class) union all (select * from student where id = 2)) A WHERE A.ID = 3;+----+-------------+------------+------------+--------+---------------+-------------+---------+----------------------+------+----------+-------------+| id | select_type | table| partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+------------+------------+--------+---------------+-------------+---------+----------------------+------+----------+-------------+| 1 | PRIMARY| <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 8 | const| 1 | 100.00 | NULL || 2 | DERIVED| student | NULL | ALL | NULL| NULL | NULL | NULL | 4 | 100.00 | NULL || 2 | DERIVED| class| NULL | eq_ref | PRIMARY | PRIMARY| 8 | news.student.classId | 1 | 100.00 | Using index || 4 | UNION | student | NULL | const | PRIMARY | PRIMARY| 8 | const| 1 | 100.00 | NULL |+----+-------------+------------+------------+--------+---------------+-------------+---------+----------------------+------+----------+-------------+4 rows in set (0.03 sec)

SUBQUERY 子查询

mysql> explain select * from student A where classid = (select id from class where name = '一1班');+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| 1 | PRIMARY| A| NULL | ALL | NULL| NULL | NULL | NULL | 4 | 25.00 | Using where || 2 | SUBQUERY | class | NULL | ALL | NULL| NULL | NULL | NULL | 5 | 20.00 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+2 rows in set (0.04 sec)

UNION RESULT 联合结果

Explain的table详解

操作的是哪张表，有时候会在表后面加序号如dirived2

mysql> explain SELECT * FROM (select * from student where classId in (select id from class) union all (select * from student where id = 2)) A WHERE A.ID = 3;+----+-------------+------------+------------+--------+---------------+-------------+---------+----------------------+------+----------+-------------+| id | select_type | table| partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+------------+------------+--------+---------------+-------------+---------+----------------------+------+----------+-------------+| 1 | PRIMARY| <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 8 | const| 1 | 100.00 | NULL || 2 | DERIVED| student | NULL | ALL | NULL| NULL | NULL | NULL | 4 | 100.00 | NULL || 2 | DERIVED| class| NULL | eq_ref | PRIMARY | PRIMARY| 8 | news.student.classId | 1 | 100.00 | Using index || 4 | UNION | student | NULL | const | PRIMARY | PRIMARY| 8 | const| 1 | 100.00 | NULL |+----+-------------+------------+------------+--------+---------------+-------------+---------+----------------------+------+----------+-------------+4 rows in set (0.03 sec)

Explain的type域详解

显示查询使用了何种类型，所有的取值范围如下表所示

从最好到最差依次是：SYSTEM>CONST>EQ_REF>REF>RANGE>INDEX>ALL

1.system

表中只有一条记录（等于系统表）,这是const类型的特例，可以忽略不计

2.const

表示通过索引，一次就找到了符合条件的记录，const 用于比较primary Key 或者unique索引。因为只匹配一行数据，所以很快。

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 | 8 | const | 1 | 100.00 | NULL |+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+1 row in set (0.03 sec)

3.eq_ref

唯一性索引扫描，对于每个索引键表中只有一条记录与之匹配，常见于主键或唯一索引。如下所示，classId在class表中只有一条记录

mysql> select * from student;+----+--------+-----+---------+| id | name | age | classId |+----+--------+-----+---------+| 1 | 张三 | 12 | 1 || 2 | 李四 | 14 | 2 || 3 | 张三风 | 16 | 1 || 4 | 李四海 | 19 | 2 || 5 | 王老吉 | 20 | 3 |+----+--------+-----+---------+5 rows in set (0.02 sec)mysql> select * from class;+----+--------+| id | name |+----+--------+| 1 | 一一班 || 2 | 一2班 || 3 | 一3班 || 4 | 一4班 || 5 | 一5班 || 6 | 一6班 |+----+--------+6 rows in set (0.04 sec)mysql> explain select * from student where exists (select 1 from class where id = student.classId);+----+--------------------+---------+------------+--------+---------------+---------+---------+----------------------+------+----------+--------------------------+| id | select_type | table | partitions | type | possible_keys | key| key_len | ref | rows | filtered | Extra|+----+--------------------+---------+------------+--------+---------------+---------+---------+----------------------+------+----------+--------------------------+| 1 | PRIMARY | student | NULL | ALL | NULL| NULL | NULL | NULL | 5 | 100.00 | Using where || 2 | DEPENDENT SUBQUERY | class | NULL | eq_ref | PRIMARY | PRIMARY | 8 | news.student.classId | 1 | 100.00 | Using where; Using index |+----+--------------------+---------+------------+--------+---------------+---------+---------+----------------------+------+----------+--------------------------+2 rows in set (0.04 sec)

4.ref

非唯一索引扫描，返回匹配某个单独值的所有行。

mysql> explain select * from student where classId = 1;+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+| 1 | SIMPLE| student | NULL | ref | idx_classId_age | idx_classId_age | 4 | const | 2 | 100.00 | NULL |+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+1 row in set (0.02 sec)

5.range

通过索引扫描的是一个范围

mysql> explain select * from student where classId >= (select id from class where name = '一一班' limit 1);+----+-------------+---------+------------+-------+---------------+-------------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+-------+---------------+-------------+---------+-------+------+----------+-------------+| 1 | PRIMARY| student | NULL | range | idx_classId | idx_classId | 8 | NULL | 4 | 100.00 | Using where || 2 | SUBQUERY | class | NULL | ref | idx_name| idx_name | 131| const | 1 | 100.00 | Using index |+----+-------------+---------+------------+-------+---------------+-------------+---------+-------+------+----------+-------------+2 rows in set (0.04 sec)

6.index

全索引扫描,index与ALL的区别是index类型只遍历索引树、这通常会比ALL快，因为索引文件比数据文件小。

mysql> explain select name from student;+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key| key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-------------+| 1 | SIMPLE| student | NULL | index | NULL| idx_name | 39| NULL | 4 | 100.00 | Using index |+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-------------+1 row in set (0.03 sec)

7.all

全表扫描

mysql> explain select * from student;+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE| student | NULL | ALL | NULL| NULL | NULL | NULL | 4 | 25.00 | Using where |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set (0.04 sec)

Explain之possible_keys域详解

显示可能应用在这张表中的索引，一个或者多个、查询涉及到的字段上若存在索引，则该索引被列出，但是并不一定被查询实际所使用到。

Explain之key域详解

实际使用的索引，如果为NULL，则没有使用索引。（查询中使用了覆盖索引，则该索引和查询中的select 字段重叠）

Explain之ref域详解

显示索引的那一列被使用了，如果可能的话是一个常数(const)

mysql> explain select * from student where classId = 1 and age = 12;+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------+| 1 | SIMPLE| student | NULL | ref | idx_classId_age | idx_classId_age | 9 | const,const | 1 | 100.00 | NULL |+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------+1 row in set (0.03 sec)mysql>

例如，student 表有复合索引，上述查询语句中全匹配，classId 和age ，使用到了索引的两个列，所以ref中出现两个const。

Explain之rows域详解

根据表统计信息及索引选用情况、大致估算出找到所需记录需要读取的行数。

Explain之Extra域详解

包含不适合在其他列中展示但十分重要的额外信息

1. Using filesort（文件排序）

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。 mysql中无法利用索引完成的排序称之为“文件排序”,这种情况应当极力避免。

mysql> explain select * from student where classId >= 0 order by age;+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------------------------+| 1 | SIMPLE| student | NULL | range | idx_classId | idx_classId | 8 | NULL | 4 | 100.00 | Using index condition; Using filesort |+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------------------------+1 row in set (0.07 sec)

2. Using temporary（使用临时表）

使用了临时表保存中间的结果，MYSQL在对查询结果进行排序是使用临时表、常见于排序order by 和分组查询group by，这种情况也应当极力避免，比如使用覆盖索引。

mysql> explain select distinct classId,age,name from student;+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+| 1 | SIMPLE| student | NULL | ALL | NULL| NULL | NULL | NULL | 4 | 100.00 | Using temporary |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+1 row in set (0.03 sec)

3. Using index.(使用索引)

表示相应的select 操作中使用了覆盖索引(Covering Index)，避免了访问数据表的数据行，效率不错！

如果同时出现了Using where,表明索引用来执行索引键值的查找；

如果没有出现Using Where，表明索引用来读取数据而非执行查找。

mysql> explain select classId,age from student where classId > 1 ;+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra|+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+| 1 | SIMPLE| student | NULL | range | idx_classId_age | idx_classId_age | 4 | NULL | 2 | 100.00 | Using where; Using index |+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+1 row in set (0.03 sec)

4. Using Where

表示出现了where过滤，如上所示。

5. Using join buffer 使用了连接缓存

mysql> explain select distinct classId,student.age,student.name from student left join class on student.name = class.name;+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra|+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------+| 1 | SIMPLE| student | NULL | ALL | NULL| NULL | NULL | NULL | 4 | 100.00 | Using temporary|| 1 | SIMPLE| class | NULL | ALL | NULL| NULL | NULL | NULL | 6 | 100.00 | Using where; Distinct; Using join buffer (Block Nested Loop) |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------+2 rows in set (0.02 sec)mysql>

6. impossible where

where字句的值总是false，不能用来获取任何元素

mysql> explain select * from student where false ;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+| 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| Impossible WHERE |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+1 row in set (0.03 sec)

7. select tables optimized away

mysql> explain select max(classId),min(classId) from student ;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+| 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| Select tables optimized away |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+1 row in set (0.04 sec)

8. distinct

优化distinct操作，在找到第一匹配的元祖后立即停止找同样值的动作。

示例操作见(Using join buffer)

总结

至此，Explain的运用全部介绍完毕，希望此文能够帮助到大家更好的使用mysql的SQL语言性能优化神器Explain。

需要重点记住的有如下几点：

Explain 的type从最好到最差依次是：SYSTEM>CONST>EQ_REF>REF>RANGE>INDEX>ALL,所以应当尽量避免全表扫描，并按照该顺序进行SQL语句的优化。

需要重点关注Key，它指的是实际使用的索引名称。

Extra域中需要重点关注是否出现“Using filesort” 和“Using temporary”，这两者应当尽量避免，会严重影响性能。