MySQL中EXPLAIN解释下令的介绍（附示例）

本篇文章给大家带来的内容是对于MySQL中EXPLAIN解释下令的介绍（附示例），有一定的参照 价值，有需要的伴侣可以参照 一下，但愿对你有所帮忙。

本篇文章给大家带来的内容是对于MySQL中EXPLAIN解释下令的介绍（附示例），有一定的参照 价值，有需要的伴侣可以参照 一下，但愿对你有所帮忙。

1 EXPLAIN概念

EXPLAIN会向我们供给一些MySQL是施行sql的信息：

1. EXPLAIN可以解释注明 SELECT, DELETE, INSERT, REPLACE, and UPDATE 等语句.
2. 当EXPLAIN与可解释的语句一起运用时，mysql会显示一些来自于优化器的对于sql施行规划的信息。即mysql解释它是怎样处置这些语句的，和表之间是怎样连贯的。想猎取更多对于EXPLAIN怎样猎取施行规划信息的。
3. 当EXPLAIN背面是一个会话的connection_id 而不是一个可施行的语句时，它会展现会话的信息。
4. 关于SELECT语句，EXPLAIN会发生额外的施行规划信息，这些信息可以用SHOW WARNINGS显示出来。
5. EXPLAIN关于检查设计分区表的查询时非常实用。
6. FORMAT选项可以用于选中输出格局，要是没有配置FORMAT选项，默许已表格情势输出。JSON 选项让信息已json格局展现。

2 EXPLAIN 输出列信息

EXPLAIN输出的字段信息
首先列:列名, 第二列:FORMAT = JSON时输出中显示的等效属性名称 ,第三列：字段含义

Column	JSON Name	Meaning
id	select_id	select标识号
select_type	None	select类型
table	table_name	这一行数据是对于哪张表的
partitions	partitions	匹配的分区，关于未分区表，该值为空
type	access_type	运用的连贯种别,有无运用索引
possible_keys	possible_keys	MySQL能运用哪个索引在该表中寻到行
key	key	MySQL现实决议运用的键（索引）
key_len	key_length	MySQL决议运用的键长度。要是键是NULL，长度为NULL
ref	ref	与索引关联的列
rows	rows	mysql以为施行sql时必需被校验的行数
filtered	filtered	表示此查询前提所过滤的数据的百分比
Extra	None	附加信息

2.1 id

SELECT标识符。SELECT在查询中的序列号，可认为空。

2.2 select_type

SELECT类型，所有类型鄙人表中展现，JSON格局的EXPLAIN将SELECT类型公示为query_block的属性，除非它是SIMPLE或PRIMARY。 JSON名称(不适用为None)也显示在表中。

select_type Value	JSON Name	Meaning
SIMPLE	None	简略SELECT(不运用UNION或子查询等)
PRIMARY	None	嵌套查询时最外层的查询
UNION	None	UNION中的第二个或背面的SELECT语句
DEPENDENT UNION	dependent (true)	UNION中的第二个或今后的SELECT语句，取决于外部查询
UNION RESULT	union_result	UNION的效果
SUBQUERY	None	子查询中的首先个选中
DEPENDENT SUBQUERY	dependent (true)	子查询中的首先个选中，取决于外部查询
DERIVED	None	派生表（子查询中发生的暂时表）
MATERIALIZED	materialized_from_subquery	物化子查询
UNCACHEABLE SUBQUERY	cacheable (false)	没法缓存效果的子查询，必需对外部查询的每一行进行从新盘算
UNCACHEABLE UNION	cacheable (false)	UNION中属于不成缓存子查询的第二个或今后的选中(请参 UNCACHEABLE SUBQUERY)

表信息（背面演示用）：

