mysql的explain有哪些列

题目详细答案

EXPLAIN命令用于分析 SQL 查询的执行计划，帮助优化查询性能。通过 explain 命令获取 select 语句的执行计划，通过 explain 我们可以知道以下信息：表的读取顺序，数据读取操作的类型，哪些索引可以使用，哪些索引实际使用了，表之间的引用，每张表有多少行被优化器查询等信息。

id

查询的标识符。一个查询中的每个子查询或联合查询都会有一个唯一的id。id相同时，执行顺序由上至下。如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。主要用于区分查询中的不同部分。

select_type

查询的类型，描述 SELECT 的类型。

常见值：

SIMPLE：简单的 SELECT 查询，不包含子查询或 UNION。
PRIMARY：最外层的 SELECT。
UNION：UNION 中的第二个或后续的 SELECT 查询。
DEPENDENT UNION：UNION 中的第二个或后续的 SELECT 查询，依赖于外部查询。
UNION RESULT：UNION 的结果。
SUBQUERY：子查询中的第一个 SELECT。
DEPENDENT SUBQUERY：子查询，依赖于外部查询。
DERIVED：派生表（子查询的结果作为临时表）。

当通过union来连接多个查询结果时，第二个之后的select其select_type为UNION。

当union作为子查询时，其中第二个union的select_type就是DEPENDENT UNION。第一个子查询的select_type则是DEPENDENT SUBQUERY。

table

这一列表示 explain的这一行正在访问哪个表。

1）当 from 子句中有子查询时，如果table列是 <derivenN> 格式，则表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。

2）当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。

partitions

如果该查询是基于分区表的查询，partitions字段会显示查询所访问的分区。

type

这一列表示关联类型或访问类型，即，Mysql决定通过哪种方式查找数据表中的数据。

从优到最差分别为：system > const > eq_ref > ref > range > index > ALL

一般来说，至少需要保证查询达到range级别，最好达到ref级别。

常见值：

类型	解释
system	表只有一行（等于系统表）
const	表最多有一行匹配
eq_ref	对于每个来自前一个表的行，最多一行与之匹配，比如一个订单表的对应另一张订单信息扩充表，扩充表的记录和订单表肯定是 1 对 1 的，就是 eq
ref	对于每个来自前一个表的行，可能有多行与之匹配，如果上面的例子是一对多，那么就是 ref
range	使用索引查找指定范围的行
index	全索引扫描，扫描索引树
ALL	全表扫描

possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）

key

key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

可能出现这种情况，possible_keys有显示列，而key显示NULL的情况，这种情况是因为表中数据不多，Mysql优化器认为查询时走索引对此查询语句帮助不大，从而优化器会选择全表扫描（扫描聚簇索引），而不是走索引来查询。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。

ref

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows

显示MySQL认为它执行查询时检查的行数。多行之间的同组数据相乘可以估算要处理的行数，不同组的相加

filtered

该列是一个百分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）。

Extra

附加信息。

常见值：

Using index：查询使用了覆盖索引（索引包含所有需要的数据）。

Using where：使用了WHERE子句进行过滤。

Using temporary：使用了临时表。

Using filesort：使用了文件排序。

MySQL EXPLAIN 命令详解：读懂执行计划的核心指南

在 MySQL 性能优化中，EXPLAIN 命令是分析查询语句执行计划的核心工具。它能清晰展示 MySQL 优化器如何解析 SQL 语句、选择索引、连接表等关键信息，帮助开发者定位性能瓶颈。本文将系统解析 EXPLAIN 输出的各字段含义，重点解读 type 等核心指标，掌握这些知识是写出高效 SQL 的基础。

一、EXPLAIN 命令的基本用法

EXPLAIN 可用于分析 SELECT 语句的执行计划，使用方式极为简单：在 SELECT 语句前加上 EXPLAIN 关键字即可。

示例：

EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';

