EXPLAIN 是 MySQL 提供的一个非常强大的工具，用于分析 SQL 查询语句的执行计划。通过使用 EXPLAIN 命令，我们可以深入了解 MySQL 是如何执行一个 SELECT、INSERT、UPDATE 或 DELETE 语句的，包括它使用了哪些索引、表的连接顺序、扫描了多少行数据等。理解 EXPLAIN 的输出对于数据库性能优化至关重要，它可以帮助开发者识别并解决慢查询问题。

核心思想：揭示查询语句的内部执行机制，为索引设计、SQL 重写和数据库结构优化提供数据支持。

一、为什么需要 EXPLAIN？

在复杂的数据库应用中，性能问题往往是瓶颈所在。SQL 查询效率低下是导致性能问题的常见原因之一。当一个 SQL 查询执行缓慢时，我们需要知道：

  • 是否使用了正确的索引？ 或者根本没有使用索引？
  • 扫描了多少行数据？ 全表扫描还是部分扫描？
  • 表的连接顺序是否合理？
  • 是否存在不必要的临时表或文件排序？
  • 查询的瓶颈究竟在哪里？

EXPLAIN 命令能够回答这些问题，它通过输出一张表格来详细描述 MySQL 查询优化器的工作方式，从而帮助我们：

  • 定位性能瓶颈：快速找出查询中效率低下的部分。
  • 评估索引效果：判断现有索引是否被有效利用，或者是否需要创建新索引。
  • 优化 SQL 语句：根据 EXPLAIN 的建议，调整 SQL 语句的写法。
  • 优化表结构：根据 EXPLAIN 的反馈，改进表的设计。

二、EXPLAIN 的使用方法

EXPLAIN 命令非常简单，只需在待分析的 SELECT（最常用）、INSERT、UPDATE 或 DELETE 语句前加上 EXPLAIN 关键字即可。

语法：

1
2
3
4
EXPLAIN SELECT ... FROM ... WHERE ...;
EXPLAIN INSERT ...;
EXPLAIN UPDATE ...;
EXPLAIN DELETE ...;

示例：

1
EXPLAIN SELECT * FROM users WHERE age > 25 ORDER BY name;

执行后，MySQL 会返回一个表格，包含多行数据，每行代表查询计划中的一个操作步骤（通常与表相关）。

三、EXPLAIN 输出列详解

EXPLAIN 输出的表格包含了多个列，每个列都提供了关于查询计划的重要信息。理解这些列的含义是解读 EXPLAIN 输出的关键。

