内容：介绍MySQL中的EXPLAIN和DESCRIBE

事实上，DESCRIBE和EXPLAIN是同义词。但是，DESCRIBE通常用于表结构信息，而EXPLAIN用户获取“查询执行计划”（即MySQL如何执行查询）。
所以，本文内容分为两部分：一部分介绍”如何获取表结构信息”；另外一部分介绍”如何获取执行计划信息”；

EXPLAIN功能有限，并不会总说出真相。但可以很好的解释查询是如何执行的。

相关引用内容：

DESCRIBE和EXPLAIN语法

鉴于二者的角色不同，所以将其区分开解释，但是要记住他们两个是可以互相替换的。下面是语法格式:

-- DESC

{DESCRIBE | DESC}
    tbl_name [col_name | wild]

-- EXPLAIN

{EXPLAIN}
    [explain_type]
    {explainable_stmt | FOR CONNECTION connection_id}

explain_type: {
    EXTENDED
  | PARTITIONS
  | FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
}

explainable_stmt: {
    SELECT statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

DESCRIBE：如何获取表结构信息

DESCRIBE主要用于显示TABLE的信息，看如下Demo:

mysql> DESCRIBE City;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | NO   |     |         |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | NO   |     | 0       |                |
+------------+----------+------+-----+---------+----------------+

DESCRIBE也可以用于显示VIEW的信息。

默认情况下，DESCRIBE显示TABLE中所有列的信息。
如果给出col_name(列名)则仅显示指定列的信息。
wild是匹配字符串，它可以包含SQL中的`％’和`_’通配符。如果给出wild，则仅显示wild的列名。如果wild中不包含空格或其他特殊字符，wild就不需要使用双引号引起来。

DESCRIBE实际是SHOW COLUMNS的简写。SHOW COLUMNS提供了更多关于列的信息。关于SHOW COLUMN的内容，查看: 《SHOW语法》

EXPLAIN：如何获取执行计划

EXPLAIN提供了有关如何执行语句的信息：

• EXPLAIN只对SELECT, DELETE, INSERT, REPLACE, UPDATE有效。
• 当EXPLAIN解释SQL的时候，MySQL会显示从优化器获取的语句执行计划。执行计划解释了MySQL如何处理语句，以及表是如何连接的以及以什么顺序。
• 如果EXPLAIN和FOR CONNECTION connection_id一起使用，将显示在命名连接中执行的语句的执行计划。获取命名连接的执行计划: https://dev.mysql.com/doc/refman/5.7/en/explain-for-connection.html
• 对于SELECT语句，EXPLAIN还给出了附加的执行计划信息。注意：在旧版本的MySQL中，附加信息的显示需要使用EXPLAIN EXTENDED，但是新版本中的EXTENDED是为了兼容，以后会删除这个关键字。关于扩展的EXPLAIN的输出格式：https://dev.mysql.com/doc/refman/5.7/en/explain-extended.html
• EXPLAIN也可用于检查涉及分区表的查询。注意：和EXTENDED一样，也是为了兼容，以后会删除这个关键字。关于“获取分区信息”查看：https://dev.mysql.com/doc/refman/5.7/en/partitioning-info.html
• FORMAT选项用于指定输出格式。format_name的取值：TRADITIONAL，以表格格式输出（默认格式）；JOSN，以JOSN格式输出。
• MySQL5.6之前，如果要解释的语句中包含子查询，那么会执行这个子查询，然后完成对外层查询优化。MySQL5.6之后取消了这个限制。

“优化器跟踪”有时可以提供与EXPLAIN的信息互补的信息。但是，“优化器跟踪”格式和内容可能会在版本之间发生更改。详情查看：https://dev.mysql.com/doc/internals/en/optimizer-tracing.html

如果你相信某个问题是由于索引没有使用而产生的，那么运行ANALYZE TABLE来更新表统计信息（例如键的基数），这能影响优化器所做的选择。详情查看：https://dev.mysql.com/doc/refman/5.7/en/analyze-table.html

EXPLAIN的一些局限性

EXPLAIN中不会显示存储过程、触发器、UDF对查询的影响。
不支持对存储过程的分析。
查询计划的信息也不够全面。
查询执行中的特定优化也不会显示出来。
也不会区分具有相同名字的事物。如：filesort可能表示临时文件或者内存排序。Using temporary可能是内存临时表，也可能是磁盘临时表。

EXPLAIN的输出格式

EXPLAIN返回SELECT中使用的每张表的信息。输出的表的顺序，按照MySQL读取的顺序显示出来。MySQL使用“嵌套循环连接“(nested-loop join)方法连接所有的表，意思是说：MySQL从第一张表中读取一行，然后依次在第二张表中查找匹配的行，第三张……，依次类推。当所有表处理完之后，MySQL输出选择的行并通过表列表回溯，直到找到有更多匹配行的表。从该表中读取下一行，并且继续下一个表。

EXPLAIN的输出包括分区信息。此外，对于SELECT语句，EXPLAIN会生成扩展信息，可以在EXPLAIN之后，使用SHOW WARNINGS显示，详细内容查看：https://dev.mysql.com/doc/refman/5.7/en/explain-extended.html

EXPLAIN输出的列

输出格式又两种，但是这两种的格式输出的”列名”有所不同，所以在下面中开头为TRADITIONAL格式的”列名”，而括号中JOSN name为JSON格式的”列名”。

以下是对输出列的详细说明：

id (JSON name: select_id)

select_type (JSON name: none)

• SIMPLE（JSON name: None）：简单SELECT，未使用UNION或子查询。
• PRIMARY（JSON name: None）：如果查询包含了复杂的子部分，则PRIMARY标识的是最外层的SELECT。
• UNION（JSON name: None）：表示在UNION中第二或之后SELECT。
• DEPENDENT UNION（JSON name: dependent (true)）：在UNION中第二或之后SELECT，依赖于外层查询。
• UNION RESULT（JSON name: union_result）：UNION的结果。UNION结果会输出到一个临时表中，然后MySQL将临时表中的数据读出来。
• SUBQUERY（JSON name: None）：子查询中的第一个SELECT。
• DEPENDENT SUBQUERY（JSON name: dependent (true)）：在子查询中第一个SELECT，依赖于外层查询。
• DERIVED（JSON name: None）：表示是FROM中的子查询。MySQL将查询结果会放在一个临时表中，这个表称为“派生表”。
• MATERIALIZED（JSON name: materialized_from_subquery）：物化子查询。
• UNCACHEABLE SUBQUERY（JSON name: cacheable (false)）：不能缓存结果的子查询，并且为外层查询的每一行进行重新评估。比如使用了RNAD()。注意这里说的缓存并不是查询缓存。
• UNCACHEABLE UNION（JSON name: cacheable (false)）：UNION中第二个或之后的查询，属于不可缓存的子查询。

table (JSON name: table_name)

• <unionM,N>：指id=M和id=N的并集。
• <derivedN>：指id=N的派生表的结果。当FROM中有子查询时，可能会导致这种情况。
• <subqueryN>：该行是指id值为N的行的物化子查询的结果。https://dev.mysql.com/doc/refman/5.7/en/subquery-materialization.html

partitions (JSON name: partitions)

type (JSON name: access_type)

possible_keys (JSON name: possible_keys)

key (JSON name: key)

key_len (JSON name: key_length)

ref (JSON name: ref)：

rows (JSON name: rows)

filtered (JSON name: filtered)

Extra (JSON name: none)

EXPLAIN的Join类型

JOIN类型有如下几种：

system

const

SELECT * FROM tbl_name WHERE primary_key=1;

SELECT * FROM tbl_name
  WHERE primary_key_part1=1 AND primary_key_part2=2;

eq_ref

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;

ref

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;

fulltext

ref_or_null

SELECT * FROM ref_table
  WHERE key_column=expr OR key_column IS NULL;

index_merge

unique_subquery

index_subquery

range

SELECT * FROM tbl_name
  WHERE key_column = 10;

SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name
  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

index

• 如果索引是查询的覆盖索引，并且可以用于满足表中所需的所有数据，则仅扫描索引。在这种情况下，Extra显示Using index。”仅索引扫描”通常比ALL更快，因为索引的大小通常小于表数据。
• 使用从索引读取来执行全表扫描，以按索引顺序查找数据行。 Using index不会显示在Extra中。

ALL

EXPLAIN的附加信息

Extra中显示的是MySQL解析查询的附加信息。

如果你希望你的查询尽可能的块一些，那你要留意Extra中是否使用了Using filesort、Using temporary，或者JOSN格式中的using_filesort、using_temporary_table是否为true。

Child of ‘table’ pushed join@1 (JSON: message text)

const row not found (JSON property: const_row_not_found)

Deleting all rows (JSON property: message)

Distinct (JSON property: distinct)

FirstMatch(tbl_name) (JSON property: first_match)

Full scan on NULL key (JSON property: message)

Impossible HAVING (JSON property: message)

Impossible WHERE (JSON property: message)

Impossible WHERE noticed after reading const tables (JSON property: message)

LooseScan(m..n) (JSON property: message)

No matching min/max row (JSON property: message)

no matching row in const table (JSON property: message)

No matching rows after partition pruning (JSON property: message)

No tables used (JSON property: message)

Not exists (JSON property: message)

SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id
  WHERE t2.id IS NULL;

Plan isn’t ready yet (JSON property: none)

Range checked for each record (index map: N) (JSON property: message)

Scanned N databases (JSON property: message)

Select tables optimized away (JSON property: message)

SELECT MIN(c1), MIN(c2) FROM t1;

SELECT MIN(c2) FROM t1 WHERE c1 <= 10;

SELECT MIN(c2) FROM t1 WHERE c1 = 10;

Skip_open_table, Open_frm_only, Open_full_table (JSON property: message)

Start temporary, End temporary (JSON property: message)

unique row not found (JSON property: message)

Using filesort (JSON property: using_filesort)

Using index (JSON property: using_index)

Using index condition (JSON property: using_index_condition)

Using index for group-by (JSON property: using_index_for_group_by)

Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access) (JSON property: using_join_buffer)

