共计 18278 个字符，预计需要花费 46 分钟才能阅读完成。
作者：胡呈清
爱可生 DBA 团队成员，善于故障剖析、性能优化，集体博客：https://www.jianshu.com/u/a95…，欢送探讨。
本文起源：原创投稿
* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。
执行打算字段概要阐明
id
查问语句中每呈现一个 SELECT 关键字，MySQL 就会为它调配一个惟一的 id 值。也有例外，比方优化器对子查问做了 semi-join 优化时，和关联查问一样两个查问的 id 是一样的：
mysql> explain select * from t1 where a in (select b from t2 where t2.b=100);
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------------------------------------------------+
| 1 | SIMPLE | t1 | NULL | ref | a | a | 5 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where; FirstMatch(t1); Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------------------------------------------------+另外一个比拟非凡的是 id 为 NULL，比方：
mysql> explain select * from t1 union select * from t2;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | NULL |
| 2 | UNION | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+这是因为 union 后果是要去重的，外部创立了一个 <union1,2> 名字的长期表，把查问 1 和查问 2 的后果集都合并到这个长期表中，利用惟一键进行去重，这种状况下查问 id 就为 NULL。
select_type
示意查问的类型,
1.SIMPLE
查问语句中不蕴含 UNION 或者子查问的查问都算作是 SIMPLE 类型，比方说下边这个单表查问的 select_type 的值就是 SIMPLE：
mysql> explain select * from t1 where b=1 order by a;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+---------------------------------------+
| 1 | SIMPLE | t1 | NULL | ref | idx_b | idx_b | 5 | const | 1 | 100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+---------------------------------------+关联查问也是 SIMPLE 类型：
mysql> explain select * from t1 join t2 on t1.a=t2.a;
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+
| 1 | SIMPLE | t2 | NULL | ALL | a | NULL | NULL | NULL | 1 | 100.00 | Using where |
| 1 | SIMPLE | t1 | NULL | ref | a | a | 5 | hucq.t2.a | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-----------+------+----------+-------------+2.PRIMARY
对于蕴含 union 或者子查问的大查问来说，它是由几个小查问组成的，其中最右边的那个查问的 select_type 值就是 PRIMARY，比方：
mysql> explain select * from t1 union select * from t2;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | NULL |
| 2 | UNION | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+3.UNION
位于 union 或者 union all 查问中第二个及其当前的查问被标记为 UNION，第一个被标记为 primary，能够比照上一个例子的成果。
4.UNION RESULT
MySQL 抉择应用长期表来实现 UNION 查问的去重工作，针对该长期表的查问的 select_type 就是 UNION RESULT，跟上一个例子一样。
5.SUBQUERY
当子查问不能被优化成 semi-join，但能够用 materialization 来优化，显示为 SUBQUERY，示意这是一个不相干子查问，子查问只须要执行一遍（因为后果被物化成长期表了），举例：
mysql> SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE id < 10);
+----+-------------+-------+-------+---------+------+------+-------------+
| id | select_type | table | type | key | ref | rows | Extra |
+----+-------------+-------+-------+---------+------+------+-------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | 100 | Using where |
| 2 | SUBQUERY | t2 | range | PRIMARY | NULL | 9 | Using where |
+----+-------------+-------+-------+---------+------+------+-------------+6.DEPENDENT SUBQUERY
当子查问不能被优化成 semi-join，并且是一个相干子查问（或者非相干子查问敞开 materialization 优化策略时），会被优化器转化成 exists 相干子查问来进行查问，显示为 DEPENDENT SUBQUERY，示意这是一个相干子查问，会依据外查问后果执行屡次。举例：
mysql> SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE id < 10);
+----+--------------------+-------+-------+---------+------+----------+-------------+
| id | select_type | table | type | key | rows | filtered | Extra |
+----+--------------------+-------+-------+---------+------+----------+-------------+
| 1 | PRIMARY | t1 | ALL | NULL | 100 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | range | PRIMARY | 9 | 10.00 | Using where |
+----+--------------------+-------+-------+---------+------+----------+-------------+7.MATERIALIZED
当子查问被优化成 semi-join 执行，并且 semi-join 用的是 Materialize 策略，这个子查问对应显示就是 MATERIALIZED，而后用子查问后果物化后的长期表与另一张表进行关联查问。举例：
mysql> SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE id < 10);
+----+--------------+-------------+-------+---------+---------------+------+-------------+
| id | select_type | table | type | key | ref | rows | Extra |
+----+--------------+-------------+-------+---------+---------------+------+-------------+
| 1 | SIMPLE | <subquery2> | ALL | NULL | NULL | NULL | Using where |
| 1 | SIMPLE | t1 | ref | a | <subquery2>.b | 1 | NULL |
| 2 | MATERIALIZED | t2 | range | PRIMARY | NULL | 9 | Using where |
+----+--------------+-------------+-------+---------+---------------+------+-------------+8.DERIVED
对于无奈应用 derived_merge 优化的派生子查问，优化器会采纳物化的形式执行的蕴含派生表的子查问，该派生表对应的子查问的 select_type 就是 DERIVED，举例：
mysql> explain select * from t1 join (select distinct a from t2) as derived_t2 on t1.a=derived_t2.a;
+----+-------------+------------+------------+-------+---------------+-------------+---------+----------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+-------------+---------+----------------+------+----------+-------------+
| 1 | PRIMARY | t1 | NULL | ALL | a | NULL | NULL | NULL | 100 | 100.00 | Using where |
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 5 | join_test.t1.a | 10 | 100.00 | Using index |
| 2 | DERIVED | t2 | NULL | index | a | a | 5 | NULL | 1000 | 100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+-------------+---------+----------------+------+----------+-------------+对于子查问和派生表的优化策略，可具体查看对应的文章：MySQL 子查问优化、SQL 优化：derived 派生表优化
type
type 显示的是拜访类型，是较为重要的一个指标，后果值从好到坏顺次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL，一般来说，得保障查问至多达到 range 级别，最好能达到 ref。
1.system
当表中只有一条记录并且该表应用的存储引擎的统计数据是准确的，比方 MyISAM、Memory，那么对该表的拜访办法就是 system：
mysql> explain select * from t_myisam;
+----+-------------+----------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+--------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | t_myisam | NULL | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+----------+------------+--------+---------------+------+---------+------+------+----------+-------+InnoDB 表即便只有一行，也不是 system，而是 ALL：
mysql> explain select * from t5; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | t5 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+2.const
依据主键或者惟一二级索引列与单个常数进行等值匹配时（不能有多个条件用 or 连贯，这属于范畴查问），对单表的拜访办法就是 const，举例：
mysql> explain select * from t1 where id=100;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+3.eq_ref
在连贯查问时，如果被驱动表是通过主键或者惟一二级索引列等值匹配的形式进行拜访的（如果该主键或者惟一二级索引是联结索引的话，所有的索引列都必须进行等值比拟），则对该被驱动表的拜访办法就是 eq_ref，举例：
mysql> explain select * from t1 join t2 on t1.id=t2.id where t1.a<50;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+
| 1 | SIMPLE | t2 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | t1 | NULL | eq_ref | PRIMARY,a | PRIMARY | 4 | hucq.t2.id | 1 | 5.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+4.ref
当通过一般的二级索引列与常量进行等值匹配时，那么对该表的拜访办法就是 ref，即便匹配到的值可能是多行，举例：
mysql> explain select * from t11 where a=100;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t11 | NULL | ref | a | a | 5 | const | 500 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+5.ref_or_null
当对一般二级索引进行等值匹配查问，该索引列的值也能够是 NULL 值时，那么对该表的拜访办法就可能是 ref_or_null，举例：
mysql> explain select * from t11 where a=100 or a is null;
+----+-------------+-------+------------+-------------+---------------+------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------------+---------------+------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | t11 | NULL | ref_or_null | a | a | 5 | const | 501 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------------+---------------+------+---------+-------+------+----------+-----------------------+6.index_merge
个别状况下对于某个表的查问只能应用到一个索引，在某些场景下能够应用 Intersection、Union、Sort-Union 这三种索引合并的形式来执行查问，此时就显示为 index_merge，举例：
mysql> explain select * from t1 where a<50 or b=50;
+----+-------------+-------+------------+-------------+---------------+---------+---------+------+------+----------+----------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------------+---------------+---------+---------+------+------+----------+----------------------------------------+
| 1 | SIMPLE | t1 | NULL | index_merge | a,idx_b | a,idx_b | 5,5 | NULL | 50 | 100.00 | Using sort_union(a,idx_b); Using where |
+----+-------------+-------+------------+-------------+---------------+---------+---------+------+------+----------+----------------------------------------+7.unique_subquery
对一些蕴含 in 子查问的查问语句中，如果优化器无奈应用 semi-join 或物化进行优化，最终将子查问转换为 EXISTS 子查问，而且子查问能够应用到主键或者惟一键进行等值匹配的话，那么该子查问执行打算的 type 列的值就是 unique_subquery。举例：
mysql> explain select * from t1 where a in(select id from t2 where t1.a=t2.a) or b=100;
+----+--------------------+-------+------------+-----------------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+-----------------+---------------+---------+---------+------+------+----------+-------------+
| 1 | PRIMARY | t1 | NULL | ALL | idx_b | NULL | NULL | NULL | 1000 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | NULL | unique_subquery | PRIMARY,a | PRIMARY | 4 | func | 1 | 100.00 | Using where |
+----+--------------------+-------+------------+-----------------+---------------+---------+---------+------+------+----------+-------------+8.range
如果应用索引获取某些范畴区间的记录，那么就可能应用到 range 拜访办法。举例：
mysql> explain select * from t1 where a<50 and a>20;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t1 | NULL | range | a | a | 5 | NULL | 29 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
mysql> explain select * from t1 where a in(1,2,3);
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t1 | NULL | range | a | a | 5 | NULL | 3 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+9.index
须要扫描全副的索引记录时，该表的拜访办法就是 index，老本很高。举例：
mysql> EXPLAIN SELECT key_part1 FROM s1 WHERE key_part3 = 'a';
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | s1 | NULL | index | NULL | idx_key_part | 909 | NULL | 9688 | 10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+possible_keys 和 key
在 EXPLAIN 语句输入的执行打算中，possible_keys 列示意在某个查问语句中，对某个表执行单表查问时可能用到的索引有哪些，key 列示意理论用到的索引有哪些。
有些时候应用到的索引未必在 passible_keys 中，下面 type 为 index 的示例中，passible_keys 显示为 NULL，但理论 key 显示是应用到索引的。possible_keys 列中的值并不是越多越好，可能应用的索引越多，查问优化器计算查问老本时就得破费更长时间，所以如果能够的话，尽量删除那些用不到的索引。
key_len
key_len 列显示 MySQL 决定应用的键长度。如果键是 NULL，则长度为 NULL。应用的索引的长度。在不损失精确性的状况下，长度越短越好。
ref
当应用索引列等值匹配的条件去执行查问时，也就是在拜访办法是 const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery 其中之一时，ref 列展现的就是与索引列作等值匹配的对象是啥。如果不是等值查问，则显示为 NULL。
比方单表查问时必定是个常数 const：
mysql> explain select * from t1 where a=100;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | a | a | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+而关联查问中则是驱动表的关联字段 t2.a：
mysql> explain select * from t1 join t2 on t1.a=t2.a where t1.a<50;
+----+-------------+-------+------------+-------+---------------+------+---------+-----------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+-----------+------+----------+-----------------------+
| 1 | SIMPLE | t2 | NULL | range | a | a | 5 | NULL | 1 | 100.00 | Using index condition |
| 1 | SIMPLE | t1 | NULL | ref | a | a | 5 | hucq.t2.a | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+------+---------+-----------+------+----------+-----------------------+rows
如果查问优化器决定应用全表扫描的形式对某个表执行查问时，执行打算的 rows 列就代表预计须要扫描的行数；如果应用索引来执行查问时，执行打算的 rows 列就代表预计扫描的索引记录行数。
这有可能是个准确值，也可能是个估算值，计算方法有 index dive 和基于统计索引信息的估算。
filtered
对于单表查问来说：
- 如果是全表扫描，filtered 值代表满足 where 条件的行数占表总行数的百分比；
- 如果是应用索引来执行查问，filtered 值代表从索引上获得数据后，满足其余过滤条件的数据行数的占比。
mysql> explain select * from t1 where a<100 and b > 100;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------+
| 1 | SIMPLE | t1 | NULL | range | a | a | 5 | NULL | 99 | 33.33 | Using index condition; Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------------------+下面示例中，从 a 索引上获得 99 行数据，优化器估算认为这 99 行数据中有 33.33% 满足 b > 100 这个条件。对于单表查问来说，这个意义不大。
对于关联查问来说，驱动表的 rows*(filtered/100) 代表优化器认为的扇出，对于关联查问的老本估算有很大的影响。举例：
mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE | s1 | NULL | ALL | idx_key1 | NULL | NULL | NULL | 9688 | 10.00 | Using where |
| 1 | SIMPLE | s2 | NULL | ref | idx_key1 | idx_key1 | 303 | xiaohaizi.s1.key1 | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+从执行打算中能够看进去，查问优化器打算把 s1 当作驱动表，s2 当作被驱动表。咱们能够看到驱动表 s1 表的执行打算的 rows 列为 9688，filtered 列为 10.00，这意味着驱动表 s1 的扇出值就是 9688 × 10.00% = 968.8，这阐明还要对被驱动表执行大概 968 次查问。
Extra
Extra 是 EXPLAIN 输入中另外一个很重要的列，该列显示 MySQL 在查问过程中的一些详细信息。
因为对其中几个状态有纳闷，所以这部分内容写到另一篇文章中独自探讨。
正文完
