MySQL :: MySQL 8.2 Reference Manual :: 8.8.2 EXPLAIN Output Format
explain命令提供了mysql数据库如何执行SQL语句的信息，可以跟 SELECT, DELETE, INSERT, REPLACE, UPDATE, 和 TABLE一起使用。
explain命令可能输出多行，每行涉及一个表 。
先来看一个实际的执行，假设执行explain select * from test_table where type='register';输出如下：
explain命令输出的列如下：
Table 8.1 EXPLAIN Output Columns
| Column | JSON Name | Meaning |
|---|---|---|
| id | select_id |
The SELECT identifier |
| select_type | None | The SELECT type |
| table | table_name |
The table for the output row |
| partitions | partitions |
The matching partitions |
| type | access_type |
The join type |
| possible_keys | possible_keys |
The possible indexes to choose |
| key | key |
The index actually chosen |
| key_len | key_length |
The length of the chosen key |
| ref | ref |
The columns compared to the index |
| rows | rows |
Estimate of rows to be examined |
| filtered | filtered |
Percentage of rows filtered by table condition |
| Extra | None | Additional information |
id：select的标识符。- select_type：select的类型，取值如下：
elect_type Value |
JSON Name | Meaning |
|---|---|---|
SIMPLE |
None | Simple SELECT (not using UNION or subqueries) |
PRIMARY |
None | Outermost SELECT |
| UNION | None | Second or later SELECT statement in a UNION |
DEPENDENT UNION |
dependent (true) |
Second or later SELECT statement in a UNION, dependent on outer query |
UNION RESULT |
union_result |
Result of a UNION. |
| SUBQUERY | None | First SELECT in subquery |
DEPENDENT SUBQUERY |
dependent (true) |
First SELECT in subquery, dependent on outer query |
DERIVED |
None | Derived table |
DEPENDENT DERIVED |
dependent (true) |
Derived table dependent on another table |
MATERIALIZED |
materialized_from_subquery |
Materialized subquery |
UNCACHEABLE SUBQUERY |
cacheable (false) |
A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query |
UNCACHEABLE UNION |
cacheable (false) |
The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY) |
table：这一行的输出涉及的表名 。partitions：查询从哪个分区匹配到的记录。如果不是分区表，那么该返回的值为NULL。type：join 类型。各种类型的解释请参考https://dev.mysql.com/doc/refman/8.2/en/explain-output.html#explain-join-types- possible_keys：一个索引列表，mysql可以从其中选择索引，来找到要查询的行。
key：mysql最终决定使用的索引。key_len：mysql最终决定使用的索引的长度。ref：使用key列指定的索引，找到要查询的记录，用哪些列或者常数跟索引进行比较。rows：mysql认为它找到要查找的记录所要遍历的行数。filtered：用表的条件过滤表的行数据的百分比，最大值是100。Extra：包含了mysql如何解析查询的额外信息。各种取值请参考：MySQL :: MySQL 8.2 Reference Manual :: 8.8.2 EXPLAIN Output Format
