MySQL之EXPLAIN命令分析SQL执行计划
EXPLAIN命令概述
MySQL提供一个执行计划的工具，通过EXPLAIN命令可以模拟服务端执行SQL查询语句的过程，并对语句的执行计划进⾏分析, 并输出执行的详细信息, 以供针对性优化。因此可以分析语句或者表的性能瓶颈。
MySQL5.6.3以前：只能分析SELECT
MySQL5.6.3以后：可以分析update、delete、insert
使用explain命令来查看SQL语句的执行计划，查看该SQL语句有没有使用索引，有没有做全表扫描，这都可以通过explain命令来查看。
还可以通过explain命令深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采⽤。
EXPLAIN 命令⽤法⼗分简单, 在 SELECT 语句前加上 explain 就可以了。
例如：EXPLAIN SELECT * FROM tuser
mysql> EXPLAIN SELECT * FROM tuser ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | tuser | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
测试SQL
DROP TABLE IF EXISTS course ;
CREATE TABLE `course` (
`id` int(3) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`tid` int(3) DEFAULT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
DROP TABLE IF EXISTS teacher ;
CREATE TABLE `teacher` (
`id` int(3) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
INSERT INTO course VALUES ( 1 , 'PHP' , 1 ) ;
INSERT INTO course VALUES (2, 'Go', 2 ) ;
INSERT INTO course VALUES ( 3 , 'Java' , 3 ) ;
INSERT INTO course VALUES ( 4 , 'Python', 3 ) ;
INSERT INTO teacher VALUES ( 1 , 'wangwu' );
INSERT INTO teacher VALUES ( 2 , 'zhangsan' ) ;
INSERT INTO teacher VALUES ( 3, 'lisi' ) ;
参数说明
expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
id:选择标识符
select_type:表示查询的类型
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明
1.id 标识符
id是查询序列编号，每张表都是单独访问的，一个SELECT就会有一个序号，它是自动分配的⼀个唯一标识符
表示查询中操作表的顺序：
数字越大越先执行，如果数字一样大，那么就从上往下依次执行，id列为null就表示这是一个结果集，不需要使用它来进行查询。
查询1
ID不同的先大后小顺序执行
拿到内层结果之后才进行外层查询
mysql> EXPLAIN SELECT id,name FROM course WHERE tid=(SELECT id FROM teacher WHERE name='lisi');
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | PRIMARY | course | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
| 2 | SUBQUERY | teacher | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
查询2
ID相同的从上往下顺序执行
在连接查询中，先查询的叫做驱动表，后查询的叫做被驱动表
先查小表，中间结果最少，用小表驱动大表
mysql> EXPLAIN SELECT a.id,a.name tname,b.name FROM course a,teacher b WHERE a.tid=b.id and b.name='lisi';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | b | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
概括：ID有相同也有不同，ID不同的先大后小，ID相同的从上往下
2.select_type 查询类型
查询类型：主要用于区别普通查询、联合查询(union、union all)、子查询等复杂查询。
1.simple
表示不需要union关联查询操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple，且只有一个。
mysql> EXPLAIN SELECT * FROM teacher;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | teacher | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
2.primary
一个需要union操作或者含有子查询的select，SQL语句中的主查询，位于最外层的查询，且只有一个
mysql> EXPLAIN SELECT id,name FROM course WHERE tid=(SELECT id FROM teacher WHERE name='lisi');
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | PRIMARY | course | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
| 2 | SUBQUERY | teacher | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
3.subquery
子查询中所有的内层查询都是SUBQUERY类型
4.dependent subquery
与dependent union类似，表示这个subquery的查询要受到外部表查询的影响
mysql> explain select id,name,(select name from teacher a where a.id=b.tid) from course b;
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | PRIMARY | b | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
| 2 | DEPENDENT SUBQUERY | a | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+--------------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)
5.derived
from字句中出现的子查询，也叫做派生表。
也叫衍生查询，表示在得到最终查询结果之前会用到临时表
对于关联查询，先执行右边的table(UNION)，再执行左边的table，类型是DERIVED。
mysql> EXPLAIN SELECT tb.* FROM (SELECT * FROM teacher a WHERE a.name='lisi' UNION SELECT * FROM teacher b WHERE b.name='wangwu' ) tb;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
| 2 | DERIVED | a | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
| 3 | UNION | b | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
| NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
4 rows in set, 1 warning (0.00 sec)
6.union
union连接的两个select查询，第一个查询是PRIMARY，除了第一个表外，第二个以后的表select_type都是union
7.union result
包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null
主要是显示哪些表之间存在UNION查询。<union2,3>代表id=2和id=3的查询存在UNION。
5.dependent union
与union一样，出现在union 或union all语句句中，但是这个查询要受到外部查询的影响
mysql> explain select * from course where tid in (SELECT id FROM teacher a WHERE a.name='lisi' UNION SELECT id FROM teacher b WHERE b.name='wangwu' );
+----+--------------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | course | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | a | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
| 3 | DEPENDENT UNION | b | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
| NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
4 rows in set, 1 warning (0.00 sec)
3.table 结果表
显示的查询表名，如果查询使用了别名，那么这里显示的是别名
如果不涉及对数据表的操作，那么这显示为null
如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。
如果是尖括号括起来的<union M,N>，与类似，也是⼀个临时表，表示这个结果来自于union查询的id为M,N的结果集。
4.type 连接类型
type依次从好到差：system、const、eq_ref、ref、fulltext、ref_or_null、unique_subquery、index_subquery、range、index_merge、index、ALL
除了all之外，其他的type都可以使用到索引
除了index_merge之外，其他的type只可以用到⼀个索引
type最少要索引使用到range级别
1.system
表中只有一行数据或者是空表，且只能用于myisam和memory表，如果是Innodb引擎表，type列在这个情况通常都是all或者index。
2.const（重要）
使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描
mysql> explain select * from teacher where id=1;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | teacher | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
3.eq_ref（重要）
连接字段主键或者唯一性索引。此类型通常出现在多表的 join 查询, 表示对于前表的每⼀个结果, 都只能匹配到后表的一行结果，并且查询的比较操作通常是 ‘=’, 查询效率较高。
简言之：出现在多表的join查询，被驱动表通过唯一性索引（UNIQUE、PRIMARY KEY)进行访问
eg_ref是除const之外最好的访问类型。
mysql> explain select a.id from course a left join teacher b on a.tid=b.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+
| 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
| 1 | SIMPLE | b | NULL | eq_ref | PRIMARY | PRIMARY | 4 | demo.a.tid | 1 | 100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
4.ref（重要）
表示表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。
针对非唯一性索引，使用等值（=）查询非主键。或者是使用了最左前缀规则索引的查询。
非唯一索引
添加非唯一性索引
ALTER TABLE course ADD INDEX key_tid (tid);
mysql> explain select * from course where tid=1;
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | course | NULL | ref | key_tid | key_tid | 5 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
等值非主键连接
ALTER TABLE teacher ADD INDEX key_name (name);
ALTER TABLE course ADD INDEX key_name (name);
mysql> explain select a.id from course a left join teacher b on a.name=b.name;
+----+-------------+-------+------------+-------+---------------+----------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | a | NULL | index | NULL | key_name | 83 | NULL | 4 | 100.00 | Using index |
| 1 | SIMPLE | b | NULL | ref | key_name | key_name | 83 | demo.a.name | 1 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+-------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
最左前缀
mysql> explain select * from course where name = 'go';
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | course | NULL | ref | key_name | key_name | 83 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
5.fulltext
全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全⽂索引
mysql> explain select * from tb where match(addr) against('aa');
+----+-------------+-------+------------+----------+---------------+---------+---------+-------+------+----------+-------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+----------+---------------+---------+---------+-------+------+----------+-------------------------------+
| 1 | SIMPLE | tb | NULL | fulltext | ft_addr | ft_addr | 0 | const | 1 | 100.00 | Using where; Ft_hints: sorted |
+----+-------------+-------+------------+----------+---------------+---------+---------+-------+------+----------+-------------------------------+
1 row in set, 1 warning (0.00 sec)
6.ref_or_null
与ref⽅法类似，只是增加了null值的⽐较。实际⽤的不多
7.unique_subquery
⽤于where中的in形式子查询，⼦查询返回不重复值唯一值
8.index_subquery
用于in形式子查询使⽤到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。
9.range（重要）
索引范围扫描，只检索给定范围的行，使用一个索引来选择行,常见于使用>,<,is null,between ,in ,like等运算符的查询中。
mysql> explain select * from course where id>1;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | course | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 3 | 100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
like 'a%' 使用索引， like '%a' 不使用索引
mysql> explain select * from course where name like 'aa%';
+----+-------------+--------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | course | NULL | range | key_name | key_name | 83 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+--------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from course where name like '%aa';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | course | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
10.index_merge
表示查询使⽤了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能⼤部分时间都不如range
11.index（重要)
index与ALL区别为index类型只遍历索引树
关键字：条件是出现在索引树中的节点的。可能没有完全匹配索引
索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询
单索引
mysql> explain select name from course;
+----+-------------+--------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | course | NULL | index | NULL | key_name | 83 | NULL | 4 | 100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
组合索引
删除单个索引，创建组合索引
ALTER TABLE course DROP INDEX key_tid
ALTER TABLE course DROP INDEX key_name
ALTER TABLE course ADD INDEX key_name_tid (name,tid);
mysql> explain select name,tid from course;
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | course | NULL | index | NULL | key_name_tid | 88 | NULL | 4 | 100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
12.all（重要）
MySQL将遍历全表以找到匹配的行，然后再在server层进行过滤返回符合要求的记录。是回表查询。
5.possible_keys 可能使用的索引
指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
查询中可能选用的索引，一个或多个，如果没有任何索引显示 null，可能用到索引不代表一定用到索引
即使possible_keys为空，key也可能有值，如以下情况，使用到覆盖索引。
mysql> explain select name from course;
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | course | NULL | index | NULL | key_name_tid | 88 | NULL | 4 | 100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
6.key 实际使用的索引
查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中
7.key_len 索引长度
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。不损失精确性的情况下，长度越短越好。
简言之：索引的长度，即使用的字节数。跟索引字段的类型、长度有关。
用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去。
mysql> explain select * from course where id=1;
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | course | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。
mysql> explain select * from course where id=2 and name='Go';
+----+-------------+--------+------------+-------+----------------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+----------------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | course | NULL | const | PRIMARY,key_name_tid | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+-------+----------------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
另外，key_len 只计算 where 条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len 中。
8.ref 列与索引比较
列与索引的比较，表示表的连接匹配条件，即哪些列或常量被用于查找索引列上的值，即使用哪个列或者常数和索引一起从表中筛选数据
如果是使用的常数等值查询，这里会显示const
如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段
如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这⾥里可能显示为func
9.rows 预估结果行数
估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
这里是执行计划中估算的扫描行数，不是精确值
InnoDB不是精确的值，MyISAM是精确的值，主要原因是InnoDB里⾯面使⽤了MVCC并发机制
行数越少越好
10.extra 额外信息
执行计划给出的额外的信息说明。这个列包含不适合在其他列中显示单十分重要的额外的信息，这个列可以显示的信息非常多，有几十种，常用的有:
1.using temporary
表示使用了临时表存储中间结果
MySQL在对查询结果order by和group by时使⽤用临时表
临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量量，used_tmp_table，used_tmp_disk_table才能看出来。
distinct非索引列
mysql> explain select distinct name from teacher;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | SIMPLE | teacher | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using temporary |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)
GROUP BY非索引列
mysql> explain select * from teacher GROUP BY name ;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| 1 | SIMPLE | teacher | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using temporary; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.01 sec)
JON连接，Group By 任意列
mysql> explain select a.id from course a left join teacher b on a.name=b.name GROUP BY a.name;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | a | NULL | index | key_name_tid | key_name_tid | 88 | NULL | 4 | 100.00 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | b | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
2.no tables used
不带from字句的查询或者From dual查询
使⽤用not in()形式子查询或not exists运算符的连接查询，这种叫做反连接
即，一般连接查询是先查询内表，再查询外表，反连接就是先查询外表，再查询内表。
mysql> explain select now();
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 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 tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
3.using filesort（重要）
排序时无法使用到索引时，就会出现这个。常见于order by和group by语句句中
说明MySQL会使用一个外部的索引排序，而不是按照索引顺序进⾏行读取。
MySQL中无法利用索引完成的排序操作称为“文件排序”
mysql> explain select * from teacher ORDER BY name ;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | teacher | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
4.using index（重要）
查询时不不需要回表查询，直接通过索引就可以获取查询的数据，是使用覆盖索引的表现
表示相应的SELECT查询中使用到了覆盖索引（Covering Index），避免访问表的数据行，效率不错！
如果同时出现Using Where ，说明索引被用来执行查找索引键值
如果没有同时出现Using Where ，表明索引用来读取数据而⾮非执⾏行查找动作。
mysql> explain select name,tid from course ;
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | course | NULL | index | NULL | key_name_tid | 88 | NULL | 4 | 100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
5.using where（重要）
使用了where过滤，表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤，跟是否使用索引没有关系
查询条件无索引
mysql> explain select * from teacher where name='lisi';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | teacher | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
索引失效,组合索引顺序：name age sex
mysql> explain select * from tuser where age=1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tuser | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
查询条件中分为限制条件和检查条件：
5.6之前：存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤再返回真正符合查询的数据
5.6.x之后：支持ICP特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就⼤大减少了存储引擎扫描的记录数量。extra列显示using index condition
6.firstmatch(tb_name)
5.6.x开始引入的优化子查询的新特性之一，常见于where字句含有in()类型的子查询。如果内表的数据量比较大，就可能出现这个。
7.loosescan(m…n)
5.6.x之后引入的优化子查询的新特性之⼀一，在in()类型的子查询中，子查询返回的可能有重复记录时，就可能出现这个
8.filtered
使用explain extended时会出现这个列列，5.7之后的版本默认就有这个字段，不需要使用explain extended了。这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/137041.html