mysql> show create table t_a;
------+
| t_a   | CREATE TABLE `t_a` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `age` int(20) DEFAULT NULL,
  `code` int(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_code` (`code`),
  KEY `age_key` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+-----------------------------------
------+
1 row in set (0.03 sec)

SIMPLE：简略SELECT(不运用UNION或子查询等)

mysql> explain select * from t_a where id =1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_a   | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.03 sec)

PRIMARY：嵌套查询时最外层的查询

mysql> explain select * from t_a where num >(select num from t_a where id = 3);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
|  1 | PRIMARY     | t_a   | NULL       | range | num_key       | num_key | 5       | NULL  |    6 |   100.00 | Using where; Using index |
|  2 | SUBQUERY    | t_a   | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL                     |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
2 rows in set, 1 warning (0.03 sec)

UNION：UNION中的第二个或背面的SELECT语句

mysql> explain select * from t_a where id =9 union all select * from t_a;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | t_a   | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL        |
|  2 | UNION       | t_a   | NULL       | index | NULL          | num_key | 5       | NULL  |    9 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.04 sec)

DEPENDENT UNION：UNION中的第二个或今后的SELECT语句，取决于外部查询

mysql> explain select * from t_a where id in (select id from t_a where id >8 union all select id from t_a where id =5);
+----+--------------------+-------+------------+--------+---------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type        | table | partitions | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra                    |
+----+--------------------+-------+------------+--------+---------------+---------+---------+-------+------+----------+--------------------------+
|  1 | PRIMARY            | t_a   | NULL       | index  | NULL          | num_key | 5       | NULL  |    9 |   100.00 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | t_a   | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | func  |    1 |   100.00 | Using where; Using index |
|  3 | DEPENDENT UNION    | t_a   | NULL       | const  | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | Using index              |
+----+--------------------+-------+------------+--------+---------------+---------+---------+-------+------+----------+--------------------------+
3 rows in set, 1 warning (0.08 sec)

UNION RESULT：UNION的效果

mysql> explain select num from t_a where id = 3 union select num from t_a where id =4;
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra           |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
|  1 | PRIMARY      | t_a        | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL            |
|  2 | UNION        | t_a        | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL            |
| NULL | UNION RESULT |  | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
3 rows in set, 1 warning (0.03 sec)

SUBQUERY：子查询中的首先个选中

mysql> explain select * from t_a where num >(select num from t_a where id = 3);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
|  1 | PRIMARY     | t_a   | NULL       | range | num_key       | num_key | 5       | NULL  |    6 |   100.00 | Using where; Using index |
|  2 | SUBQUERY    | t_a   | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL                     |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
2 rows in set, 1 warning (0.03 sec)

DEPENDENT SUBQUERY：子查询中的首先个选中，取决于外部查询

mysql> explain select * from t_a where num in(select num from t_a where id = 3 union select num from t_a where id =4);
+----+--------------------+------------+------------+-------+-----------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type        | table      | partitions | type  | possible_keys   | key     | key_len | ref   | rows | filtered | Extra                    |
+----+--------------------+------------+------------+-------+-----------------+---------+---------+-------+------+----------+--------------------------+
|  1 | PRIMARY            | t_a        | NULL       | index | NULL            | num_key | 5       | NULL  |    9 |   100.00 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | t_a        | NULL       | const | PRIMARY,num_key | PRIMARY | 8       | const |    1 |   100.00 | NULL                     |
|  3 | DEPENDENT UNION    | t_a        | NULL       | const | PRIMARY,num_key | PRIMARY | 8       | const |    1 |   100.00 | NULL                     |
| NULL | UNION RESULT       |  | NULL       | ALL   | NULL            | NULL    | NULL    | NULL  | NULL |     NULL | Using temporary          |
+----+--------------------+------------+------------+-------+-----------------+---------+---------+-------+------+----------+--------------------------+
4 rows in set, 1 warning (0.12 sec)

DERIVED：派生表（子查询中发生的暂时表）

mysql> explain select a.id from (select id from t_a where id >8 union all select id from t_a where id =5) a;
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
|  1 | PRIMARY     |  | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |    3 |   100.00 | NULL                     |
|  2 | DERIVED     | t_a        | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL  |    1 |   100.00 | Using where; Using index |
|  3 | UNION       | t_a        | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | Using index              |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
3 rows in set, 1 warning (0.12 sec)

2.3 table

显示这一行的数据是对于哪张表的，有时是真实的表名字，有时也可能是下列几种效果

• : 指id为M,N行效果的并集
• : 该行是指id值为n的行的派生表效果。派生表可能来自例如from子句中的子查询。
• : 该行是指id值为n的行的物化子查询的效果。

2.4 partitions

查询的记载所属于的分区，关于未分区表，该值为NULL。

2.5 type

连贯运用了哪品种别,有无运用索引,常用的类型有：system, const, eq_ref, ref, range, index, ALL（从左到右，机能越来越差），详情查看 EXPLAIN Join Types

NULL：MySQL在优化历程中分解语句，施行时甚至不消拜访表或索引，例如从一个索引列里拔取最小值可以通过独自索引查寻完成

system：这个表（也可能是查询出来的暂时表）只要一行数据 (= system table). 是const中的一个特例

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；

--例子
mysql> explain select * from t_a where id =1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_a   | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.07 sec)

eq_ref：关于前几个表中的每一行组合，从该表中读取一行。除了system和const，这是最佳的连贯类型。当连贯运用索引的所有局部，而且索引是主键或独一非空索引时，将运用它。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;

--例子（t_b为t_a的复制表，表构造雷同）
mysql> explain select * from t_a,t_b where t_a.code=t_b.code;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref           | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
|  1 | SIMPLE      | t_a   | NULL       | ALL    | uk_code       | NULL    | NULL    | NULL          |    9 |   100.00 | NULL  |
|  1 | SIMPLE      | t_b   | NULL       | eq_ref | uk_code       | uk_code | 4       | test.t_a.code |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
2 rows in set, 1 warning (0.03 sec)

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;

--例子（t_b为t_a的复制表，表构造雷同）
mysql> explain select * from t_a,t_b where t_a.age=t_b.age;
+----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref          | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+-------------+
|  1 | SIMPLE      | t_a   | NULL       | ALL  | age_key       | NULL    | NULL    | NULL         |    9 |   100.00 | Using where |
|  1 | SIMPLE      | t_b   | NULL       | ref  | age_key       | age_key | 5       | test.t_a.age |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+-------------+
2 rows in set, 1 warning (0.03 sec)

fulltext：运用FULLTEXT索引施行连贯

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 t_a where t_a.age =3 or t_a.age is null;
+----+-------------+-------+------------+-------------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type        | possible_keys | key     | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------------+---------------+---------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t_a   | NULL       | ref_or_null | age_key       | age_key | 5       | const |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------------+---------------+---------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.03 sec)

index_merge：该联接类型表示运用了索引合并优化办法。在这种状况下，key列包括了运用的索引的清单，key_len包括了运用的索引的最长的关键元素。

SELECT * FROM ref_table
  WHERE idx1=expr1 OR idx2 =expr2;

--例子
mysql> explain select * from t_a where t_a.code =3 or t_a.age = 3;
+----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                                     |
+----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+
|  1 | SIMPLE      | t_a   | NULL       | index_merge | uk_code,age_key | uk_code,age_key | 4,5     | NULL |    2 |   100.00 | Using union(uk_code,age_key); Using where |
+----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+
1 row in set, 1 warning (0.03 sec)

unique_subquery：该类型替代了下面情势的IN子查询的ref：

value IN (SELECT primary_key FROM single_table WHERE some_expr)
unique_subquery是一个索引查寻函数，可以完全替代子查询，效率更高。

index_subquery：该联接类型相似于unique_subquery。可以替代IN子查询，但只适合以下情势的子查询中的非独一索引：

value IN (SELECT key_column FROM single_table WHERE some_expr)

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

mysql> explain select * from t_a where id > 8;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_a   | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.03 sec)

index：该联接类型与ALL雷同，除了只要索引树被扫描。这平常比ALL快，由于索引文件平常比数据文件小。当查询只运用作为单索引一局部的列时，MySQL可以运用该联接类型。

ALL：关于每个来自于先前的表的行组合，进行完备的表扫描。要是表是首先个没标志const的表，这平常欠好，而且平常在它状况下很差。平常可以添加更多的索引而不要运用ALL，使得行能基于前面的表中的常数值或列值被检索出。

2.6 possible_keys

possible_keys列指出MySQL能运用哪个索引在该表中寻到行。注意，该列完全独立于EXPLAIN输出所示的表的顺序。这意味着在possible_keys中的某些键现实上不克不及按生成的表顺序运用。

要是该列是NULL，则没有相干的索引。在这种状况下，可以通过检查WHERE子句看可否它援用某些列或适合索引的列来提高你的查询机能。要是是这样，制造一个恰当的索引而且再次用EXPLAIN检查查询

2.7 key

key列显示MySQL现实决议运用的键（索引）。要是没有选中索引，键是NULL。要想强迫MySQL运用或忽视possible_keys列中的索引，在查询中运用FORCE INDEX、USE INDEX或者IGNORE INDEX。

2.8 key_len

key_len列显示MySQL决议运用的键长度。要是键是NULL，则长度为NULL。
运用的索引的长度。在不亏损精准性的状况下，长度越短越好

2.9 ref

ref列显示运用哪个列或常数与key一起从表当选择行。

2.10 rows

rows列显示MySQL以为它施行查询时必需检查的行数。

2.11 Extra

该列包括MySQL解决查询的细致信息,下面细致.

1. Distinct：一旦MYSQL寻到了与行相结合匹配的行，就不再搜寻了
2. Not exists：MYSQL优化了LEFT JOIN，一旦它寻到了匹配LEFT JOIN规范的行，就不再搜寻了
3. Range checked for each：没有寻到志愿的索引，因而关于从前面表中来的每一个行组合，MYSQL检查运用哪个索引，并用它来从表中返回行。这是运用索引的最慢的连贯之一
4. Using filesort：看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发明怎样对返回的行排序。它依据连贯类型以及存储排序键值和匹配前提的全部行的行指针来排序全部行
5. Using index：列数据是从仅仅运用了索引中的信息而没有读取现实的动作的表返回的，这产生在对表的全部的要求列都是统一个索引的局部的时候
6. Using temporary：看到这个的时候，查询需要优化了。这里，MYSQL需要新建一个暂时表来存储效果，这平常产生在对不一样的列集进行ORDER BY上，而不是GROUP BY上
7. Using where：运用了WHERE从句来限定哪些行将与下一张表匹配或者是返回给会员。要是不想返回表中的全部行，而且连贯类型ALL或index，这就会产生，或者是查询有题目

【