mysql Explain 详解

 参考：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

1. EXPLAIN 是做什么的

 EXPLAIN语句提供了有关MySQL如何执行语句的信息。 EXPLAIN使用SELECT，DELETE，INSERT，REPLACE和UPDATE语句。EXPLAIN返回SELECT语句中使用的每个表的一行信息。它按照MySQL在处理语句时读取它们的顺序列出输出中的表。 MySQL使用嵌套循环连接方法解析所有连接。这意味着MySQL从第一个表中读取一行，然后在第二个表，第三个表中找到匹配的行，依此类推。处理完所有表后，MySQL通过表列表输出选定的列和回溯，直到找到有更多匹配行的表。下一行从该表中读取，并且该过程继续下一个表。EXPLAIN输出包含分区信息。另外，对于SELECT语句，EXPLAIN生成扩展信息，可以在EXPLAIN之后使用SHOW WARNINGS显示。

      2. explain 如何使用

EXPLAIN
SELECT * FROM information_schema.PROCESSLIST

  结果

+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | PROCESSLIST | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL | NULL     | NULL  |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+

     3. explain 字段解析

	中文名	详情
id	查询ID	ID
select_type	查询类型	查询类型，会因为关联关系更改值
table	表名	查询的表名
partitions	分区信息	The matching partitions
type	链接类型	连接类型。 有关不同类型的说明
possible_keys	可能命中的索引	possible_keys列表示MySQL可以从中选择查找表中的行的索引。 请注意，此列完全独立于EXPLAIN输出中显示的表的顺序。
key	命中的索引	关键字列表示MySQL实际决定使用的关键字（索引）。如果MySQL决定使用其中一个possible_keys索引来查找行，则该索引被列为关键值。  要强制MySQL使用或忽略possible_keys列中列出的索引，请在查询中使用FORCE INDEX，USE INDEX或IGNORE INDEX
key_len	索引长度	key_len数值越小越优  由于密钥的存储格式，关键字长度对于可以为NULL的列的长度大于对于NOT NULL列的关键字长度  key_len列表示MySQL决定使用的密钥的长度。 key_len的值使您能够确定MySQL实际使用的多部分密钥的多少部分。 如果键列指NULL，则len_len列也指NULL。
ref	ref	ref列显示哪些列或常量与键列中指定的索引进行比较，以从表中选择行。  如果该值是func，则使用的值是某个函数的结果。 要查看哪个函数，请在EXPLAIN之后使用SHOW WARNINGS来查看扩展的EXPLAIN输出。 该函数实际上可能是一个运算符，如算术运算符。  （目前我自己测试的只有const 如果有知道怎么更改这个值的请直接留言）
rows	行数
filtered	过滤行数百分比	过滤的列表示将由表条件过滤的表行的估计百分比。 也就是说，行显示了检查的估计行数，行×过滤/ 100显示了将与之前的表连接的行数。
Extra	None	Extra其他 其他值比如  ：Using where  ；Using temporary; Using filesort; Using join buffer (Block Nested Loop)

 4. explain 拓展

  4.1select_type 结果解析

参考:  https://yq.aliyun.com/articles/61934

未完

