1、EXPLAIN介绍
EXPLAIN语句提供MySQL如何执行语句的信息。EXPLAIN返回SELECT语句中使用的每个表的信息并列出一行运行数据。它是按照MySQL在处理语句时读取表的顺序列出并输出到一个表格中。
2、查询示例
2.1、【explain + 表名】显示的是这个表的表结构。
2.2、【explain + select语句】显示的是这个语句执行时的信息。
3、explain 列详细介绍
3.1、explain 表
| 列名 | JSON 名称 | 含义 |
id |
select_id |
SELECT 标识符 |
select_type |
None |
SELECT 类型 |
table |
table_name | 输出行的表 |
partitions |
partitions | 匹配的分区 |
type |
access_type | 联接类型 |
possible_keys |
possible_keys | 可能选择的索引 |
key |
key | 实际选择的索引 |
key_len |
key_length | 所选键的长度 |
ref |
ref | 列与索引的比较 |
rows |
rows | 估计要检查的行数 |
filtered |
filtered | 按表条件筛选的行百分比 |
Extra |
None | 其他信息 |
3.2、列名：id
id列是一个有顺序的编号，表示查询中每个SELECT子句的唯一标识符。在复杂查询中，可能会有多个SELECT语句，id值用于确定这些语句的执行顺序。一般来说，id值越大，执行优先级越高，越先执行；如果id值相同，则按照从上到下的顺序执行；如果id值为NULL，则表示这是一个结果集，通常来自UNION操作。
3.3、列名：select_type
SELECT 的类型，可以是下表中所示的任何类型。
select_type 类型 |
JSON 名称
|
含义 |
|---|---|---|
SIMPLE |
None
|
简单SELECT：不涉及UNION或子查询的简单查询。 |
PRIMARY |
None
|
最外层SELECT：查询中最外层的SELECT语句。在包含子查询或UNION的复杂查询中，最外层的操作会被标记为此类型。 |
UNION |
None
|
UNION中的非首条SELECT：在UNION操作中，除第一个SELECT之外的后续SELECT语句。 |
DEPENDENT UNION |
dependent (true) |
依赖外部查询的UNION：UNION中第二个及以后的SELECT语句，并且依赖于外部查询的结果。性能警告：通常意味着效率较低，因为要对外部查询的每一行都执行一次。 |
UNION RESULT |
union_result |
UNION的结果集：表示从UNION查询的匿名临时表中获取结果的SELECT。 |
SUBQUERY |
None
|
（非相关）子查询：子查询中的第一个SELECT，并且不依赖于外部查询的结果。通常子查询可以独立执行，效率尚可。 |
DEPENDENT SUBQUERY |
dependent (true) |
依赖外部查询的子查询：子查询中的第一个SELECT，并且依赖于外部查询的结果。性能警告：必须为外部查询的每一行都执行一次，效率极低，是需要优化的重点。 |
DERIVED |
None
|
派生表：在FROM子句中包含的子查询（也称为派生表），MySQL会将其结果物化为一个临时表供外部查询使用。性能提示：在MySQL 8.0中，优化器会尝试合并派生表以提升性能。 |
DEPENDENT DERIVED |
dependent (true) |
依赖外部查询的派生表：派生表依赖于另一个表（通常是外部查询）的数据。 |
MATERIALIZED |
materialized_from_subquery |
物化子查询：优化器选择将子查询的结果物化（即实际填充到一个临时表中），然后通过连接此临时表来执行查询。这通常用于优化 IN (subquery) 等场景。 |
UNCACHEABLE SUBQUERY |
cacheable (false) |
不可缓存子查询：子查询的结果无法被缓存，必须为外部查询的每一行都重新计算。性能警告：效率非常低，通常因为子查询中包含 volatile 函数（如 RAND(), NOW()）或用户变量。 |
UNCACHEABLE UNION |
cacheable (false) |
不可缓存的UNION：属于不可缓存子查询（UNCACHEABLE SUBQUERY）中的UNION操作。 |
建议：
- 性能友好型：
- SIMPLE, PRIMARY, UNION：通常是高效查询的基础组成部分。
- SUBQUERY (非相关)：可以独立执行一次，效率尚可。
- MATERIALIZED：优化器的一种优化策略，用空间（临时表）换时间，有时能极大提升性能。
- 需要重点关注和优化：
- DEPENDENT SUBQUERY： 头号性能杀手。因为它对外部查询的每一行都会执行一次，如果外部查询返回1万行，这个子查询就会执行1万次。应极力尝试用 JOIN 重写。
- UNCACHEABLE SUBQUERY： 另一个性能杀手。同样需要多次执行，且无法缓存结果。
- DEPENDENT UNION： 结合了 DEPENDENT 和 UNION 的双重开销，效率很低。
- DERIVED： 在旧版本（如5.7）中，创建临时表可能带来较大开销。虽然在8.0中得到了优化（派生表合并），但大量数据时仍需注意。
【3.1】和【3.3】中的JSON名称使用【EXPLAIN FORMAT=JSON + 查询语句】时就可以看见，如下图：
3.4、列名：table
输出行所指向的表的名称。这也可以是以下值之一：
-
可以是实际的表名、视图名。
-
可以是
<derivedN>格式，其中N是id值，表示该行访问的是id为N的查询产生的派生表。 -
可以是
<unionM,N>格式，表示该行访问的是id为M和N的查询UNION后产生的临时表。
3.5、列名：partitions
查询将从其中匹配记录的分区。对于非分区表，该值为NULL。
3.6、列名：type
连接类型，它表示了MySQL决定如何查找表中的行。
| 类型 | 含义 | 性能优先级（数字越小性能越好） |
| system | 该表只有一行（等于系统表）。这是const连接类型的一种特殊情况。 | 1 |
| const | 表最多有一个匹配行，在查询开始时读取。因为只有一行，所以该行中列的值可以被优化器的其余部分视为常量。Const表非常快，因为它们只被读取一次。 | 2 |
| eq_ref | 在JOIN查询中，使用主键或唯一索引作为连接条件。对于来自前一个表的每一行组合，从当前表中读取一行。是除 system 和 const 之外最好的连接类型。 | 3 |
| ref | 对于来自前一个表的每个行组合，将从这个表中读取具有匹配索引值的所有行。如果连接只使用键的最左边的前缀，或者键不是PRIMARY key或UNIQUE索引（换句话说，如果连接不能根据键值选择单行），则使用ref。如果所使用的键只匹配几行，则这是一个很好的连接类型。简单来说就是使用非唯一索引进行等值扫描。 | 4 |
| fulltext |
连接是使用FULLTEXT索引执行的。 |
9 |
| ref_or_null | 这种连接类型类似于ref，但是MySQL会对包含NULL值的行进行额外的搜索。这种连接类型优化最常用于解析子查询。 | 7 |
| index_merge | 此连接类型表明使用了索引合并优化。在这种情况下，输出行的键列包含所使用的索引列表，key_len包含所使用索引的最长键部分列表。 | 6（一般情况下） |
| unique_subquery | 这种类型替换了以下形式的一些IN子查询的eq_ref：value IN (SELECT primary_key FROM single_table WHERE some_expr) |
8 |
| index_subquery |
这种连接类型类似于unique_subquery。它取代了IN子查询，但它适用于以下形式的子查询中的非唯一索引： value IN (SELECT key_column FROM single_table WHERE some_expr) |
8 |
| range | 只检索给定范围内的行，使用索引选择行。输出行中的键列指示使用哪个索引。key_len包含使用的最长的键部分。这种类型的ref列为NULL。 |
5 |
| index | 除了扫描索引树之外，索引连接类型与ALL相同。这有两种情况：
1、如果索引是一个覆盖查询的索引，并且可以用来满足表中所需的所有数据，则只扫描索引树。仅索引扫描通常比ALL更快，因为索引的大小通常小于表数据的大小。 2、执行全表扫描时，使用从索引读取数据，按索引顺序查找数据行。使用索引不会出现在Extra列中。 全索引扫描。遍历整个索引树来查找数据。虽然避免了全表扫描，但通常仍然很慢。Extra列如果出现 Using index，则表示是覆盖索引，性能会好很多。 |
10 |
| ALL | 全表扫描。性能最差，通常意味着没有合适的索引可用，必须检查表中的每一行。这是必须要优化的信号。 | 11 |
3.7、列名：possible_keys
显示查询可能会使用哪些索引来查找行。基于查询条件和表结构分析得出。如果此列为 NULL，并不意味着没有索引可用，可能只是需要检查表结构和 WHERE 条件。
3.8、列名：key
显示查询实际决定使用的索引。这是优化器从 possible_keys 中选择的、成本最低的索引。如果为 NULL，则表示优化器决定不使用任何索引。注意： 有可能 key 列显示的索引并不在 possible_keys 列表中，这表明可能使用了覆盖索引。
3.9、列名：key_len
key_len 列表示MySQL决定使用的键的长度。key_len 的值使您能够确定MySQL实际使用索引的哪些部分。如果键列为 NULL，则 key_len 列也为 NULL。由于键存储格式的原因，可以为 NULL 的列的键长度比 NOT NULL 列的键长度大1。
3.10、列名：ref
ref 列显示哪些列或常量与 key 列中指定的索引进行比较，以便从表中选择行。如果值是 func，则使用的值是某个函数的结果。
3.11、列名：rows
rows 列表示MySQL认为执行查询必须检查的行数。对于InnoDB表，这个数字是一个估计值，可能并不总是准确的。
3.12、列名：filtered
筛选的列表示按表条件筛选的表行的估计百分比。最大值是100，这意味着没有对行进行过滤。理想值是100%，表示返回的数据完全符合WHERE条件。数值越低，需要读取的无关数据越多。从100开始递减的值表示过滤量在增加。Rows显示检查的估计行数，Rows × filtered显示与下表连接的行数。例如，如果rows为1000，filtered为50.00(50%)，则要与下表连接的行数为1000 × 50% = 500。
3.13、列名：Extra
这一列包含关于MySQL如何解析查询的附加信息，通常包含了非常关键的优化线索。没有单独的JSON属性对应于Extra列。
| 常见值 | 含义 |
| Using index (覆盖索引) | 查询所需的所有数据都可以在索引中找到，无需回表读取数据行。这是非常好的性能信号。 |
| Using where | 服务器层在存储引擎返回行之后，又使用了WHERE条件进行过滤。如果 type 是 index 或 ALL，出现这个值通常不是好兆头。 |
| Using temporary | MySQL为了执行查询，必须创建一张临时表来保存中间结果。这通常发生在 GROUP BY 和 ORDER BY 子句涉及不同列的查询中。需要优化。 |
| Using filesort | MySQL无法使用索引来完成排序操作，需要在内存或磁盘上进行额外的排序。需要优化，考虑为 ORDER BY 子句添加索引。 |
| Using join buffer (Block Nested Loop) | 表示JOIN操作没有使用索引，需要用到连接缓冲区。需要优化，考虑为JOIN条件添加索引。 |
| Select tables optimized away | 使用了一些聚合函数（如 MIN()/MAX()）来访问某个索引列，优化器知道可以直接从索引中获取值而无需遍历整个索引。 |
其他详细值请查看官方文档：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-extra-information
为了更直观地理解，我们可以将它们放入一个更完整的性能梯队中：
| 性能等级 | 访问类型 (type) |
| 最优 | system > const > eq_ref > ref > range |
| 中等 | index_merge (有时) > ref_or_null > index_subquery > unique_subquery |
| 特殊 | fulltext (为特定场景设计) |
| 需优化 | index (全索引扫描) |
| 必须优化 | ALL (全表扫描) |
建议：
目标： 让你的查询尽量达到 const, eq_ref, ref, range 级别。
看到 index_merge： 不要高兴得太早，检查是否可以通过创建更合适的复合索引来获得更好的性能（通常是 range 或 ref）。
看到 index_subquery / unique_subquery： 考虑能否使用 JOIN 来重写查询，通常 JOIN 的效率更高。
看到 index 或 ALL： 除非表数据量极小，否则这就是需要紧急优化（增加索引或重写查询）的明确信号。
4、参考资料
官网地址：https://dev.mysql.com/doc/refman/8.0/en/explain.html
EXPLAIN参数详情：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
安装最新版本docker)
【23】之泛化：从概念到实践)
)
的解析与应用)
)