执行后，MySQL 会返回一张包含 12 列的表格（不同版本可能略有差异），每一列代表执行计划的一个维度。我们需要重点关注 id、select_type、table、type、key、rows、Extra 等字段。

二、EXPLAIN 输出字段详解

1. id：查询的执行顺序标识

id 表示查询中每个子查询或联合查询的唯一标识符，决定了 SQL 各部分的执行顺序：

• id 相同：表示这些查询是同一组，执行顺序由上至下；
• id 不同：id 值越大，优先级越高，越先执行（通常是子查询）；
• id 有相同也有不同：大 id 先执行，同 id 组内按顺序执行。

示例：

EXPLAIN
SELECT * FROM orders 
WHERE user_id IN (SELECT id FROM users WHERE age > 30);

• 子查询 SELECT id FROM users ... 的 id 更大，会先执行；
• 外层查询 SELECT * FROM orders ... 的 id 较小，后执行。

2. select_type：查询类型

select_type 描述了查询的类型，常见值如下：

类型	含义
SIMPLE	简单查询，不包含子查询或 UNION
PRIMARY	最外层的查询（当存在子查询或 UNION 时）
SUBQUERY	子查询中的第一个 SELECT （不依赖外部查询结果）
DEPENDENT SUBQUERY	依赖外部查询结果的子查询（子查询的执行结果受外层查询影响）
UNION	UNION 中第二个及以后的 SELECT 语句
DEPENDENT UNION	UNION 中第二个及以后的 SELECT ，且依赖外部查询
UNION RESULT	UNION 的结果集（临时表）
DERIVED	派生表（子查询作为临时表，如 FROM 子句中的子查询）

示例：

EXPLAIN
SELECT * FROM (
    SELECT id FROM users WHERE age > 30
) AS derived_t
UNION
SELECT id FROM admins WHERE status = 1;

 

• 子查询 SELECT id FROM users ... 的 select_type 为 DERIVED；
• UNION 后的 SELECT id FROM admins ... 的 select_type 为 UNION；
• 最终结果集的 select_type 为 UNION RESULT。

3. table：当前行访问的表

table 列显示当前行对应的查询涉及的表名，可能出现以下特殊值：

• <derivedN>：表示引用 id=N 的派生表（如子查询生成的临时表）；
• <unionM,N>：表示引用 id=M 和 id=N 的 UNION 结果集。

4. type：访问类型（核心指标）

type 列是 EXPLAIN 中最重要的字段之一，它表示 MySQL 访问表中数据的方式，直接反映查询的效率。从优到差的顺序如下：

（1）system：系统表，最优

表中只有一行数据（如 MySQL 系统表），是 const 类型的特例。

（2）const：常量查询

表中最多只有一行匹配，通常用于主键或唯一索引的等值查询，查询速度极快。
示例：

EXPLAIN SELECT * FROM users WHERE id = 100; -- id 是主键

（3）eq_ref：唯一索引等值匹配

对于前一个表的每一行，当前表只有一行与之匹配，通常用于主键或唯一索引的关联查询（JOIN）。
示例：

-- orders 表的 user_id 关联 users 表的主键 id
EXPLAIN SELECT * FROM orders 
JOIN users ON orders.user_id = users.id;

（4）ref：非唯一索引等值匹配

对于前一个表的每一行，当前表可能有多个行与之匹配，用于非唯一索引的等值查询。
示例：

-- status 是普通索引（非唯一）
EXPLAIN SELECT * FROM orders WHERE status = 'paid';

（5）range：索引范围查询

使用索引查询指定范围的行，如 BETWEEN、IN、>、< 等条件。
示例：

EXPLAIN SELECT * FROM orders WHERE create_time > '2023-01-01';

（6）index：全索引扫描

扫描整个索引树（而非数据行），通常发生在查询字段均可通过索引覆盖，但需遍历所有索引节点。性能优于 ALL，但仍较差。

（7）ALL：全表扫描

不使用索引，遍历整个表查找匹配行，性能最差，应尽量避免。