列名	解释	示列
SIMPLE	简单查询，系统不做任何优化	EXPLAIN SELECT * FROM information_schema.`PROCESSLIST`;
PRIMARY	查询最外层表 select_type 为 primary	EXPLAIN SELECT * FROM (  SELECT * FROM information_schema.`PROCESSLIST`  UNION ALL  SELECT * FROM information_schema.`PROCESSLIST` ) AS t
UNION	关联表，列子中 p2 , p3 为关联表	EXPLAIN SELECT ID FROM information_schema.`PROCESSLIST` AS p1 UNION ALL SELECT ID FROM information_schema.`PROCESSLIST` AS p2 UNION ALL SELECT ID FROM information_schema.`PROCESSLIST` AS p3
DEPENDENT UNION	表示union或union all联合而成的单位查询受外部影响	EXPLAIN SELECT * FROM information_schema.`PROCESSLIST` WHERE ID in( SELECT ID FROM information_schema.`PROCESSLIST` AS p1 UNION ALL SELECT ID FROM information_schema.`PROCESSLIST` AS p2 UNION ALL SELECT ID FROM information_schema.`PROCESSLIST` AS p3 )
UNION RESULT	联合查询结果集	无
SUBQUERY	none	无
DEPENDENT SUBQUERY	none	无
DERIVED	派生表	EXPLAIN SELECT * FROM ( SELECT * FROM information_schema.`PROCESSLIST` as p1 ) as p2
MATERIALIZED	Materialized  查询	无
UNCACHEABLE SUBQUERY	无法缓存结果的子查询，必须对外部查询	无
UNCACHEABLE UNION	第二个或更高版本在属于不可缓存子查询的UNION中选择	无

 4.2 type 值解释

完成列子需要创建表

CREATE TABLE `explaindemo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` varchar(255) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `b_index` (`b`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

字段名称	解释	列子
system	该表只有一行（=系统表）。 这是const连接类型的特例。	无
const	该表至多有一个匹配的行，在查询开始时读取。 由于只有一行，因此该行中列的值可以被优化器的其余部分视为常量。 const表非常快，因为它们只读一次。  当您将PRIMARY KEY或UNIQUE索引的所有部分与常量值进行比较时，将使用const。 在以下查询中，可以将explaindemo用作常量表：	EXPLAIN SELECT * FROM explaindemo WHERE b = 1;
eq_ref	从这个表读取一行，对于前面的表中的每一行的组合。 除了系统和常量类型，这是最好的连接类型。 它在索引的所有部分被连接使用时使用，并且索引是PRIMARY KEY或UNIQUE NOT NULL索引。	EXPLAIN SELECT * FROM explaindemo d1 LEFT JOIN explaindemo d2 ON d1.b = d2.b
ref	所有具有匹配索引值的行都从这个表中读取，用于以前表中的每个行的组合。 如果连接仅使用键的最左边的前缀，或者键不是PRIMARY KEY或UNIQUE索引（换句话说，如果连接不能根据键值选择单个行），则使用ref。 如果使用的键只匹配几行，这是一个很好的连接类型。	EXPLAIN SELECT * FROM explaindemo d1 WHERE d1.c = 'query'
full_text	使用了 full_text索引	无
ref_or_null	这种连接类型与ref类似，但是除此之外，MySQL还对包含NULL值的行进行额外的搜索。 这种连接类型优化最常用于解析子查询。 在以下示例中，MySQL可以使用ref_or_null连接来处理ref_table	没有实现此列子
index_merge	索引合并， 扫描单个表索引并合并，不能应用在多表中。	没有实现此列子
uniqe_subquery	这种类型替换了以下形式的一些IN子查询的eq_ref unique_subquery只是一个索引查找函数，它可以完全替代子查询以提高效率。	没有实现此列子
range	只有在给定范围内的行才能被检索，使用索引来选择行。 输出行中的关键列指示使用哪个索引。 key_len包含使用的最长密钥部分。 这个类型的ref列是NULL。 范围可以用于使用任何=，<>，>，> =，<，<=，IS NULL，<=>，BETWEEN或IN（）运算符将键列与常量进行比较：	EXPLAIN SELECT * FROM explaindemo d1 WHERE d1.c LIKE '所有%'
index	索引连接类型与ALL相同，只是索引树被扫描。 这发生在两个方面：  如果索引是查询的覆盖索引，并且可用于满足表中所需的所有数据，则只扫描索引树。 在这种情况下，额外列说使用索引。 仅索引扫描通常比ALL快，因为索引的大小通常小于表数据。     全表扫描使用索引中的读取来按索引顺序查找数据行。 使用索引不会出现在Extra列中。
all	全表扫描	EXPLAIN SELECT * FROM explaindemo d1 WHERE    d1.b in( all expression);