MySQL EXPLAIN 命令详解

MySQL EXPLAIN 命令详解

EXPLAIN 是 MySQL 中一个非常有用的命令，用于分析 SQL 查询的执行计划，帮助开发者理解查询是如何被执行的，从而优化查询性能。

EXPLAIN 输出的基本结构

执行 EXPLAIN 后，MySQL 会返回一个表格，包含多列信息。以下是主要字段的详细解释：

id

• 用途：查询的标识符，表示查询中 SELECT 子句或操作表的顺序。
• 值：数字，从 1 开始递增。
• 特殊情况：
  • 如果 id 相同，表示这些操作是同一查询的一部分，执行顺序从上到下。
  • 如果 id 不同，id 值越大，优先级越高，越先执行。

2. select_type

• 用途：表示 SELECT 查询的类型。
• 常见值：
  • SIMPLE：简单的 SELECT 查询，不包含子查询或 UNION。
  • PRIMARY：最外层的 SELECT 查询（在包含子查询或 UNION 时）。
  • SUBQUERY：子查询中的第一个 SELECT（不在 FROM 子句中）。
  • DERIVED：派生表（FROM 子句中的子查询）。
  • UNION：UNION 中的第二个或后续的 SELECT 查询。
  • UNION RESULT：UNION 的结果集。

3. table

• 用途：表示当前查询涉及的表名或别名。
• 特殊情况：
  • 对于派生表（DERIVED），会显示派生表的名称（通常是子查询的编号）。
  • 对于临时表，可能会显示 “temporary”。

4. partitions

• 用途：表示查询访问的分区（如果表是分区表）。
• 值：分区名列表，如果没有分区则为 NULL。

5. type

• 用途：表示访问类型，是 EXPLAIN 中最重要的列之一，反映了查询的效率。
• 常见值及效率从高到低排序：
  • system：表只有一行（系统表），这是 const 的特例。
  • const：通过索引一次就找到了，用于比较 PRIMARY KEY 或 UNIQUE 索引。因为只匹配一行数据，所以很快。
  • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引的连接查询。
  • ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
  • range：只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引。一般就是在你的 where 语句中出现了 between、<、>、in 等的查询。
  • index：Full Index Scan，Index 与 All 区别为 index 类型只遍历索引树。这通常比 ALL 快，因为索引文件通常比数据文件小。（也就是说虽然 all 和 index 都是读全表，但 index 是从索引中读取的，而 all 是从硬盘中读的）
  • ALL：Full Table Scan，即全表扫描，意味着 MySQL 必须扫描整张表来找到需要的行。

6. possible_keys

• 用途：表示查询可能使用的索引。
• 值：索引名列表，如果没有可能的索引则为 NULL。
• 注意：这只是 MySQL 认为可能使用的索引，实际执行时可能不会使用这些索引。

7. key

• 用途：表示查询实际使用的索引。
• 值：索引名，如果没有使用索引则为 NULL。
• 注意：如果 key 为 NULL，表示没有使用任何索引。

8. key_len

• 用途：表示使用的索引的长度（字节数）。
• 值：数字，表示索引使用的字节数。
• 注意：
  • 可以通过这个值估算出使用了多少列。
  • 对于字符串类型，key_len 的计算方式是：字符集的每个字符占用的字节数 × 字符串长度 + 可能的额外字节（如 NULL 终止符）。
  • 对于数字类型，key_len 是固定的。

9. ref

• 用途：表示索引的哪一列被使用了，或者常量被用于比较。
• 值：
  • 如果是 const，表示使用了常量值。
  • 如果是列名，表示使用了该列的值进行比较。
  • 如果是 NULL，表示没有使用引用。

10. rows

• 用途：表示 MySQL 认为必须检查的行数。
• 值：数字，表示估计需要检查的行数。
• 注意：
  • 这是一个估计值，不是精确值。
  • 值越小，查询效率越高。

11. filtered

• 用途：表示存储引擎返回的数据在 server 层过滤后，剩余的数据的百分比。
• 值：百分比（0-100）。
• 注意：
  • 这个值可以帮助你理解查询的过滤效率。
  • 值越高，表示过滤效果越好。

12. Extra

• 用途：包含额外的信息，对查询优化非常有用。
• 常见值及含义：
  • Using index：表示使用了覆盖索引（查询的列都在索引中，不需要回表）。
  • Using where：表示使用了 WHERE 条件过滤。
  • Using join buffer：表示使用了连接缓存（通常出现在没有使用索引的连接查询中）。
  • Using temporary：表示使用了临时表（通常出现在 GROUP BY 或 ORDER BY 中使用了非索引列时）。
  • Using filesort：表示使用了文件排序（通常出现在 ORDER BY 使用了非索引列时）。
  • Using index condition：表示使用了索引条件下推（ICP，MySQL 5.6+ 的特性）。
  • Using sort_union/Using union/Using intersect：表示使用了索引合并优化。

使用 EXPLAIN 的注意事项

1. EXPLAIN 不会执行查询：它只是分析查询计划，不会实际执行查询。
2. EXPLAIN 的结果依赖于当前数据：如果表的数据分布发生变化，EXPLAIN 的结果可能会不同。
3. EXPLAIN 的结果依赖于 MySQL 版本：不同版本的 MySQL 可能有不同的执行计划和 EXPLAIN 输出。
4. 结合其他工具使用：EXPLAIN 只是优化查询的一个工具，还需要结合慢查询日志、性能监控等其他工具进行综合分析。

示例

EXPLAIN SELECT * FROM users WHERE id = 1;

这个查询可能会返回类似如下的结果：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	users	NULL	const	PRIMARY	PRIMARY	4	const	1	100.00	Using index

从这个结果可以看出：

• 查询使用了 PRIMARY 索引
• 只需要检查 1 行数据
• 使用了覆盖索引（Using index）