MySQL中EXPLAIN命令详解与优化指南

EXPLAIN 基本用法

使用非常简单，只需在待分析的 SELECT 语句前加上 EXPLAIN 关键字即可。

mysql> EXPLAIN SELECT * FROM kt_course ORDER BY create_time DESC;

输出结果通常包含以下列：

• id: SELECT 查询的序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序。
• select_type: 表示 SELECT 的类型，常见有 SIMPLE（简单查询）、PRIMARY（主查询）、UNION（联合查询）、SUBQUERY（子查询）等。
• table: 输出结果所的表名。
• type: 访问类型，反映查询效率的关键指标，从最好到最差依次为：system > const > eq_ref > ref > range > index > ALL。
• possible_keys: 显示可能应用在这张表中的索引。如果为空，表示没有可能的索引。
• key: 实际使用的索引。如果为 NULL，则没有使用索引。
• key_len: 使用索引的长度，越短越好。
• ref: 显示索引的哪一列被使用了。
• rows: 预计要检查的行数。
• Extra: 额外信息，包含重要优化线索，如 Using filesort、Using temporary 等。

核心列深度解析

第1步：理解 Select_Type 含义

该列标识查询的类型，用于判断查询的复杂度：

• SIMPLE: 简单的 SELECT 查询，不使用 UNION 或子查询。
• PRIMARY: 如果查询包含任何复杂的子部分，最外层的那部分被标记为 PRIMARY。
• UNION: UNION 中的第二个或随后的 SELECT 语句。
• SUBQUERY: 子查询中的第一个 SELECT。
• DERIVED: 导出表的 SELECT （FROM 子句的子查询）。

第2步：评估访问类型 (Type)

Type 列显示了连接使用了何种类型，从最好到最差的连接类型如下：

1. System/Const: 表中最多只有一行匹配。Const 用于比较主键或唯一索引。这是最高效的连接类型。
2. eq_ref: 从前面的表中，对每一个记录的联合都从表中读取一个记录。通常出现在主键或唯一索引的关联查询中。
3. Ref: 非唯一或非主键索引的使用。对于之前的表的每一个行联合，全部记录都将从表中读出。依赖索引匹配的记录数量，越少越好。
4. Range: 使用索引返回一个范围中的行，例如使用 >、<、BETWEEN 等操作时发生的情况。
5. Index: 对前面的表中的每一个记录联合进行完全扫描，但只扫描索引树。比 ALL 好，因为索引通常小于表数据。
6. ALL: 全表扫描。对于前面的每一个记录联合进行完全扫描。这通常是最差的情况，应尽量避免。

第3步：分析 Extra 字段以定位问题

Extra 列提供了关于 MySQL 如何解析查询的额外信息，是优化的重点：

• Using filesort: MYSQL 需要进行额外的步骤来发现如何对返回的行排序。这意味着查询无法利用索引排序，需要优化。
• Using temporary: MYSQL 需要创建一个临时表来存储结果。这通常发生在对不同的列集进行 ORDER BY 或 GROUP BY 时，严重影响性能。
• Using index: 所需数据仅需在 Index 即可获得，不需要再到表中取数据（覆盖索引），这是非常好的现象。
• Using where: 服务器层面对存储引擎层返回的数据进行了过滤。如果连接类型为 ALL 或 index，且出现了 Using where，说明查询效率较低。
• Using index for group-by: 类似 Using index，但在查询中使用 GROUP BY 或 DISTINCT 子句时，如果分组字段也在索引中，则会显示此信息。

常见优化建议

当 EXPLAIN 结果显示 type 为 ALL 或 Extra 出现 Using filesort/Using temporary 时，建议采取以下措施：

1. 检查索引: 确认 WHERE 子句和 ORDER BY 子句涉及的列是否建立了合适的索引。
2. 使用强制索引: 如果 MySQL 选择了非最优索引，可以使用 USE INDEX(indexname) 强制指定索引，或使用 IGNORE INDEX(indexname) 忽略特定索引。
3. 重构查询: 避免在索引列上进行函数运算或类型转换，确保使用最左前缀原则。
4. 覆盖索引: 尽可能选择使用覆盖索引（Select 列表中只有索引列），这样只需扫描索引页而不需要回表，可极大提升性能。