Using MRR (JSON property: message)

Using sort_union(…), Using union(…), Using intersect(…) (JSON property: message)

Using temporary (JSON property: using_temporary_table)

Using where (JSON property: attached_condition)

Using where with pushed condition (JSON property: message)

Zero limit (JSON property: message)

EXPLAIN的输出解释

通过使用EXPLAIN输出的行列中的值的乘积，可以很好地表明连接的好坏。这应该大致告诉你MySQL必须检查多少行才能执行查询。如果使用max_join_size系统变量限制查询，则此产生的行还用于确定要执行的多表SELECT语句以及要中止哪一个。https://dev.mysql.com/doc/refman/5.7/en/server-configuration.html

以下示例显示了如何根据EXPLAIN提供的信息逐步优化多表连接：

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
               tt.ProjectReference, tt.EstimatedShipDate,
               tt.ActualShipDate, tt.ClientID,
               tt.ServiceCodes, tt.RepetitiveID,
               tt.CurrentProcess, tt.CurrentDPPerson,
               tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
               et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
          AND tt.ActualPC = et.EMPLOYID
          AND tt.AssignedPC = et_1.EMPLOYID
          AND tt.ClientID = do.CUSTNMBR;

对于此示例，进行以下假设：

最初，在执行任何优化之前，EXPLAIN语句产生以下信息：
table type possible_keys key key_len ref rows Extra
et ALL PRIMARY NULL NULL NULL 74
do ALL PRIMARY NULL NULL NULL 2135
et_1 ALL PRIMARY NULL NULL NULL 74
tt ALL AssignedPC, NULL NULL NULL 3872