列名 描述 优化建议
id SELECT 查询的标识符。 如果查询包含子查询或 UNION 操作，则每个 SELECT 语句都会有一个唯一的 id。id 越大，优先级越高；id 相同，则从上往下依次执行。 用于判断子查询的执行顺序。
select_type SELECT 查询的类型。 表示查询中每个部分的 SQL 操作类型，常见类型包括：- SIMPLE：简单的 SELECT 查询，不包含 UNION 或子查询。- PRIMARY：最外层的 SELECT 查询。- SUBQUERY：子查询中的第一个 SELECT。- DERIVED：派生表（FROM 子句中的子查询）SELECT。- UNION：UNION 中的第二个或后续 SELECT。- UNION RESULT：UNION 查询的结果。 SUBQUERY 和 DERIVED 生成的临时表可能导致性能问题，考虑重写为 JOIN。
table 正在访问的表名。 有时会是 <derivedN>（N 是 id 号）表示派生表，或 <unionM,N...> 表示 UNION 结果。 用于识别每个操作涉及的具体表。
partitions 查询匹配到的分区。 对于使用了分区表的查询，这个列会显示命中的分区。 如果查询命中了过多分区，可能需要优化分区策略或查询条件。
type Join 类型/访问类型。 这是 EXPLAIN 输出中最重要的列之一，表示 MySQL 如何查找表中的行。从最好到最差的顺序大致是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。ALL 是最差的情况 (全表扫描)。 ALL 代表全表扫描，应极力避免。 争取达到 range 或 ref 级别，最好是 const 或 eq_ref。优化方向通常是通过创建索引来改变 type。
possible_keys 可能被用于查询的索引。 MySQL 优化器认为在查找表中行时可能使用的索引。不代表实际使用了这些索引。 如果此列为空，说明没有合适的索引可用。如果 key 列也不为空，但 possible_keys 为空，说明索引可能不是最优或有其他问题。
key 实际使用的索引。 MySQL 优化器最终选择用于查询的索引。如果为 NULL，表示没有使用索引。这可能意味着没有合适的索引，或者优化器认为全表扫描更有效 (例如，表太小或需要扫描大部分行)。 如果为 NULL，通常需要考虑创建合适的索引。如果 key 和 possible_keys 不同，可能需要手动 FORCE INDEX 或分析优化器选择的原因。
key_len 实际使用的索引的长度（字节数）。 表示 MySQL 此次查询中使用了索引的哪个部分。对于复合索引，key_len 可以告诉你多列索引中实际使用了多少列。 key_len 值越小，通常说明索引的使用越高效。对于复合索引，如果 key_len 没有达到预期长度，可能说明索引没有完全被利用 (如没有使用到覆盖索引全部列)。
ref 与 key 列所示索引结合使用的列或常量。 说明了哪个列或常量被用于与 key 列中使用的索引进行比较。例如，如果 key 是 idx_name，ref 可能是 const（常量）、database.table.column_name（某个表的列）。 ref 列通常与 type 列一起判断索引的使用效率。如果 ref 引用的是常量，通常效率高。
rows MySQL 估计为了找到所需的行而必须读取的行数。 这是一个非常重要的指标，可以粗略地衡量查询的效率。它不是一个精确的值，而是一个估计值。 rows 越小越好。 高的 rows 值通常意味着查询效率低，可能存在全表扫描或索引使用不当。优化目标是让 rows 尽可能接近实际返回的行数。
filtered 通过表条件过滤后，剩余的行百分比的估计值（MySQL 5.7+）。 例如，如果 rows 是 1000，filtered 是 10.00，则表示有 100 行通过条件过滤后保留下来。这与 rows 列一起用于估计传递给下一个表中的行数。 filtered 值越高越好 (接近 100%)，说明经过索引检索后，再通过 WHERE 条件过滤掉的行数越少。值越低，说明过滤条件效果不佳，可能需要创建更精确的索引。
Extra 额外信息。 提供了关于查询执行计划的额外详细信息，这通常是 EXPLAIN 输出中最重要的部分之一。常见的 Extra 值包括：- Using filesort：需要进行文件排序，通常出现在 ORDER BY 子句中没有使用索引的情况下，应尽量避免。- Using temporary：MySQL 需要创建临时表来处理查询，通常出现在 DISTINCT、GROUP BY或UNION操作中，且无法通过索引优化。-Using index：**使用覆盖索引**，表示查询所需的所有数据都可以在索引中找到，无需回表查询，效率非常高。- Using where：使用了 WHERE 子句来限制返回的行，但可能没有充分利用索引。- Using index condition：**索引条件下推 (ICP)**，MySQL 5.6+ 引入的优化。在存储引擎层就对数据进行过滤，而不是返回所有数据到服务器层再过滤。- Using join buffer (Block Nested Loop)：使用了连接缓冲区。- Impossible WHERE`：WHERE 子句总是为假，查询永远不会返回任何行。 应尽量避免 Using filesort 和 Using temporary。 Using index 表示使用了覆盖索引，这是非常高效的。Using index condition 也是一个好的优化标志。

四、EXPLAIN 优化案例与解读

通过几个案例来演示如何利用 EXPLAIN 进行优化。

4.1 案例一：全表扫描 (ALL)

表结构：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(50) NOT NULL,
  `age` INT,
  `email` VARCHAR(100),
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (username, age, email) VALUES
('Alice', 28, 'alice@example.com'),
('Bob', 35, 'bob@example.com'),
('Charlie', 22, 'charlie@example.com'),
('David', 40, 'david@example.com'),
('Eve', 30, 'eve@example.com');

查询：

1
EXPLAIN SELECT * FROM users WHERE age = 30;

EXPLAIN 输出：

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE users NULL ALL NULL NULL NULL NULL 5 20.00 Using where

解读与优化：

  • type: ALL：表示全表扫描，这是最糟糕的情况。
  • possible_keys: NULL, key: NULL：说明没有使用任何索引。
  • rows: 5：估计需要读取 5 行，对于小表尚可接受，但对于大表将是灾难。
  • Extra: Using where：表示 WHERE 条件用于过滤，但没有通过索引加速过滤。

优化方案： 为 age 列添加索引。

1
CREATE INDEX idx_age ON users (age);

再次 EXPLAIN：

1
EXPLAIN SELECT * FROM users WHERE age = 30;

新的 EXPLAIN 输出：

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE users NULL ref idx_age idx_age 5 const 1 100.00 NULL

优化后解读：

  • type: ref：表示使用了非唯一索引进行查找，效率显著提高。
  • key: idx_age：实际使用了 idx_age 索引。
  • key_len: 5：索引长度。
  • ref: const：表示与一个常量进行比较。
  • rows: 1：估计只需要读取 1 行，效率极高。
  • Extra: NULL：没有额外的复杂操作。

4.2 案例二：文件排序 (Using filesort)

查询：

1
EXPLAIN SELECT * FROM users WHERE age > 25 ORDER BY username;

假设 username 列没有索引。

EXPLAIN 输出 (部分)：

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE users NULL range idx_age idx_age 5 NULL 3 100.00 Using where; Using filesort

解读与优化：

  • type: range：对于 age > 25 范围查询，使用了 idx_age 索引，这部分不错。
  • Extra: Using filesort：这是关键，表示 MySQL 无法利用索引进行排序，而是在内存或磁盘上对结果集进行额外的排序操作。对于大量数据，filesort 会非常耗时。

优化方案： 为 username 列添加索引，以便 ORDER BY 子句可以使用索引。或者考虑创建复合索引以同时覆盖 WHERE 和 ORDER BY。

1
CREATE INDEX idx_username ON users (username);

再次 EXPLAIN：

1
EXPLAIN SELECT * FROM users WHERE age > 25 ORDER BY username;

新的 EXPLAIN 输出 (部分)：

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE users NULL index idx_age,idx_username idx_username 152 NULL 5 60.00 Using where

优化后解读：

  • type: index：表示 MySQL 对 idx_username 索引进行了全扫描以获取排序后的结果，虽然不是 ref 或 range 那么高效，但至少避免了 filesort。
  • Extra: Using where：说明 age > 25 仍然需要额外过滤。

更优的方案 (复合索引)：

如果查询经常同时用到 age 和 username 进行过滤和排序，可以考虑一个包含 age 和 username 的复合索引。

1
CREATE INDEX idx_age_username ON users (age, username);

再次 EXPLAIN：

1
EXPLAIN SELECT * FROM users WHERE age > 25 ORDER BY username;

新的 EXPLAIN 输出 (部分)：

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE users NULL range idx_age_username idx_age_username 5 NULL 3 100.00 Using where

此时 type: range，且 Extra 中没有 Using filesort。这是因为 ORDER BY username 可以利用 idx_age_username 索引的顺序特性来避免额外的排序。

4.3 案例三：覆盖索引 (Using index)

查询：

1
EXPLAIN SELECT id, username FROM users WHERE age > 25;

EXPLAIN 输出：

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE users NULL range idx_age idx_age 5 NULL 3 100.00 Using index condition

解读与优化：

  • type: range：使用了 idx_age 索引。
  • Extra: Using index condition：这是 MySQL 5.6+ 的一个优化，表示索引条件下推。它在存储引擎层就过滤了大部分不满足 age > 25 的行，减少了返回到服务器层的行数。

如果要实现“覆盖索引 (Using index)”，即查询的所有列都包含在索引中，无需再回表查询数据。

优化方案： 创建一个包含 age、id 和 username 的复合索引。

1
CREATE INDEX idx_age_id_username ON users (age, id, username);

再次 EXPLAIN：

1
EXPLAIN SELECT id, username FROM users WHERE age > 25;

新的 EXPLAIN 输出：

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE users NULL range idx_age,idx_age_id_username idx_age_id_username 5 NULL 3 100.00 Using index

优化后解读：

  • key: idx_age_id_username：使用了复合索引。
  • Extra: Using index：表示使用了覆盖索引。因为查询需要的 id 和 username 列都在 idx_age_id_username 索引中，MySQL 无需再访问主表，大大提高了查询效率。

五、EXPLAIN 扩展：FORMAT = JSON

从 MySQL 5.7 开始，EXPLAIN 支持 FORMAT = JSON，可以输出更详细、更结构化的 JSON 格式执行计划。这对于自动化分析和更深入的优化非常有用。

语法：

1
EXPLAIN FORMAT = JSON SELECT ...;

示例：

1
EXPLAIN FORMAT = JSON SELECT * FROM users WHERE age = 30;

JSON 格式的输出包含了更多细节，例如成本估计 (cost_info)、具体操作的嵌套结构等，但其基本信息与传统表格输出是对应的。

六、总结

EXPLAIN 是 MySQL DBA 和开发者进行性能优化的必备工具。通过系统地学习和实践 EXPLAIN 输出中的各个列 (id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered, Extra)，我们可以深入理解查询的执行计划，识别潜在的性能瓶颈。

优化总结原则：

  • 避免 type: ALL：尽量通过索引将访问类型优化到 range、ref 甚至 const。
  • 避免 Using filesort 和 Using temporary：它们通常是性能问题的根源，可以通过创建合适的多列索引或重写 SQL 语句来消除。
  • 追求 Using index (覆盖索引)：如果查询所需的列都在索引中，避免回表操作可以显著提高性能。
  • 关注 rows 和 filtered：这两个值越小越好，代表 MySQL 需要处理的数据量越少。

通过反复使用 EXPLAIN 并结合实践，你将能够有效地优化 MySQL 查询，提升数据库整体性能。