优化目标：至少保证查询达到 range 级别，最好达到 ref 或 eq_ref 级别。若出现 ALL 或 index，需检查是否缺少索引或索引使用不当。

5. possible_keys 与 key：索引的候选与实际使用

• possible_keys：MySQL 认为可能适用的索引（查询涉及的字段上的所有索引），但不一定会实际使用；
• key：MySQL 实际选择使用的索引（必然是 possible_keys 中的一个，若为 NULL 则未使用索引）。

 

注意：possible_keys 有值但 key 为 NULL 的情况，通常是因为表数据量太小，MySQL 优化器认为全表扫描比走索引更快。

6. key_len：索引使用的字节数

key_len 表示查询中实际使用的索引长度（字节），可用于判断索引的使用效率：

• 长度越短，说明索引使用越充分（如联合索引只使用了部分字段时，长度较短）；
• 计算规则：根据字段类型（如 INT 占 4 字节，VARCHAR(20) utf8mb4 编码占 20*4 + 2 字节）和是否为 NOT NULL 计算。

7. ref：索引匹配的条件

ref 列显示哪些列或常量被用于匹配索引列的值：

• 若为常量（如 const），表示使用常量匹配；
• 若为表名。列名（如 users.id），表示使用其他表的列进行关联匹配。

8. rows：预估扫描行数

rows 是 MySQL 优化器预估的需要检查的行数，值越小越好。它是判断查询效率的重要参考：

• 对于单表查询，rows 直接反映扫描范围；
• 对于多表连接，rows 的乘积可估算总扫描量。

9. filtered：过滤比例

filtered 是一个百分比，表示符合条件的行数占 rows 的比例。rows * filtered / 100 可估算与前一个表连接的行数，值越高说明过滤效果越好。

10. Extra：额外信息（关键优化点）

Extra 列包含大量关键的额外信息，常见值如下：

（1）Using index：覆盖索引

查询所需的所有字段都包含在索引中，无需回表查询数据行，性能极佳。
示例：

-- 索引 (user_id, status) 覆盖查询字段
EXPLAIN SELECT user_id, status FROM orders WHERE user_id = 100;

（2）Using where：使用 WHERE 过滤

索引未覆盖所有过滤条件，需在索引查找后用 WHERE 子句进一步过滤。

（3）Using temporary：使用临时表

MySQL 需要创建临时表存储中间结果（如 GROUP BY 非索引字段、DISTINCT 等），性能较差，应优化索引避免。

（4）Using filesort：文件排序

查询需要排序，且无法利用索引排序，需在内存或磁盘中进行排序，性能较差。
优化：让排序字段包含在索引中，利用索引有序性避免文件排序。

（5）Using join buffer：使用连接缓冲区

表连接未使用索引，通过连接缓冲区存储中间结果，性能差，需为连接字段添加索引。

三、实战分析：通过 EXPLAIN 优化查询

问题 SQL：查询用户订单，未使用索引，执行计划如下：

+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 10000| Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+

• type 为 ALL（全表扫描），key 为 NULL（未用索引），需优化。

优化方案：为 user_id 添加索引：

ALTER TABLE orders ADD INDEX idx_user_id (user_id);

 

优化后执行计划：

+----+-------------+--------+------+---------------+--------------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key          | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+--------------+---------+------+------+-------------+
|  1 | SIMPLE      | orders | ref  | idx_user_id   | idx_user_id  | 4       | const| 100  | Using where |
+----+-------------+--------+------+---------------+--------------+---------+------+------+-------------+

• type 提升为 ref，rows 从 10000 降至 100，性能大幅提升。

四、总结

EXPLAIN 命令是 MySQL 性能优化的 “显微镜”，通过解读其输出的 type、key、rows、Extra 等字段，能精准定位查询瓶颈：

• 优先关注 type 字段，确保查询至少达到 range 级别；
• 避免 Using temporary 和 Using filesort，通过索引优化排序和分组；
• 利用 Using index 实现覆盖索引，减少回表操作。