因为每个表的类型都是ALL，所以这个输出表明MySQL正在生成所有表的笛卡儿乘积;也就是每行的组合。这需要相当长的时间，因为必须检查每个表中的行数的乘积。就目前情况而言，该产品为74×2135×74×3872 = 45,268,558,720行。如果table较大，您只能想象需要多长时间。

这里的一个问题是，如果MySQL被声明为相同的类型和大小，那么MySQL可以更有效地使用列上的索引。在这种情况下，VARCHAR和CHAR被认为是相同的，如果它们被声明为相同的大小。 tt.ActualPC被声明为CHAR（10），et.EMPLOYID是CHAR（15），因此存在长度不匹配。

要修复列长度之间的差异，请使用ALTER TABLE将ActualPC从10个字符延长到15个字符：

ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

现在tt.ActualPC和et.EMPLOYID都是VARCHAR（15）。再次执行EXPLAIN语句产生此结果：
table type possible_keys key key_len ref rows Extra
tt ALL AssignedPC, NULL NULL NULL 3872 Using

do ALL PRIMARY NULL NULL NULL 2135

et_1 ALL PRIMARY NULL NULL NULL 74

et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1

这不是完美的，但是更好：行值的乘积小于74。这个版本在几秒钟内执行。

可以进行第二次更改以消除tt.AssignedPC = et_1.EMPLOYID和tt.ClientID = do.CUSTNMBR比较的列长度不匹配：

ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
                      MODIFY ClientID   VARCHAR(15);

在此修改后，EXPLAIN生成如下所示的输出：
table type possible_keys key key_len ref rows Extra
et ALL PRIMARY NULL NULL NULL 74
tt ref AssignedPC, ActualPC 15 et.EMPLOYID 52 Using

et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1

do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1

在这一点上，查询几乎尽可能优化。剩下的问题是，默认情况下，MySQL假定tt.ActualPC列中的值均匀分布，tt表不是这样。幸运的是，很容易告诉MySQL来分析key分发：

ANALYZE TABLE tt;

使用附加索引信息，连接是完美的，EXPLAIN产生此结果：
table type possible_keys key key_len ref rows Extra
tt ALL AssignedPC NULL NULL NULL 3872 Using

et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1

et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1

do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1

来自EXPLAIN的输出中的rows列是MySQL连接优化器的一个受过教育的猜测。通过将行乘积与查询返回的实际行数进行比较，检查数字是否接近真实。如果数字完全不同，您可以通过在SELECT语句中使用STRAIGHT_JOIN来获得更好的性能，并尝试在FROM子句中以不同的顺序列出表。 （但是，STRAIGHT_JOIN可能会阻止使用索引，因为它禁用半连接转换。https://dev.mysql.com/doc/refman/5.7/en/semi-joins.html

在某些情况下，可以执行EXPLAIN SELECT与子查询一起修改数据的语句;https://dev.mysql.com/doc/refman/5.7/en/derived-tables.html